This chapter describes how to use the Oracle Communications Billing and Revenue Management (BRM) Account Migration Manager (AMM) software to migrate accounts from a source database schema to a destination database schema in the same database. It also describes how to perform such account migration when you use Oracle IMDB Cache in your environment.
Migrating accounts includes the following general tasks. Although you can perform some tasks at any time, the following order is recommended:
If you use Oracle IMDB Cache in your system, verify that you have configured your environment appropriately.
See "Verifying Your System Configuration When Oracle IMDB Cache Is Used" for more information.
Create the account search configuration file.
See "Creating the Account Search Configuration File" for more information.
Submit the account migration job.
See "Submitting the Account Search File" for more information.
For account group migration, run a group_details report to verify that each account group includes all account members.
See "Checking Account Group Details" for more information.
Caution:
You must verify that the job includes all accounts in the account group. Any missing accounts will be stored in a separate database schema from the account group, which severs the account's relationship with the group.Enable the account migration job.
See "Enabling Migration Jobs in the Queue" for more information.
Start the AMM Controller.
See "Starting the AMM Controller" and "Monitoring the AMM Controller" for more information.
Monitor the job's progress.
See "Monitoring Account Migration" for more information.
Fix account migration failures, when necessary.
See "Handling Account Migration Failures" for more information.
Purge the migrated accounts from the source database schema.
See "Purging Migrated Objects from the Source Database Schema" for more information.
Stop the AMM Controller.
See "Stopping the AMM Controller" for more information.
If you use Oracle IMDB Cache in your system, load the data for the migrated accounts into the destination cache grid.
See "Loading Destination Oracle IMDB Cache with Data from BRM Database" for more information.
See "Deleting Jobs from the Source Database Schema" for information on deleting jobs from the source database schema.
Verify the following before you proceed with the account migration:
The TimesTen JDBC Driver Jar files are available for every instance of BRM in your environment. See "Configuring the TimesTen JDBC Driver Jar file for BRM".
Verify that the Infranet.properties file is configured appropriately. See "Configuring the AMM Infranet.properties File".
Verify that the load_pin_uniqueness utility has been configured correctly. See "Configuring the load_pin_uniqueness Utility for Oracle IMDB Cache".
You use the account search configuration file to specify the source and destination database schemas, the search criteria, the maximum number of accounts in a job, and the number of accounts in each batch.
AMM can search for accounts that meet five default criteria:
Account creation date
Account status
Billing day of month
Product name
POID
If you would like to migrate accounts that meet some other custom criteria, see "Creating Custom Account Search Criteria".
To create an account search configuration file:
Copy the sample account search configuration file (BRM_home/apps/amt/account_search.cfg) and save it with another name. Use this file, which contains all of the configuration entries, as a template.
Edit the entries listed in Table 35-1 in the file.
Note:
Only the source database schema, destination database schema, batch size, and one other entry is required. If you do not want to use an entry, leave it blank.Table 35-1 account_search.cfg Parameters
| Parameter | Description | Required |
|---|---|---|
|
src_database |
Specifies the source database schema, which is the schema from which you are migrating accounts. For example, enter 0.0.0.1. This value must match one of the database numbers specified in the Infranet.properties file. |
Yes |
|
dest_database |
Specifies the destination database schema, which is the schema to which you are migrating accounts. For example, enter 0.0.0.2. This value must match one of the database numbers specified in the Infranet.properties file. |
Yes |
|
start_creation_date |
Use this parameter to migrate accounts that were created in a specific date range. AMM migrates accounts created between midnight (00:00:00) on the start date and 23:59:59 on the end date. For example, to migrate accounts created after midnight on August 1, 2004, enter 08/01/2004. Important: If you set this parameter, you must also set the end_creation_date parameter. |
No |
|
end_creation_date |
Use this parameter to migrate accounts that were created in a specific date range. AMM migrates accounts created between midnight (00:00:00) on the start date and 23:59:59 on the end date. For example, to migrate accounts created on or before 11:59:59 p.m. on August 10, 2004, enter 08/10/2004. Important: If you set this parameter, you must also set the start_creation_date parameter. |
No |
|
migration_mode |
Specifies whether to migrate account groups. When AMM finds an account that belongs to a hierarchy, sponsorship, or resource sharing group, AMM migrates all accounts related to that account.
The default is ExcludeAccountGroup. Important: If you set this parameter, you must also set the max_group_size parameter. |
No |
|
max_group_size |
Specifies the maximum size of an account group that AMM can migrate. If an account group exceeds the maximum number of accounts, AMM excludes the account group from the job. The default is 100. |
No |
|
product_name |
Migrates accounts that purchased the specified product. For example, Product 1b - Email Account. |
No |
|
account_status |
Migrates accounts based on the specified account status.
|
No |
|
bill_day_of_month |
Migrates accounts that have the specified billing day of month. You can specify any number from 1 through 31. For example, enter 4 to migrate all accounts that are billed on the 4th of the month. |
No |
|
max_accounts |
Specifies the maximum number of accounts to move in a job. |
No |
|
batch_size |
Specifies the number of accounts in each batch. You can specify any amount from 1 through 1,000. However, for optimal performance, set this to an integer between 50 and 100. Important:
|
Yes |
|
poid_list |
Migrates accounts based on the POID. Use comma separators, for example, 22860, 22861, 22862. Limit the number of accounts to 1,000 or less. |
No |
Save the file.
The following sample account search configuration file specifies to:
Migrate accounts from database schema 0.0.0.1 to database schema 0.0.0.2.
Migrate in batches of 50 accounts.
Migrate only nonmember accounts.
Migrate accounts that meet the following criteria:
Created between January 1, 2004 and June 31, 2004
Have an active account status
Purchased the Product 1b - Email Account product
src_database=0.0.0.1 dest_database=0.0.0.2 start_creation_date=01/01/2004 end_creation_date=06/31/2004 migration_mode=ExcludeAccountGroup max_group_size= product_name=Product 1b - Email Account account_status=Active bill_day_of_month= max_accounts= batch_size=50 poid_list=
When you submit an account search file, the pin_amt utility searches the source database schema and populates the job management tables on the primary, source, and destination database schemas with a list of accounts meeting the specified criteria.
Submit your account search information to the pin_amt utility:
% pin_amt -s AccountSearchFile submitted job job_id=30
The pin_amt utility notifies you if it successfully submitted the file and gives you the job ID number.
Write down the job ID number, because you will need it later.
The AMM Controller can only begin processing an account migration job after it's enabled in the queue.
To enable a job in the queue, enter this command:
% pin_amt -e JobID enabled job
After it is started, the AMM Controller runs as a server process, continuously checking for jobs to process in the queue.
To start the AMM Controller, enter this command:
% pin_amt -c start [-a ControllerID] controller is started controller_id=1
Note:
If your system contains multiple AMM Controllers, use the -a option to specify which AMM Controller to start. By default, pin_amt starts Controller 1.The pin_amt utility notifies you if the AMM Controller started successfully and which AMM Controller is active.
You can monitor the AMM Controller's status at any time by using the pin_amt utility or checking the AMM Controller log file.
To check whether the AMM Controller is up and running, enter this command:
% pin_amt -c status [-a ControllerID] controller status is up
Note:
If your system contains multiple AMM Controllers, use the -a option to specify which AMM Controller to check.The pin_amt utility notifies you that the AMM Controller is up or down. If the AMM Controller is down or cannot be started, check the AMM Controller log file for more information.
The AMM Controller log file contains a detailed list of all transactions executed by the AMM Controller. This log is created in the directory specified in the controller_N_log_directory entry of the Infranet.properties file. You can open the log file by using a text editor.
You can use the pin_amt utility to see what the AMM Controller is doing in real time.
To monitor the AMM Controller in real time, enter this command:
% pin_amt -c log [-a ControllerID]
Note:
If your system contains multiple AMM Controllers, use the -a option to specify which AMM Controller to check.A separate Xterm window opens. For best viewing, set the Xterm width to 120. If an Xterm window fails to open, make sure your DISPLAY environment variable is set correctly.
You can monitor the status of jobs in the queue by running three special AMM reports: list_jobs, job_details, and group_details.
The list_jobs report provides the status of each job in the queue, including the number of batches that failed to migrate.
To run the list_jobs report, enter this command:
% pin_amt -r list_jobs
Sample output from a list_jobs report:
Tue Mar 12 page 1
AMT jobs
Total Failed Succ.
Account Account Account Proc. Job creation
Job Name User Name Job ID batches batches batches Job Status time[sec] time Accounts
-------- --------- ------ ------- ------- ------- ---------- --------- ---------------- --------
test.cfg pin 1 6 0 6 FINISHED 40 02/02/2002 13:39 100
srch.cfg pin 2 3 0 3 FINISHED 25 02/15/2002 12:00 50
mar1.cfg pin 3 8 1 7 FINISHED 205 03/01/2002 18:42 400
If any batches failed, you can see greater detail on why the batch failed by running the job_details report.
The job_details report provides detailed information about a job's status, including why a batch failed.
To run the job_details report, enter this command:
% pin_amt -r job_details
enter job id:
Sample output from a job_details report:
Tue Mar 12 page 1
AMT job details
Account Processing start Batch processing
Job ID batches Status Error Message date time[sec] Accounts
------ ------- -------- --------------------------- ---------------- ---------------- --------
3 1 FINISHED 03/01/2002 18:42 25 50
2 FAILED ORA-02055: distributed 03/01/2002 18:42 5 0
update operation failed;
rollback required
ORA-02049: timeout:
distributed transaction
waiting for lock
ORA-06512: at ”PIN.AMT_MV”,
line 454
ORA-06512: at line 1
3 FINISHED 03/01/2002 18:42 25 50
4 FINISHED 03/01/2002 18:43 25 50
5 FINISHED 03/01/2002 18:43 25 50
6 FINISHED 03/01/2002 18:44 25 50
7 FINISHED 03/01/2002 18:44 25 50
8 FINISHED 03/01/2002 18:45 25 50
The report lists any error messages from the Oracle database. For information, see the Oracle documentation.
The group_details report lists the accounts in each account group and provides information about each group's migration status. You use this information to verify that all account members are included in a group.
Caution:
All accounts in a hierarchy, sponsorship, or resource sharing group must reside in the same database schema. Any accounts separated from a parent account will no longer be associated with the account group.To run the group_details report, enter this command:
% pin_amt -r group_details
enter job id:
enter group id:
Sample output from a group_details report:
Tue Mar 12 page 1
AMT group details
Account Batch Group Group
Job ID batches Status ID Status
------ ------- -------- --------- ----------------
3 1 FINISHED 1 NOT_PROCESSED
Tue Mar 12 page 1
AMT group member details
Account
batches Accounts ID Accounts DB
------- ----------- -----------
1 17009 2
1 17289 2
1 16489 2
1 17313 2
1 16465 2
1 17066 2
An account batch may fail for several reasons. The most common reasons are as follows:
An application is accessing or modifying the data you are attempting to migrate.
The database is down.
For information on why a batch failed, you can run a job_details report or check any of the following files, which are located in the directories you specified in the Infranet.properties file.
AMM installation log file (pin_amt_install.log)
AMM Controller log file (controller_N_YYYYMMDDhhmm.log)
pin_amt log file (pin_amt.log)
AMM configuration file (Infranet.properties)
Account search configuration file (account_search.cfg)
AMM Mover log files (amt_migrate_JobID_BatchNumber.log)
AMM delete log file (amt_delete_JobID_BatchNumber.log)
If you need assistance in resolving migration failures, send these files along with any additional information about the problem to your Oracle BRM representative.
To reprocess a batch that failed:
Fix the problem.
Change the status of the batch from FAILED to NOT PROCESSED:
% pin_amt -b JobID:BatchNumber
Enable the job in the queue again:
% pin_amt -e JobID
The AMM Controller processes all batches that have a NOT PROCESSED status and ignores batches with a FINISHED status.
After you successfully migrate your accounts, you can improve your overall system performance by purging the migrated (invalid) objects from your source database schema. Also, because the purging process uses only one thread, purges accounts sequentially, and does not affect data used by BRM, you can purge accounts at any time.
To purge successfully migrated objects from the source database schema, enter this command:
pin_amt -p SourceDatabaseSchema
You can use the delete option to:
Remove both failed and successfully migrated jobs from your database schemas
Free up disk space
The delete option performs the following actions listed in Table 35-2:
| Job Type | Action |
|---|---|
|
Failed jobs |
Deletes the job from the AMM job management tables. |
|
Successfully migrated jobs |
|
To delete a job, run the pin_amt script with the delete option:
pin_amt -d JobID
You can stop the AMM Controller at any time. If you stop the AMM Controller while it is processing a batch, it finishes the batch before stopping.
To stop the AMM Controller, enter this command:
% pin_amt -c stop
controller is stopped
controller_id=1
If a job contains a large number of accounts, but you only have a limited amount of time in which to migrate accounts, you can migrate the job in stages. The AMM software enables you to start an account migration job and then pause it when your window of opportunity is over. When you reach the next window of opportunity, you can resume the job where it left off.
Note:
An AMT Controller must completely finish migrating one job before it can start migrating another job. Therefore, if you pause one job and then enable a second job, the AMT Controller cannot begin processing the second job until the first job is finished.To pause an account migration job, enter this command:
% pin_amt -c pause
paused controller
controller_id=1
To resume an account migration job, enter this command:
% pin_amt -c continue
continued controller
controller_id=1
Load the destination Oracle IMDB Cache with the migrated accounts from the destination BRM database schema only after the pin_amt utility successfully migrates the accounts into the destination BRM database schema.
Note:
For each migrated account, the AMM Mover updates the account POIDs in the uniqueness table to reflect the account's new location.After you complete this step, the critical data for the migrated accounts will be in the appropriate Oracle IMDB Cache (data store) ready for use.
To load the account data from the destination BRM database schema into the appropriate Oracle TimesTen Cache (data store) in the destination Oracle IMDB Cache grid, you must run the appropriate tt_load_Logical_Partition.sql script. The tt_load_Logical_Partition.sql scripts were generated on the destination Oracle IMDB Cache during the installation of Oracle TimesTen.
For example, the destination Oracle TimesTen Cache (data store) for a migration task is identified as tt_0.1.0.1 (associated with the logical partition identified as 0.1.0.1). After you migrate accounts to the BRM database schema associated with this destination Oracle IMDB Cache, you must run tt_load_0.1.0.1.sql on the 0.1.0.1 logical partition of this cache grid.
If some accounts were migrated to one Oracle IMDB Cache and other accounts were migrated to a different Oracle IMDB Cache, then you must load the data for the migrated accounts from the BRM database schema into the appropriate cache. To do so, you the run tt_load_Logical_Partition.sql script for every (destination) data store which received the migrated accounts.
Note:
If necessary, run the pin_tt_schema_gen utility against the destination Oracle IMDB Cache grid to generate the required tt_load_Logical_Partition.sql scripts for the logical partitions.Complete the steps in the following procedure only after the account migration is successfully completed. To load subscriber data from the BRM database schema into the destination Oracle IMDB Cache:
Go to the bin directory where the active data store resides:
cd IMDB_home/bin
where IMDB_home is the directory in which Oracle IMDB Cache is installed.
Connect to the destination Oracle IMDB Cache using ttIsql:
ttisql Data_Store_Name
where Data_Store_Name is the name of the data store, such as tt_0.1.0.1
Enter the following command to run the required tt_load.sql script against the destination Oracle IMDB Cache data store:
run BRM_home/bin/tt_load_Logical_Partition.sql;
where Logical_Partition is the database number of the logical partition associated with the data store, such as 0.1.0.1
See "Loading Subscriber Data into the Oracle IMDB Cache Data Stores" for information.
You can use an external scheduler, such as cron, to automate account migration during your maintenance window. If you must migrate a large number of accounts, you can set up cron to stop and restart account migration at specific times.
For example, scheduling account migration for every Sunday from 2:00 a.m. to 4:00 a.m. requires these tasks:
Stop the AMM Controller.
Create your account search configuration files.
Submit your jobs.
Enable your jobs in the queue.
Configure one cron job to start the AMM Controller every Sunday at 2:00 a.m. and check for errors. See "AMM Return Codes and Messages".
Configure a second cron job to stop the AMM Controller every Sunday at 4:00 a.m. and check for errors.