6 Database Storage and Reporting

This chapter contains information on the installation and setting up of Database and Reporting software for Oracle Communications Offline Mediation Controller Nortel DMS SMDR 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.

Installing and Configuring

To install and configure the database components, see Offline Mediation Controller 6.0 Installation Guide.

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

Loading the Offline Mediation Controller Schema

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

To load the schema:

  1. As the oracle user, run the Oracle Enterprise Manager by typing oemapp worksheet and pressing Enter.

  2. Select connect directly to database.

  3. For Username, type system.

  4. For Password, type Oracle.

  5. For Service, type NMCDRS.

  6. For Connect, type normal.

  7. Click OK.

    You are now connected to the NMCDRS schema.

  8. Navigate to the directory where the SMDR jar file was un-jarred.

    For example: /opt/test/dbstorage/Oracle11g

  9. As the oracle SYSTEM user, load the following tablespace: DDL_create_tblspc_SMDRS_MAIN

    This is a default tablespace, and does not store daily CDRs.

  10. 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

  11. Set the SIZE parameter to a minimum of 10M (ten megabytes).

  12. As the oracle SYSTEM user, load DDL_create_usr_NMUSER1.sql.

  13. Disconnect as the SYSTEM user and connect as NMUSER1. The default password is NMUSER1.

  14. Ensure you are in the directory where the SMDR jar file was un-jarred.

    For example: /opt/test/dbstorage/Oracle11g

  15. Load DDL_create_tbl_MSLV_SMDR_STORAGE.sql.

  16. Go to: <directory where you un-jarred the SMDR jar file>/dbstorage/Oracle11g/RepoMan.

  17. Load DDL_create_tbl_SMDR_REPOMAN_CONFIGURATION.sql.

  18. Load PLSQL_SMDR_PKG.sql.

  19. Load PLSQL_SMDR_LOGTOFILE.sql.

  20. Load JAVA_UTIL.sql.

  21. Load PLSQL_RUN_CMD.sql.

  22. Load PLSQL_RC.sql.

  23. Load PLSQL_SMDR_PARFILEWRITER.sql.

  24. Load PLSQL_BUILD_SMDR_EXCHANGE.sql.

  25. Load PLSQL_DROP_SMDR_PARTITIONS.sql.

  26. Load PLSQL_BUILD_SMDR_PARTITIONS.sql.

  27. Load PLSQL_MSLV_START_SMDR_REPOMAN.sql.

  28. Load PLSQL_MSLV_STOP_SMDR_REPOMAN.sql.

All required Oracle objects are now loaded into Oracle database.