RMAN PITR - testing some scenarios ~ Marko Sutic's Database Blog

I am regular follower of Oracle-l mailing list which is great source of knowledge for Oracle experts. Two days ago one Oracle DBA posted question “RMAN restore/recover problem” which induced me to re-check my knowledge about some RMAN PITR scenarios.

So I’ve performed some tests and decided to publish them online.
I have backup from 20.08.2013 and I want to perform database point in time recovery to that time.

Scenarios:
1. I don’t have old backup (autobackup) of control files.
2. I have incorrect incarnation and getting RMAN-20207 error.
3. I just have controlfile SQL script and suddenly lost current control files.

This is virtual machine on my notebook running OEL 5.8 and Oracle EE 11.1.0.7.

First scenario - I don’t have old backup (autobackup) of control files:

Simulate loss of all control file backups:

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/oradata/fra
db_recovery_file_dest_size           big integer 5000M

$ rm -rf /u01/oradata/fra/autobackup

Check backups of control files:

$ export NLS_DATE_FORMAT="DD.MM.YYYY HH24:MI:SS"

RMAN> crosscheck backup of controlfile;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/oradata/fra/ORAKL/autobackup/2013_08_20/o1_mf_s_823967119_916wqnk5_.bkp RECID=35 STAMP=823967124
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/oradata/fra/ORAKL/autobackup/2013_08_29/o1_mf_s_824745009_91yndoyo_.bkp RECID=39 STAMP=824745013
Crosschecked 2 objects


RMAN> list backup of controlfile;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
35      Full    9.36M      DISK        00:00:06     20.08.2013 15:45:25
        BP Key: 35   Status: EXPIRED  Compressed: NO  Tag: TAG20130820T154519
        Piece Name: /u01/oradata/fra/ORAKL/autobackup/2013_08_20/o1_mf_s_823967119_916wqnk5_.bkp
  Control File Included: Ckp SCN: 597418       Ckp time: 20.08.2013 15:45:19

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
39      Full    9.36M      DISK        00:00:04     29.08.2013 15:50:14
        BP Key: 39   Status: EXPIRED  Compressed: NO  Tag: TAG20130829T155010
        Piece Name: /u01/oradata/fra/ORAKL/autobackup/2013_08_29/o1_mf_s_824745009_91yndoyo_.bkp
  Control File Included: Ckp SCN: 598295       Ckp time: 29.08.2013 15:50:09

As you can see backups of control files are missing - they have EXPIRED flag.

List backup of database.

RMAN> list backup of database;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
33      Full    772.44M    DISK        00:00:36     20.08.2013 15:45:08
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20130820T154432
        Piece Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
  List of Datafiles in backup set 33
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/system01.dbf
  2       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/sysaux01.dbf
  3       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/undotbs01.dbf
  4       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/users01.dbf

Restore/recover database to 20.08.2013 15:45:08.

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     626327552 bytes

Fixed Size                     2162280 bytes
Variable Size                171966872 bytes
Database Buffers             448790528 bytes
Redo Buffers                   3407872 bytes




RMAN> run
2> {
3> set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }



executing command: SET until clause

Starting restore at 30.08.2013 09:09:53
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/orakl/orakl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/orakl/orakl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/orakl/orakl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/orakl/orakl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
channel ORA_DISK_1: piece handle=/u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp tag=TAG20130820T154432
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 30.08.2013 09:11:39

Starting recover at 30.08.2013 09:11:39
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
archived log file name=/u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 30.08.2013 09:11:40

sql statement: alter database open resetlogs

Second scenario - I have incorrect incarnation and getting RMAN-20207 error:

As I opened database with resetlogs statement new incarnation of the target database has been created. What will happen if I decide to perform PITR in this situation.

Again I don't have backup of control files.

RMAN> list backup of controlfile;


RMAN> list backup of database;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
33      Full    772.44M    DISK        00:00:36     20.08.2013 15:45:08
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20130820T154432
        Piece Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
  List of Datafiles in backup set 33
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/system01.dbf
  2       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/sysaux01.dbf
  3       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/undotbs01.dbf
  4       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/users01.dbf

  

RMAN> run
2> {
3> set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }

executing command: SET until clause
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 08/30/2013 09:29:30
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

I am getting RMAN-20207 error because now my target recovery time is time before database was last opened with RESETLOGS. Remember, in previous scenario I have opened database with RESETLOGS and created new incarnation.
To overcome this issue I have to set different incarnation which is sufficiently old.

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORAKL    3724279545       PARENT  1          07.07.2013 16:06:17
2       2       ORAKL    3724279545       PARENT  588882     07.07.2013 17:11:19
3       3       ORAKL    3724279545       ORPHAN  588882     07.07.2013 17:27:20
4       4       ORAKL    3724279545       PARENT  590385     07.07.2013 18:04:42
5       5       ORAKL    3724279545       CURRENT 597410     30.08.2013 09:11:40


RMAN> reset database to incarnation 4;

database reset to incarnation 4

Now to perform restore/recovery.

RMAN> run
2> {
3>  set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }

executing command: SET until clause

