We're here to help!

Using Logical Standby Database as Replicate Source

Follow

Read our docs The following document describes the steps required to configure and use an Oracle Logical Standby as a source for Replication.  The advantage of such a configuration allows for the replication to a separate target environment, additional schemas/objects that have been built within the logical standby.

In this example, we build a new materialized views in the logical standby that report on data with the SCOTT.EMP table, which is updated by being part of the standby configuration.  These materialized views are then replicated to a separate Target database.


Description

  1. Created a physical standby LAA_STBY (with rman duplicate) 
  2. Added the configuration to Dataguard (needed additional Standby redo logs).
  3. Performed the necessary prereq steps to convert from physical to logical.  
    1. Adding the log miner dictionary
    2. Started recover to logical database
    3. Shutdown/Startup
    4. Open Resetlogs
  4. Removed the physical standby from the data guard config and added back in.
  5. Created basic MV to query SCOTT.EMP table and limit with a WHERE clause on EMPNO=7934
  6. Configured Dbvisit Replicate with LAA_STBY as source and oracle database TGT as target.
    1. Needed to create a logon trigger for DBVREP to ALTER SESSION GUARD OFF before inserting into the DBVREP replication tables.
  7. Updated data within the tables on Primary and checked the replication chain through to the data within the tables on TGT

Environment
 

Host (Linux)

Database (11.2.0.4)

Database Role

dbvlin701

LAA

Primary

dbvlin702

LAA_STBY

Logical Standby/Replication Source

dbvlin829

TGT

Replication Target  


Hostname

Database

Replicated Objects

dbvlin701

LAA

SCOTT.EMP

dbvlin702

LAA_STBY

SCOTT.EMP_MV

dbvlin829

TGT

SCOTT.EMP_MV

  

Steps Performed
 
Step 1 
In order to build a logical standby, we first must have a physical standby managed by dataguard in place.  In the example below, I built a Physical Standby using the RMAN Duplicate option.
Step 2
Add this new Physical Standby into the Dataguard configuration
SQL> alter system set dg_broker_Start=true;
System altered.
 
$ dgmgrl sys/kiwi123@LAA
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL>
DGMGRL> create configuration laa_dg_config as PRIMARY DATABASE IS LAA CONNECT IDENTIFIER IS LAA;
Configuration "laa_dg_config" created with primary database "laa"
DGMGRL> ADD DATABASE laa_stby AS CONNECT IDENTIFIER IS LAA_STBY MAINTAINED AS PHYSICAL;
Database "laa_stby" added
DGMGRL>
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration
Configuration - laa_dg_config
  Protection Mode: MaxPerformance
  Databases:
    laa      - Primary database
    laa_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
Step 3
Convert the Physical Standby to a Logical Standby
Create Logical Standby Dictionary on Primary
SQL> exec dbms_logstdby.build
PL/SQL procedure successfully completed.
SQL> conn sys/kiwi123@laa_Stby as sysdba
Connected.
APPLY must be off at this point!
SQL> alter database recover to logical standby laa_stby;
Database altered.
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open resetlogs;
Step4
On the primary database connect to DGMGRL and remove the old configuration and add it back in now as a logical standby
DGMGRL> remove database laa_stby;
Removed database "laa_stby" from the configuration
DGMGRL> ADD DATABASE laa_stby AS CONNECT IDENTIFIER IS LAA_STBY;
Database "laa_stby" added
DGMGRL> enable database laa_stby;
Enabled.

On the standby database start the logical apply.

SQL> alter database start logical standby apply immediate;
Database altered.
SQL> select database_role from v$database;
 
DATABASE_ROLE
----------------
LOGICAL STANDBY

Verify the Setup within Dataguard on the Primary

DGMGRL> show configuration
 
Configuration - laa_dg_config
 
  Protection Mode: MaxPerformance
  Databases:
    laa      - Primary database
    laa_stby - Logical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL>
Step 5
Created a simple Materialised View on the Logical Standby which will form the basis of the replication source.  The standby guard must be disabled to allow the creation of new objects.  If this is handled at the session level, then this guard status is disabled upon exit.  
SQL> ALTER SESSION DISABLE GUARD;
SQL> CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP WITH PRIMARY KEY INCLUDING NEW VALUES;
SQL> CREATE MATERIALIZED VIEW SCOTT.EMP_MV
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS SELECT  *  
FROM SCOTT.EMP E WHERE EMPNO = 7934;
Step 6
In order to allow replication to run, the DBVREP schema on the Source Database (logical standby) needs to maintain a set of dictionary tables containing replication metadata and for this the Guard must be disabled.  It isn't advisable to disable the guard as a long term solution. So for this, I created a logon trigger and altered the session and disabled guard for each connection as the DBVREP schema. 
SQL> ALTER SESSION DISABLE GUARD;
SQL > CREATE OR REPLACE TRIGGER DBVREP_GUARD_OFF
AFTER LOGON ON DATABASE 
BEGIN    IF USER = 'DBVREP'   
THEN    execute immediate 'alter session DISABLE GUARD';   
END IF; 
EXCEPTIONWHEN OTHERS THENNULL;
END; 
/

Configured Dbvisit Replicate with LAA_STBY as source and oracle database TGT as target and SCOTT.EMP_MV as the object to be replicated.

Once the  laastby_tgt-all.sh has been executed successfully follow the next steps and scp the relevant *APPLY* script to the target machine.

Start the MINE and APPLY servers.

Step 7

Performing a simple test update a row on primary database.

PRIMARY DATABASE

SQL> update scott.emp set sal=sal+10 where empno=7934;
SQL> commit;
SQL> select empno, sal from scott.emp where empno=7934;

    EMPNO	  SAL
---------- ----------
      7934	 1480

LOGICAL STANDBY

SQL> select empno, sal from scott.emp_mv;

    EMPNO	  SAL
---------- ----------
      7934	 1480

/ Dbvisit Replicate 2.8.00(MAX edition) - Evaluation License expires in 30 days
MINE is running. Currently at plog 38 and SCN 1511946 (02/20/2017 21:15:14).
APPLY is running. Currently at plog 38 and SCN 1511886 (02/20/2017 21:15:07).
Progress of replication laastby_tgt:MINE->APPLY: total/this execution
------------------------------------------------------------------------------------------------------------------------------------------------
SCOTT.EMP_MV:                 100%  Mine:7/7             Unrecov:0/0         Applied:7/7         Conflicts:0/0       Last:20/02/2017 21:13:10/OK
------------------------------------------------------------------------------------------------------------------------------------------------
1 tables listed.


TARGET

SQL> select * from scott.emp_mv;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7934 MILLER     CLERK	      7782 23-JAN-82	   1480 		   10
Have more questions? Submit a request

Comments