DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, September 23, 2015

ORA-20200: Database/Instance 2899795566/1 does not exist in DBA_HIST_DATABASE_INSTANCE While taking AWR report

You are getting the error while taking AWR report, so just re-create the AWR report as follows,


1.Take the backup of pfile before changes

create pfile='/home/oracle/admin/dbs/init@.ora.20140122' from spfile;

In 11g, you may need to set both SGA_TARGET and MEMORY_TARGET to 0:


alter system set sga_target=0 scope= spfile;
alter system set memory_target=0 scope= spfile;

otherwise database may not startup and will fail with the errors below:

SQL> startup restrict
ORA-01078: failure in processing system parameters
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGET

2.Shutdown database and startup in restrict mode so that no transactions will occur while dropping the
    AWR repository:

sqlplus /nolog
connect / as sysdba
shutdown immediate
startup restrict

3. Drop and recreate the AWR objects

The following scripts drop AWR tables and then recreates them.
After recreating ,utlrp is run in order to validate all views and objects dependent on the AWR tables.
-- On both 10g and 11g drop AWR

start ?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;


4.Check to see if all the objects are dropped :

SQL> select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';

If there are objects after running catnoawr.sql, drop them manually:

drop type AWR_OBJECT_INFO_TABLE_TYPE;
drop type AWR_OBJECT_INFO_TYPE;
drop table WRH$_PLAN_OPERATION_NAME;
drop table WRH$_PLAN_OPTION_NAME;
drop table WRH$_MV_PARAMETER;
drop table WRH$_MV_PARAMETER_BL;
drop table WRH$_DYN_REMASTER_STATS;
drop table WRH$_PERSISTENT_QMN_CACHE;
drop table WRH$_DISPATCHER;
drop table WRH$_SHARED_SERVER_SUMMARY;
drop table WRM$_WR_USAGE
drop table WRM$_SNAPSHOT_DETAILS


5.Now create AWR:

start ?/rdbms/admin/catawrtb.sql
start ?/rdbms/admin/utlrp.sql

start ?/rdbms/admin/execsvrm.sql

If following errors occur, then recompile the objects:


start ?/rdbms/admin/execsvrm.sql

Fails with the following errors :

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYS.DBMS_SWRF_INTERNAL" has been
invalidated


6.Compile again the package dbms_swrf_internal even if the objects appear valid:

alter package dbms_swrf_internal compile;
alter package dbms_swrf_internal compile body;

Reset the parameters shared_pool_size,db_cache_size, java_pool_size ,large_pool_size, sga_target  statistics_level and cluster_database to original values.Also can reset the parameter from copy of the spfile:


create spfile from pfile='/home/oracle/admin/dbs/init@.ora.20140122' ;

Restart instance in normal mode:

sqlplus /nolog
    connect / as sysdba
    shutdown immediate
    startup


7) Check invalid objects exists are not , if exists then please compile it manually
     As we have run utlrp.sql, any invalid objects should already have been reported there


spool objects.lst
set pagesize500
set linesize 100

select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version
from dba_registry
order by comp_name;

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type
from dba_objects
where status='INVALID' order by owner,object_type;

select owner,object_type,count(*)
from dba_objects
where status='INVALID'
group by owner,object_type order by owner,object_type ;

spool off

alter package <schema name>.<package_name> compile;
alter package <schema name>.<package_name> compile body;
alter view <schema name>.<view_name> compile;
alter trigger <schema).<trigger_name> compile;


8) To take the AWR snapshots:

exec dbms_workload_repository.create_snapshot;
--wait for 5 min
exec dbms_workload_repository.create_snapshot;

9) To create  AWR report run the script:

start $ORACLE_HOME/rdbms/admin/awrrpt.sql

Now you can able to take the report.




No comments: