If a media failure has affected the online redo logs of a
database, then the appropriate recovery procedure depends on the following:
·
The configuration of the online redo log:
mirrored or non-mirrored
·
The type of media failure: temporary or
permanent
·
The types of online redo log files affected by
the media failure: current, active, unarchived, or inactive
Table
19-1 displays V$LOG status information that
can be crucial in a recovery situation involving online redo logs.
Table 19-1
STATUS Column of V$LOG
[1] Recovering
After Losing a Member of a Multiplexed Online Redo Log Group
If the online
redo log of a database is multiplexed, and if at least one member of each
online redo log group is not affected by the media failure, then the database
continues functioning as normal, but error messages are written to the log
writer trace file and the alert_SID.log of the
database.
Solve the problem by taking one of the following actions:
·
If the hardware problem is temporary, then
correct it. The log writer process accesses the previously unavailable online
redo log files as if the problem never existed.
·
If the hardware problem is permanent, then drop
the damaged member and add a new member by using the following procedure.
Note:
The newly added member
provides no redundancy until the log group is reused.
Locate the filename of the
damaged member in V$LOGFILE.
The status is INVALID if the file is
inaccessible:
SELECT GROUP#, STATUS,
MEMBER
FROM V$LOGFILE
WHERE STATUS='INVALID';
GROUP# STATUS
MEMBER
------- -----------
---------------------
0002
INVALID
/oracle/oradata/trgt/redo02.log
Drop the damaged member.
For example, to drop member redo01.log from group
2, issue:
ALTER DATABASE DROP LOGFILE MEMBER
'/oracle/oradata/trgt/redo02.log';
Add a new member to the
group. For example, to add redo02.log to group 2, issue:
ALTER DATABASE ADD
LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'
TO GROUP
2;
If the file you want to add
already exists, then it must be the same size as the other group members, and
you must specify REUSE. For example:
ALTER DATABASE ADD
LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'
REUSE TO
GROUP 2;
[2] Recovering
After the Loss of All Members of an Online Redo Log Group
If a media failure damages all
members of an online redo log group, then different scenarios can occur
depending on the type of online redo log group affected by the failure and the
archiving mode of the database.
If the damaged log group is active, then it is needed for
crash recovery; otherwise, it is not.
[3] Your
first task is to determine whether the damaged group is active or inactive.
Locate the filename of the
lost redo log in V$LOGFILE
and then look for the group number corresponding to it. For example,
enter:
SELECT GROUP#, STATUS,
MEMBER FROM V$LOGFILE;
GROUP# STATUS
MEMBER
------- -----------
---------------------
0001 /oracle/dbs/log1a.f
0001 /oracle/dbs/log1b.f
0002 INVALID /oracle/dbs/log2a.f
0002 INVALID /oracle/dbs/log2b.f
0003 /oracle/dbs/log3a.f
0003 /oracle/dbs/log3b.f
Determine which groups are
active. For example, enter:
SELECT GROUP#, MEMBERS,
STATUS, ARCHIVED
FROM V$LOG;
GROUP# MEMBERS STATUS ARCHIVED
------ ------- --------- -----------
0001
2 INACTIVE YES
0002
2 ACTIVE NO
0003 2 CURRENT NO
If the affected group is
inactive, follow the procedure in Losing
an Inactive Online Redo Log Group. If the affected group is active (as in
the preceding example), then follow the procedure in "Losing
an Active Online Redo Log Group".
Losing an Inactive Online Redo Log Group
If all members of an online redo log group with INACTIVE status are damaged, then the procedure depends
on whether you can fix the media problem that damaged the inactive redo log
group.
[4] Clearing
Inactive, Archived Redo
You can clear an inactive redo log group when the database
is open or closed. The procedure depends on whether the damaged group has been
archived.
To clear an inactive, online redo
log group that has been archived, use the following procedure:
If the database is shut
down, then start a new instance and mount the database:
STARTUP MOUNT
Reinitialize the damaged
log group. For example, to clear redo log group 2,
issue the following statement:
ALTER DATABASE CLEAR LOGFILE GROUP 2;
[5] Clearing
Inactive, Not-Yet-Archived Redo
Clearing a not-yet-archived redo log allows it to be
reused without archiving it. This action makes backups unusable if they were
started before the last change in the log, unless the file was taken offline
prior to the first change in the log. Hence, if you need the cleared log file
for recovery of a backup, then you cannot recover that backup. Also, it
prevents complete recovery from backups due to the missing log.
To clear an inactive, online redo
log group that has not been archived, use the following procedure:
If the database is shut
down, then start a new instance and mount the database:
STARTUP MOUNT
Clear the log using the UNARCHIVED keyword. For example, to clear log group 2, issue:
ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;
If there is an offline
datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE
are required. The datafile and its entire tablespace have to be dropped because
the redo necessary to bring it online is being cleared, and there is no copy of
it. For example, enter:
ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2
UNRECOVERABLE DATAFILE;
Immediately back up the
whole database with an operating system utility, so that you have a backup you
can use for complete recovery without relying on the cleared log group. For
example, enter:
% cp /disk1/oracle/dbs/*.f /disk2/backup
Back up the database's
control file with the ALTER DATABASE statement. For example, enter:
ALTER DATABASE BACKUP CONTROLFILE TO
'/oracle/dbs/cf_backup.f';
[6] Failure
of CLEAR LOGFILE Operation
The ALTER DATABASE CLEAR LOGFILE statement can fail with an I/O error due to
media failure when it is not possible to:
·
Relocate the redo log file onto alternative media
by re-creating it under the currently configured redo log filename
·
Reuse the currently configured log filename to
re-create the redo log file because the name itself is invalid or unusable (for
example, due to media failure)
In these cases, the ALTER DATABASE CLEAR LOGFILE statement (before receiving the I/O error)
would have successfully informed the control file that the log was being
cleared and did not require archiving. The I/O error occurred at the step in
which the CLEAR LOGFILE
statement attempts to create the new redo log file and write zeros to it. This
fact is reflected in V$LOG.CLEARING_CURRENT.
[7] [8] Losing
an Active Online Redo Log Group
If the database is still running and the lost active redo
log is not the current log, then issue the ALTER SYSTEM CHECKPOINT
statement. If successful, then the active redo log becomes inactive, and you
can follow the procedure in "Losing
an Inactive Online Redo Log Group". If unsuccessful, or if your
database has halted, then perform one of procedures in this section, depending
on the archiving mode.
The current log is the one LGWR is currently writing to.
If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this
case, you must restore a backup, perform incomplete recovery, and open the
database with the RESETLOGS option.
To recover from loss of an active online log group in
NOARCHIVELOG mode:
If the media failure is temporary, then correct the problem
so that the database can reuse the group when required.
Restore the database from a
consistent, whole database backup (datafiles and control files) as described in
"Restoring
Datafiles Before Performing Incomplete Recovery". For example, enter:
% cp /disk2/backup/*.dbf
$ORACLE_HOME/oradata/trgt/
Mount the database:
STARTUP MOUNT
Because online redo logs
are not backed up, you cannot restore them with the datafiles and control
files. In order to allow the database to reset the online redo logs, you must
first mimic incomplete recovery:
RECOVER DATABASE UNTIL
CANCEL
CANCEL
Open the database using the
RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;
Shut down the database
consistently. For example, enter:
SHUTDOWN IMMEDIATE
Make a whole database
backup.
To recover from loss of an active online redo log group in
ARCHIVELOG mode:
If the media
failure is temporary, then correct the problem so that the database can reuse
the group when required. If the media failure is not temporary, then use the
following procedure.
Begin incomplete media
recovery, recovering up through the log before the damaged log.
Ensure that the current
name of the lost redo log can be used for a newly created file. If not, then
rename the members of the damaged online redo log group to a new location. For
example, enter:
ALTER DATABASE RENAME
FILE "?/oradata/trgt/redo01.log" TO "/tmp/redo01.log";
ALTER DATABASE RENAME FILE "?/oradata/trgt/redo01.log"
TO "/tmp/redo02.log";
Open the database using the
RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;
Note:
All updates executed from
the endpoint of the incomplete recovery to the present must be re-executed.
[9] Loss
of Multiple Redo Log Groups
If you have lost multiple groups of
the online redo log, then use the recovery method for the most difficult log to
recover. The order of difficulty, from most difficult to least difficult,
follows:
The current online redo log
An active online redo log
An unarchived online redo
log
An inactive online redo log
[10] [11] [12] Recovering
After the Loss of Archived Redo Log Files: Scenario
If the database is operating in ARCHIVELOG
mode, and if the only copy of an archived redo log file is damaged, then the
damaged file does not affect the present operation of the database. The
following situations can arise, however, depending on when the redo log was
written and when you backed up the datafile.
If you know that an archived redo log group has been
damaged, immediately back up all datafiles so that you will have a whole
database backup that does not require the damaged archived redo log.
[14] Recovering
from a Dropped Table: Scenario
One
not-uncommon error is the accidental dropping of a table from your database. In
general, the fastest and simplest solution is to use the flashback drop
feature, described inOracle
Database Backup and Recovery Basics, to reverse the dropping of the table.
However, if for some reason, such as flashback drop being disabled or the table
having been dropped with the PURGE option, you cannot use flashback table, you
can create a copy of the database, perform point-in-time recovery of that copy
to a time before the table was dropped, export the dropped table using an
Oracle export utility, and re-import it into your primary database using an
Oracle import utility.
In this scenario, assume that you do not have the
flashback database functionality enabled, so FLASHBACK
DATABASE is not an option, but you do have
physical backups of the database.
Note:
Grant powerful privileges (such as DROP
ANY TABLE) only to
only selected, appropriate users, to minimize user errors that require database
recovery.
To recover a
table that has been accidentally dropped, use the following procedure:
If possible, keep the
database that experienced the user error online and available for use. Back up
all datafiles of the existing database in case an error is made during the
remaining steps of this procedure.
Restore a database backup
to an alternate location, then perform incomplete recovery of this backup using
a restored backup control file, to the point just before the table was dropped.
Export the lost data from
the temporary, restored version of the database using an Oracle export utility.
In this case, export the accidentally dropped table.
Note:
System audit options are
exported.
Use an Oracle import
utility to import the data back into the production database.
Delete the files of the
temporary copy of the database to conserve space.
No comments:
Post a Comment