Standby Database Recovery Using RMAN Incremental Backup

CASE 1- NON ASM SYSTEM...all datafiles and control file is on file system.
#####################################################################################


DGMGRL> show configuration;

Configuration
Name: DR
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PRSP01_VE - Primary database
PRSP01_BS - Physical standby database

Current status for "DR":
Warning: ORA-16607: one or more databases have failed


DGMGRL> show database 'PRSP01_VE';

Database
Name: PRSP01_VE
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
PRSP01

Current status for "PRSP01_VE":
Error: ORA-16778: redo transport error for one or more databases


DGMGRL> show database 'PRSP01_BS';

Database
Name: PRSP01_BS
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
PRSP01

Current status for "PRSP01_BS":
Error: ORA-16766: Redo Apply unexpectedly offline


DGMGRL> SHOW DATABASE 'PRSP01_VE' 'StatusReport';
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
PRSP01 ERROR ORA-16737: the redo transport service for standby database "PRSP01_BS" has an error

DGMGRL> SHOW DATABASE 'PRSP01_VE' 'LogXptStatus';
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS
PRSP01 PRSP01_BS ORA-00254: error in archive control string ''



Thu Jan 14 04:07:05 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9777]: Assigned to RFS process 25364
RFS[9777]: Identified database type as 'physical standby'
RFS[9777]: Error in standby_archive_dest '/local/ORAPRSP01/DBDUMPS/ARCHIVE/PRSP01'
RFS: Forced Shutdown due to RFS_ERROR state
Thu Jan 14 04:07:05 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9778]: Assigned to RFS process 25366
RFS[9778]: Identified database type as 'physical standby'
RFS[9778]: Error in standby_archive_dest '/local/ORAPRSP01/DBDUMPS/ARCHIVE/PRSP01'
RFS: Forced Shutdown due to RFS_ERROR state
Thu Jan 14 04:07:07 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9779]: Assigned to RFS process 25368
RFS[9779]: Identified database type as 'physical standby'
RFS[9779]: Error in standby_archive_dest '/local/ORAPRSP01/DBDUMPS/ARCHIVE/PRSP01'
RFS: Forced Shutdown due to RFS_ERROR state
Thu Jan 14 04:07:07 2010
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[9780]: Assigned to RFS process 25370
RFS[9780]: Identified database type as 'physical standby'
RFS[9780]: Error in standby_archive_dest '/local/ORAPRSP01/DBDUMPS/ARCHIVE/PRSP01'
RFS: Forced Shutdown due to RFS_ERROR state



Logged on to standby successfully
Client logon and security negotiation successful!
*** 2010-01-13 19:39:16.857
Redo shipping client performing standby login
*** 2010-01-13 19:39:17.016 65194 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 254 creating standby archive log file at host '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=eqd-oradev02)(PORT=1575)))(CONNECT_DATA=(SERVICE_NAME=PRSP01_BS_XPT)(INSTANCE_NAME=PRSP01)(SERVER=dedicated)))'
*** 2010-01-13 19:39:17.078 61287 kcrr.c
ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (254)
*** 2010-01-13 19:39:17.078 61287 kcrr.c
ARC1: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
ORA-00254: error in archive control string ''



(1)At standby site.
=================
SQL> column current_scn format 9999999999999
SQL> select current_scn from v$database;

CURRENT_SCN
--------------
8918544365814


(2) At primary site
================
BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 8918544365814 DATABASE FORMAT '/local/ORAPRMP01/DBDUMPS/bkup_%U';

(3) At BCP site
============
3a) Stop MRP at BCP databae side

At BCP site
===========
3b) Note location of controlfiles

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/local/ORAPRSP01/data01/PRSP01/control01.ctl
/local/ORAPRSP01/data02/PRSP01/control02.ctl
/local/ORAPRSP01/data03/PRSP01/control03.ctl


At BCP Site
============

4) Incremental backup
==================

RMAN> catalog start with '/local/ORAUASP01/DBDUMPS/incr_bkup';

Starting implicit crosscheck backup at 14-JAN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=38 devtype=DISK
Crosschecked 803 objects
Finished implicit crosscheck backup at 14-JAN-10

Starting implicit crosscheck copy at 14-JAN-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-JAN-10

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /local/ORAUASP01/DBDUMPS/incr_bkup

