steps to do real time apply in oracle 11g

the feature can make 11g standby database more powerful and flexible

[@more@]

real time apply:

before:

check log file:

Successful mount of redo thread 1, with mount id 965009577
Physical Standby Database mounted.
Lost write protection disabled
Completed: alter database mount
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (CAT11GST)
Tue Jul 22 14:32:15 2008
MRP0 started with pid=23, OS id=2175024
MRP0: Background Managed Standby Recovery process started (CAT11GST)
Fast Parallel Media Recovery enabled
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 7 processes

after:

in primary:

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=CAT11GST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CAT11GST

SQL> alter system set log_archive_dest_2='SERVICE=CAT11GST SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CAT11GST';

System altered.

in standby:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> alter database add standby logfile ('/u02/oradata/cat11gst/CAT11GST/stb_redo1.log') size 50m;
alter database add standby logfile ('/u02/oradata/cat11gst/CAT11GST/stb_redo1.log') size 50m
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files

SQL> alter database add standby logfile ('/u02/oradata/cat11gst/CAT11GST/stb_redo1.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u02/oradata/cat11gst/CAT11GST/stb_redo2.log') size 50m;

Database altered.

SQL> alter database add standby logfile ('/u02/oradata/cat11gst/CAT11GST/stb_redo3.log') size 50m;

Database altered.

alter system set log_archive_dest_3 = 'LOCATION=/u02/oradata/cat11gst/arch2 VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=CAT11GST';

System altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

check stanby log:

Completed: alter database open read only
Tue Jul 22 16:22:39 2008
alter database recover managed standby database
using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (CAT11GST)
Tue Jul 22 16:22:39 2008
MRP0 started with pid=24, OS id=2265102
MRP0: Background Managed Standby Recovery process started (CAT11GST)
Fast Parallel Media Recovery enabled
Managed Standby Recovery starting Real Time Apply

test real time apply:

in primary:

SQL> insert into szhao.read_only values(3,'bb');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into szhao.read_only values(4,'dd');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into szhao.read_only values(5,'ee');

1 row created.

SQL> commit;l
2

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into szhao.read_only values(6,'ff');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> insert into szhao.read_only values(7,'gg')l
2

SQL> insert into szhao.read_only values(7,'gg');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

in standby:

SQL> select * from szhao.read_only;

A B
---------- ------------------------------
1 aa
2 bb
3 bb
4 dd

SQL> /

A B
---------- ------------------------------
1 aa
2 bb
3 bb
4 dd
5 ee
6 ff
7 gg

7 rows selected.

note that the previous redo log won't be used anymore for recovery:

SQL> col member for a50
SQL> set linesize 200

SQL> select * from V$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u02/oradata/cat11gst/CAT11GSTredo03.log NO
2 ONLINE /u02/oradata/cat11gst/CAT11GSTredo02.log NO
1 ONLINE /u02/oradata/cat11gst/CAT11GSTredo01.log NO
4 STANDBY /u02/oradata/cat11gst/CAT11GST/stb_redo1.log NO
5 STANDBY /u02/oradata/cat11gst/CAT11GST/stb_redo2.log NO
6 STANDBY /u02/oradata/cat11gst/CAT11GST/stb_redo3.log NO

6 rows selected.

SQL> select * from V$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 16 52428800 1 YES CLEARING_CURRENT 1122302 2008-07-22:16:39:26
2 1 15 52428800 1 YES CLEARING 1122203 2008-07-22:16:34:50
3 1 16 52428800 1 YES CLEARING 1122302 2008-07-22:16:39:26

alert log:

RFS[2]: Assigned to RFS process 1503478
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/u02/oradata/cat11gst/CAT11GST/stb_redo1.log'
Tue Jul 22 16:30:55 2008
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 454692
RFS[3]: Identified database type as 'physical standby'
kcrrvslf: active RFS archival for log 4 thread 1 sequence 12
RFS[3]: Successfully opened standby log 5: '/u02/oradata/cat11gst/CAT11GST/stb_redo2.log'
Tue Jul 22 16:31:16 2008
Media Recovery Log /u02/oradata/cat11gst/arch2/1_11_660407962.dbf
Media Recovery Waiting for thread 1 sequence 12 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0
Mem# 0: /u02/oradata/cat11gst/CAT11GST/stb_redo1.log
Tue Jul 22 16:32:56 2008
Primary database is in MAXIMUM PERFORMANCE mode
kcrrvslf: active RFS archival for log 4 thread 1 sequence 12
RFS[2]: Successfully opened standby log 5: '/u02/oradata/cat11gst/CAT11GST/stb_redo2.log'
Tue Jul 22 16:32:56 2008
Media Recovery Waiting for thread 1 sequence 13 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 13 Reading mem 0
Mem# 0: /u02/oradata/cat11gst/CAT11GST/stb_redo2.log
Tue Jul 22 16:33:52 2008
Primary database is in MAXIMUM PERFORMANCE mode
kcrrvslf: active RFS archival for log 5 thread 1 sequence 13
RFS[2]: Successfully opened standby log 4: '/u02/oradata/cat11gst/CAT11GST/stb_redo1.log'
Tue Jul 22 16:33:52 2008
Media Recovery Waiting for thread 1 sequence 14 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 14 Reading mem 0
Mem# 0: /u02/oradata/cat11gst/CAT11GST/stb_redo1.log
Tue Jul 22 16:34:49 2008
Primary database is in MAXIMUM PERFORMANCE mode
kcrrvslf: active RFS archival for log 4 thread 1 sequence 14
RFS[2]: Successfully opened standby log 5: '/u02/oradata/cat11gst/CAT11GST/stb_redo2.log'
Tue Jul 22 16:34:49 2008
Media Recovery Waiting for thread 1 sequence 15 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 15 Reading mem 0
Mem# 0: /u02/oradata/cat11gst/CAT11GST/stb_redo2.log