2 Replies Latest reply on Jun 12, 2019 12:54 PM by Leandro M de Lima

    Errors with sdcli migration

    Leandro M de Lima

      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.

        • 1. Re: Errors with sdcli migration
          Barry Mcgillin-Oracle

          Can you use SQLDeveloper for this and connect to your repository user.  You can right click on the connection and associate the connection with the migrqation.  You should be able to see the migration project then and also peruse the packages.

          • 2. Re: Errors with sdcli migration
            Leandro M de Lima

            Hi, Barry. It's a pretty big post, and that's why I decided to create a new thread instead of using my other question; too much info and details to share and explain.

             

            Like I said, yes, with SQLDeveloper I can create and use a repository and perform a complete migration.  But I need to automate this; it must be run with a batch script.  The goal is to to perform the migration without any intervention and manipulation, just a single command. I did exactly what you said, I was able to create the repository (its tables and packages) with SQLDeveloper and then sdcli was able to proceed (but then I hit a java error on a further step).  But again, the whole operation need to be scripted to avoid any manual intervention, so creating the reposit with SQL Developer is not an option.

             

            Thanks for you input.