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#;

Physical Standby

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

1) Put the primary database in force logging mode

ALTER DATABASE FORCE LOGGING;

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


log_archive_dest_1='LOCATION=/oracle/ORCLDB_P/arch’
log_archive_dest_2='SERVICE=ORCLDB_S reopen=60'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=ARCH_%t_%r_%s.dbf

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


3) On Standby Database enable the below intialization parameter

fal_server=ORCLDB_P
fal_client=ORCLDB_S
log_archive_dest_1='LOCATION=/oracle/ORCLDB_P/arch'
log_archive_dest_state_1=enable
log_archive_format=ARCH_%t_%r_%s.dbf

4) Startup nomount database

startup nomount;

5) On standby use RMAN Duplicate to restore standby database from primary

rman target sys/manager@ORCLDB_P auxiliary sys/manager nocatalog

run
{
allocate auxiliary channel c1 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER;
}

6) Put standby database in recover managed mode

alter database recover managed standby database disconnect from session;