How can I migrate data from Derby to Oracle?

To transfer a database from Derby to Oracle, follow the instructions below:

It is recommended to prepare a new PDB database for aSISt (asistPDB). If you have PDB database, you can use it. It is not necessary to create separate PDB database for aSISt. Using the root database is not recommended.

To create asistPDB you can:

use sql:

<SQL> CREATE PLUGGABLE DATABASE asistpdb ADMIN USER asistdba identified by “asist” DEFAULT TABLESPACE USERS DATAFILE ‘D:\APP\ORACLE12\ORADATA\ASIST\ASIST\USERS01.DBF’ SIZE 1G AUTOEXTEND ON FILE_NAME_CONVERT=(‘D:\APP\ORACLE12\ORADATA\ORCL\PDBSEED\’,’D:\APP\ORACLE12\ORADATA\ASIST\ASIST\’);

<SQL> ALTER PLUGGABLE DATABASE asistpdb open read write;

<SQL> ALTER PLUGGABLE DATABASE asistpdb SAVE STATE;

Note: The path in the first SQL has to define the existing place and files.

use the instruction available on the Oracle’s website.

You can use SQL to create a new Oracle user:

<SQL> alter session set container = asistpdb;

<SQL> create user asist identified by asist default tablespace users temporary tablespace temp;

<SQL> grant connect,resource,create view to asist;

<SQL> GRANT UNLIMITED TABLESPACE TO asist;

SQL can be modified if you need to change the path or database name, user name.

Connect with the new asist user.

Next, it is required to prepare backup of Derby database.

If you have the backup, you can start the migration.

Run the derby_2_oracle_migrator.bat, which is available in the aSISt5 directory:

In the wizard it is required to fill:

User: asist (migr in attached screenshots)

Password: asist (migr in attached screenshots)

Address: jdbc:oracle:thin:@//localhost:1522/asistpdb (format for Oracle 12, you have to fill the address for the new asist connection including the proper PDB database – orclpdb in my case)