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:
- Step 1: Setting up the environment variables
- Step 2: Preparing an Oracle parameters file
- Step 3: Creating a binary parameter file
- Step 4: Creating a database instance
- Step 5: Building the Data Dictionary Views
- Step 6: Preparing a table for the Netcool integration
- Step 7: Creating a password file
- Step 8: Updating the tnsnames.ora file
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.
-
As root, set full permissions (apply
chmod 777
) to /opt/oracle_BASE. -
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.
Step 4: Creating a database instance
To create a database instance, use the following steps:
-
Run the following command to connect to an idle instance:
[oracle@klxv0104 bin]$ ./sqlplus / as sysdba
-
Run the following command:
SQL> CREATE SPFILE FROM PFILE;
This creates the following file: $ORACLE_HOME/dbs/spfileortestdb.ora
-
Run the following command to start the Oracle instance:
SQL> STARTUP NOMOUNT;
-
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:
-
Exit the NOMOUNT state: SHUTDOWN IMMEDIATE
-
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.
-
-
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;
-
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:
-
Log in as
SYSDBA
. -
Run the following command to start the database in
OPEN
state:SQL> STARTUP OPEN;
-
Run the following command to check the instance status:
SQL> select INSTANCE_NAME, STATUS from v$instance;
-
As
SYSDBA
, run the following scripts in the order shown:@?/rdbms/admin/catalog.sql @?/rdbms/admin/catproc.sql @?/rdbms/admin/utlrp.sql
-
Connect as
SYSTEM
at the SQL prompt, log in with theSYSTEM
password specified in the CREATE DATABASE command:SQL> connect system
-
Run the following command:
SQL> @?/sqlplus/admin/pupbld.sql
Note: You can safely ignore theDROP
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.
- Modify the orainstall script:
-
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 ];
-
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
-
-
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.
-
Run the nco_oracle.sql script.
SQL> @/tmp/netcool_ora_sql_scripts/audit_scripts/nco_oracle.sql
-
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:
- 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
-
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 distinctSYSDBA
,SYSOPER
,SYSASM
,SYSKM
,SYSDG
orSYSBACKUP
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 whetherSYS
is in the password file using the following command:SQL> select * from v$pwfile_users;
-
Grant the appropriate role to the user.
-
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;
-
Grant a role to user by running the following command:
SQL> grant <role> to <user>;
For example:
SQL> grant sysoper to jdbcgwoper;
-
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:
-
Update $ORACLE_HOME/network/admin/tnsnames.ora to include the
SID
in theCONNECT_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))
-
Start the listener using the following commands:
$ORACLE_HOME/bin/lsnrctl start $ORACLE_HOME/bin/lsnrctl status