Oracle® Database Backup and Recovery Advanced User's Guide 10g Release 1 (10.1) Part Number B10734-01 |
|
|
View PDF |
This chapter describes how to use Recovery Manager to perform restore and recovery operations. This chapter contains these topics:
RMAN can perform recovery of the whole database to a specified past time, SCN, or log sequence number. This type of recovery is sometimes called incomplete recovery because it does not completely use all of the available redo. Incomplete recovery of the whole database is also called database point-in-time recovery (DBPITR).
If you have enabled the collectionof flashback logs, you may be able to use Oracle Flashback Database instead of performing DBPITR. Flashback Database is generally faster and simpler to use, when it is available, because it does not require restoring a past backup. Depending upon your situation, you may also find one of the other Oracle flashback features can meet your data recovery need. See "Oracle Flashback Technology: Overview" for more details about these alternatives before deciding whether to use DBPITR.
DBPITR requires restoring your database from an older backup, then performing media recovery until your specified target time, SCN or log sequence number. Note that because you need your archived redo log files to perform this process, you cannot perform database point-in-time recovery if you have been running your database in NOARCHIVELOG mode.
After database point-in-time recovery, you must open the database with the RESETLOGS
option. Using the RESETLOGS
option archives the current online redo logs, resets the log sequence to 1, and then gives the online redo logs a new time stamp and SCN. In this way, the database eliminates the possibility of corrupting datafiles by the application of obsolete archived redo logs.
You have to recover all datafiles: you cannot recover some datafiles before the RESETLOGS
and others after the RESETLOGS
.
The OPEN RESETLOGS
operation will fail if a datafile is off-line, unless the datafile went offline normally or is read-only. You can bring files in read-only or offline normal tablespaces online after the RESETLOGS
because they do not need any redo.
When performing DBPITR, consider using the SET
UNTIL
command to set the target time at the beginning of the process, rather than specifying the UNTIL
clause on the RESTORE
and RECOVER
commands individually. SET UNTIL
sets the desired time for any subsequent RESTORE
, SWITCH
, and RECOVER
commands in the same RUN
job.
Note that if you specify a SET
UNTIL
command after a RESTORE
and before a RECOVER
, you may not be able to recover the database to the point in time required because the restored files may already have time stamps more recent than the set time. Hence, it is recommended that you specify the SET
UNTIL
command before the RESTORE
command.
The database must be closed to perform database point-in-time recovery. If you are recovering to a time, then you should set the time format environment variables before invoking RMAN. The following are sample Globalization Support settings:
NLS_LANG = american_america.us7ascii NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"
To recover the database until a specified time, SCN, or log sequence number:
SHUTDOWN IMMEDIATE; STARTUP MOUNT;
You can also examine the alert.log
to find the SCN of an event and recover to a prior SCN. Alternatively, you can determine the log sequence number that contains the recovery termination SCN, and then recover through that log. For example, query V$LOG_HISTORY
to view the logs that you have archived.
RECID STAMP THREAD# SEQUENCE# FIRST_CHAN FIRST_TIM NEXT_CHANG ---------- ---------- ---------- ---------- ---------- --------- ---------- 1 344890611 1 1 20037 24-SEP-02 20043 2 344890615 1 2 20043 24-SEP-02 20045 3 344890618 1 3 20045 24-SEP-02 20046
RUN
command:
NLS_LANG
and NLS_DATE_FORMAT
environment variables.The following example performs an incomplete recovery until November 15 at 9 a.m.
RUN { SET UNTIL TIME 'Nov 15 2002 09:00:00'; # SET UNTIL SCN 1000; # alternatively, specify SCN # SET UNTIL SEQUENCE 9923; # alternatively, specify log sequence number RESTORE DATABASE; RECOVER DATABASE; }
ALTER DATABASE OPEN RESETLOGS;
RMAN can seamlessly restore and recover backups from previous incarnations to the current incarnation. To perform point-in-time recovery to a target time prior to the most recent RESETLOGS
, however, you must run the RESET
DATABASE
command to reset the database to the incarnation current at the desired target time.
Assume the following situation:
trgt
on October 2, 2002.RESETLOGS
option on October 10, 2002. A new database incarnation was created.On October 25, you discover that you need crucial data that was dropped from the database at 8:00 a.m. on October 8, 2002. You decide to reset trgt
to the prior incarnation, restore the October 2 backup, and recover to 7:55 a.m. on October 8.
Note: It is not possible to restore one datafile of a previous incarnation while the current database is in a different incarnation--you must restore the whole database. |
To recover the database by means of a backup from the old incarnation:
LIST
command:
# obtain primary key of old incarnation LIST INCARNATION OF DATABASE trgt; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- ------- ------ ------- ---------- ---------- 1 2 TRGT 1224038686 PARENT 1 02-OCT-02 1 582 TRGT 1224038686 CURRENT 59727 10-OCT-02
SHUTDOWN FORCE NOMOUNT
# reset database to old incarnation RESET DATABASE TO INCARNATION 2;
RUN
command:
For example, run the following commands:
RUN { # set time to just before data was lost. SET UNTIL TIME 'Oct 8 2002 07:55:00'; RESTORE CONTROLFILE; # FROM AUTOBACKUP not needed in catalog mode ALTER DATABASE MOUNT; # mount database after restoring control file RESTORE DATABASE; RECOVER DATABASE; }
# this command automatically resets the database so that this incarnation is # the new incarnation ALTER DATABASE OPEN RESETLOGS;
If all copies of the current control file are lost or damaged, then you must restore and mount a backup control file before you can perform recovery. There are two cases to consider:
The following notes and restrictions apply regardless of whether you use a recovery catalog:
RECOVER
command after restoring a backup control file, even if no datafiles have been restored.RESETLOGS
option. If you do not, then the database can display the following error when attempting to sort: ORA-25153:
Temporary
Tablespace
is
Empty
.RESETLOGS
option after performing either complete or point-in-time recovery with a backup control file.If you changed the archiving destination or format during recovery, or if you added new online log members after the backup of the control file, then RMAN may not be able to automatically catalog a needed online or archived log. In this situation, RMAN reports errors similar to the following:
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/29/2001 14:23:09 RMAN-06054: media recovery requesting unknown log: thread 1 scn 86945
In this case, you must use the CATALOG
command to manually add the required logs to the repository so that recovery can proceed. The cataloging procedure is described in Oracle Database Backup and Recovery Basics.
If you use a recovery catalog and have a backup control file available, then this procedure does not differ substantially from a standard restore and recovery. The procedure in this section assumes that you are restoring the control file to its default location. If you must restore the control file to a new location, then refer to Oracle Database Backup and Recovery Basics for instructions.
When you perform a restore operation using a backup control file and you use a recovery catalog, RMAN automatically adjusts the control file to reflect the structure of the restored backup.
The following procedure assumes that you do not have more than one target database registered in the catalog with the same name. If multiple target databases are registered with the same name, then you must specify the DBID with the SET
DBID
command so that RMAN knows which control file to restore. The DBID is the unique numerical identifier for a database.
See Also:
"Performing Recovery with a Backup Control File and No Recovery Catalog" to learn how to set the DBID |
To recover the database with a backup control file and a recovery catalog:
STARTUP NOMOUNT
RESTORE
CONTROLFILE
command to restore the control file to all default locations specified in the CONTROL_FILES
initialization parameter. To restore a control file from an older backup, you can run SET
UNTIL
or specify the UNTIL
clause on the RESTORE
CONTROLFILE
command.SET
UNTIL
command for incomplete recovery. Note that you can also specify the UNTIL
clause on the RESTORE
and RECOVER
commands.This example restores the control file to its default location, then restores and completely recovers the database:
RESTORE CONTROLFILE; ALTER DATABASE MOUNT; RESTORE DATABASE; RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
SQL "ALTER TABLESPACE temp ADD TEMPFILE ''?/oradata/trgt/temp01.dbf'' REUSE";
This section assumes that you have RMAN backups of the control file, but do not use a recovery catalog. Assuming that you enabled the control file autobackup feature for the target database, you can restore an autobackup of the control file. Because the autobackup uses a default format, RMAN can restore it even though it does not have a repository available that lists the available backups. You can restore the autobackup to the default or a new location. RMAN replicates the control file to all CONTROL_FILES
locations automatically.
Because you are not connected to a recovery catalog, the control file must have a record of all needed backups. If any backups are not listed in the control file, then RMAN cannot restore them. You can add backup pieces and image copies to the control file repository with the CATALOG
command.
Because the repository is not available when you restore the control file, run the SET
DBID
command to identify the target database. You should only run the SET
DBID
command in the following specialized circumstances:
To recover the database with an autobackup of the control file without a recovery catalog:
CONNECT TARGET /
STARTUP NOMOUNT;
SET
DBID
. RMAN displays the DBID whenever you connect to the target. You can also obtain it by inspecting saved RMAN log files, querying the catalog, or looking at the filenames of control file autobackup. (refer to "Restoring When Multiple Databases in the Catalog Share the Same Name: Example"). For example, run:
SET DBID 676549873;
sbt
, then you must allocate one or more sbt
channels. Because no repository is available, you cannot use preconfigured channels. If the autobackup was created on a disk channel, however, then you do not need to manually allocate a channel.If you do not care about using configured channels from your control file, then you can simply mount the database at this point.
UNTIL
clause to a time, log sequence, or SCN before the online redo logs. If the online logs are usable, then perform a complete recovery as described in Oracle Database Backup and Recovery Basics.In this example, the online redo logs have been lost. This example limits the restore of the control file autobackup, then performs recovery of the database to log sequence 13243, which is the most recent archived log:
RUN { # Optionally, set upper limit for eligible time stamps of control file # backups # SET UNTIL TIME '09/10/2000 13:45:00'; # Specify a nondefault autobackup format only if required # SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK # TO '?/oradata/%F.bck'; ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...'; # allocate manually RESTORE CONTROLFILE FROM AUTOBACKUP MAXSEQ 100 # start at sequence 100 and count down MAXDAYS 180; # start at UNTIL TIME and search back 6 months ALTER DATABASE MOUNT DATABASE; } # uses automatic channels configured in restored control file RESTORE DATABASE UNTIL SEQUENCE 13243; RECOVER DATABASE UNTIL SEQUENCE 13243; # recovers to latest archived log
ALTER DATABASE OPEN RESETLOGS;
Various scenarios are possible when restoring a database to a new host. For example, you may want to:
To create a duplicate database for testing while maintaining the original database, use the DUPLICATE
command instead of the RESTORE
command (refer to "Duplicating a Database with Recovery Manager"). RMAN automatically creates a unique database identifier for the duplicate database. This chapter covers the use of the RESTORE
command only.
To test the restore of a database to a new host or to move the database to a new host, run the RESTORE
command. If you perform a test restore only, then you should do the following to prevent overwriting the target records in the recovery catalog:
NOCATALOG
mode when restoring the datafiles.Table 8-1 describes the impact on the RMAN repository when you are restoring or duplicating to a new host.
The basic procedure for restoring the database to a new host does not differ substantially from incomplete recovery on the original host. The principal issue is whether the path names of the database files on the new host are going to be the same as the path names of the files on the primary host.
Which restore procedure you should use depends on your situation. If the path names of the restored files will be the same as the original path names, see "Restoring Datafile Copies to a New Host: Example". If the path names are different, refer to "Performing Disaster Recovery".
Note the following when restoring to a new host:
CATALOG
command to update the RMAN repository with the new filenames and use the CHANGE
...
UNCATALOG
command to uncatalog the old filenames.Because the restored database will not have the online redo logs of the production database, perform incomplete recovery up to the lowest SCN of the most recently archived log in each thread and then open with the RESETLOGS
option. Obtain the SCN for recovery termination by finding the lowest SCN among the most recent archived logs for each thread.
Start SQL*Plus and use the following query to determine the necessary SCN:
SQL> SELECT MIN(maxnc) FROM (SELECT MAX(a.NEXT_CHANGE#) maxnc FROM V$ARCHIVED_LOG a, V$THREAD t WHERE a.THREAD# = t.THREAD# AND a.ARCHIVED='YES' AND t.ENABLED='DISABLED' GROUP BY a.THREAD#);
The DUPLICATE
command is the preferred method of copying the target database. DUPLICATE
creates a new DBID for the copied database, allowing it to be registered in the same recovery catalog as the original target database. However, you may wish to perform a test run of your disaster recovery scenarios that uses exactly the same steps that you would use in a genuine emergency. If so, then you should use the RESTORE
and RECOVER
commands rather than DUPLICATE
.
This scenario assumes the following:
hosta
and hostb
, are running Sun Solarishostb
is different from hosta, so that trgta
is located in /net/hosta/dev3/oracle/dbs
, but you want to restore the database to /net/hostb/oracle/oradata/test
trgta
on hostb
, while keeping database trgta up and running on hostaORACLE_SID
for the trgta database is trgta and will not change for the restored databasetrgta
To test the restore of the database to a new host:
hostb
. To test disaster recovery, you need to have a recoverable backup of the target database. When preparing your disaster recovery strategy, ensure that the backups of the datafiles, control files, and server parameter file are restorable on hostb
. Hence, you must configure the media management software so that hostb
is a media manager client and can read the backup sets created on hosta
. Consult the media management vendor for support on this issue.ORACLE_SID
on hostb
. This case study assumes that you want to authenticate yourself through the operating system, which is much faster than configuring Oracle Net and creating a password file. However, you must be connected to hostb either locally or through telnet.
While connected to hostb with administrator privileges, edit the /etc/group
file so that you are included:
dba:*:614:<your_user_name>
Run the setenv
command on hostb
to set the ORACLE_SID
. In this example, you set the SID to the same value that you used on hosta
:
% setenv ORACLE_SID trgta
Start RMAN and connect to the target instance without connecting to the recovery catalog.
% rman TARGET / NOCATALOG
V$DATABASE
in the target and RC_DATABASE
in the catalogV$RMAN_STATUS
)Run SET
DBID
to set the DBID, then run STARTUP
NOMOUNT
:
SET DBID 1340752057; STARTUP NOMOUNT
RMAN will fail to find the server parameter file, which has not yet been restored, but will start the instance with a "dummy" file. Sample output follows:
startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/net/hostb/oracle/dbs/inittrgta.ora' trying to start the Oracle instance without parameter files ... Oracle instance started
RUN { ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...'; RESTORE SPFILE TO PFILE '?/oradata/test/inittrgta.ora' FROM AUTOBACKUP; SHUTDOWN ABORT; }
Change any location-specific parameters, for example, those ending in _DEST
and _PATH
, to reflect the new directory structure. For example, edit the following parameters:
- IFILE - *_DUMP_DEST - LOG_ARCHIVE_DEST* - CONTROL_FILES
Restart the instance, specifying the client-side initialization parameter file that you restored:
STARTUP FORCE NOMOUNT PFILE='?/oradata/test/inittrgta.ora';
CONTROL_FILES
initialization parameter. For example:
RUN { ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...'; RESTORE CONTROLFILE FROM AUTOBACKUP; ALTER DATABASE MOUNT; }
hostb
). Because the control file is from the trgta database, the recorded filenames use the original hosta filenames. You can query V$
views to obtain this information. Start a new SQL*Plus session and connect to the newly created instance on hostb:
% sqlplus '/ AS SYSDBA'
Run the following query in SQL*Plus:
SQL> COLUMN NAME FORMAT a60 SQL> SPOOL LOG 'db_filenames.out' SQL> SELECT FILE# AS "File/Grp#", NAME FROM V$DATAFILE UNION SELECT GROUP#,MEMBER FROM V$LOGFILE; SQL> SPOOL OFF SQL EXIT
SET
NEWNAME
for each datafile so it is renamed to its new hostb
path namehostb
path namesSET UNTIL
to limit media recovery to the end of the archived redo logs, as described in "Determining the SCN for Incomplete Recovery After Restore"SWITCH
so that the control file recognizes the new path names as the official new names of the datafilesThe following is an example of an RMAN script to perform these steps, which is contained in text file reco_test.rman
:
RUN { # allocate a channel to the tape device ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...'; # rename the datafiles and online redo logs SET NEWNAME FOR DATAFILE 1 TO '?/oradata/test/system01.dbf'; SET NEWNAME FOR DATAFILE 2 TO '?/oradata/test/undotbs01.dbf'; SET NEWNAME FOR DATAFILE 3 TO '?/oradata/test/cwmlite01.dbf'; SET NEWNAME FOR DATAFILE 4 TO '?/oradata/test/drsys01.dbf'; SET NEWNAME FOR DATAFILE 5 TO '?/oradata/test/example01.dbf'; SET NEWNAME FOR DATAFILE 6 TO '?/oradata/test/indx01.dbf'; SET NEWNAME FOR DATAFILE 7 TO '?/oradata/test/tools01.dbf'; SET NEWNAME FOR DATAFILE 8 TO '?/oradata/test/users01.dbf'; SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo01.log'' TO ''?/oradata/test/redo01.log'' "; SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo02.log'' TO ''?/oradata/test/redo02.log'' "; # Do a SET UNTIL to prevent recovery of the online logs SET UNTIL SCN 123456; # restore the database and switch the datafile names RESTORE DATABASE; SWITCH DATAFILE ALL; # recover the database RECOVER DATABASE; } EXIT
For example, connect and execute as follows:
% rman TARGET / NOCATALOG RMAN> @reco_test.rman
RMAN will apply as many of the archived redo logs as it can and leave the database in a state in which is can be opened.
RESETLOGS
options:
RMAN> ALTER DATABASE OPEN RESETLOGS;
RMAN> SHUTDOWN ABORT RMAN> EXIT
Remove all test files. You can do this with an operating system utility or in RMAN. For example, in Unix you could perform the procedure this way:
% rm $ORACLE_HOME/oradata/test/*
You can also use RMAN for a procedure that works ok all platforms. For example:
RMAN> STARTUP FORCE NOMOUNT PFILE='?/oradata/test/inittrgta.ora'; RMAN> DROP DATABASE;
Because you did not perform the restore and recovery when connected to the recovery catalog, the recovery catalog contains no records for any of the restored files or the procedures performed during the test. Likewise, the control file of the trgta
database is completely unaffected by the test.
If you are in a disaster recovery scenario, then presumably you have lost the target database, the recovery catalog database, all control files, all online redo logs, and all parameter files.
To perform a disaster recovery, the minimum required set of backups is backups of some datafiles, some archived redo logs generated after the time of the backup, and at least one autobackup of the control file.
The basic procedure for disaster recovery is found in "Performing Recovery with a Backup Control File", with an additional first step of restoring an autobackup of the server parameter file as described in Oracle Database Backup and Recovery Basics. After the instance is started, you can restore an autobackup of the control file, mount it, then restore and recover the datafiles. Because you are restoring to a new host, you should review the considerations described in "Restoring the Database to a New Host".
The following scenario restores and recovers the database to the most recently available archived log, which in this example is log 1124 in thread 1. It assumes that:
In this scenario, perform the following steps:
tnsnames.ora
and listener.ora
by means of operating system utilities.SET
DBID
command, as described in "Performing Recovery with a Backup Control File and No Recovery Catalog".STARTUP
NOMOUNT
command. RMAN attempts to start the instance with a dummy server parameter file.RESTORE
SPFILE
FROM
AUTOBACKUP
command.STARTUP
FORCE
NOMOUNT
mode so that the instance is restarted with the restored server parameter file.CATALOG
command (refer to"Removing Recovery Catalog Records with Status DELETED").SET
NEWNAME
commands before the restore and perform a switch after the restore to update the control file with the new locations for the datafiles (refer to"Performing Disaster Recovery").RESETLOGS
mode. Only complete this last step if you are certain that no other archived logs can be applied.
# Start RMAN and connect to the target database % rman TARGET SYS/oracle@trgt # Set the DBID for the target database RMAN> SET DBID 676549873; RMAN> STARTUP FORCE NOMOUNT; # rman starts instance with dummy parameter file RUN { ALLOCATE CHANNEL t1 DEVICE TYPE sbt; RESTORE SPFILE FROM AUTOBACKUP; } # Restart instance with restored server parameter file RMAN> STARTUP FORCE NOMOUNT; RMAN> RUN { # Manually allocate a channel to the media manager ALLOCATE CHANNEL t1 DEVICE TYPE sbt; # Restore autobackup of the control file. This example assumes that you have # accepted the default format for the autobackup name. RESTORE CONTROLFILE FROM AUTOBACKUP; # The set until command is used in case the database # structure has changed in the most recent backups, and you wish to # recover to that point-in-time. In this way RMAN restores the database # to the same structure that the database had at the specified time. ALTER DATABASE MOUNT; SET UNTIL SEQUENCE 1124 THREAD 1; RESTORE DATABASE; RECOVER DATABASE; } RMAN> ALTER DATABASE OPEN RESETLOGS; # Reset the online logs after recovery completes
The following example of the RUN
command shows the same scenario except with new filenames for the restored datafiles:
RMAN> RUN { # If you need to restore the files to new locations, tell Recovery Manager # to do this using SET NEWNAME commands: SET NEWNAME FOR DATAFILE 1 TO '/dev/vgd_1_0/rlvt5_500M_1'; SET NEWNAME FOR DATAFILE 2 TO '/dev/vgd_1_0/rlvt5_500M_2'; SET NEWNAME FOR DATAFILE 3 TO '/dev/vgd_1_0/rlvt5_500M_3'; ALLOCATE CHANNEL t1 DEVICE TYPE sbt; RESTORE CONTROLFILE FROM AUTOBACKUP; ALTER DATABASE MOUNT; SET UNTIL SEQUENCE 124 THREAD 1; RESTORE DATABASE; SWITCH DATAFILE ALL; # Update control file with new location of datafiles. RECOVER DATABASE; } RMAN> ALTER DATABASE OPEN RESETLOGS;
The BLOCKRECOVER
command can restore and recover individual datablocks within a datafile. This procedure is useful when a trace file or standard output reveals that a small number of blocks within a datafile are corrupt.
Block media recovery is not useful in cases where the extent of data loss or corruption is not known; in this case, use datafile recovery instead.
See Also:
|
In this scenario, you identify the blocks that require recovery and then use any available backup to perform the restore and recovery of these blocks.
To recover datablocks by using all available backups:
alert.log
, trace files, or a media management interface. For example, you may see the following in a trace file:
ORA-01578: ORACLE data block corrupted (file # 8, block # 13) ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf' ORA-01578: ORACLE data block corrupted (file # 2, block # 19) ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'
BLOCKRECOVER
command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks as in the following example:
RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19;
In this scenario, you identify the blocks that require recovery, and then use only selected backups to perform the restore and recovery of these blocks.
To recover datablocks while limiting the type of backup:
alert.log
, trace files, or a media management interface. For example, you may see the following in a trace file:
ORA-01578: ORACLE data block corrupted (file # 8, block # 13) ORA-01110: data file 8: '/oracle/oradata/trgt/users01.dbf' ORA-01578: ORACLE data block corrupted (file # 2, block # 19) ORA-01110: data file 2: '/oracle/oradata/trgt/undotbs01.dbf'
BLOCKRECOVER
command at the RMAN prompt, specifying the file and block numbers for the corrupted blocks and limiting the backup candidates by means of the available options. For example, you can specify what type of backup should be used to restore the blocks:
# restore from backupset RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM BACKUPSET; # restore from datafile image copy RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 FROM DATAFILECOPY;
You can indicate the backup by specifying a tag:
# restore from backupset with tag "mondayam" RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 199 FROM TAG = mondayam;
You can limit the backup candidates to those made before a certain point:
# restore using backups made before one week ago RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL 'SYSDATE-7'; # restore using backups made before SCN 100 RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL SCN 100; # restore using backups made before log sequence 7024 RMAN> BLOCKRECOVER DATAFILE 8 BLOCK 13 DATAFILE 2 BLOCK 19 RESTORE UNTIL SEQUENCE 7024;
Note that if you limit the restore of datablocks with the UNTIL
clause, then RMAN must perform more recovery on the blocks, and the recovery phase must scan all logs for changes to the specified blocks.
The V$DATABASE_BLOCK_CORRUPTION
view indicates which blocks in a datafile were marked corrupt since the most recent BACKUP
or BACKUP
VALIDATE
command was run. After a corrupt block is repaired, the row identifying this block is deleted from the view.
You can check for logical corruption in the database by running the BACKUP
(with or without VALIDATE
option) with the CHECK
LOGICAL
command. If RMAN finds corrupt blocks, then it populates V$DATABASE_BLOCK_CORRUPTION
. The backup will stop if the number of corrupt blocks exceeds MAXCORRUPT
. A historical record of block corruptions in RMAN backups is kept in V$BACKUP_CORRUPTION
and V$COPY_CORRUPTION
.
In this scenario, you identify the blocks that require recovery by querying V$DATABASE_BLOCK_CORRUPTION
, and then instruct RMAN to recover all blocks listed in this view by means of the CORRUPTION
LIST
keyword.
To recover datablocks while limiting the type of backup:
V$DATABASE_BLOCK_CORRUPTION
to determine whether corrupt blocks exist in the most recent backups of the datafiles:
SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
V$DATABASE_BLOCK_CORRUPTION
by running the BLOCKRECOVER
CORRUPTION
LIST
command. For example, this command restores blocks from backups created more than 10 days ago:
BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'SYSDATE-10';
See Oracle Database Recovery Manager Reference for more details on block media recovery in RMAN.
The following sections illustrate the use of RMAN restore and recovery techniques in advanced scenarios.
To move the database to a new host by means of datafile copies, you must transfer the copies manually to the new machine. This example assumes that you are using a recovery catalog.
LIST
command to see a listing of datafile copies and their associated primary keys, as in the following example:
LIST COPY;
% cp -r /tmp/*dbf /net/new_host/oracle/oradata/trgt
CHANGE COPY OF DATAFILE 1,2,3,4,5,6,7,8 UNCATALOG;
CATALOG START
WITH
(if you know all the files are in directories with a common prefix easily addressed with a CATALOG START WITH
). For example, run:
CATALOG START WITH '?/oradata/trgt/';
Or this example specifies files individually:
CATALOG DATAFILECOPY '?/oradata/trgt/system01.dbf', '?/oradata/trgt/undotbs01.dbf', '?/oradata/trgt/cwmlite01.dbf', '?/oradata/trgt/drsys01.dbf', '?/oradata/trgt/example01.dbf', '?/oradata/trgt/indx01.dbf', '?/oradata/trgt/tools01.dbf', '?/oradata/trgt/users01.dbf';
As explained in the description for SET
DBID
in Oracle Database Recovery Manager Reference, you must run the SET
DBID
command to restore the control file when the target database is not mounted and multiple databases registered in the recovery catalog share the same name. In this case, do the following steps in order:
STARTUP
FORCE
NOMOUNT
command.SET
DBID
command to distinguish this connected target database from other target databases that have the same name.RESTORE
CONTROLFILE
command. After restoring the control file, you can mount the database to restore the rest of the database.This procedure avoids the RMAN-20005
message when you attempt to restore the control file. This message occurs because more than one target database has the same name, so RMAN requires the unique DBID to distinguishes the databases from one another.
If you have saved the RMAN output log files, then refer to these logs to determine the database identifier. RMAN automatically provides the DBID whenever you connect to the database:
% rman TARGET / Recovery Manager: Release 10.1.0.2.0 - Production connected to target database: RMAN (DBID=1231209694)
The output from RMAN jobs is also stored persistently in V$RMAN_STATUS
and RC_RMAN_STATUS
. The DBID is also stored in the RC_DATABASE
and RC_DATABASE_INCARNATION
recovery catalog views.
Because the names of the databases that are registered in the recovery catalog are presumed nonunique in this scenario, you must use some other unique piece of information to determine the correct DBID. If you know the filename of a datafile or online redo log associated with the database you wish to restore, and this filename is unique across all databases registered in the recovery catalog, then substitute this fully specified filename for filename_of_log_or_df
in the following queries. Determine the DBID by performing one of the following queries:
SELECT DISTINCT DB_ID FROM DB, DBINC, DFATT WHERE DB.DB_KEY = DBINC.DB_KEY AND DBINC.DBINC_KEY = DFATT.DBINC_KEY AND DFATT.FNAME = 'filename_of_log_or_df'; SELECT DISTINCT DB_ID FROM DB, DBINC, ORL WHERE DB.DB_KEY = DBINC.DB_KEY AND DBINC.DBINC_KEY = ORL.DBINC_KEY AND ORL.FNAME = 'filename_of_log_or_df';
To set the DBID, connect RMAN to the target database and run the following SET
command, where target_dbid
is the value you obtained from the previous step:
SET DBID = target_dbid;
To restore the control file to its default location and then mount it, run:
RESTORE CONTROLFILE; ALTER DATABASE MOUNT;
To restore and recover the database, run:
RESTORE DATABASE; RECOVER DATABASE # optionally, delete logs restored for recovery and limit disk space used DELETE ARCHIVELOG MAXSIZE 2M;
You can recover a database running in NOARCHIVELOG
mode with incremental backups. Note that the incremental backups must be consistent, like all backups of a database run in NOARCHIVELOG
mode, so you cannot make backups of the database when it is open.
Assume the following scenario:
trgt
in NOARCHIVELOG
mode.trgt
to tape on Sunday afternoon.In this case, you must perform an incomplete media recovery until Friday, since that is the date of the most recent incremental backup. RMAN uses the level 0 Sunday backup as well as the Wednesday and Friday level 1 backups.
Because the online redo logs are lost, you must specify the NOREDO
option in the RECOVER
command. You must also specify NOREDO
if the online logs are available but the redo cannot be applied to the incrementals. If you do not specify NOREDO
, then RMAN searches for redo logs after applying the Friday incremental backup, and issues an error message when it does not find them. If the correct online logs for the restored backup had been available, then you could have run RECOVER
DATABASE
without specifying NOREDO
. The changes in the online logs would have been applied.
After connecting to trgt
and the catalog database, recover the database with the following command:
STARTUP FORCE MOUNT; RESTORE CONTROLFILE; # restore control file from consistent backup ALTER DATABASE MOUNT; RESTORE DATABASE; # restore datafiles from consistent backup RECOVER DATABASE NOREDO; # specify NOREDO because online redo logs are lost ALTER DATABASE OPEN RESETLOGS;
The recovered database reflects only changes up through the time of the Friday incremental backup. Because there are no archived redo logs, there is no way to recover changes made after the incremental backup.
RMAN can handle lost datafiles without user intervention during restore and recovery. When a datafile is lost, the possible cases can be classified as follows:
RESTORE
creates the datafile in the original location or in a user-specified location (for example, with SET
NEWNAME
). The RECOVER
command can then apply the necessary logs to the datafile.In this example, the following sequence of events occurs:
ARCHIVELOG
mode database.history
containing a single datafile called /mydb/history01.dbf
.history01.dbf
from the operating system before you have a chance to back it up.In this case, the current control file knows about the datafile. To restore and recover the datafile, start RMAN, connect to the target database, and then enter the following commands at the RMAN prompt:
# take the tablespace with the missing datafile offline SQL "ALTER TABLESPACE history OFFLINE IMMEDIATE"; # restore the tablespace even though you have no backup RESTORE TABLESPACE history; # recover tablespace RECOVER TABLESPACE hisotry; # bring the recovered tablespace back online SQL "ALTER TABLESPACE history ONLINE";
You can use the transportable tablespace feature to copy a tablespace from one database to another database. As described in Oracle Database Administrator's Guide, the basic method for transporting tablespaces to a database on the same platform does not make use of RMAN. Nevertheless, if you use RMAN to back up your target database, then you can also use RMAN to transport backups of a tablespace from one database into another, following the procedure described in this section.
Reasons for using this procedure instead of ordinary tablespace transport include:
In the following procedure, assume that:
users
from database trgta
, located on computer hosta
, to database trgtb
, located on computer hostb
users
datafile as /net/hostb/oracle/oradata/trgtb/users01.dbf
in database trgtb
trgta
:
trgta
are accessible by hostb
through a tape device.
See Also:
Oracle Database Administrator's Guide to learn how to transport a tablespace |
To transport a tablespace into a different database:
hostb
according to the instructions in the "Preparing the Auxiliary Instance for Duplication: Basic Steps".rman TARGET SYS/oracle@auxdb CATALOG rman/rman@catdb
RESTORE CONTROLFILE TO '/net/hostb/tmp/cf.f'; STARTUP FORCE MOUNT; EXIT
NOCATALOG
mode. You connect in NOCATALOG
so that you do not pollute the recovery catalog with unecessary metadata about the restored files. For example:
rman TARGET SYS/oracle@auxdb NOCATALOG
UNTIL
time to indicate which backup of the tablespace that you want to restore.SET
NEWNAME
to specify temporary filenames for the SYSTEM
datafiles and the datafiles containing rollback or undo segments.SET
NEWNAME
to specify the filenames in the trgtb
database that will be used by the datafiles in the transported tablespace.For example, run the following commands:
RUN { SET UNTIL ARCHIVELOG 1243 THREAD 1; # set the end recovery log ALLOCATE CHANNEL c1 DEVICE TYPE sbt; # allocate channels if not configured # specify temporary name for SYSTEM datafile SET NEWNAME FOR DATAFILE 1 TO '/net/hostb/tmp/df1.dbf'; # specify temporary names for datafiles with undo or rollback segments SET NEWNAME FOR DATAFILE 2 TO '/net/hostb/tmp/df2.dbf'; # specify names for datafiles to be plugged into trgtb database SET NEWNAME FOR DATAFILE 8 TO '/net/hostb/oracle/oradata/trgtb/users01.dbf'; # restore and recover the datafiles RESTORE DATAFILE 1, 2, 8; SWITCH DATAFILE ALL; # points control file to SET NEWNAME filenames RECOVER DATAFILE 1, 2, 8; }
SQL 'ALTER TABLESPACE cwmlite,drsys,example,indx,tools OFFLINE IMMEDIATE';
RESETLOGS
option. For example:
ALTER DATABASE OPEN RESETLOGS;
trgtb
read-only. For example:
SQL 'ALTER TABLESPACE users READ ONLY';
users
tablespace as described in "Step 2: Generate a Transportable Tablespace Set" in Oracle Database Administrator's Guide. For example:
exp TRANSPORT_TABLESPACE=y TABLESPACES=(users) TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp
% sqlplus SYS/oracle@auxdb SQL> SHUTDOWN ABORT SQL> EXIT % rm /net/hostb/tmp/*
trgtb
database as described in "Step 4: Plug In the Tablespace Set" in Oracle Database Administrator's Guide. For example:
imp TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES=('/net/hostb/oracle/oradata/trgtb/users01.dbf') TABLESPACES=(users) TTS_OWNERS=(usera) FROMUSER=(usera) TOUSER=(userb)