Moving the database instance from one server to another

This topic provides information about how to change the database connection information on the reports server when the Oracle database instance moves from one server to another. You might need to move your instance in case of database crash. 

Before you begin

Back up the tnsnames.ora file on the reports server and on the server where Oracle database instance is running. This file is typically located in the ORACLE_HOME/NETWORK/ADMIN directory.

To update the database connection information

1. Update the tnsnames.ora files on the reports server and the Oracle database instance with the new connection string information for the BMC Server Automation database and the reports warehouse, portal, and ETL Master and Work databases. 
   The following example shows the new connection string information for various databases:
    

   NEWBLDB =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP) (HOST = newbldb.xxx.com) (PORT = 1521)))
   (CONNECT_DATA = (SERVICE_NAME = NEWBLDB)))
   NEWWHSE =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP) (HOST = newwhse.xxx.com) (PORT = 1521)))
   (CONNECT_DATA = (SERVICE_NAME = NEWWHSE)))
   NEWPORTAL =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP) (HOST = newportal.xxx.com) (PORT = 1521)))
   (CONNECT_DATA = (SERVICE_NAME = NEWPORTAL))) 
2. Log on to the reports server as a root user (UNIX) or as an Administrator (Windows).
3. Log on to SQL*Plus as the BSARA_DW user (reports data warehouse user) with the new warehouse connection information by using the following command: sqlplus BSARA_DW@newconnectionstringinfo

4. Identify and drop the current linked server by using the following commands:

   select * from user_db_links;
   DROP DATABASE LINK <linkedServerName>;

    

5. Create a new linked server with the new connection information of the BMC Server Automation database by using the following command:

   CREATE DATABASE LINK <linkName> CONNECT TO <BMCServerAutomationUser> IDENTIFIED BY <password> USING '<TNSNAMEfornewBMCServerAutomationdatabase>';

    

   For example:

    

   CREATE DATABASE LINK DB_SITE1_LINK CONNECT TO BLADELOGIC IDENTIFIED BY password USING 'NEWBLDB'; 

     

6. (Optional) Display the new linked server by using the following command: select * from user_db_links; 
7. Exit from SQL*Plus.
8. From the BDSSAInstallationDirectory/bin directory, run the following command to start the Report Administration Utility: nsh blrptadmin.nsh.

9. On the blrptadmin prompt, run the gencred command with a user that has the GlobalReportAdmins role, generally BLAdmin. For the SRP authentication type, code is 1.

   blrptadmin> gencred
   Enter options for gencred
   Enter GlobalReportAdmin Site: Primary
   Enter GlobalReportAdmin User Name: BLAdmin
   Enter GlobalReportAdmin role name: BLAdmins
   Enter authentication type: 1
   Enter password:
   User credentials file bmcsareports_user.dat generated successfully.
   File is placed under : /root/home/.bmcsareports Make sure only user has permissions to file. 

     

10. (Optional) View the current information for various databases and sites.

    Click here to view the commands required to get information for databases and sites.

    1. View the current connection information for the reports warehouse database by using the get warehouse command, as shown in the following example:
       

       blrptadmin> get warehouse
       Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
       Reports Warehouse Details:
       User Name: BSARA_DW
       Connection String: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oldwhse.xxx.com)(PORT = 1521)))(CONNECT_DATA=(SID = OLDWHSE))) 

    2. View the current connection information for the portal database by using the get portalcontentstore command, as shown in the following example:
        

       blrptadmin> get portalcontentstore
       Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
       Portal Content Store Details:
       User Name: BSARA_PORTAL
       Connection String: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oldportal.xxx.com)(PORT=1521)))(CONNECT_DATA=(SID=OLDPORTAL)))

    3. View the current connection information for the ETL Master and Work repositories by using the get etldb command, as shown in the following example:

       blrptadmin> get etldb
       Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
       ETL Master Repository Details:
       User Name: BSARA_ETL_MASTER 
       Connection String:jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oldwhse.xxx.com)(PORT = 1521)))(CONNECT_DATA=(SID = OLDWHSE)))
       ETL Work Repository Details:
       User Name: BSARA_ETL_WORK
       Connection String: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oldwhse.xxx.com)(PORT = 1521)))(CONNECT_DATA=(SID = OLDWHSE)))

    4. View the current connection information for the BMC Server Automation database by using the get blcoredb command, as shown in the following example:

       blrptadmin> get blcoredb
       Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
       Enter site name: DEV
       Site Details:
       Site: DEV
       Id:1
       Description:Reports Primary site
       Primary is primary site
       Context Details:
       Database Link Name: db_site1_link
       Database Username: BLADELOGIC
       Database Connection String: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oldbldb.xxx.com)(PORT=1521)))(CONNECT_DATA=(SID=OLDBLDB))) 

    5. View all sites by using the list sites command, as shown in the following example:

       blrptadmin> list sites
       Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
       List of Sites:
       Site(Id):
       Primary(1)
       Description:Reports Primary site
       Primary is primary site 

    6. View information about the primary site by using the show site command, as shown in the following example:

       blrptadmin> show site
       Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
       Enter site name: Primary
       Site Details:
       Site: Primary
       Id:1
       Description:Reports Primary site
       Primary is primary site
       Context Details:
       Database Link Name: db_site1_link
       Database Username: BLADELOGIC
       Database Connection String: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oldbldb.xxx.com)(PORT=1521)))(CONNECT_DATA=(SID=OLDBLDB))) 

     

