Monday, January 14, 2013

recovering standby database from incremental SCN from primary

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

No comments:

Post a Comment