Oracle DBA – A lifelong learning experience

Why do you need to resetlogs after a cold backup restore

Posted by John Hallas on October 19, 2015

I posted a routine on how to take a cold backup locally to disk and then restore it back in 2010. Last week I was asked in a comment ‘why did you have to open the database using resetlogs?’  A very good question I thought so I proceeded to backup and recover just as the blog showed and I now know why.

Because Oracle will not let you do otherwise

Let me run through the example again and I will add a bit of commentary.

The original blog entry was https://jhdba.wordpress.com/2010/03/22/recovering-from-a-cold-backup-using-rman/

The basis of the commands were

startup nomount
run
 {
 allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
 restore controlfile from ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_MOMPRE1A_S_37_P_1_T_713884663’;
 }
alter database mount;
run
 {
 allocate channel c1 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
 allocate channel c2 device type disk format ‘/staging/oretail/KEEP_UNTIl_15APRIL2010/backup_db_%d_S_%s_P_%p_T_%t’;
 restore database tag=’rman_backfulcold_MOMPREPERTEST’;
 }
–YOU DO NOT NEED TO RECOVER DATABASE otherwise it rolls forward to the current time
alter database open resetlogs;

As you can see I open the database with a resetlogs and yet it is a completely clean recovery and I should be able to do a noresetlogs.

Let’s go through a similar example very quickly.

 

startup mount
run
 {
 allocate channel c1 device type disk format '/testmaster_data/KEEP_UNTIL_JOHN/backup_db_%d_S_%s_P_%p_T_%t';
 allocate channel c2 device type disk format '/testmaster_data/KEEP_UNTIL_JOHN/backup_db_%d_S_%s_P_%p_T_%t';
 backup full
 tag rman_backfulcold_TST11204
 (database include current controlfile);
 }

 

List the backups to see what we have got

RMAN> List backup summary;

903     B  F  A DISK        2015-10-15:13:18:22 1       1       NO         RMAN_BACKFULCOLD_TST11204
904     B  F  A DISK        2015-10-15:13:18:29 1       1       NO         TAG20151015T131828


RMAN> list backupset 903;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
903     Full    12.40G     DISK        00:12:49     2015-10-15:13:18:22
        BP Key: 903   Status: AVAILABLE  Compressed: NO  Tag: RMAN_BACKFULCOLD_TST11204
        Piece Name: /testmaster_data/KEEP_UNTIL_JOHN/backup_db_TST11204_S_914_P_1_T_893163933
  List of Datafiles in backup set 903
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  2       Full 10931985470038 2015-10-15:13:04:20 +DATA/tst11204/datafile/sysaux.257.851337533
  3       Full 10931985470038 2015-10-15:13:04:20 +DATA/tst11204/datafile/undotbs1.258.851337533
  4       Full 10931985470038 2015-10-15:13:04:20 +DATA/tst11204/datafile/users.259.851337533

RMAN> list backupset 904;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
904     Full    11.23M     DISK        00:00:01     2015-10-15:13:18:29
        BP Key: 904   Status: AVAILABLE  Compressed: NO  Tag: TAG20151015T131828
        Piece Name: +FRA/tst11204/autobackup/2015_10_15/s_893163860.3783.893164709
  Control File Included: Ckp SCN: 10931985470038   Ckp time: 2015-10-15:13:04:20
  SPFILE Included: Modification time: 2015-10-15:13:05:29
  SPFILE db_unique_name: TST11204
 

I now restore the controlfile from autobackup, startup mount and restore from the cold backup

RMAN> run
 {
 allocate channel c1 device type disk format '/testmaster_data/KEEP_UNTIL_JOHN/backup_db_%d_S_%s_P_%p_T_%t';
 restore controlfile from autobackup;
 }2> 3> 4> 5>
allocated channel: c1
 channel c1: SID=10 device type=DISK
Starting restore at 2015-10-15:15:19:00
recovery area destination: +FRA
 database name (or database unique name) used for search: TST11204
 channel c1: AUTOBACKUP +FRA/TST11204/AUTOBACKUP/2015_10_15/s_893170083.1419.893171427 found in the recovery area
 AUTOBACKUP search with format "%F" not attempted because DBID was not set
 channel c1: restoring control file from AUTOBACKUP +FRA/TST11204/AUTOBACKUP/2015_10_15/s_893170083.1419.893171427
 channel c1: control file restore from AUTOBACKUP complete
 output file name=+DATA/tst11204/controlfile/current.260.851337899
 Finished restore at 2015-10-15:15:19:03
 released channel: c1
RMAN> startup mount;
database is already started
 database mounted
