Using Logical Standby Database as Replicate Source

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

Created a physical standby LAA_STBY (with rman duplicate)
Added the configuration to Dataguard (needed additional Standby redo logs).
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
Removed the physical standby from the data guard config and added back in.
Created basic MV to query SCOTT.EMP table and limit with a WHERE clause on EMPNO=7934
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.
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.

https://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#SBYDB00200

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.

Verify the Setup within Dataguard on the Primary

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.

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.

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.