Skip Headers
Oracle® Communications Billing and Revenue Management Upgrade Guide
Release 7.5

Go to Documentation Home
Go to Table of Contents
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

27 Upgrading from Revenue Assurance Manager Release 2.0 to Release 3.0

This chapter contains procedures for upgrading your Portal™ Revenue Assurance Manager system directly from Release 2.0 to Release 3.0. It covers Oracle on both HP-UX and Solaris platforms.


Before performing the tasks in this document, verify that you have Revenue Assurance Manager 2.0 installed.

For information on installing Revenue Assurance Manager, see ”Installing Revenue Assurance Manager” in BRM Collecting Revenue Assurance Data.


You need to upgrade Revenue Assurance Manager Release 2.0 to Release 3.0 only if you are upgrading from Infranet Release 6.5 to Portal 7.3.

Preparing Your Environment for the Upgrade

Before upgrading to Revenue Assurance Manager 3.0, prepare your database.

Upgrading the Oracle Software and Database

You can use Revenue Assurance Manager 3.0 only with Oracle 9i or 10g.


If you are using an earlier version of Oracle, you must upgrade it before you install Revenue Assurance Manager 3.0. For information on upgrading Oracle, see your Oracle documentation.

Upgrading the Portal Database for Revenue Assurance Manager 3.0

This section describes how to upgrade the Portal database to support Revenue Assurance Manager 3.0.

  1. Backing Up Revenue Assurance Manager 2.0 Files

  2. Loading Release 2.0 Batch Rating Data

  3. Shutting Down Portal

  4. Backing Up Your Portal Database

  5. Installing the Third-Party Software

  6. Installing Revenue Assurance Manager Server 3.0

  7. Installing the Upgrade Scripts

  8. Configuring the Upgrade Parameters

  9. Creating Required Database Objects and the Upgrade Log Directory

  10. Performing Pre-upgrade Sanity Checks

  11. Running the Database Upgrade Scripts

  12. Running pin_setup to Configure Revenue Assurance Manager

  13. Restoring Service Authentication

  14. Dropping Obsolete Database Objects from the Database (Optional)

Backing Up Revenue Assurance Manager 2.0 Files

Before removing the old Revenue Assurance Manager Release 2.0 software, back up your 2.0 files.


In particular, ensure that you back up customized source code, pin.conf, and pin_setup.values files.

Loading Release 2.0 Batch Rating Data

Pipeline Rating Engine does not directly load revenue assurance data into the database, but rather creates data files that are loaded by using Universal Event (UE) Loader. Typically, this is triggered automatically by the Batch Controller. You must complete loading all the Release 2.0 data files before upgrading to Release 3.0. The UE Loader templates delivered with Release 3.0 will not work with data files created with Release 2.0.

To ensure that all the Release 2.0 data is loaded:

  1. Stop the pipeline.

  2. If UE Loader is automatically triggered, wait until all files are processed. If you run UE Loader manually, run it with all unprocessed data files.

Shutting Down Portal

Only the database instance should be running during the upgrade. For more information, see ”Starting and Stopping the BRM System” in BRM System Administrator's Guide.

  1. Stop all Portal processes.

  2. Ensure that the Connection Manager (CM) and Data Manager (DM) are not running.

  3. Ensure that no users are logged on to Portal.

    Users include customers, client applications, customer service representatives (CSRs), and so on.

Backing Up Your Portal Database

Make a complete offline backup of your Portal database, and ensure that the backup is completely valid and usable. For more information on performing full database backups, see your database software documentation.

In addition to the backup, use the Oracle export utility to export all Portal tables. This helps you restore individual tables if necessary.

Installing the Third-Party Software

Install the Third-Party software by following the instructions given in ”Installing the Third-Party software” in BRM Installation Guide.

Installing Revenue Assurance Manager Server 3.0

