9 Database Storage and Reporting

This chapter contains information on the Database Storage and Reporting functionality for Offline Mediation Controller Nortel GSM/UMTS AMA cartridge pack.

The Database Storage and Reporting functionality allows you to configure DMS-MSC node chains that can process and distribute data to both a billing system and an Oracle 11g database.

Database Storage Architecture

The Database Storage solution lets you configure DMS-MSC node chains that process and distribute data to both a billing system and an Oracle 11g database.

Figure 9-1 shows the recommended configuration of the Database Storage node chain. The DMS-MSC CC collects data from DMS-MSC network devices and passes the data to both the DMS-MSC AMA DIRP DC and the JDBC DC. The DMS-MSC AMA DIRP DC sends the data to a billing system. The JDBC DC sends the data to an Oracle 11g database.

Figure 9-1 Database Storage Architecture

Description of "Figure 9-1 Database Storage Architecture"

Repository Manager

The Repository Manager is a Offline Mediation Controller tool that runs on an Oracle server and can import and export CDRs from an Oracle 11g Enterprise database. You can configure the Repository Manager to export CDRs at a specific time and store them in an accessible way, such as on Digital Audio Tape (DAT) or Digital Linear Tape (DLT). The Repository Manager can import the archived CDRs at a later date, if necessary. You can also configure the Repository Manager to delete CDRs from the database at the same time every day, if you do not wish to archive them.

Installing and Configuring Oracle Database Components

The Database Storage tasks are as follows:

Creating an Oracle user and dba group
Installing Oracle
Verifying the Oracle installation
Loading the Offline Mediation Controller schema
Configuring Oracle
Configuring UNIX executables
Configuring and using the Repository Manager
Installing the Oracle Client
Modifying tnsnames.ora
Installing the Oracle JDBC driver
Configuring Oracle for automatic restart
Verifying the Oracle automatic restart

Oracle Database

The Oracle database stores call records it receives from the JDBC DC. The database stores each CDR it receives as a separate record in a table. You can access and modify the CDRs with standard SQL commands.

Requirements

Offline Mediation Controller supports Oracle 11g Enterprise Edition and recommends installing the database on its own Oracle Solaris server.

For more information, see the following Oracle 11g documentation:

Oracle 11g Installation Guide
Oracle 11g Administrator's Reference
Oracle 11g Real Application Clusters Guard I Configuration Guide

Oracle Database Installation Summary

To install Oracle database, you need to:

Create an Oracle user and dba group. See Oracle 11g documentation.

Run the Oracle database installation tool, select components and install. See Oracle 11g documentation.
Verify the Oracle database installation. See Oracle 11g documentation.
Load the Offline Mediation Controller schema files.
Install the Oracle Client.
Download and install the Oracle JDBC driver.

See the Oracle database documentation for more information.

Upgrading the Oracle Database Components

The Oracle database components require upgrading if the Database Storage and Reporting functionality is configured.

Upgrading If AMA 1.4.x Cartridge Packs Were Installed

Upgrade the database components if you were running an AMA 1.4.x cartridge pack before you installed the new AMA cartridge pack:

As the Oracle user, run the Oracle Enterprise Manager by typing oemapp worksheet and pressing Enter.
Select connect directly to database.
For Username, type NMUSER1.
For Password, type NMUSER1.
For Service, type NMCDRS.
For Connect, type normal.
Click OK.

You are now connected to the NMCDRS schema.
Stop the Repository Manager using the command:
```
EXEC NMUSER1.MSLV_STOP_REPOMAN
```
Navigate to the OMC_Home/db_scripts/dbstorage/Oracle9i/Upgrade/ directory where you un-jarred the AMA jar file.
If you have a GSM 1.0.4 cartridge pack installed, go to next step; otherwise, go to Step 12.
Load Upgrade_From_AMA14_GSMR104.sql.
Load Upgrade_From_AMA14.sql.
Navigate to the OMC_Home/db_scripts/dbstorage/Oracle9i/Repoman/ directory where you un-jarred the AMA jar file.
Load PLSQL_NMCDRS_PKG.sql.
Load PLSQL_LOGTOFILE.sql.
Load JAVA_UTIL.sql.
Load PLSQL_RUN_CMD.sql.
Load PLSQL_RC.sql.
Load PLSQL_PARFILEWRITER.sql.
Load PLSQL_BUILD_EXCHANGE.sql.
Load PLSQL_DROP_PARTITIONS.sql.
Load PLSQL_BUILD_PARTITIONS.sql.
Load PLSQL_MSLV_START_REPOMAN.sql.
Load PLSQL_MSLV_STOP_REPOMAN.sql.
The repoman_configuration table has three new columns. Change the values of the fields in these columns to the appropriate values. These columns are as follows:
1. TABLENAME - 1 to 24 bytes. This is the name of the base table (for example 'MSLV_ENHCDR_STORAGE').
2. NO_DATAFILES_PER_TS -1 to n. This is the number of data files that the tablespace will deploy and should always be set to 1 (unless you desire more as in the case of CHT).
3. TBLSPC_NAME - 1 to 10 bytes. This is the base portion of the tablespace name (for example 'MSLV_'). The rest will be filled in by appending a datestamp to the end (for example 'MSLV_20061219')
  
  Note:
  TBLSPC_SIZE has been renamed to DATAFILE_SIZE; as a result, the values for DATAFILE_SIZE have to be re-entered.
Start the Repository Manager using the following command.
```
EXEC NMUSER1.MSLV_START_REPOMAN(N);
```
where N is the config_num configured in REPOMAN_CONFIGURATION

Upgrading If AMA 4.0.0 to 4.0.7 Cartridge Packs Were Installed

Upgrade the database components if you were running an AMA 4.0.0 to AMA 4.0.7 cartridge pack before you installed the new AMA cartridge pack.

As the Oracle user, run the Oracle Enterprise Manager by typing oemapp worksheet and pressing Enter.
Select connect directly to database.
For Username, type NMUSER1.
For Password, type NMUSER1.
For Service, type NMCDRS.
For Connect, type normal.
Click OK.

You are now connected to the NMCDRS schema.
Stop the Repository Manager using the command:
```
EXEC NMUSER1.MSLV_STOP_REPOMAN
```
Navigate to the OMC_Home/db_scripts/dbstorage/Oracle9i/Upgrade/ directory where you un-jarred the AMA jar file.
Load Upgrade_From_AMA40.sql.
Navigate to the OMC_Home/db_scripts/dbstorage/Oracle9i/Repoman/ directory where you un-jarred the AMA jar file.
Load PLSQL_NMCDRS_PKG.sql.
Load PLSQL_LOGTOFILE.sql.
Load JAVA_UTIL.sql.
Load PLSQL_RUN_CMD.sql.
Load PLSQL_RC.sql.
Load PLSQL_PARFILEWRITER.sql.
Load PLSQL_BUILD_EXCHANGE.sql.
Load PLSQL_DROP_PARTITIONS.sql.
Load PLSQL_BUILD_PARTITIONS.sql.
Load PLSQL_MSLV_START_REPOMAN.sql.
Load PLSQL_MSLV_STOP_REPOMAN.sql.
The repoman_configuration table has three new columns. Change the values of the fields in these columns to the appropriate values. These columns are as follows:
1. TABLENAME - 1 to 24 bytes. This is the name of the base table (for example 'MSLV_ENHCDR_STORAGE').
2. NO_DATAFILES_PER_TS -1 to n. This is the number of data files that the tablespace will deploy and should always be set to 1 (unless you desire more as in the case of CHT).
3. TBLSPC_NAME - 1 to 10 bytes. This is the base portion of the tablespace name (for example 'MSLV_'). The rest will be filled in by appending a datestamp to the end (for example 'MSLV_20061219')
  
  Note:
  TBLSPC_SIZE has been renamed to DATAFILE_SIZE; as a result, the values for DATAFILE_SIZE have to be re-entered.
Start the Repository Manager using the following command.
```
EXEC NMUSER1.MSLV_START_REPOMAN(N);
```
where N is the config_num configured in REPOMAN_CONFIGURATION

Upgrade the Database Portion of AMA 3.5. to 3.5.5 For the Parameter (PAR) File Enhancement

Upgrading the database will allow timestamps to be added to the PAR script file name.

Stop the Repository Manager using the command:
```
EXEC NMUSER1.MSLV_STOP_REPOMAN
```
Navigate to the <CARTRIDGE PACK>/dbstorage/Oracle9i/Upgrade/ directory where you un-jarred the AMA jar file.
Navigate to the <CARTRIDGE PACK>/dbstorage/Oracle9i/Repoman/ directory where you un-jarred the AMA jar file.
Load PLSQL_PARFILEWRITER.sql.
Load PLSQL_DROP_PARTITIONS.sql.
Start the Repository Manager using the below command after verifying configuration values in REPOMAN_CONFIGURATION table.
```
EXEC NMUSER1.MSLV_START_REPOMAN(N);
```
where N is the config_num configured in REPOMAN_CONFIGURATION table

In order to manage disk space usage, you may want to delete the older PAR files at regular intervals. The PAR files are kept in the /opt/mslvdblogs directory. Ensure that the current PAR file is not deleted.

Loading the Offline Mediation Controller Schema

Use the following procedure to load the Offline Mediation Controller schema.

To load the schema:

As the Oracle user, run the Oracle Enterprise Manager by typing oemapp worksheet and pressing Enter.
Select connect directly to database.
For Username, type system.
For Password, type oracle.
For Service, type NMCDRS.
For Connect, type normal.
Click OK.

You are now connected to the NMCDRS schema.
Navigate to the OMC_Home/db_scripts/dbstorage/Oracle9i/ directory where you un-jarred the AMA jar file.
As the oracle SYSTEM user, load the following tablespace:

DDL_create_tblspc_ENHNMCDRS_MAIN.sql

This is a default tablespace, and does not store daily CDRs.
After loading this script, modify the DATAFILE to match the directory structure on the machine where you installed Oracle.

For example: Oracle_Home/oradata/<dbf filename>

Set the SIZE parameter to a minimum of 10M - ten megabytes.
As the oracle SYSTEM user, load:

DDL_create_usr_NMUSER1.sql

If you run the schema file NMUSER1.sql from the AMA jar file, then you do not need to run this sql again.
Disconnect as the SYSTEM user and connect as NMUSER1. The default password is NMUSER1.
Navigate to the OMC_Home/db_scripts/ dbstorage/Oracle9i/ directory where you un-jarred the AMA jar file.
Load DDL_create_tbl_MSLV_ENHCDR_STORAGE.sql.
Navigate to the OMC_Home/db_scripts/ dbstorage/Oracle9i/ directory where you un-jarred the AMA jar file and then to /RepoMan.
Load DDL_create_tbl_REPOMAN_CONFIGURATION.sql.
Load PLSQL_NMCDRS_PKG.sql.
Load PLSQL_LOGTOFILE.sql.
Load JAVA_UTIL.sql.
Load PLSQL_RUN_CMD.sql.
Load PLSQL_RC.sql.
Load PLSQL_PARFILEWRITER.sql.
Load PLSQL_BUILD_EXCHANGE.sql.
Load PLSQL_DROP_PARTITIONS.sql.
Load PLSQL_BUILD_PARTITIONS.sql.
Load PLSQL_MSLV_START_REPOMAN.sql.
Load PLSQL_MSLV_STOP_REPOMAN.sql.

All required Oracle objects are now loaded into Oracle database.

Changing the Default Oracle Username And Password

To change the default Oracle username and password, you must update the PLSQL_PARFILEWRITER.sql file. When you un-jar the file from the cartridge jar file, the script is located at: /db_scripts/dbstorage/Oracle9i/Repoman.

To change the user name and password in the script:

Open the file PLSQL_PARFILEWRITER.sql for editing.
Scroll down to line 44, which reads v_LineUser:='USERID=''system/oracle AS SYSDBA''';
The line currently displays system as the username and oracle as the password.
Replace the user name and password with new information.
Save the file and close the editing window.

Configuring Oracle Database

You must configure Oracle database to run the Repository Manager.

To configure Oracle database:

From the UNIX command line, create a new directory that the oracle user can read and write to.

For example, run the following commands in the order shown and press Enter:
```
mkdir /opt/mslvdblogs
chmod 777  /opt/mslvdblogs
```
This directory stores the Repository Manager log files and the parameter file for the archive process.
You must edit an Oracle database initialization parameter in order for the Repository Manager to function correctly.

Start the Oracle DBA Studio application by logging on to your Oracle server as the oracle user and run the following command:
```
oemapp dbastudio &
TRANSPORT_TABLESPACE=Y
```
Log on with the user ID sys and the password dba, then select SYSDBA from the drop-down list.
In the Oracle DBA Studio window, select the NMCDRS database, then select Instance and then Configuration.
In the Configuration window, go to the General tab and click All Initialization Parameters.
In the Initialization Parameters window, go to the util_file_dir entry and set it to /opt/mslvdblogs.

This is the directory where the Repository Manager stores its log files. Ensure the oracle user can access this directory.
Click Apply to save the changes and then exit the application.
Restart the database when the DBA Studio prompts you.
Change your connection back to SYSTEM.

Configuring UNIX Executable

To configure the UNIX executable for the Database Storage system:

Determine the path of the oracle user for the UNIX cp program (file copy utility) by logging on to a UNIX session as oracle and run the following command:
```
which cp
```
The system responds with a path such as /usr/bin/cp.
Determine the path of the oracle user for the UNIX exp program (export utility) by logging on to a UNIX session as oracle and run the following command:
```
which exp
```
The system responds with a path such as Oracle_Home/bin/exp.
Locate DDL_Java_Permissions.sql and open the file in a text editor program. Then edit the cp and exp information to match the directory path for your system and save the file.
Log on to Oracle SQLPlus as the SYSTEM user.
Load and run the updated DDL_Java_Permissions.sql file.

Configuring and Using the Repository Manager

These sections explain how to configure the Repository Manager.

Adding Entries to the Configuration Table

The Repository Manager requires you to provide some configuration data before it can run. You must insert the data into the Repository Manager NMUSER1.REPOMAN_ CONFIGURATION table.

You can use the table data editor in the Oracle DBA Studio tool to edit the Repository Manager configuration table. Each row in the table represents a configuration and only one configuration is used at a time. You can keep many configurations in the table for testing purposes.

Parameters

The following are the parameters you must configure in the NMUSER1.REPOMAN_ CONFIGURATION table.

CONFIG_NUM

Set to an integer value such as 1, 2, 3, 4, 5 and so on.

DESCRIPTION

Enter a text description of the configuration up to 80 characters.

RUN_MODE

Set to DELETE or ARCHIVE.

NEXT_PRTN_INTERVAL

This setting controls when the Repository Manager creates the next partition, which is usually once a day.

Table 9-1 lists some examples of the settings and their values.

NEXT_PRTN_INTERVAL Parameter Values

Table 9-1 Example Settings

Functional setting	NEXT_PRTN_INTERVAL parameter value
Everyday at midnight-12 am	TRUNC(SYSDATE + 1)
Everyday at 8 pm	TRUNC(SYSDATE + 1) + 8/24
Every Tuesday at noon-12 pm	NEXT_DAY(TRUNC(SYSDATE), "TUESDAY") + 12/24
First day of the month at midnight	TRUNC(LAST_DAY(SYSDATE) + 1)
Last day of the quarter at 11 pm	TRUNC(ADD_MONTHS(SYSDATE + 2/24,3), 'Q') - 1/24
Every Monday, Wednesday and Friday at 9 am	TRUNC(LEAST(NEXT_DAY(SYSDATE, "MONDAY"), NEXT_DAY(SYSDATE, "WEDNESDAY"), NEXT_DAY(SYSDATE, "FRIDAY") ) ) + 9/24

For testing purposes, you can configure the Repository Manager to automatically generate new partitions every minute or every five minutes. For every minute, use the value SYSDATE + 1/1440. For every five minutes, use the value SYSDATE + 5/1440.

DAYS_TO_KEEP

Set to the number of days to keep the CDRs in the database.

TBLSPC_DIRECTORY

This is the directory where Oracle database stores the tablespace data files. The oracle user must have full permissions to this directory. Set this value to the full directory path. For example: /opt/oradata/mydatadisk/

TBLSPC_SIZE

This is the size of each tablespace the Repository Manager generates. As a minimum, set the value to the maximum amount of data stored per day. Use an integer followed by M-megabytes. For example, for a 125 megabyte partition per day, use 125M.

START_PRTN

This is the date the Repository Manager generates the first partition. For example, to set the first partition on March 14, 2006, set the value to:14-Mar-2006 12:00:00 AM

Note:

The time value is not used.

Before you insert data into the database, ensure a partition exists with a date that is equal to or ahead of the date on which you are inserting data. For example, if you insert data on October 22, 2006 and the existing partitions have dates previous to October 22, the CDRs are not inserted.

START_AT

This determines when the Repository Manager starts running. Set this value to a time beyond the current time of the UNIX system. To avoid setting times in the past, change this value to a time slightly ahead of the current time whenever you run the Repository Manager.

For example, if you set the value to SYSDATE + 60/1440, you have one hour to start the Repository Manager.

ARCHIVE_OUT_DIRECTORY

This is the directory where the Repository Manager stores the archive partitions when it is running in archive mode. Ensure the oracle user has full permissions to this directory. Oracle recommends that you periodically send the archive files to a storage tape, such as DLT or DAT, and then delete the archive files from the database.

ORACLE_EXP_PATH and UNIX_CP_PATH

Set these parameters to the directory paths for the Oracle export utility and the UNIX file copy utility, as described in Configuring UNIX Executable. Enter the directory path but not the executable name. For example, for a UNIX file copy utility located in /usr/bin/, enter only /usr/bin/.

Starting the Repository Manager

From Oracle SQLPlus, log on to the database as nmuser1. Start the Repository Manager with the execute command and the number of the configuration you wish to use. For example, to use configuration 1, run the following command:

exec nmuser1.MSLV_START_REPOMAN(1)

Stopping the Repository Manager

From Oracle SQLPlus, log on to the database as nmuser1. Stop the Repository Manager with the following command:

exec nmuser1.MSLV_STOP_REPOMAN

Importing Records with the Repository Manager

You can import archive partitions into the Oracle database. If you import an archive partition into the database that you exported from, ensure the tablespace you are importing does not already exist in the database. This situation can occur if the partition was not exported correctly.

To import an archive partition, do the following:

Copy the archive file MSLV_date_TS.dbf to the ARCHIVE_OUT_DIRECTORY as specified in the REPOMAN_CONFIGURATION table.
Create an import parameter file and ensure the oracle user has read access to this file.

Here is an example of an import parameter file:
```
USERID='sys/TBD AS SYSDBA'
TABLESPACES=(MSLV_20060123)
DATAFILES=(/opt/Oracle902/oradata/NMCDRS/MSLV_20060127_TS.dbf)
FILE='/opt/mslvout/MSLV_DBSR_ARCHIVE_20060127.dmp'
```
where:
- In the USERID field, you can specify any user with SYSDBA privileges. By default, the oracle SYS user has SYSDBA privileges.
- In the DATAFILES field, specify the path to the .dbf file and the .dmp file.
Save the file. For example, you can save the file as:
```
/my_importfiles/import_date.par
JDBCUrl
```
Import the file using the Oracle import utility by running the following command:
```
imp PARFILE = /my_importfiles/import_date.par
```
The import utility loads the file. The import appears as a table called XCHANGE_ date.

Deleting Records with the Repository Manager

The Repository Manager uses a separate Oracle partition in the MSLV_CDR_ STORAGE table to store all the CDRs for each day. The Repository Manager generates new partitions daily and deletes old partitions that have existed longer than the value set in the DAYS_TO_KEEP parameter. If you wish to archive the old partitions instead of deleting them, run the Repository Manager in archive mode.

