Non-SA-supplied Oracle software and database setup

If you plan to use a non-SA-supplied Oracle database with the SA Model Repository, the following steps are required for compatibility with SA. You should also review System requirements for Oracle database before preceding with this section.

• Modifiable kernel parameters
• Installing the Oracle database
• SA Database installation sample scripts

Modifiable kernel parameters

If you manually install the Oracle database, or use an existing database, you must insure that all kernel parameter values are specified correctly for your environment but also within the limitations required by SA.

You can find additional information about kernel parameter configuration in the Configuring Kernel Parameters section of the Oracle® Database Quick Installation Guide.

Modifiable kernel parameter values for Linux

This topic provides you information about the kernel parameters you can change for supported Linux operating systems.

• You can change values for the following parameters in /etc/sysctl.conf. If the current value of any parameter is higher than the value listed in this table, then do not change the value of that parameter:

  #SA Oracle parameters begin
fs.aio-max-nr=1048576
fs.file-max=6815744
kernel.shmmax=2147483648
kernel.shmall=2097152
kernel.shmmni=4096
kernel.sem=250 32000 100 128
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586
net.ipv4.ip_local_port_range=9081 65500
net.ipv4.tcp_wmem=262144 262144 262144
net.ipv4.tcp_rmem=4194304 4194304 4194304
#SA Oracle parameters end

• You can change values for the following parameters in /etc/security/limits.conf:

  #SA Oracle parameters begin
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
#SA Oracle parameters end

• You can change values for the following parameters in /etc/pam.d/login:

  session required /lib/security/pam_limits.so

• You can change values for the following parameters in /etc/fstab:

  shmfs /dev/shm tmpfs size=4g 0 0

  Note For RHEL 7 systems, the mount should be tmpfs /dev/shm tmpfs size=4g 0 0.

• You can change values for the following parameters in /etc/selinux/config:

  #SA Oracle parameters begin
SELINUX=disabled
#SA Oracle parameters end

Modifiable kernel parameter values for SUSE Linux x86_64

This topic identifies additional required settings for SUSE Linux x86_64 when running Oracle 11g or 12c:

• Enter the following command to cause the system to read the /etc/sysctl.conf file when it restarts:

  # /sbin/chkconfig boot.sysctl on

• You must enter the GID of the oinstall group as the value for the
  /proc/sys/vm/hugetlb_shm_group parameter. Doing this grants members of oinstall a group permission to create shared memory segments. For example, where the oinstall group GID is 501:

  # echo 501 > /proc/sys/vm/hugetlb_shm_group

  After running this command, use vi to add the following text to /etc/sysctl.conf, and enable the boot.sysctl script to run on system restart:

  vm.hugetlb_shm_group=501

Modifiable kernel parameter values for Oracle SPARC Solaris (64 bit), HP-UX, and IBM AIX

Refer the Configuring Kernel Parameters section in the following Oracle documents:

• Database Quick Installation Guide for Oracle Solaris on SPARC (64 Bit)
• Database Quick Installation Guide for HP-UX Itanium
• Database Quick Installation Guide for IBM AIX on POWER Systems (64-Bit)

Installing the Oracle database

To install an Oracle database for use with the SA Model Repository:

1. Create the database with the UTF8 database character set .
2. Set the database with TIME_ZONE to '+00:00'.
3. Create the database with the required initialization (init.ora) parameters.
4. Create the database with required tablespaces.
5. Create the database user opsware_admin.
6. tnsnames.ora file requirements
7. File linking requirements
8. Enable Oracle Daylight Savings Time (DST)
9. sqlnet.ora requirements

1. Create UTF8 Database character set

Create the database with the UTF8 database character set:

CHARACTER SET UTF8

2. Set the Database TIME_ZONE

Create the database with TIME_ZONE set to '+00:00':

SET TIME_ZONE = '+00:00'

3. Specify the required initialization (init.ora) parameters

Create the database instance with the following initialization (init.ora) parameters. For parameters not listed, SA assumes that the default Oracle parameters are used.

Oracle 11.2.0.x

compatible := required to be >= 11.2.0
cursor_sharing := required to be = FORCE
db_file_multiblock_read_count := suggested to be >= 16
db_block_size := required to be >= 8192
deferred_segment_creation := required to be = FALSE
event := required to be = 12099 trace name context forever, level 1
job_queue_processes := required to be >= 1000
log_buffer := required to be >= 5242880
memory_target := required to be >= 1879048192 (1.75GB)
nls_length_semantics := required to be = CHAR
nls_sort := required to be = GENERIC_M
open_cursors := required to be >= 1500
optimizer_index_cost_adj := required to be = 100
optimizer_index_caching := required to be = 0
optimizer_mode := 'required to be = ALL_ROWS
processes := required to be >= 1024
recyclebin := required to be = OFF
remote_login_passwordfile := required to be = EXCLUSIVE
session_cached_cursors := required to be >= 50
undo_tablespace := should be = UNDO or other UNDO tablespace
undo_management := should be = AUTO
_complex_view_merging := required to be = FALSE

Oracle 12.1.0.x

compatible := required to be >= 12.1.0
cursor_sharing := required to be = FORCE
db_block_size := required to be >= 8192
db_file_multiblock_read_count := suggested to be >= 16
deferred_segment_creation := required to be = FALSE
job_queue_processes := required to be >= 1000
max_string_size := required to be = STANDARD
memory_target := required to be >= 2684354560 (2.5GB)
nls_length_semantics := required to be = CHAR
nls_sort := required to be = GENERIC_M
open_cursors := required to be >= 1500
optimizer_index_cost_adj := required to be = 100
optimizer_index_caching := required to be = 0
optimizer_mode := 'required to be = ALL_ROWS
processes := required to be >= 1024
recyclebin := required to be = OFF
remote_login_passwordfile := required to be = EXCLUSIVE
session_cached_cursors := required to be >= 50
undo_tablespace := should be = UNDO or other UNDO tablespace

4. Create the required tablespaces

The following tablespaces must be created to support SA. For tablespace disk space requirements, see Model repository (Database) disk space requirements.

• LCREP_DATA
• LCREP_INDX
• TRUTH_DATA
• TRUTH_INDX
• AAA_DATA
• AAA_INDX
• AUDIT_DATA
• AUDIT_INDX
• STRG_DATA
• STRG_INDX

5. Create the Database user opsware_admin

Create the database user 'opsware_admin' with the following privileges.

SQL> create user opsware_admin identified by opsware_admin
default tablespace truth_data temporary tablespace temp
quota unlimited on truth_data;
SQL> grant alter session to opsware_admin with admin option;
SQL> grant create procedure to opsware_admin with admin option;
SQL> grant create public synonym to opsware_admin with admin option;
SQL> grant create sequence to opsware_admin with admin option;
SQL> grant create session to opsware_admin with admin option;
SQL> grant create table to opsware_admin with admin option;
SQL> grant create trigger to opsware_admin with admin option;
SQL> grant create type to opsware_admin with admin option;
SQL> grant create view to opsware_admin with admin option;
SQL> grant delete any table to opsware_admin with admin option;
SQL> grant drop public synonym to opsware_admin with admin option;
SQL> grant select any table to opsware_admin with admin option;
SQL> grant select_catalog_role to opsware_admin with admin option;
SQL> grant query rewrite to opsware_admin with admin option;
SQL> grant restricted session to opsware_admin with admin option;
SQL> grant execute on dbms_utility to opsware_admin with grant option;
SQL> grant analyze any to opsware_admin;
SQL> grant insert, update, delete, select on sys.aux_stats$ to opsware_admin;
SQL> grant gather_system_statistics to opsware_admin;
SQL> grant create job to opsware_admin with admin option;
SQL> grant create any directory to opsware_admin;
SQL> grant drop any directory to opsware_admin;
SQL> grant alter system to opsware_admin;
SQL> grant create role to opsware_admin;
SQL> grant create user to opsware_admin;
SQL> grant alter user to opsware_admin;
SQL> grant drop user to opsware_admin;
SQL> grant create profile to opsware_admin;
SQL> grant alter profile to opsware_admin;
SQL> grant drop profile to opsware_admin;

If the Oracle version is 12.2.0.x, create the database user 'truth' with the following privileges:

6. tnsnames.ora file requirements

The tnsnames.ora file enables resolution of database names used internally by the core components. SA has the following requirements for the tnsnames.ora file:

• The file must reside in the /var/opt/oracle/tnsnames.ora and $ORACLE_HOME/network/admin locations
• If the core is installed across multiple servers, a copy of the file must reside on the servers hosting the following components:
  • Model Repository
  • Infrastructure Component bundle (required by the Data Access Engine, Model Repository Multimaster Component, Software Repository Store)
  • Slice Component bundle (required by the Command Center, Web Services Data Access Engine, Global File System)
• For a core installed on multiple servers, the directory path of the tnsnames.ora file must be the same on each server.
• In a Single Core installation, the tnsnames.ora file must contain an entry for the Model Repository, as in the following example:

  truth = DESCRIPTION= (ADDRESS=(HOST=magenta.example.com)(PORT=1521)

  (PROTOCOL=tcp)) (CONNECT_DATA=(SERVICE_NAME=truth)))

tnsnames.ora: Multimaster Mesh requirements

In a Multimaster Mesh, the tnsnames.ora file must be set up for a Source Core and a Destination Core using the following guidelines.

• Source core

  The tnsnames.ora file must contain an entry for its own Model Repository. The port number must be set to the port that you have designated that the Oracle listener process use, such as 1521 (default), 1526, and so on.

  The tnsnames.ora file must also contain an entry that specifies the Source Core Management Gateway. This port is used by the Data Access Engine for Multimaster traffic. The port number is derived from the following formula: (20000) + (facility ID of the Destination Core).

  Example: In the following example, the TNS service name of the Source Core is orange_truth, which runs on the host orange.example.com. The TNS name of the Destination Core is cyan_truth, which has a facility ID of 556. Note that the entry for cyan_truth specifies orange.example.com, which is the host running the Source Core’s Management Gateway.

  orange_truth=(DESCRIPTION=(ADDRESS=(HOST=orange.example.com)(PORT=1521)
(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=truth)))

  cyan_truth=(DESCRIPTION=(ADDRESS=(HOST=orange.example.com)(PORT=20556)
(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=truth)))

• Destination core

  The tnsnames.ora file must contain an entry for its own Model Repository. The port number must be set to the port that you have designated that the Oracle listener process use, such as 1521 (default), 1526, and so on. The tnsnames.ora file does not require any entries for other cores in the mesh.

  Example: In the following example, the TNS service name of the Destination Core is cyan_truth, and the core runs on the host, cyan.example.com.

  cyan_truth=(DESCRIPTION=(ADDRESS=(HOST=cyan.example.com)(PORT=1521)
(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=truth)))

7. File linking requirements

After creating the database, but before installing the Model Repository with the SA Installer, perform the following tasks:

1. Create the tnsnames.ora file in the /var/opt/oracle directory.
2. Verify that the file conforms to the rules listed in 6. tnsnames.ora file requirements.
   If it does not exist, create mkdir -p /var/opt/oracle directory:
3. Create the following symbolic link:

   ln -s /var/opt/oracle/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora

4. Ensure that the oracle Unix user has read-write permission on the tnsnames.ora file.

5. If the Oracle version is 12.2.0.x, create the following symbolic link as oracle user:

   su - oracle
cd $ORACLE_HOME/jdbc/lib/
ln -s ojdbc8.jar ojdbc7.jar

For Red Hat Enterprise Linux:

1. Create another symbolic link:

   ln -s /etc/oratab /var/opt/oracle/oratab

2. Copy the sample opsware-oracle script to /etc/init.d/.
3. Link /etc/init.d/opsware-oracle to corresponding scripts in the /etc/rc* directories. For example:

   ln -s /etc/init.d/opsware-oracle \

         /etc/rc0.d/K02opsware-oracle

   ln -s /etc/init.d/opsware-oracle \

         /etc/rc1.d/K02opsware-oracle

   ln -s /etc/init.d/opsware-oracle \

         /etc/rc2.d/S60opsware-oracle

   ln -s /etc/init.d/opsware-oracle \

         /etc/rcS.d/K02opsware-oracle

8. Enable Oracle Daylight Savings Time (DST)

To enable Daylight Saving Time for the Oracle database, you must apply database tier patches. To apply these patches, perform the following steps:

1. Verify that your database is running on Oracle 11g, 12c or higher.
2. Use MetaLink Note 412160.1 to apply Oracle Database time zone fixes specific to your database version.

   Use MetaLink Note 412160.1 to apply time zone fixes to the Oracle Java Virtual Machine (JVM) in the Oracle Database specific to your E-Business Suite database version.

9. sqlnet.ora requirements

Some applications in Server Automation use the oracle classes12.jar file to connect to the database. To enable these utilities to connect to the Oracle 12C database, create a sqlnet.ora in the $ORACLE_HOME/network/admin folder in both the SA Client system and the SA Core Database server with the following contents:

# File:        sqlnet.ora
# Certified:   Oracle 12.1.0
# Purpose:     Configuration File for all Net8 Clients
# Notes:       None

LOG_DIRECTORY_SERVER=/u01/app/oracle/product/12.1.0/db_1/network/log 
LOG_FILE_SERVER=sqlnet.log
TRACE_DIRECTORY_SERVER=/u01/app/oracle/product/12.1.0/db_1/network/trace
TRACE_FILE_SERVER=sqlnet.trc
NAMES.DIRECTORY_PATH= (TNSNAMES)
SQLNET.INBOUND_CONNECT_TIMEOUT=180

SA Database installation sample scripts

HPE Support can provide sample scripts for steps 1 through 5 of the Oracle Database Installation Steps.

Oracle/SA Installation Scripts, SQL Scripts, and configuration files

• truth.sh: A shell script that creates directories and then launches the truth.sql script. Running this script causes all the scripts to be run automatically, in the correct order.
• truth.sql: Prompts for passwords of the SYS and SYSTEM users and launches the remainder of the SQL scripts in this list.
• CreateDB.sql: Creates a database with the UTF8 character set and TIME_ZONE set to '+00:00'
• CreateDBFiles.sql: Creates the following tablespaces that are required by SA:
  • LCREP_DATA
  • LCREP_INDX
  • TRUTH_DATA
  • TRUTH_INDX
  • AAA_DATA
  • AAA_INDX
  • AUDIT_DATA
  • AUDIT_INDX
  • STRG_DATA

  • STRG_INDX

    See Model Repository (Database) disk space requirements for additional tablespace sizing information.

• CreateDBCatalog.sql: Runs Oracle scripts to create data system catalog objects.
• JServer.sql: Sets up the Oracle Java environment.
• CreateAdditionalDBFiles.sql: Adds data and index files to certain tablespaces and allocates additional disk space. This script is optional, but recommended.
• CreateUserOpsware_Admin.sql: Creates the opsware_admin database user and grants permissions (privileges) to this user (required by SA).
• postDBCreation.sq: Creates the spfile file from the pfile file (parameter file).
• init.ora: Contains initialization parameters for the database. See 3. Specify the required initialization (init.ora) parameters.
• tnsnames.ora: Enables resolution of database names used internally by SA.
• listener.ora: Contains configuration parameters for the listener. SA by default listens on port 1521. You can change the default port during installation or by editing the tsnames.ora file.

bash_profile or profile: Sets environment variables and sets shell limits for the oracle Unix user.

opsware-oracle: A script residing in /etc/init.d that starts up and shuts down the database and listener.

Creating the database using the SA-supplied scripts

To create the Oracle database using the SA-supplied scripts:

1. Obtain the database creation scripts from your HPE Support representative.
2. Make any required changes to the scripts.
3. As root, create the Unix user oracle and log in to the server as the user oracle.
4. Copy the SA-supplied files to the $ORACLE_BASE/admin/truth/create directory.
5. Change the mode of the SA-supplied truth.sh script:

   chmod 755 truth.sh

6. Launch the SQL scripts that create the database by running the truth.sh script:

   ./truth.sh

7. After the scripts launched by truth.sh complete, check the log files in the /u01/app/oracle/admin/truth/scripts/*.log directory for errors.