Role conversion of logical standby Database Based on Oracle11gDataGuard

The logical standby database does not copy the database service. When switchover or failover is performed, the middle layer connecting to the master database service cannot be connected (because the creation of the service is not replicated ), or the version of the connection is incorrect (because the modification of the service property is not copied ). Oracle clusters do not copy services that manage logical standby databases. You must manually synchronize the primary and standby databases,

The logical standby database does not copy the database service. When switchover or failover is performed, the middle layer connecting to the master database service cannot be connected (because the creation of the service is not replicated ), or the version of the connection is incorrect (because the modification of the service property is not copied ). Oracle clusters do not copy services that manage logical standby databases. You must manually synchronize the primary and standby databases,

The logical standby database does not copy the database service. When switchover or failover is performed, the middle layer connecting to the master database service cannot be connected (because the creation of the service is not replicated ), or the version of the connection is incorrect (because the modification of the service property is not copied ).

The Oracle cluster does not copy and manage the services of the logical standby database. You must manually synchronize the master database and the standby database. For details, refer to the management and deployment of the Oracle cluster.

I. Switchover operations for logical standby Databases

When switchover is performed to change the roles of the master database and the logical standby database, switchover is always started in the master database, and then the operations are completed in the logical standby database. The steps must be in order, otherwise, switchover cannot be successfully executed.

1. Make sure that the master database can perform switchover operations

Query the SWITCHOVER_STATUS value of V $ DATABASE to determine whether the master DATABASE can perform the switchover operation:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

As long as the SWITCHOVER_STATUS query result is to standby or sessions active, it indicates that switchover can be performed between the master database and the logical STANDBY database. Otherwise, check whether the Data Guard configuration is correct, for example, whether all the LOG_ARCHIVE_DEST_n parameters are correctly configured.

2. Preparations for switching the current master database

Run the following SQL statement before switching the current master database to the role of the logical standby database:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

This statement indicates that the current master database will soon be switched to the role of the logical standby database and will start receiving redo data from the new master database. This step is performed on the master database to receive the data dictionary recorded in the redo data stream of the Current Logical standby database. For details, see step 3.

If this step is successfully executed, the value of the V $ DATABASE. SWITCHOVER_STATUS column is preparing switchover.

3. Prepare for switch of the target logical standby Database

Use the following SQL statement to create a data dictionary for the target logical standby database:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY; 

This statement also starts the redo transmission service for the logical standby database. The database will start to send its redo Data to the current primary database and other standby databases in the Data Guard configuration. Databases that receive redo data from the logical standby database but do not apply the redo data.

When the data DICTIONARY starts writing data into the redo data stream, the value of V $ DATABASE. SWITCHOVER_STATUS in the logical standby DATABASE is changed to preparing dictionary. Once the operation is successful, SWITCHOVER_STATUS changes to preparing switchover.

4. confirm that the current master database is ready for future master database redo data streams

Before switching the role from the master DATABASE to the slave DATABASE, query the SWITCHOVER_STATUS value of V $ DATABASE to confirm that the data dictionary has been received by the master DATABASE. If the data dictionary has not been received, the switchover cannot be completed. Because the current master database cannot parse the redo data from the new master database.

The value of SWITCHOVER_STATUS indicates the process of switchover. If the value is to logical standby, go TO Step 5.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Note: Terminate the switchover operation on the master database:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;

Terminate the switchover operation of the logical standby database:

SQL> ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;

5. Switch the role of the master database to the logical standby database.

Use the following SQL statement to switch the role from the master database to the logical standby database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

This statement will wait for all the current master database to stop all the transactions and prevent any new users from starting new transactions, and establish a time point for switching and committing.

This statement also prevents you from making any changes to the data in the logical standby database. To ensure quick execution, make sure that the master database is in a static state without updating the activity before executing the switchover statement, for example, do not log on to the master database. You can use the V $ TRANSACTION View to query details about the SQL Execution that may be delayed in ongoing transactions.

Now, the master database is transitioning to the slave database role. You cannot close or restart the database.

