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;