Re: logical standby real time apply does not work unless manually switching logfile

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_at_gmail.com> To: "D'Hooge Freek" <Freek.DHooge_at_uptime.be>, "Oracle-L_at_freelists.org" <oracle-l_at_freelists.org>
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_at_freelists.org

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_at_uptime.be> 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_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer

---
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 
On Behalf Of sundar mahadevan
Sent: woensdag 10 augustus 2011 19:23
To: Oracle-L_at_freelists.org
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_at_gmail.com> 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_at_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_at_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_at_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_at_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



--
http://www.freelists.org/webpage/oracle-l