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;
