Manage the APM Database

As an administrator, you can export and import data to and from the APM database. You can import and export either the entire database, or only the configuration data.
apmdevops106
As an administrator, you can export and import data to and from the APM database. You can import and export either the entire database, or only the configuration data.
Verify Backup and Restore Prerequisites
Verify the following backup and restore prerequisites:
  • For PostgreSQL: Verify that the computer on which you are running a database script has PostgreSQL installed. You can run the script on a remote computer, as long as that remote computer also has PostgreSQL installed.
  • For a Solaris: Verify that the JAVA_HOME environment variable is set on the computer on which you run the dbrestore script.
  • For JRE 1.6: Set the JAVA_HOME environment variable and point it to the JRE root folder. JRE 1.6 or later is required, and the path cannot contain spaces. For example, if your JRE is installed in c:\program files\java\jre1.6.0, set JAVA_HOME to the shortened version of the path: c:\progra~1\java\jre1.6.0.
Back Up the APM Database
The APM database includes CA CEM configuration settings, users and groups, business services, and transaction definitions. You can back up the APM database and configuration information. For example, you can keep a copy of your database on a separate computer to protect your data. To keep a copy, backup the database, and then copy the database backup file to another computer.
The backup utility creates a .backup file of the APM database. You can customize the backup filename. If you do not specify a filename, the script creates a backup file with the name of the database and filename suffix of .backup.
Follow these steps:
  1. Navigate to the database-scripts directory for your operating system.
    • Linux: 
      <EM_Home>
      /install/database-scripts/unix/
    • Windows: 
      <EM_Home>
      \install\database-scripts\windows\
  2. Open a command line, and run the following script for your operating system:
    • Windows: dbbackup-postgres.bat
    • Linux: dbbackup-postgres.sh
    Use the following syntax and arguments:
    [dbbackup-postgres.bat | sh dbbackup-postgres.sh] [dbserverhostip] [dbinstalldir] [dbname] [dbuser] [dbpassword] [dbport] [outputdir]
    Include the following arguments:
    • dbserverhostIP
      Specifies the IP address of the computer hosting the database. If you are running it on the computer directly, you can specify localhost.
    • dbinstalldir
      Specifies the directory location of the APM database.
      On Linux, this path must be the escaped or quoted if it includes spaces. For example, the directory: /root/Introscope Enterprise Manager can be represented as: /root/Introscope\ Enterprise\ Manager.
      The default location is:
      Linux: opt/database
      Windows: <
      EM_Home
      >\database
    • database_name
      Specifies the database instance name. If you did not change the default, set this argument to cemdb.
    • dbuser
      Specifies the user name of the database user that is associated with the APM database. If you did not change the default, set this argument to admin.
      This value is
      not
      the PostgreSQL database administrator user name.
    • password
      Specifies the password for the database user that is associated with the APM database.
    • port_number
      Specifies the port number on which the database communicates to the Enterprise Manager and other components. If you did not change the default, set this argument to 5432.
    • dbbackupdir
      Specifies the existing directory location where you want to save the database backup file. For Linux, this value must be the escaped version of the path if it includes spaces. There cannot be quotes around the directory and no ending slash. For Linux, an example is the directory /root/Introscope Enterprise Manager can be represented as /root/Introscope\ Enterprise\ Manager.
    • outputdir
      Specifies the output directory for the script.
    • outputfile
      (Optional) Specifies the name of the database backup file. If you do not specify this parameter, the script generates a file with named dbname.backup, with the database name supplied. The backup filename suffix is .backup by standard convention, but it is not a requirement.
Example: Back Up a CA CEM Database
The following example generates a cemdb.backup file in the /opt/database/backups directory:
sh dbbackup-postgres.sh 127.0.0.1 /opt/database cemdb admin quality 5432 ./backups
When the backup process completes, a confirmation message displays.
If the script encounters errors, here are common explanations:
  • The database name is incorrect.
  • PostgreSQL is not installed.
  • The location of the APM database is incorrect.
