Manually setting 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.
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 Running the set_system_parameter_oracle_pnet.sql script.
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;
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. While setting these parameters, ensure to use the Auto Memory Management (AMM) option.
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;
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
Only one data file is added to the tablespace created by the script. However, if required, you can add additional data files to the same tablespace before the tablespace is filled up.
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 Infrastructure Management data. You can configure the name and password.
- A report user. This user has the permission to access the Infrastructure Management 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>; 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:
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 Infrastructure Management. 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 Archive log mode:
select log_mode from v$database;
Enabling the ARCHIVELOG mode
Use the following command to enable the Archive log 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.