Recover database

RECOVER (SQL Plus command) repost

Recover a Database, Tablespace, Data or Log file.

Syntax:

Full recovery:
  
   RECOVER [AUTOMATIC] [FROM 'location']
      [STANDBY] DATABASE 
         [UNTIL CANCEL] | [UNTIL TIME date] | [UNTIL CHANGE int] | [USING BACKUP CONTROLFILE]
              [TEST | ALLOW int CORRUPTION ]

   RECOVER [AUTOMATIC] [FROM 'location']
      [STANDBY] DATABASE 
         CANCEL

Tablespace recovery:

   RECOVER [AUTOMATIC] [FROM 'location']
      TABLESPACE tablespace [, tablespace2...]
         [TEST | ALLOW int CORRUPTION ]

   RECOVER [AUTOMATIC] [FROM 'location']
      STANDBY TABLESPACE tablespace [, tablespace2...]
         UNTIL [CONSISTENT] [WITH] CONTROLFILE
           [TEST | ALLOW int CORRUPTION ]

   RECOVER [AUTOMATIC] [FROM 'location']
      [STANDBY] TABLESPACE tablespace [, tablespace2...] CANCEL

Datafile Recovery:

   RECOVER [AUTOMATIC] [FROM 'location']
      DATAFILE 'filename' [, filename2...]
         [TEST | ALLOW int CORRUPTION ]

   RECOVER [AUTOMATIC] [FROM 'location']
      STANDBY DATAFILE 'filename' [, filename2,...]
         UNTIL [CONSISTENT] [WITH] CONTROLFILE
            [TEST | ALLOW int CORRUPTION ]

   RECOVER [AUTOMATIC] [FROM 'location']
      [STANDBY] DATAFILE 'filename' [, filename2...] CANCEL

Continue media recovery by applying a specific redo log file

   RECOVER [AUTOMATIC] [FROM 'location']
      LOGFILE 'filename'

Managed Standby:

   RECOVER MANAGED STANDBY DATABASE 
   RECOVER MANAGED STANDBY DATABASE NODELAY
   RECOVER MANAGED STANDBY DATABASE TIMEOUT int
   RECOVER MANAGED STANDBY DATABASE DISCONNECT [FROM SESSION] ] [FINISH [NOWAIT] ]
   RECOVER MANAGED STANDBY DATABASE CANCEL [IMMEDIATE] [NOWAIT]}  

Parallel option - add to any of the above:

PARALLEL int

Parallel media recovery allows you to use multiple CPU's /processes to recover a single instance.

To make the recovery more efficient, Oracle will allocate each process to a different data block while rolling forward.

For example, if parallel recovery is performed with PARALLEL 4, and only one datafile is recovered, then four spawned processes read blocks from the datafile and apply records instead of only one process.

Key:

AUTOMATIC Automatically generate the name of the next archived
          redo log file needed to continue the recovery operation.
FROM location
          The location from which the archived redo log file group is read.
STANDBY
          Recover the standby database using the control file and archived
          redo log files copied from the primary database. 
          The standby database must be mounted but not open.
UNTIL CANCEL
          Partial recovery. Recovery proceeds by prompting you with the 
          suggested filenames of archived redo log files, and recovery completes
          when you specify CANCEL instead of a filename.
UNTIL CHANGE int
          An incomplete, change-based recovery. integer is the System Change Number
          (SCN) following the last change you wish to recover.
UNTIL TIME date
          Partial time-based recovery. Use the format:
           'YYYY-MM-DD:HH24:MI:SS'
UNTIL [CONSISTENT] [WITH] CONTROLFILE
          Recover an old standby datafile or tablespace using the current
          standby database control file.
TABLESPACE tablespace
          Recover a tablespace.
          You may recover up to 16 tablespaces in one statement.
CONTINUE [DEFAULT]
          Continues multi-instance recovery after it has been
          interrupted to disable a thread.
          Continues recovery using the redo log file that Oracle
          would automatically generate if no other logfile were specified.
          This option is equivalent to specifying AUTOMATIC, except that Oracle
          does not prompt for a filename.
CANCEL
          Terminates cancel-based recovery.
MANAGED STANDBY DATABASE
          Sustained standby recovery mode. 
TIMEOUT int
          The number of MINUTES = the wait period of a sustained recovery operation.

Because of possible network timeouts, it is recommended that you use SQL*Plus command-line for long running DBA operations such as RECOVER.

The RECOVER command is available in SQL*Plus, (the syntax ALTER DATABASE RECOVER... is provided for backwards compatibility with older versions)

Related Commands:

RMAN Recover
ALTER DATABASE
Recovery - FAQ

Related Views:

 GLOBAL_NAME
 DBA_DATA_FILES
 V$CONTROLFILE
 V$DATABASE
 V$DATAFILE
 V$LOG
 V$LOGFILE