Database Switchover Steps

1. Pre-operation Steps

• Check archive logs on both the primary and standby servers:
  • Primary:

SELECT thread#, MAX(sequence#) "Last Primary Seq Generated"
FROM gv$archived_log val, gv$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
GROUP BY thread# ORDER BY 1;

* **Standby:**

SELECT thread#, MAX(sequence#) "Last Standby Seq Received"
FROM gv$archived_log val, gv$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
GROUP BY thread# ORDER BY 1;

• Verify the last applied log sequence:

SELECT thread#, MAX(sequence#) "Last Standby Seq Applied"
FROM gv$archived_log val, gv$database vdb
WHERE val.resetlogs_change# = vdb.resetlogs_change#
AND val.applied IN ('YES', 'IN-MEMORY')
GROUP BY thread# ORDER BY 1;

• Verify initialization parameters:

Ensure the following parameters are correctly configured:

SELECT name, value
FROM v$parameter
WHERE UPPER(name) IN ('DB_NAME', 'DB_UNIQUE_NAME', 'FAL_CLIENT',
                      'FAL_SERVER', 'LOG_ARCHIVE_CONFIG',
                      'LOG_ARCHIVE_DEST_2', 'LOG_ARCHIVE_DEST_STATE_2',
                      'LOG_ARCHIVE_DEST_3', 'LOG_ARCHIVE_DEST_STATE_3',
                      'REMOTE_LOGIN_PASSWORDFILE')
ORDER BY name;

* `DB_NAME`: The name of the database instance.
* `DB_UNIQUE_NAME`: A unique identifier for the database, ensuring differentiation between primary and standby databases.
* `FAL_CLIENT`: Specifies the database requesting archive log gaps to be resolved.
* `FAL_SERVER`: Identifies the server providing missing archive logs.
* `LOG_ARCHIVE_CONFIG`: Defines the Data Guard configuration, including primary and standby roles.
* `LOG_ARCHIVE_DEST_2`: Specifies the archive destination for the standby database.
* `LOG_ARCHIVE_DEST_STATE_2`: Controls the state (enabled/disabled) of the second archive destination.
* `LOG_ARCHIVE_DEST_3`: Additional archive destination for multi-standby setups.
* `LOG_ARCHIVE_DEST_STATE_3`: Controls the state of the third archive destination.
* `REMOTE_LOGIN_PASSWORDFILE`: Ensures password file authentication is used for remote connections.

Note: Ensure Data Guard is functioning. If logs are not being written, restart standby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

2. Switchover Steps

* Verify switchover readiness on the primary database:

ALTER DATABASE SWITCHOVER TO <standby db_name> VERIFY;

* Perform the switchover:

ALTER DATABASE SWITCHOVER TO <standby db_name>;

* Open the new primary database:

ALTER DATABASE OPEN;

3. Post-switchover Steps

* On the primary, verify logs and create an archive log:

ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT dest_id, error, status FROM v$archive_dest WHERE dest_id=2;

* On the standby, verify logs and processes:

SELECT MAX(sequence#), thread# FROM v$archived_log GROUP BY thread#;

Tip: Test data flow by inserting data into a test table to confirm proper replication.