Oracle® Communications ASAP System Administrator's Guide
Release 7.2
E18879-01
  Go To Table Of Contents
Contents
Previous
Previous 		 Next
Next		  

6 Managing the Database and File System

This chapter provides information about managing your Oracle Communications ASAP database and file system.

Overview of Managing the Database and File System

The Oracle database and file system management tasks include:

Configuring Kernel and Database Initialization Parameters

This sections outline some important issues for implementing ASAP. These issues are listed in point form for easy scanning. For a complete reference on the subjects, refer to the appropriate Oracle documentation.

1. DB_BLOCK_SIZE

Set the DB_BLOCK_SIZE – 8K is the default. ASAP benefits from a larger block size. This must be done during database creation and cannot be changed.

2. DB_BLOCK_BUFFERS

DB_BLOCK_BUFFERS is the area of the SGA that is used for the storage and processing of data in memory. As users request information, the information is put into memory. If the DB_BLOCK_BUFFERS parameter is set too low, the least recently used data will be flushed from memory. Set this value between 3000 to 5000 depending on the memory limitations of your machine. A value of 5000 with a block size of 8K uses approximately 40MB of memory.

3. SHARED_POOL_SIZE

SHARED_POOL_SIZE is used to process the procedures, packages, and triggers, as well as the library and data dictionary cache. If the SHARED_POOL_SIZE is set too low, you will not get the full advantage of your DB_BLOCK_BUFFERS. Set this value between 90MB to 150MB. For most installations this should be sufficient. Again this value should be adjusted depending on the memory limitations of your machine. Setting the SHARED_POOL_SIZE too high can cause your system to swap excessively.

4. SESSIONS

SESSIONS determines the number of user connections that ASAP can establish. Configure 150-200 sessions for each ASAP environment, more if you are running multiple Service Request Processor (SRP) and Network Element Processors (NEPs). Be sure to allocate enough semaphores at the UNIX level to accommodate the amount of sessions.

In all the cases above, the values given as examples only. The appropriate value for your installation will depend on the machine characteristics and limitations.

About Monitoring Database Segment and File System Size

The Control server has a background thread that monitors database table and log segment sizes. This thread reads a static configuration database table to determine database size thresholds. Should a particular threshold be exceeded, the thread issues the system event specified in the static table tbl_db_threshold in the Control database. This feature provides a warning to the operations center should the threshold be exceeded.

In addition, the Control server has a background thread that monitors operating system file sizes. This thread reads a static configuration database table to determine file system size thresholds. Should a particular threshold be exceeded, the thread issues the system event specified in the static table tbl_fs_threshold in the Control database. This feature allows ASAP to warn the operations center whenever the free disk space drops below the minimum threshold set by you.

The monitoring thresholds function is used to specify thresholds for ASAP databases and transactions affecting it. When these thresholds are defined, an ASAP Control server can monitor the specified database data and transaction log sizes at intervals specified in the ASAP environment variable DB_MONITOR_TIME. If any of the thresholds are exceeded, the Control server issues the system event defined by the user. The event can trigger an alarm or be logged.

See "Configuring System Events and Alarms Using Stored Procedures" for more information on configuring alarms.

An ASAP environment can be partitioned on various machines, and each machine can be monitored by its respective Control server. Typically, one ASAP installation has multiple databases and the ASAP environment can be distributed on one machine or multiple machines. The database and transaction log threshold need to be specified for each database to be monitored.

You can use the DB_MONITOR_TIME and FS_MONITOR_TIME parameters to configure the Control server database and file system monitoring capabilities. For more information about these parameters, see the chapter about the ASAP parameter configuration file (ASAP.cfg) in the ASAP Server Configuration Guide.

Configuring File System Thresholds

