ORA-00942 when analyzing sequences of Oracle Database

[jgarcia-desic]

📝 Description

In the "analyze" phase, when analyzing sequences, we get the following error:

Error: ORA-00942: table or view does not exist at async _0x27becb.query (/app/index.js:5:343009) at async _0x27becb.handleQuery (/app/index.js:5:343575) at async /app/index.js:5:334131 at async _0x27becb.handleListener (/app/index.js:5:344512) at async _0x27becb.getSequences (/app/index.js:5:334047) at async Promise.all (index 11) at async _0x27becb.retrieveSystemCatalog (/app/index.js:5:310078) at async _0x788c2f.readMetaData (/app/index.js:5:294539) at async _0xa33ccf.runJob (/app/index.js:5:292644)

and we are not able to continue.

Tested on Oracle Database versions 11.2.0.3, 11.2.0.4 and 10.2.0.4 with different schemas in each of them.

📜 Steps to reproduce

1. Conection succesful both to souce and destination database (Oracle 11.2.0.3, 11.2.0.4 or 10.2.0.4 )
2. Select only one schema to migrate
3. Get the error on the "analyze" phase.

🙁 Actual behavior

We get the error:

Error: ORA-00942: table or view does not exist at async _0x27becb.query (/app/index.js:5:343009) at async _0x27becb.handleQuery (/app/index.js:5:343575) at async /app/index.js:5:334131 at async _0x27becb.handleListener (/app/index.js:5:344512) at async _0x27becb.getSequences (/app/index.js:5:334047) at async Promise.all (index 11) at async _0x27becb.retrieveSystemCatalog (/app/index.js:5:310078) at async _0x788c2f.readMetaData (/app/index.js:5:294539) at async _0xa33ccf.runJob (/app/index.js:5:292644)

and we are not able to continue.

🙂 Expected behavior

Get a full analysis of the source schema to select and fix the issues.

Migrator version

3.9.0

In which environment are you operating?

• [X] Linux
• [ ] macOS
• [ ] Windows
• [ ] Other

What browser are you using

Firefox

Other

No response

[KieranKaelin]

Thanks for reporting the bug!

When querying sequence information from the Oracle system catalog, we use the following statement:

   SELECT s.sequence_owner "schemaName",
          s.sequence_name "sequenceName",
          s.min_value "minValue",
          s.max_value "maxValue",
          s.increment_by "incrementBy",
          CASE WHEN s.cycle_flag = 'Y' THEN 1 ELSE 0 END "cyclical",
          GREATEST(s.cache_size, 1) "cacheSize",
          s.last_number "lastValue"
     FROM dba_sequences s
     LEFT JOIN dba_tab_identity_cols id ON s.sequence_owner = id.owner AND s.sequence_name = id.sequence_name
    WHERE id.sequence_name IS NULL
      AND s.sequence_owner IN ('<schema_name>') -- Enter your schema name here
    ORDER BY s.sequence_owner, s.sequence_name;

The likely culprit is dba_tab_identity_cols, which seems to have been introduced with Oracle 12. Could you confirm this by testing the query on your system?

If this truly is the cause, we will create a patch and include it in the hotfix v3.10.1, scheduled for release tomorrow (2022-09-08).

[jgarcia-desic]

Tested the query on Oracle 11.2.0.3, 11.2.0.4 and 10.2.0.4 and yes, "dba_tab_identity_cols" is marked as the non-existent object. Thanks for the patch. I'm afraid I won't be able to upgrade from my 3.9 standard edition for which I need a download link. Thank you!

[KieranKaelin]

Perfect, thanks for the quick answer. We will incorporate the patch into the hot-fix, and I will post the link to the download location of the v3.10.1-standard edition here tomorrow.

[mh182]

We provide the download link for the newest Standard Edition in our CYBERTEC Migrator newsletter. So if you registered to the newsletter (when you downloaded the Standard Edition) you will get access to the release with the hot-fix in a couple days.