Restore the APM Database
The restore utility lets you restore an APM database backup file to the current system. For example, you can restore your database after a system crash or a hard disk failure.
The time that it takes to restore a database is directly related to the database size. A large database takes longer to restore than a small one. The restore times also depend on the computer capacity and memory. For example, a 1-GB database can take one to two hours to restore, depending on the computer resources.
Follow these steps:
The restore function destroys the specified database before it creates a database with the name specified. If the database is on a UNIX computer, the restore process upgrades the database.
  1. Ensure that no users are connected to the database.
  2. Navigate to the database-scripts directory for your operating system.
    • Linux: 
      <EM_Home>
      /install/database-scripts/unix/
    • Windows: 
      <EM_Home>
      \install\database-scripts\windows\
  3. Open a command line, and run the following script for your operating system:
    • Windows: dbrestore-postgres.bat
    • Linux: dbrestore-postgres.sh
    Use the following syntax and arguments:
    [dbrestore-postgres.bat | sh dbrestore-postgres.sh] [dbserverhostip] [dbinstalldir] [dbserviceuser] [dbservicepassword] [dbname] [dbuser] [dbpassword] [dbport] [backupfile]
    Include the following arguments:
    • dbserverhostIP
      Specifies the IP address of the computer hosting the database. If you are running it on the computer directly, you can specify localhost.
    • dbinstalldir
      Specifies the directory location of the APM database.
      On Linux, this path must be the escaped or quoted if it includes spaces. For example, the directory: /root/Introscope Enterprise Manager can be represented as: /root/Introscope\ Enterprise\ Manager.
      The default location is:
      Linux: opt/database
      Windows: <
      EM_Home
      >\database
    • dbserviceuser
      Specifies the PostgreSQL administrator username. By default, this name is
      postgres
      .
    • dbservicepassword
      Specifies the password that is associated with dbserviceuser (the PostgreSQL administrator password).
    • database_name
      Specifies the database instance name. If you did not change the default, set this argument to cemdb.
    • dbuser
      Specifies the user name of the database user that is associated with the APM database. If you did not change the default, set this argument to admin.
      This value is
      not
      the PostgreSQL database administrator user name.
    • password
      Specifies the password for the database user that is associated with the APM database.
    • port_number
      Specifies the port number on which the database communicates to the Enterprise Manager and other components. If you did not change the default, set this argument to 5432.
    • backupfile
      The directory location and name of the database backup file.
Example: Restore a cemdb.backup File
The following example restores a cemdb.backup file from the /opt/database/backups directory:
sh dbrestore-postgres.sh 127.0.0.1 /opt/database postgres [email protected] cemdb admin quality 5432 ./backups/cemdb.backup
When the restore process completes, a confirmation message displays.
If you encounter errors:
  • The most common error is that users are still connected to the database. You can restart the PostgreSQL server to disconnect the users.
  • If you get an error that says that database does not exist, disregard it. This error means that the script is being used to create a database in the process.
Prepare for an APM Database Export or Import
In some cases, it is not possible to place the CA CEM system in a production environment for the first installation. For example, a company policy requires that you install all new hardware and software in a test lab before entering the production network. Establish and test your CA CEM configuration in a QA or test environment. Then, move the proven configuration into production using the CA CEM export and import. Configuration information includes users and user groups and business services and transactions.
Both export and import require that you run the scripts:
  • On the computer where the APM database is installed.
  • In the location where the Enterprise Manager installer placed the scripts.
These scripts refer to files that are relative to these locations.
Follow these steps:
  1. Back up the APM database before attempting to run the two utilities (especially import, because it destroys the existing configuration before rebuilding it). The import script destroys the database, and the result is a database that contains only the configuration data for the old database.
  2. Turn off all connections to the APM database.
  3. Shut down the Enterprise Manager.
  4. Close any database administration tools (for example, pgAdmin).
  5. Verify that all users are logged off or have closed the CEM console.
    You do not have to disable the TIMs, but you must enable them after import.
  6. (Valid for JRE 1.6 or later) Set the JAVA_HOME environment variable and point it to the JRE root folder. JRE 1.6 or later is required, and the path cannot contain spaces. For example, if your JRE is installed in c:\program files\java\jre1.6.0, set JAVA_HOME to the shortened version of the path, c:\progra~1\java\jre1.6.0.
