Re: Q: far-sync gap
Date: Mon, 3 Nov 2014 15:03:13 +0200
Message-ID: <CAKb+SBWU-GynuisCNW13_BLgRUQGvmPOtNP7xTnC4Wc0Fe7sBA_at_mail.gmail.com>
Hi , I've product a report and put it here -->http://pastebin.com/4Mvpx2m4
I think that if we stop (or restarting) the farsync then LNS process on the FS is not started and the way to push data is to failover to standby.
On Wed, Oct 29, 2014 at 10:33 AM, amihay gonen <agonenil_at_gmail.com> wrote:
> Thanks for the input . you are correct , as you can bellow :
>
> although I still don't understand ,why in the following scenario , I get
> data gap:
>
> 1. stop standby.
> 2. put data into primary
> 3. stop primary abort
> 4. restart FS
> 5. start standby .
> 6. compare count on some test table - i see difference .
>
> anyhow , I'll repeat my test using the new query bellow and report to the
> oracle-l list
>
>
> Every 2.0s: ./doquery.sh
>
> Wed Oct 29 02:31:57 2014
>
>
> ldb1 LNS 114 356
>
> ldb1fs RFS 0 0
> ldb1fs RFS 0 0
> ldb1fs RFS 114 356
> ldb1fs RFS 0 0
> ldb1fs LNS 114 356
>
> ldb1sb RFS 0 0
> ldb1sb RFS 114 356
> ldb1sb RFS 0 0
>
>
> set timing off feedback off head off
> select INSTANCE_NAME,process,SEQUENCE#,BLOCK# from v$instance
> ,v$managed_standby where process in ('LNS','RFS');
> exit
>
>
> On Wed, Oct 29, 2014 at 9:40 AM, Ludovico Caldara <
> ludovico.caldara_at_gmail.com> wrote:
>
>> Hi Amihay, I put back the list as recipient, yesterday I've done a reply
>> instead a reply to all using my smartphone...
>>
>> No, indeed, SYNC should be better.
>> The problem is that you're checking the RECOVERY LAG and not the
>> TRANSPORT LAG,
>> SYNC means that the transport is synchronous but you can still have a
>> small recovery lag. A commit on the primary forces a write of the standby
>> log on the standby site but doesn't force the recovery process (MRP0) to
>> apply it on the standby. So this is the problem.
>>
>> You should check instead the sequence# and block# of the RFS processes in
>> the v$managed_standby view. There you should notice no lag if the transport
>> is sync.
>>
>> Best regards
>> --
>> Ludo
>>
>> 2014-10-29 2:08 GMT+01:00 amihay gonen <agonenil_at_gmail.com>:
>>
>>> setting to fastsync doesn't seems to do any difference ...
>>>
>>> DGMGRL> show database verbose ldb1
>>>
>>> Database - ldb1
>>>
>>> Role: PRIMARY
>>> Intended State: TRANSPORT-ON
>>> Instance(s):
>>> ldb1
>>>
>>> Properties:
>>> DGConnectIdentifier = 'ldb1'
>>> ObserverConnectIdentifier = ''
>>> LogXptMode = 'SYNC'
>>> RedoRoutes = '(LOCAL : ldb1fs FASTSYNC)'
>>> DelayMins = '0'
>>> Binding = 'optional'
>>>
>>>
>>> On Wed, Oct 29, 2014 at 2:44 AM, amihay gonen <agonenil_at_gmail.com>
>>> wrote:
>>>
>>>> hi , I'm not sure , but I think I'm using SYNC .
>>>>
>>>> My main problem is that it seems the FS is not sync.
>>>>
>>>> For example :
>>>> the following test:
>>>> 1. stop standby db.
>>>> 2. put data into primary.(commit)
>>>> 3. shutdown abort primary.
>>>> 4. restart FS
>>>> 5. start standby db.
>>>>
>>>> after step 5 - it seems that the standby doesn't completely close the
>>>> GAP .
>>>>
>>>> when I start the Primary again the gap is closed .
>>>>
>>>>
>>>>
>>>>
>>>> here the output of dgmlgr command and oracle configuration :
>>>>
>>>> DGMGRL> show database verbose ldb1
>>>>
>>>> Database - ldb1
>>>>
>>>> Role: PRIMARY
>>>> Intended State: TRANSPORT-ON
>>>> Instance(s):
>>>> ldb1
>>>>
>>>> Properties:
>>>> DGConnectIdentifier = 'ldb1'
>>>> ObserverConnectIdentifier = ''
>>>> LogXptMode = 'SYNC'
>>>> RedoRoutes = '(LOCAL : ldb1fs SYNC)'
>>>> DelayMins = '0'
>>>> Binding = 'optional'
>>>> MaxFailure = '0'
>>>> MaxConnections = '1'
>>>> ReopenSecs = '300'
>>>> NetTimeout = '30'
>>>> RedoCompression = 'DISABLE'
>>>> LogShipping = 'ON'
>>>> PreferredApplyInstance = ''
>>>> ApplyInstanceTimeout = '0'
>>>> ApplyLagThreshold = '0'
>>>> TransportLagThreshold = '0'
>>>> TransportDisconnectedThreshold = '30'
>>>> ApplyParallel = 'AUTO'
>>>> StandbyFileManagement = 'AUTO'
>>>> ArchiveLagTarget = '0'
>>>> LogArchiveMaxProcesses = '4'
>>>> LogArchiveMinSucceedDest = '1'
>>>> DbFileNameConvert = 'ldb1, ldb1sb'
>>>> LogFileNameConvert = 'ldb1, ldb1sb'
>>>> FastStartFailoverTarget = ''
>>>> InconsistentProperties = '(monitor)'
>>>> InconsistentLogXptProps = '(monitor)'
>>>> SendQEntries = '(monitor)'
>>>> LogXptStatus = '(monitor)'
>>>> RecvQEntries = '(monitor)'
>>>> StaticConnectIdentifier =
>>>> '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.11)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ldb1_DGMGRL)(INSTANCE_NAME=ldb1)(SERVER=DEDICATED)))'
>>>> StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
>>>> AlternateLocation = ''
>>>> LogArchiveTrace = '0'
>>>> LogArchiveFormat = '%t_%s_%r.arc'
>>>> TopWaitEvents = '(monitor)'
>>>>
>>>> Database Status:
>>>> SUCCESS
>>>>
>>>>
>>>> Far Sync Instance - ldb1fs
>>>>
>>>> Transport Lag: 0 seconds (computed 1 second ago)
>>>> Instance(s):
>>>> ldb1fs
>>>>
>>>> Properties:
>>>> DGConnectIdentifier = 'ldb1fs'
>>>> LogXptMode = 'ASYNC'
>>>> RedoRoutes = '(ldb1 : ldb1sb ASYNC)'
>>>> Binding = 'optional'
>>>> MaxFailure = '0'
>>>> MaxConnections = '1'
>>>> ReopenSecs = '300'
>>>> NetTimeout = '30'
>>>> RedoCompression = 'DISABLE'
>>>> LogShipping = 'ON'
>>>> TransportLagThreshold = '0'
>>>> TransportDisconnectedThreshold = '30'
>>>> LogArchiveMaxProcesses = '4'
>>>> LogArchiveMinSucceedDest = '1'
>>>> LogFileNameConvert = 'ldb1, ldb1fs'
>>>> InconsistentProperties = '(monitor)'
>>>> InconsistentLogXptProps = '(monitor)'
>>>> LogXptStatus = '(monitor)'
>>>> StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
>>>> AlternateLocation = ''
>>>> LogArchiveTrace = '0'
>>>> LogArchiveFormat = '%t_%s_%r.arc'
>>>> TopWaitEvents = '(monitor)'
>>>>
>>>> Far Sync Instance Status:
>>>> SUCCESS
>>>>
>>>>
>>>> DGMGRL> show database verbose ldb1sb
>>>>
>>>> Database - ldb1sb
>>>>
>>>> Role: PHYSICAL STANDBY
>>>> Intended State: APPLY-ON
>>>> Transport Lag: 0 seconds (computed 1 second ago)
>>>> Apply Lag: 0 seconds (computed 1 second ago)
>>>> Average Apply Rate: 3.00 KByte/s
>>>> Active Apply Rate: 1.38 MByte/s
>>>> Maximum Apply Rate: 5.11 MByte/s
>>>> Real Time Query: ON
>>>> Instance(s):
>>>> ldb1sb
>>>>
>>>> Properties:
>>>> DGConnectIdentifier = 'ldb1sb'
>>>> ObserverConnectIdentifier = ''
>>>> LogXptMode = 'ASYNC'
>>>> RedoRoutes = ''
>>>> DelayMins = '0'
>>>> Binding = 'optional'
>>>> MaxFailure = '0'
>>>> MaxConnections = '1'
>>>> ReopenSecs = '300'
>>>> NetTimeout = '30'
>>>> RedoCompression = 'DISABLE'
>>>> LogShipping = 'ON'
>>>> PreferredApplyInstance = ''
>>>> ApplyInstanceTimeout = '0'
>>>> ApplyLagThreshold = '0'
>>>> TransportLagThreshold = '0'
>>>> TransportDisconnectedThreshold = '30'
>>>> ApplyParallel = 'AUTO'
>>>> StandbyFileManagement = 'AUTO'
>>>> ArchiveLagTarget = '0'
>>>> LogArchiveMaxProcesses = '4'
>>>> LogArchiveMinSucceedDest = '1'
>>>> DbFileNameConvert = 'ldb1, ldb1sb'
>>>> LogFileNameConvert = 'ldb1, ldb1sb'
>>>> FastStartFailoverTarget = ''
>>>> InconsistentProperties = '(monitor)'
>>>> InconsistentLogXptProps = '(monitor)'
>>>> SendQEntries = '(monitor)'
>>>> LogXptStatus = '(monitor)'
>>>> RecvQEntries = '(monitor)'
>>>> StaticConnectIdentifier =
>>>> '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ldb1sb_DGMGRL)(INSTANCE_NAME=ldb1sb)(SERVER=DEDICATED)))'
>>>> StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'
>>>> AlternateLocation = ''
>>>> LogArchiveTrace = '0'
>>>> LogArchiveFormat = '%t_%s_%r.arc'
>>>> TopWaitEvents = '(monitor)'
>>>>
>>>> Database Status:
>>>> SUCCESS
>>>>
>>>>
>>>> ----------------------------------- -----------
>>>> ------------------------------
>>>> log_archive_dest string
>>>> log_archive_dest_1 string
>>>> LOCATION=USE_DB_RECOVERY_FILE_
>>>> DEST
>>>> VALID_FOR=(ALL_LOGFILES
>>>> ,ALL_ROLES)
>>>> log_archive_dest_10 string
>>>> log_archive_dest_11 string
>>>> log_archive_dest_12 string
>>>> log_archive_dest_13 string
>>>> log_archive_dest_14 string
>>>> log_archive_dest_15 string
>>>> log_archive_dest_16 string
>>>> log_archive_dest_17 string
>>>> log_archive_dest_18 string
>>>> log_archive_dest_19 string
>>>> log_archive_dest_2 string
>>>> log_archive_dest_20 string
>>>> log_archive_dest_21 string
>>>> log_archive_dest_22 string
>>>> log_archive_dest_23 string
>>>> log_archive_dest_24 string
>>>> log_archive_dest_25 string
>>>> log_archive_dest_26 string
>>>> log_archive_dest_27 string
>>>> log_archive_dest_28 string
>>>> log_archive_dest_29 string
>>>> log_archive_dest_3 string service="ldb1fs", SYNC
>>>> AFFIRM
>>>> delay=0 optional
>>>> compression=d
>>>> isable max_failure=0
>>>> max_conne
>>>> ctions=1 reopen=300
>>>> db_unique_
>>>> name="ldb1fs"
>>>> net_timeout=30,
>>>>
>>>> valid_for=(online_logfile,all_
>>>> roles)
>>>>
>>>>
>>>>
>>>>
>>>> On Tue, Oct 28, 2014 at 8:43 PM, Ludovico Caldara <
>>>> ludovico.caldara_at_gmail.com> wrote:
>>>>
>>>>> Hi, Are you using sync or fast sync for the redo transport?
>>>>> Il 28-ott-2014 15:21 "amihay gonen" <agonenil_at_gmail.com> ha scritto:
>>>>>
>>>>> Hi all ,
>>>>>> I'm testing Farsync and it seems that always a gap in current_scn
>>>>>> between primary and farsync and standby .
>>>>>>
>>>>>> I don't understand why...
>>>>>>
>>>>>>
>>>>>> I've the following configuration :
>>>>>>
>>>>>> DGMGRL> show configuration verbose
>>>>>>
>>>>>> Configuration - dr
>>>>>>
>>>>>> Protection Mode: MaxAvailability
>>>>>> Members:
>>>>>> ldb1 - Primary database
>>>>>> ldb1fs - Far sync instance
>>>>>> ldb1sb - Physical standby database
>>>>>>
>>>>>> Properties:
>>>>>> FastStartFailoverThreshold = '30'
>>>>>> OperationTimeout = '30'
>>>>>> TraceLevel = 'USER'
>>>>>> FastStartFailoverLagLimit = '30'
>>>>>> CommunicationTimeout = '180'
>>>>>> ObserverReconnect = '0'
>>>>>> FastStartFailoverAutoReinstate = 'TRUE'
>>>>>> FastStartFailoverPmyShutdown = 'TRUE'
>>>>>> BystandersFollowRoleChange = 'ALL'
>>>>>> ObserverOverride = 'FALSE'
>>>>>> ExternalDestination1 = ''
>>>>>> ExternalDestination2 = ''
>>>>>> PrimaryLostWriteAction = 'CONTINUE'
>>>>>>
>>>>>> Fast-Start Failover: DISABLED
>>>>>>
>>>>>> Configuration Status:
>>>>>> SUCCESS
>>>>>>
>>>>>> DGMGRL> enable configuration
>>>>>> Enabled.
>>>>>> DGMGRL> show configuration verbose
>>>>>>
>>>>>> Configuration - dr
>>>>>>
>>>>>> Protection Mode: MaxAvailability
>>>>>> Members:
>>>>>> ldb1 - Primary database
>>>>>> ldb1fs - Far sync instance
>>>>>> ldb1sb - Physical standby database
>>>>>>
>>>>>> Properties:
>>>>>> FastStartFailoverThreshold = '30'
>>>>>> OperationTimeout = '30'
>>>>>> TraceLevel = 'USER'
>>>>>> FastStartFailoverLagLimit = '30'
>>>>>> CommunicationTimeout = '180'
>>>>>> ObserverReconnect = '0'
>>>>>> FastStartFailoverAutoReinstate = 'TRUE'
>>>>>> FastStartFailoverPmyShutdown = 'TRUE'
>>>>>> BystandersFollowRoleChange = 'ALL'
>>>>>> ObserverOverride = 'FALSE'
>>>>>> ExternalDestination1 = ''
>>>>>> ExternalDestination2 = ''
>>>>>> PrimaryLostWriteAction = 'CONTINUE'
>>>>>>
>>>>>> Fast-Start Failover: DISABLED
>>>>>>
>>>>>> Configuration Status:
>>>>>> SUCCESS
>>>>>>
>>>>>> DGMGRL>
>>>>>>
>>>>>>
>>>>>> when doing the following queries :
>>>>>>
>>>>>> sqlplus -l -s a/a_at_ldb1 <<EOF
>>>>>> set head off
>>>>>> select 'ldb1:'||current_scn||','||CONTROLFILE_CHANGE# from
>>>>>> v\$database;
>>>>>> EOF
>>>>>> sqlplus -l -s sys/a_at_ldb1fs as sysdba<<EOF
>>>>>> set head off
>>>>>> select 'ldb1fs:'||current_scn||','||CONTROLFILE_CHANGE# from
>>>>>> v\$database;
>>>>>> EOF
>>>>>> sqlplus -l -s a/a_at_ldb1sb <<EOF
>>>>>> set head off
>>>>>> select 'ldb1fs:'||current_scn||','||CONTROLFILE_CHANGE# from
>>>>>> v\$database;
>>>>>> EOF
>>>>>>
>>>>>>
>>>>>> I got the following results
>>>>>>
>>>>>> ldb1:*2275269*,2274999
>>>>>>
>>>>>> Elapsed: 00:00:00.00
>>>>>>
>>>>>> ldb1fs*:2275073*,1800180
>>>>>>
>>>>>> Elapsed: 00:00:00.00
>>>>>>
>>>>>> ldb1fs:*2275267*,2250255
>>>>>>
>>>>>> Elapsed: 00:00:00.01
>>>>>>
>>>>>>
>>>>
>>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 03 2014 - 14:03:13 CET