RE: Real-time Apply in DataGuard
Date: Mon, 11 Oct 2010 10:39:58 -0400
Message-ID: <OF471EAB39.88AF4778-ON852577B9.0050781F-852577B9.00508FE9_at_lnotes-gw.ent.nwie.net>
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_at_uptime.be>
To:
"Allen, Brandon" <Brandon.Allen_at_OneNeck.com>, Andrew Kerber
<andrew.kerber_at_gmail.com>, "oracle-l_at_freelists.org"
<oracle-l_at_freelists.org>
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_at_freelists.org
Errors-to: oracle-l-bounce_at_freelists.org
X-original-sender: Freek.DHooge_at_uptime.be
Precedence: normal
Reply-To: Freek.DHooge_at_uptime.be
List-help: <mailto:ecartis_at_freelists.org?Subject=help> List-unsubscribe: <oracle-l-request_at_freelists.org?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_at_freelists.org?Subject=subscribe> List-owner: <mailto:steve.adams_at_ixora.com.au> List-post: <mailto:oracle-l_at_freelists.org> 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_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
-----Original Message-----
From: Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com]
Sent: vrijdag 8 oktober 2010 21:30
To: D'Hooge Freek; Andrew Kerber; oracle-l_at_freelists.org
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.
- 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_at_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
--------- ------------ ---------- ----------
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_at_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_at_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
--------- ------------ ---------- ----------
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_at_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
--------- ------------ ---------- ----------
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_gmail.com]
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 -- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 11 2010 - 09:39:58 CDT