To configure file system thresholds:

  1. Open a UNIX terminal to your ASAP environment.

  2. Using a text editor, set the FS_MONITOR_TIME parameter in ASAP_home/config/ASAP.cfg to a time interval in seconds. For example 120 seconds.

  3. Using sqlplus, connect to the Control server.

    sqlplus CTRL$/password

    Where password is the password for your Control server.

  4. Populate the Control server tbl_fs_threshold database table. The syntax is:

    INSERT INTO "TEST"."TBL_FS_THRESHOLD" (ASAP_SYS, FILE_SYSTEM, FULL_THRESHOLD, FULL_EVENT) VALUES ('envID', 'fs_path', 'fs_threshold', 'event_type');
COMMIT;

    Where the values for the tbl_fs_threshold columns are:

    • envid: The ASAP environment ID. The Control server only reads records from the table with values of asap_sys equal to the currently defined ASAP_SYS environment variable.

    • fs_path: The name of the file system.

    • fs_threshold: The threshold above which the system event is issued (in percentage 0-100).

    • event_type: Specifies the system event you want to trigger. Enter an event listed in the the Control server tbl_event_type table (see the ASAP Developer Guide).

  5. Populate the Control server tbl_component database table. The syntax is:

    INSERT INTO "TEST"."TBL_COMPONENT" (TERRITORY, SYSTEM, COMPONENT) VALUES ('envID', 'territory', 'path');
COMMIT;

    Where the values for the tbl_component columns are:

    • envid: The ASAP environment ID.

    • territory: Usually the same name as the ASAP environment ID.

    • fs_path: The path to the file system you want to monitor.

  6. Restart ASAP for the changes to take effect.


    Note:

    When a file system size goes beyond the threshold specifies in tbl_fs_threshold, ASAP triggers an event and logs a diagnostic message in the Control server diagnostic file.

Configuring Database Thresholds

To configure database thresholds:

  1. Open a UNIX terminal to your ASAP environment.

  2. Using a text editor, set the DB_MONITOR_TIME parameter in ASAP_home/config/ASAP.cfg to a time interval in seconds. For example 120 seconds.

  3. Using sqlplus, connect to the Control server.

    sqlplus CTRL$/password

    Where password is the password for your Control server.

  4. Populate the Control server tbl_db_threshold database table. The syntax is:

    INSERT INTO "TEST"."TBL_DB_THRESHOLD" (ASAP_SYS, DB_NAME, DATA_THRESHOLD,  DATA_EVENT) VALUES ('envID', 'DB_server', 'data_threshold', 'data_event_type')
COMMIT;

    Where the values for the tbl_db_threshold columns are:

    • envid: The ASAP environment ID. The Control server only reads records from the table with values of asap_sys equal to the currently defined ASAP_SYS environment variable.

    • DB_server: The name of the database server tablespace.

    • data_threshold: The data threshold above which the system event is issued (in percentage 0-100).

    • data_event_type: Specifies the system event you want to trigger as a result of crossing the data threshold. Enter an event listed in the Control server tbl_event_type table (see the ASAP Developer Guide).

  5. Populate the Control server tbl_component database table. The syntax is:

    INSERT INTO "TEST"."TBL_COMPONENT" (TERRITORY, SYSTEM, COMPONENT) VALUES ('envID', 'territory', 'tablespace');
COMMIT;

    Where the values for the tbl_component columns are:

    • envid: The ASAP environment ID.

    • territory: Usually the same name as the ASAP environment ID.

    • tablespace: The tablespace want to monitor.

  6. Restart ASAP for the changes to take effect.


    Note:

    When a tablespace size goes beyond the threshold specifies in tbl_db_threshold, ASAP triggers an event and logs a diagnostic message in the Control server diagnostic file.

Database Management and Tuning Recommendations

Refer to the Oracle Tuning Guide for more detailed information on tuning SQL statements.

Statspack

Statspack is an alternative to the UTLBSTAT/UTLESTAT tuning scripts. Statspack collects more information, storing performance statistics data permanently in the database for later use in analysis and reporting. The data collected can be analyzed using the report provided, which includes an instance health and load summary page, high resource SQL statements, as well as the traditional wait events and initialization parameters.

