Configure API Portal with an Oracle Database
To customize the API Portal with Oracle 11g, Oracle 12c, or Oracle 19c database you need the following components:
An operating Oracle database.
ojdbc driver jar, which can be downloaded from Oracle website to a directory of your choice in the machine where
API Portal is installed. Example: ojdbc6.jar.
Note:
For Oracle 12c and Oracle 19c with API Portal 10.5, download ojdbc8.jar driver.
SQL scripts and all additional files. These scripts can be downloaded from the
ARIS Download Center.
The SQL scripts creates a database and necessary database objects required by the API Portal components.
Note:
Database scripts should be executed from the machine where sqlplus client is running.
To configure
API Portal with an Oracle Database
1. Back up data from API Portal.
a. Log on to API Portal as an Administrator.
b. Click 
in the right top corner of the API Portal window to display the menu options. c. Click Administration > Manage data.
d. Select Backup.
e. Select the relevant options and click Backup.
A success message appears when the backup process is completed. The backup file with an extension .acb is created and saved in the downloads section. You can move the file and save it in another location of your choice.
2. Open API Portal Cloud Controller and run the following commands.
stopall
deconfigure postgres_s
start zoo_s
3. To configure envset.bat, login to the machine where oracle server is running, and go to the directory where the script files are downloaded from ARIS Download centre. Scripts are present in the folder, download_root_folder\ARIS.xxx.DatabaseScripts\DatabaseScripts\Design&ConnectServer\oracle
4. Open the envset.bat file, modify the following fields, and save the file:
SET CIP_ORA_BIN_PATH=
Path where sqlplus.exe can be found (for example C:\app\<username>\product\11.2.0\<dbname>\BIN)
SET TARGET_HOST=
DB Server Name (Machine name in which Oracle server is running)SET TARGET_PORT=
Port (Port in which oracle server is running. Example: 1521)SET TARGET_SERVICE_NAME=
Service name (Name of the oracle service. Example: XE for oracle 11g)SET CIP_INSTALL_USER=
User Name (Database administrator username)SET CIP_INSTALL_PWD=
Password (Database administrator password)SET CIP_TS_DATA=
Table space name (Table space which is already present in the database)SET CIP_APP_USER=
Username (User that will be used by the application. Example: dbuser)SET CIP_APP_PWD=
Password (Password of the application user. Example: dbuser123)SET CIP_TENANT_SCHEMA_PWD=
Password (Password used for tenant schemas. Example: dbuser123)5. Before running the database scripts ensure that the Oracle query tool (sqlplus) is available in the command prompt. Run the envset.bat file.
6. Run cip_create_app_user.bat file. This creates the application user, which was specified in envset.bat file.
For Oracle 12c and Oracle 19c, the following changes should be made for error free execution of the scripts files,
a. To avoid ORA-65096: invalid common user or role name error during schema creation, open cip_create_empty_tenant_schema.sql and cip_create_app_user.sql files, and add the following after "set verify off", alter session set "_ORACLE_SCRIPT"=true;
b. If complex password policy is enabled by default in the database and the application user password does not comply to it, an error message displays, while creating the tenant schema. To avoid this, open cip_create_empty_tenant_schema.sql file and add the following after "BEGIN",
EXECUTE IMMEDIATE 'ALTER PROFILE default LIMIT
PASSWORD_VERIFY_FUNCTION null';
7. To create database schema for the tenants default and master, run the following commands in a command line.
cip_create_schema_for_tenant.bat CIP_MASTER cip_create_schema_for_tenant.bat CIP_DEFAULT Note:
You can use dbvisualizer to ensure that the schemas are created.
8. Switch to the machine where API Portal is installed. Add the JDBC drivers to API Portal classpath.
a. Start API Portal Cloud Controller.
b. Run the following command:
enhance apiportalbundle_s with commonsClasspath
local file "location of ojdbc file"
Example:
ACC+ localhost>enhance apiportalbundle_s with commonsClasspath
local file "C:/jdbc/jar/ojdbc8.jar"
9. Register the external service database.
a. In API Portal Cloud Controller, run the following commands:
register external service db url="jdbc:oracle:thin:@
servername:port/servicename"
driverClassName=oracle.jdbc.OracleDriver jmxEnabled=true maxActive=100
maxIdle=15 logAbandoned=true rollbackOnReturn=true maxWait=10000
removeAbandoned=false defaultAutoCommit=false
username=Application Username password=Application User password
host=servername
An external service identifier is returned once the above command is executed, for example, it returns the service id as db0000000000.
b. Run the following command to assign the service to the default and master tenants:
assign tenant default to service db0000000000
com.aris.cip.db.schema=CIP_DEFAULT
assign tenant master to service db0000000000
com.aris.cip.db.schema=CIP_MASTER
10. In API Portal Cloud Controller, run the following command to start all the runnables:
startall
11. Restore the backed up data.
a. Log on to API Portal as an Administrator.
b. Click in the right top corner of the API Portal window to display the menu options. c. Click Administration > Manage data.
d. Select Restore.
e. Click Upload and select the relevant backup file to be uploaded.
f. Select the relevant options and click Restore.
A success message appears when the restore process is completed.
If you want to create a new tenant you can do the following:
Stop all runnables except zoo_s
Run the following commands:
Switch to the machine where Oracle server is running. Open sqlplus cmd and execute the following command,
cip_create_schema_for_tenant.bat CIP_TENANTNAME
Switch the machine where API Portal is installed and run the following command in ACC console, assign tenant default to service db0000000000
assign tenant TENANTNAME to service db0000000000
com.aris.cip.db.schema=CIP_TENANTNAME
After running the above command, start all runnables.
Run the following commands in ACC console to create a new tenant:
ACC + localhost> create tenant tenant_name
master.tenant.user.name=system master.tenant.user.pwd=manager
ACC + localhost> invoke enhancement_importLicense on apiportalbundle_s
tenant.name=tenant_name local file enhancement.path="Install_dir/API_Portal/license.xml"
tenant.user.name=system tenant.user.pwd=manager
ACC + localhost> invoke prepareTenant on apiportalbundle_s
tenant.name=tenant_name isDemo=false