If you are importing the configuration from a previous version of CA CEM, you can import any previous version on Linux. The version must be 5.0 or later on Windows. The import utility automatically upgrades the database schema from the old release to the current release.
Export the APM Database Configuration
The configexport.bat or configexport.sh script creates an XML file with all the CA CEM configuration information. Configuration information includes users and user groups and business services and transactions. Exporting configuration information enables you to move data from test or lab systems as a base for a production system.
Follow these steps:
  1. Set the JAVA_HOME environment variable.
  2. Navigate to the 
    <EM_Home>
    /install/database-scripts directory for your operating system. For example:
    cd /home/Introscopex.x.x.x/install/database-scripts/unix
    : Replace x.x.x.x with the correct APM version number.
  3. Open a command or terminal window and run the configexport.bat on Windows or configexport.sh on other platforms with the following arguments:
    ConfigExport <dbhostIP> <database_name> <dbuser> <password> <port_number> <databasetype> <export-file>
    • dbhostIP
      Specifies the IP address of the computer hosting the database. You cannot use localhost for this command.
    • database_name
      Specifies the database instance name. If you are using PostgreSQL and you did not change the default, set this argument to cemdb. If you are using Oracle, set this argument to the Oracle system identifier (SID).
    • dbuser
      Specifies the user name of the database user that is associated with the APM database. If you are using PostgreSQL and you did not change the default, set this argument to admin. If you are using Oracle, set this argument to the APM database schema owner.
      Note: 
      This name is 
      not 
      the PostgreSQL database administrator user name.
    • password
      Specifies the password for the database user that is associated with the APM database.
    • port_number
      Specifies the port number on which the database communicates to the Enterprise Manager and other components. If you are using PostgreSQL and you did not change the default, set this argument to 5432. If you are using Oracle and the default Listener port, set this argument to 1521.
    • databasetype
      Specifies the type of database you are using. Set this argument to
      postgres
      or
      oracle
      .
    • export-file
      Specifies the absolute path and file name for the XML file that contains the exported data.
Example: Export the APM Database Configuration
This example shows how to run the configexport program on Linux. Use a command line similar to the following one:
./configexport.sh 197.168.144.10 arcade apmdba mypwd 1521 oracle /home/apmdba/export_oracle.xml
When the configuration export process completes, a confirmation message displays.
Import the APM Database Configuration
You can import the configuration settings from one APM database to another. For example, you can use the configimport.bat or configimport.sh program to import the database configuration from a QA system onto a production system. The imported configuration settings include users and user groups and business services and transactions.
The APM database configuration import process does the following actions:
  • Deletes the previous database configuration information (about operators, users, groups, business service process, and transaction definitions) and replaces it with the imported configuration data.
  • Deletes defect, report, and statistical data and does not replace it.
Configuration import does not import an existing HTTP analyzer plug-in configuration. To install the plug-in manually, use the CEM console.
Follow these steps:
  1. Navigate to the 
    <EM_Home>
    /install/database-scripts directory for your operating system. For example:
    cd /home/Introscopex.x.x.x/install/database-scripts/unix
    : Replace x.x.x.x with the correct APM version number.
  2. Open a command or terminal window and run the configimport.bat on Windows or configimport.sh on other platforms with the following arguments:
    ConfigImport -dbhost <hostname> -dbname <database_name> -dbport <port_number> -databasetype <database_type> -dbuser <dbuser> -dbpassword <password> -dbscriptsdir <dir> -importfile <import-filename> -targetrelease <targetrelease> -dbserviceuser <dbserviceuser> -dbservicepwd <dbservicepwd> [-postgresinstalldir <postgresinstalldir>] [-nolog] [-jdbcbatchsize <jdbcbatchsize>] [-promptbeforeimport true|false] [-is64Bit true|false]
    • dbhost
      Specifies the host name or IP address of the computer hosting the database. You cannot use localhost for this command.
    • dbname
      Specifies the database instance name. If you are using PostgreSQL and you did not change the default, set the argument to cemdb. If you are using Oracle, set the argument to the Oracle system identifier (SID).
    • dbport
      Specifies the port on which the database communicates to the Enterprise Manager and other components. If you are using PostgreSQL and you did not change the default, set the argument to 5432. If you are using Oracle and the default Listener port, set the argument to 1521.
    • databasetype
      Specifies the type of database you are using. Set the argument to postgres or oracle.
    • dbuser
      Specifies the user name of the database user that is associated with the APM database. If you are using PostgreSQL and you did not change the default, set the argument to admin. If you are using Oracle, set the argument to the APM database schema owner.
    • dbpassword
      Specifies the password for the database user that is associated with the APM database.
    • dbscriptsdir
      Specifies the directory for database-specific SQL scripts. For example, use 
      <EM_Home>
      /install/oracle/database-scripts if you are using an Oracle database schema or 
      <EM_Home>
      /install/database-scripts if you are using a PostgreSQL database.
    • importfile
      Specifies the absolute path and file name for the XML file that contains the imported data.
    • targetrelease
      Specifies the target release version of the database you want after the import process.
    • dbserviceuser
      Specifies the PostgreSQL administrative service user account. The default administrative service account is postgres. The argument is not applicable for Oracle.
    • dbservicepwd
      Specifies the password for the PostgreSQL administrative service user account.
    • postgresinstalldir
      (Optional) Specifies the directory location of the APM database when using PostgreSQL. On Linux or Solaris, the path must be enclosed in quotes or include escape sequences if the path has spaces in it. For example, if the directory is /root/Introscope Enterprise Manager, you can specify the location as "/root/Introscope Enterprise Manager" or /root/Introscope\ Enterprise\ Manager.
      If you did not change the defaults, the location is:
      • opt/database on 32-bit Linux or Solaris
      • opt/database/postgres/8.3-community on 64-bit Linux or Solaris
      • <EM_Home>
        \database on Windows
      The argument is not applicable for Oracle.
    • nolog
      (Optional) Specifies that you want to turn off logging to standard output (StdOut).
    • jdbcbatchsize
      (Optional) Specifies the maximum size of data packets that are transferred at a time through Java database connections (JDBC). The default value is 2500.
    • promptbeforeimport
      (Optional) Specifies whether you want the configimport program to display a prompt before it begins importing data. If you want a prompt, set this parameter to true. The default value is false.
    • is64Bit
      Specifies whether the PostgreSQL database is 32-bit or 64-bit.If you are importing data into a 64-bit PostgreSQL database, set this parameter to true. This argument is not applicable for Oracle databases.
    The import script performs an initial validation to ensure that the XML is properly formatted. The import script offers you the option to validate the XML more thoroughly to ensure the XML content can be processed properly.
  3. Enter 
    Y
     to perform the validation, or enter 
    to continue with the import without validating the XML content.
    This second validation time depends on the size of the XML file.
    If either validation fails, an error message displays, and the configuration import does not proceed. If both validation checks succeed, the script imports the specified database configuration data.
Example: Use a Command Line for Importing
For example, to run the configimport program on Linux and a PostgreSQL database, use a command line similar to the following one:
./configimport.sh -dbhost sfqa07 -dbname cemdb -dbport 5432 -databasetype postgres -dbuser admin -dbpassword mypassword
 
-dbscriptsdir /apps/Introscope/install/database-scripts -importfile /tmp/apm_data.xml -targetrelease x.x.x.x -dbserviceuser postgres -dbservicepwd %pas5w0rd -postgresinstalldir /opt/database -promptbeforeimport true
Move the APM Database
You can move the APM database from one computer to another or from one platform to another. For example, you can move the APM database from PostgreSQL on Windows or Linux to PostgreSQL on Solaris.
Follow these steps:
  1. Export the business transaction and configuration data from a source database.
  2. Import the business transaction and configuration data into a target database on the new platform or computer.
Alternatively, you can use the dbbackup-postgres and dbrestore-postgres utilities. These utilities let you create a complete backup of a PostgreSQL database and restore the database on a new computer.
Migrate Data from a PostgreSQL Database to an Oracle Database
You can migrate your data from a PostgreSQL to an Oracle database. For example, if your organization is standardized on Oracle, migrate the APM database to Oracle to enable production-level monitoring. Use the CA APM migration utility if you want to move your existing data from a source database in PostgreSQL to a target database in Oracle.
Upgrading from an existing APM database on Oracle to a new APM database on PostgreSQL is not supported.
Note:
 Data migration from PostgreSQL to Oracle using migration.bat migrates the APM Team Center data except historical status change events as the data is large and the migration would take a significant time. After the migration, you will miss historical status changes events on the timeline that have been collected before migration. All other data including topological map, attributes, perspectives, and other settings are migrated.
Do not run the migration utility more than once for any source and target database. If the migration fails, you can restart the program. Verify that any running instance of the migration utility has completed processing before starting a new migration. Running more than one instance of the migration utility generates errors and can prevent the APM database from being migrated successfully.
Follow these steps:
  1. Plan and prepare for the upgrade by reviewing the upgrade scenarios and upgrade paths.
  2. Stop the Enterprise Manager on the source server.
  3. Create a backup copy of all components.
  4. Upgrade the Enterprise Manager and APM database using the interactive or silent Enterprise Manager installer.
  5. Navigate to the 
    <EM_Home>
    /install/migration directory.
  6. Run the migration.bat or migration.sh script as appropriate for your operating environment using the following arguments:
    migration -srcDatabaseName <database_name> -srcDatabaseType <database_type> -srcHost <hostname> -srcPort <port_number>  -srcUser <username> -srcPassword <password> -tgtDatabaseName <datatargetname> -tgtDatabaseType <database_type> -tgtHost <hostname> -tgtPort <port_number> -tgtUser <username> -tgtPassword <password> 
    • srcDatabaseName
      Specifies the name of the source database. If you use the default settings, the database name is cemdb.
    • srcDatabaseType
      Specifies the type of the source database. In this release, the only valid value is postgres.
    • srcHost
      Specifies the host name or IP address of the database server for the source database.
    • srcPort
      Specifies the port number for connecting to the source database. If you used the default settings, the port is 5432.
    • srcUser
      Specifies the database user name for the source database. If you used the default settings, the database user name is admin.
    • srcPassword
      Specifies the password for the database user in the source database.
    • tgtDatabaseName
      Specifies the name or service account identifier of the target database. For an Oracle database, the name for the target database is unique system identifier(SID).
    • tgtDatabaseType
      Specifies the type of the target database. The only valid value is oracle.
    • tgtHost
      Specifies the host name or IP address of the database server for the target database.
    • tgtPort
      Specifies the port number for connecting to the target database. If you used the default settings, the port is 1521.
    • tgtUser
      Specifies the database user name for the target database.
    • tgtPassword
      Specifies the password for the database user in the target database.
    All arguments are case-sensitive and required to run the migration utility.
  7. Check the migration.log file in the logs directory to verify that the schema has been migrated successfully or to troubleshoot problems with the migration. For example, navigate to the 
    <EM_Home>/
    install/migration/logs directory.
