Manual steps to set up an Oracle database

This section helps the database administrator to manually set the system parameters, create tablespaces, user accounts, and permissions for Oracle single instance database and Oracle RAC. If you have not run the BMC-provided script, you can follow the steps provided in the following sections.

Note

Although you can set up the Oracle database manually, BMC recommends that you run the BMC-provided scripts for setting up the Oracle database. For more information, see Steps to run the BMC provided script set_system_parameter_oracle_pnet.sql.

Setting system parameters

Use the ALTER SYSTEM SET command by using SQLPlus to set the following parameters.

bitmap_merge_area_size =0 scope =spfile;
commit_point_strength =0 scope =spfile;
db_block_checksum =typical scope =spfile;
db_flashback_retention_target =0 scope =spfile;
db_writer_processes =2 scope =spfile;
fast_start_mttr_target =0 scope =spfile;
fast_start_parallel_rollback =LOW scope =spfile;
job_queue_processes =20 scope =spfile;
parallel_execution_message_size =4096 scope =spfile;
remote_login_passwordfile =EXCLUSIVE scope =spfile;
replication_dependency_tracking =FALSE scope =spfile;
session_cached_cursors =75 scope =spfile;
session_max_open_files =20 scope =spfile;
streams_pool_size =0 scope =spfile;
timed_statistics =TRUE scope =spfile;
undo_management =AUTO scope =spfile;
undo_retention =10800 scope =spfile;
shared_pool_size =0 scope =spfile;
java_pool_size =0 scope =spfile;
large_pool_size =0 scope =spfile;
processes =1000 scope=spfile;
open_cursors=4000;
sga_target=0 scope=spfile;
pga_aggregate_target=0 scope=spfile;

Set the memory_max_target and memory_target parameters to the maximum memory you can allocate for your Oracle database instance. The memory for each parameter must be set to a minimum 50% of the total memory available for one Oracle database instance.

For example, if the RAM size is 4GB, the following parameters must be set to 2GB:

Alter system set memory_max_target=2000M scope=spfile;
Alter system set memory_target=2000M scope=spfile;

Creating tablespaces

Running the run_oracle_db_scripts.sql script creates a tablespace with a data file with an initial size of 1 GB and AUTOEXTEND to ON with NEXT size of 500M. To increase the size of AUTOEXTEND NEXT value, use the following procedure. BMC recommends that you create two tablespaces, a main and a temporary tablespace.

Manually creating a tablespace

When the BMC-provided script asks you to enter the tablespace sizes, use the following guidelines to set the tablespace size for small, medium, and large deployments:

Small: 15 GB
Medium: 30 GB
Large: 30 GB

CREATE TABLESPACE <tablespace name> DATAFILE '<path>' SIZE 1G REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 15G;
Alter system set memory_target=2000M scope=spfile;
CREATE TEMPORARY TABLESPACE <temporary tablespace name> TEMPFILE '<path>' SIZE 200M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

BMC recommends you to use the following data file sizes while creating tablespaces:

Initial SIZE: 1 GB
REUSE AUTOEXTEND ON NEXT: 500 MB
MAXSIZE: 15 GB or you can increase the size as per your requirement

Extending the tablespace

Use the following command to extend the tablespace with an additional data file:

ALTER TABLESPACE PNET_TS ADD DATAFILE '<path>' SIZE 1G AUTOEXTEND ON NEXT 500M MAXSIZE 30G;

Creating user accounts and permissions

Create the following database users:

A main or schema user, by default proact. This user has the permission to store BMC ProactiveNet data. You can configure the name and password.
A report user. This user has the permission to access the BMC ProactiveNet data.

Use the following commands to create main and report users instead of using the BMC provided script:

CREATE USER <userName> IDENTIFIED BY <passwd>;

<userName> is the user being created and, and <passwd> is the password.

ALTER USER <userName> DEFAULT TABLESPACE <tablespace name> QUOTE UNLIMITED ON <tablespace name>;
ALTER USER <userName> TEMPORARY TABLESPACE <temporary tablespace name>;

The user is assigned the default tablespace and temporary tablespace.

To grant privileges to users, use the following command:

GRANT <privilege> TO <userName>;

<privilege> is the permission, and <userName> is the user to whom the permission is granted.

Use the list of permissions given below along with the GRANT command for the schema user.

grant connect
grant resource
grant create view
grant select_catalog_role
grant execute_catalog_role
grant gather_system_statistics
grant select any table
grant select on dba_data_files
grant select on dba_temp_files
grant select on dba_jobs_running
grant select on dba_jobs
grant select on dba_scheduler_jobs
grant QUERY REWRITE
grant UNLIMITED TABLESPACE
grant select on sys.v_$parameter
grant select on sys.dba_free_space
grant select on sys.dba_tablespaces
grant select on sys.v_$session
grant select on sys.v_$lock
grant select on sys.dba_jobs
grant select on sys.v_$waitstat
grant select on sys.v_$sysstat
grant select on sys.v_$librarycache
grant select on sys.v_$rowcache
grant select on sys.dba_users
grant select on sys.dba_roles
grant select on sys.dba_tab_privs
grant select on sys.dba_data_files
grant select on sys.dba_temp_files
grant select on sys.v_$instance
grant select on sys.v_$rollname
grant select on sys.v_$rollstat
grant select on sys.v_$system_event
grant select on sys.v_$log
grant select on sys.v_$archive_dest
grant select on sys.v_$database
grant select on sys.props$

You must grant the report user with the following permission:

grant connect

To create a permanent password that will never expire, use the following commands:

CREATE PROFILE <UserName>_PROFILE LIMIT
PASSWORD_LIFE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER USER <UserName> PROFILE <UserName>_PROFILE;
ALTER USER <report UserName> PROFILE <UserName>_PROFILE;

Restart the Oracle database instance after setting all the above parameters. If you are using Oracle 12c, open all the Pluggable Databases (PDBs) relevant to BMC ProactiveNet. For information about stopping and starting the Oracle database, see Stopping and starting the Oracle database.

Recommendations and restrictions for creating Oracle objects

Ensure that the following guidelines are met while creating Oracle objects:

The Oracle user name must start with a letter from the English alphabet.
The tablespace name can start either with a letter or with the underscore (_) special character.
The Oracle user name and the tablespace name can contain special characters within them. The special characters $, #, and _ are allowed.
While creating the Oracle user name, tablespace name, and password, ensure that you do not leave a space between the characters.
The data file entry in a tablespace can have a space between characters. For example: C:\apps<space>oracle\oradata\main<space>ts.dbf
While creating the Oracle user name, tablespace name, and password, ensure that the length for each of these is not more than thirty characters.

Running in the ARCHIVELOG mode

Ensure that the Oracle database is running in ARCHIVELOG mode. Running in the ARCHIVELOG mode enables you to back up your log files and perform point-in-time recovery.

Use the following command to check the type of ARCHIVELOG mode:

select log_mode from v$database;

Enabling the ARCHIVELOG mode

Use the following command to enable the ARCHIVELOG mode:

ALTER SYSTEM SET log_archive_dest_1='location=/home/oracle/archivelog' SCOPE=spfile;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Archive log files use large amounts of disk space. Ensure that you delete the old archive log files at regular intervals. For information about managing archive log files, see the Oracle database documentation.