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

  • From: sundar mahadevan <sundarmahadevan82@xxxxxxxxx>
  • To: "D'Hooge Freek" <Freek.DHooge@xxxxxxxxx>, "Oracle-L@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 10 Aug 2011 14:18:05 -0400

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#CHDGBCGIto
>  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: