One of my standby database was out of sync for 15 days and we didn't have our archivelogs to recover it, so i had followed the below steps to recover it by taking a incremental backup from primary
Step1
=======
Find the difference in SCN from primary Database and standby database.
---------
primary
---------
SQL> select NAME,CURRENT_SCN from v$database;
NAME CURRENT_SCN
--------- -----------
ORCL 15819959
----------
standby
----------
SQL> select NAME,CURRENT_SCN from v$database;
NAME CURRENT_SCN
--------- -----------
ORCL 11281979
step2
======
Take a incremental backup from primary database form the required SCN number as below , move and catalog the backups at standby database
-----------------------------
Incremental backup at primary
------------------------------
[oracle@subhen_prim07 backup_ORCL]$ pwd
/oracle/app/backup_ORCL
[oracle@subhen_prim07 backup_ORCL]$ echo $ORACLE_SID
ORCL1
[oracle@subhen_prim07 backup_ORCL]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 11 17:30:29 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1213379060)
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 11281979 DATABASE FORMAT '/oracle/app/backup_ORCL/bkupsby_%U';
Starting backup at 11-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=71 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=137 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=203 instance=ORCL1 device type=DISK
backup will be obsolete on date 18-JAN-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=+DATA/ORCL/datafile/wcccontts.1446.799932363
input datafile file number=00005 name=+DATA/ORCL/datafile/undotbs2.1388.799870311
input datafile file number=00004 name=+DATA/ORCL/datafile/users.1381.799870137
input datafile file number=00010 name=+DATA/ORCL/datafile/nwcont_ias_discuss.1439.799930727
channel ORA_DISK_1: starting piece 1 at 11-JAN-13
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00016 name=+DATA/ORCL/datafile/wcpcontts.1445.799932339
input datafile file number=00003 name=+DATA/ORCL/datafile/undotbs1.1380.799870137
input datafile file number=00009 name=+DATA/ORCL/datafile/nwcont_ias_portlet.1438.799930725
channel ORA_DISK_2: starting piece 1 at 11-JAN-13
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/ORCL/datafile/sysaux.1379.799870137
input datafile file number=00014 name=+DATA/ORCL/datafile/nwcont_urmserver.1443.799930741
input datafile file number=00015 name=+DATA/ORCL/datafile/nwcont_ipm.1444.799930747
input datafile file number=00011 name=+DATA/ORCL/datafile/nwcont_ocssearch.1440.799930729
input datafile file number=00008 name=+DATA/ORCL/datafile/nwcont_orairm.1437.799930725
channel ORA_DISK_3: starting piece 1 at 11-JAN-13
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORCL/datafile/system.1378.799870137
input datafile file number=00012 name=+DATA/ORCL/datafile/nwcont_ias_activity.1441.799930729
input datafile file number=00013 name=+DATA/ORCL/datafile/nwcont_ocs.1442.799930735
input datafile file number=00007 name=+DATA/ORCL/datafile/nwcont_ias_webcenter.1436.799930721
input datafile file number=00006 name=+DATA/ORCL/datafile/nwcont_mds.1435.799930719
channel ORA_DISK_4: starting piece 1 at 11-JAN-13
channel ORA_DISK_1: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_2: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_4: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_3: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:36
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
backup will be obsolete on date 18-JAN-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-JAN-13
channel ORA_DISK_1: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_ksnv5ore_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-JAN-13
--------------------------------------
catalog backup pieces at standby site
--------------------------------------
[oracle@subhen_sby07 backup_ORCL]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 11 17:46:10 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1213379060, not open)
RMAN> catalog start with '/oracle/app/backup_ORCL/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/app/backup_ORCL/
List of Files Unknown to the Database
=====================================
File Name: /oracle/app/backup_ORCL/bkupsby_ksnv5ore_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1
File Name: /oracle/app/backup_ORCL/forstandbyctrl.bck
File Name: /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_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: /oracle/app/backup_ORCL/bkupsby_ksnv5ore_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1
File Name: /oracle/app/backup_ORCL/forstandbyctrl.bck
File Name: /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1
Step3
==========
Do the recover of the standby database
------------------------
recover standby database
------------------------
RMAN> recover database noredo;
Starting recover at 11-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=518 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=582 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=645 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=710 instance=ORCLSBY1 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/ORCLSBY/datafile/system.1253.800020585
destination for restore of datafile 00006: +DATA/ORCLSBY/datafile/nwcont_mds.1261.800020819
destination for restore of datafile 00007: +DATA/ORCLSBY/datafile/nwcont_ias_webcenter.1258.800020741
destination for restore of datafile 00012: +DATA/ORCLSBY/datafile/nwcont_ias_activity.1255.800020655
destination for restore of datafile 00013: +DATA/ORCLSBY/datafile/nwcont_ocs.1257.800020717
channel ORA_DISK_1: reading from backup piece /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: +DATA/ORCLSBY/datafile/sysaux.1254.800020621
destination for restore of datafile 00008: +DATA/ORCLSBY/datafile/nwcont_orairm.1262.800020835
destination for restore of datafile 00011: +DATA/ORCLSBY/datafile/nwcont_ocssearch.1260.800020793
destination for restore of datafile 00014: +DATA/ORCLSBY/datafile/nwcont_urmserver.1256.800020681
destination for restore of datafile 00015: +DATA/ORCLSBY/datafile/nwcont_ipm.1259.800020767
channel ORA_DISK_2: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1
channel ORA_DISK_3: starting incremental datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: +DATA/ORCLSBY/datafile/undotbs1.1264.800020865
destination for restore of datafile 00009: +DATA/ORCLSBY/datafile/nwcont_ias_portlet.1266.800020875
destination for restore of datafile 00016: +DATA/ORCLSBY/datafile/wcpcontts.1251.800020377
channel ORA_DISK_3: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1
channel ORA_DISK_4: starting incremental datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: +DATA/ORCLSBY/datafile/users.1267.800020883
destination for restore of datafile 00005: +DATA/ORCLSBY/datafile/undotbs2.1265.800020873
destination for restore of datafile 00010: +DATA/ORCLSBY/datafile/nwcont_ias_discuss.1263.800020849
destination for restore of datafile 00017: +DATA/ORCLSBY/datafile/wcccontts.1252.800020481
channel ORA_DISK_4: reading from backup piece /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/11/2013 17:48:00
ORA-19870: error while restoring backup piece /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1
--failed because the managed recovery was active for standby database, so cancel the managed recovery first and retry recovery
[oracle@subhen_sby07 backup_ORCL]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 11 17:49:41 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2234960 bytes
Variable Size 2432697776 bytes
Database Buffers 1728053248 bytes
Redo Buffers 12582912 bytes
Database mounted.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> exit
RMAN> recover database noredo;
Starting recover at 11-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=515 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=579 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=643 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=708 instance=ORCLSBY1 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/ORCLSBY/datafile/system.1253.800020585
destination for restore of datafile 00006: +DATA/ORCLSBY/datafile/nwcont_mds.1261.800020819
destination for restore of datafile 00007: +DATA/ORCLSBY/datafile/nwcont_ias_webcenter.1258.800020741
destination for restore of datafile 00012: +DATA/ORCLSBY/datafile/nwcont_ias_activity.1255.800020655
destination for restore of datafile 00013: +DATA/ORCLSBY/datafile/nwcont_ocs.1257.800020717
channel ORA_DISK_1: reading from backup piece /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: +DATA/ORCLSBY/datafile/sysaux.1254.800020621
destination for restore of datafile 00008: +DATA/ORCLSBY/datafile/nwcont_orairm.1262.800020835
destination for restore of datafile 00011: +DATA/ORCLSBY/datafile/nwcont_ocssearch.1260.800020793
destination for restore of datafile 00014: +DATA/ORCLSBY/datafile/nwcont_urmserver.1256.800020681
destination for restore of datafile 00015: +DATA/ORCLSBY/datafile/nwcont_ipm.1259.800020767
channel ORA_DISK_2: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1
channel ORA_DISK_3: starting incremental datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: +DATA/ORCLSBY/datafile/undotbs1.1264.800020865
destination for restore of datafile 00009: +DATA/ORCLSBY/datafile/nwcont_ias_portlet.1266.800020875
destination for restore of datafile 00016: +DATA/ORCLSBY/datafile/wcpcontts.1251.800020377
channel ORA_DISK_3: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1
channel ORA_DISK_4: starting incremental datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: +DATA/ORCLSBY/datafile/users.1267.800020883
destination for restore of datafile 00005: +DATA/ORCLSBY/datafile/undotbs2.1265.800020873
destination for restore of datafile 00010: +DATA/ORCLSBY/datafile/nwcont_ias_discuss.1263.800020849
destination for restore of datafile 00017: +DATA/ORCLSBY/datafile/wcccontts.1252.800020481
channel ORA_DISK_4: reading from backup piece /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1
channel ORA_DISK_1: piece handle=/oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1 tag=TAG20130111T173046
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_4: piece handle=/oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1 tag=TAG20130111T173046
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:07
channel ORA_DISK_3: piece handle=/oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1 tag=TAG20130111T173046
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:15
channel ORA_DISK_2: piece handle=/oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1 tag=TAG20130111T173046
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:55
Finished recover at 11-JAN-13
-- start the managed recovery in standby database again
[recover managed standby database cancel;]
[alter database recover managed standby database using current logfile disconnect from session;]
here in the alert log of standby we will find still it is looking for older archivelogs this is due to the control file is still the older one. so we will restore the standby controlfile from primary
-- backup standby control file from primary
RMAN> backup device type disk current controlfile for standby format '/oracle/app/backup_ORCL/forstandbyctrl.bck';
Starting backup at 11-JAN-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-JAN-13
channel ORA_DISK_1: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/forstandbyctrl.bck tag=TAG20130111T173139 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-JAN-13
Starting Control File and SPFILE Autobackup at 11-JAN-13
piece handle=+BACKUP/ORCL/autobackup/2013_01_11/s_804447105.6477.804447105 comment=NONE
Finished Control File and SPFILE Autobackup at 11-JAN-13
-- move and catalog the controlfile in standby and restore the controlfile
RMAN> restore standby controlfile from '/oracle/app/backup_ORCL/forstandbyctrl.bck';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/05/2013 14:49:30
RMAN-06496: must use the TO clause when the database is mounted or open
-- put the standby database in nomount mode and try again
[oracle@subhen_sby01 backup_ORCL]$ srvctl stop database -d ORCLB -o immediate
[oracle@subhen_sby01 backup_pptnwaut]$ echo $ORACLE_SID
ORCLB2
[oracle@subhen_sby01 backup_pptnwaut]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 5 14:53:21 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 3140026368 bytes
Fixed Size 2232472 bytes
Variable Size 788533096 bytes
Database Buffers 2332033024 bytes
Redo Buffers 17227776 bytes
-- restore again
RMAN> restore standby controlfile from '/oracle/app/backup_ORCL/forstandbyctrl.bck';
Starting restore at 11-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=899 instance=ORCLSBY1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/ORCLSBY/controlfile/current.1250.800020347
output file name=+FRA/ORCLSBY/controlfile/current.102570.800020347
Finished restore at 11-JAN-13
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
-- try the managed recovery again, here we might find the below error in alert log of standby due to datafile mismatch so use the alter database file rename command
[match the file id from primary database(v$datafile) and standby database (v$datafile)]
Managed Standby Recovery starting Real Time Apply
Fri Jan 11 18:06:38 2013
Errors in file /oracle/app/diag/rdbms/ORCLSBY/ORCLSBY1/trace/ORCLSBY1_dbw0_16388.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/ORCL/datafile/system.1378.799870137'
ORA-17503: ksfdopn:2 Failed to open file +DATA/ORCL/datafile/system.1378.799870137
ORA-15012: ASM file '+DATA/ORCL/datafile/system.1378.799870137' does not exist
Errors in file /oracle/app/diag/rdbms/ORCLSBY/ORCLSBY1/trace/ORCLSBY1_dbw0_16388.trc:
-- rename file at standby database
[before that make standby_file_management to manual after rename complete change the parameter again to auto]
SQL> alter system set standby_file_management=MANUAL scope=both sid='*';
SQL> alter database rename file '+DATA/ORCL/datafile/sysaux.1379.799870137' to '+DATA/ORCLSBY/DATAFILE/SYSAUX.1254.800020621';
Database altered.
SQL> alter system set standby_file_management=AUTO scope=both sid='*';
-- shutdown and restart standby database in mount state and check for archive_log_dest parameters and enable managed recovery again
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
[oracle@subhen_sby07 backup_ORCL]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 11 18:29:28 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2234960 bytes
Variable Size 2449474992 bytes
Database Buffers 1711276032 bytes
Redo Buffers 12582912 bytes
Database mounted.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
-- in my case log_archive_dest_3 value was wrongly populated so i disabled that in standby database
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string location="USE_DB_RECOVERY_FILE
_DEST", valid_for=(STANDBY_LO
GFILE,STANDBY_ROLE)
SQL> alter system set log_archive_dest_3=' ' scope=both sid='*';
-- finally check the archivelogs from both primary and standby database
========================================
Dataguard broker configuration redo
========================================
After the standby databases back into sync i found that the DG broker configuaration is not working, so i had to redo the configuration these are the steps i followed
[oracle@subhen_prim07 backup_PPTNWAUT]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration
Configuration - ORCLdg
Protection Mode: MaxAvailability
Databases:
ORCL - Primary database
Warning: ORA-16809: multiple warnings detected for the database
ORCLSBY - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
-- disable configuration and remove it
DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Removed configuration
-- login to both primary and standby Database disable broker and remove the broker files from ASM location of both servers
SQL> alter system set dg_broker_start=FALSE scope=both sid='*';
-- recreate the configuration
[oracle@subhen_prim07 backup_PPTNWAUT]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@ORCL
Password:
Connected.
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL> create configuration ORCLDG as primary database is 'ORCL' connect identifier is 'ORCL';
Configuration "ORCLdg" created with primary database "ORCL"
DGMGRL> add database 'ORCLSBY' as connect identifier is 'ORCLSBY' maintained as physical;
Database "ORCLSBY" added
DGMGRL> show configuration
Configuration - ORCLdg
Protection Mode: MaxAvailability
Databases:
ORCL - Primary database
ORCLSBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - ORCLdg
Protection Mode: MaxAvailability
Databases:
ORCL - Primary database
ORCLSBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database verbose 'ORCL';
Database - ORCL
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORCL1
ORCL2
Properties:
DGConnectIdentifier = 'ORCL'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/ORCLSBY, +DATA/ORCL'
LogFileNameConvert = '+FRA/ORCLSBY, +FRA/ORCL'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Database Status:
SUCCESS
DGMGRL> show database verbose 'ORCLSBY';
Database - ORCLSBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
ORCLSBY1
Properties:
DGConnectIdentifier = 'ORCLSBY'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/ORCLSBY, +DATA/ORCL'
LogFileNameConvert = '+FRA/ORCLSBY, +FRA/ORCL'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'ORCLSBY1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.62.107)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=ORCLSBY_DGMGRL)(INSTANCE_NAME=ORCLSBY1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+BACKUP'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
Step1
=======
Find the difference in SCN from primary Database and standby database.
---------
primary
---------
SQL> select NAME,CURRENT_SCN from v$database;
NAME CURRENT_SCN
--------- -----------
ORCL 15819959
----------
standby
----------
SQL> select NAME,CURRENT_SCN from v$database;
NAME CURRENT_SCN
--------- -----------
ORCL 11281979
step2
======
Take a incremental backup from primary database form the required SCN number as below , move and catalog the backups at standby database
-----------------------------
Incremental backup at primary
------------------------------
[oracle@subhen_prim07 backup_ORCL]$ pwd
/oracle/app/backup_ORCL
[oracle@subhen_prim07 backup_ORCL]$ echo $ORACLE_SID
ORCL1
[oracle@subhen_prim07 backup_ORCL]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 11 17:30:29 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1213379060)
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 11281979 DATABASE FORMAT '/oracle/app/backup_ORCL/bkupsby_%U';
Starting backup at 11-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=71 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=137 instance=ORCL1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=203 instance=ORCL1 device type=DISK
backup will be obsolete on date 18-JAN-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=+DATA/ORCL/datafile/wcccontts.1446.799932363
input datafile file number=00005 name=+DATA/ORCL/datafile/undotbs2.1388.799870311
input datafile file number=00004 name=+DATA/ORCL/datafile/users.1381.799870137
input datafile file number=00010 name=+DATA/ORCL/datafile/nwcont_ias_discuss.1439.799930727
channel ORA_DISK_1: starting piece 1 at 11-JAN-13
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00016 name=+DATA/ORCL/datafile/wcpcontts.1445.799932339
input datafile file number=00003 name=+DATA/ORCL/datafile/undotbs1.1380.799870137
input datafile file number=00009 name=+DATA/ORCL/datafile/nwcont_ias_portlet.1438.799930725
channel ORA_DISK_2: starting piece 1 at 11-JAN-13
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/ORCL/datafile/sysaux.1379.799870137
input datafile file number=00014 name=+DATA/ORCL/datafile/nwcont_urmserver.1443.799930741
input datafile file number=00015 name=+DATA/ORCL/datafile/nwcont_ipm.1444.799930747
input datafile file number=00011 name=+DATA/ORCL/datafile/nwcont_ocssearch.1440.799930729
input datafile file number=00008 name=+DATA/ORCL/datafile/nwcont_orairm.1437.799930725
channel ORA_DISK_3: starting piece 1 at 11-JAN-13
channel ORA_DISK_4: starting compressed full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORCL/datafile/system.1378.799870137
input datafile file number=00012 name=+DATA/ORCL/datafile/nwcont_ias_activity.1441.799930729
input datafile file number=00013 name=+DATA/ORCL/datafile/nwcont_ocs.1442.799930735
input datafile file number=00007 name=+DATA/ORCL/datafile/nwcont_ias_webcenter.1436.799930721
input datafile file number=00006 name=+DATA/ORCL/datafile/nwcont_mds.1435.799930719
channel ORA_DISK_4: starting piece 1 at 11-JAN-13
channel ORA_DISK_1: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_2: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_4: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_3: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:36
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
backup will be obsolete on date 18-JAN-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-JAN-13
channel ORA_DISK_1: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/bkupsby_ksnv5ore_1_1 tag=TAG20130111T173046 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-JAN-13
--------------------------------------
catalog backup pieces at standby site
--------------------------------------
[oracle@subhen_sby07 backup_ORCL]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 11 17:46:10 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1213379060, not open)
RMAN> catalog start with '/oracle/app/backup_ORCL/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/app/backup_ORCL/
List of Files Unknown to the Database
=====================================
File Name: /oracle/app/backup_ORCL/bkupsby_ksnv5ore_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1
File Name: /oracle/app/backup_ORCL/forstandbyctrl.bck
File Name: /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_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: /oracle/app/backup_ORCL/bkupsby_ksnv5ore_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1
File Name: /oracle/app/backup_ORCL/forstandbyctrl.bck
File Name: /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1
File Name: /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1
Step3
==========
Do the recover of the standby database
------------------------
recover standby database
------------------------
RMAN> recover database noredo;
Starting recover at 11-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=518 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=582 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=645 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=710 instance=ORCLSBY1 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/ORCLSBY/datafile/system.1253.800020585
destination for restore of datafile 00006: +DATA/ORCLSBY/datafile/nwcont_mds.1261.800020819
destination for restore of datafile 00007: +DATA/ORCLSBY/datafile/nwcont_ias_webcenter.1258.800020741
destination for restore of datafile 00012: +DATA/ORCLSBY/datafile/nwcont_ias_activity.1255.800020655
destination for restore of datafile 00013: +DATA/ORCLSBY/datafile/nwcont_ocs.1257.800020717
channel ORA_DISK_1: reading from backup piece /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: +DATA/ORCLSBY/datafile/sysaux.1254.800020621
destination for restore of datafile 00008: +DATA/ORCLSBY/datafile/nwcont_orairm.1262.800020835
destination for restore of datafile 00011: +DATA/ORCLSBY/datafile/nwcont_ocssearch.1260.800020793
destination for restore of datafile 00014: +DATA/ORCLSBY/datafile/nwcont_urmserver.1256.800020681
destination for restore of datafile 00015: +DATA/ORCLSBY/datafile/nwcont_ipm.1259.800020767
channel ORA_DISK_2: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1
channel ORA_DISK_3: starting incremental datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: +DATA/ORCLSBY/datafile/undotbs1.1264.800020865
destination for restore of datafile 00009: +DATA/ORCLSBY/datafile/nwcont_ias_portlet.1266.800020875
destination for restore of datafile 00016: +DATA/ORCLSBY/datafile/wcpcontts.1251.800020377
channel ORA_DISK_3: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1
channel ORA_DISK_4: starting incremental datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: +DATA/ORCLSBY/datafile/users.1267.800020883
destination for restore of datafile 00005: +DATA/ORCLSBY/datafile/undotbs2.1265.800020873
destination for restore of datafile 00010: +DATA/ORCLSBY/datafile/nwcont_ias_discuss.1263.800020849
destination for restore of datafile 00017: +DATA/ORCLSBY/datafile/wcccontts.1252.800020481
channel ORA_DISK_4: reading from backup piece /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/11/2013 17:48:00
ORA-19870: error while restoring backup piece /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1
--failed because the managed recovery was active for standby database, so cancel the managed recovery first and retry recovery
[oracle@subhen_sby07 backup_ORCL]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 11 17:49:41 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2234960 bytes
Variable Size 2432697776 bytes
Database Buffers 1728053248 bytes
Redo Buffers 12582912 bytes
Database mounted.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> exit
RMAN> recover database noredo;
Starting recover at 11-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=515 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=579 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=643 instance=ORCLSBY1 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=708 instance=ORCLSBY1 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/ORCLSBY/datafile/system.1253.800020585
destination for restore of datafile 00006: +DATA/ORCLSBY/datafile/nwcont_mds.1261.800020819
destination for restore of datafile 00007: +DATA/ORCLSBY/datafile/nwcont_ias_webcenter.1258.800020741
destination for restore of datafile 00012: +DATA/ORCLSBY/datafile/nwcont_ias_activity.1255.800020655
destination for restore of datafile 00013: +DATA/ORCLSBY/datafile/nwcont_ocs.1257.800020717
channel ORA_DISK_1: reading from backup piece /oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1
channel ORA_DISK_2: starting incremental datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: +DATA/ORCLSBY/datafile/sysaux.1254.800020621
destination for restore of datafile 00008: +DATA/ORCLSBY/datafile/nwcont_orairm.1262.800020835
destination for restore of datafile 00011: +DATA/ORCLSBY/datafile/nwcont_ocssearch.1260.800020793
destination for restore of datafile 00014: +DATA/ORCLSBY/datafile/nwcont_urmserver.1256.800020681
destination for restore of datafile 00015: +DATA/ORCLSBY/datafile/nwcont_ipm.1259.800020767
channel ORA_DISK_2: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1
channel ORA_DISK_3: starting incremental datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: +DATA/ORCLSBY/datafile/undotbs1.1264.800020865
destination for restore of datafile 00009: +DATA/ORCLSBY/datafile/nwcont_ias_portlet.1266.800020875
destination for restore of datafile 00016: +DATA/ORCLSBY/datafile/wcpcontts.1251.800020377
channel ORA_DISK_3: reading from backup piece /oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1
channel ORA_DISK_4: starting incremental datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: +DATA/ORCLSBY/datafile/users.1267.800020883
destination for restore of datafile 00005: +DATA/ORCLSBY/datafile/undotbs2.1265.800020873
destination for restore of datafile 00010: +DATA/ORCLSBY/datafile/nwcont_ias_discuss.1263.800020849
destination for restore of datafile 00017: +DATA/ORCLSBY/datafile/wcccontts.1252.800020481
channel ORA_DISK_4: reading from backup piece /oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1
channel ORA_DISK_1: piece handle=/oracle/app/backup_ORCL/bkupsby_krnv5oqb_1_1 tag=TAG20130111T173046
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_4: piece handle=/oracle/app/backup_ORCL/bkupsby_konv5oq9_1_1 tag=TAG20130111T173046
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:07
channel ORA_DISK_3: piece handle=/oracle/app/backup_ORCL/bkupsby_kpnv5oqa_1_1 tag=TAG20130111T173046
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:15
channel ORA_DISK_2: piece handle=/oracle/app/backup_ORCL/bkupsby_kqnv5oqa_1_1 tag=TAG20130111T173046
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:55
Finished recover at 11-JAN-13
-- start the managed recovery in standby database again
[recover managed standby database cancel;]
[alter database recover managed standby database using current logfile disconnect from session;]
here in the alert log of standby we will find still it is looking for older archivelogs this is due to the control file is still the older one. so we will restore the standby controlfile from primary
-- backup standby control file from primary
RMAN> backup device type disk current controlfile for standby format '/oracle/app/backup_ORCL/forstandbyctrl.bck';
Starting backup at 11-JAN-13
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 11-JAN-13
channel ORA_DISK_1: finished piece 1 at 11-JAN-13
piece handle=/oracle/app/backup_ORCL/forstandbyctrl.bck tag=TAG20130111T173139 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-JAN-13
Starting Control File and SPFILE Autobackup at 11-JAN-13
piece handle=+BACKUP/ORCL/autobackup/2013_01_11/s_804447105.6477.804447105 comment=NONE
Finished Control File and SPFILE Autobackup at 11-JAN-13
-- move and catalog the controlfile in standby and restore the controlfile
RMAN> restore standby controlfile from '/oracle/app/backup_ORCL/forstandbyctrl.bck';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/05/2013 14:49:30
RMAN-06496: must use the TO clause when the database is mounted or open
-- put the standby database in nomount mode and try again
[oracle@subhen_sby01 backup_ORCL]$ srvctl stop database -d ORCLB -o immediate
[oracle@subhen_sby01 backup_pptnwaut]$ echo $ORACLE_SID
ORCLB2
[oracle@subhen_sby01 backup_pptnwaut]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 5 14:53:21 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 3140026368 bytes
Fixed Size 2232472 bytes
Variable Size 788533096 bytes
Database Buffers 2332033024 bytes
Redo Buffers 17227776 bytes
-- restore again
RMAN> restore standby controlfile from '/oracle/app/backup_ORCL/forstandbyctrl.bck';
Starting restore at 11-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=899 instance=ORCLSBY1 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/ORCLSBY/controlfile/current.1250.800020347
output file name=+FRA/ORCLSBY/controlfile/current.102570.800020347
Finished restore at 11-JAN-13
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
-- try the managed recovery again, here we might find the below error in alert log of standby due to datafile mismatch so use the alter database file rename command
[match the file id from primary database(v$datafile) and standby database (v$datafile)]
Managed Standby Recovery starting Real Time Apply
Fri Jan 11 18:06:38 2013
Errors in file /oracle/app/diag/rdbms/ORCLSBY/ORCLSBY1/trace/ORCLSBY1_dbw0_16388.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/ORCL/datafile/system.1378.799870137'
ORA-17503: ksfdopn:2 Failed to open file +DATA/ORCL/datafile/system.1378.799870137
ORA-15012: ASM file '+DATA/ORCL/datafile/system.1378.799870137' does not exist
Errors in file /oracle/app/diag/rdbms/ORCLSBY/ORCLSBY1/trace/ORCLSBY1_dbw0_16388.trc:
-- rename file at standby database
[before that make standby_file_management to manual after rename complete change the parameter again to auto]
SQL> alter system set standby_file_management=MANUAL scope=both sid='*';
SQL> alter database rename file '+DATA/ORCL/datafile/sysaux.1379.799870137' to '+DATA/ORCLSBY/DATAFILE/SYSAUX.1254.800020621';
Database altered.
SQL> alter system set standby_file_management=AUTO scope=both sid='*';
-- shutdown and restart standby database in mount state and check for archive_log_dest parameters and enable managed recovery again
SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
[oracle@subhen_sby07 backup_ORCL]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jan 11 18:29:28 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4175568896 bytes
Fixed Size 2234960 bytes
Variable Size 2449474992 bytes
Database Buffers 1711276032 bytes
Redo Buffers 12582912 bytes
Database mounted.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
-- in my case log_archive_dest_3 value was wrongly populated so i disabled that in standby database
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string location="USE_DB_RECOVERY_FILE
_DEST", valid_for=(STANDBY_LO
GFILE,STANDBY_ROLE)
SQL> alter system set log_archive_dest_3=' ' scope=both sid='*';
-- finally check the archivelogs from both primary and standby database
========================================
Dataguard broker configuration redo
========================================
After the standby databases back into sync i found that the DG broker configuaration is not working, so i had to redo the configuration these are the steps i followed
[oracle@subhen_prim07 backup_PPTNWAUT]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration
Configuration - ORCLdg
Protection Mode: MaxAvailability
Databases:
ORCL - Primary database
Warning: ORA-16809: multiple warnings detected for the database
ORCLSBY - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
-- disable configuration and remove it
DGMGRL> disable configuration
Disabled.
DGMGRL> remove configuration
Removed configuration
-- login to both primary and standby Database disable broker and remove the broker files from ASM location of both servers
SQL> alter system set dg_broker_start=FALSE scope=both sid='*';
-- recreate the configuration
[oracle@subhen_prim07 backup_PPTNWAUT]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@ORCL
Password:
Connected.
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL
DGMGRL> create configuration ORCLDG as primary database is 'ORCL' connect identifier is 'ORCL';
Configuration "ORCLdg" created with primary database "ORCL"
DGMGRL> add database 'ORCLSBY' as connect identifier is 'ORCLSBY' maintained as physical;
Database "ORCLSBY" added
DGMGRL> show configuration
Configuration - ORCLdg
Protection Mode: MaxAvailability
Databases:
ORCL - Primary database
ORCLSBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration - ORCLdg
Protection Mode: MaxAvailability
Databases:
ORCL - Primary database
ORCLSBY - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database verbose 'ORCL';
Database - ORCL
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORCL1
ORCL2
Properties:
DGConnectIdentifier = 'ORCL'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/ORCLSBY, +DATA/ORCL'
LogFileNameConvert = '+FRA/ORCLSBY, +FRA/ORCL'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName(*)
StaticConnectIdentifier(*)
StandbyArchiveLocation(*)
AlternateLocation(*)
LogArchiveTrace(*)
LogArchiveFormat(*)
TopWaitEvents(*)
(*) - Please check specific instance for the property value
Database Status:
SUCCESS
DGMGRL> show database verbose 'ORCLSBY';
Database - ORCLSBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
ORCLSBY1
Properties:
DGConnectIdentifier = 'ORCLSBY'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '+DATA/ORCLSBY, +DATA/ORCL'
LogFileNameConvert = '+FRA/ORCLSBY, +FRA/ORCL'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'ORCLSBY1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.12.62.107)(PORT=1621))(CONNECT_DATA=(SERVICE_NAME=ORCLSBY_DGMGRL)(INSTANCE_NAME=ORCLSBY1)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '+BACKUP'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
No comments:
Post a Comment