2. Setup and Configuration of the Database Server

2.1 Creating Database

The first step in the database creation is the creation of Database Server Users and Groups. The below sections are a guide to:

1. Creating the Database Server Users and Groups
2. Creating Database

2.1.1 Creating the Database Server Users and Groups

The software and processes on the database server can be broken into three groups of ownership. These are; the Oracle processes, the Oracle external procedure processes, and the home directory. Some of this information applies only to UNIX server installations. The typical Windows server Application installation does not make use of separate users and groups, for the various processes and instead runs everything as the system user.

Process Group	Description
Oracle	The Oracle processes are the database processes (excluding the listener process) responsible for data­base operations. Normally, the Oracle processes are owned by a user named oracle and a group called dba. Some of the processes use an internal Oracle PL/SQL utility called UTL_FILE to read and/or write to files in the home directory. The UTL_FILE program will per­form its functions as the oracle user, hence, add the oracle user to the Oracle Financial Services Lending and Leasing user’s group.
Oracle External Procedures	Oracle Financial Services Lending and Leasing uses external procedures to perform tasks that cannot (or does not want to) be performed inside the database. Such as, interfacing with the credit bureaus, writing files, or converting images. Oracle implements external procedures through its listener process. Multiple listen­ers can be defined (one for “normal” database connec­tions and one for external procedures) or run a single listener that handles both types of requests. Regard­less of what is done, the external procedures will need access to files and directories that are within the home directory. It is recommended that the owner of the lis­tener process handling external procedures is either the Oracle Financial Services Lending and Leasing user or a part of the same group.
Oracle Financial Services Lending and Leasing home directory	The home directory is a top-level directory under which some application files are stored. For example, log files, document templates, email templates, temporary files, and output files. There are no processes or pro­grams that run (outside of the Oracle external proce­dure processes) as the Oracle Financial Services Lending and Leasing user, this is the location where the server side application files reside. Oracle Financial Services Lending and Leasing administrator can log into the server and perform the administrative functions without needing any special system superuser or administrative privileges. The home directory is needed for each application instance (production, test, development) installed on the server.

Keeping this information in mind, here are the recommended users and groups for the server:

User	Group(s)	Description
ofsll	ofsll	Owns the home directory. Set up the directory with, group write privileges so that other application related processes can access the files and directories below it.
oracle	dba, ofsll	Owns the Oracle database processes.
ofsllext	ofsll	Owns the Oracle listener process or just the external procedure listener process. If the latter, run the “normal” SQL*Net listener process as the oracle user.

2.1.2 Creating the Database

Create a database as per corporate standards and/or Oracle best practices with the following recommendations/guidelines.

• Set NLS Characterset as AL32UTF8.
• Ensure that the external procedure listener has been configured. Set the EXTPROC_DLLS environment variable so that the external procedure listener can access the Oracle Financial Services Lending and Leasing external procedure shared libraries. A section from a listener.ora file and a tnsnames.ora file is given below as an example. This sets up the external procedure listener as a separate listener named LEXT. See the Oracle Net Services documentation for more information on setting up an external procedure listener.

listener.ora

For Unix

2.2 Installing the Application Database

Download and unzip the Application Database file (ofslldb.zip) to a staging folder.

The application database software consists of tables, indexes, types, directories, libraries, views, and packages. A script program creates the application home directory structure, database user, the required tablespaces, database objects and loads the seed data. Snap shots of the script are provided after each step, to enable easy understanding. The steps involved in installing the application database are:

1. Running the installation script
2. Selecting the Install Type
3. Setting up the Installation Environment
4. Creating Application Home directory
5. Creating Application Tablespaces
6. Creating the Application Owner User and Grant Tablespace Quotas
7. Installing Database Objects and Seed Data
8. Installing Library Objects
9. Installing Directory Objects

2.2.1 Running the installation script

From the unzipped ‘Application Database file’ folder, run the install script from a UNIX shell prompt and follow the on screen prompts to install the Oracle Financial Services Lending and Leasing infrastructure database objects.

On Unix:

$ ./installofslldb.sh

When the script prompts for confirmation, enter ‘y’ to continue.

2.2.2 Selecting the Install Type

The script displays install option..

The script prompts to ‘Choose an installer option? [1-3]’.

• Enter ‘1’ for complete installation of Product. When entered, the script runs installer for the complete installation of the product. The below sections guide you on the process, in detail.
• Enter ‘2’ for upgrade installation. When entered, the script runs installer to upgrade the existing version of the product. Installing Upgrade section guides you on the upgrade installation.
• Enter ‘3’ for only DB Object installation. When entered, the script runs installer to install all the OFSLL database objects. The script assumes that the user (OFSLL schema owner) and the tablespaces are created and all the required privileges are granted to the schema.

2.2.3 Setting up the Installation Environment

The installation script requires a properly set up environment in order to run. The script requests for few details. Enter the values as listed below. The script requests confirmation after each entry, enter ‘y’ to confirm.

Script Prompts	Description and Action Required
Oracle Financial Services Lending and Leasing Home Path	Enter the path to the application home directory. This is referred to as $OFSLL_HOME.
Oracle DB Home Path	Enter the path to the Oracle DB home directory. This is referred to as $ORACLE_HOME
Oracle SID	Enter the Name of Oracle Instance. In case of multitenant environment, enter the PDB name'
Path	Sets the path that includes $ORACLE_HOME/bin as the installation script requires SQL*Plus utility

When the script prompts for the confirmation, enter ‘y’ to continue.

2.2.4 Creating Application Home directory

The install script will create a proper directory structure for the application below the user’s home directory and will set the proper permissions on the directories.

$OFSLL_HOME

Variable		Description
/api		Stores the captured Applications/Account data from any third party Origination/Servicing System and setup the account in OFSLL
/acct_doc_load		Input load directory for account documents
/bin		Contains executable scripts
/cor_storage		Top level directory for generated correspondences
/doc_templates		Correspondence document template load directory
/email_templates		Contains email message templates
/dot_storage		Top level directory for account documents
/cus_dot_storage		Top level directory for customer/business documents
/images		Contains fax / document images uploaded to OFSLL screen
/lib		Contains external procedure shared libraries
/logs		Contains all Oracle Financial Services Lending and Leasing log files
/output		Destination for Oracle Financial Services Lending and Leas­ing output data files.
/rs_archive		Top level directory for archived reports
/sql		Directory contain SQL scripts used for creating database objects, recompiling packages, and various utility functions.
/tmp		Temporary directory used by some external procedures.
/input		Repository for data files needed as input to Oracle Financial Services Lending and Leasing processes
/input/lockbox		Contains files for lockbox processing.
/input/lockbox/processed		Contains files that are already processed.
/input/adr		Contains incoming adr files.
/input/adr/processed		Contains files that are already processed.
/input/cac		Contains incoming call activity files.
/input/cac/processed		Contains files that are already processed.
/input/ifd		Contains incoming lien title tracking files.
/input/ifd/processed		Contains files that are already processed.
/input/itu		Contains incoming ITU files.
/input/itu/processed		Contains files that are already processed.
/input/ivr		Contains incoming IVR files.
/input/ivr/processed		Contains files that are already processed.
/input/wfp		Contains incoming wholesale floor planning files.
/input/wfp/processed		Contains files that are already processed.
/input/ibn		Contains incoming BANKO_NEW files.
/input/ibn/processed		Contains files that are already processed.
/input/ibu		Contains incoming BANKO_UPDATE files.
/input/ibu/processed		Contains files that are already processed.
/input/ice		Contains currency exchange files.
/input/ice/processed		Contains files that are already processed.
/input/icl		Contains cure letter files.
/input/icl/processed		Contains files that are already processed.
/input/ipr		Contains AP Transaction History files.
/input/ipr/processed		Contains files that are already processed.
/input/ist		Contains files for Input Sale Transfer.
/input/ist/processed		Contains files that are already processed.
/input/ipu		Contains files for Payment Upload.
/input/ipu/processed		Contains files that are already processed.
/input/ipi		Contains files for Personally Identifiable Information (PII).
/input/ipi/processed		Contains files that are already processed.
input/iuh		Contains files for Input Usage History.
input/iuh/processed		Contains files that are already processed.
input/ifc		Contains files for Offline Cross Upsell Activity Posting.
input/ifc/processed		Contains files that are already processed.
input/icp		Contains files for Customer Based Payment Upload.
input/icp/processed		Contains files that are already processed.
input/icc		Contains files for Collateral Management Upload.
input/icc/processed		Contains files that are already processed.
input/isc		Contains files for Securitization Pool Upload.
input/isc/processed		Contains files that are already processed.
input/iar		Contains Asset Billing Rate Setup Create/Update file upload.
input/iar/processed		Contains files that are already processed.
input/api		Contains Upload Legacy Account Information.
input/api/processed		Contains files that are already processed.
input/itr		Contains files for tracking attribute upload process.
input/itr/processed		Contains files that are already processed.
input/ipc		Contains files for Customer Payment Upload.
input/ipc/processed		Contains files that are already processed.
input/gls		Contains files for GL Attributes Upload.
input/gls/processed		Contains files that are already processed.
input/prp		Contains files for Product Pricing Upload.
input/prp/processed		Contains files that are already processed.

2.2.5 Creating Application Tablespaces

The Oracle Financial Services Lending and Leasing table and index creation DDL allow for the use of up to 22 different tablespaces --11 for tables and 11 for indexes. The DDL commands reference a set of files that contain storage parameter information. These files allow mapping of logical tablespace names to physical tablespaces. Create as many of these tablespaces as necessary depending on the storage resources. Use locally managed tablespaces with a uniform extent policy. The following table describes different tablespaces, their content, and their expected growth pattern.

Tablespace	Storage Parameter File	Default Tablespace Name	Data	Comment
Admin	storage_parms_table_adm.sql storage_parms_index_adm.sql	OFSLL_ADM_DATA OFSLL_ADM_INDEX	setup data	low growth, low change.
Origination	storage_parms_table_org.sql storage_parms_index_org.sql	OFSLL _ORG_DATA OFSLL _ORG_INDEX	loan application data	growth varies by customer, few updates.
Servicing	storage_parms_table_svc.sql storage_parms_index_svc.sql	OFSLL _SVC_DATA OFSLL _SVC_INDEX	non transaction account data	growth varies by customer, few updates.
Transactions	storage_parms_table_txn.sql storage_parms_index_txn.sql	OFSLL _TXN_DATA OFSLL _TXN_INDEX	transaction data	high growth (relative to account and application data table spaces)
API	storage_parms_table_api.sql storage_parms_index_api.sql	OFSLL _API_DATA OFSLL _API_INDEX	application/account data used during imports/conversions	high growth, one time usage, data can be removed after loading
Archive	storage_parms_table_arc.sql storage_parms_index_arc.sql	OFSLL _ARC_DATA OFSLL _ARC_INDEX	Archived application/account data	steady growth, potentially very large
Common	storage_parms_table_cmn.sql storage_parms_index_cmn.sql	OFSLL _CMN_DATA OFSLL_CMN_INDEX	common non-admin data	generally low growth, some tables can be periodically truncated
Input Process	storage_parms_table_ipf.sql storage_parms_index_ipf.sql	OFSLL_IPF_DATA OFSLL_IPF_INDEX	Input processing file	steady growth, potentially very large
Output Process	storage_parms_table_opf.sql storage_parms_index_opf.sql	OFSLL_OPF_DATA OFSLL_OPF_INDEX	Output processing file	steady growth, potentially very large
Logging	storage_parms_table_log.sql storage_parms_index_log.sql	OFSLL_LOG_DATA OFSLL_LOG_INDEX	Error Logging	steady growth
Audit	storage_parms_table_aud.sql storage_parms_index_aud.sql	OFSLL_AUD_DATA OFSLL_AUD_INDEX	Audit Logging	Growth depends on enabling number of audits fields.

 

When prompted to ‘Select a number’ to redefine the tablespace name, you have 2 options:

Accept default Tablespace Name and Size: Select # 21

Modify the default Tablespace Name and Size: Select the number of the tablespace, between 1–20, you need to modify

