The first step in the database creation is the creation of Database Server Users and Groups. The below sections are a guide to:
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 database 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 perform 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 listeners can be defined (one for “normal” database connections and one for external procedures) or run a single listener that handles both types of requests. Regardless 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 listener 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 programs that run (outside of the Oracle external procedure 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. |
Create a database as per corporate standards and/or Oracle best practices with the following recommendations/guidelines.
listener.ora
For Unix
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:
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.
The script displays install option..
The script prompts to ‘Choose an installer option? [1-3]’.
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.
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 Leasing 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. |
|
input/iuu |
Contains files for User Upload. |
|
input/iuu/processed |
Contains files that are already processed. |
|
input/ias |
Contains files for Assets Upload. |
|
input/ias/processed |
Contains files that are already processed. |
|
input/iav |
Contains files for Asset Valuations Upload. |
|
input/iav/processed |
Contains files that are already processed. |
|
input/iat |
Contains files for Asset Tracking Attributes. |
|
input/iat/processed |
Contains files that are already processed. |
|
input/iaa |
Contains files for Asset Attributes. |
|
input/iaa/processed |
Contains files that are already processed. |
|
input/iad |
Contains files for Account Dues Upload. |
|
input/iad/processed |
Contains files that are already processed. |
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 |
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.
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.
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 |
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 |
The factory shipped seed data is automatically uploaded during installation and once complete, a confirmation message is displayed as indicated below:
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).
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.
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.
Set the t3://hostname:port/console (Host name and Port) where application is installed
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)
Set to ‘Yes
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
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.
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.
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
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
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.
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:
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:
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.
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.
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.
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:
To enable MDB EJB queue:
To switch back to the existing file system mechanism:
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.
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.
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.
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. |
principal_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. |
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.10.0.0.0 to 14.11.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 > OFSLL 14.10.0.0.0 > OFSLL 14.11.0.0.0.
Note