Logical Standby

Steps to create logical standby database which has similar file system structure.

1) Identify unsupported objects and tables that have no primary key or unique constratint

SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;

SELECT OWNER, TABLE_NAME, BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;

Change unsupported column and add primary key if any rows selected from above queries respectively

2) On primary database enable below intialization parametera and ensure that database archivelog mode.

log_archive_dest_1 = "location=/oracle/ORCLDB_P/arch"
log_archive_dest_2 = "SERVICE=ORCLDB_S LGWR"
log_archive_dest_state_1 = ENABLE
log_archive_dest_state_2 = ENABLE
log_archive_format = ARCH_%t_%r_%s.arc

Shutdown immediate
Startup mount
alter database archivelog;
alter database open;
archive log list;


3) On Standby Database enable the below intialization parameters

log_archive_start = true
log_archive_dest_1 = "location=/oracle/ORCLDB_P/arch"
log_archive_dest_state_1 = ENABLE
log_archive_format = ARCH_%t_%r_%s.arc

4) Enable supplemental logging on primary key and unique index

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS;

SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI from V$DATABASE;

SUP SUP
--- ---
YES YES

5) Make sure log_parallelism is set to 1 (default)

SQL> show parameter LOG_PARALLELISM;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_parallelism integer 1

6) Create alternate tablespace for logical standby system tables

CREATE TABLESPACE LOGMNR DATAFILE '/oracle/ORCLDB_P/oradata/logmnr.dbf' SIZE 50M;;

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE ('LOGMNR');

7) On Primary perform cold backup and copy all files(datafiles,redo..) to standby site

8) Create Logical standby control file from primary

ALTER DATABASE CREATE LOGICAL STANDBY CONTROLFILE AS '/backup/log.ctl';

9) On primary database and build the log miner dictionary

ALTER SYSTEM ENABLE RESTRICTED SESSION;

EXECUTE DBMS_LOGSTDBY.BUILD;

10) Archive log current

ALTER SYSTEM ARCHIVE LOG CURRENT;

11) Identify archived redo log that contains the log miner dictionary

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES' AND STANDBY_DEST='NO';

12) Startup mount standby database and turn on the database guard

STARTUP MOUNT;

ALTER DATABASE GUARD ALL;

ALTER DATABASE OPEN;

13) Create tempfile on standby database

alter tablespace TEMP ADD TEMPFILE '/oracle/ORCLDB_P/oradata/temp.dbf' size 200M reuse

14) On standby database, register logfile identified in step 11

alter database register logical logfile '/oracle/ORCLDB_P/ARCH_001_8474994_00011.arc'

15) alter database to begin SQL apply on the logical standby

ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

16) Register database with listener

ALTER SYSTEM REGISTER;

17) Check V$LOGSTDBY & DBA_LOGSTDBY_PROGRESS whether archived logs being applied if not start apply again

ALTER DATABASE START LOGICAL STANDBY APPLY ;

select SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END from DBA_LOGSTDBY_LOG order by SEQUENCE#;