Step by Step to create the Niku Database under Oracle

Document ID : KB000045571
Last Modified Date : 14/02/2018
Show Technical Document Details

Summary: 

This document is a step by step of how to create the NIKU database and user under the Oracle server with all the option needed to .

Instructions: 

Step A: Create the Database NIKU

1. Open the Command Windows  as a Administrator under the Oracle server.

2. From the command line prompt to create the Database please run this line, it is verify important to know that the line run is case sensitive in the creation:

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname NIKU -sid NIKU -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL

NOTE: This creation with ask you for the password for the users : SYS, SYSTEM, DBSNMP, SYSMAN 

 

3. From the command line prompt to login using the SQLPLUS and after run the commands:

sqlplus system@niku as sysdba

alter system set nls_date_format="YYYY-MM-DD HH24:MI:SS" scope=spfile;

alter system set nls_sort="BINARY" scope=spfile;

alter system set nls_comp="BINARY" scope=spfile;

alter system set query_rewrite_enabled="true" scope=spfile;

alter system set cursor_sharing=”FORCE” scope=spfile;

alter system set processes=1000 scope=spfile;

alter system set sessions=1000 scope=spfile;

alter system set open_cursors=1000 scope=spfile;

exit;

 

4. In order to create the Tablespaces needed, from the command line prompt to login using the SQLPLUS and after run the commands:

sqlplus sys@niku as sysdba

 

Note 1: Run one at a time until it is create, only then create the next one, we have identified here.

Note 2: Please verify that the path is the correct one X:\app\Administrator\oradata\NIKU\ to the one you have !!!

 

CREATE TABLESPACE "USERS_LARGE" DATAFILE

'X:\app\Administrator\oradata\NIKU\USERS_LARGE.DBF' SIZE 2G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 10G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

CREATE TABLESPACE "USERS_SMALL" DATAFILE

'X:\app\Administrator\oradata\NIKU\USERS_SMALL.DBF' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 4G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

CREATE TABLESPACE "INDX_LARGE" DATAFILE

'X:\app\Administrator\oradata\NIKU\INDX_LARGE.DBF' SIZE 2G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 10G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

CREATE TABLESPACE "INDX_SMALL" DATAFILE

'X:\app\Administrator\oradata\NIKU\INDX_SMALL.DBF' SIZE 1G REUSE AUTOEXTEND ON NEXT 1G MAXSIZE 4G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

 

Step B: Create the user and grant the privileges for "niku"

In order to create the user and add privileges needed, from the command line prompt to login using the SQLPLUS and after run the commands:

sqlplus sys@niku as sysdba

CREATE USER niku IDENTIFIED BY niku DEFAULT TABLESPACE USERS_LARGE TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;

 

GRANT CREATE SESSION TO niku;

GRANT ALTER SESSION TO niku;

GRANT CREATE TABLE TO niku;

GRANT CREATE TRIGGER TO niku;

GRANT CREATE VIEW TO niku;

GRANT QUERY REWRITE TO niku;

GRANT CREATE PROCEDURE TO niku;

GRANT UNLIMITED TABLESPACE TO niku;

GRANT CONNECT TO niku;

 

GRANT RESOURCE TO niku;

Exit;

 

Step C: Restore the NIKU CA PPM database from the IMAGE

1. From the Image of the CA PPM version "CA PPM Install Media 14.X, DVD Master" you will have to copy the directory: 

DVD:\Database      or        ISO:\Database

to the local disk, the hold directory \Database

EXAMPLE :

          F:\Database    to    C:\SOFTWARE\Database

 

2. To set the the import directory under Oracle to do the restore, from the Windows command line prompt as a Administrator you have to run :

sqlplus system@NIKU as sysdba

create directory import_directory as 'C:\Software\Database';

exit;

 

3. From the command line prompt to do the loading of the database you have to run: 

impdp system@niku DIRECTORY= import_directory transform=oid:n DUMPFILE=oracle_base.db SCHEMAS=niku LOGFILE=import.log

 

Best Practice:

In order to resolve invalid objects in the imported database, please run these steps, from the command line prompt to login using the SQLPLUS and after run the commands:

 

sqlplus sys@niku as sysdba

EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema=>'NIKU');