[KieranKaelin]

We released v3.10.1 yesterday, which may be downloaded by filling our download form anew. Please follow the Offline upgrade guide in our Upgrades section, and let us know if the hot-fix resolves your issue.

[jgarcia-desic]

Thanks for the quick fix. I had to use a different email for the download because of the "one mail - one link" rule. Unfortunately after upgrading I'm getting another error analyzing sequences in the Oracle Database:

Error: ORA-00904: "COL"."DEFAULT_ON_NULL": identificador no válido at async _0x519a65.query (/app/index.js:5:349249) at async _0x519a65.handleQuery (/app/index.js:5:349813) at async /app/index.js:5:316719 at async _0x519a65.handleListener (/app/index.js:5:350755) at async _0x519a65.getColumns (/app/index.js:5:314374) at async Promise.all (index 1) at async _0x519a65.retrieveSystemCatalog (/app/index.js:5:312447) at async _0x3bf391.readMetaData (/app/index.js:5:296206) at async _0x5199a3.runJob (/app/index.js:5:294303)

Thank you

[jgarcia-desic]

The ORA-00904 error was reported on 11.2.0.4, 11.2.0.3 and 10.2.0.4.

Besides, on 10.2.0.4 analyzing a different schema I get this error when dealing with triggers:

Error: ORA-00920: invalid relational operator at async _0x519a65.query (/app/index.js:5:349249) at async _0x519a65.handleQuery (/app/index.js:5:349813) at async /app/index.js:5:342070 at async _0x519a65.handleListener (/app/index.js:5:350755) at async _0x519a65.getTriggers (/app/index.js:5:341945) at async Promise.all (index 15) at async _0x519a65.retrieveSystemCatalog (/app/index.js:5:312447) at async _0x3bf391.readMetaData (/app/index.js:5:296206) at async _0x5199a3.runJob (/app/index.js:5:294303)

Thanks

[mh182]

We currently support/test the following Oracle versions in our CI build: 12.2.0.1, 18.4.0, 19.3.0. (with 21.3 on our TODO list).

I think we are only going to be able to provide support Oracle versions older than 12.2 if we enhance our CI build with those versions. Our main difficulty is to get hands on those Oracle with a license which allows us to run the tests in a CI build (.i.e. an XE version with no costs).

Any suggestions from your side how we could get hands on Oracle 10.2 and 11.2.0.4 so we could integrate it into our CI build?

[jgarcia-desic]

I'm by no means an expert on Oracle product licensing, but I believe that if you have current support for Oracle Database for a given server, you are able to install any version, including the old ones available by request. Because Oracle licences their products by machine I guess you can install any number of databases/oracle_homes if your hardware can handle it.

So, if you have an up to date license for, lets say, 19c for a server you could, in theory, install any number of versions of Oracle Database (like 10g or 11g) in the same server but different oracle_homes.

I think that supporting Migrator for older releases of Oracle Database is important as many clients are looking alternative databases like PostgreSQL, specially those with standard edition who are stuck in 10g or 11g due to licesing changes/limitations when Oracle introduced SE2 with RAC not supported for 19c onwards.

Thanks. Thank you

[mh182]

We are looking into the prioritization for Oracle 11 and 10 and will come back to you as soon we know a timeline.

[mh182]

Fixed with v3.11.0 released on 2022-09-28.

We added Oracle 11 in our CI build and adapted the queries to read the meta-data so they would work. I'm not sure if this also fixes the problems with Oracle 10.

[jgarcia-desic]

Thanks for adding 11g to your CI build.

How do I get version v3.11.0 standard? I've already used the one-time per email download link. Even so, I'm not sure the download link points to the latest version.

Thank you

[mh182]

When downloaded the standard edition the first tim you could subscribe to the migrator newsletter. The newsletter is sent out on a monthly basis and contains the download link to the newest release. The newsletter for 3.11 should go out in the next days.