Performing DDL on a Logical Standby Database

Performing DDL on a Logical Standby Database

You can temporarily disable Data Guard within a logical standby database. When you need to perform DDL operations (such as the creation of new indexes to improve query performance), you will follow the same basic steps:

1. Stop the application of redo on the logical standby database.
2. Disable Data Guard.
3. Execute the DDL commands.
4. Enable Data Guard.
5. Restart the redo apply process.

For example, to create a new index, start by turning off the Data Guard features:

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> ALTER SESSION DISABLE GUARD;

Execute DDL operations. When you are done, re-enable the Data Guard features:

SQL> ALTER SESSION ENABLE GUARD;
SQL> ALTER DATABASE START LOGICAL APPLY;

The logical standby database will then restart its redo apply process, while the index will be
available to its query users.

Using Real-Time Apply

To enable real-time apply on physical standby database, execute the following command:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

For a logical standby database, the command to use is

SQL> ALTER DATABASE START LOGICAL STANDBY IMMEDIATE;

The disconnect keyword allows the command to run in the background control return to command prompt. If not using “disconnect” keyword, control is not returned to the command prompt until the recovery is cancelled by another session.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Stop the Log Apply Services

For a Physical standby database, the command to stop the Log Apply Services is

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

For a logical standby database, the command to stop the Log Apply Services is

SQL > ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Administering Standby Databases

Following are the steps required performing standard maintenance actions on the databases that are part of the Data Guard configuration, including startup and shutdown operations.

Startup and Shutdown of Physical Standby Databases

When you start up a physical standby database, you should start the redo apply process. First, mount the database then start redo apply process:

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To shut down the standby database, you should first stop the Log Apply Services

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> SHUTDOWN IMMEDIATE;

Opening Physical Standby Databases in Read-Only Mode

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

Startup and Shutdown of Logical Standby Databases

Stop logical standby database and shutdown;

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> SHUTDOWN IMMEDIATE;

Start logical standby database and start redo apply service

SQL> STARTUP;
SQL> ALTER DATABASE START LOGICAL STANDBY IMMEDIATE;