Create database and users in existing Oracle farm in a Split Architecture deployment
This procedure provides the steps to create database and users in an existing Oracle farm during a Split-Architecture deployment of IT Analytics.
Table: Prerequisites for creating database and users in existing Oracle farm
Requirement | Description |
|---|---|
Oracle | Version 19c with latest security patches applied. |
Pluggable Database (PDB) |
|
Create database and users:
- Create tablespaces specific to IT Analytics.
Tablespace name
Reference commands
aptare_tbs_data_1m
CREATE TABLESPACE aptare_tbs_data_1m DATAFILE <datafile path> SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO /
aptare_tbs_idx_1m
CREATE TABLESPACE aptare_tbs_idx_1m DATAFILE <datafile path> SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO /
aptare_tbs_data_20m
CREATE TABLESPACE aptare_tbs_data_20m DATAFILE <datafile path> SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO /
aptare_tbs_idx_10m
CREATE TABLESPACE aptare_tbs_idx_10m DATAFILE <datafile path> SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO /
aptare_tbs_data_200m
CREATE TABLESPACE aptare_tbs_data_200m DATAFILE <datafile path> SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO /
aptare_tbs_idx_100m
CREATE TABLESPACE aptare_tbs_idx_100m DATAFILE <datafile path> SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO /
aptare_tbs_data_200m_lob
CREATE TABLESPACE aptare_tbs_data_200m_lob DATAFILE <datafile path> SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO /
aptare_tbs_data_200m_col
CREATE TABLESPACE aptare_tbs_data_200m_col DATAFILE <datafile path> SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO /
aptare_tbs_iot_200m
CREATE TABLESPACE aptare_tbs_iot_200m DATAFILE <datafile path> SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO /
Information on the file system size to be provisioned for the data files is provided in Step-3. See Step-3: Portal and database prerequisites.
- Create two database schema users: PORTAL and APTARE_RO.
For PORTAL user, assign the password portal and for APTARE_RO user, assign the password aptaresoftware123. You can change these while creating a user with the SQL query below.
CREATE USER PORTAL PROFILE DEFAULT IDENTIFIED BY portal DEFAULT TABLESPACE aptare_tbs_data_1m TEMPORARY TABLESPACE aptare_temp_tbs ACCOUNT UNLOCK; CREATE USER APTARE_RO IDENTIFIED BY aptaresoftware123 DEFAULT TABLESPACE APTARE_TBS_DATA_1M TEMPORARY TABLESPACE APTARE_TEMP_TBS; - Create the following required database directories with the required permissions:
CREATE OR REPLACE DIRECTORY UNIX_DIR AS '/u01/aptare/database'; CREATE OR REPLACE DIRECTORY LOGFILE_DIR AS '/tmp'; GRANT READ,WRITE ON DIRECTORY UNIX_DIR to portal; GRANT READ,WRITE ON DIRECTORY LOGFILE_DIR to portal;
Note that he folder path on the database server filesystem can be changed to other path as well.
- Grant the following privileges to the respective user schema:
User-specific Schema
Grants required for the schema
APTARE_RO
GRANT CONNECT TO aptare_ro; GRANT CREATE ANY CONTEXT TO aptare_ro; GRANT CREATE SYNONYM TO aptare_ro;
PORTAL
GRANT CONNECT TO PORTAL; GRANT RESOURCE TO PORTAL; GRANT CREATE TABLESPACE TO PORTAL; GRANT UNLIMITED TABLESPACE TO PORTAL; GRANT QUERY REWRITE TO PORTAL; GRANT SELECT_CATALOG_ROLE TO PORTAL; GRANT SELECT ANY DICTIONARY TO PORTAL; GRANT CREATE ANY LIBRARY TO PORTAL; GRANT SELECT ON dba_free_space TO PORTAL; GRANT SELECT ON dba_data_files TO PORTAL; GRANT SELECT ON dba_temp_files TO PORTAL; GRANT CREATE SESSION, CREATE ANY CONTEXT TO PORTAL;
Statement to execute the dbms_session package:
GRANT EXECUTE ON DBMS_SESSION TO PORTAL; GRANT EXECUTE ON DBMS_LOCK TO PORTAL;
Statement to execute the dbms_utility package
GRANT EXECUTE ON DBMS_UTILITY TO PORTAL; GRANT CREATE VIEW TO PORTAL; GRANT CREATE TABLE TO PORTAL;
Statement to create materialized views
GRANT CREATE MATERIALIZED VIEW TO PORTAL;
Statement to set up the logging directory
GRANT CREATE ANY DIRECTORY TO PORTAL;
Statement to add for Oracle 19c
GRANT CREATE JOB TO PORTAL; GRANT EXECUTE ON DBMS_SCHEDULER TO PORTAL;
Statement to add security
GRANT EXECUTE ON SYS.DBMS_LOB TO PORTAL; GRANT EXECUTE ON SYS.UTL_FILE TO PORTAL; GRANT EXECUTE ON SYS.DBMS_RANDOM TO PORTAL; GRANT EXECUTE ON SYS.DBMS_JOB TO PORTAL; GRANT EXECUTE ON SYS.DBMS_SQL TO PORTAL; GRANT READ ON SYS.ALL_DIRECTORIES TO PORTAL; GRANT READ ON SYS.DUAL TO PORTAL;
Statement to enable FIPS compliance
GRANT EXECUTE ON DBMS_CRYPTO TO PORTAL;
- To install IT Analytics with a custom password:
Copy the
config.shfile to a local directory on the Portal server so that files can be edited.# mount -o loop itanalytics_installer_11400_linux.iso /mnt/diska # mkdir /tmp/portal # cp /mnt/diska/config.sh /tmp/portal
Update PORTAL and APTARE_RO user passwords in
/tmp/portal/config.sh.Note:
If you are using customized password for PORTAL and APTARE_RO database users during a fresh IT Analytics Portal installation, exclude special characters like &, ?, \, <, >, ! from the passwords.
ORACLE_PORTAL_SEC_CODE=<password for PORTAL user> ORACLE_APTARE_RO_SEC_CODE=<password for APTARE_RO user>
Run the Portal installer
Itanalyticsinstaller.sh./mnt/diska/Itanalyticsinstaller.sh -C /tmp/portal/config.sh
Follow the step-4 onwards described in Install Portal binaries where Oracle application binaries are deployed on a separate machine section on a separate machine to deploy the Portal binaries.
- To install IT Analytics with a default password, follow the step-3 onwards described in Install Portal binaries where Oracle application binaries are deployed on a separate machine section on a separate machine to deploy the Portal binaries.