Q?
What is the LEAST amount of recovery that has to be done before a database restored from a hot rman backup can be opened?
A:
ALL archivelogs from backup start to backup end must be applied before the database can be opened - this is the MINIMUM amount of recovery needed.
To determine which log was current at the time the backup completed, note the COMPLETION time of the rman database backup - take this from the backup log.
If the backup log is not longer available query the rman metadata but make sure you set NLS_DATE_FORMAT before invoking rman so that timestamps as well as date are returned:
- for unix: %export NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'
%rman target /
- for windows: >set nls_date_format=dd-mon-rr:hh24
>rman target /
To find your backup:
RMAN>LIST BACKUP OF DATABASE COMPLETED AFTER '<date>';
or
RMAN>LIST BACKUP OF DATABASE COMPLETED AFTER 'sysdate -n';
Set <date> to limit the output to the backups that you are interested and note the completion time - for a multi-piece backup, note the completion time of the LAST backuppiece created.
Lets say, the backup completed 04-Jun-11 13:13:05 query the controlfile:
SQL>ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE '04-JUN-11 13:13:05' BETWEEN FIRST_TIME AND NEXT_TIME;
If the above query does not return any rows, it may be that the information has aged out of the controlfile - run the same query against v$log_history.
The sequence# returned by the above query is the log sequence current at the time the backup ended - let say 530 thread 1. For minimum recovery use:
RMAN>RUN { SET UNTIL SEQUENCE 531 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
If this is a RAC implementation the use this SQL instead to query the controlfile:
SQL>SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG WHERE '04-JUN-11 13:13:05' BETWEEN FIRST_TIME AND NEXT_TIME;
For minimum recovery use the log sequence and thread that has the lowest NEXT_CHANGE# returned by the above query.
Reference : How to determine minimum end point for recovery of an RMAN backup (Doc ID 1329415.1)
A:
ALL archivelogs from backup start to backup end must be applied before the database can be opened - this is the MINIMUM amount of recovery needed.
To determine which log was current at the time the backup completed, note the COMPLETION time of the rman database backup - take this from the backup log.
If the backup log is not longer available query the rman metadata but make sure you set NLS_DATE_FORMAT before invoking rman so that timestamps as well as date are returned:
- for unix: %export NLS_DATE_FORMAT='dd-mon-rr hh24:mi:ss'
%rman target /
- for windows: >set nls_date_format=dd-mon-rr:hh24
>rman target /
To find your backup:
RMAN>LIST BACKUP OF DATABASE COMPLETED AFTER '<date>';
or
RMAN>LIST BACKUP OF DATABASE COMPLETED AFTER 'sysdate -n';
Set <date> to limit the output to the backups that you are interested and note the completion time - for a multi-piece backup, note the completion time of the LAST backuppiece created.
Lets say, the backup completed 04-Jun-11 13:13:05 query the controlfile:
SQL>ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR HH24:MI:SS';
SQL> SELECT THREAD#, SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE '04-JUN-11 13:13:05' BETWEEN FIRST_TIME AND NEXT_TIME;
If the above query does not return any rows, it may be that the information has aged out of the controlfile - run the same query against v$log_history.
The sequence# returned by the above query is the log sequence current at the time the backup ended - let say 530 thread 1. For minimum recovery use:
RMAN>RUN { SET UNTIL SEQUENCE 531 THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
If this is a RAC implementation the use this SQL instead to query the controlfile:
SQL>SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG WHERE '04-JUN-11 13:13:05' BETWEEN FIRST_TIME AND NEXT_TIME;
For minimum recovery use the log sequence and thread that has the lowest NEXT_CHANGE# returned by the above query.
Reference : How to determine minimum end point for recovery of an RMAN backup (Doc ID 1329415.1)
