Quality Center Useful SQL Statements

Below is the link to get the list of KB which talks about useful QC  SQL statements.

http://support.openview.hp.com/selfsolve/document/KM179211

DB server migration

Below are the detailed steps to move the Database to another server.

You will need to:

1.     Update the siteadmin.xml file on the application server

2.     Modify the dbid.xml for all the projects.

Step 1. Updating the siteadmin.xml file on the application server

Note: Remember to make a backup of DB and Repository before any change. Also, move all the DB to the new DB server before perform the following steps.

For JBoss
1.Make a backup copy of the 20qcbin.war file found in the <QC_HOME>/application directory.

Linux/Unix:
mv -i 20qcbin.war sabin_bak.war

2. Move 20qcbin.war from <QC_HOME>/jboss/server/default/deploy to <QC_HOME>/application, and rename it to qcbin.war. This step will ensure all previous modifications are preserved.

Linux/Unix:
cp -i <QC_HOME>/application/jboss/server/default/deploy/20qcbin.war qcbin.war

3. Extract qcbin.war to a temporary directory (Ex. qcbin).

Example:
cd <QC_HOME>\application\20qcbin
jar xf <QC_HOME>\application\20qcbin.war

4. Open the siteadmin.xml file for editing (e.g., this file will be found at <QC_HOME>\application\20qcbin\WEB-INF).

5. Update the siteadmin.xml file using a text editor. Modify the <DbUrl>jdbc:mercury:sqlserver://qcsrv10:1433</DbUrl> with the correct information for the new DB server.

6. Zip up the qcbin.war file.

Example:

cd <QC_HOME>\application\qcbin
jar cvf qcbin.war *

7. Copy the qcbin.war file from <QC_HOME>\application\qcbin to <QC_HOME>\jboss\server\default\deploy.

Linux/Unix:
cp -i qcbin.war <QC_HOME>/jboss/server/default/deploy/qcbin.war

8. Rename the qcbin.war file to 20qcbin.war.

Linux/Unix:
mv -i qcbin.war 20qcbin.war

9. Restart the Quality Center service.

For Weblogic

Please refer to Document ID KM188675 - How to manually deploy or undeploy Quality Center Web application modules on WebLogic server

For WebSphere

Please refer to Document ID KM191173 - How to manually deploy or undeploy Quality Center web application modules on WebSphere server

Note:
Please refer to Document ID 186840 - How to start/stop the deployment of Quality Center on the application server for instructions on how to start/stop the deployment of Quality Center.

Notes:

·          <QC_HOME> is the installation directory of Quality Center.

·         The default location on Windows is C:\Program Files\Mercury Interactive\Quality Center.

·         The default location on Linux/Unix is /opt/Mercury_Interactive/Quality_Center/.

Step 2. Modify the dbid.xml for all the projects

You need to update all the dbid.xml files for all the projects to update the information about the new DB server.

1. Log into the Site Administrator.

2. Remove one of the projects.

3. Search for it dbid.xml file.

4. Edit the file:

* <DB_CONNSTR_FORMAT>jdbc:mercury:sqlserver://qcsrv10:1433</DB_CONNSTR_FORMAT> (Use the same as in the siteadmin.xml file)

* <DBSERVER_NAME>qcsrv10</DBSERVER_NAME>

* <PHYSICAL_DIRECTORY>C:\Quality Center\repository\qc\Domain\ProjectName\</PHYSICAL_DIRECTORY>

5. Save the file.

6. Return to Site Administrator.

7. Right click on the Domain and click on Restore.

8. Search for this dbid.xml file and click on Restore.

9. After the project is restored, click on Upgrade.

10. Activate the project.

11. Try to login to this project.

12. Repeat the steps from 1 to 11 for the rest of the projects.

Quality Center: Export and Import Oracle database

Exporting Oracle Users

When migrating an Oracle project database, you export users from the source database server and import them to the target database server.
To export an Oracle user:
1. Open the exp utility in the Command Prompt dialog box and log in to theserver with administrator permissions.

Choose Start -> Run. The Run dialog box opens.

In the Open box, type cmd and click OK.

In the Command Prompt dialog box, type exp and press Enter.

Specify the user name and password of the user that has administratorpermissions. For example, system. 2. When the Enter array fetch buffer size: 4096 prompt is displayed, pressEnter to select the default size.
3. When the Export file: EXPDAT.DMP prompt is displayed, specify the pathand the name of the target dump file with the .DMP extension. For example,d:exportmy_proj.dmp.
4. When the (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U prompt isdisplayed, press Enter to select the default option (2)U(sers).
5. For the prompts that follow, press Enter to accept the default values.
6. When the User to be exported: (RETURN to quit) prompt is displayed,specify the user name that you want to export and press Enter. If you wantto export several users to the same dump file, perform the same step for eachuser.
7. Press Enter to exit the User to be exported: (RETURN to quit) prompt andstart the exporting process.


Importing Oracle Users

To import an Oracle user you need to create a tablespace for the user and then create a default Oracle user. You can then import the TestDirector Oracle user from the dump file. To import an Oracle user:
1. Create a tablespace with the same name as the one on the source Oracleserver, as follows:

In the DBA Studio, under the Storage root, right-click the Tablespacesfolder and choose Create.

Specify the name and the size of the tablespace. The name must be thesame as the one on the source Oracle server. The size of the tablespacemust be set according to the imported user.

Click Create.

2. Create the Oracle user in the tablespace, as follows:

In the DBA Studio, under Security node, right-click the Users folder andchoose Create.

Specify the user name. It is recommended that you use the same name asthe one used in the source database server.

In Enter Password and Confirm Password, type the password for thisuser.

In the Tablespaces section, select the tablespace you have previouslycreated.

Select a temporary tablespace.

Click the Quota tab. Select the tablespace you have previously created,and select Unlimited.

Select the temporary tablespace you have previously selected, and selectUnlimited.

Click Create. Note: You can also create a user using the following query:
CREATE USER <dbUser> IDENTIFIED BY password DEFAULT TABLESPACE <tableSpace>
    TEMPORARY TABLESPACE <tempTableSpace> QUOTA UNLIMITED ON
<tableSpace>QUOTA UNLIMITED ON <tempTableSpace>
Note:
You should grant the dbUser the following permissions for TestDirector 7.2, 7.6, and 8.0:
GRANT CONNECT, RESOURCE TO <dbUser>;
3. Create a batch file (with the .BAT extension) to invoke the Oracle importutility and the user from a given dump file.
Specify the command as follows:
imp <user name>/<password> file=<source dump file> log=<log file>
IGNORE=Y GRANTS=Y BUFFER=20000 FEEDBACK=1000
fromuser=<original user name> touser=<the new user>
Where:

Syntax	Description
<user name>	The user name with administrator permissions on the Oracle server. For example, system.
<password>	The password of the specified user.
<source dump file>	The location and the source dump file (with the .DMP extension).
<log file>	The location and file name to be used by the imp utility when logging the import process.
<original user name>	The original name of the Oracle user on the source Oracle server.
<the new user>	The name of the new user.

Example:
imp system/manager file=d:/exportmy_proj.dmp log=d:/importmy_proj_imp.log IGNORE=Y GRANTS=Y BUFFER=20000 FEEDBACK=1000 fromuser=td_project_db touser=td_new_project_db
To import several Oracle users, you create new users on the target databaseserver, and specify the source and target users in the fromuser and touserparameters. Make sure that you separate each user by a comma.
For example, suppose you want to import 2 users (td_proj1 and td_proj2) tothe new users (td_new1 and td_new2):
imp system/manager file=d:exportmy_proj.dmp log=d:importmy_proj_imp.logIGNORE=Y GRANTS=Y BUFFER=20000 FEEDBACK=1000 fromuser=td_proj1,td_proj2 touser=td_new1,td_new2
4. Execute the batch file created in the previous step. After the importingprocess is complete, you can view the log file.
Note: If you are only importing one user, then you can run the imp command directly from the Dos-Prompt.