Copy control file from Primary to Stanby DB

DataGuard - Copy control file from Primary to Standby DB

Steps to do the control file copy to standby database. There are number of times you need to do the same in DG environment.

1. Create the standby control file from Primary database.
      
          ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/test_stdby.ctl';

2. Shutdown the the standby database.
3. Transfer the file from Primary to the standby server, copy to the control file locations.
4. startup mount  (standby)
5. switch on the flashback if it was on before you copy the controlfile.

         ALTER DATABASE FLASHBACK ON;

Note: Flashback is required for FSFO configuration.

Now the above seems pretty straight forward - but sometimes you may see dataguard configuration is broken after the controlfile copy from Primary to standby, here is the case -

DGMGRL> show configuration verbose

Configuration - FSF

  Protection Mode: MaxAvailability
  Databases:
    test_a - Primary database
      Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database

    test_b - (*) Physical standby database
      Warning: ORA-16817: unsynchronized fast-start failover configuration

  (*) Fast-Start Failover target


Cause:
REAL TIME APPLY is not active as standby redologs are created after the controlfile copy from the primary and once you have copied the old control file from primary it doesn't have the info regarding the new standby redo logs.

Fix:

Drop and recreate the standby redologs on standby database.

1. check the status if any standby redolog is active, if none - that means RFS is not using standby redo logs, lets recreate the same.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024/1024 ,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE# BYTES/1024/1024/1024 STATUS
---------- ---------- ---------- -------------------- ----------
        21          1          0                    1 UNASSIGNED
        22          1          0                    1 UNASSIGNED
        23          0          0                    1 UNASSIGNED
        24          0          0                    1 UNASSIGNED
        25          0          0                    1 UNASSIGNED
        26          0          0                    1 UNASSIGNED
        27          0          0                    1 UNASSIGNED
        28          0          0                    1 UNASSIGNED
        29          0          0                    1 UNASSIGNED

1. stop the MRP in standby database -

alter database recover managed standby database cancel;

2. Drop the standby redo logs.

ALTER DATABASE DROP STANDBY LOGFILE GROUP 21;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 22;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 23;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 24;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 25;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 26;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 27;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 28;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 29;

3. Recreate all with reuse.

alter database add standby logfile group 21 '/redo-01/databases/test/stby-g21-m1.log'  size 1073741824 reuse;
alter database add standby logfile group 22 '/redo-01/databases/test/stby-g22-m1.log'  size 1073741824 reuse;
alter database add standby logfile group 23 '/redo-01/databases/test/stby-g23-m1.log'  size 1073741824 reuse;
alter database add standby logfile group 24 '/redo-01/databases/test/stby-g24-m1.log'  size 1073741824 reuse;
alter database add standby logfile group 25 '/redo-01/databases/test/stby-g25-m1.log'  size 1073741824 reuse;
alter database add standby logfile group 26 '/redo-01/databases/test/stby-g26-m1.log'  size 1073741824 reuse;
alter database add standby logfile group 27 '/redo-01/databases/test/stby-g27-m1.log'  size 1073741824 reuse;
alter database add standby logfile group 28 '/redo-01/databases/test/stby-g28-m1.log'  size 1073741824 reuse;
alter database add standby logfile group 29 '/redo-01/databases/test/stby-g29-m1.log'  size 1073741824 reuse;

4. Done. check the dgmgrl status.

DGMGRL> show configuration verbose

Configuration - FSF

  Protection Mode: MaxAvailability
  Databases:
    test_a - Primary database
    test_b - (*) Physical standby database

  (*) Fast-Start Failover target

ORA-16664: unable to receive the result from a database

Error Code: ORA-16664: unable to receive the result from a database

Case: You can see the redo shipping is working fine from Primary to standby DB. you can notice this error when you try to enable the DG broker.

Test the following:

1. connection to standby from Primary server - should be good if the redo shipping is fine

2. connection from standby to Primary server

This could be the cause - As during normal redo shipping we don't need the vice versa connection from standby to Primary but when you enable the DG Broker it needs that for the auto switchover, fail over.

From standby server, try to connect to the Primary DB

sqlplus sys@dg1_a as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 19 05:46:02 2013

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

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

In My case  the issue is because I changed the tnsnames port to replicate the issue. 

From the standby drc*log also you can make out the same -

drc*.log location - same as alert log.

$ tail -f drc$ORACLE_SID.log
06/19/2013 05:37:54
Failed to send message to site dg1_a. Error code is ORA-16501.
06/19/2013 05:39:09
Failed to send message to site dg1_a. Error code is ORA-16501.
06/19/2013 05:40:24
Failed to send message to site dg1_a. Error code is ORA-16501.
06/19/2013 05:41:39
Failed to send message to site dg1_a. Error code is ORA-16501.
06/19/2013 05:42:44

Fix:

Check the tnsnames in the standby server and make it similar to the Primary server. Make sure you have right ports in tnsnames which is used in listener.ora and local_listener parameter.

Some other causes for the same issue:

The logon trigger was preventing the connection from the standby database during a switchover.

Fix: disable the logon trigger in primary database.  (Metalink id 1530881.1)

Check the non default port used in connect string of both primary and standby(tnsnames.ora). Metalink id 1228797.1