For more information, see Oracle 11g Database Performance Tuning Guide and Reference Guide.

Enabling Automated ASAP Database Administration Options

The Control application programming interface (API) provides a background thread that performs database administration tasks within ASAP. At a user-configured time of day, every application server process connects to its primary database and calls a user-defined function that performs the following tasks:

The ASAP configuration variable, DB_ADMIN_ON, enables and disables the database administration procedures. This feature is of value to users who may have many application servers defaulting to the same database and do not want them performing database administration procedures independently on it.

For more information on the Control API, refer to the ASAP Developer's Guide.

Purging the Database and File System

To keep disk usage at an acceptable level the ASAP administrator should regularly purge the diagnostic and log files generated by ASAP, as well as the work orders (WOs) received from the service order system. Database purging is controlled by database administrator stored procedures that are automatically executed daily.

The diagnostic files are located in the directory ASAP_home/DATA/logs (where ASAP_home is the ASAP installation directory). The flat files of service orders are usually located in the ASAP_home/DATA directory in a dated file.

Purging the Database

Database purging can be performed for the SRP, Service Activation Request Manager (SARM), and control databases, but is most commonly performed in the SARM as this is where the majority of the WO information is stored. The SRP and SARM databases maintain a history of all WOs received, while the ASAP control database maintains a history of alarms, events, performance, and process information.

The purging of the SRP and SARM is based on WO age. The purge age is usually determined by the amount of available disk space. Usually, only orders that have been completed for a certain amount of time are purged.

Database purging is controlled by database administration functions that automatically execute according to the following parameters that you can configure in ASAP.cfg:

  • DB_ADMIN_ON – Boolean flag. If set, it enables the database administration thread operation in the application server. This can be disabled in particular servers in situations where multiple servers share the same application database (for example, multiple NEPs) and then only one server is required to perform this database administration. Default = 0.

  • DB_ADMIN_TIME – The number of minutes after midnight when the database administration tasks are to be performed. This is usually performed at a time of low system activity. Default = 300.

  • DB_ADMIN_PROC – The function the database administration thread calls at a specified time in the day. This function can be configured to perform multiple tasks, including archiving and purging dynamic data. Default = SSP_db_admin.

  • DB_ADMIN_PROC_PARAM – The integer parameter passed to the database administration function. For example, this can specify a purge interval for a particular database. Default = 100.

  • GATHER_STATS –Enables the gathering of statistics for tables and indexes. Default = 0.

  • GATHER_STATS_PROC – Indicates the procedure to use to gather statistics on the SARM database. Default = SSP_gather_asap_stat.

  • DB_PCT_ANALYZE – Percentage of table to analyze when gathering stats. Default = 20.

  • DB_PCT_ANALYZE_IDX – Percentage of index to analyze when gathering stats. Default = 40.

  • GATHER_DEGREE – Degree of parallelism to use when gathering stats. Default = 1.


    Note:

    You can configure every ASAP server to run similar administration functions, but the SARM is the only server where a default function (SSP_db_admin) has been provided. To perform administration on databases other than the SARM database, you must copy the above configuration parameters to the appropriate section of the ASAP.cfg configuration file, and load the SSP_db_admin to those servers.

Table 6-1 provides recommended data purge frequency and methods.

Table 6-1 Database Purge Frequency and Methods

Data Object Being Purged Selection Criteria Frequency of Purge Method of Purge

ASAP log files

All log files older than X days

Daily

Cron script. A sample cron script is located on "Sample Cron Script for Clearing Alarms, Events, and Process Information".

ASAP control database

All dynamic tables older than X days

Daily

Function

Sample functions are described in "Sample Database Purge Script".

SRP database

All WOs older than X days

Daily

Function

Sample functions are described in "Sample Database Purge Script".

SARM database

All WOs older than X days

Daily

Function

Sample functions are described in "Sample Database Purge Script".

Sample Database Purge Script