Script Prompts	Description and Action Required
Enter new tablespace name	Enter tablespace name you need to assign to the default tablespace you have selected.
Enter tablespace size (in MB)	Enter the size (in MB) you need to assign to the new tablespace name you have entered

Repeat this process for all the tablespace names you need to modify

Enter # 21 to redefine and to apply changes for the tablespaces you modified.

Script Prompts	Description and Action Required
Oracle tablespace path	Installed Database path that stores DBF files. Once entered the script prompts for confirmation. Enter ‘y’, if it is ok
Oracle User ID that will own the Oracle Financial Services Lending and Leasing objects	Valid User ID
Password for this User ID	Valid Password
Oracle sysdba User ID	Valid User ID
Password for sysdba User ID	Valid Password

2.2.6 Creating the Application Owner User and Grant Tablespace Quotas

Oracle Financial Services Lending and Leasing application requires a single Oracle user (or schema) and this user is the application owner. The application owner user owns all of the tables, indexes, views, sequences, packages, etc. that make up the application.

The user is then granted access to the application tablespaces.

2.2.7 Installing Database Objects and Seed Data

Once the User ID is created, the script initiates the database object installation and lists the objects available for installation.

When the script prompts for the confirmation, enter ‘y’ to continue.

2.2.8 Installing Library Objects

Once the confirmation is received, the script initiates installation of library objects. A sample of the script is given below. ­

Script Prompts	Description and Action Required
Shared Library Directory	The path for the library directory. The default value is $OFSLL_HOME/lib

2.2.9 Installing Directory Objects

The fax images, correspondence documents, and the account document images are stored as BFILE objects. With BFILE, the image or document is physically stored outside the database on a file system available either on the database server or to the database server. A pointer to the file consisting an Oracle directory object and a file name is stored in the database. Application programs that create the BFILEs use application parameters to determine the specific directory object name. The application parameters are company level parameters, hence, a different directory object can be created for each defined company and branch.

Choose a name that identifies the usage, company, and branch for the directory object. Since directory objects are for the entire database and not just for the application, it is suggested that the schema name or identifier is used in the name to identify it from other directory objects. Choose a directory path with enough storage space to handle the expected volume of correspondences.

Directory Object Types	Abbreviation for Object Types	Suggested installation Location in $OFSLL_HOME
Correspondence interface directory objects	COR	cor_storage
Account documents interface directory objects	DOT	dot_storage

Loading Seed Data

The factory shipped seed data is automatically uploaded during installation and once complete, a confirmation message is displayed as indicated below:

2.3 Modifying Application System Parameters

Several system parameters in the seed data require modification to fit the local installation environment. One such parameter must be set manually. You can modify the rest using the application system parameter setup screen (Setup > Administration > System > System Parameter).

Setting the CMN_SERVER_HOME parameter values

As mentioned, the CMN_SERVER_HOME parameters must be set manually before the application screens can be used to set other parameters.

CMN_SERVER_HOME

Set it to the $OFSLL_HOME directory.

Setting the remaining system parameters manually or from the setup screen

Set the below parameters which are located on the Setup→Administration→System→System Parameters screen. This is not a complete list of system parameters, but is a list of system parameters related to installation details. The other parameters are related to application business functionality. The values for many of these parameters are known only after the installation and configuration of application server.

1. CMN_SER_ENVIRONMENT_FILE

• Set it to the full path of the text file that contains environment settings that will be used when running external commands from the job service. For example, $OFSLL_HOME/ofsllenv

2. CMN_SERVER_TEMP_DIR

• Set it to the full path of a directory that will be used to store temporary files needed during certain procedures. This is typically a directory named “tmp” underneath the $OFSLL_HOME directory (ex. $OFSLL_HOME\tmp or $OFSLL_HOME/tmp).

3. ADMIN_SERVER_URL

• This parameter is used to enable changing the user-level passwords. The default parameter value will generally have the form:

Set the t3://hostname:port/console (Host name and Port) where application is installed

4. UIX_DEFAULT_IMAGE_PATH

• Set the path to the fax / document images that are uploaded to OFSLL screen. Typically, it is $OFSLL_HOME/images.

