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