The following sample script can be used to delete successfully provisioned WOs from tbl_work_ord that are older than a specified number of days. In addition, this script calls the SSP_orphan_purge function, which deletes all orphaned records. When you delete WOs, information related to these WO may remain in other tables. The SSP_orphan_purge function purges information that is related to WOs that have been removed from the database. The affected tables include:

  • tbl_asap_stats

  • tbl_info_parm

  • tbl_srq

  • tbl_srq_csdl

  • tbl_srq_log

  • tbl_asdl_log

  • tbl_srq_parm

  • tbl_srq_asdl_parm

  • tbl_wo_event_queue

  • tbl_wo_audit

  • tbl_usr_wo_prop

  • tbl_aux_wo_prop


    Note:

    The SSP_orphan_purge function is time-consuming and requires considerable system resources. Therefore, it should not run during peak hours.    		 

create proc SSP_db_admin @days int
as
begin
    declare @cutoff_dts datetime
    if (@days is not null and @days > 0)
    begin
        select @cutoff_dts = dateadd(day, -@days, getdate())
        delete tbl_wrk_ord
        where comp_dts < @cutoff_dts and wo_stat = 104
    end
    exec SSP_orphan_purge
end

You can customize this script in a variety of ways (for example, you can delete WOs that have successfully completed (104) or orders that have completed and failed due to timeout). When ASAP triggers the DB_admin procedure, ASAP also performs other optimization, such as recompiling stored procedures for optimal database access, and so forth.

In the following example, after the function has deleted each set of 1000 orders, the function performs a commit and the rollback segment is flushed. This prevents the Oracle rollback segment from being exceeded. This example also employs the SSP_orphan_purge function described for the previous example.

CREATE OR REPLACE FUNCTION SSP_db_admin(
days INTEGER )
RETURN INTEGER
AS
StoO_selcnt INTEGER;
StoO_error INTEGER;
StoO_rowcnt INTEGER;
StoO_errmsg VARCHAR2(255);
cutoff_dts DATE;
retval integer;
BEGIN
IF (SSP_db_admin.days IS NOT NULL AND SSP_db_admin.days > 0) THEN

BEGIN

SSP_db_admin.cutoff_dts :=  SYSDATE-SSP_db_admin.days;
BEGIN

StoO_error   := 0;
StoO_rowcnt  := 0;
-- Created a loop to split the deletion of orders in portions of 1000
-- to remove the risk of reaching the rollback segment limit
-- NOTE! The orphans are deleted by a trigger defined on the
-- tbl_wrk_ord table and not by the SSP_orphan_purge function call
-- below
LOOP

  DELETE  FROM tbl_wrk_ord

WHERE comp_dts < SSP_db_admin.cutoff_dts
AND wo_stat = 104
AND rownum <= 1000;
 EXIT WHEN SQL%ROWCOUNT = 0;
  COMMIT;
END LOOP;
StoO_rowcnt := SQL%ROWCOUNT;
COMMIT WORK;
-- EXCEPTION
-- WHEN OTHERS THEN
-- StoO_error  := SQLCODE;
END;

END;

END IF;
BEGIN

retval := SSP_orphan_purge;
EXCEPTION
WHEN OTHERS THEN

StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;

END;

RETURN 0;
END;
/

Sample Cron Script for Clearing Alarms, Events, and Process Information

The following is a sample cron script that clears alarm entries, event logs and process information.

###########################################################################
#
# ASAP database and log housekeeping script
#
# Call this script with one parameter, specifying the number of days. 
# Alarm entries, event logs and process info
# logs are cleared. Log files and directories are also cleared.
#
# The script will exit if the number of days is less than 5
#
############################################################################

. $HOME/.profile > /dev/null

PROG_NAME=`basename "$0"`

if [ "$1" == "" ]
then
    echo "Usage $PROG_NAME <admin_days>"
    exit
fi

let ADMIN_DAYS=$1+0
if [ "$?" != "0" ]
then
    echo "Number of days must be numeric"
    exit
