Integrating with an Oracle database

This topic describes how to create and configure an Oracle database to integrate it with Netcool/OMNIbus.

Integrating the JDBC Gateway with Oracle requires the following steps:

Note: The steps in this procedure should be run by the user oracle, which is created during the Oracle installation.

Step 1: Setting up the environment variables

Update the .bash_profile of the user oracle using the following command:

export ORACLE_SID=ortestdb

Where ortestdb is your database instance name.

ORACLE_HOME and ORACLE_SID have been configured for user oracle.

To check, run the following command:

"env | grep ORACLE"

ORACLE_SID holds the name of the database instance. To create a new instance, or to use an existing instance, the environment variable must be updated first.

Step 2: Preparing an Oracle parameters file

Update the following parameters in the $ORACLE_HOME/dbs/init.ora file. 

db_name='ortestdb'
memory_target=1G
processes = 150
audit_file_dest='/opt/oracle_BASE/test1/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain='my.ibm.com'
db_create_file_dest='/opt/oracle_BASE/test1/oradata'
db_create_online_log_dest_1='/opt/oracle_BASE/test1/u02/oradata'
db_create_online_log_dest_2='/opt/oracle_BASE/test1/u03/oradata'
db_recovery_file_dest='/opt/oracle_BASE/test1/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/opt/oracle_BASE/test1'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBSP'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/opt/oracle_BASE/test1/ctl/u01/prod/control01.ctl,
                 /opt/oracle_BASE/test1/ctl/u01/prod/control02.ctl,
                 /opt/oracle_BASE/test1/ctl/u01/prod/control03.ctl)
compatible ='12.0.0'
OS_AUTHENT_PREFIX=""

Note:

db_name is the name of the database instance held by ORACLE_SID.

db_domain is the domain of the server.

undo_tablespace must be set to 'UNDOTBSP'

/opt/oracle_BASE and the sub-directories (before the file names) shown in the sample init.ora must be staged before running the CREATE SPFILE command.

It is better to create a bash script for directory creation.

For staging directories, perform the following steps:

  1. As root, set full permissions (apply chmod 777) to /opt/oracle_BASE.

  2. Use user oracle to create the sub-directories.

You can use a name other than oracle_BASE, but do not use $ORACLE_HOME as the parent directory to the storage location of the database instance files, this is to ease resource management of the database instances. For example, if Step 3 hit errors, you can remove all files under /opt/oracle_BASE/test1 before rerunning Step 3.

The CREATE DATABASE command will look for init<ORACLE_SID>.ora. If the CREATE DATABASE <ORACLE_SID> command fails reporting: file not found, rename it init.ora to init<ORACLE_SID>.ora (for example initortestdb.ora).

Step 3: Creating a binary parameter file

Run the following command:

SQL> CREATE SPFILE FROM PFILE;

Verify the creation of the $ORACLE_HOME/dbs/spfile<ORACLE_SID>.ora file.

Note: Do not use a text editor to amend the spfile<ORACLE_SID>.ora file. Consult the Oracle documentation for the appropriate editing procedure.

Step 4: Creating a database instance

To create a database instance, use the following steps:

  1. Run the following command to connect to an idle instance:

    [oracle@klxv0104 bin]$ ./sqlplus / as sysdba

  2. Run the following command:

    SQL> CREATE SPFILE FROM PFILE;

    This creates the following file: $ORACLE_HOME/dbs/spfileortestdb.ora

  3. Run the following command to start the Oracle instance:

    SQL> STARTUP NOMOUNT;

  4. Run the following command to create the Oracle database:

    SQL> CREATE DATABASE ortestdb USER SYS IDENTIFIED BY sys_pass USER SYSTEM IDENTIFIED BY system_pass EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE temp UNDO TABLESPACE UNDOTBSP DEFAULT TABLESPACE users;
    Note:

    The command example in the Oracle document uses a different name for undo table, the value in the command shown here uses UNDOTBSP, which agrees with the undo_tablespace parameter in the init.ora file. (See the note in Step 2: Preparing an Oracle parameters file).

    Verify the database instance file location for the files created.

    If the CREATE DATABASE command failed at some point, before rerunning the step, perform the following steps:

    1. Exit the NOMOUNT state: SHUTDOWN IMMEDIATE

    2. Clean up the db_instance folder specified in init.ora (see Step 2: Preparing an Oracle parameters file).

    For the following steps, pass in the designated paths of your setup as the command argument. The path must be staged beforehand.

  5. Run the following command to create the tablespace apps_tbs.

    SQL> CREATE TABLESPACE apps_tbs LOGGING DATAFILE '/opt/oracle_BASE/test1/oradata/ORTESTDB/datafile/apps01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

  6. Run the following command to create the tablespace indx_tbs.:

    SQL> CREATE TABLESPACE indx_tbs LOGGING DATAFILE '/opt/oracle_BASE/test1/oradata/ORTESTDB/datafile/indx01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

Step 5: Building the Data Dictionary Views

Before starting, the database instance created previously must be in OPEN state.

To build data dictionary views, perform the following steps:

  1. Log in as SYSDBA.

  2. Run the following command to start the database in OPEN state:

    SQL> STARTUP OPEN;

  3. Run the following command to check the instance status:

    SQL> select INSTANCE_NAME, STATUS from v$instance;

  4. As SYSDBA, run the following scripts in the order shown:

    @?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql

  5. Connect as SYSTEM at the SQL prompt, log in with the SYSTEM password specified in the CREATE DATABASE command:

    SQL> connect system

  6. Run the following command:

    SQL> @?/sqlplus/admin/pupbld.sql

    Note: You can safely ignore the DROP errors displayed while the SQL is executing.

Step 6: Preparing a table for the Netcool integration

Preparing REPORTER mode:

Use the scripts bundled in the $OMNIHOME/gates/jdbc2/scripts package.

Run the following SQL at the SQL prompt to provision the primary Reporting tables:

SQL>@<path>/oracle.reporting.sql

For example:

SQL> @/tmp/netcool_ora_sql_scripts/reporting_scripts/oracle.reporting.sql

Run the following SQL at the SQL prompt to provision the secondary Reporting audit tables, triggers and views:

SQL>@<path>/oracle.reporting_audit.sql

Check that the table and the tablespace were created correctly:

SQL> select owner, table_name from all_tables where tablespace_name='REPORTER';

Preparing AUDIT mode:

Use the scripts bundled in the $OMNIHOME/gates/jdbc2/scripts directory.

  1. Modify the orainstall script:

    1. Change the availability check in create_tables.audit.sql:

      if [ ! -f $OMNIHOME/gates/nco_g_oracle/sql_scripts/create_tables.audit.sql ];

      to

      if [ ! -f ./create_tables.audit.sql ];

    2. Change the location of the create_tables.audit.sql file.

      Comment out:

      #cat $OMNIHOME/gates/nco_g_oracle/sql_scripts/$INPUT_SQL_FILE | sed -e "s/__TABLESPACE__/$TABLESPACE/g" -e "s/__STATUS__/$STATUSTAB/g" -e "s/__JOURNAL__/$JOURNALTAB/g" -e "s/__DETAILS__/$DETAILSTAB/g" -e "s/__STORAGE__/$STORAGEOPT/g" >> /tmp/nco_oracle.sql

      Add the following code:

      cat ./$INPUT_SQL_FILE | sed -e "s/__TABLESPACE__/$TABLESPACE/g" -e "s/__STATUS__/$STATUSTAB/g" -e "s/__JOURNAL__/$JOURNALTAB/g" -e "s/__DETAILS__/$DETAILSTAB/g" -e "s/__STORAGE__/$STORAGEOPT/g" >> /tmp/nco_oracle.sql

  2. Run orainstall to generate the audit script from the template create_tables.audit.sql file.

    Note: Run orainstall from the same directory storing the audit SQL scripts.
    ./orainstall

    This creates the /tmp/nco_oracle.sql script.

  3. Run the nco_oracle.sql script.

    SQL> @/tmp/netcool_ora_sql_scripts/audit_scripts/nco_oracle.sql

  4. Verify that the tables were created by nco_oracle.sql:

    SQL> select TABLE_NAME from all_tables where TABLESPACE_NAME='SYSTEM' and (TABLE_NAME='STATUS' or TABLE_NAME='JOURNAL' or TABLE_NAME='DETAILS');

Step 7: Creating a password file

This step is only required if users other than the one specified in the table creation scripts in Step 6: Preparing a table for the Netcool integration were specified.

To create the password file, perform the following steps:

  1. Check the password file: 
    SQL> select username, from v$pwfile_users;

    Note: The default directory where the password file is located is: $ORACLE_HOME/dbs/orapw$ORACLE_SID

  2. If the password file is empty or does not exist, create it now:

    cd $ORACLE_HOME/bin
./orapwd FILE=/opt/oracle/product/19c/dbhome_1 /dbs/orapwortestdb ENTRIES=30 PASSWORD=<password of SYS>

    Note:

    FILE = $ORACLE_HOME/dbs/orapw$ORACLE_SID

    PASSWORD = SYS password should comply to password requirements (for example, username should not be a substring, and contain at least one numeric and one special character)

    If not specified, the password prompt will appear for input.

    ENTRIES = the maximum number of distinct SYSDBA, SYSOPER, SYSASM, SYSKM, SYSDG or SYSBACKUP users that can be stored in the password file.

    Important:

    The password should be set to PASSWORD or the password prompt will override the password originally specified in the CREATE DATABASE command.

    The password update can be verified using the attempt to connect by the following command: 

    SQL> connect sys as sysdba

    To update the SYS password, use following command: 

    SQL> ALTER USER sys IDENTIFIED BY "<password>";

    To use the SYS user to connect to the Oracle database, check whether SYS is in the password file using the following command:

    SQL> select * from v$pwfile_users;

  3. Grant the appropriate role to the user.

    1. Create the Oracle user by running the following command:

      SQL> CREATE USER <user> IDENTIFIED BY <password>;

      For example:

      SQL> CREATE USER jdbcgwoper IDENTIFIED BY jdbcgwoper_pass;

    2. Grant a role to user by running the following command:

      SQL> grant <role> to <user>;

      For example:

      SQL> grant sysoper to jdbcgwoper;

    3. Verify the user’s role by running the following command:

      SQL> select SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, SYSKM from v$pwfile_users where USERNAME='SYS';

Step 8: Updating the tnsnames.ora file

You may get the following error when connecting JDBC to Oracle database:

2020-05-07T23:27:36: Error: E-GJA-000-000: [ngjava]: G_JDBC: Thread-7: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA

2020-05-07T23:27:36: Error: E-GJA-000-000: [ngjava]: G_JDBC: Thread-7: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SID in CONNECT_DATA

If so, use the following steps:

  1. Update $ORACLE_HOME/network/admin/tnsnames.ora to include the SID in the CONNECT_DATA definition:

    ORCLCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLCDB)
      (SID = ortestdb)
    )
  )

LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = 1521))

  2. Start the listener using the following commands:

    $ORACLE_HOME/bin/lsnrctl start
$ORACLE_HOME/bin/lsnrctl status