Data transfer from Oracle to Incremental + Append mode fails

[ParkHeedong]

• Is this your first time deploying Airbyte?: Yes
• OS Version / Instance: Amazon Linux 16Core
• Memory : 128GB
• Deployment: docker
• Airbyte Version: 0.50.30
• Source name/ Connector version: Oracle / 0.3.23
• Destination name/version: S3 / 0.5.1
• Replication frequency: Manual
• Step: We are transferring Oracle's data to S3 via Airbyte. Synchronization mode was tested in Incremental + Append mode, and after loading all data to S3, no work has been done in the second synchronization. No failure errors are seen and no logs have been seen since the phrase 'Preparing query for table: INVITEMS'. May I know why you're doing this?

2023-11-21 00:17:25 DEBUG i.a.c.h.LogClientSingleton(setJobMdc):190 - Setting docker job mdc
2023-11-21 00:17:26 INFO i.a.w.t.TemporalAttemptExecution(get):124 - Docker volume job log path: /tmp/workspace/649/0/logs.log
2023-11-21 00:17:26 INFO i.a.w.t.TemporalAttemptExecution(get):129 - Executing worker wrapper. Airbyte version: 0.50.30
2023-11-21 00:17:26 INFO i.a.a.c.AirbyteApiClient(retryWithJitterThrows):290 - Attempt 0 to save workflow id for cancellation
2023-11-21 00:17:26 INFO i.a.w.g.BufferedReplicationWorker(run):146 - start sync worker. job id: 649 attempt id: 0
2023-11-21 00:17:26 INFO i.a.c.i.LineGobbler(voidCall):149 - 
2023-11-21 00:17:26 INFO i.a.c.i.LineGobbler(voidCall):149 - ----- START REPLICATION -----
2023-11-21 00:17:26 INFO i.a.c.i.LineGobbler(voidCall):149 - 
2023-11-21 00:17:26 INFO i.a.w.i.DefaultAirbyteDestination(start):88 - Running destination...
2023-11-21 00:17:26 DEBUG i.a.c.f.EnvVariableFeatureFlags(getEnvOrDefault):83 - Using default value for environment variable APPLY_FIELD_SELECTION: 'false'
2023-11-21 00:17:26 DEBUG i.a.c.f.EnvVariableFeatureFlags(getEnvOrDefault):83 - Using default value for environment variable FIELD_SELECTION_WORKSPACES: ''
2023-11-21 00:17:26 DEBUG i.a.c.f.EnvVariableFeatureFlags(getEnvOrDefault):83 - Using default value for environment variable APPLY_FIELD_SELECTION: 'false'
2023-11-21 00:17:26 DEBUG i.a.c.f.EnvVariableFeatureFlags(getEnvOrDefault):83 - Using default value for environment variable FIELD_SELECTION_WORKSPACES: ''
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable SIDECAR_KUBE_CPU_LIMIT: '2.0'
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable SIDECAR_KUBE_CPU_LIMIT: '2.0'
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable SOCAT_KUBE_CPU_LIMIT: '2.0'
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable SOCAT_KUBE_CPU_LIMIT: '2.0'
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable SIDECAR_KUBE_CPU_REQUEST: '0.1'
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable SIDECAR_KUBE_CPU_REQUEST: '0.1'
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable SOCAT_KUBE_CPU_REQUEST: '0.1'
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable SOCAT_KUBE_CPU_REQUEST: '0.1'
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable LAUNCHDARKLY_KEY: ''
2023-11-21 00:17:26 INFO i.a.c.EnvConfigs(getEnvOrDefault):1228 - Using default value for environment variable LAUNCHDARKLY_KEY: ''
2023-11-21 00:17:26 INFO i.a.c.i.LineGobbler(voidCall):149 - Checking if airbyte/source-oracle:0.3.23 exists...
2023-11-21 00:17:26 INFO i.a.c.i.LineGobbler(voidCall):149 - Checking if airbyte/destination-s3:0.5.1 exists...
2023-11-21 00:17:26 INFO i.a.c.i.LineGobbler(voidCall):149 - airbyte/destination-s3:0.5.1 was found locally.
2023-11-21 00:17:26 INFO i.a.c.i.LineGobbler(voidCall):149 - airbyte/source-oracle:0.3.23 was found locally.
2023-11-21 00:17:26 INFO i.a.w.p.DockerProcessFactory(create):143 - Creating docker container = destination-s3-write-649-0-idvib with resources io.airbyte.config.ResourceRequirements@1541a1d3[cpuRequest=1,cpuLimit=2,memoryRequest=1Gi,memoryLimit=2Gi,additionalProperties={}] and allowedHosts null
2023-11-21 00:17:26 INFO i.a.w.p.DockerProcessFactory(create):143 - Creating docker container = source-oracle-read-649-0-pwkos with resources io.airbyte.config.ResourceRequirements@37fdbfd8[cpuRequest=1,cpuLimit=2,memoryRequest=1Gi,memoryLimit=2Gi,additionalProperties={}] and allowedHosts io.airbyte.config.AllowedHosts@7439c517[hosts=[193.123.240.5, *.datadoghq.com, *.datadoghq.eu, *.sentry.io],additionalProperties={}]
2023-11-21 00:17:26 INFO i.a.w.p.DockerProcessFactory(create):196 - Preparing command: docker run --rm --init -i -w /data/649/0 --log-driver none --name destination-s3-write-649-0-idvib --network host -v airbyte_workspace:/data -v /tmp/airbyte_local:/local -e DEPLOYMENT_MODE=OSS -e WORKER_CONNECTOR_IMAGE=airbyte/destination-s3:0.5.1 -e AUTO_DETECT_SCHEMA=true -e LAUNCHDARKLY_KEY= -e SOCAT_KUBE_CPU_REQUEST=0.1 -e SOCAT_KUBE_CPU_LIMIT=2.0 -e USE_STREAM_CAPABLE_STATE=true -e FIELD_SELECTION_WORKSPACES= -e WORKER_ENVIRONMENT=DOCKER -e AIRBYTE_ROLE= -e APPLY_FIELD_SELECTION=false -e WORKER_JOB_ATTEMPT=0 -e OTEL_COLLECTOR_ENDPOINT=http://host.docker.internal:4317 -e FEATURE_FLAG_CLIENT=config -e AIRBYTE_VERSION=0.50.30 -e WORKER_JOB_ID=649 --cpus=2 --memory-reservation=1Gi --memory=2Gi airbyte/destination-s3:0.5.1 write --config destination_config.json --catalog destination_catalog.json
2023-11-21 00:17:26 INFO i.a.w.p.DockerProcessFactory(create):196 - Preparing command: docker run --rm --init -i -w /data/649/0 --log-driver none --name source-oracle-read-649-0-pwkos -e CONCURRENT_SOURCE_STREAM_READ=false --network host -v airbyte_workspace:/data -v /tmp/airbyte_local:/local -e DEPLOYMENT_MODE=OSS -e WORKER_CONNECTOR_IMAGE=airbyte/source-oracle:0.3.23 -e AUTO_DETECT_SCHEMA=true -e LAUNCHDARKLY_KEY= -e SOCAT_KUBE_CPU_REQUEST=0.1 -e SOCAT_KUBE_CPU_LIMIT=2.0 -e USE_STREAM_CAPABLE_STATE=true -e FIELD_SELECTION_WORKSPACES= -e WORKER_ENVIRONMENT=DOCKER -e AIRBYTE_ROLE= -e APPLY_FIELD_SELECTION=false -e WORKER_JOB_ATTEMPT=0 -e OTEL_COLLECTOR_ENDPOINT=http://host.docker.internal:4317 -e FEATURE_FLAG_CLIENT=config -e AIRBYTE_VERSION=0.50.30 -e WORKER_JOB_ID=649 --cpus=2 --memory-reservation=1Gi --memory=2Gi airbyte/source-oracle:0.3.23 read --config source_config.json --catalog source_catalog.json --state input_state.json
2023-11-21 00:17:26 INFO i.a.w.i.VersionedAirbyteMessageBufferedWriterFactory(createWriter):41 - Writing messages to protocol version 0.2.0
2023-11-21 00:17:26 INFO i.a.w.i.VersionedAirbyteStreamFactory(create):177 - Reading messages from protocol version 0.2.0
2023-11-21 00:17:26 INFO i.a.w.i.VersionedAirbyteStreamFactory(create):177 - Reading messages from protocol version 0.2.0
2023-11-21 00:17:26 INFO i.a.w.g.BufferedReplicationWorker(readFromSource):328 - readFromSource: start
2023-11-21 00:17:26 INFO i.a.w.g.BufferedReplicationWorker(writeToDestination):405 - writeToDestination: start
2023-11-21 00:17:26 INFO i.a.w.i.HeartbeatTimeoutChaperone(runWithHeartbeatThread):98 - Starting source heartbeat check. Will check every 1 minutes.
2023-11-21 00:17:26 INFO i.a.w.g.BufferedReplicationWorker(readFromDestination):438 - readFromDestination: start
2023-11-21 00:17:26 INFO i.a.w.g.BufferedReplicationWorker(processMessage):364 - processMessage: start
2023-11-21 00:17:27 source > INFO i.a.i.s.o.OracleSource(main):208 starting source: class io.airbyte.integrations.source.oracle.OracleSource
2023-11-21 00:17:27 destination > INFO i.a.i.b.a.AdaptiveDestinationRunner$Runner(getDestination):74 Running destination under deployment mode: OSS
2023-11-21 00:17:27 destination > INFO i.a.i.b.a.AdaptiveDestinationRunner$Runner(run):86 Starting destination: io.airbyte.integrations.destination.s3.S3Destination
2023-11-21 00:17:27 source > INFO i.a.i.b.IntegrationCliParser(parseOptions):126 integration args: {read=null, catalog=source_catalog.json, state=input_state.json, config=source_config.json}
2023-11-21 00:17:27 source > INFO i.a.i.b.IntegrationRunner(runInternal):105 Running integration: io.airbyte.integrations.base.ssh.SshWrappedSource
2023-11-21 00:17:27 source > INFO i.a.i.b.IntegrationRunner(runInternal):106 Command: READ
2023-11-21 00:17:27 source > INFO i.a.i.b.IntegrationRunner(runInternal):107 Integration config: IntegrationConfig{command=READ, configPath='source_config.json', catalogPath='source_catalog.json', statePath='input_state.json'}
2023-11-21 00:17:27 destination > INFO i.a.i.b.IntegrationCliParser(parseOptions):126 integration args: {catalog=destination_catalog.json, write=null, config=destination_config.json}
2023-11-21 00:17:27 destination > INFO i.a.i.b.IntegrationRunner(runInternal):106 Running integration: io.airbyte.integrations.destination.s3.S3Destination
2023-11-21 00:17:27 destination > INFO i.a.i.b.IntegrationRunner(runInternal):107 Command: WRITE
2023-11-21 00:17:27 destination > INFO i.a.i.b.IntegrationRunner(runInternal):108 Integration config: IntegrationConfig{command=WRITE, configPath='destination_config.json', catalogPath='destination_catalog.json', statePath='null'}
2023-11-21 00:17:27 source > WARN c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword order - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2023-11-21 00:17:27 source > WARN c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword airbyte_secret - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2023-11-21 00:17:27 destination > WARN c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword airbyte_secret - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2023-11-21 00:17:27 destination > WARN c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword order - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2023-11-21 00:17:27 destination > WARN c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword requires - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2023-11-21 00:17:27 destination > INFO i.a.i.d.s.S3FormatConfigs(getS3FormatConfig):22 S3 format config: {"flattening":"Root level flattening","compression":{"compression_type":"GZIP"},"format_type":"JSONL"}
2023-11-21 00:17:27 destination > INFO i.a.i.d.s.S3DestinationConfig(createS3Client):239 Creating S3 client...
2023-11-21 00:17:27 source > INFO i.a.i.b.s.SshTunnel(getInstance):204 Starting connection with method: NO_TUNNEL
2023-11-21 00:17:27 source > INFO i.a.i.s.r.s.StateManagerFactory(createStateManager):48 Legacy state manager selected to manage state object with type LEGACY.
2023-11-21 00:17:27 source > INFO i.a.i.s.r.s.CursorManager(createCursorInfoForStream):166 Found matching cursor in state. Stream: FNF_INVITEMS. Cursor Field: UPDATE_DATE Value: 2023-11-16T08:45:54.000000Z Count: 30
2023-11-21 00:17:27 source > INFO i.a.i.s.r.CdcStateManager(<init>):29 Initialized CDC state with: null</init>
2023-11-21 00:17:27 source > INFO c.z.h.HikariDataSource(<init>):80 HikariPool-1 - Starting...</init>
2023-11-21 00:17:27 source > INFO c.z.h.HikariDataSource(<init>):82 HikariPool-1 - Start completed.</init>
2023-11-21 00:17:27 destination > INFO i.a.i.d.s.SerializedBufferFactory(getCreateFunction):47 S3 format config: S3JsonlFormatConfig(flattening=ROOT_LEVEL, compressionType=GZIP)
2023-11-21 00:17:27 destination > INFO i.a.i.d.s.S3ConsumerFactory(lambda$toWriteConfig$0):81 Write config: WriteConfig{streamName=INVITEMS, namespace=FNF, outputBucketPath=cdc/, pathFormat=cdc//${NAMESPACE}/${STREAM_NAME}/${YEAR}_${MONTH}_${DAY}_${EPOCH}_, fullOutputPath=cdc/FNF/INVITEMS/2023_11_21_1700525847523_, syncMode=append}
2023-11-21 00:17:27 destination > INFO i.a.i.d.b.BufferedStreamConsumer(startTracked):144 class io.airbyte.integrations.destination.buffered_stream_consumer.BufferedStreamConsumer started.
2023-11-21 00:17:27 destination > INFO i.a.i.d.s.S3ConsumerFactory(lambda$onStartFunction$1):88 Preparing bucket in destination started for 1 streams
2023-11-21 00:17:27 destination > INFO i.a.i.d.s.S3ConsumerFactory(lambda$onStartFunction$1):102 Preparing storage area in destination completed.
2023-11-21 00:17:58 source > Nov 21, 2023 12:17:58 AM oracle.simplefan.impl.FanManager configure
2023-11-21 00:17:58 source > SEVERE: attempt to configure ONS in FanManager failed with oracle.ons.NoServersAvailable: Subscription time out
2023-11-21 00:17:58 source > INFO i.a.i.s.j.AbstractJdbcSource(discoverInternal):189 Internal schemas to exclude: []
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table INVOICES column BRAND (type VARCHAR2[2], nullable false) -> JsonSchemaType({type=string})
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table INVOICES column SEASON (type VARCHAR2[4], nullable false) -> JsonSchemaType({type=string})
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table INVOICES column SORDERNUM (type VARCHAR2[18], nullable false) -> JsonSchemaType({type=string})
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table INVOICES column INVOICENUM (type VARCHAR2[20], nullable false) -> JsonSchemaType({type=string})
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table INVOICES column SHOPCODE (type VARCHAR2[8], nullable false) -> JsonSchemaType({type=string})
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table INVOICES column SHIPTO (type VARCHAR2[8], nullable false) -> JsonSchemaType({type=string})
.
.
.
.
.
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table HR_ORG column ADD_LINE_V (type NUMBER[6], nullable false) -> JsonSchemaType({type=number})
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table HR_ORG column NAME_ENG (type VARCHAR2[50], nullable false) -> JsonSchemaType({type=string})
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table HR_ORG column UP_DISNAME_YN (type VARCHAR2[1], nullable false) -> JsonSchemaType({type=string})
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$discoverInternal$5):211 Table HR_ORG column NAME_SHORT (type VARCHAR2[40], nullable false) -> JsonSchemaType({type=string})
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(queryTableIncremental):340 Queueing query for table: INVITEMS
2023-11-21 00:18:00 source > INFO i.a.i.s.j.AbstractJdbcSource(lambda$queryTableIncremental$18):345 Preparing query for table: INVITEMS

[marcosmarxm]

@ParkHeedong Did you try to break the connection into large and small tables?

[ParkHeedong]

@marcosmarxm I'm sorry, but I didn't understand what you said. Could you explain it in detail again?
into a single connection Only one table was in progress.

[sajarin]

hey @ParkHeedong

Marcos's suggestion was to maybe try making this sync smaller somehow. One way to do that would be to split this pipeline into multiple Airbyte connections but doesn't seem like that would help since you're only syncing one table.

On another note, this error stood out to me:
2023-11-21 00:17:58 source > SEVERE: attempt to configure ONS in FanManager failed with oracle.ons.NoServersAvailable: Subscription time out

This may indicate that there are network connectivity issues, which may explain why the sync is hanging on the INVITEMS query.