Set Up Oracle

Topics marked with * relate to features available only in vFabric Hyperic.

Set Up Hyperic Database on Oracle

This section has instructions for setting up Oracle as your external Hyperic database. It is assumed that you have already installed Oracle and are either familiar with Oracle or have the support of someone who is.

Create Hyperic Database

The database can be created with Oracle Database Configuration Assistant.

Select New Database (Includes datafiles = No). To save space, decline to install the Example Schemas.

Assuming Oracle runs on a dedicated host, you can select the "Typical Memory" configuration.

Select "OLTP" as the type of database sizing to use. Allocate as high a percentage of system resources as you can afford: 70-90%, ideally in the higher range. See Configure Initialization Parameters for Oracle for SGA and PGA size.

Hyperic requires utf-8 character encoding

Create the database with utf-8 character encoding.

Create Tablespaces

  1. Create the TEMP_HQDB temporary tablespace, 2 GB in size

  2. Create the TS_HQDB tablespace, 25 GB in size. This tablespace will be used to store HQ_METRIC_DATA_*D_*S tables.

Create the Database User

In this step you create the database user account that the server will use to access the Oracle database.

There are multiple methods for creating a user in Oracle. To do it using SQL*Plus, log into the Oracle instance as the system user with SQL*Plus, and issue the create user command:

SQL> CREATE USER HQUSER IDENTIFIED BY HQPASSWORD DEFAULT TABLESPACE TS_HQDB;

replacing HQUSER and HQPASSWORD with desired values.

Grant Permissions to the Database User

You can grant the database user the necessary permissions in SQL*Plus with the grant command:

SQL> GRANT CONNECT, RESOURCE, CREATE VIEW TO HQUSER;

Verify the permission setting:

SQL> SELECT GRANTED_ROLE, DEFAULT_ROLE FROM dba_role_privs WHERE grantee = 'HQUSER';

Make sure that you see the following rows for CONNECT and{{ RESOURCE}} roles:

GRANTED_ROLE DEFAULT_ROLE
CONNECT YES
RESOURCE YES

If that is not the case, update the permissions:

ALTER USER HQUSER DEFAULT ROLE RESOURCE, CONNECT;

Set Up Hyperic Server

For instructions, see Step 3 - Set Up Hyperic Server of the Hyperic Installation and Startup Process.

Enable Row Movement


After installing Hyperic Server, run the
enable_row_movement.sql procedure attached to this page — this enables the routine maintenance described in Periodic Oracle Database Maintenance. It is only necessary to enable row movement once; you do not need to do it on a recurring basis.

Tuning Hyperic Database on Oracle for Medium to Large Environments


If you manage more than 100 platforms, follow the steps in this section to tune your Oracle-hosted Hyperic database.

Create the TS_HQDB_16K Tablespace

Create the TS_HQDB_16K tablespace, 25 GB in size, with 16 K blocksize. Here is an example of the syntax to create a table space with 16 K blocksize, where 'datafile_name.dbf' is a file where the tablespace data will physically reside:

CREATE TABLESPACE TS_HQDB_16K
datafile 'datafile_name.dbf' SIZE 25000M AUTOEXTEND OFF
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
;

Configure REDO Logs

Redo logs are transaction journals. Each transaction is recorded in the redo logs. Redo logs are used in a serial fashion with each transaction queuing up in the redo log buffers and being written one at a time into the redo logs.

Configure at least three REDO logs, each 2048 MB in size.

Locate REDO logs on a separate disk spindle from datafiles.

Do not locate REDO logs on a RAID array.

Configure Initialization Parameters for Oracle

Set following Oracle initialization parameters:

DB_WRITER_PROCESS = 4
SGA_MAX = 8G
SGA_TARGET = 7G
SHARED_POOL_SIZE=700M
PGA_AGGREGATE_TARGET=1500M
DB_16K_CACHE_SIZE=1000M
DB_KEEP_CACHE_SIZE = 500M
FILESYSTEMIO_OPTIONS=SetAll
DB_FILE_MULTIBLOCK_READ_COUNT=16
OPEN_CURSORS=300
PROCESSES=500

Configure Batch Aggregate Inserter

  1. Make sure that the Hyperic Server is shut down.

  2. Log into the Oracle instance as the Hyperic user with SQL*Plus, and issue these commands to increase the data aggregate inserter batch size to 8000 and number of workers to 10:

UPDATE EAM_CONFIG_PROPS SET PROPVALUE=8000 where PROPKEY='BATCH_AGGREGATE_BATCHSIZE';
UPDATE EAM_CONFIG_PROPS SET PROPVALUE=10 where PROPKEY='BATCH_AGGREGATE_WORKERS';
UPDATE EAM_CONFIG_PROPS SET PROPVALUE=5000000 where
PROPKEY='BATCH_AGGREGATE_QUEUE';

