In SQL Developer

Oracle SQLcl version 22.2 is now available

June 23, 2022 3 Mins Read

Download

Release Notes/Readme

TL/DR;

New Command: MIGRATEADVISOR (MA)

MA for short, checks your database for potential problems before data pumping to the Cloud.

Docs and Demo

We have an entire chapter in the SQLcl docs to help you get started with this new command, complete with examples.

Of course there’s help built-in for the command as well.

There’s basically two modes, ‘advise’ and ‘properties.’

Capture ‘properties’ of your Target Instance

If you are migrating to an existing Oracle Database in the Cloud, you can connect with SQLcl, and capture a snapshot of it’s properties.

Connecting to Autonomous is as easy as downloading the Wallet (zip) and doing a CONNECT.

set cloudconfig & show tns are your friends

Once you’re connected, use the ma command with the properties option.

By default we’ll write to the local directory.

-p is short for -outfileprefix, and is tacked onto the beginning of the report file(s).

If we peak into this .properties file, we can see what’s being recorded for our upcoming ‘advise’ command –

Now we know that our Autonomous Database is running with a Timezone file version of 36.

Running Advise on your local database

With a highly privileged account, such as ‘SYSTEM’, you can now connect to the database you’ll be migrating one or more schemas off of, and generate your reports.

‘hr’ is god on my local instance of Oracle.

ma advise -t ATPS -analysisprops ATP-TJS-19c_premigration_advisor_analysis.properties

-t is short for -targettype, which can be {ATPS|ATPD|ADWS|ADWD|DEFAULT}. Default just means, any ole Oracle Database. ‘ATPS’ means, an Oracle Autonomous Shared Transaction Processing instance.

I could use the -s, or -schemas flag to specify EXACTLY which schemas we want to analyze. But I didn’t, so the report looks at EVERYTHING sans the system maintained schemas.

The report comes back with a quick summary of what was found, and tells you about the reports generated.

My 33 schemas took about 25-30 seconds for SQLcl to chunk through.

The HTML Report

I clicked into the ‘Review Required’ section for the rules that were tripped.

We can see on the report what the source system is, and we even record the ‘ma’ command used to generate the report.

If I click into one of the detail items, I get more info –

If your application uses these objects, you need to code around that before migrating.

Note that we include the SQL behind the report, so you can run it yourself to see what’s what…

Here’s the SQL used to find Objects that are in ‘Custom’ tablespaces. When I run my Data Pump import job, I’ll need to remap the tablespaces to the ‘Data’ one you get in Autonomous.

Ew that SQL is ugly, if only there were a formatter…

Isn’t this just the CPAT Utility?

Yes. It is in fact just the Cloud Premigration Advisor Tool (CPAT.) You can still download that from the Support portal (Doc ID 2758371.1), assuming you have an account in good standing. But now, it’s officially part of SQLcl, and will get updates as needed on a regular basis. And of course we try to make things as easy as possible for you, so here we are.

Author thatjeffsmith

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

6 Comments

satish 3 months ago Reply

Hi Jeff,

Can you please help out. We are installing SQLCL but it shows wrong version. It should be 23.4. but it shows 21.4.
We have raised SR but no solution yet.

Steps:

[orasupp@erpsupportdb java11]$ export JAVA_HOME=/u02/java11/jdk-11.0.21
[orasupp@erpsupportdb java11]$ export JRE_HOME=/u02/java11/jdk-11.0.21

[orasupp@erpsupportdb java11]$ cd /u02/Oracle_SQLcl/
[orasupp@erpsupportdb Oracle_SQLcl]$ ls
sqlcl-23.4.0.023.2321.zip

