Wednesday, November 9, 2011

RESTORE DATABASE USING RMAN TAG

We have a test server; it has completely crashed, so I have to restore everything from the backup. This restore & recovery doesn’t have a recovery catalog. I started with spfile, controlfile and DB recovery.


Note: when you are allocating the channel & no of channels, you have specified
      Accordingly to system perform. It can be either from disk & tape. In the  below example  i have used TAPE. so i have used the below option.
STEP#1
If you have backup of spfile or old pfile, start DB with it.
You can see below link which has the step by step restoration of spfile from TAPE
http://oracledbabhuvan.blogspot.com/2011/10/restoring-spfile-from-autobackup-mode.html

STEP#2

A) If you have recover catalog available then use the below option

bhuora01[BHU_1]>rman target / catalog rman/rman@catalog
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Nov 9 09:31:31 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BHU (not mounted)
RMAN> restore controlfile from autobackup;
Note: Allocate channel as your environment and no need to set the DBID, recovery catalog will have all the details.

B) If you don’t have recover catalog available then use the below option
1)      set DB ID (you can find from the alert log of the database)
2)       Issue “restore controlfile from autobackup;”

OUTPUT LOG
bhuora01[BHU_1]>rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Nov 9 09:51:01 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BHU (not mounted)
RMAN> set dbid 614318649
executing command: SET DBID
RMAN> run
{
ALLOCATE CHANNEL 'dev_1' type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=WYC_bhuora01_ORA_BHU_ON_Daily)';
restore controlfile from autobackup;
}2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: dev_1
channel dev_1: SID=3876 instance=BHU_1 device type=SBT_TAPE
channel dev_1: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
Starting restore at 09-NOV-11
channel dev_1: looking for AUTOBACKUP on day: 20111109
channel dev_1: looking for AUTOBACKUP on day: 20111108
channel dev_1: AUTOBACKUP found: c-614318649-20111108-02
channel dev_1: restoring control file from AUTOBACKUP c-614318649-20111108-02
channel dev_1: control file restore from AUTOBACKUP complete
output file name=+BHU_DATA1/BHU_a/controlfile/current.275.766749519
output file name=+BHU_ARCH/BHU_a/controlfile/current.2692.766749519
output file name=+BHU_RECO/BHU_a/controlfile/current.562.766749521
Finished restore at 09-NOV-11
released channel: dev_1
RMAN>

STEP#3

If you are using ASM disk and spfile, then the system will change the control file naming conversation automatically.
If you are using ASM and using pfile then you have change the controlfile name after restoring the controlfile.
Note: If you are creating spfile before the DB is mount, DB SPFILE will be created in the UNKNOWN Directory in the specified Disk group.


STEP#4
RESTORING THE DB FROM THE TAG

