Re: logical standby real time apply does not work unless manually switching logfile
- From: sundar mahadevan <sundarmahadevan82@xxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Thu, 11 Aug 2011 10:13:35 -0400
Hi Niall,
You were spot on.
Changing my LOG_ARCHIVE_DEST_3 on primary from
alter system set LOG_ARCHIVE_DEST_3='SERVICE=
mfprod_logstdby.theglobeandmail.com *ARCH *ASYNC
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=MFPRODLS';
to
alter system set LOG_ARCHIVE_DEST_3='SERVICE=
mfprod_logstdby.theglobeandmail.com *LGWR *ASYNC
VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=MFPRODLS';
did the trick.
This even worked with the same number of standby redo logs rather than the
redo log groups +1. Special thanks to Remigiusz, Jack C. Applewhite, Freek
and this group for the help.
On Thu, Aug 11, 2011 at 4:13 AM, Niall Litchfield <
niall.litchfield@xxxxxxxxx> wrote:
> As Remigiuz points out the issue is not particularly logical standby
> related but rather log shipping/archival related. The relevant documentation
> for your version is at
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#i1268542
> -
> clearly the standby can only apply what it has actually received. In order
> to ship in real time then you need to ensure transport is done by the LGWR
> process, it's likely however that you don't want to *unnecessarily *slow
> the LGWR process on the primary so you'll probably want to
> specify asynchronous transmission of the redo stream to the standby. You
> can read all about the (large number of) parameters at
> http://download.oracle.com/docs/cd/B13789_01/server.101/b10823/log_arch_dest_param.htm
> .
> You'll also likely want to consider the effect of network timeouts
> (NET_TIMEOUT in the dest parameter and SQLNET.EXPIRE_TIME at the standby)
>
> I suspect you want something along the lines of
>
> log_archive_dest_3 = 'SERVICE=mfprod_logstdby LGWR ASYNC NET_TIMEOUT=60
> VALID_FOR=(ONLINE_LOGFILE, ALL_ROLES) DB_UNIQUE_NAME=MFPRODLS'
>
> If you configure Data Guard broker then the management interface is
> somewhat nicer.
>
> On Wed, Aug 10, 2011 at 6:23 PM, sundar mahadevan <
> sundarmahadevan82@xxxxxxxxx> wrote:
>
>> 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
>>>
>>
>>
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
Other related posts:
