Database point in time recovery using Data protector and RMAN

1 Introduction

The next document will describe the steps to restore database point in time using Data protector and RMAN

2. Restore using data protector console

2.1. Restore screen shot

3. Restore Database PITR using command prompt (Best/Preferred way)

3.1 Restore database 1st incarnation (No need to reset incarnation)

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:38:28 2017

connected to target database: BKPTST (not mounted)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=1 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/23/2017 17:38:32
ORA-01507: database not mounted

RMAN> alter database mount;

Statement processed

allocated channel: dev_0
channel dev_0: SID=36 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

creating datafile file number=5 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

3.2 Now we need to restore database older incarnation than above (For example above restoration happened till 16.41.54 PM now this restoration will happen till 16.41.04)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:04′,’MM/DD/YY HH24:MI:SS’)”;
restore database;
5> 6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

You will get the error as we have already have new incarnation in database. So we need to reset the incarnation.

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:48:01 2017

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 BKPTST 2013020542 PARENT 1 26-JAN-17
2 2 BKPTST 2013020542 PARENT 1408558 22-JUN-17
3 3 BKPTST 2013020542 CURRENT 1511521 23-JUN-17

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> exit

Restoration will now work fine

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:49:33 2017

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:04′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
}8>

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=1 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=6
channel dev_0: reading from backup piece bkptst_new<BKPTST_22:947418639:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_22:947418639:1>.dbf tag=TAG20170623T115024
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf thread=1 sequence=6
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=7
channel dev_0: reading from backup piece bkptst_new<BKPTST_26:947418651:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_26:947418651:1>.dbf tag=TAG20170623T115024
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

3.3 Now we need to restore database again to 3.1 incarnation (For example above restoration happened till 16.41.04 PM now this restoration will happen till 16.41.54 again)

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 18:56:47 2017

connected to target database: BKPTST (DBID=2013020542, not open)

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

You will get the error as we have already have new incarnation in database. So we need to reset the incarnation.

RMAN> list incarnation;

RMAN> reset database to incarnation 2;

database reset to incarnation 2

Now restore again

allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

NB: If you need to restore backup older than (15 days or control file keep record days) ,then you need to connect to recovery catalog as well.

4 . Restore Database with full, incremental and archive log using command prompt (Best/Preferred way)

Restore Database

All full backup will be restored

Recover Database

All incremental backup will be restored

All archive log backup will be restored

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Sun Jun 25 19:21:28 2017

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> run {
allocate channel ‘dev_0’ type ‘sbt_tape’
2> 3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/25/17 10:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
recover database;
6> 7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 25-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 25-JUN-17

Starting recover at 25-JUN-17
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_42:947503915:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_42:947503915:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_43:947503930:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_43:947503930:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_45:947503941:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_45:947503941:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_46:947503948:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_46:947503948:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:03

starting media recovery

channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=4
channel dev_0: reading from backup piece bkptst_new<BKPTST_49:947503970:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_49:947503970:1>.dbf tag=TAG20170624T113242
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=5
channel dev_0: reading from backup piece bkptst_new<BKPTST_50:947503977:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_50:947503977:1>.dbf tag=TAG20170624T113242
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=6
channel dev_0: restoring archived log
archived log thread=1 sequence=7
channel dev_0: reading from backup piece New3<BKPTST_53:947531995:1>.dbf
channel dev_0: piece handle=New3<BKPTST_53:947531995:1>.dbf tag=TAG20170624T191955
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=8
channel dev_0: reading from backup piece bkptst_new<BKPTST_61:947590300:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_61:947590300:1>.dbf tag=TAG20170625T113140
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=9
channel dev_0: reading from backup piece bkptst_new<BKPTST_62:947590308:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_62:947590308:1>.dbf tag=TAG20170625T113140
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:06
Finished recover at 25-JUN-17

Statement processed
released channel: dev_0

clouddba:-DBA blog on Oracle,Oracle cloud,DevOps,PostgreSQL and Other Databases

Database point in time recovery using Data protector and RMAN

2. Restore using data protector console

3. Restore Database PITR using command prompt (Best/Preferred way)

3.1 Restore database 1st incarnation (No need to reset incarnation)

3.2 Now we need to restore database older incarnation than above (For example above restoration happened till 16.41.54 PM now this restoration will happen till 16.41.04)

3.3 Now we need to restore database again to 3.1 incarnation (For example above restoration happened till 16.41.04 PM now this restoration will happen till 16.41.54 again)

4 . Restore Database with full, incremental and archive log using command prompt (Best/Preferred way)

Related

Leave a Reply Cancel reply

2. Restore using data protector console

3. Restore Database PITR using command prompt (Best/Preferred way)

3.1 Restore database 1st incarnation (No need to reset incarnation)

3.2 Now we need to restore database older incarnation than above (For example above restoration happened till 16.41.54 PM now this restoration will happen till 16.41.04)

3.3 Now we need to restore database again to 3.1 incarnation (For example above restoration happened till 16.41.04 PM now this restoration will happen till 16.41.54 again)

4 . Restore Database with full, incremental and archive log using command prompt (Best/Preferred way)

Share this:

Related

Leave a Reply Cancel reply