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

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 11 Aug 2011 16:20:02 +0100
Message-ID: <CABe10sYq1ky1BCaYsnL+2jHQ4PtYxWuZM3ZFSxe=FEFWUuQ7HA_at_mail.gmail.com>

nb you'll also want to make a similar (mirror image) change on the standby (for when the roles are reversed. )

On Thu, Aug 11, 2011 at 3:13 PM, sundar mahadevan < sundarmahadevan82_at_gmail.com> wrote:

> 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_at_gmail.com> 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_at_gmail.com> 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_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#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_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
>>>>
>>>
>>>
>>
>>
>> --
>> Niall Litchfield
>> Oracle DBA
>> http://www.orawin.info
>>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

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

Received on Thu Aug 11 2011 - 10:20:02 CDT