LOGICAL STANDBY DATABASE STEP BY STEP  

 

Primary DB: CLRVMS (ip:10.3.248.5 and 10.3.248.8)

Logical Standby DB: CLRVMS_B(ip:10.3.248.129)

 

1. Please check whether Primary database is in archivelog or not.It should be in archivelog.

 

select log_mode from v$database;

LOG_MODE

————

ARCHIVELOG

 

2. Enable Force Logging,

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

 

3. Configure Standby Redo Log on PRIMARY

 

1. Check the Online log files and sizes on Primary

SQL>SELECT GROUP#,BYTES FROM V$LOG;

Create Standby Redo Log on Primary where number of Standby redo log group should be same with number of online log files and both size should be same:

 

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 524288000;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 524288000;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 524288000;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 SIZE 524288000;

 

Verify the standby redo log file groups were created(do this after the creation of stanby database)

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS,bytes FROM V$STANDBY_LOG;

SQL> select group#,bytes from v$standby_log;

4.Please add following entry marked in bold in existing spfile or pfile of primary database CLRVMS for configuring  logical standby database.

 

CLRVMS2.__db_cache_size=4496293888
CLRVMS1.__db_cache_size=4429185024
CLRVMS2.__java_pool_size=33554432
CLRVMS1.__java_pool_size=33554432
CLRVMS1.__large_pool_size=100663296
CLRVMS2.__large_pool_size=33554432
CLRVMS1.__oracle_base=’/opt/app/oracle’#ORACLE_BASE set from environment
CLRVMS2.__oracle_base=’/opt/app/oracle’#ORACLE_BASE set from environment
CLRVMS2.__pga_aggregate_target=3120562176
CLRVMS1.__pga_aggregate_target=3120562176
CLRVMS2.__sga_target=5804916736
CLRVMS1.__sga_target=5804916736
CLRVMS2.__shared_io_pool_size=0
CLRVMS1.__shared_io_pool_size=0
CLRVMS2.__shared_pool_size=1140850688
CLRVMS1.__shared_pool_size=1140850688
CLRVMS2.__streams_pool_size=33554432
CLRVMS1.__streams_pool_size=33554432
*._kghdsidx_count=1
*._log_deletion_policy=’ALL’
*._shared_pool_reserved_pct=10
*.audit_file_dest=’/opt/app/oracle/admin/CLRVMSTT/adump’
*.audit_trail=’DB’
*.cluster_database=true
*.compatible=’11.2.0.0.0′
*.control_files=’+LOG/clrvms/controlfile/current.763.792022797′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_create_online_log_dest_1=’+LOG’
*.db_domain=”
*.db_file_name_convert=’+DATA1′,’+DATA’
*.db_name=’CLRVMS’
*.db_recovery_file_dest=’+LOG’
*.db_recovery_file_dest_size=536870912000
*.deferred_segment_creation=FALSE
*.diagnostic_dest=’/opt/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLRVMSXDB)’
*.fal_client=’CLRVMS’
*.fal_server=’CLRVMS_B’
CLRVMS2.instance_number=2
CLRVMS1.instance_number=1
*.job_queue_processes=10
*.log_archive_config=’DG_CONFIG=(CLRVMS,CLRVMS_B)’
*.log_archive_dest_1=’LOCATION=+LOG’
*.log_archive_dest_2=’SERVICE=CLRVMS_B VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CLRVMS_B’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%t_%s_%r.dbf’
*.log_file_name_convert=’+LOG1′,’+LOG’
*.memory_max_target=8912896000
*.memory_target=6878658560
CLRVMS2.memory_target=8925478912
CLRVMS1.memory_target=8925478912
*.nls_length_semantics=’CHAR’
*.open_cursors=4000
*.processes=600
*.query_rewrite_enabled=’TRUE’
*.query_rewrite_integrity=’TRUSTED’
*.remote_listener=’vms-db-scan.nmc.vms:1521′
*.remote_login_passwordfile=’exclusive’
*.sec_case_sensitive_logon=FALSE
*.sga_target=0
CLRVMS2.sga_target=0
CLRVMS1.sga_target=0
*.shared_pool_size=0
CLRVMS1.shared_pool_size=0
CLRVMS2.shared_pool_size=1073741824
*.standby_file_management=’AUTO’
CLRVMS2.thread=2
CLRVMS1.thread=1
*.undo_retention=28800
CLRVMS1.undo_tablespace=’UNDOTBS1′
CLRVMS2.undo_tablespace=’UNDOTBS2′

 

5. Create the parameter file for standby from Primary database and change relevant parameters indicated by sample:

CREATE PFILE=’/tmp/initCLRVMS_B.ora’ from spfile;

 

Sample Standby init.ora file will look below:-

CLRVMS_B.__db_cache_size=1845493760
CLRVMS_B.__java_pool_size=16777216
CLRVMS_B.__large_pool_size=16777216
CLRVMS_B.__oracle_base=’/data/app/oracle11g’#ORACLE_BASE set from environment
CLRVMS_B.__pga_aggregate_target=1493172224
CLRVMS_B.__sga_target=2801795072
CLRVMS_B.__shared_io_pool_size=0
CLRVMS_B.__shared_pool_size=872415232
CLRVMS_B.__streams_pool_size=16777216
*._kghdsidx_count=1
*._shared_pool_reserved_pct=10
*.audit_file_dest=’/data/app/oracle11g/admin/CLRVMS_B/adump’
*.audit_trail=’DB’
*.compatible=’11.2.0.0.0′
*.control_files=’+LOG1/clrvms/controlfile/control01.ctl’#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest=’+DATA1′
*.db_create_online_log_dest_1=’+LOG1′
*.db_domain=”
*.db_file_name_convert=’+DATA’,’+DATA1′
*.db_name=’CLRVMS_B’#db_name
*.db_recovery_file_dest=’+LOG1′
*.db_recovery_file_dest_size=536870912000
*.db_unique_name=’CLRVMS_B’
*.deferred_segment_creation=FALSE
*.diagnostic_dest=’/data/app/oracle11g’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLRVMSXDB)’
*.fal_client=’CLRVMS_B’
*.fal_server=’CLRVMS’
*.filesystemio_options=’setall’
*.job_queue_processes=5
*.log_archive_config=’DG_CONFIG=(CLRVMS,CLRVMS_B)’
*.log_archive_dest_1=’LOCATION=+LOG1′
*.log_archive_dest_2=’SERVICE=CLRVMS LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CLRVMS’
*.log_archive_format=’%t_%s_%r.dbf’
*.log_file_name_convert=’+LOG’,’+LOG1′
*.memory_max_target=4G
*.memory_target=4G
*.nls_length_semantics=’CHAR’
*.open_cursors=4000
*.processes=600
*.query_rewrite_enabled=’TRUE’
*.query_rewrite_integrity=’TRUSTED’
*.remote_login_passwordfile=’exclusive’
*.sec_case_sensitive_logon=FALSE
*.standby_file_management=’AUTO’
*.undo_management=’AUTO’
*.undo_retention=28800

 

6. Connect using RMAN to take backup of the Primary db:

 

export ORACLE_SID=CLRVMS1

rman target / nocatalog log=/tmp/rman_bkp.log << EOF1
run
{
backup as compressed backupset database format ‘/export/home/oracle/stdby/CLRVMS_%U’;
backup as compressed backupset archivelog all format ‘/export/home/oracle/stdby/CLRVMS_ARCH_%U’;
backup current controlfile for standby format ‘/export/home/oracle/stdby/stdby.ctl’;
}
exit;
EOF1

7.Copy the backup files (backupset,standby controlfile,archivelog and password file) to the same location in standby database server.

 

8.Please start the standby database in nomount mode.

 

On standby server

 