List of Files Unknown to the Database
=====================================
File Name: /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n4l3dti1_1_1
File Name: /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n5l3du33_1_1
File Name: /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n3l3dti1_1_1
File Name: /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n6l3dua5_1_1

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

List of Cataloged Files
=======================
File Name: /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n4l3dti1_1_1
File Name: /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n5l3du33_1_1
File Name: /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n3l3dti1_1_1
File Name: /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n6l3dua5_1_1


5)At BCP site


RMAN> recover database noredo;

Starting recover at 14-JAN-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00008: /local/ORAPRSP01/data01/PRSP01/PRSP01_PRISM_DATA_03.dbf
destination for restore of datafile 00009: /local/ORAPRSP01/data01/PRSP01/PRSP01_PRISM_INDEX_02.dbf
destination for restore of datafile 00014: /local/ORAPRSP01/data03/PRSP01/PRSP01_PRISM_DATA_04.dbf
destination for restore of datafile 00018: /local/ORAPRSP01/data02/PRSP01/PRSP01_PRISM_DATA_06.dbf
destination for restore of datafile 00022: /local/ORAPRSP01/data03/PRSP01/capacity01.dbf
destination for restore of datafile 00023: /local/ORAPRSP01/data02/PRSP01/PRSP01_sysaux02.dbf
destination for restore of datafile 00024: /local/ORAPRSP01/data02/PRSP01/PRSP01_system02.dbf
destination for restore of datafile 00025: /local/ORAPRSP01/data02/PRSP01/dbus01.dbf
channel ORA_DISK_1: reading from backup piece /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n4l3dti1_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n4l3dti1_1_1 tag=TAG20100114T064416
channel ORA_DISK_1: restore complete, elapsed time: 00:04:36
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /local/ORAPRSP01/data01/PRSP01/PRSP01_users01.dbf
destination for restore of datafile 00012: /local/ORAPRSP01/data02/PRSP01/PRSP01_CERD_CORPORATE_DATA_02.dbf
destination for restore of datafile 00013: /local/ORAPRSP01/data01/PRSP01/PRSP01_CERD_CORPORATE_DATA_03.dbf
destination for restore of datafile 00015: /local/ORAPRSP01/data03/PRSP01/PRSP01_PRISM_INDEX_03.dbf
destination for restore of datafile 00021: /local/ORAPRSP01/data03/PRSP01/PRSP01_PRISM_INDEX_06.dbf
channel ORA_DISK_1: reading from backup piece /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n5l3du33_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n5l3du33_1_1 tag=TAG20100114T064416
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /local/ORAPRSP01/data01/PRSP01/PRSP01_system01.dbf
destination for restore of datafile 00003: /local/ORAPRSP01/data01/PRSP01/PRSP01_sysaux01.dbf
destination for restore of datafile 00005: /local/ORAPRSP01/data03/PRSP01/PRSP01_PRISM_DATA_01.dbf
destination for restore of datafile 00007: /local/ORAPRSP01/data02/PRSP01/PRSP01_PRISM_DATA_02.dbf
destination for restore of datafile 00010: /local/ORAPRSP01/data01/PRSP01/PRSP01_undotbs02.dbf
destination for restore of datafile 00011: /local/ORAPRSP01/data01/PRSP01/PRSP01_CERD_CORPORATE_DATA_01.dbf
destination for restore of datafile 00016: /local/ORAPRSP01/data01/PRSP01/PRSP01_PRISM_DATA_05.dbf
destination for restore of datafile 00017: /local/ORAPRSP01/data01/PRSP01/PRSP01_PRISM_INDEX_04.dbf
destination for restore of datafile 00019: /local/ORAPRSP01/data02/PRSP01/PRSP01_PRISM_INDEX_05.dbf
channel ORA_DISK_1: reading from backup piece /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n3l3dti1_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n3l3dti1_1_1 tag=TAG20100114T064416
channel ORA_DISK_1: restore complete, elapsed time: 00:10:36
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /local/ORAPRSP01/data03/PRSP01/PRSP01_undotbs01.dbf
destination for restore of datafile 00006: /local/ORAPRSP01/data02/PRSP01/PRSP01_PRISM_INDEX_01.dbf
destination for restore of datafile 00020: /local/ORAPRSP01/data03/PRSP01/PRSP01_CERD_CORPORATE_DATA_04.dbf
channel ORA_DISK_1: reading from backup piece /local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n6l3dua5_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/ORAUASP01/DBDUMPS/incr_bkup/bkup_n6l3dua5_1_1 tag=TAG20100114T064416
channel ORA_DISK_1: restore complete, elapsed time: 00:13:17
Finished recover at 14-JAN-10



6) At BCP site
starting MRP asks for same archive gap still as the control file is not updated.


MRP0: Background Managed Standby Recovery process started (PRSP01)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 16 processes
Media Recovery Waiting for thread 1 sequence 94040
Fetching gap sequence in thread 1, gap sequence 94040-94052
Thu Jan 14 08:32:03 2010
Completed: alter database recover managed standby database disconnect from session
Thu Jan 14 08:32:15 2010
alter database recover managed standby database cancel
Thu Jan 14 08:32:33 2010
MRP0: Background Media Recovery cancelled with status 16037
Thu Jan 14 08:32:33 2010
Errors in file /local/ORAPRSP01/sw/oracle/admin/PRSP01/bdump/prsp01_mrp0_27018.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Thu Jan 14 08:32:35 2010
Errors in file /local/ORAPRSP01/sw/oracle/admin/PRSP01/bdump/prsp01_mrp0_27018.trc:
ORA-16037: user requested cancel of managed recovery operation
Thu Jan 14 08:32:35 2010
MRP0: Background Media Recovery process shutdown (PRSP01)

7)At priamry and ship to BCP site
create a new standby control file at primary and restart the standby using that new satndby controlfile .




CASE 2- ASM SYSTEM...all datafiles and control file is on ASM.
#####################################################################################



1) get latest SCN number from standby database.

SQL> column current_scn format 999999999999
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-------------
18934679323

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.


2)Take incremantal SCN backup on primary side.


RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 18934679323 database format '/backup/oracle/bkup_%U';

Starting backup at 19-JAN-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=705 instance=PWHGRD21 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=712 instance=PWHGRD21 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=792 instance=PWHGRD21 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=677 instance=PWHGRD21 devtype=DISK
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00014 name=+ARCHDG1/pwhgrd20/datafile/mgmt_tablespace.994.686140129
input datafile fno=00005 name=+DATADG1/pwhgrd20/datafile/mgmt_tablespace.279.629708905
input datafile fno=00007 name=+DATADG1/pwhgrd20/datafile/bearmon.268.619705149
channel ORA_DISK_1: starting piece 1 at 19-JAN-10
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
channel ORA_DISK_3: starting compressed full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00010 name=+DATADG1/pwhgrd20/datafile/mgmt_tablespace.276.641683063
input datafile fno=00017 name=+DATADG1/pwhgrd20/datafile/mgmt_tablespace.292.689859511
input datafile fno=00015 name=+ARCHDG1/pwhgrd20/datafile/sysaux.478.686918653
input datafile fno=00012 name=+DATADG1/pwhgrd20/datafile/bearaudit.282.645449195
input datafile fno=00004 name=+DATADG1/pwhgrd20/datafile/users.266.619034639
channel ORA_DISK_3: starting piece 1 at 19-JAN-10
channel ORA_DISK_4: starting compressed full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
input datafile fno=00008 name=+DATADG1/pwhgrd20/datafile/mgmt_tablespace.277.629285163
input datafile fno=00013 name=+DATADG1/pwhgrd20/datafile/mgmt_tablespace.273.685143701
input datafile fno=00006 name=+DATADG1/pwhgrd20/datafile/mgmt_ecm_depot_ts.280.629709499
input datafile fno=00003 name=+DATADG1/pwhgrd20/datafile/sysaux.264.619034633
input datafile fno=00016 name=+ARCHDG1/pwhgrd20/datafile/mgmt_ecm_depot_ts.938.688263225
channel ORA_DISK_4: starting piece 1 at 19-JAN-10
including current SPFILE in backupset
including current control file in backupset
input datafile fno=00011 name=+DATADG1/pwhgrd20/datafile/mgmt_tablespace.287.657955169
input datafile fno=00009 name=+DATADG1/pwhgrd20/datafile/undotbs2.278.631236955
input datafile fno=00002 name=+DATADG1/pwhgrd20/datafile/undotbs1.263.619034627
input datafile fno=00001 name=+DATADG1/pwhgrd20/datafile/system.262.619034617
channel ORA_DISK_2: starting piece 1 at 19-JAN-10
channel ORA_DISK_3: finished piece 1 at 19-JAN-10
piece handle=/backup/oracle/bkup_tol3rslj_1_1 tag=TAG20100119T135458 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:21:48
channel ORA_DISK_4: finished piece 1 at 19-JAN-10
piece handle=/backup/oracle/bkup_tpl3rslj_1_1 tag=TAG20100119T135458 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:22:03
channel ORA_DISK_1: finished piece 1 at 19-JAN-10
piece handle=/backup/oracle/bkup_tml3rslj_1_1 tag=TAG20100119T135458 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:26:28
channel ORA_DISK_2: finished piece 1 at 19-JAN-10
piece handle=/backup/oracle/bkup_tnl3rslj_1_1 tag=TAG20100119T135458 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:28:43
Finished backup at 19-JAN-10


3) cancel MRP on standby side and ship the backup pieces to satndby site in a separate directory.

4) MAke a note of all control files,datafiles and satndby redologs if there. Als

5) connect to standby database using rman and catalog the backup pieces.

RMAN> catalog start with '/backup/oracle/incr_bkup';


6) recover standby database with noredo.

RMAN> recover database noredo.

7) make a new standby controlfile from primary and ship to satndby site. And now comes the different part as controlfiles and datafiles are in the ASM.

8) Shut down standby database.

sql> shut immediate; (make a backup of old standby control file now)

sql> startup nomount;

9) Now restore the new standby control file in to ASM DG from file system using rman.

rman> RESTORE STANDBY CONTROLFILE TO '' FROM '/tmp/ForStandbyCTRL.ctl';

ie rman > RESTORE STANDBY CONTROLFILE TO '+DATADG1/bnygrd20/controlfile/current.274.693183845' FROM '/tmp/ForStandbyCTRL.ctl';

10) If directory structure is same then the workis done just mount the database and start MRP.
But if primary and Standby datafile/redolog location is different then do following.

The new standby control file will be showing the datafiles locations of the primary now.

####
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATADG1/pwhgrd20/datafile/system.262.619034617
+DATADG1/pwhgrd20/datafile/undotbs1.263.619034627
+DATADG1/pwhgrd20/datafile/sysaux.264.619034633
+DATADG1/pwhgrd20/datafile/users.266.619034639
+DATADG1/pwhgrd20/datafile/mgmt_tablespace.279.629708905
+DATADG1/pwhgrd20/datafile/mgmt_ecm_depot_ts.280.629709499
+DATADG1/pwhgrd20/datafile/bearmon.268.619705149
+DATADG1/pwhgrd20/datafile/mgmt_tablespace.277.629285163
+DATADG1/pwhgrd20/datafile/undotbs2.278.631236955
+DATADG1/pwhgrd20/datafile/mgmt_tablespace.276.641683063
+DATADG1/pwhgrd20/datafile/mgmt_tablespace.287.657955169

NAME
--------------------------------------------------------------------------------
+DATADG1/pwhgrd20/datafile/bearaudit.282.645449195
+DATADG1/pwhgrd20/datafile/mgmt_tablespace.273.685143701
+ARCHDG1/pwhgrd20/datafile/mgmt_tablespace.994.686140129
+ARCHDG1/pwhgrd20/datafile/sysaux.478.686918653
+ARCHDG1/pwhgrd20/datafile/mgmt_ecm_depot_ts.938.688263225
+DATADG1/pwhgrd20/datafile/mgmt_tablespace.292.689859511

17 rows selected.
####

While the datafiles arre in ASM in differeent directory structure

