Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(14 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Migrate the Informatica Domain and PowerCenter repository to a new database
Solution

To migrate the PowerCenter domain (and all repositories in the domain) to a new database server, do the following:

  1. Login to Administrator Console.
  2. Stop all Integration Services.
  3. Backup all repositories.
  4. Stop all Repository Services.
  5. Shut down the PowerCenter domain.
  6. Backup the domain using the infasetup backupDomain command
    (refer, PowerCenter Command Reference > "infasetup Command Reference" > "BackupDomain").
    This command creates a backup file of the domain configuration metadata.

    Syntax

    The BackupDomain command uses the following syntax:

    infasetup BackupDomain <-da database_hostname:database_port | -cs database_connection_string> -du database_user_name -dp database_password -dt database_type [-ds database_service_name] -bf backup_file_name [<-Force|-f>] -dn domain_name

    Example

    The following is an example of a domain in an Oracle database with connection string (-cs) option:

    infasetup BackupDomain -cs "jdbc:informatica:oracle://ora10gDB:1521;ServiceName=ORCL" -du Ora_User -dp Ora_PW -dt Oracle -bf INFA_Domain_Ora -dn domain_name

  7. Optional - Backup and restore the following tables:
    • For PowerCenter version 8.x
      • PCSF_CPU_USAGE_SUMMARY
      • PCSF_REPO_USAGE_SUMMARY
      • PCSF_RUN_LOG
      • PCSF_SOURCE_AND_TARGET_USAGE
    • For PowerCenter version 9.x
      • ISP_RUN_LOG
      • ISP_MASTER_ELECTION
      • ISP_MASTER_ELECT_LOCK
    • For PowerCenter version 10.x
      • ISP_RUN_LOG
  8. Confirm that you can connect to a new database (for Oracle use tnsping or SQL*Plus).
  9. Create the domain in the new database using the infasetup restoreDomain command (refer, PowerCenter Command Reference > "infasetup Command Reference" > "RestoreDomain").
    This restores the domain configuration metadata from the backup file created in step 6.
  10. Syntax

    The RestoreDomain command uses the following syntax:

    RestoreDomain <-da database_hostname:database_port | -cs database_connection_string> -du database_user_name -dp database_password -dt database_type [-ds database_service_name] -bf backup_file_name [<-Force|-f>]

    Example

    infasetup.sh RestoreDomain -cs "jdbc:informatica:oracle://ora10gDB:1521;ServiceName=ORCL" -du Ora_User -dp Ora_PW -dt Oracle -bf INFA_Domain_Ora

  11. Optionally, restore the tables that have been backup in Step 7 manually.
  12. Update connectivity information for the gateway node using the infasetup UpdateGatewayNode command.
    This command updates connectivity information for a gateway node on the current machine.
  13. Syntax

    The UpdateGatewayNode command uses the following syntax:

    UpdateGatewayNode <-da new_database_hostname:new_database_port | -cs new_database_connection_string> -du new_database_user_name -dp new_database_password -dt new_database_type [-ds new_database_service_name] -dn domain_name

    Example

    infasetup.sh UpdateGatewayNode -cs "jdbc:informatica:oracle://ora11gDB:1521;ServiceName=ORCL" -du new_ora_uname -dp new_ora_pword -dt Oracle -dn domain_name

  14. Start the Domain (infaservice.sh startup).
  15. Log in to the Administrator Console.
  16. Change the database settings for the Repository Service by clicking on "Edit" next to "Database Properties".
  17. Restart the Repository Service. If the new settings are correct, it will start in Exclusive mode and report that there is no Repository Content
  18. Now you are ready to restore the backup created earlier to insert the Repository content into the new database. Make sure not to select "Import as New" and not to select any of the "Skip" options.
  19. Finally, restart the Repository in Normal mode and resume normal operation.


For Data Quality, with the additional profiling warehouse and human task warehouse, as well as data object cache and reference data, it is more complicated because it does not have a backup and restore utilities to handle those repositories. DOC can be rebuilt within the Administrator tool and the Reference data can be backed up and restored – more easily if stored in one project – in the Developer tool.

The other two might need to be rebuilt manually or by using some database techniques to export the data as insert statements that can be modified for the other DB type and then executed. The Model Repository Service database can be backed-up and restored similar to the way the PowerCenter Repository was done.

More Information

Command Lines for backing up and restoring Repositories:

Syntax

Backup

pmrep connect -r <repository_name> -d <domain_name> -n <user_name> -x <password>
pmrep backup -o <output_file_name> [-d <description>] [-f (overwrite existing output file)] [-b (skip workflow and session logs)]  [-j (skip deploy group history)] [-q (skip MX data)] [-v (skip task statistics)]

Example

pmrep connect -r pcrepo -n Administrator -x userpassword -d pcdomain > backuplog.out 2>1
pmrep backup -o backup_prod.rep >> backuplog.out 2>1

Restore

pmrep connect -r <repository_name> -d <domain_name> -n <user_name> -x <password>
pmrep restore -i <input_file_name> [-f (skip task statistics)]  [-b (skip workflow and session logs)]  [-j (skip deploy group history)] [-q (skip MX data)] [-a (as new repository)]

Example

pmrep connect -r restore_rep -d Domain_s861
pmrep>restore -u admin -p admin -i C:\backup_prod.rep
Note 

 

Optionally, you can also do a safe backup of your users and passwords using:

infacmd exportUsersAndGroups and infacmd ImportUsersAndGroups commands.

 

Descriptions of the tables require manual backup and restore for domain:

 

For PowerCenter versions 9.x and 10.x

ISP_RUN_LOG

Stores the location of workflow and session log binary files. The Log Manager retrieves the location of files from this table. The Workflow Monitor and infacmd commands request the location of the workflow and session log binary files from the Log Manager.


ISP_MASTER_ELECTION

 

It stores the Master Gateway Node last refresh time and the domain name, node name, the hostname of the Master Gateway Node. Election status is also stored in this table.

 

ISP_MASTER_ELECT_LOCK

The domain name and node name of the current Master Gateway Node is stored in this table.


Reference
Applies To
Product: PowerCenter
Problem Type: Configuration
User Type: Administrator
Project Phase: Configure
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:8/25/2019 11:39 PMID:108709
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)