Wednesday, July 25, 2012

ORA-00376: file 6 cannot be read at this time


When I try to start a standby database in the read only mode, I have been thrown the below error message
oracle BHU_1> srvctl start database -d BHU_B
PRCR-1079 : Failed to start resource ora.BHU_b.db
CRS-5017: The resource action "ora.BHU_b.db start" encountered the following error:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '+BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193'
Process ID: 9165
Session ID: 3497 Serial number: 1
. For details refer to "(:CLSN00107:)" in "/oracle/GRID/11203/log/bhurac01/agent/crsd/oraagent_oracle/oraagent_oracle.log"

It is an undo tablespace (datafile) which is throwing the error and I have checked the datafile it is in the recovery mode.
select file#,name,status,enabled from v$datafile where file#=6;
6 +BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193                                                                                                   RECOVER READ WRITE
SO I HAVE FOLLOWED BELOW PROCEDURE TO OVERCOME THIS PROBLEM

#1
1 A)  Stop the apply process in the standby database

DGMGRL> edit database 'BHU_B' set state='APPLY-OFF';
Succeeded.

2 B)  It is RAC Database, kept only one instance in mount stage and other instance are in the offline mode(shutdown)
3
  C)  It is standby database (undo tablespace-datafile). So I am not able to do offline

#2 – Take a online backup of datafile through RMAN

RMAN> copy datafile 6 to '+BHU_B_DATA1';
Starting backup at 25-JUL-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1842 instance=BHU_1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+BHU_B_SYSTEM/BHU_b/datafile/undo.264.789559193
output file name=+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089 tag=TAG20120725T124447 RECID=69 STAMP=789569388
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:05
Finished backup at 25-JUL-12
Starting Control File and SPFILE Autobackup at 25-JUL-12
piece handle=/oracle/BHU/11203/dbs/c-72629545-20120725-01 comment=NONE
Finished Control File and SPFILE Autobackup at 25-JUL-12

#3 I doing a rename through RMAN itself, there is no need of using the RENAME command in the sqlplus

RMAN> switch datafile 6 to copy;
datafile 6 switched to datafile copy "+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089"
RMAN> exit

#4 when I check the status of the datafile, it looks in the RECOVER MODE. So I cant open the database in the READ-ONLY MODE.

SQL> select name,status from v$datafile where file#=6;
NAME                                                 STATUS
----------------------------------------------------------------------------
+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089  RECOVER

#5 Started the Recover through DG Broker

DGMGRL> edit database 'BHU_B' set state='APPLY-ON';
Succeeded.

#6 Monitor the apply Lag
I could see that the system is using the old archive log to recover the datafile.

Once the recovery is completed, you can open the database in the read only mode.
I have see the status of the datafile; it is in the online mode

SQL> select name,status from v$datafile where file#=6;
NAME                                  STATUS
----------------------------------------------------------
+BHU_B_DATA1/BHU_b/datafile/undo.301.789569089 ONLINE

No comments:

Post a Comment