Redo log file recovery scenarios
What is an Online Redo Log File
The most crucial structure for recovery operations is the Online redo log file, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an OracleDatabase has an associated redo log to protect the database in case of an instance failure.
In most cases, an online redo log file will exist in any of the following states
- current -> log writer pointing to the log file.
- Active -> an online redo log file which is needed for recovery, or redo log file with data not written to archive log
- inactive -> an online redo log file which is not needed for recovery
So, we will discuss the steps to perform to recover online redo log file which is lost with any of the above states.
Check the current logfiles in a database using v$logfile
|
1 2 3 4 5 6 7 8 9 |
SYS> SYS> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/TEST/redo03.log /u01/TEST/redo02.log /u01/TEST/redo01.log |
How to add a logfile member to a group
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SYS> SYS> alter database add logfile member '/u01/TEST/redo01_02.log' to group 1; Database altered. SYS> alter database add logfile member '/u01/TEST/redo02_02.log' to group 2; Database altered. SYS> alter database add logfile member '/u01/TEST/redo03_02.log' to group 3; Database altered. |
How to drop a logfile member from a group
|
1 2 3 4 |
SYS> SYS> alter database drop logfile member '/u01/TEST/redo02_02.log'; Database altered. |
How to drop a logfile group
|
1 2 3 4 |
SYS> SYS> alter database drop logfile group 2; Database altered. |
How to add a logfile group
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SYS> SYS> alter database add logfile member '/u01/TEST/redo04_01.log' to group 4; Database altered. SYS> alter database add logfile group 5 '/u01/TEST/redo05.log' size 100M; Database altered. SYS> alter database add logfile member '/u01/TEST/redo05_01.log' to group 5; Database altered. |
How to recover loss of Member of the Inactive Redo Log group
|
1 2 3 4 |
SQL> alter database clear unarchived logfile group 1; Database altered. SQL> |
How to recover loss of Member of the Current redo log group
Step 1: get the last archive log number generated and execute the below command
|
1 2 3 4 5 6 |
run { set until sequence 16; restore database; recover database; alter database open resetlogs; } |
Here, 16 is the archive log number.
Summary
[table id=2 /]
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.