RMAN> run
2> {
3> ALLOCATE CHANNEL 'dev_1' type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=WYC_bhuora01_ORA_BHU_ON_Daily)';
4> ALLOCATE CHANNEL 'dev_2' type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=WYC_bhuora01_ORA_BHU_ON_Daily)';
5> ALLOCATE CHANNEL 'dev_3' type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=bhuora01_ORA_BHU_ON_Daily)';
6> ALLOCATE CHANNEL 'dev_4' type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=bhuora01_ORA_BHU_ON_Daily)';
7> restore database from tag 'TAG20111027T203116';
8> }
allocated channel: dev_1
channel dev_1: SID=1252 instance=BHU_1 device type=SBT_TAPE
channel dev_1: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
allocated channel: dev_2
channel dev_2: SID=1377 instance=BHU_1 device type=SBT_TAPE
channel dev_2: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
allocated channel: dev_3
channel dev_3: SID=1502 instance=BHU_1 device type=SBT_TAPE
channel dev_3: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
allocated channel: dev_4
channel dev_4: SID=1628 instance=BHU_1 device type=SBT_TAPE
channel dev_4: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
Starting restore at 09-NOV-11
channel dev_1: starting datafile backup set restore
channel dev_1: specifying datafile(s) to restore from backup set
channel dev_1: restoring datafile 00002 to +BHU_DATA1/BHU_a/datafile/sysaux.276.762885919
channel dev_1: restoring datafile 00004 to +BHU_DATA1/BHU_a/datafile/pic.270.761328545
channel dev_1: restoring datafile 00010 to +BHU_DATA1/BHU_a/datafile/undo_2.264.760176683
channel dev_1: reading from backup piece bhuora01_ORA_BHU_ON_Daily<BHU_A_1120:765664278:1>.dbf
channel dev_2: starting datafile backup set restore
channel dev_2: specifying datafile(s) to restore from backup set
channel dev_2: restoring datafile 00001 to +BHU_DATA1/BHU_a/datafile/system.258.762885661
channel dev_2: restoring datafile 00006 to +BHU_DATA1/BHU_a/datafile/pic730.278.762886223
channel dev_2: restoring datafile 00008 to +BHU_DATA1/BHU_a/datafile/picusr.280.762886387
channel dev_2: reading from backup piece bhuora01_ORA_BHU_ON_Daily<BHU_A_1123:765664278:1>.dbf
channel dev_3: starting datafile backup set restore
channel dev_3: specifying datafile(s) to restore from backup set
channel dev_3: restoring datafile 00005 to +BHU_DATA1/BHU_a/datafile/pic.271.761409691
channel dev_3: restoring datafile 00007 to +BHU_DATA1/BHU_a/datafile/pic730.279.762886223
channel dev_3: reading from backup piece bhuora01_ORA_BHU_ON_Daily<BHU_A_1121:765664278:1>.dbf
channel dev_4: starting datafile backup set restore
channel dev_4: specifying datafile(s) to restore from backup set
channel dev_4: restoring datafile 00003 to +BHU_DATA1/BHU_a/datafile/undo.277.762886011
channel dev_4: restoring datafile 00009 to +BHU_DATA1/BHU_a/datafile/picdb.281.762886439
channel dev_4: reading from backup piece bhuora01_ORA_BHU_ON_Daily<BHU_A_1122:765664278:1>.dbf
channel dev_1: piece handle=bhuora01_ORA_BHU_ON_Daily<BHU_A_1120:765664278:1>.dbf tag=TAG20111027T203116
channel dev_1: restored backup piece 1
channel dev_1: restore complete, elapsed time: 00:36:37
channel dev_2: piece handle=bhuora01_ORA_BHU_ON_Daily<BHU_A_1123:765664278:1>.dbf tag=TAG20111027T203116
channel dev_2: restored backup piece 1
channel dev_2: restore complete, elapsed time: 00:36:47
channel dev_3: piece handle=bhuora01_ORA_BHU_ON_Daily<BHU_A_1121:765664278:1>.dbf tag=TAG20111027T203116
channel dev_3: restored backup piece 1
channel dev_3: restore complete, elapsed time: 00:37:17
channel dev_4: piece handle=bhuora01_ORA_BHU_ON_Daily<BHU_A_1122:765664278:1>.dbf tag=TAG20111027T203116
channel dev_4: restored backup piece 1
channel dev_4: restore complete, elapsed time: 00:37:57
Finished restore at 09-NOV-11
released channel: dev_1
released channel: dev_2
released channel: dev_3
released channel: dev_4
RMAN>

STEP#5

Once the Restore is completed, check the FUZZY in the v$datafile_header. If FUZZY is “YES” then you have to restore old archive logs(probably couple of archive logs)

AS SOON RESTORE COMPLETED

SQL> select file#,STATUS, FUZZY from v$datafile_header where FUZZY='YES';

     FILE# STATUS  FUZ
---------- ------- ---
         1 ONLINE  YES
         2 ONLINE  YES
         3 ONLINE  YES
        10 ONLINE  YES

RECOVERY DB USING BACKUP CONTROLFILE

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 78452381 generated at 10/27/2011 20:30:20 needed for thread 2
ORA-00289: suggestion : +BHU_ARCH
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'BHU_A'
ORA-00280: change 78452381 for thread 2 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>

Note: it is asking for archive log since it is a incremental level backup and it is in open mode. Oracle will expected a consistency in between datafiles.



STEP#6 RESTORING ARCHIVE LOG FROM BACKUP USING SCN

