| Oracle® Database Backup and Recovery Advanced User's
Guide 10g Release 1 (10.1) Part Number B10734-01 |
|
|
<
td align="center" valign="top"> Previous  Next |
| Oracle® Database Backup and Recovery Advanced User's
Guide 10g Release 1 (10.1) Part Number B10734-01 |
|
|
<
td align="center" valign="top">Previous Next |
This chapter describes how to recover from common media failures, and includes the following topics:
If either a permanent or temporary media failure affects any datafiles of a database operating in NOARCHI
VELOG mode, then the database automatically shuts down.
If the media failure is temp orary, correct the underlying problem and restart the database. Usually, crash recovery will recover all committed transactions from the online redo log. If the media failure is permanent, then restore the database as described in "Reco vering a Database in NOARCHIVELOG Mode".
If either a permanent or temporary media failure affects the datafiles of a database operating
in ARCHIVELOG mode, then the following scenarios can occur.
| Damaged Datafi les | Database Status | Solution |
|---|---|---|
|
Datafiles in the |
Database shuts down . |
If the hardware problem is temporary, then fix it and restart t he database. Usually, crash recovery recovers lost transactions. If the hardware problem is permanent, then recover the database as d escribed in "Performing Closed Database Recovery". |
|
Datafiles not in the |
Affected datafiles are taken offline, but the database stays open. |
If the unaffected portions of the database must remain available, then do not shut down the database. T ake tablespaces containing problem datafiles offline using the temporary option, then recover them as described in "Performing Datafile Recovery in an Open Database". |
If database recovery with a backup control file rolls forward through a CREATE TABLESPACE or
an ALTER TABLESPACE ADD DATAFILE operation, then the database stops recovery whe
n applying the redo record for the added files and lets you confirm the filenames.
For exam ple, suppose the following sequence of events occurs:
/oracle/oradata/trgt/test01.dbf and /oracle/oradata/trgt/test02.dbf.CREATE TABLESPACE operation.You may see the following error when applyin
g the CREATE TABLESPACE redo data:
ORA-00283: recovery session ca nceled due to errors ORA-01244: unnamed datafile(s) added to controlfile by media recovery < /a>ORA-01110: data file 11: '/oracle/oradata/trgt/test02.dbf' ORA-01110: data file 10: '/oracle/oradata/trgt/te st01.dbf'
To recover through an ADD DATAFILE operation:
V$DATAFILE. For example:
SELECT FILE#,NAME FROM V$DATAFILE; FILE# NAME --------------- ------------ ---------- 1 /oracle/oradata/trgt/system01.dbf . . . 10 /oracle/oradata/trgt/UNNAMED00001 11 /oracle/oradata/trgt/UNNAMED00002
ALTER DATABASE RENAME FILE statement to rename the datafiles. For exa
mple, enter:
ALTER DATABASE RENAME FILE '/db/UNNAMED00001' TO '/oracle/oradata/trgt/test01.dbf'; ALTER DATABASE RENAME FILE '/db/UNNAMED00002' TO '/oracle/oradata/trgt/test02.dbf';
RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
If a datafile is damaged and no backup of the file is available, then you can still recover the datafile if:
To re-create a datafile for recovery:
?/oradata/trgt/users01.dbf has been damaged, and no backup is available. The following s
tatement re-creates the original datafile (same size) on disk2:
ALTER DATABASE C REATE DATAFILE '?/oradata/trgt/users01.dbf' AS '/disk2/users01.dbf';
This statement creates an empty file that is the same size as the lost file . The database looks at information in the control file and the data dictionary to obtain size information. The old datafile is renam ed as the new datafile.
RECOVER DATAFILE '/disk2/users01.dbf'
You can recover backups through an O
PEN RESETLOGS so long as:
If you need to re-create the control file, th
e trace file generated by ALTER DATABASE BACKUP CONTROLFILE TO TRACE will contain the necessary commands to re-construct the complete incarnation history. The V$DATABASE_INCARNATION<
/code> view displays the RESETLOGS history known to the control file, while the V$LOG_HISTORY view displays the archived
log history.
It is possible for the incarnation history to be incomplete in the in re-crea
ted control file. For example, archived logs necessary for recovery may be missing. In this case, it is possible to create incarnatio
n records explicitly with the ALTER DATABASE REGISTER LOGFILE statement.
In the following example, you register four logs that are necessary for recovery but are not record ed in the re-created control file, and then recover the database:
ALTER DATABASE REGISTER L OGFILE '?/oradata/trgt/arch/arcr_1_1_42343523.arc'; ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1 _1_34546466.arc'; ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_23435466.arc'; ALTER DATABASE REGISTER LOGFILE '?/oradata/trgt/arch/arcr_1_1_12343533.arc'; RECOVER AUTOMATIC DATABASE;
You can create tables an
d indexes with the CREATE TABLE AS SELECT statement. You can also specify that th
e database create them with the NOLOGGING option. When you create a table or index as NOLOGGING, the databa
se does not generate redo log records for the operation. Thus, you cannot recover objects created with NOLOGGING, even i
f you are running in ARCHIVELOG mode.
|
Note: If you cannot afford to lose tables or indexes created with |
Be aware that when you perform media recovery, and some tables or indexes are created normally whereas ot
hers are created with the NOLOGGING option, the NOLOGGING objects are marked logically corrupt by the RECOVER operation. Any attempt to access the unrecoverable objects returns an ORA-01578 error message. Drop the
NOLOGGING objects and re-create them if needed.
Because it is possible to crea
te a table with the NOLOGGING option and then create an index with the LOGGING option on that table, the in
dex is not marked as logically corrupt after you perform media recovery. The table was unrecoverable (and thus marked as corrupt afte
r recovery), however, so the index points to corrupt blocks. The index must be dropped, and the table and index must be re-created if
necessary.
| See Also:
Oracle Da
ta Guard Concepts and Administration for information about the impact of |
If you have a read-only tablespace on read-only or slow media, then you may encounter errors or poor perf
ormance when recovering with the USING BACKUP CONTROLFILE option. This situation occurs when t
he backup control file indicates that a tablespace was read/write when the control file was backed up. In this case, media recovery m
ay attempt to write to the files. For read-only media, the database issues an error saying that it cannot write to the files. For slo
w media, such as a hierarchical storage system backed up by tapes, performance may suffer.
To avoid these recovery problems, use current control files rather than backups to recover the database. If you need to use a backup control file, then you can also avoid this problem if the read-only tablespace has not suffered a media failure.
You have these alternatives for re covering read-only and slow media when using a backup control file:
If a current or backup control file is unavailable for recovery, then you can execute a CRE
ATE CONTROLFILE statement as described in "Losing All Current and Backup Control Fi
les". Read-only files should not be listed in the CREATE CONTROLFILE
statement so that recovery can skip these files. No recovery is required for read-only datafiles unless you restored backups of these
files from a time when the datafiles were read/write.
After you create a new control file
and attempt to mount and open the database, the database performs a data dictionary check against the files listed in the control fil
e. Files that were not listed in the CREATE CONTROLFILE statement but are present in the data dictionary ha
ve entries created for them in the control file. These files are named as MISSINGnnnnn, where
nnnnn is a five digit number starting with 0.
After the data
base is open, rename the read-only files to their correct filenames by executing the ALTER DATABASE R
ENAME FILE statement for all the files whose name is prefixed with MISSING.
To prepare for a scenario in which you might have to re-create the control file, run the following statement when t
he database is mounted or open to obtain the CREATE CONTROLFILE syntax:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This SQL state
ment produces a trace file that you can edit and use as a script to re-create the control file. You can specify either the RESE
TLOGS or NORESETLOGS (default) keywords to generate CREATE CONTROLFILE ...
RESETLOGS or CREATE CONTROLFILE ... NORESETLOGS versions of the scr
ipt.
All the restrictions related to read-only files in CREATE CONTROLFI
LE statements also apply to offline normal tablespaces, except that you need to bring the tablespace online after the database
is open. You should leave out tempfiles from the CREATE CONTROLFILE statement and add them after database
open.
The transportable tablespace feature of Oracle allows a user to transport a set of tablespaces from one database to another. Tr ansporting a tablespace into a database is like creating a tablespace with preloaded data. Using this feature is often an advantage b ecause:
Like normal tablespaces, transportable tablespaces are recoverable. While you can recover normal tablespaces without a backup, you must h ave a version of the transported datafiles in order to recover a transported tablespace.
To recover a transportable tablespace:
ALTER TABLESPACE users OFFLINE IMMEDIATE;
% cp /backup/users.dbf $ORACLE_HOME/oradata/trgt/us ers01.dbf
RECOVER TABLESPACE users
You may see the error ORA-01244 when recovering through a transportable tablespace ope
ration just as when recovering through a CREATE TABLESPACE operation. In this case, rename the unnamed file
s to the correct locations using the procedure in "Recovering Through an Added Datafile with a Backup
Control File: Scenario".
| See Also:
Oracle Database Administrator's Guide for detailed information ab out using the transportable tablespace feature |
If a media failure has affected the online redo logs o f a database, then the appropriate recovery procedure depends on the following:
Table 18-1 displays V$LOG status information that can be crucial in a recovery
situation involving online redo logs.
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.
Solv e the problem by taking one of the following actions:
To replace a damaged member of a redo log group:
V$LOGFILE. The status is INVALID if the file
is inaccessible:
SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE WHERE STATUS='INVALID'; GROUP# STATUS MEMBER < /a>------- ----------- --------------------- 0002 INVALID /oracle/oradata/trgt/redo02.log
redo01.log from group 2, issue:
ALTER DATABASE DROP LOGFILE ME MBER '/oracle/oradata/trgt/redo02.log';
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 DATAB ASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log' REUSE TO GROUP 2;
If a media failure damages a ll 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 inactive, then it is not needed for crash recovery; if it is active, then it is needed for crash recovery.
< p class="BP">Your first task is to determine whether the damaged group is active or inactive.To determine whether the damaged groups are active:
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/db s/log1b.f 0002 INVALID /oracle/dbs/log2a.f 0002 INVALID /oracle/dbs /log2b.f 0003 /oracle/dbs/log3a.f 0003 /oracle/dbs/ log3b.f
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 all members of an online redo log group with INACTIVE status are damaged, then the procedu
re depends on whether you can fix the media problem that damaged the inactive redo log group.
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 be en archived:
STARTUP MOUNT
2, issue the following statement:
ALTER DATABASE CLEAR LOGFILE GROUP 2 ;
Clearing a not-yet-ar chived 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 fo r 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 archive d:
STARTUP MOUNT
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 brin
g 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;
% cp /disk1/oracle/dbs/*.f /disk2/backup
ALTER DATABASE stat
ement. For example, enter:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/dbs/cf_backup.f';
The ALTER DA
TABASE CLEAR LOGFILE statement can fail with an I/O error due to media failure when it is not possib
le to:
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 wa
s 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.
If
the database is still running and the lost active redo log is not the current log, then issue the ALTE
R SYSTEM CHECKPOINT statement. If successful, then the active redo log is rendered 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 L
GWR 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 act ive online log group in NOARCHIVELOG mode:
% cp /disk2/backup/*.dbf $ORACLE_HOME/oradata/trgt/
< a name="1006587">STARTUP MOUNT
RECOVER DATABASE UNTI L CANCEL CANCEL
RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;
SHUTDOWN IMMEDIATE
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 th e group when required. If the media failure is not temporary, then use the following procedure.
RESETLOGS option:
ALTER DATABASE OPEN RESETLOGS;
If you have lost multiple groups of the online redo log, then use the recovery met hod for the most difficult log to recover. The order of difficulty, from most difficult to least difficult, follows:
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 do
es 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 backed up . . . | |
|---|---|
|
All datafiles after the filled online redo log group (which is now archived) was written |
The archived version of the filled online redo log group is not required for compl ete media recovery operation. |
|
A specific datafile before the filled online redo log group was written |
If the corresponding datafile is damaged by a permanent media failure, use the most recent backup of the dam aged datafile and perform incomplete recovery of the tablespace containing the damaged datafile, up to the damaged log. |
One not-uncommon error i s the accidental dropping of a table from your database. In general, the fastest and simplest solution is to use the flashback drop f eature, described in "Oracle Flashback Drop: Undo a DROP TABLE Operation", to reverse the dropping of the table. However, if for some reason, such as flashback drop being disabled or the table hav ing 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 Oralce 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: If you have granted powerful privileges
(such as |
To recover a table that has been accidentally dropped:
< a name="1007946">D
| See Also:
Or acle Database Utilities for more information about the Oracle export and import utilities |
How you perform media recovery depends on whether your database participates in a distributed database system. The Oracle distributed database architecture is autonomous. Therefore, depending on the type of recovery operation selected for a single damaged database, you may have to coordinate recovery operations globally among all databases in the distributed system.
Table 18-2 summarizes different types of recovery operations and wheth er coordination among nodes of a distributed database system is required.
If one node in a distributed database requires recovery to a past ti me, it is often necessary to recover all other nodes in the system to the same point in time to preserve global data consistency. Thi s operation is called coordinated, time-based, distributed database recovery. The following tasks shoul d be performed with the standard procedures of time-based and change-based recovery described in this chapter.
RESETLOGS option, search the alert_S
ID.log of the database for the RESETLOGS message.
If the message is, "RESETLOGS after complete recovery through change xxx", then you have applied all the changes in the
database and performed complete recovery. Do not recover any of the other databases in the distributed system, or you will unnecessa
rily remove changes in them. Recovery is complete.
If the message is, "RESETLOGS aft
er incomplete recovery UNTIL CHANGE xxx", then you have successfully performed an incomplete recovery. Record the change numbe
r from the message and proceed to the next step.
You may need to remove a
database, that is, the database files that form the database, from the operating system. For example, this scenario can occur when yo
u create a test database and then no longer have a use for it. The SQL*Plus command DROP DATABASE can perfo
rm this function.
| See Also:
Ora
cle Database Backup and Recovery Basics to learn how to use the equivalent RMAN command |
To drop the database:
SQL> STARTUP FORCE MOUNT
SQL> DROP DATABASE; # deletes all database files, both ASM and non-ASM
If the database is on raw disk, the command does not delete the actual raw disk special files.
< a name="1006928">% rm /backup/* ?/oradata/trgt/arch/*
|