fi

if [ $ADMIN_DAYS -lt 5 ]
then
    echo "Cannot run with less than 5 days lead-time"
    exit
fi


############################################################################
# Clear CTRL entries
############################################################################
echo "Deleting alarm logs, event logs and process info from CTRL more than $ADMIN_DAYS days old"

sqlplus -s $CTRL_USER/$CTRL_USER << HERE

var admin_days number;
exec :admin_days := $ADMIN_DAYS;

delete from TBL_ALARM_LOG where start_dts < sysdate - :admin_days;
commit;
delete from TBL_EVENT_LOG where event_dts < sysdate - :admin_days;
commit;
delete from TBL_PROCESS_INFO where info_dts < sysdate - :admin_days;
commit;

HERE

############################################################################
# Truncate ASAP.Console and ControlProgramOutput so that old entries can be
# cleared with the diagnostic files
############################################################################
cd $LOGDIR
FILE1=ASAP.Console
FILE2=ControlProgramOutput
CUR_DTS=`date +'%Y_%b_%d-%T'`

if [ -f $FILE1 ]; then
    NEWFILE=$FILE1.diag.$CUR_DTS
    echo Copying current $FILE1 to $NEWFILE
    cp $FILE1 $NEWFILE
    cp /dev/null $FILE1
else
    echo $LOGDIR/$FILE1 not found
fi

if [ -f $FILE2 ]; then
    NEWFILE=$FILE2.diag.$CUR_DTS
    echo Copying current $FILE2 to $NEWFILE
    cp $FILE2 $NEWFILE
    cp /dev/null $FILE2
else
    echo $LOGDIR/$FILE2 not found
fi

############################################################################
# Clear log files
############################################################################
echo "Clearing old ASAP log files..."

find . -type f -name '*diag*' -atime +$ADMIN_DAYS -exec echo Removing file {} \; -exec rm -f {} \;

############################################################################
# Clear log directories not accessed within admin days
############################################################################
echo "Clearing old ASAP log directories..."

find . -type d -name '2*' -atime +$ADMIN_DAYS -exec echo Removing directory {} \; -exec rm -rf {} \;

cd -

echo "$PROG_NAME finished"

Purging Test Systems

The cleandata script is designed to clean data from the test system during system testing. It is intended for use in development environments.


Note:

You must stop the Oracle WebLogic Server before running the cleandata script. Otherwise, a JDBCStoreException is thrown, and a WebLogic Server error is logged.

Usage

cleandata [-d]

The -d option instructs the script to retrieve password information from the credential store factory (CSF) wallet, located in ASAP_Home/install/cwallet.sso file, where ASAP_Home is the directory in which ASAP is installed. The cwallet.sso file is typically used only in development environments.

This script does the following:

  • Deletes events, process information, and alarms from the Control (CTRL) database (specifically, truncates tbl_event_log, tbl_process_info, tbl_alarm_log, tbl_unid)

  • deletes WOs from the ASAP database and executes an orphan purge (specifically, truncates tbl_wrk_ord, truncates tbl_ne_monitor, tbl_ne_event, tbl_unid, tbl_label_value, tbl_srt_correlation, tbl_srt_ctx, temp_wrk_ord)

    Note: The tbl_ne_monitor table has been deprecated from ASAP 4.6.x onwards.

  • Deletes WOs from the SRP database (tbl_wrk_ord)

  • Deletes performance data from Admin database (truncates tbl_perf_order, tbl_perf_csdl, tbl_perf_asdl, tbl_perf_ne, tbl_perf_ne_asdl, tbl_aims_rpc, tbl_aims_rpc_param, tbl_aims_rpc_dest, tbl_aims_audit_log)

  • Deletes Java Message Service (JMS) messages from the Admin database

  • Deletes diagnostic and log files


    Note:

    Restart the WebLogic Server for the domain after running the cleandata script.

  Previous
Previous 		 Next
Next
  Go To Table Of Contents
Contents