Catalog the rman backup piece in sbt_tape with DataProtector

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent

RMAN-03002: failure of recover command at 01/27/2014 22:09:43
RMAN-06054:media recovery requesting unknown archived log for thread 1 with sequence 786 and starting SCN of 22193647084

We have the tape backup of the database with archive log.. But we lost the recover catalog and the Original database of the backup..So we had started the restore session with the tape backup available with the new server in different host
Once the restore of the tape backup had  been completed successfully but the recover fails with the above error

Step 1 :=Run the recover command after successful restore of the database

Connect the rman as target  and run the below script to restore from the data protector[M.M.L]

RMAN> run
2> {
3>  allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
4> set until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
5> recover database;
6> }
using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=256 device type=SBT_TAPE
channel dev_0: Data Protector A.07.00/72
executing command: SET until clause
Starting recover at 27-JAN-14
starting media recovery
unable to find archived log
archived log thread=1 sequence=785
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oradata/system.316.825482365.dbf'
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/27/2014 09:32:0

Step 2 :=Check the database status by using the below query and run the database restore preview

This error occurred Because of the  database needs the some of the archive log to Complete restore of the system until the Specified point in time which we have mentioned in while restore and recover

To find until which sequence number we need to restore we can find by using the below script in the database which is in the mount stage

checking the database restore Preview
run 
{ 
 allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
restore database preview  until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
}

check the database incarnation

RMAN> list incarnation of database;
list incarnation of database;
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 OMPROD 3864922897 CURRENT 21234847062 06-SEP-13
 
SQL> select * from v$database_incarnation;

    INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS      RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWE
D
---------------- ----------------- ------------------- ----------------------- ------------------- ------- ---------------- ------------------ -------------------------
-
               1       21234847062 06-09-2013 01:43:14               102590762 18-01-2008 03:33:51 CURRENT        825385394                  0 NO


SQL> select max(fhafs) "Minimum PITR SCN" from x$kcvfh;

Minimum PITR SCN
----------------
22193643139------- Until this scn number we need to recover the database

To Find the Archive log  Number use the below query which will display the SCN and archive log  number

SQL> SELECT hxfil file_num,
  2  fhscn scn,
  3  fhsta status ,
  4  fhthr thread,
  5  fhrba_seq sequence
  6  FROM x$kcvfh
  7  order by scn;

To check the database current status
SQL> SELECT name,
  2  file#,
  3  status,
  4  error,
  5  creation_change#,
  6  TO_CHAR(creation_time, 'DD-MM-YYYY HH24:MI:SS') as creation_time,
  7  TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
  8  TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
  9  TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
 10  TO_CHAR(resetlogs_time, 'DD-MM-YYYY HH24:MI:SS') as resetlogs_time,
 11  TO_CHAR(bytes, '9,999,999,999,990') as bytes
 12  FROM v$datafile_header
 13  ORDER BY checkpoint_change#;



Step 3  :=Check the Data Protector Internal database to find the backup piece which contains  the archive log  


Go to the data Protector and check the particular backup Piece which contains the archive log sequence number

Open the Dp----internaldatabase----- sessions----backup<with date>----right check and go to the messages tab and you will find the details message's when the backup had been ran and where the archive log is store

The message fine will be like below



RMAN-08540: channel dev_0: backup set complete, elapsed time: 00:01:45
RMAN-08009: channel dev_0: starting archived log backup set
RMAN-08014: channel dev_0: specifying archived log(s) in backup set
RMAN-08504: input archived log thread=2 sequence=1007 RECID=1831 STAMP=828132023
RMAN-08504: input archived log thread=2 sequence=1008 RECID=1832 STAMP=828134415
RMAN-08504: input archived log thread=2 sequence=1009 RECID=1835 STAMP=828137780
RMAN-08504: input archived log thread=1 sequence=782 RECID=1834 STAMP=828134857
RMAN-08504: input archived log thread=1 sequence=783 RECID=1838 STAMP=828137786
RMAN-08504: input archived log thread=2 sequence=1010 RECID=1836 STAMP=828137783
RMAN-08504: input archived log thread=2 sequence=1011 RECID=1837 STAMP=828137783
RMAN-08504: input archived log thread=1 sequence=784 RECID=1841 STAMP=828143453
RMAN-08504: input archived log thread=2 sequence=1012 RECID=1839 STAMP=828141110
RMAN-08504: input archived log thread=2 sequence=1013 RECID=1840 STAMP=828143453
RMAN-08504: input archived log thread=1 sequence=785 RECID=1843 STAMP=828148493
RMAN-08504: input archived log thread=2 sequence=1014 RECID=1842 STAMP=828148491
RMAN-08038: channel dev_0: starting piece 1 at 07-OCT-13
[Normal] From: OB2BAR_SBT_CHANNEL@db01.<domain>.com "OMPROD"  Time: 10/7/2013 1:16:42 AM
    Starting OB2BAR Backup: db01.<domain>.com:DB01-New<OMPROD_784:828148601:1>.dbf "Oracle8"
