How to create ODI Repository on Autonomous Database

16848 ワード
oraclecloud oci autonomous_database ODI oci テキストリンク

Introduction
In my previous blog, I've introduced how to launch an ODI instance from OCI Marketplace. This time I will show you how to create ODI repository (Master/Work) on Autonomous Database.

Validation Environment
ODI Marketplace (12.2.1.4)
Autonomous Data Warehouse

Steps
1. Create an User on Autonomous DB
2. Download ADB wallet file and upload to ODI instance
3. Create a New Master Repository
4. Connect to the Master Repository
5. Create a Work Repository
6. Connect to the Work Repository

Please note this method is NOT applicable to ODI On-P, because saving repository on ADB is not supported for ODI On-P.

1. Create an User on Autonomous DB

Login as admin user, create a new user for ODI repository. (I use SQL Developer for this test.)

Grant "DWROLE" to the user.

Grant "PDB_DBA" to the user with "Admin" option.

Grant unlimited tablespace to the user.

Click "Apply" button to create user.

User created.

Here is the SQL script.

-- USER SQL
CREATE USER "ODI_REPO" IDENTIFIED BY "<Password>"  
DEFAULT TABLESPACE "DATA"
TEMPORARY TABLESPACE "TEMP";

-- QUOTAS
ALTER USER "ODI_REPO" QUOTA UNLIMITED ON "DATA";

-- ROLES
GRANT "PDB_DBA" TO "ODI_REPO" WITH ADMIN OPTION;
GRANT "DWROLE" TO "ODI_REPO" ;

2. Download ADB wallet file and upload to ODI instance

Autonomous Database -> Autonomous Database Details -> DB Connection
Select "Wallet Type", then click "Download wallet" button.

Enter password, then begin to download.

I'll skip the step of uploading wallet to ODI instance. You can do it with any FTP/SSH tool you like. Save the wallet file under /home/oracle/.odi/oracledi/ewallet. (NO need to unzip it.)

[opc@oracle-odi-inst-wc4b ~]$ pwd
/home/opc
[opc@oracle-odi-inst-wc4b ~]$ ls -l Wallet_ADW2021.zip
-rw-rw-r--. 1 opc opc 21617 Nov  5 06:49 Wallet_ADW2021.zip
[opc@oracle-odi-inst-wc4b ~]$ sudo mv Wallet_ADW2021.zip /home/oracle/.odi/oracledi/ewallet/
[opc@oracle-odi-inst-wc4b ~]$ sudo chown oracle:oracle /home/oracle/.odi/oracledi/ewallet/Wallet_ADW2021.zip
[opc@oracle-odi-inst-wc4b ~]$ sudo ls -l /home/oracle/.odi/oracledi/ewallet/Wallet_ADW2021.zip
-rw-rw-r--. 1 oracle oracle 21617 Nov  5 06:49 /home/oracle/.odi/oracledi/ewallet/Wallet_ADW2021.zip
[opc@oracle-odi-inst-wc4b ~]$

3. Create a New Master Repository

Startup ODI Studio, begin to create repo with following steps.
Menu Bar -> File -> New -> Create a New Master Repository

Enter connection information as below:

Item Comment
Technology Oracle (Default)
JDBC Driver oracle.jdbc.OracleDriver (Default)
Use Credential File Check it
Credential File Select ADB wallet zip file
Connection Details Select from <ADB_Name_high>,<ADB_Name_medium>,<ADB_Name_low>
Use Http Proxy Not checked by default. (I'll pass it.)
JDBC URL No need to edit.
User Schema of ODI repository
Password Password of above schema for ODI repository
DBA User admin
DBA Password Password of ADB admin user.

Click "Test Connection" to test.

After you get a successful connection, click "Next" button to proceed.

You can choose to use "ODI Authentication"(By default) or "External Authentication".
In the case of "ODI Authentication", you need to create password for "Supervisor User".
After entering required information, then click "Next" to proceed.

You can choose to store password on Internal or External storage. I choose "Internal Password Storage"(By default) for this test.
When "Finish" button is clicked, ODI begins to create master repository.

Creation of master repository takes about 4~5 minutes.

After creating, you'll get information like this.

4. Connect to the Master Repository

Security Tab -> Connect to Repository -> "+" (Plus icon)

Enter connection information as below.

Item Comment
Login Name Enter a login name for master repository.
User(ODI Connection) SUPERVISOR
Password Enter supervisor's password.
User(DB Connection) ODI_REPO (You created on STEP-1.)
Password Enter password of above user "ODI_REPO".
Driver List Oracle JDBC Driver (Default)
Driver Name oracle.jdbc.OracleDriver (Default)
Use Credential File Check it
Credential File Select ADB wallet zip file.
Connection Details Select from <ADB_Name_high>,<ADB_Name_medium>,<ADB_Name_low>
Use Http Proxy Not checked by default. (I'll pass it.)
URL No need to edit.
Master Repository Only Check it

Click "Test Connection" to test.

When you get a successful connection, click "OK" to exit connection window.

You can store password in secure wallet. I'll pass it.

Confirm login information, then click "OK" to login (To the master repository).

5. Create a Work Repository

Topology Tab -> Repositories -> Work Repositories -> New Work Repository

Connection information is displayed as following by default. Usually, you don't need to edit. After confirmation, click "Test Connection" button to test.

When you get a successful connection, press "Next" button to proceed.

After enter following information, click "Finish" button to begin creating work repository.

Item Comment
Name WORKREP1 (By default)
Password Enter(Create) a password. (No re-input for confirmation.)
Work Repository Type Select "Development"(By default) or "Execution".

When work repository is created, you can create a new login for it (Press "Yes".)

Enter "Login Name" for work repository.

6. Connect to the Work Repository

Disconnect from master repository. (Designer Tab -> Disconnect)

Designer Tab -> Connect to Repository -> Select Login Name for work repository -> Click Edit icon

Enter supervisor password, select ADB wallet file, then press "Test" button to test connection.

When you get a successful connection, press "OK" to close the connection window.
It took me a little long time to pass this test at first time.

Designer Tab -> Connect to Repository -> Select Login Name for work repository -> Click OK button

Now you're connected to work repository.

End

Related Blogs
Personal Blogs on Oracle Cloud Infrastructure
ODI On-P vs ODI Marketplace vs OCI Data Integration
How to launch ODI instance from OCI Marketplace
Autonomous DBにODIレポジトリの作成方法 (Japanese version)