Alert.png The wiki is deprecated and due to be decommissioned by the end of September 2022.
The content is being migrated to other supports, new updates will be ignored and lost.
If needed you can get in touch with EGI SDIS team using operations @ egi.eu.

GOCDB/Regional Module Technical Documentation/DeployOnOracle

From EGIWiki
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

<<Back to Install

Oracle DB Preparation Example

We advise that you create a dedicated GOCDB5 user. For Oracle, you can create the user with the following script (substitute GOCDB5 for your username and <PASSWORD> with a sensible password). Run this script as the Oracle admin/system user:

-- Manage GOCDB5 user if already exists (optional) --
drop user gocdb5 cascade;
ALTER USER gocdb5 IDENTIFIED BY new_password;

-- CREATE USER SQL
CREATE USER GOCDB5 IDENTIFIED BY <PASSWORD> 
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- ROLES
GRANT "RESOURCE" TO GOCDB5 ;
-- SYSTEM PRIVILEGES
GRANT CREATE TRIGGER TO GOCDB5 ;
GRANT CREATE SEQUENCE TO GOCDB5 ;
GRANT CREATE TABLE TO GOCDB5 ;
GRANT CREATE JOB TO GOCDB5 ;
GRANT CREATE PROCEDURE TO GOCDB5 ;
GRANT CREATE TYPE TO GOCDB5 ;
GRANT CREATE SESSION TO GOCDB5 ;

Manage the GOCDB password expiry

By default, Oracle 11g will expire a password in 180 days. In previous versions of Oracle, the default policy was UNLIMITED, so please be aware of this change! As a system user, you can see your password expiry settings by looking at the PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME parameters in the DBA_PROFILES table:

-- select the profile for the GOCDB user (e.g. will return DEFAULT)
SELECT profile FROM dba_users WHERE username = 'GOCDB5'; 

-- select the password expiry settings for the profile assigned to the GOCDB5 user
select resource_name,resource_type, limit from dba_profiles where profile=DEFAULT; 


If you prefer, you can update the default expiry from 180days to UNLIMITED using the following (assumng GOCDB5 user profile is DEFAULT):

-- requires system privilege
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;