Recreating and restore controlfiles

Controlfile contains important information regarding instance and media recovery and here is how to understand its important role in several scenarios.

My environment:

OS:  Redhat Linux 6.3 x86_64
DB:  Oracle 11.2.0.3 Single instance

1 Control file contains backup information.

[oracle@bcmsdbs01 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 9 10:08:48 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/oraapp/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_31037.trc
SQL> 
SQL> -- dump controlfile content to trace 
SQL> alter session set events 'immediate trace name controlf level 8';

Session altered.

Controlfile dump:

***************************************************************************
BACKUP SET RECORDS
***************************************************************************
 (size = 40, compat size = 40, section max = 409, section in-use = 3,
  last-recid= 3, old-recno = 1, last-recno = 3)
 (extent = 1, blkno = 99, numrecs = 409)
Earliest record:
 RECID #1 Recno 1 Record timestamp  11/09/16 10:00:24
  Backup set key: stamp=927453623, count=2
  Backup status:
  Backup contains:   
  Flags:
  Blocksize=16384 Piece-Count=1 Level=0 Time:
  Backup does not have keep options.
Latest record:
 RECID #3 Recno 3 Record timestamp  11/09/16 10:03:40
  Backup set key: stamp=927453819, count=4
  Backup status:
  Backup contains:   
  Flags:
  Blocksize=16384 Piece-Count=1 Level=0 Time:
  Backup does not have keep options.
 RECID #2 Recno 2 Record timestamp  11/09/16 10:03:36
  Backup set key: stamp=927453734, count=3
  Backup status:
  Backup contains: 
  Flags:
  Blocksize=8192 Piece-Count=1 Level=0 Time:
  Backup does not have keep options.
 RECID #1 Recno 1 Record timestamp  11/09/16 10:00:24
  Backup set key: stamp=927453623, count=2
  Backup status:
  Backup contains:   
  Flags:
  Blocksize=16384 Piece-Count=1 Level=0 Time:
  Backup does not have keep options.



***************************************************************************
BACKUP PIECE RECORDS
***************************************************************************
 (size = 736, compat size = 736, section max = 200, section in-use = 3,
  last-recid= 3, old-recno = 1, last-recno = 3)
 (extent = 1, blkno = 100, numrecs = 200)
Earliest record:
 RECID #1 Recno 1 Record timestamp  11/09/16 10:00:24 piece #1  copy #1 pool 0
  Backup set key: stamp=927453623, count=2
  V$RMAN_STATUS: recid=140734845125304, stamp=140734845125296
  Flags:  
  Device: DISK
  Handle: /home/oracle/backup/data_02rkfkdn_1_1
  Media-Handle:
  Comment:
  Tag: TAG20161109T100022
  Completion time  11/09/16 10:00:24
Latest record:
 RECID #3 Recno 3 Record timestamp  11/09/16 10:03:40 piece #1  copy #1 pool 0
  Backup set key: stamp=927453819, count=4
  V$RMAN_STATUS: recid=140734845125304, stamp=140734845125296
  Flags:  
  Device: DISK
  Handle: /home/oracle/backup/data_04rkfkjr_1_1
  Media-Handle:
  Comment:
  Tag: TAG20161109T100213
  Completion time  11/09/16 10:03:40
 RECID #2 Recno 2 Record timestamp  11/09/16 10:02:14 piece #1  copy #1 pool 0
  Backup set key: stamp=927453734, count=3
  V$RMAN_STATUS: recid=140734845125304, stamp=140734845125296
  Flags:  
  Device: DISK
  Handle: /home/oracle/backup/data_03rkfkh6_1_1
  Media-Handle:
  Comment:
  Tag: TAG20161109T100213
  Completion time  11/09/16 10:03:36
 RECID #1 Recno 1 Record timestamp  11/09/16 10:00:24 piece #1  copy #1 pool 0
  Backup set key: stamp=927453623, count=2
  V$RMAN_STATUS: recid=140734845125304, stamp=140734845125296
  Flags:  
  Device: DISK
  Handle: /home/oracle/backup/data_02rkfkdn_1_1
  Media-Handle:
  Comment:
  Tag: TAG20161109T100022
  Completion time  11/09/16 10:00:24

list backup output in RMAN:

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
1       Full    1.05M      DISK        00:00:01     2016-11-09 10:00:24
        BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: TAG20161109T100022
        Piece Name: /home/oracle/backup/data_02rkfkdn_1_1
  SPFILE Included: Modification time: 2016-09-16 22:00:21
  SPFILE db_unique_name: TESTDB
  Control File Included: Ckp SCN: 22299823     Ckp time: 2016-11-09 10:00:23

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
2       Full    255.07M    DISK        00:01:22     2016-11-09 10:03:36
        BP Key: 2   Status: AVAILABLE  Compressed: YES  Tag: TAG20161109T100213
        Piece Name: /home/oracle/backup/data_03rkfkh6_1_1
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 22300240   2016-11-09 10:02:14 /home/oracle/oradata/testdb/system01.dbf
  2       Full 22300240   2016-11-09 10:02:14 /home/oracle/oradata/testdb/sysaux01.dbf
  3       Full 22300240   2016-11-09 10:02:14 /home/oracle/oradata/testdb/undotbs01.dbf
  4       Full 22300240   2016-11-09 10:02:14 /home/oracle/oradata/testdb/users01.dbf
  5       Full 22300240   2016-11-09 10:02:14 /home/oracle/oradata/testdb/zq01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
3       Full    1.05M      DISK        00:00:01     2016-11-09 10:03:40
        BP Key: 3   Status: AVAILABLE  Compressed: YES  Tag: TAG20161109T100213
        Piece Name: /home/oracle/backup/data_04rkfkjr_1_1
  SPFILE Included: Modification time: 2016-11-09 10:01:26
  SPFILE db_unique_name: TESTDB
  Control File Included: Ckp SCN: 22300601     Ckp time: 2016-11-09 10:03:39

2 Recreating controlfile scenario

1 dump controlfile
2 alter system checkpoint;
3 shutdown immediate;
4 recreate controlfile with NORESETLOGS option
5 alter database open; -> succeeded, which means checkpoint cnt and scn are consistent for each datafile

PS: with RESETLOGS option, the database must be opened with RESETLOGS.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

In addition, if you shutdown abort and recreate controlfile afterwards, open database would remind you to conduct media recovery since low cache rba and on disk SCN info was lost in controlfile.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'

***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
 (size = 8180, compat size = 8180, section max = 11, section in-use = 0,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00

and we need to recover database:

SQL> recover database;
Media recovery complete.
SQL> 
SQL> 
SQL> alter database open;

Database altered.

3 Recover database using backup controlfile

Shutdown immediate and restore controlfile from backup:

-- checkpoint info in controlfile
SQL> select file#, checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1           22300240
         2           22300240
         3           22300240
         4           22300240
         5           22300240

-- checkpoint info in disk header
SQL> select file#,fuzzy, name, checkpoint_change# from v$datafile_header;

     FILE# FUZ NAME                                               CHECKPOINT_CHANGE#
---------- --- -------------------------------------------------- ------------------
         1 NO  /home/oracle/oradata/testdb/system01.dbf                     22322908
         2 NO  /home/oracle/oradata/testdb/sysaux01.dbf                     22322908
         3 NO  /home/oracle/oradata/testdb/undotbs01.dbf                    22322908
         4 NO  /home/oracle/oradata/testdb/users01.dbf                      22322908
         5 NO  /home/oracle/oradata/testdb/zq01.dbf                         22322908

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

At this point, we don’t have current controlfile therefore Oracle does NOT know the SCN to stop applying recovery.

Meanwhile, recover database would fail with the following error (Alternatively, you can use until time clause). The reason is we opened the database in previous RESETLOGS option and the datafiles belong to incarnation 2.

SQL> recover database until cancel using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '/home/oracle/oradata/testdb/system01.dbf'

The solution would be manually create controlfile and open with resetlogs option. Alternatively, you could recover database using backup controlfile to rollforward the database to a particular point of time. Afterwards, you must open database with RESETLOGS option.

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 22366297 generated at 11/09/2016 15:56:07 needed for thread 1
ORA-00289: suggestion : /home/oracle/oradata/arch/1_6_927458140.dbf
ORA-00280: change 22366297 for thread 1 is in sequence #6


Specify log: {=suggested | filename | AUTO | CANCEL}
/home/oracle/oradata/testdb/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> col name for a50              
SQL> select file#,fuzzy, name, checkpoint_change# from v$datafile_header;

     FILE# FUZ NAME                                               CHECKPOINT_CHANGE#
---------- --- -------------------------------------------------- ------------------
         1 NO  /home/oracle/oradata/testdb/system01.dbf                     22366433
         2 NO  /home/oracle/oradata/testdb/sysaux01.dbf                     22366433
         3 NO  /home/oracle/oradata/testdb/undotbs01.dbf                    22366433
         4 NO  /home/oracle/oradata/testdb/users01.dbf                      22366433
         5 NO  /home/oracle/oradata/testdb/zq01.dbf                         22366433

SQL> alter database open resetlogs;

Database altered.

Conclusion:
1 Recreating controlfile could help open the database, but backup history, checkpoint cnt information would be lost.
2 Either the database was shutdown cleanly or complete redo logs remain, recreating control file would not require openning database with RESETLOGS option and there would be no data loss.
3 Restoring backup controlfile only could not recover database to the lastest status, whose information was not recorded in the controlfile
4 Recover database using backup controlfile until cancel can be used when there is no current controlfile available.

Reference:

https://jenniferlinca.wordpress.com/2008/02/08/what-is-the-scn/

Advertisements

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