Manually Failover of Oracle dataguard 19c.

In this article we will failover Pirmary databae Manually and will try to start Standby database as new Primary

ON the basis of Cases we can assume below steps can be performed to Failover to Standby database.

Case 1 => NO DATA LOSS.

If the primary database can be mounted, then flush any unsent archived and current redo from the primary database to the standby database.
If this operation is successful, a zero data loss failover is possible even if the primary database is not in a zero data loss data protection mode.
First, ensure that Redo Apply is active at the target standby database.
Then mount, but do not open the primary database.

Issue the following SQL statement at the primary database:

SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;

For target_db_name, specify the DB_UNIQUE_NAME of the standby database that is to receive the redo flushed from the primary database.

This statement flushes any unsent redo from the primary database to the standby database, and waits for that redo to be applied to the standby database.

Case 2 => DATA LOSS.

Query the V$ARCHIVED_LOG view on the target standby database to obtain the highest log sequence number for each redo thread.

SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;

    THREAD       LAST
---------- ----------
         1        100
Query the V$ARCHIVE_GAP view on the target standby database to determine if there are any redo gaps on the target standby database.

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

THREAD#    LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1            90             92

If possible, copy the most recently archived redo log file for each primary database redo thread to the standby database if it does not exist there, and register it.
This must be done for each redo thread.

For example:

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
If, after performing Case 2Stesp ,you are not able to resolve all gaps in the archived redo log files ,then you can expect some data loss during the failover.

Converting to Primary Mode -

Post Case 1 and Case 2 if all well let we convert to Primary mode as below.Execute on Standby Site.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Issue the following SQL statement on the target standby database.

SQL> ALTER DATABASE FAILOVER TO target_db_name;

target_db_name is STANDBY DATABASE DB_UNIQUE_NAME

Open the new primary database:

SQL> ALTER DATABASE OPEN;

If Converting to Primary Mode  Fails.

If above mentioned steps which convert to Primary fails , then we need to do some Forcefull operations as below

If the FORCE option is unsuccessful, go to Step 9.
Perform a data loss failover.

If an error condition cannot be resolved, a failover can still be performed (with some data loss) by issuing the following SQL statement on the target standby database:

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

In the following example, the failover operation fails with an ORA-16472 error. That error means the database is configured in MaxAvailability
or MaxProtection mode but data loss is detected during failover.

SQL> ALTER DATABASE FAILOVER TO CHICAGO;
ERROR at line 1:
ORA-16472: failover failed due to data loss

You can complete the data loss failover by issuing the following SQL statement:

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database altered.

Demonstration

Let we practice for Failover Case as below.

Primary Side

SQL> @name

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE

Check if Real-Time apply Running on Standby side .Execute below on Primary Side.

SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=2;

DEST_ID DEST_NAME         STATUS    TYPE        SRL RECOVERY_MODE
------- -------------------- --------- ---------------- --- ----------------------------------
      2 LOG_ARCHIVE_DEST_2   VALID     PHYSICAL     YES MANAGED REAL TIME APPLY WITH QUERY

[oracle@racsetn1 ~]$sqlplus "/as sysdba"

SQL> shut abort;
ORACLE instance shut down.
SQL> exit

[oracle@racsetn1 ~]$ ssh racsetn2
[oracle@racsetn2 ~]$sqlplus "/as sysdba"

SQL> shut abort
ORACLE instance shut down.
SQL> exit

Let we start Current Primary in MOUNT Mode

[oracle@racsetn1 ~]$ srvctl start database -d DBTAR -o "MOUNT"

[oracle@racsetn1 ~]$ srvctl start database -d DBTAR -o "MOUNT"
[oracle@racsetn1 ~]$ !sq
sqlplus "/as sysdba"

SQL>  ALTER SYSTEM D  REDO TO DBSET;
 ALTER SYSTEM FLUSH REDO TO DBSET
*
ERROR at line 1:
ORA-38777: database must not be started in any other instance
SQL> exit

[oracle@racsetn1 ~]$ srvctl stop instance -db DBTAR -instance DBTAR2
[oracle@racsetn1 ~]$ sqlplus "/as sysdba"

SQL> @name

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET      MOUNTED           PRIMARY        MAXIMUM PERFORMANCE

SQL> ALTER SYSTEM FLUSH REDO TO DBSET;

System altered.

Check Alert Log for Primary Side.