###
ASMCMD [+] > ls
ARCHDG1/
DATADG1/
ASMCMD [+] > cd DATADG1/
ASMCMD [+DATADG1] > ls
BNYGRD20/
DB_UNKNOWN/
ASMCMD [+DATADG1] > cd BNYGRD20/
ASMCMD [+DATADG1/BNYGRD20] > ls
CONTROLFILE/
DATAFILE/
DATAGUARDCONFIG/
ONLINELOG/
TEMPFILE/
dr1.dat
dr2.dat
spfilebnygrd20.ora
ASMCMD [+DATADG1/BNYGRD20] > cd DATAFILE/
ASMCMD [+DATADG1/BNYGRD20/DATAFILE] > ls
BEARAUDIT.313.693183871
BEARMON.302.693183873
MGMT_ECM_DEPOT_TS.304.693183871
MGMT_TABLESPACE.269.693183869
MGMT_TABLESPACE.272.693183863
MGMT_TABLESPACE.273.693183865
MGMT_TABLESPACE.281.693183869
MGMT_TABLESPACE.307.693183867
MGMT_TABLESPACE.310.693183867
SYSAUX.315.693183871
SYSTEM.295.693183871
UNDOTBS1.293.693183871
UNDOTBS2.278.693183871
USERS.301.693183871

######


Starting MRP with this control file without modifying this file structure will give error like below when starting MRP.

###

Errors in file /opt/oracle/admin/PWHGRD20/bdump/pwhgrd21_mrp0_32314.trc:
ORA-01110: data file 1: '+DATADG1/pwhgrd20/datafile/system.262.619034617'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATADG1/pwhgrd20/datafile/system.262.619034617'
Tue Jan 19 23:52:43 2010
MRP0: Background Media Recovery process shutdown (PWHGRD21)
###


11) So If the primary and standby database data file directories are different, then in RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just-cataloged data files. For example:

RMAN> CATALOG START WITH '+DATA_1/CHICAGO/DATAFILE/';
RMAN> SWITCH DATABASE TO COPY;



#########
$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Jan 20 00:10:43 2010

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

connected to target database: PWHGRD20 (DBID=1812226798, not open)

RMAN> CATALOG START WITH '+DATADG1/BNYGRD20/DATAFILE/';

using target database control file instead of recovery catalog
searching for all files that match the pattern +DATADG1/BNYGRD20/DATAFILE/

List of Files Unknown to the Database
=====================================
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.272.693183863
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.273.693183865
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.310.693183867
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.307.693183867
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.269.693183869
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.281.693183869
File Name: +datadg1/BNYGRD20/DATAFILE/UNDOTBS2.278.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/UNDOTBS1.293.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/SYSTEM.295.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_ECM_DEPOT_TS.304.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/BEARAUDIT.313.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/SYSAUX.315.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/USERS.301.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/BEARMON.302.693183873

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

List of Cataloged Files
=======================
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.272.693183863
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.273.693183865
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.310.693183867
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.307.693183867
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.269.693183869
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.281.693183869
File Name: +datadg1/BNYGRD20/DATAFILE/UNDOTBS2.278.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/UNDOTBS1.293.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/SYSTEM.295.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/MGMT_ECM_DEPOT_TS.304.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/BEARAUDIT.313.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/SYSAUX.315.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/USERS.301.693183871
File Name: +datadg1/BNYGRD20/DATAFILE/BEARMON.302.693183873

RMAN> CATALOG START WITH '+ARCHDG1/BNYGRD20/DATAFILE/';

searching for all files that match the pattern +ARCHDG1/BNYGRD20/DATAFILE/

List of Files Unknown to the Database
=====================================
File Name: +archdg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.689.693183863
File Name: +archdg1/BNYGRD20/DATAFILE/MGMT_ECM_DEPOT_TS.631.693183871
File Name: +archdg1/BNYGRD20/DATAFILE/SYSAUX.393.693183871

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

