Step 1

Validate the fal parameters mismatch: Run the query below on both primary and secondary DB to ensure that the fal parameters are matching. The expected output of the matching fal parameters of the primary and secondary DB is shown below.

SQL> show parameter fal
NAME          TYPE        VALUE
------------  ---------- --------
fal_client    string      cgms_p
fal_server    string      cgms_s

SQL> show parameter fal
NAME          TYPE        VALUE
------------  ---------- --------
fal_client    string      cgms_s
fal_server    string      cgms_p

1. If the fal parameters of the databases are not matching, then run the following commands to fix the fal parameter mismatch.

   alter system set  fal_client=cgms_p;
   alter system set fal_server=cgms_s;
   

2. Validate the tnsping on both servers after fixing the fal parameters:

   su - oracle
   tnsping cgms_p
   tnsping cgms_s
   

   Ensure that the tnsping works for both fal_client and fal_server. If the tnsping fails, then check the tnsnames.ora file that is located in the $ORACLE_HOME/network/admin folder. Make sure that there is an entry for cgms_p and cgms_s in the tnsnames.ora file. A sample output of the ora file is shown. Any missing entry in the ora file is added manually.

   Sample ora file output

   cgms =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = fndPrimaryDB)(PORT = 1522))
     (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = cgms)
     )
    )
   cgms_p =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCPS)(HOST = fndPrimaryDB)(PORT = 1622))
     (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = cgms)
     )
    )
   cgms_ss =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.104.198.103)(PORT = 1622))
     (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = cgms_s)
     )
    )
   cgms_s =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCPS)(HOST = 10.104.198.103)(PORT = 1622))
     (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = cgms_s)
     )
    )
   

Step 2

Validate the redo log and standby log: Ensure that the redo log outputs are same in both primary and secondary DB as shown in the sample output below:

SQL> select THREAD#, count(*) from v$log group by THREAD#;

THREAD#   COUNT(*)
-------- ----------
    1          3

SQL> select THREAD#, count(*) from v$STANDBY_LOG group by THREAD#;

THREAD#   COUNT(*)
-------- ----------
     1          4

1. Run the following scripts:

   SQL>  select THREAD#, count(*) from v$log group by THREAD#;
   
   THREAD#   COUNT(*)
   -------- ----------
       1          3
   
   
   SQL> select THREAD#, count(*) from v$STANDBY_LOG group by THREAD#;
   
   THREAD#   COUNT(*)
   -------- ----------
       0          2      
       1          2     

2. Run the following script in the standby database.

   dgmgrl sys/cgmsDba123
   disable fast_start failover;
   exit
   
   sqlplus / as sysdba
   recover managed standby database cancel;
   
   
   create a sql file like "a.sql" with below content.
   vi a.sql[copy the below content and paste in the file]
   
   set linesize 200
   set pagesize 3000
   set echo on
   set feedback on
   spool standby_redo.log
   set time on
   ALTER DATABASE CLEAR LOGFILE GROUP 4;
   ALTER DATABASE CLEAR LOGFILE GROUP 5;
   ALTER DATABASE CLEAR LOGFILE GROUP 6;
   ALTER DATABASE CLEAR LOGFILE GROUP 7;
   
   
   ALTER DATABASE DROP LOGFILE GROUP 4;
   ALTER DATABASE DROP LOGFILE GROUP 5;
   ALTER DATABASE DROP LOGFILE GROUP 6;
   ALTER DATABASE DROP LOGFILE GROUP 7;
   
   alter database add standby logfile thread 1 group 4 '/home/oracle/app/oracle/oradata/cgms_s/srl01.log' size 2147483648 reuse;
   alter database add standby logfile thread 1 group 5 '/home/oracle/app/oracle/oradata/cgms_s/srl02.log' size 2147483648 reuse;
   alter database add standby logfile thread 1 group 6 '/home/oracle/app/oracle/oradata/cgms_s/srl03.log' size 2147483648 reuse;
   alter database add standby logfile thread 1 group 7 '/home/oracle/app/oracle/oradata/cgms_s/srl04.log' size 2147483648 reuse;
   
   spool off
   
   
   execute the a.sql file in the sql prompt.
   
   sqlplus / as sysdba
   @a.sql

3. Run the following command in the primary database.

   create a sql file like "a.sql" with below content.
   vi a.sql[copy the below content and paste in the file]
   
   
   set time on
   set feedback on
   set echo on
   spool standby.log
   
   ALTER DATABASE CLEAR LOGFILE GROUP 4;
   ALTER DATABASE CLEAR LOGFILE GROUP 5;
   ALTER DATABASE CLEAR LOGFILE GROUP 6;
   ALTER DATABASE CLEAR LOGFILE GROUP 7;
   
   
   ALTER DATABASE DROP LOGFILE GROUP 4;
   ALTER DATABASE DROP LOGFILE GROUP 5;
   ALTER DATABASE DROP LOGFILE GROUP 6;
   ALTER DATABASE DROP LOGFILE GROUP 7;
   
   alter database add standby logfile thread 1 group 4 '/home/oracle/app/oracle/oradata/cgms/srl01.log' size 2147483648 reuse;
   alter database add standby logfile thread 1 group 5 '/home/oracle/app/oracle/oradata/cgms/srl02.log' size 2147483648 reuse;
   alter database add standby logfile thread 1 group 6 '/home/oracle/app/oracle/oradata/cgms/srl03.log' size 2147483648 reuse;
   alter database add standby logfile thread 1 group 7 '/home/oracle/app/oracle/oradata/cgms/srl04.log' size 2147483648 reuse;
   
   spool off
   
   execute the a.sql file in the sql prompt.
   
   sqlplus / as sysdba
   @a.sql