The upgrade adds or replaces several libraries, UE Loader templates, and iScripts. It also updates the database schema and the Portal data dictionary.

  1. Install the Revenue Assurance Manager server 3.0 package for your platform. Follow the instructions in ”Installing Revenue Assurance Manager” in BRM Collecting Revenue Assurance Data.


    Do not run the pin_setup script before upgrading your database. Running pin_setup before upgrading your database might corrupt your data.


    • Pay particular attention to entries that specify your installation directory; CM and DM port numbers; and database host name, user name, and password.

    • Ensure that the $SETUP_INIT_DB entry is set to YES.

    • Ensure that the $SETUP_DROP_ALL_TABLES entry is set to NO.

    • Ensure that the $SETUP_CONFIGURE entry is set to YES.

    • Ensure that the $CREATE_DATABASE_TABLES entry is set to NO.

  2. Make a backup copy of the pin_setup.values file (Portal_Home/setup/pin_setup.values file) and save it to another location.


    The upgrade scripts do not modify these files.

Installing the Upgrade Scripts

To install the Revenue Assurance Manager-Release-2.0-to-Release-3.0 upgrade scripts:

  1. Download the software to a temporary directory (temp_dir).


    • If you download to a Windows workstation, use FTP to copy the .bin file to a temporary directory on your UNIX server.

    • You must increase the heap size used by the Java Virtual Machine (JVM) before running the installation program to avoid ”Out of Memory” error messages in the log file. For information, see ”Increasing heap size to avoid ”Out of Memory” error messages” in BRM Installation Guide.

  2. Go to the directory where you installed the Third-Party package and source the file.


    You must source the file to proceed with installation, otherwise ”suitable JVM not found” and other error messages appear.

    Bash shell:


    C shell:

  3. Log in as user integrate, go to the temp_dir directory, and enter this command:



    You can use the -console parameter to run the installation in command-line mode. To enable a graphical user interface (GUI) installation, install a GUI application such as X Windows and set the DISPLAY environment variable before you install the software.
  4. Follow the instructions displayed during installation.

Configuring the Upgrade Parameters

The upgrade.cfg file contains your upgrade parameters, such as which scripts to execute and whether to upgrade only the most recent data. You should customize these parameters to meet your business requirements.

To edit the upgrade.cfg file:

  1. Log in as user pin, go to Portal_Home/upgrade/ara/20_30, and open the upgrade.cfg file in a text editor such as vi:

    % su - pin
    % cd Portal_Home/upgrade/ara/20_30
    % vi upgrade.cfg
  2. Go to Portal_Home/upgrade/ara/20_30, and open the upgrade.cfg file.

  3. Configure the file's parameters as necessary. For information on each parameter, see the comments in the upgrade.cfg file. Pay particular attention to the parameters described in Table 27-1:

    Table 27-1 upgrade.cfg Parameters of Note

    Parameter Description




    Specify the location of the database utilities and executables. By default, they are set to sqlplus, imp, and exp, respectively.


    Specifies the Portal database user name. By default, this is set to pin.


    Specifies the Portal database password. By default, this is set to pin.


    Specifies the name of the Portal database you are upgrading. By default, this is set to pindbdb.



    Specify the tablespaces where your new tables and indexes will be created.







    Specify the storage parameters to use when the tables and indexes are created.

    Note: Information on how your tablespace and storage parameters are configured in Release 2.0 is in your Release 3.0 Portal_ home/setup/scripts/pin_tables.values file. You can use this information to help you configure the parameters in your Release 3.0 upgrade.cfg file.


    Specifies which upgrade scripts are executed by the script.


    Specifies in which directory to create the log and pinlog files generated by the upgrade process.


    Specifies the user name with DBA privileges. By default, the user name is system.

    The user name and password are required to perform the pre-upgrade sanity checks of the database.


    Specifies the password of the DB_DBA_USER. By default, the password is manager.

Creating Required Database Objects and the Upgrade Log Directory

To create the database objects required for the upgrade and the upgrade log directory, run the script with the -o parameter. For more information, see "About the Script".

Performing Pre-upgrade Sanity Checks

