Feb 26, 2011

ORA-27041 in DR

Error: ORA-01186, ORA-01116, ORA-01110, ORA-27041

Overview of the problem

Tablespace was dropped on Primay and archived logs not applied to DR and the below data file was removed from OS level on DR site and below are the errors on DR alert log and DR went down and not starting..

MRP process failling with below error since it could not able to find those datafile physically on DR

ORA-01186: file 35 failed verification tests  
ORA-01116: error in opening database file 35
ORA-01110: data file 35: '/db01/oradata/warm/ASAM200901_f01.dbf'
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory


Solution: On DR database:

a. Set standby file management from 'AUTO' to 'MANUAL'

            SQL> alter system set standby_file_management='MANUAL';

b. Drop those datafiles what ever appered in the error

SQL> alter database datafile '/db01/oradata/warm/ASAM200901_f01.dbf' offline drop;
SQL> alter database datafile '/db01/oradata/warm/ASAM200901_f02.dbf' offline drop;



c. Recover the DR database 

            SQL> alter database recover standby database;

You may see below errors in alert log and these may be ignored:

Managed Standby Recovery not using Real Time Apply
parallel recovery started with 7 processes
ORA-279 signalled during: alter database recover standby database...
Managed Standby Recovery not using Real Time Apply
MRP0: Some datafile enqueues are still held! Retry recovery...

Thu Feb 12 00:28:39 2010
Errors in file /oracle/admin/asam/bdump/asam_mrp0_770128.trc:
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: '/db02/oraundo/system01.dbf

d. restart the mpr process..

      SQL> exit
      $ sqlplus "/as sysdba"
      SQL> alter database recover managed standby database disconnect from session;

You should be seeing below in alert log:

Thu Feb 12 00:31:03 2010
Recovery deleting file #35:'/db01/oradata/warm/ASAM200901_f01.dbf' from controlfile.
Recovery deleting file #125:'/db01/oradata/warm/ASAM200901_f02.dbf' from controlfile.
Recovery dropped tablespace 'MTL201026'
Media Recovery Log /db01/oraarch/asam020_1_632092_602170022.arc
Media Recovery Log /db01/oraarch/asam020_1_632093_602170022.arc
Thu Feb 12 00:31:15 2010

we can confirmed the MRP process restarted and we can switch back the file management to 'AUTO'.

       SQL> alter system set standby_file_management='AUTO';
       SQL> exit;
       $ ps -ef|grep mrp

 you see the MRP process..

We also have one more alternate option to fix this problem...

Create standby control file in primary and recovery the standby database, if this option doesn't work then use rman backup and start Point Of Recovery use roll forward..

1 comment:

oracle real scenarios said...

a. Set standby file management from 'AUTO' to 'MANUAL'


SQL> alter system set standby_file_management='MANUAL';

can you confirm the following command needs to be executed from standby site or from the primary site.

Thanks, Muhammed.