6 Replies Latest reply: Jul 21, 2011 4:09 AM by 854743 RSS

    migration -actions=mkconn returns always Error: java.sql.SQLException: Inva

    854743
      Hi

      I'm migrate from MS SQL 2005 to Oracle 11g using SQL Developer V3.0.04
      Trying to use the batch file migration.bat. Found the documentation with migration -help=guide

      Every action I start results in the SQLException below:
      D:\oracle\product\sqldeveloper_3.0.04\sqldeveloper\sqldeveloper\bin>migration -actions=mkconn -connDetails=super_oracle:oracle:system/manager@xxxxxxxx:1521/mps
      Error: java.sql.SQLException: Invalid connection information specified.
      Verify the URL format for the specified driver.
      HELP:

      Running the command with -output=d:\migr creates a logfile with the same error message.
      If I run the login credential with sqlplus system/manager@xxxxxxxx:1521/mps, I manage to connect to the database.
      I assume the help is wrong specifying host:port:sid. I think host:port/sid is correct, at least with sqlplus this works
      If I do a migration with sqldeveloper it works too.

      I'm lost at this point. Any advise is appreciated.
      Thanks a lot
      Beat
        • 1. Re: migration -actions=mkconn returns always Error: java.sql.SQLException: Inva
          Kgronau-Oracle
          Beat,
          the -conn specifies the details for the remote database that you want to migrate see the help: -conn=<sourceConnectionName>
          BTW, why are you trying to execute it through the batch file? The online version is doing the same things like the batch file and has the advantage that you'll be guided what to specify.
          • 2. Re: migration -actions=mkconn returns always Error: java.sql.SQLException: Inva
            854743
            Hi
            Thanks for the fast feedback.
            The reason to use migration.bat: I have a couple of ms development databases which I have to migrate to productive oracle databases.
            With migration.bat I can automate the migration.

            I did some further tests:
            Reopen a new cmd.exe
            xxx>migration -action=init -conn=migration_repository:username/password@xxxxxxxxxxx:1521/SID results in
            HELP:
            Syntax:
            Migration -help|-h=<actions> to get help on one or more actions.
            without error message
            What can be expected?

            And
            xxx>migration -action=info results also in
            HELP:
            Syntax:
            Migration -help|-h=<actions> to get help on one or more actions.
            without error message
            What can be expected?

            But if I do now
            xxx>migration -actions=driver -files=D:\oracle\product\sqldeveloper_3.0.04\sqldeveloper\sqldeveloper\lib\jtds-1.2.1.jar
            Error: java.sql.SQLException: Invalid connection information specified.
            Verify the URL format for the specified driver.
            I run into the exception.
            • 3. Re: migration -actions=mkconn returns always Error: java.sql.SQLException: Inva
              Kgronau-Oracle
              ok, I see...

              In SQL Developer, did you already create a connection to the Oracle database storing the Migration Repository?

              For example in my SQL Developer I've created a connection to the repository and it is named "emeagtw_11_20_2_repository"
              Now I can connect to the repository as follows:
              D:\oracle\sqldeveloper-3.0.04.34\sqldeveloper\sqldeveloper\bin>Migration -actions=init -conn=emeagtw_11_20_2_repository
              init completed successfully

              Edited by: kgronau on Jul 20, 2011 4:35 PM

              When you don't have a name, but an Oracle database with the repository you can use:
              D:\oracle\sqldeveloper-3.0.04.34\sqldeveloper\sqldeveloper\bin>Migration -actions=init -connDetails=migration_repo:oracle:kgrrepo/kgrrepo@emeagtw.de.oracle.com:11221:db1122
              init completed successfully

              where kgrrepo is the username and password of the migration repository, the host is emeagtw.de.oracle.com and the listener is using 11221 port#, the db SID is db1122



              D:\oracle\sqldeveloper-3.0.04.34\sqldeveloper\sqldeveloper\bin>

              Edited by: kgronau on Jul 20, 2011 4:37 PM

              so the difference is, when you have an ALIAS, then you can use -conn option, when you specify the connect details to the repository as a connect string, then you have to use -connDetails

              see the help:
              Migration -actions=init (-connDetails=[<superConnectionDetails>,]<repositoryConnectionDetails>[,<sourceConnectionDetails>,<targetConnectionDetails>] |
              -conn=<repositoryConnection>) [-output=<output dir>]
              • 4. Re: migration -actions=mkconn returns always Error: java.sql.SQLException: Inva
                854743
                Hi

                In SQL Developer, did you already create a connection to the Oracle database storing the Migration Repository?
                Yes

                I do:
                migration -action=init -conn=MyRepositoryDB
                HELP:
                Syntax:
                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:
                Migration -help=capture
                Migration -h=capture,convert
                Migration -h=guide

                The message: init completed successfully is missing. Only HELP:.......... is showing up

                The same if I do now:
                D:\oracle\product\sqldeveloper_3.0.04\sqldeveloper\sqldeveloper\bin>migration -action=lsconn
                HELP:
                Syntax:
                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:
                Migration -help=capture
                Migration -h=capture,convert
                Migration -h=guide

                Valid actions:
                capture, convert, datamove, delcaptured, delconn, delconverted, driver, generate, guide, idmap, info, init, ls
                m, runsql, scan and translate

                D:\oracle\product\sqldeveloper_3.0.04\sqldeveloper\sqldeveloper\bin>

                Only HELP:.......... is showing up
                • 5. Re: migration -actions=mkconn returns always Error: java.sql.SQLException: Inva
                  Kgronau-Oracle
                  There's still a typo - it is actions instead of action...

                  migration -actions=init -conn=MyRepositoryDB
                  • 6. Re: migration -actions=mkconn returns always Error: java.sql.SQLException: Inva
                    854743
                    Hi kgronau

                    Thanks for the typo.
                    Investigated further and found the problem:
                    migration -actions=init -conn=MigrRepoDB suddenly didn't work anymore
                    migration -actions=info still does not work

                    Solution here is:
                    Changed the connectionType in sqldeveloper from TNS  to Basic for the MigrRepoDB and adapt correct settings.
                    migration -actions=info returns now data
                    migration -actions=lsconn return now data
                    migration -actions=delconverted, delcaptured -model=latest removes sqldeveloper created migrations
                    migration works now perfect

                    Thanks a lot
                    Regards
                    Beat