Run the script with the -s parameter to verify the following:

  • The indexes required for upgrading have been created.

  • Portal storable class objects required for upgrading exist.

  • The following database preparations have been made:

    • The correct version of Oracle is installed.

    • The database character set is correct.

    • The Portal user pin has CREATE TABLE and CREATE SEQUENCE privileges.

    • The required rollback segments exist.

Results are printed to the pre_upg_sanity_chk1.sql.log and pre_upg_sanity_chk2.sql.log files in Portal_Home/upgrade/ara/20_30/sqllog directory. For more information, see "About the Script".


To reduce system downtime, you can perform the sanity checks before stopping Portal (see Shutting Down Portal).

Running the Database Upgrade Scripts

The script runs a series of scripts that upgrade the Release 2.0 tables to Release 3.0. By default, the script runs all the upgrade scripts.

To run only the offline scripts, you must first edit the upgrade.cfg file. See "Configuring the Upgrade Parameters".

For more information about the script, see "About the Script".

For information on offline scripts, see "Offline Scripts".

To upgrade your database schema:

  1. Run the script from the UNIX command prompt:

    % cd Portal_Home/upgrade/ara/20_30
    % perl
  2. Check each script's log and pinlog file in the directory specified by the UPGRADE_LOG_DIR parameter in your upgrade.cfg file (Portal_Home/upgrade/ara/20_30/sqllog).


    If any errors are reported, fix them, and then rerun the script.

Running pin_setup to Configure Revenue Assurance Manager

The pin_setup script reads the pin_setup.values file and configures Portal by initializing the database, configuring various pin.conf files, and starting various servers, including the dm_oracle server, the Connection Manager (CM), and the Java server.

To run the pin_setup script:

  1. Add customizations from your backed-up Revenue Assurance Manager 2.0 pin_setup.values file to the Revenue Assurance Manager Portal_Home/setup/pin_setup.values file.

  2. Log in as user pin, go to the Portal_Home/setup directory, and run the pin_setup script:

    % su - pin
    % cd Portal_Home/setup
    % ./pin_setup
  3. Check the following files for errors:

    • Portal_Home/setup/pin_setup.log

    • Portal_Home/var/cm/cm.pinlog

    • Portal_Home/var/cm/cm.log

    • Portal_Home/var/dm_database/dm_database.pinlog

    • Portal_Home/var/dm_database/dm_database.log

Restoring Service Authentication

See ”Using the Authentication and Authorization Modules” in BRM RADIUS Manager.

Dropping Obsolete Database Objects from the Database (Optional)

Perform this step after you verify that the upgrade is successful. Dropping obsolete database objects from the database is optional.

To drop obsolete database tables and columns, run the drop_old_tables.sql script from the command prompt:

% cd Portal_Home/upgrade/ara/20_30
% perl -e drop_old_tables.sql 

Updating the Revenue Assurance Configuration

Follow these steps to update the Revenue Assurance configuration:

  1. Updating to Revenue Assurance Manager Release 3.0 Scenarios

  2. Changing the Constraint on the IFW_AGGREGATION Table

  3. Loading Release 3.0 Scenarios

  4. Loading New UE Loader Templates into the Portal Database

  5. Updating All the Control Points to Collect Batch Processing Timestamps

  6. Changing the Date Format to Collect Call Processing Start and End Timestamps

  7. Updating the Pipeline Registry to Track iScript File Name Changes

  8. Updating the CollectProcessAuditForIREL Trigger with the New SQL Script

  9. (Optional) Tracking Changes in /process_audit/billing Objects

  10. (Optional) Changing Custom Revenue Assurance Aggregation Scenarios

Updating to Revenue Assurance Manager Release 3.0 Scenarios

Revenue Assurance Manager Release 3.0 scenarios collect more data from the pipeline than was collected by Release 2.0. The existing scenarios of Revenue Assurance Manager Release 2.0 must be dropped from the pipeline database by using Pricing Center, and the new scenarios must be loaded by using the SQL scripts (RevenueAssurance_Scenarios.sql).