5. Update system parameters with Oracle directory objects used by the application.

• Login as an application schema owner and execute the following sql file from the dba_utils folder - ‘update_sys_parms.sql’.

Modifying Oracle Analytics Publisher Parameters

1. JSV_REPORTS_SERVER_URL

Set this to the URL to the job service should use when running a report. Reports are all submitted as a background job (either by a user or by the job scheduler). These reports will be sent to a printer/file system as chosen by the user or as configured in the job setup.  The parameter value will generally have the form: http://hostname:port/ (Host name and Port number of xmlp server where BIP is installed)

2. JSV_USE_REPORTS_SERVER

Set to ‘Yes

3. JSV_REPORT_ARCHIVE_DIRECTORY

Set to a directory that will be used to store OFSLL report PDFs. Typically it is $OFSLL_HOME/rs_archive

Batch reports can be configured to print to a special “archive” printer that will create a PDF of the report and store it below the JSV_REPORT_ARCHIVE_DIRECTORY.  This directory must be accessible to the database server machine.  All archived reports are stored by a process running on the database server machine and not by a process running on the report server machine.

The report PDFs are stored below the JSV_REPORT_ARCHIVE_DIRECTORY in a subdirectory representing the type of report (report or correspondence), the year, the month, and the day that the report was printed.  For example, if a report was printed on December 15, 2013, the PDF file will be located in: JSV_REPORT_ARCHIVE_DIRECTORY/reports/2013/12/15

4. JSV_ARCHIVE_SERVER_URL

Set this to the URL that the job service should use when running an archived report. Archive reports are all reports submitted as a background job (either by a user or by the job scheduler) to a special printer named ARCHIVE. The parameter value will generally have the form: http://hostname:port/ (Host name and Port number of xmlp server where BIP is installed)

This can have the same value as the JSV_REPORTS_SERVER_URL parameter. This additional parameter is supplied in case there are multiple report servers and it is desirable to send archive requests to a different server than the other report requests.

5. JSV_SMTP_SERVER - smtp mail router of organization
6. JSV_USE_BI_PUBLISHER - Set to YES (Y)
7. JSV_BI_USER - set as the BI Publisher Admin User Name (Required only if the system parameter ‘OUTBOUND_CALL_Q’ is set to ‘N’).
8. JSV_BI_PASSWORD - set as the BI Publisher Admin Password (Required only if the system parameter ‘OUTBOUND_CALL_Q’ is set to ‘N’).
9. UIX_REPORTS_SERVER_URL

Set this to the URL that user interface should use when running a report.  The parameter value will generally have the form: http://hostname:port/ (Host name and Port number of xmlp server where BIP is installed). This can have the same value as the JSV_REPORTS_SERVER_URL parameter.

Setting the correspondence interface company parameters manually or from the setup screen.

If using the correspondence interface, set these additional parameters located on the Setup→Administration→Company→System Parameters screen. These parameters are company level parameters, which means that a correspondence interface can be configured for each company in Oracle Financial Services Lending and Leasing.

COR_STORAGE_DIRECTORY

Set to the name of the Oracle directory object, created during installation, which defines where the generated correspondences will be stored. The correspondence files are stored in the database as a BFILE which means that only a reference to an external file is stored in the database and the actual file is stored outside on the operating system’s file system in a path defined by an Oracle directory object. This parameter is the name of the Oracle object, not the actual directory path. Typical naming convention used for COR directory objects is - COR_DIR_<application owner name>_company_branch. For example: COR_DIR_OFSLLREL_HQ

Setting the account documents interface company parameters manually or from the setup screen.

If using the account documents interface, set these additional parameters located on the Setup→Administration→Company→System Parameters screen. These parameters are company level parameter, which means that an account documents interface can be configured for each company in Oracle Financial Services Lending and Leasing.

DOT_STORAGE_DIRECTORY

