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
Note
Values of the following parameters in the above example, differ for different servers on which the database is run:
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 |
|
/fin_storage |
Top level directory for faxed-in images |
|
/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. |
The Oracle Financial Services Lending and Leasing table and index creation DDL allow for the use of up to 20 different tablespaces --10 for tables and 10 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_index_ipf.sql storage_parms_table_ipf.sql |
OFSLL_IPF_DATA OFSLL_IPF_INDEX |
Input processing file |
steady growth, potentially very large
|
Output Process |
storage_parms_index_opf.sql storage_parms_table_opf.sql |
OFSLL_OPF_DATA OFSLL_OPF_INDEX |
Output processing file |
steady growth, potentially very large
|
Logging |
storage_parms_index_log.sql storage_parms_table_log.sql |
OFSLL_LOG_DATA OFSLL_LOG_INDEX |
Error Logging |
steady growth |
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 |
Fax-in interface directory objects |
FIN |
fin_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 the fax-in service will be used, set these parameters located on the Setup→Administration→Company→System Parameters screen. These parameters are company level parameters, which mean that a fax-in service can be configured for each company in Oracle Financial Services Lending and Leasing.
Set to the name of the POP server that the fax-in service will communicate with to retrieve email messages containing faxed in images.
Set to the username/account on the POP server that the fax-in service will retrieve the email messages from.
Set to the password for the FIN_POP_USERNAME on the POP server that the fax-in service will retrieve the email messages from.
Set to the name of the Oracle directory object, created during installation, which defines where the faxed-in images will be stored. The image 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 FIN directory objects is - FIN_DIR_<application owner name>_company_branch. For example: FIN_DIR_OFSLLREL_HQ
Set to the name of a directory that the fax-in service can use during conversion of the email attachment TIF file into a GIF file. This is typically set to $OFSLL_HOME/tmp.
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
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 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:
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.
After the database has been created, some Oracle system parameters may have to be modified due to application requirements. See the previous section about Creating the Database for more details.
Application uses the Oracle UTL_FILE utility for reading from and writing to files for some of the datafile interfaces, as well as when log files and debugging files are written. The UTL_FILE utility uses an Oracle system parameter to restrict the directories that can be written to or read from. Set this parameter to * (meaning all directories on the server), or set it to a list of directories. At this time, those directories are $OFSLL_HOME/logs, $OFSLL_HOME/sql, $OFSLL_HOME/output, $OFSLL_HOME/input, $OFSLL_HOME/input/lockbox and $OFSLL_HOME/input/lockbox/processed/ (note the trailing slash). Over time this list may grow (or shrink) as new versions of Oracle Financial Services Lending and Leasing are released. This parameter cannot be set dynamically.
The database must be restarted for the above values to take effect.
Example 1
alter system set utl_file_dir=’*’ scope=spfile;
Example 2
alter system set utl_file_dir=’$OFSLL_HOME\logs’, ’$OFSLL_HOME\sql’, ’$OFSLL_HOME\output’, ’$OFSLL_HOME\input’, ’$OFSLL_HOME\input\lockbox’, ’$OFSLL_HOME\input\lockbox\processed\’ scope=spfile;
The application job service and job scheduler make heavy use of the DBMS_JOB facility. This value will represent the number of simultaneous jobs that can be running on the server at a point of time. The various services that are required to be running to make the Oracle Financial Services Lending and Leasing software function, will account for 6 job queue processes. Anything above 6 is such that the online and nightly batch jobs can run in parallel. This parameter can be set again at any time without having to restart the database, if there be a need to add to or cut back on the number of running processes.
Example
alter system set job_queue_processes=10 scope=both;
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 run 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.5.0.0.0 to 14.6.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
Note
If you choose ‘2’ as the installer option at the ‘Selecting the Install Type’ stage, then the script runs the upgrade installer.
Respond to the prompts and continue (For details, refer sections Setting up the Installation Environment to Installing Directory Objects).
While installing the upgrade, the installer performs the following tasks:
Note
Post upgrade, if there are any invalid java stored procedures noticed, please recompile the respective units manually.
Post upgrade, you need to execute the following db script available in dba_utils folder.
In the prompt, enter the user responsibility. You can also specify ‘ALL’ to provide screen access to all the users in bulk.
Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database. The operating system data files where data is stored is not used. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. Encrypted data is transparently decrypted for a database user or application that has access to data.
OFSLL application processes sensitive data. Hence, it is recommended to use a TDE to protect confidential data, such as credit card and social security numbers, stored in table columns.
A script is provided along with the distribution media to encrypt the sensitive columns in the table. You can follow the steps below to enable TDE for column encryption:
To start using TDE, the security administrator must create a wallet and set a master key. The wallet can be the default database wallet shared with other Oracle Database components, or a separate wallet specifically used by TDE. Oracle strongly recommends that you use a separate wallet to store the master encryption key.
If you wish to use a wallet specifically for TDE, then you must specify a wallet location in the sqlnet.ora file by using the ENCRYPTION_WALLET_LOCATION parameter. Oracle recommends that you use the ENCRYPTION_WALLET_LOCATION parameter to specify a wallet location for TDE.
ENCRYPTION_WALLET_LOCATION = (SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY =
/etc/ORACLE/WALLETS/oracle)))
If no wallet location is specified in the sqlnet.ora file, then the default database wallet location is used. The default database wallet location is ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet or ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet. Here, DB_UNIQUE_NAME is the unique name of the database specified in the initialization parameter file.
The master encryption key is stored in an external security module, and is used to protect the table keys and tablespace encryption keys. By default, the master encryption key is a random key generated by Transparent Data Encryption (TDE). It can also be an existing key pair from a PKI certificate designated for encryption. To use TDE with PKI key pairs, the issuing certificate authority must be able to issue X.509v3 certificates with the key usage field marked for encryption.
To set the master encryption key, use the following command:
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY ‘password’
where,
Reset/Regenerate the master encryption key only if it has been compromised or as per the security policies of the organization. You should back up the wallet before resetting the master encryption key.
Use the ALTER SYSTEM command to set or reset (rekey) the master encryption key.
The database must load the master encryption key into memory before it can encrypt or decrypt columns/tablespaces. Opening the wallet allows the database to access the master encryption key. Use the following ALTER SYSTEM command to explicitly open the wallet:
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password"; where password is the password to open the wallet. You should enclose the password string in double quotation marks (" ").
Once the wallet has been opened, it remains open until you shut down the database instance, or close it explicitly by issuing the following command:
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "password"
To add an encrypted column to an existing table, or to encrypt or decrypt an existing column, you use the ALTER TABLE SQL command with the ADD or MODIFY clause.
SQL> ALTER TABLE applicants MODIFY (apl_gender_cd ENCRYPT USING 'AES256');
Encrypting the indexed columns:
SQL> ALTER TABLE applicants MODIFY (apl_ssn ENCRYPT USING 'AES256' NO SALT);
You may want to disable encryption for reasons of compatibility or performance. To disable column encryption, use the ALTER TABLE MODIFY command with the DECRYPT clause.
Example 7-11 Turning Off Column Encryption:
SQL> ALTER TABLE applicants MODIFY (apl_gender_cd DECRYPT);
A Set of scripts are provided on the installation media in the dba_utils directory to encrypt the recommended columns in OFSLL.
upgrade_tb_tde_enable_ofsll.sql to encrypt base table columns.
upgrade_tb_opur_tde_enable_ofsll.sql to encrypt archive table columns.
upgrade_tb_api_tde_enable_ofsll.sql to encrypt api table columns.
Login as an OFSLL user and execute the scripts to encrypt the columns.
Note
Refer Oracle® Database Advanced Security Administrator's Guide, section on Securing Stored Data Using Transparent Data Encryption for details.
Data Redaction is one of the new features available in 12c. Data Redaction is in Advanced Security option of enterprise edition.
Oracle Advanced Security Data Redaction provides selective, on-the-fly redaction of sensitive data in SQL query results prior to display by applications so that unauthorized users cannot view the sensitive data.
OFSLL application processes sensitive data. Hence, it is recommended to use a Data Redaction to protect confidential data, such as credit card and social security numbers, stored in table columns.
There are different types of redaction; full, partial, regexp, random and none. Please refer Oracle® Database Advanced Security Administrator's Guide, section on Configuring Oracle Data Redaction Policies for details.
Oracle Fusion Applications use the PS_TXN table to store the intermediate processing state. When there are many concurrent users, this table receives a high number of inserts and could suffer from concurrency issues.
Follow the steps outlined in note ID 1444959.1 in My Oracle Support to alleviate the contention.