11. Update the connection string in the reports warehouse database by using the set warehouse command, as shown in the following example:

    blrptadmin> set warehouse
    Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
    Enter database user: BSARA_DW
    Enter database password [Optional]:
    Confirm password:
    Enter JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=newwhse.xxx.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=NEWWHSE)))
    Enter TNS Name: NEWWHSE
    Successfully modified Config files.
    Restarting portal and cognos services......
    Datasource ORA75 could not be tested successfully.
    Warehouse configuration set successfully and all services restarted successfully
    Set warehouse successfully. 

    Warning

    Note

    This step will be partially successful. However, this step is required to update the configuration files with the latest reports warehouse details. The step is repeated later in the process at which time it should successfully complete.

12. Update the connection string in the portal database by using the set portalcontentstore command, as shown in the following example:

    blrptadmin> set portalcontentstore
    Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
    Enter database type (sqlserver | oracle):: oracle
    Enter database user: BSARA_PORTAL
    Enter database password [Mandatory]:
    Confirm password:
    Enter JDBC URL: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL = TCP)(HOST=newportal.xxx.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=NEWPORTAL)))
    Changed portal content store database settings,
    Please restart portal to reflect portal content store changes. 

13. Update the connection string in the ETL Master and Work databases by using the set etldb command, as shown in the following example:

    blrptadmin> set etldb
    Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
    Enter database type (sqlserver | oracle):: oracle
    Eneter Master Repository User Name: BSARA_ETL_MASTER
    Eneter Master Repository Password [Optional]:
    Confirm password:
    Enter Master Repository User Name Connection String: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=newwhse.xxx.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=NEWWHSE)))
    Eneter Work Repository User Name: BSARA_ETL_WORK
    Eneter Work Repository Password [Optional]:
    Confirm password:
    Enter Work Repository User Name Connection String:
    jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=newwhse.xxx.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=NEWWHSE)))
    Changed etl settings successfully, Please restart Cognos Service and login Metadata Navigator to reflect changes before executing ETL

14. Update the connection string in the reports warehouse database again by using the set warehouse command, as shown in the following example:

    blrptadmin> set warehouse
    Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
    Enter database user: BSARA_DW
    Enter database password [Optional]:
    Confirm password:
    Enter JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=newwhse.xxx.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=NEWWHSE)))
    Enter TNS Name: NEWWHSE
    Successfully modified Config files.
    Restarting portal and cognos services......
    Tested datasource ORA75 successfully.
    Warehouse configuration set successfully and all services restarted successfully
    Set warehouse successfully. 

15. Update the connection string in the BMC Server Automation database by using the set context command, as shown in the following example:

    blrptadmin> set context
    Accept argument for command: Do you want to use cached credentials as generated by gencred command? (y/n):y
    Enter site name: Primary
    Enter linked server/database link name: db_site1_link
    Enter database type (sqlserver | oracle):: oracle
    Enter database user: BLADELOGIC
    Enter database password [Mandatory]:
    Confirm password: 
    Accept argument for command:JDBC Connection Params For ORACLE JDBC Url (y/n):y
    Enter JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=newbldb.xxx.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=NEWBLDB)))
    Database link db_site1_link connection test successful
    Set context successfully. 
16. Exit the Report Administration Utility.
17. Stop the reports server.
18. Restart the reports server.