List of Cataloged Files
=======================
File Name: +archdg1/BNYGRD20/DATAFILE/MGMT_TABLESPACE.689.693183863
File Name: +archdg1/BNYGRD20/DATAFILE/MGMT_ECM_DEPOT_TS.631.693183871
File Name: +archdg1/BNYGRD20/DATAFILE/SYSAUX.393.693183871

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATADG1/bnygrd20/datafile/system.295.693183871"
datafile 2 switched to datafile copy "+DATADG1/bnygrd20/datafile/undotbs1.293.693183871"
datafile 3 switched to datafile copy "+DATADG1/bnygrd20/datafile/sysaux.315.693183871"
datafile 4 switched to datafile copy "+DATADG1/bnygrd20/datafile/users.301.693183871"
datafile 5 switched to datafile copy "+DATADG1/bnygrd20/datafile/mgmt_tablespace.307.693183867"
datafile 6 switched to datafile copy "+DATADG1/bnygrd20/datafile/mgmt_ecm_depot_ts.304.693183871"
datafile 7 switched to datafile copy "+DATADG1/bnygrd20/datafile/bearmon.302.693183873"
datafile 8 switched to datafile copy "+DATADG1/bnygrd20/datafile/mgmt_tablespace.272.693183863"
datafile 9 switched to datafile copy "+DATADG1/bnygrd20/datafile/undotbs2.278.693183871"
datafile 10 switched to datafile copy "+DATADG1/bnygrd20/datafile/mgmt_tablespace.310.693183867"
datafile 11 switched to datafile copy "+DATADG1/bnygrd20/datafile/mgmt_tablespace.273.693183865"
datafile 12 switched to datafile copy "+DATADG1/bnygrd20/datafile/bearaudit.313.693183871"
datafile 13 switched to datafile copy "+DATADG1/bnygrd20/datafile/mgmt_tablespace.281.693183869"
datafile 14 switched to datafile copy "+ARCHDG1/bnygrd20/datafile/mgmt_tablespace.689.693183863"
datafile 15 switched to datafile copy "+ARCHDG1/bnygrd20/datafile/sysaux.393.693183871"
datafile 16 switched to datafile copy "+ARCHDG1/bnygrd20/datafile/mgmt_ecm_depot_ts.631.693183871"
datafile 17 switched to datafile copy "+DATADG1/bnygrd20/datafile/mgmt_tablespace.269.693183869"

RMAN> exit


Recovery Manager complete.

#######

12) Now Start the MRP (database is already mounted)

It will pick correct file to apply th elogs.

SQL> alter database recover managed standby database disconnect from session;

Database altered.


RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 501841 237473 2 21956

Happy Learning!

Block Recovery using RMAN

The purpose of this article is to simulate a block level corruption and recover from the same using RMAN.
In this situation the datafile remains online throughout the recovery operation and hence other segments within the tablespace remain accessible.

Corrupt a block
Let us take the EMP table and find out the segment header of the same.
SQL> select header_file,header_block from dba_segments where segment_name='EMP';


HEADER_FILE HEADER_BLOCK
----------- ------------
4 24163

We now use the bbed utility to corrupt a block below the segment header which contains the actual data belonging to the EMP table.
In the exercise below we are corrupting the block which belongs to file 4 and block 24165

C:\>bbed parfile=bbed.par
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sat Feb 19 16:25:09 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 4,10
DBA 0x0100000a (16777226 4,10)

BBED> copy to block 24166

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: E:\ORACLE\ORADATA\TENG\USERS01.DBF (4)
Block: 24166 Offsets: 0 to 511 Dba:0x01005e66
------------------------------------------------------------------------
1e820000 09000001 33280000 00000104 2fe10000 04000000 11002d00 00000000
00000000 00780000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>


SQL> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 24165)
ORA-01110: data file 4: 'E:\ORACLE\ORADATA\TENG\USERS01.DBF'

Recover the block

We now run a validate database command which
- Checks datafiles for physical and logical corruption
- Confirms that all database files exist and are in the correct locations


RMAN> run {BACKUP VALIDATE DATABASE;}

Starting backup at 19-FEB-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=91 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=E:\ORACLE\ORADATA\TENG\USERS01.DBF
input datafile fno=00001 name=E:\ORACLE\ORADATA\TENG\SYSTEM01.DBF
input datafile fno=00002 name=E:\ORACLE\ORADATA\TENG\UNDOTBS01.DBF
input datafile fno=00003 name=E:\ORACLE\ORADATA\TENG\SYSAUX01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:11
Finished backup at 19-FEB-05


SQL> select * from V$backup_corruption;

RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MAR CO
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------------------
1 550688483 550688389 46 1 4 24165 1 0 YES CORRUPT

The above output confirms that block 24165 in file 4 is indeed corrupt!
We can recover the same using the following command.

RMAN> run {blockrecover datafile 4 block 24165;}

Starting blockrecover at 19-FEB-05
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=E:\ORACLE\FLASH_RECOVERY_AREA\TENG\BACKUPSET\2005_02_19\O1_MF_NNNDF_TAG20050219T164615_11FNO9BQ_.BKP tag=TAG20050219T164615
channel ORA_DISK_1: block restore complete

starting media recovery
media recovery complete

Finished blockrecover at 19-FEB-05

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected