Hi folks.
Following a previous question on how to script data copy from SQL to Oracle, Glen gave me a hint about the sdclient interface. Since it picked the curiosity of Jeff Smith, I'm posting here some problems I found using this interface.
First, the environment. In my company we develop applications for both Oracle and SQL and also offer the possibility of DBMS migration. We're looking for a way to improve the actual migration system (based on ascii text files) to something fully automated and more performing. That said, we already have the convertion tool; we have the model ready on both Oracle and SQL and we only need a data copy tool that can handle and transform the differents datatypes. Just for curiosity, from Oracle to SQL we're using SSMA and it does a simple yet efficient job on data copy. From SQL to Oracle the most promissing tool I found was SQL Developer, but like I said, this must be automated so we need to launch it from scripts, avoiding manual interventions and manipulations and sdcli migration seems to be the answer,
So now my actual situation: I have my destination and migration schema already created on the Oracle side. So I start creating the connections with sdcli.
C:\sqldeveloper\bin>sdcli migration -actions=driver -files=C:\Users\martlean\Downloads\sqldeveloper-19.1.0.094.2042-x64\sqldeveloper\jlib\jtds-1.3.1-dist\jtds-1.3.1.jar
Warning:file:/C:/Users/martlean/Downloads/sqldeveloper-19.1.0.094.2042-x64/sqldeveloper/jlib/jtds-1.3.1-dist/jtds-1.3.1.jar!/ already exists
Driver Preferences Saved
driver completed successfully
C:\sqldeveloper\bin>sdcli migration -actions=mkconn -connDetails=dest_oracle:oracle:martlean/********@ora8:1521/orcl
mkconn completed successfully
C:\sqldeveloper\bin>sdcli migration -actions=mkconn -connDetails=source_sql:sqlserver:martlean/********@gsqldev2:1433:martlean
mkconn completed successfully
C:\sqldeveloper\bin>sdcli migration -actions=init -connDetails=migration_repo:oracle:migrep/********@ora8:1521/orcl
init completed successfully
C:\sqldeveloper\bin>sdcli migration -actions=info
----------------------------------------------------------------------
Database Connections
source_sql : source_sql:sqlserver:martlean/********@gsqldev2:1433/martlean
* migration_repo : migration_repo:oracle:migrep/********@ora8:1521/null
dest_oracle : dest_oracle:oracle:martlean/********@ora8:1521/null
----------------------------------------------------------------------
Captured Models
----------------------------------------------------------------------
Converted Models
----------------------------------------------------------------------
info completed successfully
Nice, I can add the connections and they work, I can launch SQL Developer and can open each one, even the SQL Server one. So I try to initiate the migration repository and capture the model:
C:\sqldeveloper\bin>sdcli migration -actions=init -conn=migration_repo
init completed successfully
C:\sqldeveloper\bin>sdcli migration -actions=capture -conn=source_sql -dblist=martlean
Phase intermédiaire initiée
*** Server Information ***
inseré 1 lignes à partir de null dans STAGE_SERVERDETAIL
inseré 1 lignes à partir de STAGE_SS2K5_DATABASES dans STAGE_SS2K5_DATABASES
*** martlean ***
inseré 19 lignes à partir de martlean..STAGE_SS2K5_SCHEMATA dans STAGE_SS2K5_SCHEMATA
inseré 19 lignes à partir de martlean..STAGE_SS2K5_SCHEMAS dans STAGE_SS2K5_SCHEMAS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_TABLE_PRIVILEGES dans STAGE_SS2K5_TABLE_PRIVILEGES
inseré 26 lignes à partir de martlean..STAGE_SS2K5_DB_PRINCIPALS dans STAGE_SS2K5_DB_PRINCIPALS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_FN_KEY_COLUMNS dans STAGE_SS2K5_FN_KEY_COLUMNS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_FN_KEYS dans STAGE_SS2K5_FN_KEYS
inseré 6 lignes à partir de martlean..STAGE_SS2K5_IDENTITY_COLUMNS dans STAGE_SS2K5_IDENTITY_COLUMNS
inseré 339 lignes à partir de martlean..STAGE_SS2K5_INDEX_COLUMNS dans
inseré 153 lignes à partir de martlean..STAGE_SS2K5_INDEXES dans STAGE_SS2K5_INDEXES
inseré 952 lignes à partir de martlean..STAGE_SS2K5_SERVER_PRINCIPALS dans STAGE_SS2K5_SERVER_PRINCIPALS
inseré 7 lignes à partir de martlean..STAGE_SS2K5_SQL_MODULES dans STAGE_SS2K5_SQL_MODULES
inseré 7 lignes à partir de martlean..STAGE_SS2K5_TABLES dans STAGE_SS2K5_TABLES
inseré 34 lignes à partir de martlean..STAGE_SS2K5_TYPES dans STAGE_SS2K5_TYPES
inseré 7 lignes à partir de martlean..STAGE_SS2K5_DT_CONSTRAINTS dans STAGE_SS2K5_DT_CONSTRAINTS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_SYSPROPERTIES dans STAGE_SS2K5_SYSPROPERTIES
inseré 18 lignes à partir de martlean..STAGE_SS2K5_DB_ROLE_MEMBERS dans STAGE_SS2K5_DB_ROLE_MEMBERS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_CHECK_CONSTRAINTS dans STAGE_SS2K5_CHECK_CONSTRAINTS
inseré 98 lignes à partir de martlean..STAGE_SS2K5_OBJECTS dans STAGE_SS2K5_OBJECTS
inseré 895 lignes à partir de martlean..STAGE_SS2K5_COLUMNS dans STAGE_SS2K5_COLUMNS
********************
*SUMMARY************
********************
1 source databases metadata captured
1 rows inserted from STAGE_SS2K5_DATABASES to STAGE_SS2K5_DATABASES
19 rows inserted from STAGE_SS2K5_SCHEMATA to STAGE_SS2K5_SCHEMATA
19 rows inserted from STAGE_SS2K5_SCHEMAS to STAGE_SS2K5_SCHEMAS
0 rows inserted from STAGE_SS2K5_TABLE_PRIVILEGES to STAGE_SS2K5_TABLE_PRIVILEGES
26 rows inserted from STAGE_SS2K5_DB_PRINCIPALS to STAGE_SS2K5_DB_PRINCIPALS
0 rows inserted from STAGE_SS2K5_FN_KEY_COLUMNS to STAGE_SS2K5_FN_KEY_COLUMNS
0 rows inserted from STAGE_SS2K5_FN_KEYS to STAGE_SS2K5_FN_KEYS
6 rows inserted from STAGE_SS2K5_IDENTITY_COLUMNS to STAGE_SS2K5_IDENTITY_COLUMNS
339 rows inserted from STAGE_SS2K5_INDEX_COLUMNS to
153 rows inserted from STAGE_SS2K5_INDEXES to STAGE_SS2K5_INDEXES
952 rows inserted from STAGE_SS2K5_SERVER_PRINCIPALS to STAGE_SS2K5_SERVER_PRINCIPALS
7 rows inserted from STAGE_SS2K5_SQL_MODULES to STAGE_SS2K5_SQL_MODULES
7 rows inserted from STAGE_SS2K5_TABLES to STAGE_SS2K5_TABLES
34 rows inserted from STAGE_SS2K5_TYPES to STAGE_SS2K5_TYPES
7 rows inserted from STAGE_SS2K5_DT_CONSTRAINTS to STAGE_SS2K5_DT_CONSTRAINTS
0 rows inserted from STAGE_SS2K5_SYSPROPERTIES to STAGE_SS2K5_SYSPROPERTIES
18 rows inserted from STAGE_SS2K5_DB_ROLE_MEMBERS to STAGE_SS2K5_DB_ROLE_MEMBERS
0 rows inserted from STAGE_SS2K5_CHECK_CONSTRAINTS to STAGE_SS2K5_CHECK_CONSTRAINTS
98 rows inserted from STAGE_SS2K5_OBJECTS to STAGE_SS2K5_OBJECTS
895 rows inserted from STAGE_SS2K5_COLUMNS to STAGE_SS2K5_COLUMNS
0 SQL Objects failed to translate
0 SQL Objects successfully translated and inserted into STAGE_TRANSLATEDSQL
Capture démarrée
Error:ORA-06510: PL/SQL : exception définie par l'utilisateur non traitée
ORA-06512: à "MIGREP.SS2K5ALLPLATFORM", ligne 3313
ORA-04067: package body "MIGREP.MIGRATION" n'existe pas - non exécuté
ORA-06508: PL/SQL : unité de programme nommée : "MIGREP.MIGRATION" introuvable
ORA-06512: à ligne 1
capture completed successfully
C:\sqldeveloper\bin>
And here is the first problem. The migration repository is apparently correctly created, I can see its tables and packages. But when I try the capture action, I hit this MIGREP.MIGRATION package not found (sorry for the french). So just to be sure, let me check if the said package was really created on the migrep schema:
c:\martlean>sqlplus migrep/*******@ora8
SQL*Plus: Release 12.1.0.1.0 Production on Tue Jun 11 14:15:32 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Tue Jun 11 2019 13:50:44 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select object_name,status from user_objects where object_name like 'MIGRATION';
OBJECT_NAME
--------------------------------------------------------------------------------
STATUS
-------
MIGRATION
VALID
SQL>
Yep, the package is there and it's ok, but it seems like sdcli migration cannot find it. So I tried the migration wizard with SQL Developer using this repository created with sdcli migration and it gave me a similar error: Capture failed (no additiona info, though). Was there something wrong with the repository init? OK, I try to drop and recreate it with SQL Developer. Voilà, it works perfectly. The reposit is created and I can migrate the tables and data correctly. No errors.
It seems that sdcli does not correctly create the repository, maybe it's missing a synonym or something? So another try, I drop and create the reposit again with SQL Developer but then I try the caputre with sdcli.
Surprise, surprise, it works:
C:\sqldeveloper\bin>sdcli migration -actions=capture -conn=source_sql -dblist=martlean
Phase intermédiaire initiée
*** Server Information ***
inseré 1 lignes à partir de null dans STAGE_SERVERDETAIL
inseré 1 lignes à partir de STAGE_SS2K5_DATABASES dans STAGE_SS2K5_DATABASES
*** martlean ***
inseré 19 lignes à partir de martlean..STAGE_SS2K5_SCHEMATA dans STAGE_SS2K5_SCHEMATA
inseré 19 lignes à partir de martlean..STAGE_SS2K5_SCHEMAS dans STAGE_SS2K5_SCHEMAS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_TABLE_PRIVILEGES dans STAGE_SS2K5_TABLE_PRIVILEGES
inseré 26 lignes à partir de martlean..STAGE_SS2K5_DB_PRINCIPALS dans STAGE_SS2K5_DB_PRINCIPALS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_FN_KEY_COLUMNS dans STAGE_SS2K5_FN_KEY_COLUMNS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_FN_KEYS dans STAGE_SS2K5_FN_KEYS
inseré 6 lignes à partir de martlean..STAGE_SS2K5_IDENTITY_COLUMNS dans STAGE_SS2K5_IDENTITY_COLUMNS
inseré 339 lignes à partir de martlean..STAGE_SS2K5_INDEX_COLUMNS dans
inseré 153 lignes à partir de martlean..STAGE_SS2K5_INDEXES dans STAGE_SS2K5_INDEXES
inseré 952 lignes à partir de martlean..STAGE_SS2K5_SERVER_PRINCIPALS dans STAGE_SS2K5_SERVER_PRINCIPALS
inseré 7 lignes à partir de martlean..STAGE_SS2K5_SQL_MODULES dans STAGE_SS2K5_SQL_MODULES
inseré 7 lignes à partir de martlean..STAGE_SS2K5_TABLES dans STAGE_SS2K5_TABLES
inseré 34 lignes à partir de martlean..STAGE_SS2K5_TYPES dans STAGE_SS2K5_TYPES
inseré 7 lignes à partir de martlean..STAGE_SS2K5_DT_CONSTRAINTS dans STAGE_SS2K5_DT_CONSTRAINTS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_SYSPROPERTIES dans STAGE_SS2K5_SYSPROPERTIES
inseré 18 lignes à partir de martlean..STAGE_SS2K5_DB_ROLE_MEMBERS dans STAGE_SS2K5_DB_ROLE_MEMBERS
inseré 0 lignes à partir de martlean..STAGE_SS2K5_CHECK_CONSTRAINTS dans STAGE_SS2K5_CHECK_CONSTRAINTS
inseré 98 lignes à partir de martlean..STAGE_SS2K5_OBJECTS dans STAGE_SS2K5_OBJECTS
inseré 895 lignes à partir de martlean..STAGE_SS2K5_COLUMNS dans STAGE_SS2K5_COLUMNS
********************
*SUMMARY************
********************
1 source databases metadata captured
1 rows inserted from STAGE_SS2K5_DATABASES to STAGE_SS2K5_DATABASES
19 rows inserted from STAGE_SS2K5_SCHEMATA to STAGE_SS2K5_SCHEMATA
19 rows inserted from STAGE_SS2K5_SCHEMAS to STAGE_SS2K5_SCHEMAS
0 rows inserted from STAGE_SS2K5_TABLE_PRIVILEGES to STAGE_SS2K5_TABLE_PRIVILEGES
26 rows inserted from STAGE_SS2K5_DB_PRINCIPALS to STAGE_SS2K5_DB_PRINCIPALS
0 rows inserted from STAGE_SS2K5_FN_KEY_COLUMNS to STAGE_SS2K5_FN_KEY_COLUMNS
0 rows inserted from STAGE_SS2K5_FN_KEYS to STAGE_SS2K5_FN_KEYS
6 rows inserted from STAGE_SS2K5_IDENTITY_COLUMNS to STAGE_SS2K5_IDENTITY_COLUMNS
339 rows inserted from STAGE_SS2K5_INDEX_COLUMNS to
153 rows inserted from STAGE_SS2K5_INDEXES to STAGE_SS2K5_INDEXES
952 rows inserted from STAGE_SS2K5_SERVER_PRINCIPALS to STAGE_SS2K5_SERVER_PRINCIPALS
7 rows inserted from STAGE_SS2K5_SQL_MODULES to STAGE_SS2K5_SQL_MODULES
7 rows inserted from STAGE_SS2K5_TABLES to STAGE_SS2K5_TABLES
34 rows inserted from STAGE_SS2K5_TYPES to STAGE_SS2K5_TYPES
7 rows inserted from STAGE_SS2K5_DT_CONSTRAINTS to STAGE_SS2K5_DT_CONSTRAINTS
0 rows inserted from STAGE_SS2K5_SYSPROPERTIES to STAGE_SS2K5_SYSPROPERTIES
18 rows inserted from STAGE_SS2K5_DB_ROLE_MEMBERS to STAGE_SS2K5_DB_ROLE_MEMBERS
0 rows inserted from STAGE_SS2K5_CHECK_CONSTRAINTS to STAGE_SS2K5_CHECK_CONSTRAINTS
98 rows inserted from STAGE_SS2K5_OBJECTS to STAGE_SS2K5_OBJECTS
895 rows inserted from STAGE_SS2K5_COLUMNS to STAGE_SS2K5_COLUMNS
0 SQL Objects failed to translate
0 SQL Objects successfully translated and inserted into STAGE_TRANSLATEDSQL
Capture démarrée
capture completed successfully
C:\sqldeveloper\bin>
So, I think this proves sdcli does not correctly create the reposit, right? Maybe it did not give the right permission on the MIGRATION package? Did not try it yet but maybe a GRANT SELECT TO PUBLIC could resolve this?
Anyway, now that the model is captured, I can convert it and generate the Oracle model, right? Well, not quite:
C:\sqldeveloper\bin>sdcli migration -actions=convert -model=latest
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
rowsUpdated:0
convert completed successfully
C:\sqldeveloper\bin>sdcli migration -actions=generate -model=latest -output=c:\mig
Password for migration_repo?
Error:java.lang.NullPointerException
HELP:
Syntax:
sdcli migration -help|-h=<actions> to get help on one or more actions.
where
<actions> could be one or more (comma separated list) actions or guide
guide provides a walk through of a typical migration
Examples:
sdcli migration -help=capture
sdcli migration -h=capture,convert
sdcli migration -h=guide
Valid actions:
capture, convert, datamove, delcaptured, delconn, delconverted, driver, generate, guide, idmap, info, init, lscaptured, lsconn, lsconverted, mkconn, qm, runsql, scan and translate
C:\sqldeveloper\bin>
Nice, now I hava a Java error. I have no idea how to proceed from here.
But wait, I don't really need to capture/convert the model, do I? like I said at the begining we have our tools to convert the tables. Let's just force a data copy on the Oracle tables already pre-created and hope for the best:
C:\sqldeveloper\bin>sdcli migration -actions=qm -conn=source_sql,dest_oracle -dblist=martlean
java.nio.file.NoSuchFileException: null\MigrationLog.xml.lck
at sun.nio.fs.WindowsException.translateToIOException(WindowsException.java:79)
at sun.nio.fs.WindowsException.rethrowAsIOException(WindowsException.java:97)
at sun.nio.fs.WindowsException.rethrowAsIOException(WindowsException.java:102)
at sun.nio.fs.WindowsFileSystemProvider.newFileChannel(WindowsFileSystemProvider.java:115)
at java.nio.channels.FileChannel.open(FileChannel.java:287)
at java.nio.channels.FileChannel.open(FileChannel.java:335)
at java.util.logging.FileHandler.openFiles(FileHandler.java:478)
at java.util.logging.FileHandler.<init>(FileHandler.java:420)
at oracle.dbtools.migration.workbench.core.logging.MigrationFileHandler.<init>(MigrationFileHandler.java:41)
at oracle.dbtools.migration.command_line.QMCmd.<init>(QMCmd.java:58)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at oracle.dbtools.migration.command_line.MigrationCmdRegistry.getMigrationCmd(MigrationCmdRegistry.java:40)
at oracle.dbtools.migration.command_line.MigrationCommandProcessor$migrationTask.execute(MigrationCommandProcessor.java:79)
at oracle.dbtools.migration.command_line.MigrationCommandProcessor$migrationTask.doWork(MigrationCommandProcessor.java:45)
at oracle.dbtools.migration.command_line.MigrationCommandProcessor$migrationTask.doWork(MigrationCommandProcessor.java:30)
at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:199)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:702)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
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:null
HELP:
Syntax:
sdcli migration -help|-h=<actions> to get help on one or more actions.
where
<actions> could be one or more (comma separated list) actions or guide
guide provides a walk through of a typical migration
Examples:
sdcli migration -help=capture
sdcli migration -h=capture,convert
sdcli migration -h=guide
Valid actions:
capture, convert, datamove, delcaptured, delconn, delconverted, driver, generate, guide, idmap, info, init, lscaptured, lsconn, lsconverted, mkconn, qm, runsql, scan and translate
C:\sqldeveloper\bin>
Oh damn, not another Java exception again. What do I do now???
So Mr. Jeff, do you have a hint? Like I said the wizard works just fine, even with pre-created tables.
When will the magical merged sdcli + sqlcl be available? Will it patch this? Please give me some hope, it's really frustating to feel so close yet so far from a solution.
Thansk in advance and don't mind the jokes. Like I said, you guys rock.