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 09:13:54 +0100
Message-ID: <CABe10sZkF=dTxnhVPbp6W5aErCJA6aWAFTyESX0r-xLOZVmBLg_at_mail.gmail.com>

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

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

Received on Thu Aug 11 2011 - 03:13:54 CDT