Move Database Tables

While still logged into the Oracle instance as the system user, run these commands to move metric and measurement tables to the TS_HQDB_16K tablespace:

alter table HQADMIN.HQ_METRIC_DATA_0D_0S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_0D_1S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_1D_0S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_1D_1S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_2D_0S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_2D_1S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_3D_0S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_3D_1S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_4D_0S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_4D_1S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_5D_0S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_5D_1S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_6D_0S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_6D_1S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_7D_0S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_7D_1S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_8D_0S move tablespace TS_HQDB_16K;
alter table HQADMIN.HQ_METRIC_DATA_8D_1S move tablespace TS_HQDB_16K;
alter table HQADMIN.EAM_MEASUREMENT_DATA_1D move tablespace TS_HQDB_16K;
alter table HQADMIN.EAM_MEASUREMENT_DATA_1H move tablespace TS_HQDB_16K;
alter table HQADMIN.EAM_MEASUREMENT_DATA_6H move tablespace TS_HQDB_16K;

Rebuild Indexes for Moved Tables

Run these commands to rebuild the indexes for the moved tables:

alter index HQADMIN.METRIC_DATA_0D_0S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_0D_1S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_1D_0S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_1D_1S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_2D_0S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_2D_1S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_3D_0S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_3D_1S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_4D_0S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_4D_1S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_5D_0S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_5D_1S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_6D_0S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_6D_1S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_7D_0S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_7D_1S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_8D_0S_MID_IDX rebuild;
alter index HQADMIN.METRIC_DATA_8D_1S_MID_IDX rebuild;
alter index HQADMIN.MEASUREMENT_DATA_1H_MID_IDX rebuild;
alter index HQADMIN.MEASUREMENT_DATA_6H_MID_IDX rebuild;
alter index HQADMIN.MEASUREMENT_DATA_1D_MID_IDX rebuild;

Configure Tables for High Concurrency

alter table <schema>.HQ_METRIC_DATA_0D_0S initrans 15;
alter index <schema>.METRIC_DATA_0D_0S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_0D_1S initrans 15;
alter index <schema>.METRIC_DATA_0D_1S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_1D_0S initrans 15;
alter index <schema>.METRIC_DATA_1D_0S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_1D_1S initrans 15;
alter index <schema>.METRIC_DATA_1D_1S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_2D_0S initrans 15;
alter index <schema>.METRIC_DATA_2D_0S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_2D_1S initrans 15;
alter index <schema>.METRIC_DATA_2D_1S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_3D_0S initrans 15;
alter index <schema>.METRIC_DATA_3D_0S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_3D_1S initrans 15;
alter index <schema>.METRIC_DATA_3D_1S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_4D_0S initrans 15;
alter index <schema>.METRIC_DATA_4D_0S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_4D_1S initrans 15;
alter index <schema>.METRIC_DATA_4D_1S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_5D_0S initrans 15;
alter index <schema>.METRIC_DATA_5D_0S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_5D_1S initrans 15;
alter index <schema>.METRIC_DATA_5D_1S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_6D_0S initrans 15;
alter index <schema>.METRIC_DATA_6D_0S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_6D_1S initrans 15;
alter index <schema>.METRIC_DATA_6D_1S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_7D_0S initrans 15;
alter index <schema>.METRIC_DATA_7D_0S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_7D_1S initrans 15;
alter index <schema>.METRIC_DATA_7D_1S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_8D_0S initrans 15;
alter index <schema>.METRIC_DATA_8D_0S_MID_IDX initrans 15;
alter table <schema>.HQ_METRIC_DATA_8D_1S initrans 15;
alter index <schema>.METRIC_DATA_8D_1S_MID_IDX initrans 15;

Restart Hyperic Server

Restart the Hyperic Server.

Periodic Oracle Database Maintenance

Perform the following maintenance after running Hyperic for about a week, and monthly thereafter:

  1. Run the table_maintenance.sql procedure attached to this page to compact and shrink space on all Hyperic tables (except HQ_METRIC* tables).

  2. Run the rebuild_index.sql procedure attached to this page to rebuild the index on all Hyperic tables.

  3. Run tablespace maintenance as appropriate:

    1. Perform the following query to determine how fragmented a tablespace is. The query returns the tablespace name and total count of holes in it.

      select count(*), tablespace_name 
      from dba_free_space
      group by tablespace_name
      order by 1,2;
    2. If the hole count is in the order of thousands, run Reorganize.

      images/download/attachments/79038211/tablespaceMaintenance.png