How to Create Logical Standby in DataGuard


Please refer to my post of creating physical standby, and then follow below steps to convert it to logical standby.

On standby database

Make sure that all the sequences generated on primary side has been applied on standby.
Cancel the recovery on standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On primary database

alter system set LOG_ARCHIVE_DEST_1='LOCATION=<PATH> VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)';

Make a directory for standby archive logs

alter system set LOG_ARCHIVE_DEST_3='LOCATION=<PATH> VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)';

Run DBMS_LOGSTDBY.BUILD package to create metadata for log miner to apply SQLs on logical standby site.

EXECUTE DBMS_LOGSTDBY.BUILD;

On standby database

Check if the database is in mount mode.
DB should be in mount mode

Start the recover and change the DB name of standby

ALTER DATABASE RECOVER TO LOGICAL STANDBY <NEW DATABASE NAME>;

Set the archive log destination on standby site as well.

alter system set LOG_ARCHIVE_DEST_1='LOCATION=<PATH> VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES)';
alter system set LOG_ARCHIVE_DEST_3='LOCATION=<PATH> VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=<SAME SERVICE> LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<SAME NAME>'; #show parameter log_archive_dest_2 before and after

ALTER DATABASE OPEN RESETLOGS;

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(<PRIMARY>,<NEW DATABASE NAME>)' SCOPE=BOTH;

On primary database

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(<PRIMARY>,<NEW DATABASE NAME>)' SCOPE=BOTH;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=<SAME SERVICE> LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<NEW DATABASE NAME>'; #show parameter log_archive_dest_2 before and after

tnsnames.ora replace service_name parameter with SID parameter.

On standby database

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.