4. Verify the redo logs after fixing the mismatch: On running the above scripts (steps a, b, and c), redo logs should be the same on both primary and secondary DB. To confirm, run the following commands on both primary and secondary DB.

   select THREAD#, count(*) from v$log group by THREAD#;
   select THREAD#, count(*) from v$STANDBY_LOG group by THREAD#;

   If the output of the primary and secondary DB are the same, then redo log issue is fixed.

Step 3

Validate the 'flashback_on' on both primary and secondary DB:

1. Run the following commands on both primary and secondary DB:

   su - oracle
   sqlplus / as sysdba
   set linesize 200
   set pagesize 3000
   col file_name for a80 
   col member for a80
   select name , open_mode , flashback_on , DATABASE_ROLE from v$database;
   
   SQL> select name , open_mode , flashback_on , DATABASE_ROLE from v$database;
   
   NAME      OPEN_MODE          FLASHBACK_ON       DATABASE_ROLE
   --------- ----------------- ---------------- --------------
   CGMS      READ WRITE           YES                PRIMARY
   

2. Check the tablespace flashback.

   select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts#;
   
   SQL> select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts#;
   
   FILE# FILE_NAME                                           TS# TS_NAME                      FLA
   ---- -------------------------------------------------- ---------- ----------------------- ---
   1 /home/oracle/app/oracle/oradata/CGMS/system01.dbf       0 SYSTEM                         YES
   3 /home/oracle/app/oracle/oradata/CGMS/sysaux01.dbf       1 SYSAUX                         YES
   4 /home/oracle/app/oracle/oradata/CGMS/undotbs01.dbf      2 UNDOTBS1                       YES
   7 /home/oracle/app/oracle/oradata/CGMS/users04.dbf        4 USERS                          YES
   8 /home/oracle/app/oracle/oradata/CGMS/users05.dbf        4 USERS                          YES
   9 /home/oracle/app/oracle/oradata/CGMS/users06.dbf        4 USERS                          YES
   10 /home/oracle/app/oracle/oradata/CGMS/users07.dbf       4 USERS                          YES
   11 /home/oracle/app/oracle/oradata/CGMS/users08.dbf       4 USERS                          YES
   12 /home/oracle/app/oracle/oradata/CGMS/users09.dbf       4 USERS                          YES
   13 /home/oracle/app/oracle/oradata/CGMS/users10.dbf       4 USERS                          YES
   14 /home/oracle/app/oracle/oradata/CGMS/users11.dbf       4 USERS                          YES
   15 /home/oracle/app/oracle/oradata/CGMS/users12.dbf       4 USERS                          YES
   16 /home/oracle/app/oracle/oradata/CGMS/users13.dbf       4 USERS                          YES
   17 /home/oracle/app/oracle/oradata/CGMS/users14.dbf       4 USERS                          YES
   6 /home/oracle/app/oracle/oradata/CGMS/users01.dbf        4 USERS                          YES
   5 /home/oracle/app/oracle/oradata/CGMS/users02.dbf        4 USERS                          YES
   18 /home/oracle/app/oracle/oradata/CGMS/users15.dbf       4 USERS                          YES
   2 /home/oracle/app/oracle/oradata/CGMS/users03.dbf        4 USERS                          YES
   

   Note	Ensure that the flashback_on is 'yes' for the database and also for the tablespace.

   If the flashback_on is not enabled for the tablespace, then follow the steps given below to enable:

   1. Run the following commands in the secondary DB:

      sqlplus / as sysdba
      
      alter tablespace SYSTEM flashback on;
      alter tablespace SYSAUX flashback on;
      alter tablespace UNDOTBS1 flashback on;
      alter tablespace USERS flashback on;

   2. Run the following commands in the primary DB.

      sqlplus / as sysdba
      shutdown immediate
      startup mount
      alter tablespace SYSTEM flashback on;
      alter tablespace SYSAUX flashback on;
      alter tablespace UNDOTBS1 flashback on;
      alter tablespace USERS flashback on;
      alter database open;

3. On executing the above commands, the flashback_on should be enabled on the DB and also on the tablespace. Verify using the following commands:

   select name , open_mode , flashback_on , DATABASE_ROLE from v$database;
   select a.file#, a.name file_name, b.ts#, b.name ts_name, b.flashback_on from v$datafile a, v$tablespace b where a.ts#=b.ts#;
   

Step 4

Start the recovery manager and enable the fast_start failover:

1. To start the recovery manager in the standby DB:

   sqlplus / as sysdba
   recover managed standby database parallel 4 nodelay disconnect from session;

2. To enable fast_start failover:

   dgmgrl sys/cgmsDba123
   show configuration verbose;
   enable fast_start failover;