RE: Real-time Apply in DataGuard

I just tested this and my primary database did *not* hang even when lgwr 
rotated through all its logs and started writing to the same log file that lns 
was still reading from.  The details are below - this seems to confirm that 
real-time apply is safe to use in maximum performance mode without any danger 
of halting the primary database due to delay in shipping the log files.  Please 
let me know if anyone sees any errors with my test.


1) Here is the status of the log files before I started the test - you can see 
that group 3 is the current group and it's on log sequence 299.  You can also 
see that LNS is currently writing the current redo to the standby.

11:49:10 SYS@baandev>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           
FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- 
------------- ---------------
         1          1        297  209715200          2 YES INACTIVE            
2478725990 07-OCT-10 14:12
         2          1        298  209715200          2 YES INACTIVE            
2478735507 07-OCT-10 18:47
         3          1        299  209715200          2 NO  CURRENT             
2478764775 08-OCT-10 09:04
         4          1        296  209715200          2 YES INACTIVE            
2478725759 07-OCT-10 14:12

11:49:10 SYS@baandev>select PROCESS, STATUS,SEQUENCE#,BLOCK# FROM 
V$MANAGED_STANDBY;

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
ARCH      CLOSING             297     382977
ARCH      CLOSING             298      75777
LNS       WRITING             299      15305


2) In another session I kicked of a CTAS statement to create a 1GB table and 
generate 1GB of redo.  The statement completed successfully and in the same 
amount of time as it took when I ran the same statement with Data Guard 
completely disabled:

11:50:19 BAAN@baandev>create table loadtest as select * from testtab;

Table created.

Elapsed: 00:04:37.80


3) Here you can see that the primary switched to sequence 300 and the arch 
process kicked in and started writing sequence 299 - I'm not sure if this just 
means that it started archiving it locally or if it was also transferring it to 
the standby:

11:51:12 SYS@baandev>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           
FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- 
------------- ---------------
         1          1        297  209715200          2 YES INACTIVE            
2478725990 07-OCT-10 14:12
         2          1        298  209715200          2 YES INACTIVE            
2478735507 07-OCT-10 18:47
         3          1        299  209715200          2 NO  ACTIVE              
2478764775 08-OCT-10 09:04
         4          1        300  209715200          2 NO  CURRENT             
2478770995 08-OCT-10 11:51

11:51:12 SYS@baandev>select PROCESS, STATUS,SEQUENCE#,BLOCK# FROM 
V$MANAGED_STANDBY;

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
ARCH      WRITING             299     102401
ARCH      CLOSING             298      75777
LNS       WRITING             299      15368


4) Here the primary database has rotated all the way around back to log group 
#3 and is now writing sequence 303 while LNS is still reading & writing 
sequence 299 so it looks like at this point LNS must've switched to the 
archived copy of 299:

11:53:25 SYS@baandev>select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           
FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- 
------------- ---------------
         1          1        301  209715200          2 YES ACTIVE              
2478771267 08-OCT-10 11:51
         2          1        302  209715200          2 NO  ACTIVE              
2478771507 08-OCT-10 11:52
         3          1        303  209715200          2 NO  CURRENT             
2478771739 08-OCT-10 11:53
         4          1        300  209715200          2 YES ACTIVE              
2478770995 08-OCT-10 11:51

11:53:25 SYS@baandev>select PROCESS, STATUS,SEQUENCE#,BLOCK# FROM 
V$MANAGED_STANDBY;

PROCESS   STATUS        SEQUENCE#     BLOCK#
--------- ------------ ---------- ----------
ARCH      WRITING             302     186369
ARCH      WRITING             300      18433
LNS       WRITING             299      56952


This is confirmed in the alert log on the primary:


*** 2010-10-08 11:54:10.343
Logfile reuse: expected 299 found 303
Detected Online Logfile Reuse. Check arvhived log..
*** 2010-10-08 11:54:10.346 75752 kcrr.c
  ... Opening archive log /baandev/oraarc/1_299_724152171.dbf
Archive Log file opened thr 1 seq 299
  ... Reading from block 81528


Regards,
Brandon


----------------------------
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of D'Hooge Freek


However, if the lns process is even further behind and the lgwr wants to start 
writing to the logfile from which the lns process is still reading, then the 
database will halt until the lns process has finished reading that logfile.

----------------------------
From: Andrew Kerber [mailto:andrew.kerber@xxxxxxxxx]

I have never implemented real-time apply, but it is my understanding that there 
are performance issues when there are problems with the network


Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.
--
http://www.freelists.org/webpage/oracle-l

Other related posts: