2 Migrating from Oracle Data Integrator Cloud Services to Oracle Data Integrator Marketplace

This chapter describes how to migrate the Oracle Data Integrator repository of Oracle Data Integrator Cloud Service (ODI-CS) to Oracle Data Integrator on Marketplace at the database level, using Data Pump.

This chapter contains the following sections:

• Exporting the ODI-CS Repository

• Provisioning DBaaS

• Provisioning ATP

• Provisioning ODI Marketplace

• Installing the required ODI version in ODI Marketplace node

• Creating an ODI Repository in DBaaS

• Importing the ODI Repository into the DBaaS node

• Verifying access to the DBaaS ODI Repository in ODI Studio

• Upgrading the Repository

• Exporting the Migrated Repository

• Importing the Migrated Repository into ATP

• Verifying access to the Migrated Repository in ODI Marketplace Studio

Note:

Migration using data pump should only be done when the repository is very large. For smaller repositories, a standard export/import is enough. For more information on exporting/importing repositories, refer Repository-Level Export/Import section of Developing Integration Projects with Oracle Data Integrator.

• Exporting the ODI-CS Repository
  
• Provisioning DBaaS
  
• Provisioning ATP
  
• Provisioning ODI Marketplace
  
• Installing the required ODI version in ODI Marketplace node
  
• Creating an ODI Repository in DBaaS
  
• Importing the ODI Repository into the DBaaS node
  
• Verifying access to the DBaaS ODI Repository in ODI Studio
  
• Upgrading the Repository
  
• Exporting the Migrated Repository
  
• Importing the Migrated Repository into ATP
  
• Verifying access to the Migrated Repository in ODI Marketplace Studio
  

2.1 Exporting the ODI-CS Repository

You can export the ODI-CS repository using SQL Developer (Data Pump Wizard) or with the following OS-line command:

expdp SYSTEM/<PASSWOD>@<TNS_NAME> schemas=<SCHEMA_NAME> directory= DATA_PUMP_DIR_PDB dumpfile=<DUMP_FILE_NAME > logfile=<LOG_FILE_NAME>

2.2 Provisioning DBaaS

Follow the provisioning Wizard for DBaaS System and Database as described in the below section:

https://docs.cloud.oracle.com/en-us/iaas/Content/Database/Tasks/creatingDBsystem.htm

The DBaaS version should be the same as the DBCS version used in ODI-CS.

2.3 Provisioning ATP

Refer to the tutorial, Getting Started and Provisioning Autonomous Transaction Processing for detailed steps on how to provision ATP.

2.4 Provisioning ODI Marketplace

For detailed information on provisioning ODI Marketplace, see Launching Your Oracle Data Integrator Instance.

After provisioning, the standalone agent is available by default. It is possible to switch the standalone agent (that comes with ODI Marketplace) to point to the new repository. However, if a standalone collocated/weblogic agent is required, it has to be installed separately. For more information on setting up a standalone collocated agent, see Creating a Standalone Collocated Agent.

2.5 Installing the required ODI version in ODI Marketplace node

The ODI version installed in the ODI Marketplace node must be the same as that used in ODI-CS.

Follow the below steps to install the required ODI version in the ODI Marketplace node:

1. Use SSH to connect to the ODI Marketplace node.
2. Create a new directory and download the required ODI version into this directory.
3. Unzip the files and run the ODI installer.
4. In the Welcome screen, click Next.
5. In the Auto Updates screen, select Skip Auto Updates. Click Next.
6. In the Installation Location screen, specify the Oracle Home using the directory you created for ODI. Click Next.
7. In the Installation Type screen, leave the default selected “Standalone Installation” as it is and click Next.
8. In the Prerequisite Checks screen, click Next when the prerequisite check is 100% complete.
9. In the Security Updates screen, uncheck the security updates option and click Next.
10. Click Yes in the warning dialog box. This is a temporary installation, so we do NOT need to connect to support.
11. In the Installation Summary screen, verify the installation information and click Install.
12. In the Installation Progress screen, you can see the progress of the process. This might take several minutes. Once all tasks are successfully finished, click Finish.

2.6 Creating an ODI Repository in DBaaS

You can create an ODI repository in DBaaS using the repository creation utility (rcu). The repository version must be the same as that used in ODI-CS.

Follow the below steps to create an ODI repository in DBaaS with rcu:

1. Navigate to the $MW_HOME/oracle_common/bin directory.
2. Execute rcu to create a new (empty) repository.
3. In the Welcome screen, click Next.
4. In the Create Repository screen, accept the default values and click Next.
5. In the Database Connection Details screen, provide your DBaaS connection information. Note that a user with DBA or SYSDBA privilege is required (for example, SYS).
6. Once all the pre-requisites are verified, click OK.
7. In the Select Components screen, leave all defaults and select Oracle Data Integrator. Click Next.
8. Once all the pre-requisites are verified, click OK.
9. In the Schema Passwords screen, enter a password and verify it. Please make a note of this password as you will need it later in the process. Click Next.
10. In the Custom Variables screen, enter a Supervisor password and Work Repository password.

    The Supervisor password should be 6 - 12 characters in length and can contain special characters “#” or “_”. The Work Repository password should be 0 - 10 characters in length and can contain special characters “#” or “_”. Please make a note of these passwords as you will need them later in the process.

11. In the Map Tablespaces screen, click Next.
12. In the confirmation window, click OK.
13. Once tablespaces are created, click OK.
14. In the Summary screen, click Create.
15. A screen that will allow you to follow the progress of the different tasks will appear. Once all tasks are finished, a new screen will appear. The new screen will show the status of all tasks. Click Close.

2.7 Importing the ODI Repository into the DBaaS node

Follow the below steps to import the ODI repository into the DBaaS node:

1. Use SSH to connect to the DBaaS node into which you will import the ODI repository.

2. Execute the following impdp command:

   impdp SYSTEM/<PASSWORD>@<DBBaaS_system> schemas=DEV_ODI_REPO directory=DATA_PUMP_DIR_PDB dumpfile=<DUMP_FILE_NAME> logfile=<LOG_FILE_NAME> TABLE_EXISTS_ACTION=REPLACE

   Please ensure that the TABLE_EXISTS_ACTION option is included in the command.

2.8 Verifying access to the DBaaS ODI Repository in ODI Studio

Follow the below steps in order to verify access to the DBaaS ODI repository in ODI Studio :

1. Navigate to the $MW_HOME/odi/studio directory.
2. Execute odi.sh.
3. In the Confirm Import Preferences screen, click No.
4. Click Connect to Repository available on the left panel.
5. On the Oracle Data Integrator Login screen, click the plus sign (+) icon to add the repository. The Repository Connection Information screen appears.
6. Specify the Oracle Data Integrator connection details as follows:
   1. Login Name: Specify a custom login name. You can provide a name that will remind you of the purpose of this repository.
   2. User: Specify SUPERVISOR.
   3. Password: The password of your original ODI repository.
7. Specify the Database Connection (Master Repository) details as follows:
   1. User: The schema owner (in the newly created repository).
   2. Password: The password of the schema owner.
   3. Driver List: Select Oracle JDBC Driver from the drop-down list.
   4. Driver Name: This is automatically populated to 'oracle.jdbc.OracleDriver'.
   5. URL: Specify the connection URL. The connection URL should be in the following format: jdbc:oracle:thin:@<host>:<port/ServiceName>.
8. Click Test to test the connection to the repository.
9. On receiving a successful connection message, click OK.
10. Click OK in the Repository Connection Information screen.
11. In the Oracle Data Integrator Login screen, click OK.
12. Navigate to the Topology tab on the left panel.
13. Expand the Work Repositories node and double-click WORKREP. You will receive an error message. Click OK.
14. To change the connection information to the DBaaS repository, click on the plug icon in the top left corner of the screen.
15. Provide the database user (DBaaS) for the repositories and its password. Then click on the JDBC link on the left pane.
16. Provide the connection URL to the DBaaS server and click Test Connection.
17. Click Yes in the confirmation window.
18. Click Test to test the connection to the repository. Please ensure that you do not change the agent specified here.
19. On receiving a successful connection message, click OK.

2.9 Upgrading the Repository

Follow the below steps to upgrade the repository:

1. Navigate to the $MW_HOME/oracle_common/upgrade/bin directory in the ODI Marketplace node.
2. Execute the upgrade tool.
3. In the Welcome screen, click Next.
4. In the Selected Schemas screen, select Individually Selected Schemas. Click Next.
5. In the Available Components screen, select Oracle Data Integrator. Click Next.
6. In the Prerequisites screen, select all the check boxes and click Next.
7. In the ODI Schema screen, provide the connection information to the DBaaS repository and click Connect. Please note that a user with DBA privileges is required in your DBaaS system.

   If the connection is successful, the schema user will appear in the Schema User Name field. In this case, the schema user is DEV_ODI_REPO.

8. Provide the password for the schema user and click Next.
9. In the ODI Options screen, leave all the defaults and click Next.
10. In the ODI Supervisor screen, provide the Supervisor user and its password. Click Next.
11. The system will then examine the repository and determine if it is ready for upgrade. Once the examination is complete, click Next.
12. Verify the summary and click Upgrade in the Upgrade Summary screen.
13. You can view the status of the upgrade process in the Upgrade Progress screen. Once this is finished, click Next.
14. Click Close in the Upgrade Success screen.

2.10 Exporting the Migrated Repository

You can export the migrated repository using SQL Developer (Data Pump Wizard) or with the following OS-line command:

expdp SYSTEM/<PASSWOD>@<TNS_NAME> schemas=<SCHEMA_NAME> directory= DATA_PUMP_DIR_PDB dumpfile=<DUMP_FILE_NAME > logfile=<LOG_FILE_NAME>

Please note that you need to use DATA_PUMP_DIR_PDB in the command, and not DATA_PUMP_DIR.

2.11 Importing the Migrated Repository into ATP

To import the migrated repository into ATP, do the following:

1. Upload the exported upgrade repository to Object Storage, so that it can be accessed from ATP.

2. Create credentials in your ATP system so that you can access your Object Storage:

   BEGIN
      DBMS_CLOUD.PUT_OBJECT(credential_name => 'ODI_MIG',
        object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/<NAMESPACE>/b/<BUCKET_NAME>/o/< DUMP_FILE_NAME>',
        directory_name => 'DATA_PUMP_DIR',
        file_name => '<DUMP_FILE_NAME>');
   END;
   /
   

3. You can import the file using SQL Developer (Data Pump Wizard) or with the following OS-line command:

   impdp ADMIN/<PASSWORD>@<TNS_NAME> directory=data_pump_dir credential=ODI_MIG dumpfile=https://objectstorage.us-phoenix-1.oraclecloud.com/n/orasenatdpltinfomgmt01/b/MAGU_Bucket/o/MIG_ODI_REPO.dmp parallel=16 partition_options=merge table_exists_action=REPLACE transform=segment_attributes:n transform=dwcs_cvt_iots:y transform=constraint_use_default_index:y exclude=index,cluster,indextype,materialized_view,materialized_view_log,materialized_zonemap,db_link REMAP_SCHEMA=<SOURCE_SCHEMA>:<TARGET_SCHEMA>

Note:

This procedure is only applicable for ATP-D.

2.12 Verifying access to the Migrated Repository in ODI Marketplace Studio

Follow the below steps to verify access to the migrated repository in ODI Marketplace Studio:

1. Once the repository has been successfully imported, start ODI Studio in your new ODI Marketplace node.
2. Click Connect to Repository available on the left panel. The Oracle Data Integrator Login screen appears.
3. To modify the repository connection information, click on the pencil icon. The Repository Connection Information screen appears.
4. Modify the repository connection information as follows:
   1. Password (for SUPERVISOR): Change the Supervisor password to the one you had in your original ODI repository in the Oracle Data Integrator Cloud Service.
   2. Use Credential File: Select the check box.
   3. Credential File: Browse and select the wallet file in /home/opc directory.
   4. Connection Details: Select your low connection.
   5. Work Repository: Select Master Repository Only.
5. Click Test Connection.
6. On receiving a successful connection message, click OK.
7. Click OK in the Repository Connection Information screen.
8. In the Oracle Data Integrator Login screen, click OK.
9. Navigate to the Topology tab on the left panel.
10. Open the Repositories panel.
11. Expand the Work Repositories node and double-click WORKREP.
12. To change the connection information to the ADB repository, click the Connection Information button on the top left corner of the screen.
13. Provide the database user (ADB) for the repositories and its password. Then click the JDBC link on the left pane.
14. Provide the connection URL to the ADB server in the JDBC URL field and click Test Connection.
15. In the confirmation window, click Yes.
16. Click Test. Ensure that you do not change the Physical Agent specified here.
17. On receiving a successful connection message, click OK.
18. Disconnect from the repository by clicking on the unplug icon.
19. Click Connect to Repository available on the left panel. The Oracle Data Integrator Login screen appears.
20. To repoint the work repository, click on the pencil icon. The Repository Connection Information screen appears.
21. Select the Work Repository radio button at the bottom of the screen. Then click on the magnifying glass on the right side.
22. Select the work repository that is displayed in the Select Repository screen and click OK.
23. Click OK in the Repository Connection Information screen. You are now in your repository in ODI Marketplace.