Oracle Quick Reference

Recovery scripts and examples

RMAN recover database

connect target /
RUN {
shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;
}

connect target /
RUN {
shutdown immediate;
startup nomount;
set controlfile autobackup format for device type disk to '/db1/orabackup/%F';
restore controlfile from autobackup;
mount database;
restore database;
recover database;
alter database open;
}

RMAN recover tablespace

connect target /
RUN {
sql "alter tablespace sysaux offline";
RESTORE TABLESPACE sysaux;;
RECOVER TABLESPACE sysaux;
SQL "alter tablespace sysaux online";
}

RMAN recover data file

connect target /
RUN {
sql "alter tablespace sysaux offline";
restore datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NICK\SYSAUX01.DBF';
 recover datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\NICK\SYSAUX01.DBF';
SQL "alter tablespace sysaux online";
}

RMAN Point-In-Time-Recovery database (incomplete recovery)

Until SCN:

connect target /
RUN
{
RESTORE DATABASE;
RECOVER DATABASE UNTIL SCN 1000; # recovers through SCN 999 or
ALTER DATABASE OPEN RESETLOGS;
}

Until Time:

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

connect target /
RUN
{
set until time '28-JUL-05 06:00:00';
Restore database;
Recover database;
sql "alter database open reset logs";
}

Until Log Seq:

This example assumes that log sequence 1234 was lost due to a disk failure and the database needs to be recovered by using available archived redo logs.

RUN
{
  SET UNTIL SEQUENCE 1234 THREAD 1;
  RESTORE CONTROLFILE TO '?/oradata/cf.tmp';
  RESTORE CONTROLFILE FROM '?/oradata/cf.tmp'; # restores to all CONTROL_FILES locations
  ALTER DATABASE MOUNT;
  RESTORE DATABASE;
  RECOVER DATABASE;  # recovers through log 1233
  ALTER DATABASE OPEN RESETLOGS;
  # you must add new tempfiles to locally-managed temporary tablespaces after restoring
  # a backup control file
  SQL "ALTER TABLESPACE temp ADD TEMPFILE ''?/oradata/trgt/temp01.dbf'' REUSE";
}

RMAN restore control file

See also here
1. Shut down the database and try to start it up. The instance will start and try to mount the database, but when it doesn’t find the control files, the database fails to mount:

RMAN> SHUTDOWN IMMEDIATE;
database closed
database dismounted
Oracle instance shut down
RMAN> STARTUP
Oracle instance started
RMAN-00571:
RMAN-00569: ERROR MESSAGE STACK FOLLOWS
RMAN-00571:
RMAN-03002: failure of startup command at 07/11/2005 17:18:05
ORA-00205: error in identifying controlfile, check alert log for more info

You can avoid the preceding error messages by using the alternative command STARTUP NOMOUNT:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;

2. Issue the RESTORE CONTROLFILE command so RMAN can copy the control file backups to their default locations specified in the init.ora file:

RMAN> RESTORE CONTROLFILE;

3. After the restore is over, mount the database:

RMAN> ALTER DATABASE MOUNT;

4. Recover the database:

RMAN> RECOVER DATABASE;

5. Because RMAN restores the control files from its backups, you have to open the database with the RESETLOGS option:

RMAN> ALTER DATABASE OPEN RESETLOGS;

RMAN tablespace Point-In-Time-Recovery (TSPITR)

THIS PROCEDURE WILL RECOVER THE TABLESPACE IN AUX INSTANCE FIRST AND TRANSFER DATA TO TARGET IN ONE RMAN STEP
DON'T USE THIS PROCEDURE, IF YOU DON'T WANT TO OVERWRITE TARGET TABLESPACE DATA!!!!

Recover the tablespaces from the database (the target database) by first performing the PITR in a temporary instance called the auxiliary database, which is created solely to serve as the staging area for the recovery of the tablespaces.

Here’s how to use RMAN to perform a TSPITR:

