Oracle Home

Erro ORA-20200 ao tentar gerar o Relatório do AWR (Automatic Workload Repository)

Postado em Fevereiro 22nd, 2012 sobre Oracle Database,Performance,Scripts por Maycon Tomiasi

Este artigo tem o objetivo de explicar o erro “ORA-20200″ ocorrido ao tentar gerar o relatório de estatísticas do Banco de Dados Oracle do AWR (Automatic Workload Repository).
Segue a evidência abaixo:

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  862715809 DBPROD              1 dbprod

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  862715809         1 DBPROD       dbprod       localhost.lo
                                                caldomain
* 862715809         1 DBPROD       dbprod       serveroracle
                                                .localdomain

Using  862715809 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
dbprod       DBPROD              80 17 Feb 2012 13:54      1
                                 81 17 Feb 2012 15:00      1

                                 82 17 Feb 2012 15:40      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 81
Begin Snapshot Id specified: 81

Enter value for end_snap: 82
End   Snapshot Id specified: 82

declare
*
ERROR at line 1:
ORA-20200: The instance was shutdown between snapshots 81 and 82
ORA-06512: at line 42

Disconnected from Oracle Database 10g Release 10.2.0.5.0 - Production
[oracle@serveroracle ~]$

Conforme acima, estava tentando gerar um relatório do AWR para comparação de períodos entre os horários 15:00 e 15:40 horas, porém conforme abaixo, a instância foi reiniciada as 15:11 horas.

[oracle@serveroracle ~]$ sqlplus *********** as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Feb 17 15:42:31 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.5.0 - Production

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select startup_time from v$instance;

STARTUP_TIME
-------------------
17/02/2012 15:11:20

SQL>

Ação:

Para evitar o erro acima, após reiniciar a instância, criar um novo snapshot para comparar com o outro gerado automaticamente pelo Banco de Dados ou manualmente.
Para gerar o snapshot após a reinicialização do Banco de Dados Oracle.

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL>

Tags: arwrpt.sql, automatic, awr, banco, banco de dados, criando, dados, database, erro, ORA-20200, oracle, performance, repository, script, snapshot, workload