To drop the Release 2.0 scenarios:

  1. Start Pricing Center.

  2. Choose View - Pipeline ToolBox - Aggregation - Scenarios.

    There will be Revenue Assurance Manager Release 2.0 sample scenarios from RA_01 to RA_14 and maybe some custom scenarios also.

  3. Select all the scenarios and delete them.

  4. Choose View - Pipeline Setup ToolBox - EDR - EDR container Description.

  5. Select RA_SAMPLE.

  6. Click Edit.

  7. Click the EDR Container Field tab.

  8. Select all the field details and delete them.

  9. Choose View - Pipeline Setup ToolBox - EDR - EDR container Description.

  10. Select RA_SAMPLE and delete it.

  11. Exit Pricing Center.

Changing the Constraint on the IFW_AGGREGATION Table

You must change the CKC_AGG_FUNCTION constraint before running the SQL script to load the new scenarios. To disable the constraint, run the SQL script update_v6.7.4_v6.7.5.sql on the pipeline database.

Loading Release 3.0 Scenarios

To use the sample scenarios, you must load them into the pipeline database.

To load the sample scenarios into a pipeline database, run the following commands against the pipeline database from the

IFW_Home/database/Oracle/Scripts directory:

sqlplus user/password@database RevenueAssurance_scenario.sql

where user is the pipeline user ID, password is the pipeline user password, and database is the pipeline database alias.

Loading New UE Loader Templates into the Portal Database

Upgrading copies the new UE Loader Template XML files into the installation directory, but you manually have to load them into the Portal database by using pin_uei_deploy utility. Before loading the new UE Loader templates, you must delete the old templates.

To delete the old UE Loader templates:

  1. Go to the Portal_Home/apps/uel/Revenue_Assurance directory.

  2. Run the following commands to delete the old templates:

    pin_uei_deploy -t RA01 -d
    pin_uei_deploy -t RA02 -d
    pin_uei_deploy -t RA03 -d
    pin_uei_deploy -t RA04 -d
    pin_uei_deploy -t RA05 -d
    pin_uei_deploy -t RA06 -d
    pin_uei_deploy -t RA07 -d
    pin_uei_deploy -t RA08 -d
    pin_uei_deploy -t RA09 -d
    pin_uei_deploy -t RA10 -d
    pin_uei_deploy -t RA11 -d
    pin_uei_deploy -t RA12 -d
    pin_uei_deploy -t RA13 -d
  3. Load the new UE Loader templates by running following commands:

    pin_uei_deploy -c -t RA01 -i BRM_Home/apps/uel/Revenue_Assurance/RA01.xml
    pin_uei_deploy -c -t RA02 -i BRM_Home/apps/uel/Revenue_Assurance/RA02.xml
    pin_uei_deploy -c -t RA03 -i BRM_Home/apps/uel/Revenue_Assurance/RA03.xml
    pin_uei_deploy -c -t RA04 -i BRM_Home/apps/uel/Revenue_Assurance/RA04.xml
    pin_uei_deploy -c -t RA05 -i BRM_Home/apps/uel/Revenue_Assurance/RA05.xml
    pin_uei_deploy -c -t RA06 -i BRM_Home/apps/uel/Revenue_Assurance/RA06.xml
    pin_uei_deploy -c -t RA07 -i BRM_Home/apps/uel/Revenue_Assurance/RA07.xml
    pin_uei_deploy -c -t RA08 -i BRM_Home/apps/uel/Revenue_Assurance/RA08.xml
    pin_uei_deploy -c -t RA09 -i BRM_Home/apps/uel/Revenue_Assurance/RA09.xml
    pin_uei_deploy -c -t RA10 -i BRM_Home/apps/uel/Revenue_Assurance/RA10.xml
    pin_uei_deploy -c -t RA11 -i BRM_Home/apps/uel/Revenue_Assurance/RA11.xml
    pin_uei_deploy -c -t RA12 -i BRM_Home/apps/uel/Revenue_Assurance/RA12.xml
    pin_uei_deploy -c -t RA13 -i BRM_Home/apps/uel/Revenue_Assurance/RA13.xml
    pin_uei_deploy -c -t RA14 -i BRM_Home/apps/uel/Revenue_Assurance/RA14.xml

