Re: logical standby real time apply does not work unless manually switching logfile
- From: japplewhite@xxxxxxxxxxxxx
- To: "Oracle-L@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Aug 2011 14:29:25 -0500
Sundar,
Some clarification, please. When you say "If i switch the logfile, then
the redo log file gets archive and get applied over." do you mean that
you're manually issuing the "Alter System Switch LogFile" command on your
Primary, after which the Primary Archived RedoLog gets sent to the
Standby, where it is then applied? If so, that is perfectly normal
behavior if you have the Maximum Performance setup.
If you want every transaction in the Primary to be applied immediately to
the Standby, you need to run in Maximum Protection mode. However, beware
that your Primary could freeze if it loses connection to the Standby. If
you want more frequent RedoLog switches on the Primary, set the
archive_lag_target parameter to a low number (in seconds) in the Primary's
pfile or spfile, depending on what you use.
If I've misunderstand your problem, please straighten me out.
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk) / 512.935.5929 (pager)
From: sundar mahadevan <sundarmahadevan82@xxxxxxxxx>
To: "D'Hooge Freek" <Freek.DHooge@xxxxxxxxx>, "Oracle-L@xxxxxxxxxxxxx"
<oracle-l@xxxxxxxxxxxxx>
Date: 08/10/2011 01:19 PM
Subject: Re: logical standby real time apply does not work unless
manually switching logfile
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
Thanks for your response Freek. Yes, I have configured standby logfile
with the following query:
select 'alter database add standby logfile thread 1
'''||regexp_substr(MEMBER,'/.+/')||regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' as "Create Standby redo" from v$logfile lf , v$log
l where l.group# = lf.group#;
I ran the above query on primary and edited it to match the logical
standby location and preserved the size to match primary. If i did not
create standby logfiles, it would complain when i run "alter database
start logical standby apply immediate;". Yes the thread# match the primary
database.
On Wed, Aug 10, 2011 at 1:38 PM, D'Hooge Freek <Freek.DHooge@xxxxxxxxx>
wrote:
Sundar,
Because of vacation I'm not up to date with the thread to your question,
but have you configured the standby logfiles on your logical standby? And
do they have the same size (and thread#) as the online logfiles on your
primary database?
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
---
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of sundar mahadevan
Sent: woensdag 10 augustus 2011 19:23
To: Oracle-L@xxxxxxxxxxxxx
Subject: Re: logical standby real time apply does not work unless manually
switching logfile
I checked other sites(metalink inclusive) for steps in creating logical
standby and they all list log_archive_dest_1, log_archive_dest_2 and
log_archive_dest_3 specified. Do i need to have all three mandatory and is
the cause of my problem? Please advice. Could anyone please post their
logical standby log_archive_dest_* parameter settings for primary and
logical standby configuration.Thanks.
On Tue, Aug 9, 2011 at 2:48 PM, sundar mahadevan <
sundarmahadevan82@xxxxxxxxx> wrote:
Hi All,
Good day. I set up a logical standby but real time apply does not work. If
i switch the logfile, then the redo log file gets archive and get applied
over. I looked at the various sql commands here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ls.htm#CHDGBCGI
to see what is wrong but nothing comes up. Appreciate your help.Thanks in
advance.
Few responses for the sql statements:
On primary:
14:34:15 sys@MFPROD SQL>select current_scn from v$database;
CURRENT_SCN
-----------------------
5288795814937
Parameter settings:
log_archive_config string
dg_config=(MFPROD,MFPRODLS)
log_archive_dest_1 string LOCATION=/u01/arch/mfprod
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=MFPROD
log_archive_dest_3 string SERVICE=mfprod_logstdby
arch ASYNC VALID_FOR=(ONLINE_LOGFILE, ALL_ROLES) DB_UNIQUE_NAME=MFPRODLS
On Logical standby:
14:32:20 sys@MFPRODLS SQL>SELECT DEST_ID, RECOVERY_MODE FROM
V$ARCHIVE_DEST_STATUS WHERE DEST_ID=3;
DEST_ID RECOVERY_MODE
-------- -----------------------
3 LOGICAL REAL TIME APPLY
14:47:02 sys@MFPRODLS SQL>SELECT REALTIME_APPLY FROM V$LOGSTDBY_STATE;
REALTIME_APPLY
---------------
Y
Parameter settings:
log_archive_config string
dg_config=(MFPROD,MFPRODLS)
log_archive_dest_3 string
LOCATION=/u01/arch/mfprod_stdby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=MFPRODLS
14:38:37 sys@MFPRODLS SQL>SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN,
RESTART_SCN FROM V$LOGSTDBY_PROGRESS;
APPLIED_SCN LATEST_SCN MINING_SCN
RESTART_SCN
-------------------- -------------------- --------------------
--------------------
5288795813413 5288795813413 5288795813414
528879581341
Other related posts:
