Subject: Oracle7 Recovery Scenarios and Equivalent Oracle8 Recovery Manager Techniques Type: BULLETIN Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 26-AUG-1998 1. Introduction 2. Test platform 3. Examples 3.1. Restore of complete database from a full cold backup 3.2. Restore of datafiles only from a full cold backup (current controlfile available) 3.3. Restore of datafiles from a full (hot or cold) backup and complete recovery 3.4. Restore of a subset of datafiles from a full (hot or cold) backup, and complete recovery 3.5. Restore of datafiles and controlfile from a (hot or cold) backup, and complete recovery 3.6. Restore of datafiles and controlfile from a (hot or cold) backup, and complete recovery. A datafile has been added since the controlfile was backed up 3.7. Restore of datafiles from a (hot or cold) backup, current controlfile available, and incomplete recovery 3.8. Restore of datafiles and controlfile from a (hot or cold) backup, and incomplete recovery 3.9. Restoring a subset of the database (skipping tablespaces during restore & recovery). Current controlfile and redo logs are available. 3.10. Restore of files to new location 1. Introduction This article examines Oracle7 recovery scenarios and explains how RMAN could be used to recover the database in a similar situation. This article compares the use of Oracle7 versus RMAN for common recovery scenarios. Note on terminology. An Oracle7 cold backup is equivalent to an Oracle8 (RMAN) offline (closed) backup. An Oracle7 hot backup is equivalent to an Oracle8 (RMAN) online (open) backup. The examples in this article are not exhaustive; merely a selection of common restore & recovery scenarios. You should always test all backup & recovery procedures before implementing them in a production environment. @ Please feel free to test and document further examples. 2. Test platform O/S: Sun Solaris 2.6 RMAN client version: 8.0.5 RMAN recovery catalog database: 8.0.5 Target database: 8.0.5 During testing, a recovery catalog was used as opposed to the target database controlfile. 3. Examples 3.1. Restore of complete database from a full cold backup Oracle7 backup: Cold backup of datafiles, controlfiles, redo logs Oracle8 backup: RMAN offline database backup Target database mode: Noarchivelog Loss: Complete current database has been lost (datafiles, controlfiles, redo logs) Requirement: Open the database at the 'time' of the backup Oracle 7 restore/recovery: o. Restore the cold backup of datafiles, controlfiles, and redo logs o. Startup Oracle 8 (RMAN) restore/recovery: Target database mode: Instance started (database unmounted) Example RMAN script: RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> restore controlfile to ''; RMAN> sql "alter database mount"; RMAN> restore database; RMAN> sql "alter database open resetlogs"; RMAN> } * Important Notes * o. RMAN does NOT backup online redo logs. Hence, the database must be opened with resetlogs after it has been restored o. RMAN detects that a backup controlfile has been restored, and allows a resetlogs to be specified on open 3.2. Restore of datafiles only from a full cold backup (current controlfile available) Oracle7 backup: Cold backup of datafiles, controlfiles, redo logs Oracle8 backup: RMAN offline database backup Target database mode: Noarchivelog Loss: All current datafiles have been lost. Current controlfiles and redo logs are still available. Requirement: Open the database at the 'time' of the backup This example simulates the loss of the current database datafiles. Oracle 7 restore/recovery: Example: o. Restore the cold backup of datafiles (and optionally the redo logs) o. Mount the database o. Perform 'fake' recovery (recover database until cancel, and cancel without applying any logs) o. Open the database with resetlogs Notes: o. The database must be opened with resetlogs in order to recreate (or re-initialize, if the redo logs backup was restored) the redo logs. Oracle 8 (RMAN) restore/recovery: Target database mode: Database mounted Example RMAN script: RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> restore database; RMAN> set until logseq=1 thread=; RMAN> recover database; RMAN> sql "alter database open resetlogs"; RMAN> } Notes: o. Because the redo logs need to be re-created, a resetlogs must be forced. In order to do this, the "set until...." and "recover database" commands effectively perform a fake recovery, and the database can be opened with resetlogs. It is important to specify the 'set until' clause AFTER the datafiles have been restored, otherwise RMAN will attempt to restore the datafiles from a backup taken BEFORE log sequence #1 was current! @ o. Use of the above procedure has caused internal RMAN error @ RMAN-600 [6960]. Bug 1562459 has been logged for this. The above @ procedure has been re-tested and I was unable to reproduce the @ internal error. If however the error is generated, sepecify @ logseq=2 rather than logseq=1 (as per the bug workaround). 3.3. Restore of datafiles from a full (hot or cold) backup and complete recovery Oracle7 backup: Cold or hot backup of datafiles Oracle8 backup: RMAN offline or online database backup Target database mode: Archivelog Loss: All current datafiles have been lost. Current controlfiles and redo logs are still available. Requirement: Restore and complete recovery This example simulates the loss of the current database datafiles. The current controlfile and redo logs are still intact. Oracle 7 restore/recovery: Example: o. Restore the hot or cold datafile backup o. Restore archived logs if required for recovery o. Mount the database o. Perform complete media recover by rolling forward through the archive and online logs, and open the database Oracle 8 (RMAN) restore/recovery: Target database mode: Database mounted Example RMAN script: RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> restore database; RMAN> recover database; RMAN> sql "alter database open"; RMAN> } Notes: o. Archived logs will be restored automatically as required. It is possible, but not necessary, to manually restore from an archivelog backupset 3.4. Restore of a subset of datafiles from a full (hot or cold) backup, and complete recovery Oracle7 backup: Cold or hot backup of datafiles Oracle8 backup: RMAN offline or online database backup Target database mode: Archivelog Loss: A subset of the current datafiles have been lost. Current controlfiles and redo logs are still available. Requirement: Restore and complete recovery This example simulates the loss of one of the database datafiles. The current controlfile and redo logs are still intact. Oracle 7 restore/recovery: Example 1 (database closed): o. Restore the hot or cold datafile backup o. Restore archived logs if required for recovery o. Perform complete media recover by rolling forward through the archive and online logs, and open the database Example 2 (database open): o. Offline (immediate) the tablespace to which this datafile belongs o. Restore the lost datafile o. Perform tablespace recovery o. Online the tablespace Oracle 8 (RMAN) restore/recovery: Example 1: (database mounted) RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> restore datafile ''/; RMAN> recover datafile ''/; RMAN> sql "alter database open"; RMAN> } Example 2: (database open) RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> sql "alter tablespace offline immediate"; RMAN> restore datafile ''/; RMAN> recover datafile ''/; RMAN> sql "alter tablespace online"; RMAN> } 3.5. Restore of datafiles and controlfile from a (hot or cold) backup, and complete recovery Oracle7 backup: Cold or hot backup of datafiles and controlfile Oracle8 backup: RMAN offline or online database backup Target database mode: Archivelog Loss: All current datafiles and controlfiles have been lost. Online redo logs are still available. Requirement: Restore and complete recovery This scenario simulates the loss of everything but the online logs i.e. complete database recovery can still be performed. Oracle 7 restore/recovery: Example: o. Restore the hot or cold database backup (including the controlfile backup) o. Restore archived logs if required for recovery o. Perform complete media recover by rolling forward through the archive logs, and open the database with resetlogs. 'Backup controlfile' must be specified in the recover command, and the online logs applied manually (automatic recovery will search for archived logs with the same log sequence numbers as the online logs). Oracle 8 (RMAN) restore/recovery: Target database mode: Instance started, database not mounted Example: RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> restore controlfile to ''; RMAN> sql "alter database mount"; RMAN> restore database; RMAN> recover database; RMAN> sql "alter database open resetlogs"; RMAN> } Notes: o. Unless the recovery catalog is aware of the archived logs that were created from the time of the backup up until the current time, it won't know where to find these logs to rollforward the database. It is therefore important to register any 'missing' logs using the 'catalog archivelog..' command. This should be done prior to the RMAN 'recover database' command. This is why the recovery catalog should be frequently synchronized. o. RMAN automatically detects the online redo logs on disk and applies them automatically to complete the recovery 3.6. Restore of datafiles and controlfile from a (hot or cold) backup, and complete recovery. A datafile has been added since the controlfile was backed up Oracle7 backup: Cold or hot backup of datafiles and controlfile Oracle8 backup: RMAN offline or online database backup Target database mode: Archivelog Loss: All current datafiles and controlfiles have been lost. Online redo logs are still available. Requirement: Restore and complete recovery This scenario is not uncommon. A datafile has been added to the database since the last backup was taken, and the DBA now has to restore from the backup (including the controlfile) and has no copy of the new datafile. Oracle 7 restore/recovery: Example: o. Restore the hot or cold database backup (including the controlfile backup) o. Restore archived logs if required for recovery o. Start a recovery (using backup controlfile). The recovery session will fail with errors: ORA-01244: unnamed datafile(s) added to controlfile ... ORA-01110: data file N: '' At this stage, create a new datafile: ALTER DATABASE CREATE DATAFILE.... o. Continue recovery Notes: o. See [NOTE:29430.1] for full details of recovery in this situation o. This technique is only available from Oracle7.3+. Prior to Oracle7.3 the newly added datafile would have been lost Oracle 8 (RMAN) restore/recovery: Target database mode: Instance started, database not mounted Example: RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> restore controlfile to ''; RMAN> sql "alter database mount"; RMAN> restore database skip tablespace ; RMAN> } At this stage, any RMAN recovery (if successful - see [BUG:720033] below) will run into the same warnings as traditional recovery i.e. ORA-01244, ORA-01110. I suggest using Oracle7 techniques to rolfoward the database, create the new datafile, and complete the media recovery. Notes: o. The 'restore database skip tablespace...' command can be used if the new datafile is the only file in this tbs, otherwise use multiple 'recover datafile' commands to recover all other datafiles. o. I encountered a problem when using the 'recover database skip tablespace....' command. See bug 720033 for details. 3.7. Restore of datafiles from a (hot or cold) backup, current controlfile available, and incomplete recovery Oracle7 backup: Cold or hot backup of datafiles Oracle8 backup: RMAN offline or online database backup Target database mode: Archivelog Loss: All current datafiles have been lost. Current controlfiles and redo logs are still available. Requirement: Restore and incomplete recovery This scenario is common in a number of cases. For example, it might be necessary to perform point in time recovery in order to recover a dropped table. It is also possible that complete recovery is not an option because an archived log is not available. Oracle 7 restore/recovery: Example: o. Restore the hot or cold database backup o. Restore archived logs if required for recovery o. Perform incomplete media recover by rolling forward through the archive logs, and open the database with resetlogs. The time to which the database is recovered depends upon the 'until' (cancel, time, or change) clause of the recover command Oracle 8 (RMAN) restore/recovery: Target database mode: Database mounted Example: RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> set until logseq|scn|time ......; RMAN> restore database; RMAN> recover database; RMAN> sql "alter database open resetlogs"; RMAN> } Notes: o. The 'set until' clause specifies the 'time' to which the database will be recovered. o. The 'set until' clause is equivalent to the 'until' clause of the Oracle7 recover command o. If 'logseq' is specified, the database is recovered to the end of the archived log PRIOR to the log sequence specified in the command 3.8. Restore of datafiles and controlfile from a (hot or cold) backup, and incomplete recovery Oracle7 backup: Cold or hot backup of datafiles Oracle8 backup: RMAN offline or online database backup Target database mode: Archivelog Loss: All current datafiles and controlfiles have been lost. Requirement: Restore and incomplete recovery This is similar to 3.4. except that the current controlfile has been lost. This is effectively a loss of the complete current database. Oracle 7 restore/recovery: Example: o. Restore the hot or cold database and controlfile backup o. Restore archived logs if required for recovery o. Perform incomplete media recover by rolling forward through the archive logs, and open the database with resetlogs. In this case, the 'using backup controlfile' option must be specified in the recover command Oracle 8 (RMAN) restore/recovery: Example: Target database mode: Database mounted RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> set until logseq|scn|time ......; RMAN> restore database; RMAN> recover database; RMAN> sql "alter database open resetlogs"; RMAN> } 3.9. Restoring a subset of the database (skipping tablespaces during restore & recovery). Current controlfile and redo logs are available. Oracle7 backup: Cold or hot backup of datafiles Oracle8 backup: RMAN offline or online database backup Target database mode: Archivelog or noarchivelog (depending on the backup, and whether recovery is performed Loss: All current datafiles and controlfiles have been lost. Requirement: Restore and complete recovery This technique can be useful if you do not want to restore a large temporary tablespace. It might be quicker to skip restore and recovery of such a tablespace, and simply recreate it once the database has been opened. Another example would be restoring the database in order to recover a dropped table. In this case, you might only want to restore and recover the tablespace containing the table data (plus system and rollback segment tablespaces). Example 3.10 contains an example of this. The examples below show complete recovery, but these techniques can equally be applied to incomplete media recovery. Furthermore these techniques can also be applied even if no recovery is to be performed. Oracle 7 restore/recovery: Example: o. Restore the hot or cold database and controlfile backup o. Restore archived logs if required for recovery o. Mount the database and offline datafiles belonging to the tablespaces that aren't required o. Recover the database o. Open the database and drop the tablespaces whose datafiles have not been restored (and rebuild if desired) Oracle 8 (RMAN) restore/recovery: Example 1 (media recovery): Target database mode: Database mounted RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> restore database skip tablespace temp; RMAN> recover database skip tablespace temp; RMAN> sql "alter database open"; RMAN> sql "drop tablespace temp"; RMAN> sql "create tablespace temp datafile ''''"; RMAN> } Example 2 (no media recovery): Target database mode: Database mounted RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> restore database skip tablespace temp; RMAN> sql "alter database datafile '''' offline drop"; RMAN> sql "alter database open"; RMAN> sql "drop tablespace temp"; RMAN> sql "create tablespace temp datafile ''''"; RMAN> } 3.10. Restore of files to new location Oracle7 backup: Cold or hot backup of datafiles Oracle8 backup: RMAN offline or online database backup Target database mode: Archivelog or noarchivelog (depending on the backup, and whether recovery is performed Loss: All current datafiles and controlfiles have been lost. Requirement: Restore and complete recovery This example simulates a database restore to a different filsystem, or indeed even a different server. This is usual when filesystem(s) or the original host has been lost. It is also a technique used to restore a subset of the database to a different location, and perform incomplete recovery in order to recover a dropped table. The following examples show complete media recovery, but these techniques can be used without media recovery, or with incomplete recovery. Oracle 7 restore/recovery: Example: o. Restore the hot or cold database backup to the new location o. Restore archived logs if required for recovery o. Mount the database o. Use the 'alter database rename file' command so that the controlfile knows where to find the restored datafiles (and optionally redo logs) o. Recover the database if necessary o. Open the database Oracle 8 (RMAN) restore/recovery: Example: Target database mode: Database mounted RMAN> run { RMAN> allocate channel t1 type 'sbt_tape'; RMAN> set newname for datafile 4 to ''; RMAN> restore database; RMAN> switch datafile 4; RMAN> recover database; RMAN> sql "alter database open"; RMAN> } Notes: o. In the above example the 'set newname' command tells RMAN where to restore, in this case, datafile 4. The 'switch' command is equivalent to the Oracle7 'alter database rename file' command.