Logical Standby

Steps to Prepare Production LOGICAL Standby
========================================

1) Find tables without unique logical identifier in the primary database.

Use the following query to display a list of tables that SQL apply operations

might not be able to uniquely identify:

SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE

WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'Y'

2) Add a disabled primary-key RELY constraint

To create a disabled RELY constraint on a primary database table, use the following query.

alter table NHJPCM.SMP9101_NODE1_SVRJMSSTORE add primary key (recordhandle) rely disable;

3) Run the backup of primary

Use the following script to run the backup of primary to disk.

nohup rman cmdfile=rman_logical_build.rcv log=rman_logical_build.log &

Contents of rman_logical_build.rcv file

connect catalog rman/rman_dba@rman01p

connect target /

run {

allocate channel d1 type disk format ‘/opt/oracle/exp/std_back/logical_stdby_%d%s%U.ful';

allocate channel d2 type disk format '/opt/oracle/exp/std_back/logical_stdby_%d%s%U.ful';

allocate channel d2 type disk format '/opt/oracle/exp/std_back/logical_stdby_%d%s%U.ful';

allocate channel d2 type disk format '/opt/oracle/exp/std_back/logical_stdby_%d%s%U.ful';

backup as compressed backupset incremental level 0

tag='FOR_LOGICAL_BUILD'

database

include current controlfile for standby;

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

4) Modify the following parameters on primary and secondary in order to accommodate logical in the configuration

a) alter system set log_archive_config='DG_CONFIG=(SIG01P_KSTLUSGDP02,SIG01P_KSTLUSGDP01,SIG01P_KSTLUSGRP01)' scope=BOTH;

b) alter system set log_archive_dest_3='service=sig01p_kstlusgrp01 OPTIONAL LGWR SYNC AFFIRM REOPEN=30 NET_TIMEOUT=15 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sig01p_kstlusgrp01' scope=both;

c) alter system set log_archive_dest_state_3=DEFER;

4) On the Logical standby database:

Copy the password file from Primary to Logical.

Scp kstlusgdp01:$ORACLE_HOME/dbs/orapwsig01p $ORACLE_HOME/dbs

Scp the backup that we did on primary to logical in the same path (If we did a backup on to disk)

Scp –r kstlusgdp01:/opt/oracle/exp/std_back /opt/oracle/exp

Create a pfile for Logical.

Put the database in nomount mode from the file

Sql> startup nomount pfile=/opt/oracle/admin/sig01p/pfile/initlogical.ora

5) On the logical standby database run the duplicate command

Nohup rman cmdfile=rman_dup.rcv log=rman_dup.log &

The contents of the rman_dup.rcv script is

connect catalog rman/rman_dba@rman01p

connect target sys/iw2grl@sig01p_kstlusgdp01

connect auxiliary /

run {

allocate auxiliary channel d1 type disk;

allocate auxiliary channel d2 type disk;

allocate auxiliary channel d3 type disk;

allocate auxiliary channel d4 type disk;

duplicate target database for standby;

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

6) Create a spfile for Logical

Once the duplicate command finishes the restore.

Get the controlfile information from the following command

Sql> select name from v$controlfile;

After getting the controlfile information ,update the controlfiles parameter in the pfile(that we used).

And create a spfile from that pfile

SQL> create spfile from pfile=’/opt/oracle/admin/sig01p/pfile/initlogical.ora’;

SQL>shutdown immediate

SQL> startup mount

7) Check whether there are standby redo logs in the standby

SQL>select member,type from v$logfile;

If there are no standby redo logs,create as follows(which is no. of online redo log groups+1) with the same size as online redo log.

alter database add standby logfile group 4 ('+DGLAB01E_RECO') SIZE 500M;

alter database add standby logfile group 5 ('+DGLAB01E_RECO') SIZE 500M;

alter database add standby logfile group 6 ('+DGLAB01E_RECO') SIZE 500M;

alter database add standby logfile group 7 ('+DGLAB01E_RECO') SIZE 500M;

8) Start the redo apply

Sql> alter database recover managed standby database using current logfile disconnect;

9) Stop the redo apply

SQL> alter database recover managed standby database cancel;

10) Build the logminer dictionary

SQL> execute dbms_logstdby.build;

11) Covert to logical standby Database

SQL> alter database recover to logical standby sig01rp;

12) Create a new password file

os> orapwd file=orapwsig01rp password=iw2bic entries=30

13) Set initialization parameters for Logical if not set with pfile:

SQL> shutdown immediate

SQL> startup mount

SQL>alter system set LOG_ARCHIVE_DEST_1='USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sig01p_kstlusgrp01' scope=both;

SQL>alter system set LOG_ARCHIVE_DEST_2='+SIG01P_RECO VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=sig01p_kstlusgrp01' scope=both;

SQL>alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;

SQL>alter systems set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

On PRIMARY and PHYSICAL:

SQL>alter system set log_archive_dest_state_3=ENABLE scope=both;

14) Open the logical standby

SQL> alter database open resetlogs;

15) Start the logical apply:

SQL> alter database start logical standby apply immediate;