RE: Real-time Apply in DataGuard

That works exactly like how i saw it in my testing also, its just switches 
over to using archive log shipping.

joe

_______________________________________
Joe Testa, Oracle Certified Professional 
Senior Engineering & Administration Lead
(Work) 614-677-1668
(Cell) 614-312-6715






From:
D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
To:
"Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>, Andrew Kerber 
<andrew.kerber@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" 
<oracle-l@xxxxxxxxxxxxx>
Date:
10/11/2010 10:28 AM
Subject:
RE: Real-time Apply in DataGuard



Brandon,
X-archive-position: 31518
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce@xxxxxxxxxxxxx
Errors-to: oracle-l-bounce@xxxxxxxxxxxxx
X-original-sender: Freek.DHooge@xxxxxxxxx
Precedence: normal
Reply-To: Freek.DHooge@xxxxxxxxx
List-help: <mailto:ecartis@xxxxxxxxxxxxx?Subject=help>
List-unsubscribe: <oracle-l-request@xxxxxxxxxxxxx?Subject=unsubscribe>
List-software: Ecartis version 1.0.0
List-Id: oracle-l <oracle-l.freelists.org>
X-List-ID: oracle-l <oracle-l.freelists.org>
List-subscribe: <oracle-l-request@xxxxxxxxxxxxx?Subject=subscribe>
List-owner: <mailto:steve.adams@xxxxxxxxxxxx>
List-post: <mailto:oracle-l@xxxxxxxxxxxxx>
List-archive: <http://www.freelists.org/archives/oracle-l>
X-list: oracle-l

Very nice job.
Learned something new again  :-)

What was the database version you tested this on?


Regards,
 
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

-----Original Message-----
From: Allen, Brandon [mailto:Brandon.Allen@xxxxxxxxxxx] 
Sent: vrijdag 8 oktober 2010 21:30
To: D'Hooge Freek; Andrew Kerber; oracle-l@xxxxxxxxxxxxx
Subject: 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: