5 EBS Database Installation Tasks

This chapter includes:

• Prerequisite

• Copy EBS Specific Files

• Create Tablespaces

• Create the ODI Staging Database User

• Create Database Link

• Run the EBS Initial Database Setup

• Verification

Prerequisite

It is assumed Oracle database has already been configured and loaded with the appropriate EBS schema details for your installation. Skip the entire section if you are integrating with PeopleSoft.

Copy EBS Specific Files

Note: You can skip the next steps and proceed to section Create Tablespaces if you can connect to EBS Database from ORFI Server using Sqlplus.

1. Log in to the EBS UNIX server as a user who has sufficient access to run sqlplus from the Oracle Database installation for APPS schema.

2. Create the retail-financial-integration-solution/service-based-integration folder in the <INSTALL_DIR> folder.

3. From the ORFI server, copy <INSTALL_DIR>/retail-financial-integration-solution/service-based-integration/setup-data folder to <INSTALL_DIR>/retail-financial-integration-solution/service-based-integration folder in EBS server.

Create Tablespaces

Before you run the ODI Staging database user, make sure that the following tablespace has been created in the database: ODI_STAGE_DATA. Below is a sample tablespace creation statement for this tablespace. Oracle Retail recommends the use of locally managed tablespaces with automatic extent and segment space management.

Note: These tablespaces are not sized for a production environment. The customer's Database Administrator should create the tablespace based on their datacenter database architecture and size it appropriately based on expected data volume for their peak data usage.

CREATE TABLESPACE ODI_STAGE_DATA DATAFILE
'<datafile_path>/odi_stage_data01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
;

Create the ODI Staging Database User

You must create an ODI Staging schema using a create_user.sql, included with the installer distribution. This will be used to execute the ODI Interface at run time. The create_user.sql script can be used to create the schema:

$INSTALL_DIR/retail-financial-integration-solution/service-based-integration/setup-data/ddl/create_user.sql

The script takes three arguments on the command line in sqlplus: username, password, and ODI Stage tablespace.

For example:

SQL> @create_user.sql RFI_ODI_TEMP <RFI_ODI_TEMP Schema Password> <ODI Stage Tablespace name>

Please review this script and run it as a user with adequate permissions, such as SYSTEM.

Connect to RFI_ODI_TEMP schema as sysdba and grant all privileges to AP schema available on EBS Database. Grant all privileges will be used for CRUD operation in RFI_ODI_TEMP tables while running ODI interfaces.

Create Database Link

You need to create a database link to load initial data for currency rate and suppliers from Oracle E-Business Suite to the Oracle Retail Financial Integration application.

1. Log in to the APPS database schema in the EBS database to run create_dblink.sql script.

2. Run the create_dblink.sql script available in $INSTALL_DIR/retail-financial-integration-solution/service-based-integration/setup-data/ddl/ from EBS APPS database schema.

   The script takes four arguments on the command line: DB link name (EBS_RFI_DBLINK), ORFI schema name, ORFI schema password, and ORFI DB URL.

   For example:

   SQL> @create_dblink.sql <EBS_RFI_DBLINK> <ORFI Schema> <ORFI Schema Password> <ORFI DB Host:port/SID>

   
   

   Note: This database link will be used to load initial data for Currency Rate and Supplier from EBS to ORFI Staging tables. If the database link name is different from the default (EBS_RFI_DBLINK), then the user needs to update the database link name in the SQL scripts which are available in retail-financial-integration-solution/etl-based-integration/ retail-to-ebsfin-etl-flows/setup-data/dml.

   
   

Run the EBS Initial Database Setup

This installer installs the ORFI related objects to the EBS APPS database schema.

Note: Ensure, sqlplus is executable with correct tns configuration information to EBS database is set in your path.

1. 1. Run the load-initial-setup-data-into-ebsfin-schema.sh script available in $INSTALL_DIR/ retail-financial-integration-solution/service-based-integration /bin/. After installation is completed, a detailed installation log file is created: $INSTALL_DIR/ retail-financial-integration-solution/service-based-integration/logs //<timestamp>.log.

   For example: prompt$ sh load-initial-setup-data-into-ebsfin-schema.sh

   sh load-initial-setup-data-into-ebsfin-schema.sh
   

2. The installer prompts for the following details:

   • Enter the EBS database server SID - Enter Database SID.

   • Enter the EBS Schema details -Enter APPS User ID/Password.

   • Enter the EBS database server name - Enter Database Server Name or IP address.

   • Enter the EBS database server port - Enter Database Port Number.

   
   

   Note: If you get the following error, make sure you have downloaded latest version of the ORFI software from Oracle download site and tns is configured. ERROR: ORA-12154: TNS: could not resolve the connect identifier specified.

   
   

Verification

Log in to the APPS database schema in the EBS database and verify that the RFI_GLACCT package and package body are available in the valid status using query:

"SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE IN ('PACKAGE','PACKAGE BODY') AND OBJECT_NAME='RFI_GLACCT';".