Example: Migrate Data from a PostgreSQL Database to an Oracle Database
This example shows how you can migrate data from a PostgreSQL database to an Oracle database on a Linux computer. The PostgreSQL database name is cemdb. The Oracle database has the SID orcl with the schema owner of apmdb.
Use the following command line:
./migration.sh -srcDatabaseName cemdb -srcDatabaseType postgres -srcHost nyc16 -srcPort 5432  -srcUser admin -srcPassword quality -tgtDatabaseName orcl -tgtDatabaseType oracle -tgtHost nyc23 -tgtPort 1521 -tgtUser apmdb -tgtPassword cent3R
Control the Copy and Insertion of Database Objects
The migration.properties file lets you control the copy and insertion of database objects during migration to optimize processing for your environment. The default values for the properties can provide fast and efficient data migration without overloading memory. Depending on the memory constraints of your environment, you can modify the following properties to customize data migration.
Follow these steps:
  1. Navigate to <
    EM_Home
    >/install/migration.
  2. Open the migration.properties file in a text editor.
  3. Modify the properties to the values you want:
    • migration.fetchsize
      Controls the number of records that are retrieved from the source database and held in memory at a time. The value is an integer greater than zero. The default value is 1000. For example:
      migration.fetchsize=1000
    • migration.batchsize
      Controls the number of records that are inserted into the target database in each batch. The value is an integer greater than zero. The default value is 3000. For example:
      migration.batchsize=3000
    • migration.queuesize
      Controls the size of the queue that holds the records to be migrated in a single thread. Records from the source database are placed in this queue to be read for batch insert into the target database. The value is an integer greater than zero. The default value is 10000. For example:
      migration.queuesize=10000
    • migration.parallel.table.number
      Controls the number of parallel table migrations. Each table is migrated using a single thread. The thread retrieves the records for a table from the source database. The retrieval uses the value set for the migration.fetchsize property and places the records in the queue for the value set in the migration.queuesize property. The records are read from the queue and inserted in batches according to the migration.batchsize property value. This property controls the number of threads in use at a time. The value is an integer greater than 0. The default value is 5.
      For example:
      migration.parallel.table.number=5
  4. Save and close the file.
Control Migration Logging
The log4j-migration.properties file provides properties that let you control the logging activities during migration. Use the default values for the properties unless you want to change:
  • The log file name or location
  • The maximum file size
  • The maximum number of backup copies
Follow these steps:
  1. Navigate to <
    EM_Home
    >/install/migration.
  2. Open the migration.properties file in a text editor.
  3. Modify the properties to the values you want:
    • log4j.appender.logfile.File
      Specifies the relative path to the log file for recording information about the operations that are performed during the data migration. For example:
      log4j.appender.logfile.File=logs/migration.log
    • log4j.appender.logfile.MaxFileSize
      Specifies the maximum size of the log file in KB. For example:
      log4j.appender.logfile.MaxFileSize=2048KB
    • log4j.appender.logfile.MaxBackupIndex
      Specifies the maximum number of backup copies to save. For example:
      log4j.appender.logfile.MaxBackupIndex=3
  4. Save and close the file.
Configure the Transaction Event Database Location and Data Aging
The Enterprise Manager stores transaction traces and other event data in the Transaction Event database. The location of the Transaction Event database is configured during the Enterprise Manager installation. Unless you identify a different location during the installation, the database is created in the 
<EM_Home>
/traces directory. The default number of days that Introscope retains a transaction event data is 14. After the installation, you can specify a different database location number of days for data aging.
Note:
 Increasing the length of time that Introscope saves event data increases system overhead and disk storage requirements.
Follow these steps:
  1. Go to the <
    EM_Home
    >/config directory.
  2. Open the IntroscopeEnterpriseManager.properties file in a text editor.
  3. Enter values for the following properties:
    introscope.enterprisemanager.transactionevents.storage.dir
    introscope.enterprisemanager.transactionevents.storage.max.data.age
  4. Save and close the file.
  5. Restart the Enterprise Manager.
Configure the Location of Variance Storage
By default, Introscope stores metric variance information in 
<EM_Home>
/data/variance.db. The Enterprise Manager installer does not offer the option of selecting a different location for variance storage (variance.db). However, you can configure a different location for variance.db using the IntroscopeEnterpriseManager.properties file.
Follow these steps:
  1. Go to the <
    EM_Home
    >/config directory.
  2. Open the IntroscopeEnterpriseManager.properties file in a text editor.
  3. Enter a value for the following property:
    introscope.enterprisemanager.baseline.database
  4. Save and close the file.
  5. Restart the Enterprise Manager.