[orasupp@erpsupportdb Oracle_SQLcl]$ unzip sqlcl-23.4.0.023.2321.zip
Archive: sqlcl-23.4.0.023.2321.zip
creating: sqlcl/
creating: sqlcl/bin/
creating: sqlcl/lib/
creating: sqlcl/lib/ext/
inflating: sqlcl/NOTICES.txt
inflating: sqlcl/lib/orai18n-utility.jar
inflating: sqlcl/LICENSE.txt
inflating: sqlcl/lib/orai18n-mapping.jar
inflating: sqlcl/THIRD-PARTY-LICENSES.txt
inflating: sqlcl/lib/assertj-core.jar
inflating: sqlcl/23.4.0.023.2321
inflating: sqlcl/lib/sshd-contrib.jar

: : :

inflating: sqlcl/lib/httpcore5-h2.jar
inflating: sqlcl/lib/low-level-api.jar
inflating: sqlcl/lib/sshd-sftp.jar
inflating: sqlcl/lib/sshd-scp.jar
inflating: sqlcl/lib/dbtools-data.jar
inflating: sqlcl/lib/dbtools-datapump.jar
inflating: sqlcl/lib/osdt_core.jar
inflating: sqlcl/lib/oraclepki.jar
inflating: sqlcl/lib/orai18n.jar

[orasupp@erpsupportdb Oracle_SQLcl]$ cd sqlcl/bin
[orasupp@erpsupportdb bin]$ pwd
/u02/Oracle_SQLcl/sqlcl/bin
[orasupp@erpsupportdb bin]$ ls
dependencies.txt sql sql.exe version.txt
[orasupp@erpsupportdb bin]$ cat version.txt
#
# Oracle SQLcl Build numbers
#
RELEASE=23.4.0.023.2321
JULIAN_BUILD_VERSION=23.4.0.023.2321
LATEST_BUILD=sqlcl-23.4.0.023.2321-2024-01-2323:21:53+0000[orasupp@erpsupp

[orasupp@erpsupportdb ~]$ export SQLPATH=/u02/Oracle_SQLcl/sqlcl/bin

[orasupp@erpsupportdb ~]$ sql xx_apex/*****@suppdb

SQLcl: Release 21.4 Production on Tue Feb 06 20:27:25 2024——————————–>wrong version.

Copyright (c) 1982, 2024, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.17.0.0.0

Thanks,
SG
- thatjeffsmith Post Author 3 months ago
  
  And if you run the program explicitly instead of relying on the path?
  
  cd to the bin directory and, ./sql
andreml 2 years ago Reply

Sorry for the long delay, however it’s not forgotten:
Well, the “problem” is indeed a problem. So quotation marks are out of place here.
And no, I don’t know any work around and I don’t remember to have received a hint from you.
I am still on the last working version. Or is this the work around you mean?
Clearly, this is broken functionality – and things like that has always been Oracle’s highest priority.
When did this change?
I take the reference to MOS as a joking attempt to push the topic away. But no, I don’t find it funny.
- thatjeffsmith Post Author 2 years ago
  
  It’s not a joke. If you want to keep tabs on a bug, you can ping MOS.
  
  22.3 is coming out in a few days i can check if this has been fixed tomorrow for you.
andreml 2 years ago Reply

Ji Jeff,
Do you remember this; it was a Twitter-PM to Garry 14. Okt. 2021:
We have a new problem in 21.3 (latest) Loading into a view (Actual table is in a different schema, grants assigned to “loading schema”. ) is no longer possible. So far this was OK and should be possible again. I guess you have restricted a query in your code to user_tables, but this is not OK; the views have to be considered as well – like before 21.3. If you need more Info, please let me know.
–>
Unfortunately, this functionality is still not restored. I was under the impression that the reasonableness and necessity for this was clear, and that there would therefore be a quick fix/restore. Unfortunately, that doesn’t seem to be the case.
Honestly, I don’t understand what the problem is and I’m pretty disappointed.
So many things were built in, why was there no time to fix this.
Is it perhaps too complicated after all, is there anything I can do to help you get a fix asap?
Best Regards Andre
- thatjeffsmith Post Author 2 years ago
  
  We have many products/features to maintain for many users. Things get prioritized and fixed, added, based on business needs. This ‘issue’ at least has a workaround.
  
  If you log a ticket with My Oracle Support you can keep up to date with the issue as news is available.