Oracle 11g Data Guard's logical standby role transformation

The logical repository does not replicate the database service, and the middle tier connecting to the main library service will not be able to connect (because the service's creation is not replicated) or the incorrect version (because the modification of the service properties is not replicated) when switchover or failover is performed.

Oracle clusters do not replicate the services that manage the logical repository, and must manually synchronize the main and standby libraries, with detailed information about Oracle Cluster management and deployment.

I. switchover operation of the logical standby library

When performing a switchover operation to change the roles of the main and logical repositories, always start switchover in the main library and complete the operation in the logical repository, where the steps must be in order or the switchover will not be executed successfully.

1. Ensure that the main library can be switchover operation

Determine whether the main library can perform switchover operations by querying the Switchover_status value of V$database:

Sql> SELECT switchover_status from V$database;

As long as the Switchover_status query result is to standby or sessions ACTIVE, the switchover between the primary and the logical repositories is indicated. Otherwise, see if the data Guard is configured correctly, such as if all the Log_archive_dest_n parameters are configured correctly.

2. Preparing to switch to the current main library

Before the current main library switches to the logical standby role, execute the following sql:

Sql> ALTER DATABASE PREPARE to switchover to LOGICAL STANDBY;

This statement indicates that the current main library will soon be switched to the logical standby role and start receiving redo data from the new Main library. This step is performed in the main library in order to receive a data dictionary that is recorded in the current logical standby redo data stream, as described in step 3.

If the step executes successfully, then v$database. The value of the Switchover_status column is preparing switchover.

3. Preparation of the target logic reserve switch

Create a data dictionary of the destination logical repository using the following SQL:

The statement also initiates the Redo transport service for the logical standby, which begins to send its own redo data to the current main library and to other repositories in the data guard configuration. These databases that receive the logical standby redo data but do not apply these redo data.

When the data dictionary begins to write to the redo data stream, the v$database of the logical standby library. The Switchover_status value becomes preparing DICTIONARY, and once the operation is executed successfully, Switchover_status becomes preparing switchover.

4. Confirm that the current main library is ready for the future main library redo data flow

Before completing the main library to the standby role switch, by querying the V$database switchover_status value to confirm that the data dictionary has been received by the main library, if the data dictionary is not received, then the switchover operation will not be completed. Because the current main library is not able to parse the redo data from the new Main library.

The value of Switchover_status indicates the process of switchover, which is entered in step 5 when it is standby to LOGICAL.

Sql> SELECT switchover_status from V$database;

Remarks: Terminates the main library switchover operation:

Sql> ALTER DATABASE PREPARE to switchover CANCEL;

To terminate the logical standby switchover operation:

Sql> ALTER DATABASE PREPARE to switchover CANCEL;

5. Switch the main library to the logical standby role

Switch the main library to the logical standby role by using the following SQL:

Sql> ALTER DATABASE COMMIT to switchover to LOGICAL STANDBY;

The statement waits for all current master libraries to stop all transactions and prevents any new users from starting new transactions, and establishes a point in time to switch commits.

The statement will also prevent the user from making any modifications to the data in the logical repository, in order to ensure that the main library is not in the static state of the update activity before executing the switchover statement, for example, all users are temporarily not logged in to the main library. You can query the details of a transaction that is currently in progress that might delay execution of the SQL through the V$transaction view.

At this point, the main library is already transitioning to the standby role, and you cannot shut down or restart the database.

6. Ensure that all available redo data has been applied to the target logic repository that will be converted to the new main library

When the main library to the logical reserve role of the switch, and the configuration of the standby library has received the switch notification, you should confirm that the switch notification has been prepared by the target repository, which can be checked by querying the target repository v$database in the switchover_status to verify, Once all available redo data has been applied to the logical repository, the SQL app automatically shuts down according to the intended role conversion target.

The update of the Switchover_status value shows the progress of the switchover and the 7th step is to PRIMARY.

Sql> SELECT switchover_status from V$database;

7. Switch the logical repository to the main library role

Execute the following SQL to complete the conversion of the logical repository to the main library role:

Sql> ALTER DATABASE COMMIT to switchover to PRIMARY;

There is no need to shut down or restart the logical repository in the data guard configuration because the other logical repositories in the configuration will become the repository for the new master library, but any physical standby is still a repository for the primary repository.

8. New logic repository to open SQL application

Execute SQL on new logical standby:

sql> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

two. Failover operation of the logical standby library

This section describes the failover operation of the logical repository, and the failover of the logical repository must perform the correct operations on the failed main library and all the logical repositories. In addition, if the main library does not have a flashback feature, the main library can only be rebuilt through a backup of the current main library, or the problem will be resolved through the master standby switch.

Depending on the protected mode of the database configuration and the properties of the Redo transport service, the Main library may voluntarily recover all or part of the modification.

1. Send a redo log not sent from the main library to the target repository

If the main library can be mounted (mounted), it is possible to send redo logs that are not sent by the main library to the target repository. If the operation succeeds, then the failover of 0 data loss is possible, even if the main library is not running in the protected mode of 0 data loss.

Note that the operation is to ensure that the redo apply of the target repository is on, and that the main library is the Mount state.

The main library executes the following SQL:

sql> ALTER SYSTEM FLUSH REDO to Target_db_name;

The target_db_name here refers to the db_unique_name that receives the redo log from the main library, which brushes the redo logs from the main library to the target repository and waits for the redo data to be applied to the repository.

2. Copy and register all archived redo logs to the destination logical repository so that the target logical repository becomes the new main library.

Depending on the configured component conditions, you can access the archive redo log for the main library, then:

• Determine if the logical backup database has any archive redo log files missing;

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

• Registers the copied log file.

The logical backup database registers the archived redo log files:

sql> ALTER DATABASE REGISTER LOGICAL LOGFILE, '/disk1/oracle/dbs/log-%r_%s_%t.arc ';D atabase altered.

3. Enable Log_archive_dest_state_n

If you have not previously configured a role-based archive transfer path, configure the remote log transfer path in the initialization parameters of the remote logical repository corresponding to the new primary library, and manually enable the parameter.

For example, enable Log_archive_dest_2:

sql> ALTER SYSTEM SET log_archive_dest_state_2=enable scope=both;

To ensure that the new main library restarts, the changes are still valid, you can write this parameter into the database text initialization parameter file, in general, when the database is the main library role, you must open the archive to a remote destination, if it is a standby role, this parameter should be disable.

4. Activating the new Main library

Execute SQL on the target repository:

sql> ALTER database ACTIVATE LOGICAL STANDBY database FINISH APPLY;

The statement closes the RFS process, applies the remaining redo logs from the target repository before the target standby becomes the new primary, and then stops the SQL app to activate the main library. Without the finish apply clause, the current target repository does not apply redo logs that have not been applied until the target repository is transferred to the primary library role.

5. Recover other repositories after failover

Ensure that other existing repositories continue to provide protection for the new primary library.

6. Back up the new main library

After failover is executed, it is a necessary security measure to back up the new primary library and perform the backup immediately, because without a full database backup, the database cannot be recovered after failover.

7. Restore failed Main Library

After failover, the master library can be converted to a logical standby of the new primary library, or a backup of the new primary library can be applied to the logical repository. Once the primary repository is converted to a standby role, it can be re-converted to the main library role through Swtichover.

This article is about Oracle's Official document translation, and the shortcomings are welcome criticism!

Oracle 11g Data Guard's logical standby role transformation