Creating a Logical Standby Database

 

Creating a Logical Standby Database

This chapter steps you through the process of creating a logical standby database. It includes the following main topics:

·         Prerequisite Conditions for Creating a Logical Standby Database

·         Step-by-Step Instructions for Creating a Logical Standby Database

1.1 Prerequisite Conditions for Creating a Logical Standby Database

Table 1-1 Preparing the Primary Database for Logical Standby Database Creation

Reference	Task
Section 1.1.1	Determine Support for Data Types and Storage Attributes for Tables
Section 1.1.2	Ensure Table Rows in the Primary Database Can Be Uniquely Identified

 

1.1.1 Determine Support for Data Types and Storage Attributes for Tables

If the primary database contains unsupported tables, log apply services automatically exclude the tables when applying redo logs to the logical standby database.

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;

OWNER                          TABLE_NAME

------------------------------ ------------------------------

OE                             CUSTOMERS

1.1.2 Ensure Table Rows in the Primary Database Can Be Uniquely Identified

If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table else create the primary key.

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE;

OWNER                          TABLE_NAME

------------------------------ ------------------------------

SCOTT                          SALGRADE

SQL> ALTER TABLE SALGRADE ADD PRIMARY KEY (GRADE) RELY DISABLE;

 

1.2 Step-by-Step Instructions for Creating a Logical Standby Database

Table 1-2 Creating a Logical Standby Database

Reference	Task	Database
Section 1.2.1	Create a Physical Standby Database	Primary
Section 1.2.2	Stop Redo Apply on the Physical Standby Database	Standby
Section 1.2.3	Prepare the Primary Database to Support a Logical Standby Database	Primary
Section 1.2.4	Transition to a Logical Standby Database	Standby
Section 1.2.5	Open the Logical Standby Database	Standby
Section 1.2.6	Verify the Logical Standby Database Is Performing Properly	Standby

 

1.2.1 Create a Physical Standby Database

Follow the information below and create a physical standby database.

Primary Server - HQLINUX26 

Userid - oracle

Setfile- setlgstdby

DB_NAME- lgstdby

DB_UNIQUE_NAME - EGHQ

Standby Server - ITLINUX16 

Userid - oracle

Setfile- setlgstdby

DB_NAME- lgstdby

DB_UNIQUE_NAME - MITEC

1.2.2 Stop Redo Apply on the Physical Standby Database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

1.2.3 Prepare the Primary Database to Support a Logical Standby Database

1.2.3.1 Prepare the Primary Database for Role Transitions

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3=

 'LOCATION=/ultradb01/ORACLE/lgstdby/stdby_arch

  VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) 

  DB_UNIQUE_NAME=EGHQ';

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/ultradb01/ORACLE/lgstdby/arch VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EGHQ';

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=

SERVICE='MITEC LGWR SYNC AFFIRM

VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) 

DB_UNIQUE_NAME=MITEC'

1.2.3.2 Build a Dictionary in the Redo Data

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

1.2.4 Transition to a Logical Standby Database

1.2.4.1 Convert to a Logical Standby Database

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY MISDB;

1.2.4.2 Create a New Password File

orapwd file=orapwmisdb password=oracle entries=20

1.2.4.3 Adjust Initialization Parameters for the Logical Standby Database

SQL> SHUTDOWN;

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=

  'LOCATION=/u01/ORACLE/lgstdby/arch

   VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)

   DB_UNIQUE_NAME=MITEC'

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=

  'SERVICE=EGHQ LGWR ASYNC

   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

   DB_UNIQUE_NAME=EGHQ'

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3=

  'LOCATION=/u01/ORACLE/lgstdby/stdby_arch

   VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)

   DB_UNIQUE_NAME=MITEC'

1.2.5 Open the Logical Standby Database

SQL> ALTER DATABASE OPEN RESETLOGS;

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

1.2.6 Verify the Logical Standby Database Is Performing Properly

See the following sections for help verifying that the logical standby database is performing properly:

·         Test the Application

SQL> CONN SCOTT/TIGER@LGSTDBY

CONNECTED.

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)

----------

     50432

SQL> CONN SCOTT/TIGER@MISDB

CONNECTED.

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)

----------

     50432

SQL> CONN SCOTT/TIGER@LGSTDBY

CONNECTED.

SQL> DELETE FROM TEST;

50432 ROWS DELETED.

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)

----------

         0

SQL> CONN SCOTT/TIGER@MISDB

CONNECTED.

SQL> SELECT COUNT(*) FROM TEST;

  COUNT(*)

----------

         0

·         Monitoring Log File Archival Information

Run on Primary

SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';

ALTER SYSTEM ARCHIVE LOG CURRENT;

Run on Standby

SELECT FILE_NAME FROM DBA_LOGSTDBY_LOG WHERE THREAD#=1 AND SEQUENCE#= (Sequence# from previous query);

APPLIED_SCN and NEWEST_SCN should match

SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

·         Monitoring SQL Apply on Logical Standby Databases

SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

SESSION_ID    STATE

----------    -------------

1             INITIALIZING

SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

SESSION_ID    STATE

----------    ------------------

1             LOADING DICTIONARY

SQL> COLUMN STATUS FORMAT A40

SQL> SELECT TYPE, STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS;

TYPE                           STATUS_CODE STATUS

------------------------------ ----------- ----------------------------

COORDINATOR                          16116 ORA-16116: no work available

READER                               16116 ORA-16116: no work available

BUILDER                              16116 ORA-16116: no work available

PREPARER                             16116 ORA-16116: no work available

ANALYZER                             16116 ORA-16116: no work available

APPLIER                              16116 ORA-16116: no work available

APPLIER                              16116 ORA-16116: no work available

APPLIER                              16116 ORA-16116: no work available

APPLIER                              16116 ORA-16116: no work available

APPLIER                              16116 ORA-16116: no work available