Updating All the Control Points to Collect Batch Processing Timestamps

All the control points in Revenue Assurance Manager Release 3.0 must include the following registry entry:

IncludeProcessingTimestamps = TRUE

The following is a sample registry section for a control point in Revenue Assurance Manager Release 3.0:

 ModuleName = FCT_AggreGate
  Active = TRUE
  ScenarioReaderDataModule  = ifw.DataPool.ScenarioReader 
     TempDir = result/temp
     DoneDir          = result/done
     CtlDir           = result/ctl
     FieldDelimiter   = ;
     FlushMode        = 0
     ControlPointId   = CP_PreRatingBatchStat 
     IncludeProcessingTimestamps = TRUE
   TempSuffix     = .tmp
   DoneSuffix     = .dat
   WriteEmptyFile = FALSE
  Suffix = .ctl
  DataFilePath  = TRUE

Changing the Date Format to Collect Call Processing Start and End Timestamps

You must change the date format field in the UE Loader file to collect call processing and start and end timestamps.

To change the date format:

  1. Open the Portal_Home/apps/uel/ file.

    The infranet.uel.date_pattern field has the following date/time format:

    infranet.uel.date_pattern=dd/MMM/yyyy:hh:mm:ss a zzzz
  2. Change the date/time format to:


Updating the Pipeline Registry to Track iScript File Name Changes

The iScript file named ISC_SetDiscountValue.isc in Release 2.0 has been renamed to ISC_SetRevenueFigures.isc in Release 3.0.

To update the pipeline registry, in the pipeline registry sections for modules that use this iScript file, change the FileName entry to the new iScript name.


In Release 2.0, if you have set control point to collect revenue assurance data only on Retail charged amount, the iScript ISC_PostRating.isc was used to collect charging amount (there was no dependency on the iScript ISC_SetDiscountValue.isc). In Release 3.0, the iScript ISC_SetRevenueFigures.isc collects both Retail charged amount and discount and uses the iScript ISC_FCTBillingRecord.isc. So the control point that you have set to collect only charged amount must be moved after the iScript ISC_SetRevenueFigures.isc below the iScript ISC_FCT_BillingRecord.isc to collect only charged amount.

Updating the CollectProcessAuditForIREL Trigger with the New SQL Script

The updated trigger provided with Revenue Assurance Manager Release 3.0 is more refined and differentiates between rerating and recycling batches.

To update the trigger:

  1. Go to the Portal_Home/sys/data/config directory.

  2. Run the following command:

    sqlplus login/password@$ORACLE_SID @CollectProcessAuditForIREL.sql

    where ORACLE_SID is the Portal database alias.

(Optional) Tracking Changes in /process_audit/billing Objects


It is unlikely that any action is required, but this step is included for completeness.

All changes to Portal objects for revenue assurance between Release 2.0 and Release 3.0 are additive changes except for one of the changes to the object /process_audit/billing. In Release 2.0, this object has a single PIN_FLD_FAILED_ACCOUNTS array. In Release 3.0, this is a nested array within the PIN_FLD_BILLING_SEGMENTS array. Database reports that read this array do not need to be changed. The name of the table for this array has not changed, and the report will work as it did in Release 2.0. However, any custom applications that read a /process_audit/billing record through the PCM (or Java PCM) interface must be modified because the array has been moved. Also, the PIN_FIELD_BILL_SUPPRESSION and PIN_FIELD_REVENUE arrays are added in Release 3.0.

(Optional) Changing Custom Revenue Assurance Aggregation Scenarios


This step applies only if you have added custom aggregation scenarios for Release 2.0.

If you have custom aggregation scenarios for revenue assurance, following this step ensures that all Release 3.0 features will work correctly on the data produced by these scenarios. All Release 2.0 features will continue working with the data produced by custom scenarios, even if this step is not followed.

Release 3.0 contains new support for auditing the rerating process in the pipeline. In Release 3.0, distinct batch types are recorded for recycling and rerating. Also, when CDRs are suspended and recycled during rerating, Release 3.0 can record more relationships among the batches. It records relationships between the recycling batch and the rerating batches from which they were suspended, and also the relationships between the recycling batches and the batches in which the CDRs were first processed.

To record this information, aggregation scenarios must read an additional field from EDR container, and the UE Loader templates for loading the data must pass this additional field to the Portal opcode that creates the /process_audit objects. The scenarios and their corresponding UE Loader templates have been updated. Similar changes must be made in any custom scenarios to enable these Release 3.0 features. The changes are:

  • In the aggregation scenarios, the EDR container fields that group data must include DETAIL.ASS_SUSPENSE_EXT.SUSPENDED_FROM_BATCH_ID and the order for these grouping fields must be DETAIL.BATCH_ID, DETAIL.ORIGINAL_BATCH_ID, DETAIL.ASS_SUSPENSE_EXT.SUSPENDED_FROM_BATCH_ID.

  • The corresponding UE Loader templates load this additional fields, by including it on the input flist to PCM_OP_PROCESS_AUDIT_CREATE_AND_LINK (or PCM_OP_PROCESS_AUDIT_CREATE). The field on the input flist for this value is PIN_FLD_GROUP_DETAILS.PIN_FLD_SUSPENDED_FROM_BATCH_ID.

Command-Line Scripts

The following command-line scripts automate routine upgrade tasks:

Run these scripts from the UNIX prompt.

About the Script

This Perl script performs many of the upgrade tasks, such as creating database objects and running sanity checks.


perl -o | -s 


  • Specify only one parameter at a time.

  • Run with the -o parameter before you run it with -s parameter.


The parameters are described in Table 27-2:

Table 27-2 Parameters

Parameter Description


Creates the database objects required for the upgrade.

Important: Run with the -o parameter before you run it with any other parameter.

This parameter performs these operations:

  • Creates the UPG_LOG_T table that logs all the information about the upgrade.

  • Creates the pin_upg_common package that contains all the common routines for the upgrade.


Runs the pre-upgrade sanity check. See "Performing Pre-upgrade Sanity Checks".

Note: This requires a database administrator (DBA) user name and password for the Portal database. See the upgrade.cfg file for details.

About the Script

The Perl script is the main upgrade script. It runs many other SQL scripts in the correct order.

To run all the scripts listed in the upgrade.cfg file's @ALL_SCRIPTS parameter, enter this command at the UNIX prompt:

% cd Portal_Home/upgrade/ara/20_30
% perl


The scripts are run in the order they are listed in the parameter. For more information, see "Configuring the Upgrade Parameters".

About the Upgrade Scripts and Files

This section describes the scripts and files used to upgrade your Revenue Assurance Manager Release 2.0 database to Release 3.0.

Offline Scripts

The following scripts and files in Table 27-3 must run offline (while Portal is shut down) and finish running before Portal is restarted.

Table 27-3 Offline Scripts

Script Description


SQL script that creates the common routines needed for the upgrade.


SQL script that creates temporary audit PROC_AUD_BILL_ERR_ACCT tables.

Miscellaneous Scripts and Files

The following scripts in Table 27-4 are configuration files executed by offline scripts.

Table 27-4 Miscellaneous Scripts and Files

Script or File Description


SQL script that drops obsolete database tables and columns.



SQL scripts that perform sanity checks before the upgrade starts.


SQL script that creates the pinlog files.

Perl script that configures the araframework component.


Configuration file in which you must enter details about the Oracle database configuration before you run the upgrade scripts. All the upgrade Perl scripts parse this file to get the database connection parameters.

Perl script that performs many miscellaneous upgrade tasks related to the Oracle database.

Perl script that manages many miscellaneous upgrade tasks.

Master Perl script for the upgrade process. This Perl script calls other SQL scripts to perform the upgrade.


Flist that loads the new tables and columns.


SQL script that loads the default values.