Starting restore at 30.08.2013 09:31:41
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/orakl/orakl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/orakl/orakl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/orakl/orakl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/orakl/orakl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
channel ORA_DISK_1: piece handle=/u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp tag=TAG20130820T15                                                                                        4432
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:24
Finished restore at 30.08.2013 09:33:05

Starting recover at 30.08.2013 09:33:05
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
archived log file name=/u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 30.08.2013 09:33:07

sql statement: alter database open resetlogs


SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

Third scenario - I just have controlfile SQL script and suddenly lost current controlfiles

Create SQL script of controlfile and delete all current controlfiles.

SQL> alter database backup controlfile to trace as '/home/oracle/backup/ctlfile_orkl.sql';

Database altered.

SQL> !rm /u01/oradata/orakl/orakl/control01.ctl /u01/oradata/orakl/orakl/control02.ctl /u01/oradata/orakl/orakl/control03.ctl

Check if controlfiles are really missing:

SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2162280 bytes
Variable Size             171966872 bytes
Database Buffers          448790528 bytes
Redo Buffers                3407872 bytes
ORA-00205: error in identifying control file, check alert log for more info

Edit controlfile SQL script to get executable SQL script for controlfile creation.
This is my controlfile SQL script:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORAKL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orakl/orakl/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/oradata/orakl/orakl/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/oradata/orakl/orakl/redo03.log'  SIZE 50M
DATAFILE
  '/u01/oradata/orakl/orakl/system01.dbf',
  '/u01/oradata/orakl/orakl/sysaux01.dbf',
  '/u01/oradata/orakl/orakl/undotbs01.dbf',
  '/u01/oradata/orakl/orakl/users01.dbf'
CHARACTER SET UTF8
;

Startup nomount and create controlfile from script.

SQL> @/home/oracle/backup/ctlfile_orkl.sql
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2162280 bytes
Variable Size             171966872 bytes
Database Buffers          448790528 bytes
Redo Buffers                3407872 bytes

Control file created.

In my newly created controlfile I don’t have any information about backups.

RMAN> list backup;

using target database control file instead of recovery catalog

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORAKL    3724279545       CURRENT 597410     30.08.2013 09:33:07

Let’s catalog backups from my FRA directory:

RMAN> catalog start with '/u01/oradata/fra';

searching for all files that match the pattern /u01/oradata/fra

List of Files Unknown to the Database
=====================================
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_annnn_TAG20130820T154518_916wqg5b_.bkp
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_annnn_TAG20130820T154431_916wozoy_.bkp
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_4_916wojn0_.arc
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_5_916wozks_.arc

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_annnn_TAG20130820T154518_916wqg5b_.bkp
File Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_annnn_TAG20130820T154431_916wozoy_.bkp
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_4_916wojn0_.arc
File Name: /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_5_916wozks_.arc

In this case also I don’t have backups of controlfile.


RMAN> list backup of controlfile;


RMAN> list backup of database;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4       Full    772.44M    DISK        00:00:00     20.08.2013 15:44:32
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20130820T154432
        Piece Name: /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/system01.dbf
  2       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/sysaux01.dbf
  3       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/undotbs01.dbf
  4       Full 597388     20.08.2013 15:44:32 /u01/oradata/orakl/orakl/users01.dbf

What if I now try to perform PITR.

RMAN> run
2> {
3> set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }

executing command: SET until clause
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 08/30/2013 09:45:09
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

Again incorrect incarnation.

When I change incarnation PITR successfully completes.

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
2       2       ORAKL    3724279545       PARENT  590385     07.07.2013 18:04:42
1       1       ORAKL    3724279545       CURRENT 597410     30.08.2013 09:33:07



RMAN> reset database to incarnation 2;

database reset to incarnation 2



RMAN> run
2> {
3> set until time "to_date('20.08.2013 15:45:08','DD.MM.YYYY HH24:MI:SS')";
4> restore database;
5>  recover database;
6> sql 'alter database open resetlogs';
7> }

executing command: SET until clause

Starting restore at 30.08.2013 09:46:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/orakl/orakl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/orakl/orakl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/oradata/orakl/orakl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/oradata/orakl/orakl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp
channel ORA_DISK_1: piece handle=/u01/oradata/fra/ORAKL/backupset/2013_08_20/o1_mf_nnndf_TAG20130820T154432_916wp0x9_.bkp tag=TAG20130820T15                                                                                        4432
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:04
Finished restore at 30.08.2013 09:47:41

Starting recover at 30.08.2013 09:47:41
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc
archived log file name=/u01/oradata/fra/ORAKL/archivelog/2013_08_20/o1_mf_1_6_916wqg1m_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:00
Finished recover at 30.08.2013 09:47:41

sql statement: alter database open resetlogs


SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE


SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oradata/orakl/orakl/temp01.dbf'
     SIZE 50331648  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;  2

Tablespace altered.

Menu

Marko Sutic's Database Blog

Friday, August 30, 2013

RMAN PITR - testing some scenarios

2 comments:

CATEGORIES

Blogroll

Recent Comments

About