Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for March, 2011

REINSTATE STANDBY – DATAGUARD BROKER – ISSUES

Posted by Hendry chinnapparaj on March 1, 2011

REINSTATE STANDBY  – DATAGUARD BROKER – ISSUES

Problem:-

The Primary database / network is not available, so you did a Successful failover to Standby using Dataguard Broker – DR Site – is the PRIMARY NOW.

Then you try to reinstate the old primary as standby when you encounter errors

Errors

Dataguard error

ORA-16795: database resource guard detects that database re-creation is required

Primary alert log error

ORA-16009: remote archive log destination must be a STANDBY database

Tue Mar  1 11:38:11 2011

Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

LGWR: Failed to archive log 3 thread 1 sequence 9 (16009)

Standby alert log error

ORA-16009: remote archive log destination must be a STANDBY database

For failover:

DGMGRL> failover to zoeprd_js;

Performing failover NOW, please wait…

Failover succeeded, new primary is “zoeprd_js”

DGMGRL> failover to ‘ZOEPRD_js’;

Performing failover NOW, please wait…

Failover succeeded, new primary is “ZOEPRD_js”

DGMGRL> show configuration;

Configuration

Name:                ZOEPRD_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database (disabled)

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

SUCCESS

Then you try to reinstate original primary as standby

In primary server – after backup alive

DGMGRL> connect sys/*****

Connected.

DGMGRL> reinstate database ‘ZOEPRD_fc’

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> show configuration

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> enable configuration

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> show configuration

Configuration

Name:                ZOEPRD_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

Warning: ORA-16607: one or more databases have failed

New Primary Error in Alert log

Tue Mar  1 11:38:11 2011

LGWR: Error 16009 disconnecting from destination LOG_ARCHIVE_DEST_2                                  standby host ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOS                                 T=prdu009n2)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ZOEPRD_fc_XPT)(                                 INSTANCE_NAME=zoeprd)(SERVER=dedicated)))’

LGWR: Error 16009 creating archivelog file ‘(DESCRIPTION=(ADDRESS_LI                                 ST=(ADDRESS=(PROTOCOL=tcp)(HOST=prdu009n2)(PORT=1521)))(CONNECT_DATA                                 =(SERVICE_NAME=ZOEPRD_fc_XPT)(INSTANCE_NAME=zoeprd)(SERVER=dedicated                                 )))’

Tue Mar  1 11:38:11 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_lgwr_3670184.tr                                 c:

ORA-16009: remote archive log destination must be a STANDBY database

Tue Mar  1 11:38:11 2011

Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

LGWR: Failed to archive log 3 thread 1 sequence 9 (16009)

New Standby  Error in Alert log

Redo Shipping Client Connected as PUBLIC

— Connected User is Valid

RFS[11]: Assigned to RFS process 9895980

RFS[11]: Database mount ID mismatch [0xa0162732:0xa015e92e]

RFS[11]: Not using real application clusters

Tue Mar  1 11:43:15 2011

Errors in file /u01/oracle/admin/zoeprd/udump/zoeprd_rfs_9895980.trc:

ORA-16009: remote archive log destination must be a STANDBY database

WORKAROUND Solution:-

Pre-requisite:- Flashback has to be ON for Primary & DR for this Solution to Work

Disable the Broker

DGMGRL> disable configuration

1.1.1.1        Steps as per oracle10g doc Step 4.2.10 – Establish Original Primary as Standby Using Flashback (Optional)

Once access to the failed production site is restored and if you had flashback database enabled, you can reinstate the original primary database as a physical standby of the new primary database.

On the standby site, note the SCN when the database became primary:

select to_char(standby_became_primary_scn) from v$database;

On the original production site, flashback and start managed recovery:

shutdown immediate;
startup mount;
flashback database to scn <standby_became_primary_scn>;
alter database convert to physical standby;
shutdown immediate;
startup mount;
alter database recover managed standby database using current logfile disconnect;

NEW PRIMARY SERVER

SQL> select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)

—————————————-

29609886505

ORIGINAL PRIMARY –

shutdown immediate;

startup mount;

flashback database to scn 29609886505;

alter database convert to physical standby;

shutdown immediate;

startup mount;

NEW PRIMARY SERVER

DGMGRL> show configuration

Configuration

Name:                ZOEPRD_dgb

Enabled:             NO

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

DISABLED

DGMGRL> enable configuration

Enabled.

DGMGRL> show configuration

Configuration

Name:                ZOEPRD_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

ZOEPRD_fc – Physical standby database

ZOEPRD_js – Primary database

Current status for “ZOEPRD_dgb”:

SUCCESS

SQL> select protection_mode from v$database;

PROTECTION_MODE

——————–

MAXIMUM PERFORMANCE

zoeprd:/u01/oracle> dgrmgrl sys/*****

ksh: dgrmgrl:  not found

zoeprd:/u01/oracle> dgmgrl sys/****

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

Check in primary & dr

SQL> select protection_mode from v$database;

PROTECTION_MODE

——————–

MAXIMUM AVAILABILITY

Posted in Data Guard and DR | Leave a Comment »