According to standard practice, we should consider multiplexing of online redo log files to avoid such a scenarios, Each log file group should have more than/at least 2 log file members & location of all group on different physical disk. ( In case of worst situation with disk 1 then database would be recovery with the help of disk 2 – Online redo log file ) Single current online redo log file is sufficient to restore the entire database & do an incomplete recovery.
Please consider following hands-on to demonstrate recovery of loss of online redolog files by deleting all the online redo log files at the OS level:
At SQL prompt, Ensure the online redo log members by issuing the following query:
SQL> select member from v$Logfile;
MEMBER
——————————————————————————–
/home/oracle/app/oracle/oradata/orcl/redo03.log
/home/oracle/app/oracle/oradata/orcl/redo02.log
/home/oracle/app/oracle/oradata/orcl/redo01.log
Let’s Delete/Remove all online redo log file to simulate mentioned scenario:
[root@oracle orcl]# pwd
/home/oracle/app/oracle/oradata/orcl
[root@oracle orcl]# mv redo01.log redo01.log.back
[root@oracle orcl]# mv redo02.log redo02.log.back
[root@oracle orcl]# mv redo03.log redo03.log.back
In case of current online redo log file is lost, the database will be no more in use & aleartlog file shows oracle error: ORA-00313, ORA-00312, ORA-27037 as below:
Note: We are monitoring alert log message with the help of ADRCI Prompt.
[oracle@oracle ~]$ adrci
ADRCI: Release 11.2.0.1.0 – Production on Mon Jan 20 07:30:12 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = “/home/oracle/app/oracle”
adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl
diag/rdbms/catalogdb/catalogdb
diag/tnslsnr/centos/listener
diag/tnslsnr/oracle/listener
adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail -f
2014-01-20 07:35:21.744000 +00:00
Thread 1 advanced to log sequence 44 (LGWR switch)
Current log# 2 seq# 44 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
2014-01-20 07:35:22.847000 +00:00
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_10148.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/home/oracle/app/oracle/oradata/orcl/redo01.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
-X-
With the help of RMAN, we can recover from this situation by restoring the database from the RMAN backup. ( Up-to last available archived redo logfile )
Ensure current sequence of online redolog file by issuing the following sql command:
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———
1 1 43 52428800 512 1 NO INACTIVE 1877247 20-JAN-14 1881537 20-JAN-14
2 1 44 52428800 512 1 NO CURRENT 1881537 20-JAN-14 2.8147E+14
3 1 42 52428800 512 1 YES INACTIVE 1863081 19-JAN-14 1877247 20-JAN-14
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 42
Next log sequence to archive 43
Current log sequence 44
Shutdown target database:
SQL> shutdown immediate;
Startup database in mount mode.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 197135328 bytes
Database Buffers 104857600 bytes
Redo Buffers 4775936 bytes
Database mounted.
Connect to the target database using RMAN with the help of recovery owner as below:
Use following RMAN commands to recover all online redo log file members:
[oracle@oracle ~]$ rman target / catalog recoveryman/recoveryman@catalogdb
RMAN> run { set until sequence 43; restore database; recover database; alter database open resetlogs; }
executing command: SET until clause
Starting restore at 20-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/data/user1.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_13/o1_mf_nnndf_TAG20140113T101908_9f7hdwz0_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_13/o1_mf_nnndf_TAG20140113T101908_9f7hdwz0_.bkp tag=TAG20140113T101908
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 20-JAN-14
Starting recover at 20-JAN-14
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 27 is already on disk as file /home/oracle/arch/1_27_835799980.dbf
archived log for thread 1 with sequence 28 is already on disk as file /home/oracle/arch/1_28_835799980.dbf
archived log for thread 1 with sequence 29 is already on disk as file /home/oracle/arch/1_29_835799980.dbf
archived log for thread 1 with sequence 30 is already on disk as file /home/oracle/arch/1_30_835799980.dbf
archived log for thread 1 with sequence 31 is already on disk as file /home/oracle/arch/1_31_835799980.dbf
archived log for thread 1 with sequence 32 is already on disk as file /home/oracle/arch/1_32_835799980.dbf
archived log for thread 1 with sequence 33 is already on disk as file /home/oracle/arch/1_33_835799980.dbf
archived log for thread 1 with sequence 34 is already on disk as file /home/oracle/arch/1_34_835799980.dbf
archived log for thread 1 with sequence 35 is already on disk as file /home/oracle/arch/1_35_835799980.dbf
archived log for thread 1 with sequence 36 is already on disk as file /home/oracle/arch/1_36_835799980.dbf
archived log for thread 1 with sequence 37 is already on disk as file /home/oracle/arch/1_37_835799980.dbf
archived log for thread 1 with sequence 38 is already on disk as file /home/oracle/arch/1_38_835799980.dbf
archived log for thread 1 with sequence 39 is already on disk as file /home/oracle/arch/1_39_835799980.dbf
archived log for thread 1 with sequence 40 is already on disk as file /home/oracle/arch/1_40_835799980.dbf
archived log for thread 1 with sequence 41 is already on disk as file /home/oracle/arch/1_41_835799980.dbf
archived log for thread 1 with sequence 42 is already on disk as file /home/oracle/arch/1_42_835799980.dbf
archived log file name=/home/oracle/arch/1_27_835799980.dbf thread=1 sequence=27
archived log file name=/home/oracle/arch/1_28_835799980.dbf thread=1 sequence=28
archived log file name=/home/oracle/arch/1_29_835799980.dbf thread=1 sequence=29
archived log file name=/home/oracle/arch/1_30_835799980.dbf thread=1 sequence=30
archived log file name=/home/oracle/arch/1_31_835799980.dbf thread=1 sequence=31
archived log file name=/home/oracle/arch/1_32_835799980.dbf thread=1 sequence=32
archived log file name=/home/oracle/arch/1_33_835799980.dbf thread=1 sequence=33
archived log file name=/home/oracle/arch/1_34_835799980.dbf thread=1 sequence=34
archived log file name=/home/oracle/arch/1_35_835799980.dbf thread=1 sequence=35
archived log file name=/home/oracle/arch/1_36_835799980.dbf thread=1 sequence=36
archived log file name=/home/oracle/arch/1_37_835799980.dbf thread=1 sequence=37
archived log file name=/home/oracle/arch/1_38_835799980.dbf thread=1 sequence=38
archived log file name=/home/oracle/arch/1_39_835799980.dbf thread=1 sequence=39
archived log file name=/home/oracle/arch/1_40_835799980.dbf thread=1 sequence=40
archived log file name=/home/oracle/arch/1_41_835799980.dbf thread=1 sequence=41
archived log file name=/home/oracle/arch/1_42_835799980.dbf thread=1 sequence=42
media recovery complete, elapsed time: 00:02:17
Finished recover at 20-JAN-14
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>
Database has been open successfully with creation of all online redo log file members.
Ensure online redo log-files has been generated through RMAN recovery at OS level.
Since we did incomplete recovery, we should consider new/fresh backup after this activity.
Please consider following hands-on to demonstrate recovery of loss of online redolog files by deleting all the online redo log files at the OS level:
At SQL prompt, Ensure the online redo log members by issuing the following query:
SQL> select member from v$Logfile;
MEMBER
——————————————————————————–
/home/oracle/app/oracle/oradata/orcl/redo03.log
/home/oracle/app/oracle/oradata/orcl/redo02.log
/home/oracle/app/oracle/oradata/orcl/redo01.log
Let’s Delete/Remove all online redo log file to simulate mentioned scenario:
[root@oracle orcl]# pwd
/home/oracle/app/oracle/oradata/orcl
[root@oracle orcl]# mv redo01.log redo01.log.back
[root@oracle orcl]# mv redo02.log redo02.log.back
[root@oracle orcl]# mv redo03.log redo03.log.back
In case of current online redo log file is lost, the database will be no more in use & aleartlog file shows oracle error: ORA-00313, ORA-00312, ORA-27037 as below:
Note: We are monitoring alert log message with the help of ADRCI Prompt.
[oracle@oracle ~]$ adrci
ADRCI: Release 11.2.0.1.0 – Production on Mon Jan 20 07:30:12 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = “/home/oracle/app/oracle”
adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl
diag/rdbms/catalogdb/catalogdb
diag/tnslsnr/centos/listener
diag/tnslsnr/oracle/listener
adrci> set home diag/rdbms/orcl/orcl
adrci> show alert -tail -f
2014-01-20 07:35:21.744000 +00:00
Thread 1 advanced to log sequence 44 (LGWR switch)
Current log# 2 seq# 44 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
2014-01-20 07:35:22.847000 +00:00
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_10148.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: ‘/home/oracle/app/oracle/oradata/orcl/redo01.log’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
-X-
With the help of RMAN, we can recover from this situation by restoring the database from the RMAN backup. ( Up-to last available archived redo logfile )
Ensure current sequence of online redolog file by issuing the following sql command:
SQL> select * from v$Log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———
1 1 43 52428800 512 1 NO INACTIVE 1877247 20-JAN-14 1881537 20-JAN-14
2 1 44 52428800 512 1 NO CURRENT 1881537 20-JAN-14 2.8147E+14
3 1 42 52428800 512 1 YES INACTIVE 1863081 19-JAN-14 1877247 20-JAN-14
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch
Oldest online log sequence 42
Next log sequence to archive 43
Current log sequence 44
Shutdown target database:
SQL> shutdown immediate;
Startup database in mount mode.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 308981760 bytes
Fixed Size 2212896 bytes
Variable Size 197135328 bytes
Database Buffers 104857600 bytes
Redo Buffers 4775936 bytes
Database mounted.
Connect to the target database using RMAN with the help of recovery owner as below:
Use following RMAN commands to recover all online redo log file members:
[oracle@oracle ~]$ rman target / catalog recoveryman/recoveryman@catalogdb
RMAN> run { set until sequence 43; restore database; recover database; alter database open resetlogs; }
executing command: SET until clause
Starting restore at 20-JAN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/data/user1.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_13/o1_mf_nnndf_TAG20140113T101908_9f7hdwz0_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2014_01_13/o1_mf_nnndf_TAG20140113T101908_9f7hdwz0_.bkp tag=TAG20140113T101908
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 20-JAN-14
Starting recover at 20-JAN-14
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 27 is already on disk as file /home/oracle/arch/1_27_835799980.dbf
archived log for thread 1 with sequence 28 is already on disk as file /home/oracle/arch/1_28_835799980.dbf
archived log for thread 1 with sequence 29 is already on disk as file /home/oracle/arch/1_29_835799980.dbf
archived log for thread 1 with sequence 30 is already on disk as file /home/oracle/arch/1_30_835799980.dbf
archived log for thread 1 with sequence 31 is already on disk as file /home/oracle/arch/1_31_835799980.dbf
archived log for thread 1 with sequence 32 is already on disk as file /home/oracle/arch/1_32_835799980.dbf
archived log for thread 1 with sequence 33 is already on disk as file /home/oracle/arch/1_33_835799980.dbf
archived log for thread 1 with sequence 34 is already on disk as file /home/oracle/arch/1_34_835799980.dbf
archived log for thread 1 with sequence 35 is already on disk as file /home/oracle/arch/1_35_835799980.dbf
archived log for thread 1 with sequence 36 is already on disk as file /home/oracle/arch/1_36_835799980.dbf
archived log for thread 1 with sequence 37 is already on disk as file /home/oracle/arch/1_37_835799980.dbf
archived log for thread 1 with sequence 38 is already on disk as file /home/oracle/arch/1_38_835799980.dbf
archived log for thread 1 with sequence 39 is already on disk as file /home/oracle/arch/1_39_835799980.dbf
archived log for thread 1 with sequence 40 is already on disk as file /home/oracle/arch/1_40_835799980.dbf
archived log for thread 1 with sequence 41 is already on disk as file /home/oracle/arch/1_41_835799980.dbf
archived log for thread 1 with sequence 42 is already on disk as file /home/oracle/arch/1_42_835799980.dbf
archived log file name=/home/oracle/arch/1_27_835799980.dbf thread=1 sequence=27
archived log file name=/home/oracle/arch/1_28_835799980.dbf thread=1 sequence=28
archived log file name=/home/oracle/arch/1_29_835799980.dbf thread=1 sequence=29
archived log file name=/home/oracle/arch/1_30_835799980.dbf thread=1 sequence=30
archived log file name=/home/oracle/arch/1_31_835799980.dbf thread=1 sequence=31
archived log file name=/home/oracle/arch/1_32_835799980.dbf thread=1 sequence=32
archived log file name=/home/oracle/arch/1_33_835799980.dbf thread=1 sequence=33
archived log file name=/home/oracle/arch/1_34_835799980.dbf thread=1 sequence=34
archived log file name=/home/oracle/arch/1_35_835799980.dbf thread=1 sequence=35
archived log file name=/home/oracle/arch/1_36_835799980.dbf thread=1 sequence=36
archived log file name=/home/oracle/arch/1_37_835799980.dbf thread=1 sequence=37
archived log file name=/home/oracle/arch/1_38_835799980.dbf thread=1 sequence=38
archived log file name=/home/oracle/arch/1_39_835799980.dbf thread=1 sequence=39
archived log file name=/home/oracle/arch/1_40_835799980.dbf thread=1 sequence=40
archived log file name=/home/oracle/arch/1_41_835799980.dbf thread=1 sequence=41
archived log file name=/home/oracle/arch/1_42_835799980.dbf thread=1 sequence=42
media recovery complete, elapsed time: 00:02:17
Finished recover at 20-JAN-14
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>
Database has been open successfully with creation of all online redo log file members.
Ensure online redo log-files has been generated through RMAN recovery at OS level.
Since we did incomplete recovery, we should consider new/fresh backup after this activity.
No comments:
Post a Comment