export ORACLE_SID=CLRVMS_B

SQL>startup nomount

9. Create password file for standby database

 

export ORACLE_SID=CLRVMS_B
orapwd file=orapwCLRVMS_B  password=******
OR

copy the Primary DB password file and rename it to standby db name.

Ex:-

cp orapwCLRVMS1 orapwCLRVMS_B

 

10. Check the checksum of both the password file. Both the password file checksum should be same:

 

cksum orapwCLRVMS1

cksum orapwCLRVMS_B

 

11. Establish the connectivity between primary (both the nodes) and standby.

CLRVMS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vms-db-scan.nmc.vms)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLRVMS)
)
)

CLRVMS_B =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.248.129)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLRVMS_B)
)
)

NOTE: Use TNSPING <servicename> to check the connectivity between PRIMARY and STANDBY

Please add following entry in standby tnsnames.ora

CLRVMS_B =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.3.248.129)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLRVMS_S)
)
)

CLRVMS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vms-db-scan.nmc.vms)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLRVMS)
)
)

 

 

12. Start the STANDBY instance and use RMAN duplicate to create standby database NOTE: Make sure the ASM instance also running.

$export ORACLE_SID=CLRVMS_B

Sqlplus / as sysdba

SQL>startup nomount
SQL>create spfile from pfile=’/temp/initCLRVMS_B.ora’
SQL> exit

 

$export ORACLE_SID=CLRVMS_B

rman target sys/******@CLRVMS auxiliary /

RMAN> RUN {
allocate auxiliary channel C1 device type disk;
duplicate target database for standby;
}

13. Create SRL(Standby Redo logs) on standby

 

NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

For Example,

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;

 

N.B:- This is not necessary if the Standby logfile is already exist.

 

14. Start the MRP process in PRIMARY

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE

 

15. To make real time log transfer in standb

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database using current logfile disconnect

16. Make Sure that Physical Standby is in Sync with Primary Database
    
    Use following query on Standby to check:

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied

———- ———————- ———————

1                 233996                233996

2                 282421                282421

 

17. Stop Redo Apply on the Physical Standby Database (CLRVMS_B):

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

  

18. Build a Dictionary in the Redo Data on Primary Database

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

19. Convert to a Logical Standby Database

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <new db_name>; (in mount state)

the control file will be modified with the new database name)

Ex:

 

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY CLRVMS_B;

 

The redo logs will be applied until the Log Miner dictionary is found in the log files. That could take several minutes. If a dictionary build is not successfully performed on the primary database, this command will never complete. You can cancel the SQL statement by issuing the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL statement from another SQL session.

 

20. Shutdown and Startup Logical Standby Database in Mount Stage

SQL> SHUTDOWN;
SQL> STARTUP MOUNT;

 

 

21. Open the Logical Standby Database

SQL> ALTER DATABASE OPEN RESETLOGS;

22. Start Logical Apply on Standby

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

 

23.Skip the schema that do not need to be replicated

 

SKIP table from SQL Apply: If SQL Apply process hangs to a particular table and you want to skip that table, do the following:

 

1. please see if you can skip apply for SYS.AUD$ table. If yes, please setup skip rule, so that transaction related with this table will not be applied
   eg:
   stop logical apply
   SQL> exec dbms_logstdby.skip(‘DML’,’CLARITY’,’TABLE_NAME’);

start logical sapply

 

24.Checking whether logical standby is in sync with primary

 

SQL>select a.sequence_received,a.thread,b.sequence_applied,b.thread from (select max(sequence#) sequence_received,thread# thread from dba_logstdby_log group by thread#) a,

(select max(sequence#) sequence_applied,thread# thread from dba_logstdby_log where applied=’YES’ group by thread#) b where a.thread=b.thread

 

SEQUENCE_RECEIVED          THREAD SEQUENCE_APPLIED          THREAD

—————– ————— —————- —————

283420               2           283420               2

235031               1           235031               1