Set to the name of the Oracle directory object, created during installation, which defines where the account documents will be stored. The account document files are stored in the database as a BFILE which means that only a reference to an external file is stored in the database and the actual file is stored on the operating system’s file system in a path defined by an Oracle directory object. This parameter is the name of the Oracle object, not the actual directory path. Typical naming convention used for DOT directory objects is - DOT_DIR_<application owner name>_company_branch. For example: DOT_DIR_OFSLLREL_HQ

2.4 Creating Users and Granting Access

Creating the user 'INTERNAL'. This user is required for batch job process, webservices and to start services.

A script is provided in the distribution media in the dba_utils folder to create an user. Run the script "crt_app_user.sql script" as a OFSLL application owner user.

Granting OFSLL Screen / Web Service Access to Application Users (New In­stallation only)

Post user creation, to access all the ‘Screens’ and 'web service' for a specific responsibility, login as an application schema owner and do the following:

1. Execute the below SQL statement:
   TRUNCATE TABLE FLS_ACCESS_DETAILS;

   

2. Execute the following db script available in dba_utils folder.

set_screen_access.sql

In the prompt, enter the user responsibility. You can also specify ‘ALL’ to provide screen / web service access to all the created users in bulk.

In the next prompt ‘source(UI/WS)’, enter one of the following:

• ‘UI’ to provide screen access to either All or specific user responsibility.
• ‘WS’ to provide web service access to either All or specific user responsibility.

  

Further, you can customized the access through Setup > User > Access screen - ‘Screen’ and ‘Webservice’ tabs, as detailed in setup guide.

Granting user access to Reports, Transactions, & Correspondence

Post user creation, to access all the Reports, Transactions, Correspondence for a specific responsibility, login as an application schema owner and execute the following db script available in dba_utils folder.

set_rpt_txn_cor_access.sql

In the prompt, enter the user responsibility. You can also specify ‘ALL’ to provide screen access to all the created users in bulk.

Further, you can customized the access through Setup > User > Access screen as detailed in setup guide.

Note

In case where the application schema is created using an export and import utility, please reset the sequences by executing ‘upgrade_fix_reset_all_sequences.sql file available in the data fix folder of the media. This helps to reset all the sequences to the current level.

2.5 Set the Oracle JVM File Permissions

The application correspondence interface uses the built-in Oracle Java Virtual Machine (JVM) to create subdirectories below the top-level correspondence/document storage directory (defined by the application’s COR_STORAGE_DIRECTORY, DOT_STORAGE_DIRECTORY parameters). In order to do this, the JVM’s security policy needs to be updated by the Oracle built-in DBMS_JAVA package to allow directories to be created and accessed. A SQL script named set_java_perms.sql has been supplied on the installation media in the dba_utils directory for this purpose. The script will select all directory object names defined for the application COR_STORAGE_DIRECTORY and DOT_STORAGE_DIRECTORY parameters and will provide read, write, and delete privileges to the directory named by the directory object and all directories below it.

Running the set_java_perms.sql script

After the system and company parameters have been set up, run SQL*Plus as the SYS user and execute the set_java_perms.sql script to set the file permissions.

Similarly, in case of any java permission access issues to directories, provide the required read, write or delete privileges.

2.6 Advanced Queues

Queuing feature is used in OFSLL for writing debugs into the XMLTYPE column of LOG_FILES_HEADER table instead of writing into the file system.

Also, in OFSLL, Outbound database calls are routed through application server through AQ JMS bridge. The MDB deployed in Middleware, reads the AQ message remotely which has all information required to make the call to Bureau or RO /DT.

The MDB deployed in Middleware, reads the AQ message ('OFSLL_OUTBOUND_TOPIC') remotely which has information about job set and job set status code, which has been initiated by job scheduler.

To enable alert and debug message queue:

1. Set the system parameter "CMN_DEBUG_METHOD" with syp_value = 4.

• SQL> update system_parameters set syp_value = '4' where syp_parameter_cd = 'CMN_DEBUG_METHOD';

To enable MDB EJB queue:

2. Set the system parameter and credit bureau parameter in "OUTBOUND_CALL_Q" with syp_value = Y.

