Recovering after loss of members of the INACTIVE redo log group
You’re attempting to open your database and receive this message
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log’
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02a.log’
The message indicates that two members of an online redo log group in your database have experienced a media failure.
To recover when you’ve lost all members of an inactive redo log group, perform the following steps
Verify that the log group status is INACTIVE.
SQL> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
———- ———- —————-
1 2 CURRENT
2 2 INACTIVE
3 2 ACTIVE
Recreate the log group with the clear logfile command.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
(Here I manually deleted redolog files members of group 2)
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1:‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02.log’
ORA-00312: online log 2 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo02a.log’
If all members of an online redo log group are damaged, you won’t be able to open your database, oracle will allow you to only mount your database.
SQL> select status from v$instance;
STATUS
————
MOUNTED
SQL> select group#,status,archived,thread#,sequence# from v$log;
GROUP# STATUS ARC THREAD# SEQUENCE#
———- —————- — ———- ———-
1 CURRENT NO 1 11
3 INACTIVE YES 1 10
2 INACTIVE YES 1 9
If the status is INACTIVE and ARCHIVED=YES,then this log group is no longer needed for crash recovery , therefore ,you can use the clear logfile command to recreate all members of a log group.
SQL> alter database clear logfile group 2;
Database altered.
if STATUS = INACTIVE and ARCHIVED = NO
sql> | ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2; |
Recovering After loss of members of the CURRENT redo log group
All of the members of a current online redo log group in your database have experience media failure.You lose all members of a current online redo log group. Here are possible reasons,
- Perform an incomplete recovery up to the last good SCN.
- If flashback is enabled, flash your database back to the last good SCN.
- If you’re using Oracle data guard, fail over to your physical or logical standby database.
GROUP# STATUS ARC THREAD# SEQUENCE#
———- —————- — ———- ———-
1 CURRENT NO 1 17
2 INACTIVE YES 1 15
3 INACTIVE YES 1 16
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo01.log’
ORA-00312: online log 1 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo01a.log’
ORA-00312: online log 1 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo01.log’
ORA-00312: online log 1 thread 1:
‘/home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/redo01a.log’
SQL> select group#,status,archived,thread#,sequence# from v$log;
GROUP# STATUS ARC THREAD# SEQUENCE#———- —————- — ———- ———-
1 CURRENT NO 1 17
3 INACTIVE YES 1 16
2 INACTIVE YES 1 15
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-01109: database not open
First determine the last good SCN by querying the first_change# column from v$log, you’re missing only the current online redo logs, therefore you can perform an incomplete recovery up to but not including,
SQL> select group#,status,archived,thread#,sequence#,first_change# from v$log;
GROUP# STATUS ARC THREAD# SEQUENCE# FIRST_CHANGE#
———- —————- — ———- ———- ————-
1 CURRENT NO 1 17 574131
3 INACTIVE YES 1 16 574125
2 INACTIVE YES 1 15 574103
SQL>
In this case, you can restore and recover up to but not including, 574131
Operation :-
Losing all members of your current online redo log group is arguably the worst thing taht can happen to your database. if you experience media failure with all members of the current online redo group, then you will lose any transactions contained in those logs. in this case, you will have to perform incomplete recovery before you can open your database.
[oracle@localhost root]$ rlwrap rman target sys/sys
Recovery Manager: Release 10.2.0.1.0 – Production on Mon May 31 10:51:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST (DBID=2015564632, not open)
RMAN> restore database until scn 574131;
Starting restore at 31-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=150 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/system01.dbf
restoring datafile 00002 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/sysaux01.dbf
restoring datafile 00004 to /home/oracle/oracle/product/10.2.0/db_1/oradata/TEST/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/backupset/2010_05_25/o1_mf_nnndf_TAG20100525T103307_5zpz9nft_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/backupset/2010_05_25/o1_mf_nnndf_TAG20100525T103307_5zpz9nft_.bkp tag=TAG20100525T103307
channel ORA_DISK_1: restore complete, elapsed time: 00:01:37
Finished restore at 31-MAY-10
RMAN> recover database until scn 574131;
Starting recover at 31-MAY-10
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 7 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_25/o1_mf_1_7_5zpzh95q_.arc
archive log thread 1 sequence 8 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_8_606k8n7x_.arc
archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_9_606lkvgy_.arc
archive log thread 1 sequence 10 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_10_606mfskg_.arc
archive log thread 1 sequence 11 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_11_606ndqvj_.arc
archive log thread 1 sequence 12 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_12_606szwxo_.arc
archive log thread 1 sequence 13 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_13_606t3zs8_.arc
archive log thread 1 sequence 14 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_14_606t777d_.arc
archive log thread 1 sequence 15 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_15_606t908r_.arc
archive log thread 1 sequence 16 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_16_606t9jd0_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_25/o1_mf_1_7_5zpzh95q_.arc thread=1 sequence=7
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_8_606k8n7x_.arc thread=1 sequence=8
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_9_606lkvgy_.arc thread=1 sequence=9
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_10_606mfskg_.arc thread=1 sequence=10
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_11_606ndqvj_.arc thread=1 sequence=11
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_12_606szwxo_.arc thread=1 sequence=12
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_13_606t3zs8_.arc thread=1 sequence=13
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2010_05_31/o1_mf_1_14_606t777d_.arc thread=1 sequence=14
media recovery complete, elapsed time: 00:00:06
Finished recover at 31-MAY-10
RMAN> alter database open resetlogs;
database opened.
SQL> select status from v$instance;
STATUS
————
OPEN
SQL> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
———- ———- —————-
1 2 INACTIVE
2 2 CURRENT
3 2 UNUSED
Recover from loss of active redo log file.
- shutdown the database
| SHUTDOWN IMMEDIATE; |
- mount the database
| STARTUP MOUNT; |
| select group#, sequence#, bytes, first_change#, to_char(first_time,’DD-MM-YY hh24:mi:ss’) tim, status from v$log; |
| RECOVER DATABASE UNTIL TIME ‘yyyy-mm-dd:hh24:mi:ss’; |
‘ss’ in the seconds should be 1 second less than the first_time of the missing redo log file. - open the database with resetlogs option
1 | ALTER DATABASE OPEN RESETLOGS; |
No comments:
Post a Comment