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 NetBackup IT Analytics.

Prerequisites

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 a PDB on central database server. The name can be anything that Oracle supports. Example: scdb or itadb. Ensure 16k block sizes are supported. SQL> select name, block_size, current_size from v$buffer_pool;

Create database and users:

Create tablespaces specific to NetBackup 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 NetBackup IT Analytics with a custom password:
- Copy the config.sh file 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.
```
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 NetBackup 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.

Create database and users in existing Oracle farm in a Split Architecture deployment

Feedback

Feedback