Oracle 19c: Create a Logical Standby Database

Creating a logical standby database in Oracle 19 is crucial for ensuring high availability, data protection, and load balancing in your database environment. This guide will help you understand the steps involved in creating and maintaining a logical standby database. Note that you must have an existing physical standby database to convert it into a logical stdby database.

Understanding Logical Standby Database

A logical stdby database allows read-write access while being synchronized with the primary database through SQL Apply. This method translates redo data into SQL transactions and applies them to the standby database. It’s beneficial for real-time reporting and data analysis.

Benefits of Using Logical Standby

• • High Availability: Ensures continuous database availability in case of primary database failure.

• • Load Balancing: Offloads queries and reporting tasks from the primary database, enhancing performance.

• • Data Protection: Provides a robust disaster recovery solution by maintaining a synchronized copy of the primary database.

• • Performance Optimization: Additional indexes and materialized views can be created to improve query performance.

• • Maintenance: Upgrade Oracle Database software and apply patch sets without downtime.

📢 You might also like: Oracle 19c Manage SQL Apply Filtering (Category: DataGuard)

When to Create a Logical Standby

• • High Transaction Workloads: Offloading reporting tasks to a logical standby can significantly improve performance.

• • Critical Data Protection: Provides an additional layer of data protection for businesses relying heavily on data integrity and availability.

• • Regulatory Compliance: For organizations required to maintain real-time data replicas due to regulatory compliance, a logical standby database ensures that all transactions are captured and replicated accurately.

Database Creation Process

Primary Database Preparation

Ensure that the primary database runs in ARCHIVELOG mode and configure all necessary parameters.

Unsupported Objects Check

• • Query internal schemas that will be skipped:

    SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT='INTERNAL SCHEMA' ORDER BY OWNER;

• • Query unsupported tables due to data types or partitioning:

    SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;

Unsupported Data Types

Identify and exclude unsupported data types like BFILE, ROWID, and spatial types from the logical standby.

Stop Redo Apply on Physical Standby

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Prepare Primary DB

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u04/arch1/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary_db' SCOPE=BOTH;
EXECUTE DBMS_LOGSTDBY.BUILD;

Standby Database Setup: Transition to Logical Standby

ALTER DATABASE RECOVER TO LOGICAL STANDBY &db_name;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
SELECT NAME, DB_UNIQUE_NAME, DATABASE_ROLE FROM V$DATABASE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u04/arch1/standby VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby_db' SCOPE=BOTH;

Open Logical stdby

ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Standby Database Setup: Verify Logical stdby Performance

• • Check archived redo logs:

    SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

• • Force redo data transmission:

    ALTER SYSTEM SWITCH LOGFILE; -- or ALTER SYSTEM ARCHIVE LOG CURRENT;

• • Verify redo data application:

    SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = 'coordinator state';
    SELECT SID, SERIAL#, SPID, TYPE, HIGH_SCN FROM V$LOGSTDBY_PROCESS;
    SELECT APPLIED_SCN, LATEST_SCN FROM V$LOGSTDBY_PROGRESS;

Standby Database Setup – Secure Logical stdby

• • Set database guard:

    ALTER DATABASE GUARD ALL;

• • Query guard status:

    SELECT NAME, DATABASE_ROLE, GUARD_STATUS FROM V$DATABASE;

Conclusion

Creating a logical stdby database in Oracle 19 involves several crucial steps to properly set up and synchronize it with the primary database. By following this detailed process, you can significantly enhance your database management strategy, ensuring high availability, robust data protection, and optimized performance. This setup not only improves the reliability of your database environment but also allows for efficient maintenance and upgrades, ensuring your operations run smoothly and continuously. Evaluate your database workload, data protection needs, and compliance requirements to make an informed decision about implementing a logical standby database.

See more on Oracle’s website!

Be Oracle Dataguard Certified Professional, this world is full of opportunities for qualified DBAs!