RollForward from a Cold Backup

As promised earlier here is how I do a Rollforward from a Cold Backup :

FIRST :
This is the status of the Database *BEFORE* the Cold Backup :
SQL>
SQL> col name format a45
SQL> select to_char(sysdate,'DD-MON-RR HH24:MI') from dual;
TO_CHAR(SYSDATE
---------------
22-MAY-07 22:16
SQL>
SQL> select group#, status from v$log order by status;
GROUP# STATUS
---------- ----------------
2 CURRENT
3 INACTIVE
1 INACTIVE
SQL>
SQL> select recid, name, sequence#, to_char(first_time,'DD-MON-RR HH24:MI') ,blocks*block_size
2 from v$archived_log
3 where first_time > trunc(sysdate)
4 order by first_time;
RECID NAME SEQUENCE#
---------- --------------------------------------------- ----------
TO_CHAR(FIRST_T BLOCKS*BLOCK_SIZE
--------------- -----------------
21 C:\OR10G2DB\ARCH\ARC00001_0623282965.001 1
22-MAY-07 22:09 512
22 C:\OR10G2DB\ARCH\ARC00002_0623282965.001 2
22-MAY-07 22:09 175104
SQL>
SQL> select dbid, name, checkpoint_change#, current_scn from v$database;
DBID NAME CHECKPOINT_CHANGE#
---------- --------------------------------------------- ------------------
CURRENT_SCN
-----------
138573118 OR10G2DB 812793
812933
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> spool off
========================================================================
After the shutdown, I do a COLD BACKUP
{including "online" Redo Log files -- I'll explain why later} of the database.
Once the Cold Backup is completed, I proceed to restart the database and execute some transactions :
--------------------------------------------------------------------------------------------------------------------------
SQL>
SQL> col name format a45
SQL> select to_char(sysdate,'DD-MON-RR HH24:MI') from dual;
TO_CHAR(SYSDATE
---------------
22-MAY-07 22:22
SQL>
SQL> select group#, status from v$log order by status;
GROUP# STATUS
---------- ----------------
2 CURRENT
3 INACTIVE
1 INACTIVE
SQL>
SQL> select recid, name, sequence#, to_char(first_time,'DD-MON-RR HH24:MI') ,blocks*block_size
2 from v$archived_log
3 where first_time > trunc(sysdate)
4 order by first_time;
RECID NAME SEQUENCE#
---------- --------------------------------------------- ----------
TO_CHAR(FIRST_T BLOCKS*BLOCK_SIZE
--------------- -----------------
21 C:\OR10G2DB\ARCH\ARC00001_0623282965.001 1
22-MAY-07 22:09 512
22 C:\OR10G2DB\ARCH\ARC00002_0623282965.001 2
22-MAY-07 22:09 175104
SQL>
SQL> select dbid, name, checkpoint_change#, current_scn from v$database;
DBID NAME CHECKPOINT_CHANGE#
---------- --------------------------------------------- ------------------
CURRENT_SCN
-----------
138573118 OR10G2DB 813231
813465
SQL>
SQL> REM Begin Transactions here =================
SQL>
SQL>
SQL> drop table hemant.test_txn_table ;
Table dropped.
SQL>
SQL> create table hemant.test_txn_table as
2 select * from dba_objects
3 union
4 select * from dba_objects
5 /
Table created.
SQL>
SQL> update hemant.test_txn_table
2 set object_id=object_id+1, owner=substr(owner,1,3)'_HKC' ;
51542 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL> create index hemant.test_txn_tbl_ndx on hemant.test_txn_table(owner);
Index created.
SQL>
SQL> delete hemant.test_txn_table
2 where owner = 'SYS_HKC';
24691 rows deleted.
SQL> rollback;
Rollback complete.
SQL>
SQL> update hemant.test_txn_table
2 set object_id=object_id+1, owner=substr(owner,1,3)'_HKC2'
3 where owner = 'SYS_HKC';
24691 rows updated.
SQL> commit;
Commit complete.
SQL>
SQL> create table last_txn_time (txn_time ) as select sysdate from dual;
Table created.
SQL>
SQL> alter system switch logfile;
System altered.
SQL>
SQL> select group#, status from v$log order by status;
GROUP# STATUS
---------- ----------------
2 ACTIVE
3 ACTIVE
1 CURRENT
SQL>
SQL> select recid, name, sequence#, to_char(first_time,'DD-MON-RR HH24:MI') ,blocks*block_size
2 from v$archived_log
3 where first_time > trunc(sysdate)
4 order by first_time;
RECID NAME SEQUENCE#
---------- --------------------------------------------- ----------
TO_CHAR(FIRST_T BLOCKS*BLOCK_SIZE
--------------- -----------------
21 C:\OR10G2DB\ARCH\ARC00001_0623282965.001 1
22-MAY-07 22:09 512
22 C:\OR10G2DB\ARCH\ARC00002_0623282965.001 2
22-MAY-07 22:09 175104
23 C:\OR10G2DB\ARCH\ARC00003_0623282965.001 3
22-MAY-07 22:10 8186880
RECID NAME SEQUENCE#
---------- --------------------------------------------- ----------
TO_CHAR(FIRST_T BLOCKS*BLOCK_SIZE
--------------- -----------------
24 C:\OR10G2DB\ARCH\ARC00004_0623282965.001 4
22-MAY-07 22:22 8120320
25 C:\OR10G2DB\ARCH\ARC00005_0623282965.001 5
22-MAY-07 22:22 8120320
26 C:\OR10G2DB\ARCH\ARC00006_0623282965.001 6
22-MAY-07 22:22 8120320
RECID NAME SEQUENCE#
---------- --------------------------------------------- ----------
TO_CHAR(FIRST_T BLOCKS*BLOCK_SIZE
--------------- -----------------
27 C:\OR10G2DB\ARCH\ARC00007_0623282965.001 7
22-MAY-07 22:23 8120320
28 C:\OR10G2DB\ARCH\ARC00008_0623282965.001 8
22-MAY-07 22:23 8120320
8 rows selected.
SQL>
SQL> select dbid, name, checkpoint_change#, current_scn from v$database;
DBID NAME CHECKPOINT_CHANGE#
---------- --------------------------------------------- ------------------
CURRENT_SCN
-----------
138573118 OR10G2DB 814864
815153
SQL>
SQL> spool off
============================================================================
I have deliberately created an Index and done DELETEs, INSERTs and ROLLBACKs with UPDATEs to generate noticeable Redo (ArchiveLogs)
The ArchiveLog file is at least ARC00008 (we shall see later that there were two more Archives before the shutdown).

I next restore the Cold Backup _including_ the "online" Redo Log file to verify that the Cold Backup is consistent :
-------------------------------------------------------------------------------------------------------------------------------
SQL>
SQL> col name format a45
SQL> select to_char(sysdate,'DD-MON-RR HH24:MI') from dual;
TO_CHAR(SYSDATE
---------------
22-MAY-07 22:42
SQL>
SQL> select group#, status from v$log order by status;
GROUP# STATUS
---------- ----------------
2 CURRENT
3 INACTIVE
1 INACTIVE
SQL>
SQL> select recid, name, sequence#, to_char(first_time,'DD-MON-RR HH24:MI') ,blocks*block_size
2 from v$archived_log
3 where first_time > trunc(sysdate)
4 order by first_time;
RECID NAME SEQUENCE#
---------- --------------------------------------------- ----------
TO_CHAR(FIRST_T BLOCKS*BLOCK_SIZE
--------------- -----------------
21 C:\OR10G2DB\ARCH\ARC00001_0623282965.001 1
22-MAY-07 22:09 512
22 C:\OR10G2DB\ARCH\ARC00002_0623282965.001 2
22-MAY-07 22:09 175104
SQL>
SQL> select dbid, name, checkpoint_change#, current_scn from v$database;
DBID NAME CHECKPOINT_CHANGE#
---------- --------------------------------------------- ------------------
CURRENT_SCN
-----------
138573118 OR10G2DB 813231
813379
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> spool off
============================================================================
As you can see, the ControlFile that is restored (from the Cold Backup) is NOT aware of ArchiveLogs beyond ARC00002. Checkpoint SCNs and "Current SCN"s are always incremented at every startup and shutdown so this listing of 813231 and 813379 is slightly ahead of the first listing of 813231 and 813465 (ie the SCN got incremented after the query on v$database and again possibly incremented during the OPEN)
As I have actually incremented SCNs and OPENed the database, I cannot really use this copy. I only did an OPEN to verify that the Restore was consistent and usable.
I will now Restore the Cold Backup *again*.
------------------------------------------------------------------------------------------------------------------------------
Whether I restore or without the "online" Redo Log files now, it doesn't matter --
because I will be doing an OPEN RESETLOGS that is necessitated by the USING BACKUP CONTROLFILE.
---------------------------------------------------------------------------------------------------------------------------------
Here is how I do the RollForward : Notice how the Rollforward is from ARC00003 onwards.
The "using backup controlfile" is important !
See my posting at http://www.freelists.org/archives/oracle-l/05-2007/msg00440.html
---------------------------------------------------------------------------------------------------------------------------------
SQL> startup mount
ORACLE instance started.
Total System Global Area 83886080 bytes
Fixed Size 1247420 bytes
Variable Size 54527812 bytes
Database Buffers 25165824 bytes
Redo Buffers 2945024 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 813230 generated at 05/22/2007 22:16:15 needed for thread 1
ORA-00289: suggestion : C:\OR10G2DB\ARCH\ARC00003_0623282965.001
ORA-00280: change 813230 for thread 1 is in sequence #3

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 813860 generated at 05/22/2007 22:22:49 needed for thread 1
ORA-00289: suggestion : C:\OR10G2DB\ARCH\ARC00004_0623282965.001
ORA-00280: change 813860 for thread 1 is in sequence #4
ORA-00278: log file 'C:\OR10G2DB\ARCH\ARC00003_0623282965.001' no longer needed
for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 814287 generated at 05/22/2007 22:22:50 needed for thread 1
ORA-00289: suggestion : C:\OR10G2DB\ARCH\ARC00005_0623282965.001
ORA-00280: change 814287 for thread 1 is in sequence #5
ORA-00278: log file 'C:\OR10G2DB\ARCH\ARC00004_0623282965.001' no longer needed
for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 814599 generated at 05/22/2007 22:22:59 needed for thread 1
ORA-00289: suggestion : C:\OR10G2DB\ARCH\ARC00006_0623282965.001
ORA-00280: change 814599 for thread 1 is in sequence #6
ORA-00278: log file 'C:\OR10G2DB\ARCH\ARC00005_0623282965.001' no longer needed
for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 814620 generated at 05/22/2007 22:23:01 needed for thread 1
ORA-00289: suggestion : C:\OR10G2DB\ARCH\ARC00007_0623282965.001
ORA-00280: change 814620 for thread 1 is in sequence #7
ORA-00278: log file 'C:\OR10G2DB\ARCH\ARC00006_0623282965.001' no longer needed
for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 814656 generated at 05/22/2007 22:23:06 needed for thread 1
ORA-00289: suggestion : C:\OR10G2DB\ARCH\ARC00008_0623282965.001
ORA-00280: change 814656 for thread 1 is in sequence #8
ORA-00278: log file 'C:\OR10G2DB\ARCH\ARC00007_0623282965.001' no longer needed
for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 814864 generated at 05/22/2007 22:23:07 needed for thread 1
ORA-00289: suggestion : C:\OR10G2DB\ARCH\ARC00009_0623282965.001
ORA-00280: change 814864 for thread 1 is in sequence #9
ORA-00278: log file 'C:\OR10G2DB\ARCH\ARC00008_0623282965.001' no longer needed
for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 815052 generated at 05/22/2007 22:23:12 needed for thread 1
ORA-00289: suggestion : C:\OR10G2DB\ARCH\ARC00010_0623282965.001
ORA-00280: change 815052 for thread 1 is in sequence #10
ORA-00278: log file 'C:\OR10G2DB\ARCH\ARC00009_0623282965.001' no longer needed
for this recovery

Specify log: {=suggested filename AUTO CANCEL}
ORA-00279: change 815152 generated at 05/22/2007 22:23:14 needed for thread 1
ORA-00289: suggestion : C:\OR10G2DB\ARCH\ARC00011_0623282965.001
ORA-00280: change 815152 for thread 1 is in sequence #11
ORA-00278: log file 'C:\OR10G2DB\ARCH\ARC00010_0623282965.001' no longer needed
for this recovery

Specify log: {=suggested filename AUTO CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select to_char(txn_time,'DD-MON-RR HH24:MI:SS') from last_txn_time;
TO_CHAR(TXN_TIME,'
------------------
22-MAY-07 22:23:12
SQL> select group#, status from v$log order by status;
GROUP# STATUS
---------- ----------------
2 CURRENT
3 UNUSED
1 UNUSED
SQL>
SQL> select recid, name, sequence#, to_char(first_time,'DD-MON-RR HH24:MI') ,blocks*block_size
2 from v$archived_log
3 where first_time > trunc(sysdate)
4 order by first_time;
RECID
----------
NAME
--------------------------------------------------------------------------------
SEQUENCE# TO_CHAR(FIRST_T BLOCKS*BLOCK_SIZE
---------- --------------- -----------------
25
C:\OR10G2DB\ARCH\ARC00001_0623282965.001
1 22-MAY-07 22:09 512
21
C:\OR10G2DB\ARCH\ARC00001_0623282965.001
1 22-MAY-07 22:09 512
RECID
----------
NAME
--------------------------------------------------------------------------------
SEQUENCE# TO_CHAR(FIRST_T BLOCKS*BLOCK_SIZE
---------- --------------- -----------------
22
C:\OR10G2DB\ARCH\ARC00002_0623282965.001
2 22-MAY-07 22:09 175104
23
C:\OR10G2DB\ARCH\ARC00002_0623282965.001
RECID
----------
NAME
--------------------------------------------------------------------------------
SEQUENCE# TO_CHAR(FIRST_T BLOCKS*BLOCK_SIZE
---------- --------------- -----------------
2 22-MAY-07 22:09 175104
24
C:\OR10G2DB\ARCH\ARC00003_0623282965.001
3 22-MAY-07 22:10 373760
SQL> select dbid, name, checkpoint_change#, current_scn from v$database;
DBID NAME CHECKPOINT_CHANGE# CURRENT_SCN
---------- --------- ------------------ -----------
138573118 OR10G2DB 815154 815445
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
============================================================================
During the RollForward I applied ArchiveLogs upto ARC00010. I then queried my data
(select to_char(txn_time,'DD-MON-RR HH24:MI:SS') from last_txn_time or
I could have queried "test_txn_table" as well) and verified that I do
have the data created by my transactions *after* the Cold Backup.
You can also see that also Log Sequence Numbers have got Reset, the SCNs
do *NOT* get Reset. 10g's "new" feature about "roll-forward through resetlogs"
is because archivelog filenames contain a Reset_ID as well -- nothing to
do with SCNs. SCNs always are incremented (SCNs like time can only go in
one direction -- although we can actually use Server OS commands to reset
Server Clocks, Oracle relies on SCNs, not timestamps, for ordering transactions).

NOTE : This method of "RollForward" recovery is also covered in Oracle MetaLink Note#271916.1