search

embulk / embulk-input-jdbc

MySQL, PostgreSQL, Redshift and generic JDBC input plugins for Embulk
Other
102 stars 74 forks source link

java.lang.RuntimeException: java.sql.SQLException: ORA-01555 #176

Closed OmkarPathak closed 4 years ago

OmkarPathak commented 4 years ago

Hello team,

We are trying to load a table with size of almost 400 GB using embulk-input-oracle to load from Oracle DB and embulk-output-bigquery to load into Bigquery. We are getting below error while performing this task:

org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 61 with name "XYZ$" too small

Anyone has the idea of why this error is generated? Is there anything we can do to fix this?

hiroyuki-sato commented 4 years ago

Hello, @OmkarPathak

Could you tell us the following?

I'm not familiar embulk-input-oracle. It seems that the error code ORA-01555 is related to rollback.

I think you need to minimize the problem.

out:
  type: "null" # require quote
OmkarPathak commented 4 years ago

Hi @hiroyuki-sato,

Embulk Version: 0.9.22 OS: RHEL 7

I am using following configuration:

in:
  type: oracle
  driver_path: /ojdbc7-12.1.0.2.jar
  url: jdbc:oracle:thin:@something.com:1526/DB
  user: user
  password: "password"
  query: "SELECT * SCHEMA.TABLE"
  fetch_rows: 4000
  connect_timeout: 100
out:
   type: bigquery
   mode: replace
   auth_method: service_account
   project: project
   dataset: "dataset"
   table: "table"
   location: europe-west2
   json_keyfile: credentials.json

Full Stack trace:

org.embulk.exec.PartialExecutionException: java.lang.RuntimeException: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 61 with name "XYZ$" too small

        at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
        at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:566)
        at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:35)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
        at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
        at org.embulk.spi.Exec.doWith(Exec.java:22)
        at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
        at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:242)
        at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:290)
        at org.embulk.EmbulkRunner.run(EmbulkRunner.java:154)
        at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:431)
        at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:90)
        at org.embulk.cli.Main.main(Main.java:64)
Caused by: java.lang.RuntimeException: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 61 with name "XYZ$" too small
hito4t commented 4 years ago

Hi @OmkarPathak ,

Important information might be contained after Caused by: .... Would you show the full log after Caused by: ...?

OmkarPathak commented 4 years ago

@hito4t its same mentioned above. Anyways I'm attaching the full log after Caused by:

Caused by: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 61 with name "XYZ$" too small

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
        at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1066)
        at oracle.jdbc.driver.OracleStatement.fetchMoreRows(OracleStatement.java:3716)
        at oracle.jdbc.driver.InsensitiveScrollableResultSet.fetchMoreRows(InsensitiveScrollableResultSet.java:1015)
        at oracle.jdbc.driver.InsensitiveScrollableResultSet.absoluteInternal(InsensitiveScrollableResultSet.java:979)
        at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:579)
        at org.embulk.input.jdbc.AbstractJdbcInputPlugin.fetch(AbstractJdbcInputPlugin.java:608)
        at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:497)
        at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor.runInputTask(LocalExecutorPlugin.java:269)
        at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor.access$100(LocalExecutorPlugin.java:194)
        at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor$1.call(LocalExecutorPlugin.java:233)
        at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor$1.call(LocalExecutorPlugin.java:230)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

Error: java.lang.RuntimeException: java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 61 with name "XYZ$" too small
hito4t commented 4 years ago

@OmkarPathak Thank you!

As the error message says, it seems that the rollback segment is too small for the select statement.

You might be able to avoid the error by the following action. http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm

Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.
zhouchen0401 commented 2 months ago

Hi @OmkarPathak
I think there is another possibilty, The data in this table is being modified while you are conducting a large number of queries