6. Make sure that all available redo data has been applied to the target logical standby database that will be converted to the new master database.

After switching the role from the master database to the logical standby database and the slave database in the configuration has received the switch notification, make sure that the switch notification has been processed by the target slave database, this can be verified by querying SWITCHOVER_STATUS in the target slave database v $ DATABASE. Once all available redo data has been applied to the logical slave DATABASE, the SQL application is automatically disabled based on the expected role conversion target.

The SWITCHOVER_STATUS value is updated TO display the progress of switchover. If the value is to primary, go TO step 1.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

7. Switch the role of the logical standby database to the master database

Execute the following SQL statement to convert the role of the logical standby database to the master database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

You do not need to disable or restart the logical standby database in the Data Guard configuration, because other logical standby databases in the configuration will become the standby database of the new master database, however, any physical standby database is still the standby database of the original primary database.

8. Enable SQL application for the new logical standby Database

Execute SQL on the new logical standby database:

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Ii. failover operations for the logical standby Database

This section describes the failover operations of the logical standby database. The logical standby database failover must perform correct operations on the faulty primary database and all logical standby databases. In addition, if the flash back function is not enabled for the master database, you can only use the backup of the current master database to recreate the master database, or use the Master/Slave database switch to solve the problem.

Based on the protection mode configured by the database and the attributes of the redo transmission service, the master database may independently recover all or some modifications.

1. Send the redo logs not sent from the master database to the target slave database.

If the primary database can be mounted (mounted), you may be able to send the redo logs not sent from the primary database to the target standby database. If this operation can be successfully executed, the zero data loss failover is possible, even if the master database is not running in the zero data loss protection mode.

Note: This operation must ensure that the Redo Apply of the target slave database is enabled and that the master database is in the mount state.

The master database executes the following SQL statement:

SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;

The target_db_name here refers to the DB_UNIQUE_NAME of the slave database that receives the redo logs from the master database. This SQL will fl the redo logs not sent from the master database to the target slave database, and wait until the redo data is applied to the slave database.

2. Copy and register all archived redo logs to the target logical standby database to make the target logical standby database a new primary database.

Based on the configured component conditions, you can access the archive redo log of the master database, so:

• Determine whether any archived redo log files are lost in the logical backup database;

• Copy lost log files from the primary database to the logical backup database;

• Register the copied log file.

Redo log files registered and archived in the logical backup database:

SQL> ALTER DATABASE REGISTER LOGICAL LOGFILE -> '/disk1/oracle/dbs/log-%r_%s_%t.arc';Database altered.

3. Enable LOG_ARCHIVE_DEST_STATE_n

If you have not configured a role-based archive Transfer Path, configure the remote log transfer path in the initialization parameter of the remote logical standby database corresponding to the new master database, and manually enable this parameter.

For example, enable LOG_ARCHIVE_DEST_2:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

To ensure that this change is still valid after the new master database is restarted, you can write this parameter into the Database Text initialization parameter file. Generally, when the database is a master database role, archive must be enabled to a remote destination. If it is a standby database role, this parameter should be disable.

4. Activate the new master database

Execute SQL on the target slave database:

SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE FINISH APPLY;

This statement disables the RFS process. Before the target slave database becomes the new master database, the remaining redo logs in the target slave database are applied, and the SQL application is stopped to activate the master database. If the finish apply clause is not available, the current target slave database will not APPLY the redo logs that have not been applied before the target slave database is converted to the master database role.

5. Restore other slave databases after Failover

Ensure that other slave databases continue to provide protection for the new master database.

6. Back up the new master database

After failover is executed, backing up the new master database immediately is a necessary security measure. Because there is no complete database backup, the database cannot be restored after failover.

7. Restore the failed master database

After Failover, the original master database can be converted to the logic standby database of the new master database, or the backup of the new master database can be applied to the logic standby database. Once the original master database is converted to the slave database role, you can use swtichover to re-convert it to the master database role.

Please refer to the official Oracle document for translation. If you have any shortcomings, please criticize and correct them!