Oracle Logical Standby Apply Stop and Start Commands

calendar Jan 2, 2026 / · 3 min read · Oracle Database Data Guard Logical Standby SQL Apply Database Administration Backup Recovery High Availability DBA Commands  ·
Share on: linkedin copy

Oracle Logical Standby Apply Stop and Start Commands

Purpose

The logical standby apply commands control the SQL Apply process in Oracle Data Guard logical standby databases. These commands allow database administrators to stop and start the application of redo log data from the primary database to the logical standby database, which is essential for maintenance tasks, troubleshooting, and configuration changes.

Code

1-- Stop logical standby apply
2alter database stop logical standby apply;
3
4-- Start logical standby apply
5alter database start logical standby apply;

Breakdown of Commands

Stop Command

The ALTER DATABASE STOP LOGICAL STANDBY APPLY command halts the SQL Apply process on the logical standby database. When you issue this statement, SQL Apply waits until it has committed all complete transactions that were in the process of being applied. This means the command may not stop the SQL Apply processes immediately, ensuring data consistency by completing in-progress transactions.

Start Command

The ALTER DATABASE START LOGICAL STANDBY APPLY command initiates the SQL Apply process. You can optionally use the IMMEDIATE keyword as ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE to start the process without delay. This command begins the process of transforming redo data received from the primary database into SQL statements and executing them on the logical standby database.

Key Points

  • Transaction Safety: The stop command ensures all in-progress transactions are committed before halting, preventing data corruption
  • Required for Maintenance: You must stop SQL Apply before making certain configuration changes, such as registering skip rules with DBMS_LOGSTDBY.SKIP
  • Background Processes: SQL Apply uses multiple background processes including READER, PREPARER, BUILDER, ANALYZER, COORDINATOR, and APPLIER processes
  • Immediate Option: The IMMEDIATE keyword can be added to the start command for faster initialization
  • Root Container Only: In multitenant databases (CDB), these commands function only in the root container and affect the entire database

When to Use These Commands

Database administrators need to stop and start SQL Apply in several scenarios:

  • Modifying skip rules to prevent changes to specific schema objects
  • Performing maintenance operations on the logical standby database
  • Troubleshooting replication issues or errors
  • Implementing custom DDL handlers for different file system layouts
  • Shutting down the logical standby database in the proper sequence

Monitoring SQL Apply Status

After starting SQL Apply, you can monitor its progress using several views:

  • V$LOGSTDBY_STATE: Shows the current state (INITIALIZING, LOADING DICTIONARY, APPLYING, WAITING ON GAP, or IDLE)
  • V$LOGSTDBY_PROCESS: Displays information about SQL Apply processes and their current activity
  • V$LOGSTDBY_PROGRESS: Provides detailed information about applied SCN, mining SCN, and restart SCN values
  • DBA_LOGSTDBY_EVENTS: Records interesting events that occurred during SQL Apply operations

Best Practices

Always stop SQL Apply on the logical standby database before performing administrative tasks that could interfere with the replication process. When shutting down both primary and standby databases, shutdown the primary database first, then the standby database. For starting up, reverse the order by starting the standby database first.

References

Posts in this series