RMAN>
 run
 {
 allocate channel c1 device type disk format '/testmaster_data/KEEP_UNTIL_JOHN/backup_db_%d_S_%s_P_%p_T_%t';
 allocate channel c2 device type disk format '/testmaster_data/KEEP_UNTIL_JOHN/backup_db_%d_S_%s_P_%p_T_%t';
 restore database;
 }
 RMAN> 2> 3> 4> 5> 6>

I now try to open the database uisng NORESETLOGS

RMAN> alter database open noresetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;"
RMAN-01008: the bad identifier was: noresetlogs
RMAN-01007: at line 1 column 21 file: standard input

RMAN>  alter database open resetlogs;

database opened

The default syntax for the ALTER DATABASE OPEN is READ, WRITE, NORESETLOGS and yet the command is not even allowed when you have recovered using a backup controlfile. You may think there was a mistype in the command above that caused it to fail but I and other DBAs typed it in several times and tried every permutation that exists. it is only when you type in RESETLOGS that the database takes the command as being valid.

I did wonder whether the autobackup was the problem so I tried a direct recovery of the controlfile from the backup piece

RMAN> restore controlfile from '+FRA/tst11204/autobackup/2015_10_15/s_893163860.3783.893164709';
RMAN> restore database ......
RMAN> 
RMAN> alter database open noresetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "resetlogs, ;"
RMAN-01008: the bad identifier was: norestlogs
RMAN-01007: at line 1 column 21 file: standard input

RMAN> alter database open resetlogs;

database opened

RMAN>

Pretty much the same result  – Oracle will not allow a NORESETLOGS to happen. So why is this? From the documentation on the ALTER DATABASE OPEN command

RESETLOGS | NORESETLOGS
========================
This clause determines whether Oracle Database resets the current log sequence number to 1, archives any unarchived logs (including the current log), and discards any redo information that was not applied during recovery, ensuring that it will never be applied. Oracle Database uses NORESETLOGS automatically except in the following specific situations, which require a setting for this clause:

You must specify RESETLOGS:

After performing incomplete media recovery or media recovery using a backup
controlfile

There is a note on Metalink 1077022.1 which shows how you can get around it which I show below. To be honest I am not sure when you would need it but here it is anyway.

 
      RMAN> restore controlfile......
      RMAN> mount database ;
      RMAN> restore database ;
  • Copy the online redo logs to the desired location for new database.
  • Login to SQL*Plus and generate controlfile trace script ( please note that the database is mounted from rman after restoring controlfile ) :
      SQL> alter database backup controlfile to trace 
            NORESETLOGS as '/tmp/ctl.sql' ;
      SQL> SHUTDOWN IMMEDIATE
  • Edit the controlfile if required. For example, to change the location of online redo logs copied.
  • Shutdown and STARTUP NOMOUNT the database and run the create controlfile script :
         SQL> STARTUP NOMOUNT
         SQL> @/tmp/ctl.sql
  •  Recover the database and open normal :
SQL> RECOVER DATABASE ;
 SQL> ALTER DATABASE OPEN ;

It is years since I ran a create controlfile script – the duplicate database option from RMAN has removed the need for that most of the time.

Another note referring to ORA-01610  – Doc ID 19007.1 also reiterates that when recovery has used a backup controlfile then a resetlogs is mandatory.

 

Advertisements

5 Responses to “Why do you need to resetlogs after a cold backup restore”

  1. […] Oct 2015. I have added a supplementatry post to this blog about why RESETLOGS is necessary. This came from a  comment made on this post.  https://jhdba.wordpress.com/2015/10/19/why-do-you-need-to-resetlogs-after-a-cold-backup-restore/ […]

  2. fouedgray said

    Thanks John for these insights,
    Kind regards,
    Foued

  3. vkaminsky said

    Interesting topic to discuss. I believe the root cause may be in the reincarnation count:
    a. When you do RMAN duplicate, you are creating new DB with new DBID and reincarnation is set to 1.
    b. When database is restored from any kind of backup, the DBID is not changed and as such RMAN needs to know which “base” reincarnation it should be using for future references and forces you to resetlog.
    c. When creating (cloning) databases using storage level snapshots, the new database does not have to go through resetlog because there are no material changes to the data or SCNs.

  4. Nelson Petersen said

    There is a small typographical error in your second attempt:
    “alter database open norestlogs;” instead of
    “alter database open noresetlogs;”
    It does not sound like it would make any difference.

    Thanks for your blog. Lots of interesting entries. I bookmarked your site.

    • John Hallas said

      Thnaks Nelson, I will update the entry but as you say, I doubt it will make a difeference because I tried that test many times before I realised that it would not work. Glad you enjoyed the blog posts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: