Oracle How To

How to start ISIM Oracle DB

(should be started automatically via oratab)

sudo su - oracle
export ORACLE_SID=itimdb
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/dbhome_1
export PATH="$ORACLE_HOME/bin:$PATH"
echo startup | sqlplus -S "/ as sysdba"

How to stop ISIM Oracle DB

sudo su - oracle
export ORACLE_SID=itimdb
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/dbhome_1
export PATH="$ORACLE_HOME/bin:$PATH"
echo shutdown | sqlplus -S "/ as sysdba

How to test the database connection from a server

First make sure you can connect to the port (1113 for single db, 3030 for db clusters or whatever custom port DB might be running on)
telnet [server] [port]
If there is no telnet install it with yum install telnet
Get Instant Client Package - SQL*Plus fom http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

  • oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm (52,826,628 bytes) (cksum - 888077889)
  • oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm (708,104 bytes) (cksum - 3777165168)
Install
sudo rpm -i /tmp/oracle-instantclient12.2-*

Check connection. For SID:

LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib/:$LD_LIBRARY_PATH; /usr/bin/sqlplus64 -L "user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))"

For an Oracle service name:

/usr/bin/sqlplus64  "$DB_ADMIN/$DB_ADMIN_PWD@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=$DB_SERVER)(Port=$DB_SERVER_PORT))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=$DB_NAME)))"

Or check connection with a script like this:

#!/bin/sh
export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib/:$LD_LIBRARY_PATH
echo "exit" | /usr/bin/sqlplus64 -L uid/pwd@dbschemaname | grep Connected > /dev/null
if [ $? -eq 0 ] 
then
   echo "OK"
else
   echo "NOT OK"
fi

How to drop database though sqlplus

sqlplus / as sysdba
startup
shutdown immediate
startup mount exclusive restrict
drop database;

as a oneliner:

echo -e "shutdown immediate;\nstartup mount exclusive restrict;\ndrop database;\n" | sqlplus  "/ as sysdba"

Check if the database is a container or a non-container type

select name,cdb from v$database;

no in the cdb field indicates non-container db

How to install Oracle DB for ISIM

  1. You must create a non-container database. When you create the database, ensure that the Create as a Container database check box is clear.
  2. Copy the ORACLE_HOME/product/12.1.0/dbhome_1/dbs/init.ora file to a new ORACLE_HOME/dbs/initdb_name.ora file.
  3. Edit and set
db_name=itimdb
compatible=12.1.0
processes=150
shared_pool_size=50000000

  1. Tune other values per general best practices
  2. Additionally, define three control files for the IBM Security Identity Manager database. This example statement defines the control files for the UNIX operating system:
control_files=(ORACLE_HOME/oradata/itimdb/control01.ctl,ORACLE_HOME/oradata/ itimdb /control02.ctl,ORACLE_HOME/oradata/ itimdb /control03.ctl)

  1. Manually create all the directories defined in the initdb_name.ora file.
  2. Configure profile for the ITIM user
    1. Set the environment variables for Oracle by editing the .profile file with the appropriate values. Required environment variables include (use actual values):
ORACLE_SID=itimdb
ORACLE_BASE=/home/oracle/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
PATH=$ORACLE_HOME/bin;$PATH

  1. Manually create ISIM DB:
  2. Start the database instance with these commands:
# ./sqlplus "/ as sysdba"
SQL> startup nomount pfile= ORACLE_HOME/dbs/inititimdb.ora

  1. Save to a file and run the following:
-- Create database
CREATE DATABASE itimdb
CONTROLFILE REUSE
LOGFILE '/u01/oracle/itimdb/redo01.log' SIZE 1M REUSE,
'/u01/oracle/itimdb/redo02.log' SIZE 1M REUSE,
'/u01/oracle/itimdb/redo03.log' SIZE 1M REUSE,
'/u01/oracle/itimdb/redo04.log' SIZE 1M REUSE
DATAFILE '/u01/oracle/itimdb/system01.dbf' SIZE 10M REUSE
AUTOEXTEND ON
NEXT 10M MAXSIZE 200M
CHARACTER SET UTF8;
-- Create another (temporary) system tablespace
CREATE ROLLBACK SEGMENT rb_temp STORAGE (INITIAL 100 k NEXT 250 k);
-- Alter temporary system tablespace online before proceeding
ALTER ROLLBACK SEGMENT rb_temp ONLINE;
-- Create additional tablespaces ...
-- RBS: For rollback segments
-- USERs: Create user sets this as the default tablespace
-- TEMP: Create user sets this as the temporary tablespace
CREATE TABLESPACE rbs
DATAFILE '/u01/oracle/itimdb/itimdb.dbf' SIZE 5M REUSE AUTOEXTEND ON
NEXT 5M MAXSIZE 150M;
CREATE TABLESPACE users
DATAFILE '/u01/oracle/itimdb/users01.dbf' SIZE 3M REUSE AUTOEXTEND ON
NEXT 5M MAXSIZE 150M;
CREATE TABLESPACE temp
DATAFILE '/u01/oracle/itimdb/temp01.dbf' SIZE 2M REUSE AUTOEXTEND ON
NEXT 5M MAXSIZE 150M;
-- Create rollback segments.
CREATE ROLLBACK SEGMENT rb1 STORAGE(INITIAL 50K NEXT 250K)
tablespace rbs;
CREATE ROLLBACK SEGMENT rb2 STORAGE(INITIAL 50K NEXT 250K)
tablespace rbs;
CREATE ROLLBACK SEGMENT rb3 STORAGE(INITIAL 50K NEXT 250K)
tablespace rbs;
CREATE ROLLBACK SEGMENT rb4 STORAGE(INITIAL 50K NEXT 250K)
tablespace rbs;
-- Bring new rollback segments online and drop the temporary system one
ALTER ROLLBACK SEGMENT rb1 ONLINE;
ALTER ROLLBACK SEGMENT rb2 ONLINE;
ALTER ROLLBACK SEGMENT rb3 ONLINE;
ALTER ROLLBACK SEGMENT rb4 ONLINE;
ALTER ROLLBACK SEGMENT rb_temp OFFLINE;
DROP ROLLBACK SEGMENT rb_temp ;

  1. Install the JVM for the DB (the value for “manager” is the password for the system user account)
# sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> @$ORACLE_HOME/javavm/install/initjvm.sql
SQL> @$ORACLE_HOME/xdk/admin/initxml.sql
SQL> @$ORACLE_HOME/xdk/admin/xmlja.sql
SQL> @$ORACLE_HOME/rdbms/admin/catjava.sql
SQL> connect system/manager
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

  1. Enable XA recovery operations. itim_db_user is the itim user for the db
# sqlplus "/ as sysdba"
grant select on pending_trans$ to public;
grant select on dba_2pc_pending to public;
grant select on dba_pending_transactions to public;
grant execute on dbms_system to itim_db_user;

  1. Stop and restart the database instance for these changes to take effect.
  2. Start the Oracle listener service if not already started
# su - oracle
# ./lsnrctl start

  1. To ensure that Oracle processes are started, enter this command:
ps -ef | grep ora

  1. To ensure that the listener is running, enter this command:
# ./lsnrctl status

  1. Supply IdM team with the ojdbc6.jar file for Oracle 12 c Release 1 (12.1.0.2)

How to upgrade database manually

If the administrative user ID does not have the proper privileges to create the database schema, an error message appears during the upgrade. You should run the ITIM_HOME\bin\DBUpgrade program after the upgrade completes and enter the correct database administrative ID. This ensures the database schema and tables for the messaging engine are created. The log can be viewed in
/isim/install_logs/dbUpgrade.stdout