• UPDATE SYSTEM_PARAMETERS SET SYP_VALUE='Y' WHERE SYP_PARAMETER_CD='OUTBOUND_CALL_Q' AND SYP_ENABLED_IND='Y'
• UPDATE CREDIT_BUREAU_PARAMETERS SET CRP_VALUE='Y' WHERE CRP_PARAMETER_CD='OUTBOUND_CALL_Q'

3. Start the queue services using the sql file

• SQL>setup_AQ_subscribe.sql.

4. Once the queue services are started, system subscribes and registers the queue for a notification so that whenever a data inserted into the queue it will notify the system.

To switch back to the existing file system mechanism:

5. To unsubscribe, de-register the queue and stops the queue services for notification, run the below sql file:

• SQL>setup_AQ_unsubscribe.sql

6. Set the system parameter "CMN_DEBUG_METHOD"with syp_value= 1.

• SQL> update system_parameters set syp_value = '1' where syp_parameter_cd = 'CMN_DEBUG_METHOD';

Note

- Please do not delete any queue or queue table from the schema.
- When you unsubscribe the queue, MDB queue is also stopped. You need to start ‘OFSLL_OUTBOUND_Q’ and ‘OFSLL_OUTBOUND_TOPIC’ to continue with MDB flow.
- Ensure that you have granted ‘dbms_aqin’ privileges to the schema user.

2.7 Fine-Grained Access to Network Services in Oracle Database

Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP and UTL_INADDR), all of which are implemented using the TCP protocol.

An Access Control Entry can be limited to specific PL/SQL APIs (UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL).

In a multitenant environment, Access Control Entries (ACEs) can be created at the CDB or PDB level. For the examples in this article, all the host ACLs and host ACEs will be created at the PDB level. The following code creates two test users in a PDB.

Append an Access Control List (ACE)

Host ACL are never created directly. Instead, they are implicitly created when we append a host Access Control Entry (ACE) using the DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE procedure. If we append a new ACE to a host that has no existing host ACL, a new host ACL is implicitly created. If the host already has an ACL, the new host ACE will be appended to the existing host ACL.

Login to SQL*Plus as the SYS user.

Append a second host to the existing ACL

Parameter Definitions

The parameters used in the procedures and functions above

Parameters	Description
host	Any valid host name or IP address. Wildcards are allowed.
lower_port	Specific port number, or lower part of a range of ports.
upper_port	Upper part of a range of ports. If NULL, it defaults to the lower_port value.
ace	The access control entry, defined using the XS$ACE_TYPE type.

The XS$ACE_TYPE type has the following definition.

Parameters	Description
privilege_list	The list of privileges available to the ACE.
princi­pal_name	The database user the ACE applies to.
principal_­type	You will always use XS_ACL.PTYPE_DB for these network ACEs as they apply to users and roles.

The privilege_list specifies one or more privileges in a comma separated list. The available privileges are shown below.

Parameters	Description
http	Access restricted to the UTL_HTTP package and the HttpUriType type.
http_proxy	Needed in conjunction with http if HTTP access is via a proxy.
smtp	Access restricted to the UTL_SMTP and UTL_MAIL packages.
resolve	Access restricted to the UTL_INADDR packages.
connect	Opens access to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and DBMS_LDAP packages and the HttpUriType type.
jdwp	Enables Java Debug Wire Protocol debugging operations.

2.8 Installing Upgrade

An upgrade is a process of updating an existing version to its higher version. For example, upgrading from Oracle Financial Services Lending and Leasing 14.8.0.0.0 to 14.9.0.0.0.

The following upgrade path is recommended for existing Daybreak customers:

DLS 11.6.0.0.23 > OFSLL 14.0.0.0.0 > OFSLL 14.1.0.0.0 > OFSLL 14.2.0.0.0 > OFSLL 14.3.0.0.0 > OFSLL 14.3.1.0.0 > OFSLL 14.4.0.0.0 > OFSLL 14.5.0.0.0 > OFSLL 14.6.0.0.0 > OFSLL 14.7.0.0.0 > OFSLL 14.8.0.0.0 > OFSLL 14.9.0.0.0

Note

• Please refer to ‘Upgrade Installation Guide’ for detailed information.
• If table compression feature is enabled in OFSLL, disable table compression prior to upgrade.