Oracle DataGuard Logical Standby Set up


Oracle documents

Overview of Data Guard
OracleŽ Data Guard Concepts and Administration 10g Release 2 (10.2)

Server db01(primary) and db02(to be set up as standby)

(if you have different file path, server names, change the scripts accordingly)
    OracleHome name: OraDb10g_home
OracleHome path: /usr/oracle/product/10.2.0/db_1
    Global Database Name: prime (or stdby)
SID: prime (or stdby)
    local bin      /usr/local/bin
ORACLE_HOME /usr/oracle/product/10.2.0/db_1/
control files /data/oracle/prime/
data files /data/oracle/prime/ (both db01 and db02 have the same path)
archive files /data/arch/prime/ (for archive log generated from server in primary mode)
/data/arch/stdby/ (for archive log generated from server in standby mode)

Prepare primary database

Use following SQLs to check db01

REM  see unsupported columns
select distinct owner, table_name, column_name, data_type from dba_logstdby_unsupported order by owner, table_name;
REM skipped tables
select owner, statement_opt, name from dba_logstdby_skip;
REM skipped transactions
select * from dba_logstdby_skip_transaction;
REM see not unique column
select * from dba_logstdby_not_unique;

Sample fixes

REM create a fake key, for tables with constant contents
alter table my_table1 add primary key(threshold_value, id) rely disable;
REM create unique index
create unique index my_table2_many_id_idx
on my_table2 (name_id, address_id, company_id)
tablespace my_index_tablespace
storage (initial 32M next 8M pctincrease 0);
REM remove null value and create a composit primary key
delete my_table3 where msg_type is null;
alter table my_table3 modify (msg_type char(1) not null);
alter table my_table3 add primary key (name_id, msg_type, msg_id) rely disable;

Get the scripts

Copy files onto /data/reset/


Edit these files based on your needs, make sure ssh is working for oracle user between db01 and db02

Do the work

Login to db01 as oracle


Login on db02 as oracle


That's all !