ORA-00313 ORA-00312 ORA-27037 Standby Database

PROBLEM :

 

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/d02/oradata/XXXXXXXX/redog1m2.log'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 1 thread 1: '/d01/oradata/ XXXXXXXX/redog1m1.log'

ORA-27037: unable to obtain file status

IBM AIX RISC System/6000 Error: 2: No such file or directory

Additional information: 3

Clearing online redo logfile 1 /d01/oradata/ XXXXXXXX/redog1m1.log

Clearing online log 1 of thread 1 sequence number 120028

Errors in file /oradba/app/oracle/diag/rdbms/ XXXXXXXX/ XXXXXXXX/trace/ XXXXXXXX_mrp0_64356518.trc:

 

 

SOLUTION :

 

SQL> alter database recover managed standby database cancel;

SQL>alter system set log_file_name_convert=’junk,junk’ scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

System Workload Statistics (Gather,Export and Import)

As noworkload system statistics are initialized to default values at the first database startup, we need to first gather system WORKLOAD statistics. This will gather statistics during the current workload (which is supposed to be representative of actual system I/O and CPU workload on the DB and need to be gathered when there is heavy/good workload on database)

 

a)    Gather it :

SQL> execute dbms_stats.gather_system_stats('start');

Once the workload window ends after 3,4.. hours or whatever, system statistics gathering need to be stopped using below:

SQL> execute dbms_stats.gather_system_stats('stop');

 

OR

We can use time interval (minutes) instead of issuing start/stop command manually:

SQL> execute dbms_stats.gather_system_stats('interval',60);

 

Please note that gathering workload system statistics doesn't put additional load into system.

 

Check the system values collected:
--------------------------------------------
sql> col pname format a20
sql> col pval2 format a40
sql> select * from sys.aux_stats$;

 

b)    Create STAT table and export the system stats :

 

SQL> exec dbms_stats.create_stat_table(stattab=>'STATS_TABLE_NAME',ownname=>'schema',tblspace => 'tablespace_name');

SQL> EXEC dbms_stats.export_system_stats(stattab=>'STATS_TABLE_NAME',statown=>'OWNER');

 

Example –

SQL> exec dbms_stats.create_stat_table(ownname => 'SYS', stattab => 'prod_stats',tblspace => 'USERS');

SQL> exec dbms_stats.export_system_stats(statown => 'SYS', stattab => 'prod_stats');

 

 

c)     Move statistics from one database to another, copy the statistics table to the second database using datapump and finally import the statistics into the second database

d)    Import the statistics into data dictionary :

Note that the optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary. In order to have the optimizer use the statistics in a user-owned tables, you must import those statistics into the data dictionary using the statistics import procedures.

 

SQL> exec dbms_stats.import_system_stats(stattab => 'prod_stats',statown => 'SYS');