Installation on Autonomous Database Instance

This section focuses on the following:

• Prerequisites

• Pre-Installation Steps

• Validating the Database Connection

• Data in CHAR

• Creating the Users

• Installing on the ADB Instance

• Validating the Product Installation

• Applying the Patch or Rollup

Prerequisites

Before installing on the ABD instance, make sure the following prerequisites are met:

• You should have prior knowledge of database (DBA) and Autonomous Database Instance (ADB).

• You should have ADB Instance admin credentials to connect to the database.

• ‘CISADM’ which is the admin schema of the product was used. However, if your environment has a different schema name, replace it in the SQL statements as necessary.

• After the database is provisioned, download the ADB wallet from the OCI console before installing the product.

Pre-Installation Steps

Before installing the product on the ADB instance, do the following:

1. Set up the ADB Wallet.

Create a directory and unzip the downloaded wallet in that directory. We must make changes to certain files in the wallet before installing the product. All the files mentioned below will be present under the wallet folder after you unzip it.

2. Edit the ojdbc.properties file.

a. Uncomment the following and update them accordingly:

javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks

javax.net.ssl.trustStorePassword=<password_from_console>

javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks

javax.net.ssl.keyStorePassword=<password_from_console>

b. Comment out the following:

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

c. Make sure the file looks as follows:

# Connection property while using Oracle wallets.

#oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

# FOLLOW THESE STEPS FOR USING JKS

# (1) Uncomment the following properties to use JKS.

# (2) Comment out the oracle.net.wallet_location property above

# (3) Set the correct password for both trustStorePassword and keyStorePassword.

# It's the password you specified when downloading the wallet from OCI Console or the Service Console.

javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks

javax.net.ssl.trustStorePassword=<password_from_console>

javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks

javax.net.ssl.keyStorePassword=<password_from_console>

Note: Update the Wallet password in the <password from console> field that was used while downloading the wallet.

3. Edit the sqlnet.ora file.

Replace “?/network/admin” with the downloaded wallet location. The file should look like this:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=/pathtoWallet)))

SSL_SERVER_DN_MATCH=yes

Example (Assuming the wallet location is /scratch/wallet):

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=/scratch/wallet)))

SSL_SERVER_DN_MATCH=yes

4. Set the TNS_ADMIN variable.

export TNS_ADMIN=<pathtoWallet>

Example:

export TNS_ADMIN=/scratch/wallet

Validating the Database Connection

Verify the connection to the database with TNS Name from the tnsnames.ora file.

sqlplus username/password@{service_name}_high

Example:

sqlplus admin/*********@orclADB_high

Data in CHAR

For the data to be in CHAR semantic, run the following trigger as admin user before installing the product:

CREATE OR REPLACE TRIGGER RCU_INSTALL_TRIGGER after logon on database

declare

user_name varchar2(100);

begin

select user into user_name from dual;

if ( user_name LIKE 'CIS%' ) THEN

execute immediate 'alter session set nls_length_semantics=CHAR';

END IF;

END;

Creating the Users

Create the following users and roles to proceed with the installation:

• Database User

• Read-Write User

• Read User

• Read-Write Role

• Read Role

Here, we assume CISADM as the DB user, CISUSER as the read-write user, CISREAD as the read-only user, CIS_USER as the user role, and CIS_READ as the read role.

CREATE ROLE CIS_READ;

CREATE ROLE CIS_USER;

GRANT CREATE SYNONYM TO CIS_USER;

GRANT CREATE SYNONYM TO CIS_READ;

CREATE USER CISADM IDENTIFIED BY <password> DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;

GRANT UNLIMITED TABLESPACE TO CISADM WITH ADMIN OPTION;

GRANT SELECT ANY TABLE TO CISADM;

GRANT CREATE DATABASE LINK TO CISADM;

GRANT CONNECT TO CISADM;

GRANT RESOURCE TO CISADM;

GRANT CREATE ANY SYNONYM TO CISADM;

GRANT SELECT ANY DICTIONARY TO CISADM;

GRANT CREATE MATERIALIZED VIEW TO CISADM;

GRANT SELECT ON V$SESSION TO CISADM;

GRANT SELECT ON DBA_USERS TO CISADM;

GRANT CREATE VIEW TO CISADM;

GRANT EXECUTE ON SYS.DBMS_RLS TO CISADM;

CREATE USER CISUSER PROFILE DEFAULT IDENTIFIED BY <password> DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP;

GRANT SELECT ANY TABLE TO CISUSER;

GRANT CIS_USER TO CISUSER;

GRANT CIS_READ TO CISUSER;

GRANT CONNECT TO CISUSER;

CREATE USER CISREAD IDENTIFIED BY <password> DEFAULT TABLESPACE DATA TEMPORARY TABLESPACE TEMP;

GRANT SELECT ANY TABLE TO CISREAD;

GRANT CIS_READ TO CISREAD;

GRANT CONNECT TO CISREAD;

Note: For information about restrictions on setting up the password, refer to the ADB documentation at https://docs.public.oneportal.content.oci.oraclecloud.com/en-us/iaas/autonomous-database/doc/managing-database-users.html#GUID-0E019845-31AE-44D7-B55C-9BCBA7E1377F

Installing on the ADB Instance

To install the product on the ADB instance:

1. Download the product and navigate to the folder where it is downloaded.

2. Export the following variables before installing the product:

a. Set JAVA_HOME.

Example:

export JAVA_HOME=<Java Install_Location>/jdk1.8.0

b. Set the CLASSPATH.

The jarfiles directory is shown in the Oracle Utilities Application Framework database blueprint.

Example:

export CLASSPATH=<path_to_OUAF_Blueprint>/DB/FW_V4.5.0.1.1/jarfiles/*

c. Set TNS_ADMIN.

Example:

export TNS_ADMIN={wallet_location}

3. Update the Storage.xml file to change the tablespace name from CISTS_01 to DATA.

Important! In an Autonomous Database, we cannot create the tablespaces manually, there is only one tablespace DATA available. Edit the Storage.xml file in the Blueprint and change the tablespace name to DATA.

4. Run the following command using the Command Line mode to install the product:

java com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<SERVICE_NAME>_low?TNS_ADMIN=<pathToWallet>,<DBUSER>,<DBPASS>,<RWUSER>,<RUSER>,<RW_USER_ROLE>,<R_USER_ROLE>,<DBUSER> -l 1,2 -p <RWUSER_PASS>,<RUSER_PASS>

Example:

java com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@orclADB_low?TNS_ADMIN=/scratch/wallet,CISADM,A11usr_123#,CISUSER,CISREAD,CIS_USER,CIS_READ,CISADM -l 1,2 -p A11usr_123#,A11usr_123#

5. Install the product with ILM partitioning on ADB.

To set up ILM partitioning on ADB, append “-x -t” at the end of the command mentioned in step 4 in this section.

Since we cannot create tablespaces, ‘-x’ parameter will skip creating tablespace and use the existing tablespace.

The ‘-t’ parameter is used to enable partitioning in ADB.

Example:

java com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@orclADB_low?TNS_ADMIN=/scratch/wallet,CISADM,A11usr_123#,CISUSER,CISREAD,CIS_USER,CIS_READ,CISADM -l 1,2 -p A11usr_123#,A11usr_123# -t -x

Validating the Product Installation

After installing the product, the following message is shown:

[main] INFO (oem.install.OraDBI) Process Completed Sucessfully, Now Exiting ...

Alternatively, you can validate the CI_INSTALL_PROD table by connecting to ADB.

Applying the Patch or Rollup

To apply the necessary patch or rollup to ADB:

1. Make sure to read the README documentation for the patch or rollup before proceeding.

2. In ADB, granting the DBA privileges to CISADM is restricted. So, the required permission should be granted from the admin user.

GRANT SELECT ON DBA_TABLESPACES TO CISADM;

3. Navigate to the directory where the patch or rollup is downloaded. Run the following:

./ouafDatabasePatch.sh -x <DBPASS> -p "-t O -d <DBUSER>,jdbc:oracle:thin:@<serviceName>_low?TNS_ADMIN=<pathToWallet> -u <RWUSER>,<RUSER> -o <RW_USER_ROLE>,<R_USER_ROLE> -q -r"

Example:

./ouafDatabasePatch.sh -x A11usr_123# -p "-t O -d CISADM,jdbc:oracle:thin:@orclADB_low?TNS_ADMIN=/scratch/wallet -u CISUSER,CISREAD -o CIS_USER,CIS_READ -q -r"