1. Create the auxiliary database. Use a skeleton initialization parameter file for the auxiliary instance along the lines of the following:
db_name=help (this is the target database_name)
db_file_name_convert=('/oraclehome/oradata/target/', '/tmp/')
/* Lets you convert the target database data files to a different name */
log_file_name_convert=('/oraclehome/oradata/target/redo', '/tmp/redo')
/* Lets you convert the target database redo log files to a different name. */
instance_name=aux
control_files=/tmp/control1.ctl
compatible=10.0.2
db_block_size=8192

2. Start up the auxiliary database in the nomount mode:

$ sqlplus /nolog
SQL> CONNECT sys/oracle@aux AS sysdba
SQL> STARTUP NOMOUNT PFILE = /tmp/initaux.ora

3. Generate some archived redo logs and back up the target database. You can use the ALTER SYSTEM SWITCH LOGFILE command to produce the archived redo log files.

4. Connect to all three databases—the catalog, target, and auxiliary databases—as follows:

$ rman target sys/sys_passwd@targetdb catalog rman/rman@rmandb auxiliary system/oracle@aux

5. Perform a TSPITR. If you want to recover until a certain time, for example, you can use the following statement (assuming your NLS_DATE format uses the following format mask: Mon DD YYYY HH24:MI:SS):

RMAN> RECOVER TABLESPACE users UNTIL TIME ('JUN 30 2005 12:00:00');

This is a deceptively simple step, but RMAN performs a number of tasks in this step. It restores the data files in the users tablespace to the auxiliary database and recovers them to the time you specified. It then exports the metadata about the objects in the tablespaces from the auxiliary to the target database. RMAN also uses the SWITCH command to point the control file to the newly recovered data files.

6. Once the recovery is complete, bring the user tablespace online:

$ rman target sys/sys_passwd@targetdb
RMAN> SQL "alter tablespace users online";
RMAN> Exit;

7. Shut down the auxiliary instance and remove all the control files, redo log files, and data files pertaining to the auxiliary database.

RMAN restore and recover a non-archivelog database from a full (cold) backup

Source: http://www.idevelopment.info/

In this case study, (running in no-archivelog mode), any user error or media failure would require a complete database recovery. You can, however, use the SET UNTIL command to recover to different points in time when incrementals are taken. (Keep in mind that in our example, we did not make use of incremental backups!)

NOTE: Because redo logs are not archived, only full and incremental backups (if you were taking incremental backups) are available for restore and recovery.

It is assumed that you have all the configuration files like:

    * Server parameter file (spfile - equivalent of init.ora in 9i)
    * tnsnames.ora
    * listener.ora
    * sqlnet.ora (optional)

are all in their appropriate places. It is also assumed that you can startup the Oracle instance in NOMOUNT MODE and connect from RMAN to the target instance.

The steps are:

   1. If not using a recovery catalog, or if the database name is ambiguous in, you need to start RMAN and set the DBID before restoring the controlfile from autobackup.
   2. Startup database in NOMOUNT mode. (You should have restored the initialization file for database, and listener files [only if connecting over SQLNET].)
   3. Restore controlfile.
   4. Mount the database.
   5. Restore all database files.
   6. Apply all incrementals. (In this example, we are not taking incremental backups, so this step is not required.)
   7. Open database with RESETLOGS mode to re-create the online log files.
   8. You will need to manually add any tempfiles back to the database after recovering the database.

set dbid 2528050866;

connect target /;
startup nomount;

run {
  # -----------------------------------------------------------
  # Uncomment the SET UNTIL command to restore database to the
  # incremental backup taken two days ago.
  # SET UNTIL TIME 'SYSDATE-2';
  # -----------------------------------------------------------
  set controlfile autobackup format for device type disk to '/orabackup1/rman/TARGDB/%F';
  restore controlfile from autobackup;
  alter database mount;
  restore database;
  recover database noredo;
  alter database open resetlogs;
  sql "alter tablespace temp add tempfile ''/u06/app/oradata/TARGDB/temp01.dbf''
      size 500m autoextend on next 500m maxsize 1500m";
}

exit

NOTE: Tempfiles are automatically excluded from RMAN backups. This requires them to be re-added at recovery time.