How to snapshot and then compare multiple schemas of an Oracle database

General Discussion
1

Hi

I would like to create a snapshot of my Oracle database containing multiple schemas, so that I can later run a diff between the snapshot and the possibly-changed database.

I am using Liquibase Community 4.4.3 on Linux, Java 8, an Oracle 12c database, and ojdbc8.jar.

I am referencing “Diffing Multiple Schemas in Liquibase”:
https://docs.liquibase.com/workflows/liquibase-community/diffing-multiple-schemas-in-liquibase.html
which says:

  1. Run the snapshot command to capture the state of the database containing different schemas:
liquibase --outputFile=mySnapshot.json snapshot --snapshotFormat=json --schemas=lookup,public

When running the snapshot command on multiple schemas, enter the --schemas flag after the snapshot command.

However when I try it like this:
liquibase --snapshotFormat=json --outputFile=test_snapshot.json --url=jdbc:oracle:thin:@myhostname.local:1521/dbname.local --username=liquibase --password=Password123 snapshot --schemas=SCHEMA1,SCHEMA2

It throws error:

Unexpected argument(s): --schemas=SCHEMA1,SCHEMA2

For detailed help, try ‘liquibase --help’ or ‘liquibase --help’

I am hoping to capture the definition of tables etc from both SCHEMA1 and from SCHEMA2 into file test_snapshot.json, so that I could later on use the diff command against that file and the current database (using a reference URL).
I am not using a liquibase.properties file. All parameters are passed on the command line.
Could anyone point me in the right direction please?

2

Hi @ravenp,

Please try specifiying all attributes before the actual command name. Something like below:

liquibase --snapshotFormat=json --outputFile=test_snapshot.json --url=jdbc:oracle:thin:@myhostname.local:1521/dbname.local --username=liquibase --password=Password123 --schemas=SCHEMA1,SCHEMA2 snapshot

You should see the error gone.

Please let us know the results once you try it.

Thanks,
Rakhi Agrawal

3

Hi Rakhi,

Thanks a lot for your reply. Your suggestion has helped and now I don’t get any error message when running with the command name last. However, the snapshot file doesn’t contain any tables from either of the schemas.

If I include this:
–default-schema-name=SCHEMA1
then I get tables from SCHEMA1 in the snapshot, but nothing from SCHEMA2, and likewise for SCHEMA2.

Do you have any more suggestions please?

Thanks
Paul.

4

Hi @ravenp,

Oh yeah I see this issue.

Try including both schema names with this option once. Something like-
--default-schema-name=SCHEMA1,SCHEMA2

Let us know if this works.

Thanks,
Rakhi Agrawal

5

Hi Rakhi

I tried with the inclusion of default-schema-name and specifying both schemas like this:

liquibase --snapshotFormat=json --outputFile=test_snapshot.json --url=jdbc:oracle:thin:@myhostname.local:1521/dbname.local --username=liquibase --password=Password123 --default-schema-name=SCHEMA1,SCHEMA2 --schemas=SCHEMA1,SCHEMA2 snapshot

But in that case it fails with:
Unexpected error running Liquibase: ORA-01435: user does not exist

If I trace the session in the database I can see it tried to run the following:
ALTER SESSION SET CURRENT_SCHEMA=“SCHEMA1,SCHEMA2”
, which is definitely wrong. So it looks like default-schema-name should not be used like this.

Thanks for your help so far. It looks like the author of the page “Diffing Multiple Schemas in Liquibase” was using PostgreSQL, so I wonder if the example given doesn’t work with Oracle. Perhaps it’s not possible to snapshot and diff multiple schemas at once with an Oracle database.

Thanks
Paul.