Switchover Activity:

Primary Database: pearl
Standby Database: muthu

Step 1. Check the Primary and Standby Status:

On Primary:

SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d where v.resetlogs_change# = d.resetlogs_change# group by thread# order by 1;

THREAD# Last Primary Seq# Generated
---------- ---------------------------
1 27

On Standby:

SQL> select thread#, max(sequence#) "Last Primary Seq# Generated" from v$archived_log v, v$database d where v.resetlogs_change# = d.resetlogs_change# group by thread# order by 1;

THREAD# Last Primary Seq# Generated
---------- ---------------------------
1 27

Step 2: Connect to the broker

Connect to the broker using the dgmgrl utility on the primary database. For example, you can run the command "dgmgrl /" to connect to the default broker configuration.

[oracle@primary ~]$ dgmgrl sys/pwd
Welcome to DGMGRL, type "help" for information.
Connected to "pearl"
Connected as SYSDBA.

Step 3: Verify the configuration

Verify that the broker configuration is correct by running the "show configuration" command. This will display the current configuration settings for the broker.

DGMGRL> show configuration

Configuration - pearl

Protection Mode: MaxPerformance
Members:
pearl - Primary database
muthu - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 51 seconds ago)
In this configuration, we have Primary Database "PEARL" and the standby Database is "MUTHU".

Step 4: Prepare for the switchover

Prepare for the switchover by running the "validate database" command to ensure that both the primary and standby databases are ready for switchover.

Validate Primary Database

DGMGRL> validate database pearl;

Database Role:    Primary database

Ready for Switchover: Yes

Flashback Database Status:
    pearl: Off

Managed by Clusterware:
    pearl: NO
    Validating static connect identifier for the primary database pearl...
    The static connect identifier allows for a connection to database "pearl".
Validate Standby Database

DGMGRL> validate database muthu;

Database Role:     Physical standby database
Primary Database: pearl

Ready for Switchover: Yes
Ready for Failover:    Yes (Primary Running)

Flashback Database Status:
    pearl: Off
    muthu : Off

Managed by Clusterware:
    pearl: NO
    muthu : NO
    Validating static connect identifier for the primary database pearl...
    The static connect identifier allows for a connection to database "pearl".

Current Log File Groups Configuration:
    Thread # Online Redo Log Groups Standby Redo Log Groups Status
              (pearl)               (muthu)
    1         3                       2                       Insufficient SRLs

Future Log File Groups Configuration:
    Thread # Online Redo Log Groups Standby Redo Log Groups Status
              (muthu)                (pearl)
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on pearl
Step 5: Initiate the switchover
Initiate the switchover by running the "switchover to " command in dgmgrl. This will start the switchover process and automatically failover the primary database to the standby database.

Step 5. Run the Switchover Command:

DGMGRL> switchover to muthu;
Performing switchover NOW, please wait...
Operation requires a connection to database "muthu"
Connecting ...
Connected to "muthu"
Connected as SYSDBA.
New primary database "muthu" is opening...
Operation requires start up of instance "pearl" on database "pearl"
Starting instance "pearl"...
Connected to an idle instance.
ORACLE instance started.
Connected to "pearl"
Database mounted.
Database opened.
Connected to "pearl"
Switchover succeeded, new primary is "muthu"

Step 6: Verify the switchover

After the switchover, verify that the new primary database is running by running the "show database" command in dgmgrl.

Current Primary muthu: connect with DGMGRL in current primary server

DGMGRL> show database pearl

Database - pearl

Role:               PHYSICAL STANDBY
Intended State:     APPLY-ON
Transport Lag:      (unknown)
Apply Lag:          (unknown)
Average Apply Rate: (unknown)
Real Time Query:    ON
Instance(s):
    pearl

Database Warning(s):
    ORA-16854: apply lag could not be determined
    ORA-16856: transport lag could not be determined
    ORA-16858: last communication time from redo source could not be determined

Database Status:
WARNING

DGMGRL> show database muthu;

Database - muthu

Role:               PRIMARY
Intended State:     TRANSPORT-ON
Instance(s):
    muthu
      Error: ORA-16736: unable to find the destination entry of member "pearl" in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property RedoRoutes found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property LogXptMode found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property DelayMins found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property Binding found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property MaxFailure found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property ReopenSecs found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST
      Warning: ORA-16728: consistency check for property RedoCompression found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a member in V$ARCHIVE_DEST

Database Status:
ERROR

Database Warnings

Sometimes you will face the below error, then you have to check the log_archive_dest_1 and log_archive_dest_2 parameter on the standby side and configure it properly.

Step 7: Reconfigure the environment

If necessary, reconfigure the environment to point to the new primary database.
For example, update connection strings, DNS entries, or load balancer settings to point to the new primary database.

reconfigure the dataguard parameters in new primary
like
standby logfile delete and recreate
other parameters also

Step 8: Start Recovery

After the switchover start the MRP process using the broker or manually.

Enable/Disable the MRP with Broker:

DGMGRL> EDIT DATABASE pearl SET STATE='APPLY-ON';
Succeeded.
Check MRP status using SQL command, if no row is selected that means MRP is disabled.

SQL> select inst_id, process, status from gv$managed_standby where process='MRP0';

INST_ID PROCESS STATUS
---------- --------- ------------
1 MRP0 APPLYING_LOG
Disable with Broker
DGMGRL> EDIT DATABASE pearl SET STATE='APPLY-OFF';

Enable/Disable the MRP manually:

SQL> alter database recover managed standby database disconect from session;

SQL> alter database recover managed standby database cancel;

Finally we got:

Current Primary(muthu):

DGMGRL> show database muthu

Database - muthu

Role:               PRIMARY
Intended State:     TRANSPORT-ON
Instance(s):
    muthu

Database Status:
SUCCESS

Current standby pearl
DGMGRL> show database pearl

Database - pearl

Role:               PHYSICAL STANDBY
Intended State:     APPLY-ON
Transport Lag:      0 seconds (computed 0 seconds ago)
Apply Lag:          0 seconds (computed 0 seconds ago)
Average Apply Rate: 0 Byte/s
Real Time Query:    ON
Instance(s):
    pearl

Database Status:
SUCCESS

Revert Back:

On current primary(muthu):

dgmgrl sys/sys@muthu

DGMGRL> show configuration;
DGMGRL> switchover to pearl;

Search This Blog

DBA Master