2022-07-10T18:01:09.328853+05:30
ALTER SYSTEM FLUSH REDO TO 'DBSET' CONFIRM APPLY
2022-07-10T18:01:09.328951+05:30
ALTER SYSTEM FLUSH REDO TO DBSET CONFIRM APPLY [Process Id: 7179] (DBTAR1)
2022-07-10T18:01:09.360651+05:30
.... (PID:7179): Flush redo: No wait for non-current ORLs to be archived
2022-07-10T18:01:09.360722+05:30
.... (PID:7179): Waiting for all FAL entries to be archived
2022-07-10T18:01:09.360804+05:30
.... (PID:7179): All FAL entries have been archived
2022-07-10T18:01:09.360897+05:30
.... (PID:7179): Waiting for LAD:2 to become synchronized
2022-07-10T18:01:13.779996+05:30
.... (PID:7179): Active, synchronized flush redo target has been identified
.... (PID:7179): Managed Real Time Apply recovery running at physical standby 'LOG_ARCHIVE_DEST_2'
Flush End-Of-Redo Log thread 2 sequence 149 has been fixed
Flush Redo: Primary highest seen SCN set to 0x000000000044e1ec
.... (PID:7179): Noswitch archival of T-2.S-149
NET  (PID:7179): End-Of-Redo Branch archival of T-2.S-149
NET  (PID:7179): LGWR is scheduled to archive to LAD:2 after log switch
2022-07-10T18:01:16.035798+05:30
NET  (PID:7179): SRL selected for T-2.S-149 for LAD:2
2022-07-10T18:01:16.585371+05:30
NET  (PID:7179): Flush End-Of-Redo Log T-2.S-149
2022-07-10T18:01:19.383518+05:30
NET  (PID:7179): Archived Log entry 526 added for T-2.S-149 ID 0x2a4b540b LAD:1
2022-07-10T18:01:21.640565+05:30
NET  (PID:7179): Archiving is disabled due to current logfile archival
Primary will wait for DBSET standby to have applied all redo
NET  (PID:7179): Final check for a target standby that has recovered all redo, check will be made a few times
2022-07-10T18:01:24.367452+05:30
NET  (PID:7179): LOG_ARCHIVE_DEST_2 is a potential flush redo target
NET  (PID:7179): LOG_ARCHIVE_DEST_2 has also applied all redo from primary
NET  (PID:7179): Active, synchronized target has been identified that has applied all the redo from the primary
Flush Redo: Primary redo moved to standby

Standby Side

SQL> @name

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET      READ ONLY           PHYSICAL STANDBY MAXIMUM PERFORMANCE
DBSET      READ ONLY           PHYSICAL STANDBY MAXIMUM PERFORMANCE

SQL>
SQL>  select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID DEST_NAME        STATUS      TYPE           SRL RECOVERY_MODE
---------- -------------------- --------- ---------------- --- ----------------------------------
     1 LOG_ARCHIVE_DEST_1    VALID      LOCAL        NO  MANAGED REAL TIME APPLY

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE FAILOVER TO DBSET;

Database altered.

SQL> show parameter db_uniq

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                 string     DBSET
SQL> @name

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET      MOUNTED           PRIMARY        MAXIMUM PERFORMANCE
DBSET      MOUNTED           PRIMARY        MAXIMUM PERFORMANCE

[oracle@racnoden1 ~]$ srvctl stop database -db DBSET
[oracle@racnoden1 ~]$ srvctl start database -db DBSET -o "OPEN"
[oracle@racnoden1 ~]$ sqlplus "/as sysdba"

SQL> SQL> SQL>
SQL>
SQL> @name

NAME      OPEN_MODE           DATABASE_ROLE    PROTECTION_MODE
--------- -------------------- ---------------- --------------------
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE
DBSET      READ WRITE           PRIMARY        MAXIMUM PERFORMANCE

SQL>

Standby Alert Logs

ALTER DATABASE FAILOVER TO DBSET
ALTER SYSTEM SET remote_listener=' racnode-scan:1521' SCOPE=MEMORY SID='DBSET1';
2022-07-10T18:04:03.040336+05:30
ALTER SYSTEM SET listener_networks='' SCOPE=MEMORY SID='DBSET1';
2022-07-10T18:04:03.303444+05:30
PR00 (PID:28165): Media Recovery Waiting for T-2.S-150
2022-07-10T18:04:04.665122+05:30
Already in limbo. Terminal Recovery finished with No-Data-Loss.
RECOVER FINISH applied through switchover EOR logs and stopped.
2022-07-10T18:04:04.665372+05:30
Media Recovery Complete: End-Of-REDO (DBSET1)
ALTER DATABASE SWITCHOVER TO PRIMARY (DBSET1)

NET  (PID:25148): Database role cleared from PHYSICAL STANDBY [kcvs.c:1030]
Enabling Dynamic Remastering: STNDB->NORM switch
Switchover: Complete - Database mounted as primary
2022-07-10T18:04:23.913265+05:30
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2022-07-10 18:04:23.913127
TMI: dbsdrv failover to target END 2022-07-10 18:04:23.913280
Failover completed with No-Data-Loss.
Completed: ALTER DATABASE FAILOVER TO DBSET