Oracle 11GR2 logical Standby Database Setup

Oracle 11.2.0.1 logical Standby Library Environment setup

After you create a physical standby library and create a physical fallback library, it's best to create your own spfile

Stop redo application on physical standby

ALTER DATABASE recover managed STANDBY database cancel;

Generate Logminer on main Library several switch logs are ignored

Execute dbms_logstdby.build;

Convert to logical Standby library on physical repository

ALTER DATABASE recover to logical standby ORCL; <--db_name Parameters

Shutdown immediate

Startup Mount

SELECT Db_unique_name,open_mode, database_role,switchover_status,guard_status,protection_mode from V$DATABASE;

Password file for rebuilding logical standby on the standby library

Orapwd file= $ORACLE _home/dbs/orapwstby password=oracle entries=5 ignorecase=y force=y

modifying parameters on a standby library

Alter system set log_archive_dest_1= ' Location=/u01/oradata/arch/stby valid_for= (online_logfiles,all_roles) db_ Unique_name=stby ';

Alter system set log_archive_dest_2= ' Location=/u01/oradata/arch valid_for= (standby_logfiles,standby_roles) db_ Unique_name=stby ';

Create an alternate log on a standby library

ALTER DATABASE ADD standby logfile Group 4 ('/u01/oradata/stby/standbylj01.log ') size 20M reuse;

ALTER DATABASE ADD standby logfile Group 5 ('/u01/oradata/stby/standbylj02.log ') size 20M reuse;

ALTER DATABASE ADD standby logfile Group 6 ('/u01/oradata/stby/standbylj03.log ') size 20M reuse;

Select member from V$logfile;

Open logical standby on the standby library

ALTER DATABASE open resetlogs;

ALTER DATABASE start logical standby apply immediate;

Check on the Standby library

Select Instance_name,status from V$instance;

SELECT Db_unique_name,open_mode, database_role,switchover_status,guard_status,protection_mode from V$DATABASE;

Select Group#,sequence#,used,status from V$standby_log;

Test

Creating on the main library

CREATE TABLE SCOTT.T4 (id int);

INSERT into SCOTT.T4 values (4);

Commit

alter system switch logfile;

After a while, query on the logical standby library

SELECT * from Scott.t4;