bhuora01[BHU_1]>rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Nov 9 15:50:03 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BHU (DBID=614318649, not open)
RMAN> run
{
2> 3> ALLOCATE CHANNEL 'dev_1' type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=bhuora01_ORA_BHU_ON_Daily)';
4> ALLOCATE CHANNEL 'dev_2' type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=bhuora01_ORA_BHU_ON_Daily)';
5> ALLOCATE CHANNEL 'dev_3' type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=bhuora01_ORA_BHU_ON_Daily)';
6> ALLOCATE CHANNEL 'dev_4' type 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BHU_A,OB2BARLIST=bhuora01_ORA_BHU_ON_Daily)';
7> restore archivelog scn between 78452000 and 78457000;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_1
channel dev_1: SID=1252 instance=BHU_1 device type=SBT_TAPE
channel dev_1: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
allocated channel: dev_2
channel dev_2: SID=1377 instance=BHU_1 device type=SBT_TAPE
channel dev_2: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
allocated channel: dev_3
channel dev_3: SID=1502 instance=BHU_1 device type=SBT_TAPE
channel dev_3: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
allocated channel: dev_4
channel dev_4: SID=1628 instance=BHU_1 device type=SBT_TAPE
channel dev_4: Data Protector A.06.11/PHSS_41802/PHSS_41803/DPSOL_00435/DPLNX_
Starting restore at 09-NOV-11

channel dev_1: starting archived log restore to default destination
channel dev_1: restoring archived log
archived log thread=1 sequence=4
channel dev_1: reading from backup piece bhuora01_ORA_BHU_ON_ARCHIVE<BHU_A_1127:765746115:1>.dbf
channel dev_2: starting archived log restore to default destination
channel dev_2: restoring archived log
archived log thread=1 sequence=3
channel dev_2: restoring archived log
archived log thread=2 sequence=3
channel dev_2: reading from backup piece bhuora01_ORA_BHU_ON_ARCHIVE<BHU_A_1126:765746115:1>.dbf
channel dev_1: piece handle=bhuora01_ORA_BHU_ON_ARCHIVE<BHU_A_1127:765746115:1>.dbf tag=TAG20111028T191513
channel dev_1: restored backup piece 1
channel dev_1: restore complete, elapsed time: 00:08:05
channel dev_2: piece handle=bhuora01_ORA_BHU_ON_ARCHIVE<BHU_A_1126:765746115:1>.dbf tag=TAG20111028T191513
channel dev_2: restored backup piece 1
channel dev_2: restore complete, elapsed time: 00:08:15
Finished restore at 09-NOV-11
released channel: dev_1
released channel: dev_2
released channel: dev_3
released channel: dev_4

RMAN>


STARTING DB RECOVERY AGAIN

bhuora01[BHU_1]>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 9 11:48:16 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 69764142 generated at 10/14/2011 22:00:21 needed for thread 1
ORA-00289: suggestion :
+BHU_ARCH/BHU_a/archivelog/2011_11_09/thread_1_seq_33.2715.766755695
ORA-00280: change 69764142 for thread 1 is in sequence #33


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 69764142 generated at 10/14/2011 11:04:24 needed for thread 2
ORA-00289: suggestion :
+BHU_ARCH/BHU_a/archivelog/2011_11_09/thread_2_seq_18.2833.766754657
ORA-00280: change 69764142 for thread 2 is in sequence #18


ORA-00279: change 69764145 generated at 10/14/2011 22:00:22 needed for thread 2
ORA-00289: suggestion :
+BHU_ARCH/BHU_a/archivelog/2011_11_09/thread_2_seq_19.2865.766756043
ORA-00280: change 69764145 for thread 2 is in sequence #19
ORA-00278: log file
'+BHU_ARCH/BHU_a/archivelog/2011_11_09/thread_2_seq_18.2833.766754657' no
longer needed for this recovery

ORA-00279: change 69979800 generated at 10/15/2011 22:00:15 needed for thread 1
ORA-00289: suggestion : +BHU_ARCH
ORA-00280: change 69979800 for thread 1 is in sequence #34
ORA-00278: log file
'+BHU_ARCH/BHU_a/archivelog/2011_11_09/thread_1_seq_33.2715.766755695' no
longer needed for this recovery

    ONCE RESTORE OF ARCHIVE COMPLETED

SQL> select file#,STATUS, FUZZY from v$datafile_header where FUZZY='YES';

no rows selected


STEP#7 OPENING DB IN RESETLOGS

SQL> alter database open resetlogs;
Database altered.

Note: Once the database is open and it is started to use the application team, trigger a clean backup of the database.

No comments:

Post a Comment