Here the necessary archive log file is in the backup Piece DB01-New<OMPROD_784:828148601:1>.dbf

Once we find the necessary file we need to catalog the backup in rman and then restore and recover the archive log from the tape backup

Step 4 :=CATALOG BACKUP PIECE FROM THE SBT_TAPE IN RMAN

How to catalog the rman backup piece from the tape

To catalog the backup First we need to Configure the separate channel  with the tape drive

Syntax

 CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=<Path for the Dp conenction>

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)'; 

The dp connection path can be find in the Dp backup message file wile allocating the channel

allocate channel 'dev_0' type 'sbt_tape'parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01-New)' 

We need to use the same path from this message file in the SBT_LIBRARY 

we need to set the default device type to tape

CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; 

Once both the configuration parameter's are set in the Dp then we need to catalog the backup

Syntax 

Catalog Device type 'Sbt_Tape' backupPiece '<backup piece name from the data protector>

 

RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'DB01-New<OMPROD_784:828148601:1>.dbf';

Step 5 :=Run Recover Command to recover the database

Recover the database by using the recover command  in the rman connect the the rman as target with database in the mount stage and run the recover command 

The below command is used for the recover the database 

run

{

allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';

set until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";

recover database;

}

Legends

Run--- is used in Rman to run the set of rman command's 

Allocate channel --- we need to allocate the channel which will establish the communication with the database and the backup medium through this channel the backup,restore and recover happens.. since in this process we are using the tape we need to specify the sbt_library location<path> ..this path will be communicate to the physical tape library with the media management library software 

Set until time ----- It is used to set the time until we need to recover

RMAN> run
2> {
3> allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
4> recover database until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
5> }

released channel: ORA_SBT_TAPE_1
allocated channel: dev_0
channel dev_0: SID=506 device type=SBT_TAPE
channel dev_0: Data Protector A.07.00/72

Starting recover at 27-JAN-14

starting media recovery

channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=785
channel dev_0: restoring archived log
archived log thread=2 sequence=1014
channel dev_0: reading from backup piece DB01-New<OMPROD_784:828148601:1>.dbf
channel dev_0: piece handle=DB01-New<OMPROD_784:828148601:1>.dbf tag=TAG20131007T011454
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:05:25
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_785_9gf7zs2n_.arc thread=1 sequence=785
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1014_9gf7zs4w_.arc thread=2 sequence=1014
channel default: deleting archived log(s)
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_785_9gf7zs2n_.arc RECID=2 STAMP=837986910
unable to find archived log
archived log thread=1 sequence=786
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/27/2014 22:09:43
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 786 and starting SCN of 22193647084

Now the archivelog's had been restored in the fast_recover_area but still the database need's more archivelog to recover  the database so again we need to catalog the another file from the data protector backup and then start recover command

In the above senario we need the log sequence 786 that is in the below backup Piece 

catalog the backup next backup piece which Contains the archive log

We need to catalog the backup piece one by one until the database recover had been completely successfully .. once the catalog of the backup piece is create then the rman looks over that particular file and then start's the recover command ...

 

RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'DB01-New<OMPROD_784:828148601:1>.dbf';

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=506 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protector A.07.00/72
cataloged backup piece
backup piece handle=DB01-New<OMPROD_784:828148601:1>.dbf RECID=1 STAMP=837986306

Once the particular backup file is cataloged then we need to run the recover command again 

RMAN> run
2> {
3> allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
4> recover database until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
5> }

released channel: ORA_SBT_TAPE_1
allocated channel: dev_0
channel dev_0: SID=506 device type=SBT_TAPE
channel dev_0: Data Protector A.07.00/72

Starting recover at 27-JAN-14

starting media recovery

archived log for thread 2 with sequence 1014 is already on disk as file /prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1014_9gf7zs4w_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=786
channel dev_0: restoring archived log
archived log thread=2 sequence=1015
channel dev_0: reading from backup piece DB01-New<OMPROD_791:828236299:1>.dbf
channel dev_0: piece handle=DB01-New<OMPROD_791:828236299:1>.dbf tag=TAG20131008T013818
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:06:06
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_786_9gf97ylf_.arc thread=1 sequence=786
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1014_9gf7zs4w_.arc thread=2 sequence=1014
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1015_9gf97ymf_.arc thread=2 sequence=1015
channel default: deleting archived log(s)
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_786_9gf97ylf_.arc RECID=4 STAMP=837988248
channel default: deleting archived log(s)
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1015_9gf97ymf_.arc RECID=3 STAMP=837988247
media recovery complete, elapsed time: 00:00:13
Finished recover at 27-JAN-14
released channel: dev_0

Now the Recover of the database had been completely successfully and  we need to open the database by using the reset logs;

Step 6 := Open the database by using the reset logs 

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

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