Oracle RMAN 11g Backup and Recovery (102 page)

294
Part II: Setup Principles and Practices

The first two types of online redo log loss are an annoyance at best. The last two categories can be catastrophic with respect to data loss.

Recall that the redo logs are written to as soon as there is a commit or as soon as the online redo log buffer is filled to a certain size (and other events can cause writes, too). As a result, uncommitted undo, along with committed changes, can be written to the online redo logs.

Because the database datafiles are written to later, sometimes much later, the database datafiles are often way out of synchronization with the actual current state of the database. Due to this lack of synchronization between the actual state of the database data and the data contained in the database datafiles, Oracle will have to apply redo from the online redo logs during database recoveries.

Since database datafiles are often out of synch with the actual state of the database, loss of an
active online redo log
can result in loss of data. Loss of the current online redo log can also result in data loss. Obviously, redo logs are quite important. The current, active, and inactive redo logs differ as follows:

■
Active
This is a online redo log that is not currently in use. However, it contains redo that still needs to be written to the datafiles, and the group may (or may not) still need to be archived.

■
Current
This is the current online redo log group. Oracle is actively writing to this online redo log group.

■
Inactive
This online redo log is not currently in use, and redo has been written to datafiles 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 our redo log groups.

Loss of an Inactive Online Redo Log Group Member

To recover from the loss of one or more members of an online redo log group (but not the entire group), the response is pretty easy. You can simply re-create the member by using the
alter
database add logfile member
command. You might discover that you have lost a member via an alert in OEM or in the alert log that might look something like this: ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: 'C:\ORACLE\ORADATA\DANCE\REDO01a.LOG'

As a best practice, we’d recommend that if the database has not shut down and the member is part of an active or current redo log group, you immediately attempt to checkpoint the database by using the
alter system checkpoint
command. The
alter system checkpoint
command will force the database to write any dirty blocks from the database buffer cache to the database datafiles in an urgent manner. This can help protect your database against data loss should the database crash because of this missing online redo log.

Once the checkpoint has completed, we would issue the
alter database add logfile
command to re-create the redo log group member redo02a.log:

SQL>Alter database add logfile 'C:\ORACLE\ORADATA\DANCE\REDO02a.LOG' reuse to group 2;

Chapter 12: RMAN Restore and Recovery
295

If the database happened to crash before you could add the logfile, you would mount the database, and then issue the
alter database add logfile
command. You should then be able to open the database.

Another option that is available is that you could shut down the database in a consistent manner (
shutdown normal
) and then copy another member of the redo log group to the missing member. You can then restart the database normally.

NOTE

We strongly advise against using a
shutdown abort
anytime you are
dealing with the loss of an online redo log group or a member of such
a group.

Loss of an Inactive Online Redo Log Group

Loss of an
inactive online redo log group
is a very survivable event and is easy to recover from.

You will need to understand two different situations that might occur. First is loss of an inactive online redo log group during database startup. Second is loss of an inactive online redo log group during database operations. In the next two sections, we will address these situations.

Loss of an Inactive Online Redo Log Group on Startup

If you start up the database and the inactive online redo log group cannot be opened, you will get the following error message:

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: 'C:\ORACLE\ORADATA\DANCE\REDO02a.LOG'

First, determine if one of the online redo log group members has survived. If so, follow the steps listed in the previous section on recovering from the loss of an online redo log member. If none of the members survived, then you will need to drop the logfile group by using the
alter
database
command as seen here:

SQL> alter database drop logfile group 2;

Once the online redo log group is dropped, you simply re-create the online redo log group by using the
alter database add logfile
command:

SQL> Alter database add logfile

2 group 2 'c:\oracle\oradata\dance\redo02a.log' size 50m;

Loss of an Inactive Online Redo Log Group When the Database Is Running
If you have lost an inactive online redo log group (or it becomes corrupted) while the database is running, it is possible that the database will continue to operate. Oracle Database will sometimes skip the online redo log group that went missing and continue to operate normally. If this occurs, you can issue an
alter system checkpoint
command, and then clear the logfile group with the
alter database clear logfile group
command, as seen here:

SQL>alter system checkpoint;

SQL>alter database clear logfile group 1;

296
Part II: Setup Principles and Practices

If the logfile you are trying to clear has not been archived, you may get the following error: SQL> alter database clear logfile group 1;

alter database clear logfile group 1

*

ERROR at line 1:

ORA-00350: log 1 of instance orcl (thread 1) needs to be archived

ORA-00312: online log 1 thread 1: '/oracle01/oradata/dance/redo01.log'

Of course, since the logfile is not there, it cannot be archived. In this case, we use the
alter
database clear unarchived logfile
command to clear the unarchived log file and rebuild the log file in its current location, as seen here:

SQL> alter database clear unarchived logfile '/oracle01/oradata/dance/redo01.log'; You will need to back up your database in this case, since an archived redo log will have been lost.

In some cases, the database will not crash, but will freeze. If this occurs, open another SQL*Plus session and connect to the database. Then issue the
alter database checkpoint
command followed by either the
alter database clear logfile
or the
alter database clear
unarchived logfile
command, depending on the type of recovery required. After you issue these commands, the database should operate as usual.