Performing Oracle User-Managed Database Recoveries - OCP: Oracle Database 12c Administrator Certified Professional - page 86

Recall that the redo logs are written to as soon as there is a commit (other events can
cause writes too). Remember also that the database data files are written to later, some-
times much later. Thus, the database data files are often way out of synchronization with
the actual current state of the database. If the database crashes, then often the database
data files are not up to date, and this forces Oracle to apply redo to get them current when
you start up the database. Normally, Oracle will do this automatically in a process called
instance recovery .
As a result of the fact that the database data files are often out of synch with the actual
state of the database, loss of an active or the current online redo log group can be disastrous.
Loss of an active online redo log can result in loss of data. Loss of the current online redo
log will likely result in data loss, but this is not always the case. Thus, redo logs are quite
important. You may wonder what the difference between the current, active, and inactive
redo logs is:
Current Current online redo log group.
Active Not currently in use but the dirty blocks associated with the redo in the log file still
need to be written to the data files by DBWR. Also, the group may still need to be archived.
Inactive Not currently in use and dirty blocks associated with the redo in the log file have
been written to data files by DBWR.
You can see the status of an online redo log group by querying the STATUS column of the
V$LOG view. Let's look at what to do when it comes to recovering from loss of redo log groups.
Dealing with the Loss of an Inactive Online Redo Log
Group Member
If you have lost one or more members of an online redo log group (but not the entire
group), then the response is pretty easy. You can simply recreate the member using the
ALTER DATABASE ADD LOGFILE MEMBER command. For example, you might see this error
in the alert log:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: 'C:\ORACLE\ORADATA\ORCL\REDO02.LOG'
If the database has not shut down, you should immediately attempt to checkpoint the
database using the ALTER SYSTEM CHECKPOINT command. The ALTER SYSTEM CHECKPOINT
command forces the database to write any dirty blocks from the database buffer cache to
the database data files in an urgent manner. This will be helpful in the event the database
crashes because of this missing online redo log.
Once the checkpoint has completed, you would issue the ALTER DATABASE ADD LOGFILE
command to recreate the redo log group member redo02.log :
SQL>alter database add logfile member
'C:\ORACLE\ORADATA\ORCL\REDO02.LOG' reuse to group 2;