Re: Q: far-sync gap
- From: amihay gonen <agonenil@xxxxxxxxx>
- To: Ludovico Caldara <ludovico.caldara@xxxxxxxxx>
- Date: Wed, 29 Oct 2014 10:33:11 +0200
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@xxxxxxxxx> 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@xxxxxxxxx>:
>
>> 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@xxxxxxxxx> 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@xxxxxxxxx> wrote:
>>>
>>>> Hi, Are you using sync or fast sync for the redo transport?
>>>> Il 28-ott-2014 15:21 "amihay gonen" <agonenil@xxxxxxxxx> 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@ldb1 <<EOF
>>>>> set head off
>>>>> select 'ldb1:'||current_scn||','||CONTROLFILE_CHANGE# from v\$database;
>>>>> EOF
>>>>> sqlplus -l -s sys/a@ldb1fs as sysdba<<EOF
>>>>> set head off
>>>>> select 'ldb1fs:'||current_scn||','||CONTROLFILE_CHANGE# from
>>>>> v\$database;
>>>>> EOF
>>>>> sqlplus -l -s a/a@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
>>>>>
>>>>>
>>>
>>
>
Other related posts:
