Change-based recovery allows the DBA to recover to a desired point of System change numner (SCN). This situation is most likely to occur if archive logfiles or redo logfiles needed for recovery are lost or damaged and cannot be restored.
From SQL*Plus as SYS get the last archived SCN using the following.
SQL> SELECT archivelog_change#-1 FROM v$database;
ARCHIVELOG_CHANGE#-1
——————–
7536295
Steps:
– If the database is still open, shut down the database using the SHUTDOWN command with the ABORT option.
– Make a full backup of the database including all datafiles, a control file, and the parameter files in case an error is made during the recovery.
– Restore backups of all datafiles. Make sure the backups were taken before the point in time you are going to recover to. Any datafiles added after the point in time you are recovering to should not be restored. They will not be used in the recovery and will have to be recreated after recovery is complete. Any data in the datafiles created after the point of recovery will be lost.
– Make sure read-only tablespace are offline before you start recovery so recovery does not try to update the datafile headers.
Now Connect the rman with the target database and run the following command:
RMAN TARGET SYS/[email protected] CATALOG RMAN/[email protected]
RMAN> shutdown Abort;
RMAN> startup mount;
RMAN>run {
set until scn 1048438;
restore database;
recover database;
alter database open resetlogs;
}
How to find Exact SCN for Oracle Restore
1. If you have a controlfile mounted then you can query the latest backup details, archivelog details and find the exact SCN to use in your recovery script.
select max(absolute_fuzzy_change#) fuzz#, max(checkpoint_change#) chkpnt#
from (select file#, completion_time, checkpoint_change#, absolute_fuzzy_change#
from v$backup_datafile
where incremental_level = 0
and trunc(completion_time) = to_date(‘Nov-28-2012′,’MON-DD-YYYY’)
and file# <> 0
order by completion_time desc
);
2. Either You can choose the day in which your level0 or level1 completed and modify appropriately.
select sequence#, to_date(first_time,’DD-MON-YYYY HH24:MI:SS’) first_time, first_change#,
to_date(next_time,’DD-MON-YYYY HH24:MI:SS’) next_time, next_change#
from v$archived_log
where completion_time between to_date(‘Nov-27-2012′,’MON-DD-YYYY’) and Sysdate;
Note: This will return 2 SCN numbers. Pick the greatest of the 2.
3. Or, since checkpoints occur at every log switch, You can then choose an SCN from the exact log switch closest to your recovery needs.
select sequence#, to_date(first_time,’DD-MON-YYYY HH24:MI:SS’) first_time, first_change#,
to_date(next_time,’DD-MON-YYYY HH24:MI:SS’) next_time, next_change# from v$archived_log
where completion_time between to_date(‘Nov-27-2012′,’MON-DD-YYYY’) and Sysdate ;
4. You can use logMinor to find Exact SCN prior to Crash.
BEGIN
DBMS_LOGMNR.add_logfile (
options => DBMS_LOGMNR.new,
logfilename => ‘/tmp/a55050.arc’);
END;
/
BEGIN
DBMS_LOGMNR.start_logmnr (
starttime => to_date(’10-MAR-2010 18:50:00′,’DD-MON-YYYY HH24:MI:SS’),
endtime => to_date(’10-MAR-2010 19:05:00′,’DD-MON-YYYY HH24:MI:SS’),
options => Dbms_Logmnr.DDL_Dict_Tracking);
END;
/
Now Query with v$logmnr_contents Table and find the exact SCN
SELECT scn, to_char(timestamp,’DD-MON-YYYY HH24:MI:SS’) timest, operation, sql_redo
FROM v$logmnr_contents
where scn between 21822207692 and 21822211410
order by scn;
Query to find the SCN for Incomplete Recovery After Restore
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#);
Note: You can also examine the alert.log to find the SCN of an event and recover to a prior SCN
if you follow the above steps, you will always be able to “alter database open resetlogs” without error.