To run the Repository Manager in delete mode, set the RUN_MODE parameter to DELETE in the REPOMAN_CONFIGURATION table.

Archiving Records with the Repository Manager

When the Repository Manager runs in archive mode, it sends old partitions to a storage disk before deleting them from the database.

To run the Repository Manager in archive mode, set the RUN_MODE parameter to ARCHIVE in the REPOMAN_CONFIGURATION table.

The Repository Manager creates a pair of files for each partition, and stores them in the ARCHIVE_OUT_DIRECTORY as specified in the REPOMAN_CONFIGURATION table. For example, for an archive partition from January 21, 2006, the Manager generates two files:

MSLV_20060121_TS.dbf
MSLV_DBSR_ARCHIVE_20060121.dmp

The .dbf file is an Oracle data file and the .dmp file is a metadata file that Oracle uses when importing archive partitions into the database. You can use the Oracle Import program, imp, to load archive partitions into the database. These files are only compatible with the Oracle Transportable Tablespaces export and import mode.

Installing the Oracle Client

You must install the Oracle 11g Client to query the database.

See the Oracle Installation Guide for instructions on installing the Oracle Client. Once you have installed the Oracle Client, you can install the Oracle JDBC driver.

Installing the Oracle JDBC Driver

To use the JDBC DC, you must download and install the Oracle JDBC driver.

To install the Oracle JDBC driver:

Download the ojdbc6.jar file from the following location:

http://www.oracle.com/technetwork/index.html
On the Offline Mediation Controller server where the Node Manager is running, place the file in the OMC_Home/3rdparty_jars directory.

To configure the JDBC-DC node for RAC environment, use the following URL when you define a new NPL for JDBC-DC node:

JDBCDriver "oracle.jdbc.OracleDriver";
 "jdbc:oracle:thin:(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=
Host1) (PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=Host2)
 (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service)))";

You can now start or restart the Offline Mediation Controller Administration Server, Node Manager, and JDBC DC.

Configuring Oracle Database for Automatic Restart

You can set up Oracle database to automatically restart after the server reboots.

To configure Oracle database to automatically restart:

As root, copy the OMC_Home/db_scripts/dbstorage/Oracle9i/dbora.sh file and paste the dbora.sh file on the Oracle database server, in the /etc/init.d directory.
Ensure the ORA_Home is set to the same value as the ORACLE_Home setting.
Enable NMCDRS in the oratab file by changing the setting of the /var/opt/oracle/oratab directory from N to Y.
As root, in the /etc directory, edit the services file.

Use the chmod command to modify the file permissions. Add the following line for the Oracle listener:
```
listener   1521/tcp    #oracle listener
ln -s /etc/init.d/dbora /etc/rc2.d/S99dbora
```
To create symbolic links, run the following commands as root:
```
ln -s /etc/init.d/dbora /etc/rc0.d/K10dbora
```
Create a PFILE that Oracle reads upon start-up that points to a valid SPFILE. Navigate to the Oracle_Home/dbs directory. There is an SPFILE called spfileNMCDRS.ora. You must create a new file in this directory called initNMCDRS.ora.

In the new initNMCDRS.ora file, add the following line:

SPFILE = 'Oracle_Home/dbs/spfileNMCDRS.ora'
ORAENV_ASK=NO ; export ORAENV_ASK

To set the Oracle profile, as the Oracle user, navigate to the Oracle_Home directory.
Create a file called .profile and add:
```
PATH=$HOME/bin:$PATH
ORACLE_SID=NMCDRS ; export ORACLE_SID
. $HOME/bin/oraenv
```
This allows the dbora script in /etc/init.d to access the correct environment to start the Oracle listener.

Verifying the Oracle Database Automatic Restart

To verify the Oracle database automatic restart:

To see the processes that are running, run the following command:
```
ps -ef | grep ora
```
As the root user, go to the /etc/init.d directory.
Run the following command to stop the listener and oracle processes:
```
./dbora stop
```
The listener and oracle processes stop.
Run the following command to start the listener and oracle processes:
```
./dbora start
```
The listener and oracle processes start.