HOW TO PERFORM TYPICAL OPERATIONS WITH ORACLE 7 FOR CLEARQUEST Document Number TR 29.3430 Jon G. Gregory, Angel Rivera SCM Center of Competency and e-Business Tools Customer Support IBM Software Solutions Research Triangle Park, North Carolina, USA Copyright (C) 2001, IBM All rights reserved. DISCLAIMER: This technical report is not an official publication from the support groups. The authors are solely responsible for its con- tents. ii ClearQuest: Oracle ABSTRACT This technical report provides a collection of hints and tips for ClearQuest administrators that use the Oracle database management system (DBMS), Version 7. Some of the scenarios are: o Installing and configuring Oracle Server in Solaris. o Installing the Oracle Client in Windows NT. o Starting and stopping the Oracle Server. o Creating a ClearQuest databases under Oracle. o Backup and restore of Oracle databases. o Accessing the ClearQuest tables. o Error messages and how recover from them. ITIRC KEYWORDS o ClearQuest o Oracle ABSTRACT iii iv ClearQuest: Oracle ABOUT THE AUTHORS JON G. GREGORY Mr. Gregory is a Staff Software Engineer working with the SCM Center of Competency in IBM. He joined IBM in 1997 and has pro- vided UNIX support across multiple AIX and OEM platforms since that time. Mr. Gregory has a B.S. in Computer Information Science with a Computer Science minor from Clarion University of Pennsylvania. He is also a M.S. candidate in Information Science with a concen- tration in UNIX operating systems and networking from the Univer- sity of North Carolina at Chapel Hill. ANGEL RIVERA Mr. Rivera is an Advisory Software Engineer and technical lead for the VisualAge TeamConnection and CMVC customer support team. He joined IBM in 1989 and since then has worked in the develop- ment and support of library systems. Mr. Rivera has an M.S. in Electrical Engineering from The Univer- sity of Texas at Austin, and B.S. in Electronic Systems Engi- neering from the Instituto Tecnologico y de Estudios Superiores de Monterrey, Mexico. ABOUT THE AUTHORS v vi ClearQuest: Oracle CONTENTS ABSTRACT . . . . . . . . . . . . . . . . . . . . . . . . . III ITIRC KEYWORDS . . . . . . . . . . . . . . . . . . . . . iii ABOUT THE AUTHORS . . . . . . . . . . . . . . . . . . . . . . V Jon G. Gregory . . . . . . . . . . . . . . . . . . . . . . v Angel Rivera . . . . . . . . . . . . . . . . . . . . . . . v FIGURES . . . . . . . . . . . . . . . . . . . . . . . . . VIII 1.0 INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Disclaimers . . . . . . . . . . . . . . . . . . . . . 1 1.2 How to get the most up to date version of this technical report. . . . . . . . . . . . . . . . . . . . . . 2 1.3 Acknowledgements . . . . . . . . . . . . . . . . . . . 2 2.0 INSTALLING ORACLE IN SOLARIS . . . . . . . . . . . . . . 3 2.1 Pre-installation tasks performed by root . . . . . . . 3 2.2 Pre-installation tasks performed by the Oracle user id 5 2.3 Installation tasks performed by root - part 1 . . . . 6 2.4 Installation tasks performed by the Oracle user id - part 1 . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.5 Installation tasks performed by root - part 2 . . . . 7 2.6 Installation tasks performed by the Oracle user id - part 2 . . . . . . . . . . . . . . . . . . . . . . . . . . 7 2.7 Post-Installation tasks performed by root . . . . . 12 2.8 Post-Installation tasks performed by the Oracle user id . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 2.9 How to find out some of the main Oracle components that are installed . . . . . . . . . . . . . . . . . . . 13 3.0 CONFIGURING THE ORACLE SERVER . . . . . . . . . . . . 15 3.1 SQL*net adds a listener port number in /etc/services 15 4.0 INSTALLING THE ORACLE CLIENT IN WINDOWS NT . . . . . . 17 4.1 ClearQuest clients need the Oracle client . . . . . 17 4.2 Installation of the Oracle Client software in Windows NT . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 4.3 Setup of the Oracle Client . . . . . . . . . . . . . 19 5.0 STARTING AND STOPPING THE ORACLE DBMS . . . . . . . . 21 5.1 State diagram for Oracle DBMSs . . . . . . . . . . . 21 5.2 How to start the Oracle DBMS . . . . . . . . . . . . 21 5.3 How to stop the Oracle DBMS . . . . . . . . . . . . 22 5.4 How to query the status of the Oracle DBMS . . . . . 22 5.5 How to find out the version of the Oracle DBMS . . . 23 6.0 INSTALLING CLEARQUEST . . . . . . . . . . . . . . . . 25 6.1 Installing ClearQuest for the Administrator . . . . 25 Contents vii 7.0 CREATING A DATABASE FOR A CLEARQUEST DATABASE . . . . 27 7.1 Activities to be performed by root . . . . . . . . . 27 7.2 Activities to be performed by the Oracle user id. . 28 7.3 Create two or more separate Oracle database user logins . . . . . . . . . . . . . . . . . . . . . . . . . 28 7.3.1 Create user id's for Schema and User database . . 29 7.4 Activities to be performed by the ClearQuest user ids 29 7.4.1 Profile for a ClearQuest user id . . . . . . . . 30 7.4.2 How to create a basic database . . . . . . . . . 31 7.5 How to find out which table and index spaces are defined . . . . . . . . . . . . . . . . . . . . . . . . . 32 7.6 How to add another file to the system table in Oracle? 33 7.7 How to find out the size of a table or index space . 33 8.0 BACKUP AND RESTORE . . . . . . . . . . . . . . . . . . 35 8.1.1 Backup/restore an Oracle database . . . . . . . . 35 9.0 ACCESSING THE ORACLE DATABASE TABLES . . . . . . . . . 37 9.1 How to use sqlplus to gain access to the database . 37 9.2 How to exit from the database . . . . . . . . . . . 37 9.3 How to use a script to execute SQL commands . . . . 38 9.3.1 Korn shell script: oracleScript . . . . . . . . . 38 9.4 How to use sqlplus with a file with SQL commands . . 39 10.0 COPYRIGHTS, TRADEMARKS AND SERVICE MARKS . . . . . . 41 FIGURES 1. Sample .profile for the Oracle user id . . . . . . . . . 5 2. Stage diagram for Oracle DBMSs . . . . . . . . . . . . 21 viii ClearQuest: Oracle 1.0 INTRODUCTION This technical report provides a collection of hints and tips for ClearQuest administrators that use the Oracle database management system (DBMS), version 7. The chapters in this TR are organized in the expected sequence of utilization of Oracle from a ClearQuest point of view: o Chapter 2.0, "Installing Oracle in Solaris" on page 3 pro- vides some overall recommendations for installing the Oracle Server in Solaris. o Chapter 3.0, "Configuring the Oracle Server" on page 15 describes how to configure the Oracle Server in order to properly create and support ClearQuest databases. o Chapter 4.0, "Installing the Oracle Client in Windows NT" on page 17 provides some overall recommendations for installing the Oracle client in a Windows NT workstation. o Chapter 5.0, "Starting and stopping the Oracle DBMS" on page 21 describes how to start, stop, query the status, verify, and find out the version of the Oracle DBMS. o Chapter 6.0, "Installing ClearQuest" on page 25 describes how to install ClearQuest in a Windows NT machine. Chapter 7.0, "Creating a database for a ClearQuest database" on page 27 describes how to create a ClearQuest database under Oracle. o Chapter 8.0, "Backup and restore" on page 35 describes how to perform a backup of the database and how to restore it. o Chapter 9.0, "Accessing the Oracle database tables" on page 37 provides procedures for accessing the database tables and views used by ClearQuest. 1.1 DISCLAIMERS To avoid misunderstandings with the purpose of this technical report and to better understand its scope, the following dis- claimers are in order: o This technical report is not an official publication from the support groups. The authors are solely responsible for its contents. o This technical report was prepared when working with ClearQuest 2 with Oracle 7.3.4 in Solaris 2.7. Therefore, if you have a different version of the mentioned software, then Introduction 1 you may expect some differences in the information or in the procedures described in this technical report. o It is the intention of this technical report to provide recommendations and guidelines that can be helpful to ClearQuest administrators when using ClearQuest databases stored in Oracle. In some cases, the procedures will not be exhaustive, and will just show the overall sequence that has worked before, which might be different in your case. o Real values that were used in our setup will be used in this technical report. Thus, you will need to customize the com- mands that you issue to reflect the values that are mean- ingful to your setup. o It is assumed that the reader has knowledge of ClearQuest, Oracle and the appropriate operating system. This technical report is not a substitute to the information provided by Oracle, ClearQuest and the appropriate operating system. Please refer to the appropriate documentation pro- vided with the corresponding software. 1.2 HOW TO GET THE MOST UP TO DATE VERSION OF THIS TECHNICAL REPORT. The most up to date version of this technical report can be obtained from the IBM VisualAge TeamConnection ftp site at URL: ftp://ftp.software.ibm.com/ps/products/teamconnection/papers/trcq-ora.pdf 1.3 ACKNOWLEDGEMENTS Many of the questions and answers that are compiled in this tech- nical report were obtained from co-workers. I want to thank in particular the following co-workers: o Bill Anderson, IBM RTP, North Carolina, USA. o Edna Wong Kyu, IBM RTP, North Carolina, USA. o Keith Purcell, IBM RTP, North Carolina, USA. o Lee Perlov, IBM RTP, North Carolina, USA. 2 ClearQuest: Oracle 2.0 INSTALLING ORACLE IN SOLARIS This chapter provides some overall recommendations when installing Oracle. You need to consult the following manual (or its equivalent for your operating system) for the details on the requirements and installation steps: Oracle 7, Release 7.3 for Sun SPARC Solaris 2.x Installation Guide This manual will be referenced in this document as the "Oracle Installation Guide". It is assumed that this is the first installation of Oracle in your server. That is, this TR does not explain how to perform advanced installation and/or upgrade tasks. It is highly recommended that you open 2 windows, one for the tasks to be performed by root and the other for the tasks to be performed by the Oracle user id. This will help you a lot, because you have to change the userid quite frequently. 2.1 PRE-INSTALLATION TASKS PERFORMED BY ROOT These pre-installation instructions are focused to Solaris. Please make the necessary adjustments for AIX and HP-UX. 1. Login as root. 2. (HP-UX and Solaris) Configure the UNIX Kernel for Oracle by modifying /etc/system file with the minimum recommended values as shown in the Oracle Installation Guide. This step is only applicable to HP-UX and Solaris (not for AIX). 3. Change the umask to ensure that group and other have read and execute permissions, but not write permission: umask 022 4. Create Mount Points for Oracle. Oracle recommends distrib- uting data across three mount points. However, for our pur- poses we have decided to use only one mount point. Installing Oracle in Solaris 3 We created a separate file system and called it 'u01' and used /opt/u01 as the mount point. NOTE: If more than one version of Oracle is to be installed on a box, then create a separate file system, such as 'u02' and use this as the mount point. 5. Create the ORACLE_BASE directory: mkdir -p /opt/u01/app/oracle Do not worry yet for the ownership of these directories. 6. Create a new group, such as "dba". During installation, this group is assigned Oracle DBA rights. This group is hard-coded in the file $ORACLE_HOME/rdmbs/lib/config.c. Thus, it is highly recom- mended to use the name "dba" for the group. We are not going to create the optional group "oper" because it is not needed for using ClearQuest. 7. Create a new user id, such as "oracle" that belongs to the "dba" group. This will be known as "the Oracle user id". The home directory for this user id will be $ORACLE_BASE: /opt/u01/app/oracle 8. Create a local bin directory; the Oracle default is '/usr/lbin'. 9. Create the directory structure for Oracle 7.3.4, which is based on the Mount Point for Oracle; for example: mkdir -p /opt/u01/app/oracle/product/7.3.4 10. Now you can change the ownership of the Oracle directory structure: chown -R oracle:dba /opt/u01 11. Proceed with 2.2, "Pre-installation tasks performed by the Oracle user id" on page 5. 4 ClearQuest: Oracle 2.2 PRE-INSTALLATION TASKS PERFORMED BY THE ORACLE USER ID 1. Login as the Oracle user id. 2. Change the umask to ensure that group and other have read and execute permissions, but not write permission: umask 022 3. Set environment variables in the .profile as shown in Figure 1. #!/usr/bin/ksh # Set up environment variables for Oracle export ORACLE_HOME=/opt/u01/app/oracle/product/7.3.4 export ORACLE_SID=sid export ORACLE_TERM=vt100 export OBK_HOME=$ORACLE_HOME/obackup export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/lib export ORACLE_BASE=/opt/u01/app/oracle export ORACLE_DOC=$ORACLE_BASE/doc export ORACLE_PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin export ORACLE_PATH=$ORACLE_PATH:/opt/bin:/bin: export ORACLE_PATH=$ORACLE_PATH:/usr/bin:/usr/ccs/bin export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb export PATH=$PATH:$HOME/bin:/usr/bin/X11: export PATH=$PATH:/sbin:. export PATH=$PATH:.&colon$ORACLE_HOME/bin: export PATH=$PATH:$ORACLE_HOME/rdbms/install/rdbms: export TERM=vt100 export DISPLAY=hostName:0 # where hostName is a valid one set -o vi # To allow vi commands for line command retrieval # end of file Figure 1. Sample .profile for the Oracle user id 4. Logout and login again to refresh the environment for the Oracle user id. 5. Proceed with 2.3, "Installation tasks performed by root - part 1" on page 6. Installing Oracle in Solaris 5 2.3 INSTALLATION TASKS PERFORMED BY ROOT - PART 1 1. Login as root. 2. Mount the Product Installation CD-ROM. Place the product installation CD-ROM in the CD-ROM drive and mount the CD-ROM drive on the CD-ROM mount point directory. If the CD-ROM mount point directory does not exist, then you need to create it, such as /cdrom: mkdir /cdrom 3. The following tasks are ONLY for AIX and HP-UX. Create the Oracle link directory and set the permissions to make it accessible to all users: mkdir /opt/olink chmod 777 /opt/olink This directory requires up to 80 MB of free space and may be deleted after completing your installation. 4. Proceed with 2.4, "Installation tasks performed by the Oracle user id - part 1." 2.4 INSTALLATION TASKS PERFORMED BY THE ORACLE USER ID - PART 1 The tasks in this section are ONLY for AIX and HP-UX. If you are installing in Solaris, then skip this section and con- tinue with 2.6, "Installation tasks performed by the Oracle user id - part 2" on page 7. 1. Login as the Oracle user id. 2. Change to the orainst directory on the CD-ROM: cd /cdrom/orainst 3. Run the start.sh script to create the link names: ./start.sh 4. When prompted, specify the Oracle link directory, such as: /opt/olink 5. Proceed with 2.5, "Installation tasks performed by root - part 2" on page 7. 6 ClearQuest: Oracle 2.5 INSTALLATION TASKS PERFORMED BY ROOT - PART 2 The tasks in this section are ONLY for AIX and HP-UX. If you are installing in Solaris, then skip this section and con- tinue with 2.6, "Installation tasks performed by the Oracle user id - part 2" on page 7. 1. Login as root. 2. Change to the Oracle link directory: cd /opt/olink 3. Run the rootpre.sh script: ./rootpre.sh After the completion of the script, you will see a comment about the stream facility. As far as our systems is con- cerned, we did not have to change anything about the stream facility. 4. Proceed with 2.6, "Installation tasks performed by the Oracle user id - part 2." 2.6 INSTALLATION TASKS PERFORMED BY THE ORACLE USER ID - PART 2 1. Login as the Oracle user id. 2. Change to the appropriate directory: o For AIX and HP-UX: Change to the Oracle link directory: cd /opt/olink o For Solaris Change to: cd /cdrom/oracle734/orainst 3. Run the Oracle installer: o Motif mode (this is the one that we used): Ensure that your DISPLAY and TERM variables are properly set. Then execute: ./orainst /m Installing Oracle in Solaris 7 o Character mode: ./orainst 4. You will see the logo for the Oracle Unix Installer program. Then you will see the dialog window "Install Type". Select the default option: Default Install Click on OK. 5. You will see the dialog window "Installation Activity Choice". Select the default option: Install, Upgrade or De-install Software Click on OK. 6. You will see the dialog window "Installation Options". Select the default option: Install New Product - Create DB Objects Click on OK. 7. You will see the dialog window "Environment Variables". Ensure that you have the proper values for: ORACLE_BASE=/opt/u01/app/oracle ORACLE_HOME=/opt/u01/app/oracle/product/7.3.4 ORACLE_SID=sid Click on OK. 8. For AIX and HP-UX only: You will see the dialog window "Pre-Installation OS Prepara- tion" that will ask you if rootpre.sh was executed as root; this step was done already. Click on Yes. 9. You will see the dialog window "Relink All Executables?" Accept the default for NOT relinking the executables. Click on No. 8 ClearQuest: Oracle 10. You will see a dialog window with a title of a file name that ends with "defaults". Notice the following: o Install Log is: $ORACLE_HOME/orainst/install.log o Sql Log is: $ORACLE_HOME/orainst/sql.log o OS Log is: $ORACLE_HOME/orainst/os.log o Make Log is: $ORACLE_HOME/orainst/make.log o Language is: American/English Click on OK. 11. You will see the window "Software Asset Manager". Select to install the following products from the left column: o For AIX and HP-UX: "Products available on /opt/olink" o For Solaris: "Products available on /cdrom" You need to select MANY products at once. We included also some components for our development activ- ities (using the C language). This dialog window is not too intuitive, because if you select only one component and click on Install, it will go ahead and try to install it. Thus, you need to select ALL the following items in order to install them at the same time. Installing Oracle in Solaris 9 Oracle Names Oracle On-Line Text Viewer Oracle Server Manager (Motif) Oracle Unix Installer Oracle7 Distributed Database Option Oracle7 Server (RDBMS) Oracle7 XA Library PL/SQL V2 ProC* SQL*Module for C SQL*Net (V2) SQL*Plus TCP/IP Protocol Adapter Click on Install. 12. You will see the window "DBA Group". Accept the value of "dba". Click on OK. 13. You will see the window "OSOPER Group" Accept the value of "dba". Click on OK. 14. You will see the window "Create DB Objects: Storage Type". Accept the default: Filesystem-Based Database Click on OK. 15. You will see the window "Create DB Objects (F/S): Control File Distribution". In our case we are using a single mount point, instead of three mount points. Click on No. 16. You will see the window "Create DB Objects (F/S): Mount Point Locator". Enter the value for $ORACLE_BASE which in our case is: /opt/u01/app/oracle Click on OK. 17. You will see the window "Character Set". Accept the default: US7ASCII 10 ClearQuest: Oracle Click on OK. 18. You will see the window "SYSTEM Password". Enter a password, such as: system Click on OK. Then confirm it again. 19. You will see the window "SYS Password". Enter a password, such as: system Click on OK. Then confirm it again. 20. You will see the window "dba Password". Accept the default: No Click on No. 21. You will see the window "Configure MTS and start a SQL*Net listener called 'LISTENER'?". Change to: Yes Click on Yes 22. You will see the window "Create DB Objects (F/S): Control File Locator". Accept the default: Yes Click on Yes. 23. You will see the windows "DB Defaults". Accept the default. Click on OK. 24. You will see the window "Default DB". Accept the default: Yes Click on Yes. 25. You will see the window "Help Facility". Accept the default: Yes Installing Oracle in Solaris 11 Click on Yes. 26. You will see the window "Demo Tables". Accept the default: Yes Click on Yes. 27. At this moment, the actual installation of the different Oracle components will begin. Click on OK to the several dialog windows that report the completion of the installation of some components. 28. You may see the window titled "Client Shared Library" to ask you if you would like to regenerate the shared version of some Oracle libraries. The default is no and that is what we chose. In case that you can to regenerate them later, you will need to do: make -f clntsh.mk libclntsh 29. When the installation is complete, you will see the window "Software Asset Manager". If you are curious you can browse the list of installed com- ponents (right column: "Products installed on ...". Click on Exit. 30. Change the directory to $ORACLE_HOME: cd $ORACLE_HOME 31. Proceed with 2.7, "Post-Installation tasks performed by root." 2.7 POST-INSTALLATION TASKS PERFORMED BY ROOT 1. Login as root. 2. Change to the directory orainst in $ORACLE_HOME, for example: cd /opt/u01/app/oracle/product/7.3.4/orainst 3. Run the root.sh script to set the necessary file permissions for Oracle products and to perform any other required setup activites: ./root.sh 12 ClearQuest: Oracle Answer the questions from this script: o Ensure that the values for ORACLE_OWNER, ORACLE_HOME and ORACLE_SID are correct. Answer "Y" (Yes). o Enter the full path name of the local bin directory: /usr/lbin o Answer 'Y" (Yes) to the confusing question: ORACLE_HOME does not match the home directory for oracle. Okay to continue? 4. You may need to increase the ulimit for the Oracle user id. See the Oracle documentation. 5. For AIX and HP-UX only: Remove the temporary Oracle link directory: rm -fr /u01/olink 6. Proceed with 2.8, "Post-Installation tasks performed by the Oracle user id." 2.8 POST-INSTALLATION TASKS PERFORMED BY THE ORACLE USER ID 1. Login as the Oracle user id. 2. For a single-instance machine, add the following 2 lines in the .profile: export ORAENV_ASK=NO . /usr/lbin/oraenv 2.9 HOW TO FIND OUT SOME OF THE MAIN ORACLE COMPONENTS THAT ARE INSTALLED One way to find out what are some of the main Oracle components that are installed is by executing the command: grep -i server $ORACLE_HOME/unix.prd A sample output is shown below. It is truncated, in order to show only 70 characters in each line): 500 rdbms root "rdbms" "7.3.4.0.0" "Oracle7 Server (RDBMS)" 6510 svrmgrm root "svrmgr" "2.3.4.0.0" "Oracle Server Manager" Installing Oracle in Solaris 13 14 ClearQuest: Oracle 3.0 CONFIGURING THE ORACLE SERVER This chapter describes how to configure Oracle in order to prop- erly support ClearQuest databases. This chapter describes a very important configuration (a listener daemon) that needs to be done in order to allow the Oracle server to allow remote connections. 3.1 SQL*NET ADDS A LISTENER PORT NUMBER IN /ETC/SERVICES The installation of SQL*Net in the Unix server will add automat- ically an entry in /etc/services for the port number of the lis- tener daemon, which is used to allow remote connectivity from Oracle clients. listenor 1521/tcp # Listener port for Oracle Configuring the Oracle Server 15 16 ClearQuest: Oracle 4.0 INSTALLING THE ORACLE CLIENT IN WINDOWS NT This chapter provides some overall recommendations when installing the Oracle Client in Windows NT. You need to consult the following manual for the details on the requirements and installation steps: Oracle Client Software Version 7.3.4.0.0 for Windows NT and Windows 95 Installation Instructions This manual will be referenced in this document as the "Oracle Installation Guide for Windows". It is assumed that this is the first installation of the Oracle Client in your workstation. That is, this TR does not explain how to perform advanced installation and/or upgrade tasks. According to the ClearQuest documentation, when using an Oracle server, each client, including the ClearQuest Designer, requires the following software: Oracle Client Software Version 7.3.4 (install SQL*Net 2.3.4 with TCP/IP Protocol Adapter 2.3.4). 4.1 CLEARQUEST CLIENTS NEED THE ORACLE CLIENT The ClearQuest administrator and all the ClearQuest end-users must install the Oracle Client software on their local machines in order to install ClearQuest. The Oracle 7 Client software contains networking drivers that ClearQuest needs to interact with the Oracle databases. Before using ClearQuest, all users must use the SQL*Net Easy Con- figuration program to configure their computers to access Oracle databases. Important: If your Oracle for Windows menu does not list the SQL*Net Easy Configuration, you do not have the correct network configuration drivers installed. Once you have the correct drivers, return here and proceed with the following steps. Installing the Oracle Client in Windows NT 17 4.2 INSTALLATION OF THE ORACLE CLIENT SOFTWARE IN WINDOWS NT 1. Login into a Windows NT as a user that belongs to the Admin- istrators group, such as "Administrator". Insert the CD-ROM from the CD jewel case titled: Oracle Client Software Version 7.3.4.0.0 for Windows NT and Windows 95 2. If Autoplay is enabled, then the following dialog will be shown: 3. If Autoplay is not enabled, then execute "setup.exe" to run the "Oracle Installer" program. 4. You will see a window called "Oracle7 Client for Windows NT" which has the following question: Do you want to install Oracle7 Client software for Windows NT? Answer: Yes 5. The Oracle Installer will start and will ask you for a default language. Accept the default of English 6. You will see the window called "Oracle Installation Set- tings": Specify the location for the Oracle Home: D:\orant 7. We chose to allow the Oracle installer to perform the neces- sary modifications to the Path, from the window titled "Path Modifications", by answering "Yes". 8. Accept the default to install the Oracle7 Client 9. Accept the default to have the primary function: Application User 10. Accept the default to use CD-ROM for the documentation. Now the files will be copied from the CD-ROM and installed in your computer. Wait for the window named "Installation Complete". 11. The following Oracle components will be installed (showing only the main ones): o 32-bit Administrator o Oracle Installer 18 ClearQuest: Oracle o SQL Net Easy Configuration o SQL Plus 3.3 o Oracle Protocol Adapters o Oracle Objects for OLE o Oracle ODBCS Driver o Oracle7 Client Documentation o Oracle7 Client Release Notes 12. Reboot your system. 13. Proceed with 4.3, "Setup of the Oracle Client." 4.3 SETUP OF THE ORACLE CLIENT 1. From the Start -> Programs menu, click "Oracle for Windows NT" -> " SQL*Net Easy Configuration". 2. Select "View Configuration Information" to see if your Oracle database administrator configured a Database Alias for you. 3. Add a new database alias by selecting "Add Database Alias". Click OK. 4. Type the Database Alias, which is also known as the SQL*Net Database Alias, and which is a database name that the Oracle database administrator set up previously. In our example, the alias to use is: CQAlias Notice that this is a mixed case name, and you need to enter it exactly as shown. Click OK. 5. Accept the default network protocol. TCP/IP Click OK. 6. Enter the host name and the database instance which will have the ClearQuest data. a. Enter the TCP/IP host name. In our example, the host name is: oem-sn16.raleigh.ibm.com b. Enter the Database Instance (the Oracle system ID or SID). In our example, the SID is: afeb c. Click OK. 7. Review the setup and ensure that is correct; then click "Yes". 8. After configuring your database, the initial opening screen is displayed again so that you can configure additional aliases. Installing the Oracle Client in Windows NT 19 9. Select "Exit the SQL*Net Easy Configuration application". Click OK. The main effect of the above procedure was to add an entry in the file: D:\ORANT\NETWORK\ADMIN\TNSNAMES.ORA The entry is: CQAlias.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world) (PROTOCOL = TCP) (Host = oem-sn16.raleigh.ibm.co) (Port = 1521) ) (ADDRESS = (COMMUNITY = tcp.world) (PROTOCOL = TCP) (Host = oem-sn16.raleigh.ibm.co) (Port = 1526) ) ) (CONNECT_DATA = (SID = afeb) ) ) 20 ClearQuest: Oracle 5.0 STARTING AND STOPPING THE ORACLE DBMS This chapter describes how to start, stop, query the status, verify, and find out the version of the Oracle DBMS. Because the utilities to start and stop the Oracle DBMS are in a rather deep directory and this directory is not explicitly recom- mended in the Oracle manuals as a required change in the .profile of the Oracle user id, we recommended to do the following in order to start and stop the Oracle DBMS in an easier manner: export PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/rdbms/install/rdbms:$PATH 5.1 STATE DIAGRAM FOR ORACLE DBMSS The figure Figure 2 shows the main states in which an Oracle DBMS can be at a given point in time, and the commands that are used to trigger the state transitions. +--------------+ shutdown.sh +--------------+ | |--------->---------| | | Off-line | | On-line | | |---------<---------| | +--------------+ startup.sh +--------------+ Figure 2. Stage diagram for Oracle DBMSs NOTES: 1. You need to be the Oracle user id in order to issue the com- mands for starting or stopping the DBMS. 5.2 HOW TO START THE ORACLE DBMS To start the Oracle database management system do the following: 1. Login into the Oracle user id. 2. Change to the appropriate directory: cd $ORACLE_HOME/rdbms/install/rdbms 3. Start the Oracle DBMS: Starting and stopping the Oracle DBMS 21 ./startup.sh Wait for the end of the startup process: SVRMGR> Server Manager complete. The state of the DBMS will move from off-line to on-line. 5.3 HOW TO STOP THE ORACLE DBMS To stop the Oracle database management system do the following: 1. Login into the Oracle user id. 2. Change to the appropriate directory: cd $ORACLE_HOME/rdbms/install/rdbms 3. Stop the Oracle DBMS: ./shutdown.sh Wait for the end of the shutdown process: SVRMGR> Server Manager complete. The state of the DBMS will move from on-line to off-line. 5.4 HOW TO QUERY THE STATUS OF THE ORACLE DBMS One way to find out if the Oracle DBMS is active, is by using the following command: ps -ef | grep -i ora You should see some processes like: oracle 11274 15834 2 16:01:32 pts/2 0:00 grep sid oracle 14072 1 0 13:29:27 - 0:00 ora_pmon_sid oracle 14332 1 0 13:29:27 - 0:06 ora_dbwr_sid oracle 15108 1 0 13:29:27 - 0:00 ora_smon_sid oracle 16128 1 0 13:29:27 - 0:12 ora_lgwr_sid oracle 16904 1 0 13:29:28 - 0:00 ora_reco_sid If you do not see any processes related to Oracle, then the Oracle DBMS is not active. Where the suffix used in the process names is the Oracle SID; in this case is "sid". 22 ClearQuest: Oracle 5.5 HOW TO FIND OUT THE VERSION OF THE ORACLE DBMS To find out the version of the Oracle DBMS, perform the fol- lowing: 1. Login into the Oracle user id. 2. Start or stop the Oracle DBMS, and in the first informational messages you will see the version for "Oracle7 Server". The output will have something like this: Oracle7 Server Release 7.3.4.0.0 - Production Starting and stopping the Oracle DBMS 23 24 ClearQuest: Oracle 6.0 INSTALLING CLEARQUEST This chapter describes how to install ClearQuest in a Windows NT machine. 6.1 INSTALLING CLEARQUEST FOR THE ADMINISTRATOR Before installing ClearQuest on client machines you must install it on the administrators machine, set up licensing, and set up your schema repository and user databases. If you are using an Oracle database, install the Oracle client software before installing ClearQuest. To install ClearQuest for administrators: 1. Insert the ClearQuest CD-ROM. 2. If the setup program does not run automatically, navigate to the top level directory of the CD-ROM and double-click on: setup.exe 3. The Rational Software Setup wizard is displayed. Click Next. 4. Select the product to install: Rational ClearQuest 2.0 Click Next. 5. Accept the agreement. Click Yes and click Next. 6. Accept the default of a Typical installation. If you want, modify the target directory. Click Next. 7. Click Next in the screen to Update Shared Components. 8. Choose the Administrator installation. Click Next. 9. Confirm the installation by clicking Next. At this point, the product will be installed in your system. 10. Launch the Licence Administrator to enter your ClearQuest licenses. Follow the instructions on your License Certif- icate and in the online Help for the License Key Adminis- trator. Installing ClearQuest 25 26 ClearQuest: Oracle 7.0 CREATING A DATABASE FOR A CLEARQUEST DATABASE This chapter describes how to create the databases under Oracle that will be used for ClearQuest. For more information see the manual "Installing Rational ClearQuest 2.0". This chapter also explains how to find out which table (see 7.5, "How to find out which table and index spaces are defined" on page 32) and index spaces have been defined for the Oracle data- base, and how to expand them (see 7.6, "How to add another file to the system table in Oracle?" on page 33). 7.1 ACTIVITIES TO BE PERFORMED BY ROOT 1. Login as root. 2. Create the following user ids to be used for ClearQuest data- bases, which should belong to the same group as the Oracle user id (dba): o One for the ClearQuest schema repository (such as cqschema). o One for the ClearQuest user database (such as cquserdb). 3. Create directories where to store the table and index spaces for the ClearQuests databases. You have to choose a place where you have enough file system space for the databases. umask 022 mkdir -p /export/home/cq-space/cqschema mkdir -p /export/home/cq-space/cquserdb chown -R oracle:dba /export/home/cq-space There are not explicit manual activities that are needed to register these databases. 4. Logout as root. Creating a database for a ClearQuest database 27 7.2 ACTIVITIES TO BE PERFORMED BY THE ORACLE USER ID. It is very important to avoid using the default system space (which is used by Oracle itself) when creating the user data- bases. These user databases need to have their own table and index database spaces, and they can be created by performing the following steps. 1. Login as the Oracle user id. 2. Create the table and index spaces where to store the regular database for cqschema: a. Issue: sqlplus system/system b. Specify the table space (in this case, with a starting size of 10 MB): create tablespace cqschematblsp datafile '/export/home/cq-space/cqschema/data01.dbf' size 10M default storage (initial 10k next 10k pctincrease 50 minextents 1 maxextents 120) ; 3. Create the table space where to store the regular database for cquserdb: a. Issue: sqlplus system/system b. Specify the table space (in this case, with a starting size of 10 MB): create tablespace cquserdbtblsp datafile '/export/home/cq-space/cquserdb/data01.dbf' size 10M default storage (initial 10k next 10k pctincrease 50 minextents 1 maxextents 120) ; 4. Logout as the Oracle user id. 7.3 CREATE TWO OR MORE SEPARATE ORACLE DATABASE USER LOGINS Create two or more separate Oracle database user logins: o One for the ClearQuest schema repository. o One for each ClearQuest user database. 28 ClearQuest: Oracle Grant each login "Connect" and "Resource" roles only. Note: If you plan to use the Oracle Context Option you should also grant the CTXAPP role. Associate each user with a unique default tablespace that is sep- arate from all other users. NOTE: The users of the database can share temporary tablespaces, but these tablespaces must be separate from the system tablespace. 7.3.1 Create user id's for Schema and User database ____________________________________________________ After tablespaces have been created, execute the following com- mands at the SQL prompt: 1. Schema user creation SQL>create user identified by 2 default tablespace cqschematblsp 3 temporary tablespace temp 4 quota unlimited on cqschematblsp; 2. User Database user creation SQL>create user identified by 2 default tablespace cquserdbtblsp 3 temporary tablespace temp 4 quota unlimited on cquserdbtblsp; 3. After both users are created, grant connect and resource to the ID's: SQL>grant connect,resource to ; Perform the above statement for the schema user and all user database users. 7.4 ACTIVITIES TO BE PERFORMED BY THE CLEARQUEST USER IDS 1. Login as one of the ClearQuest user ids (such as cqschema and cquserdb). 2. Modify the .profile. See 7.4.1, "Profile for a ClearQuest user id" on page 30. 3. Create an Oracle database. See 7.4.2, "How to create a basic database" on page 31. Creating a database for a ClearQuest database 29 7.4.1 Profile for a ClearQuest user id _______________________________________ Ensure that all the environment variables related to Oracle are defined (in this case the entries for the table and index spaces use cqschema; you need to reflect the actual ones for cquserdb too). #!/usr/bin/ksh ## ## Profile for a ClearQuest user id ## stty istrip ## ## Oracle setup ## ## NOTE: DO NOT USE A LINK for ORACLE_HOME ## You MUST use the original directory path. export ORACLE_HOME=/opt/u01/app/oracle/product/7.3.4 export ORACLE_DBA=system/system export DATABASE_PASS=password # Password for the database itself ## See /etc/oratab to find out the Oracle SID (System ID) export ORACLE_SID=sid ## Specify the table and index spaces to use for the database export ORACLE_TBLSP=cqschematblsp export ORACLE_NDXSP=cqschemandxsp ## ## Modification of PATH ## export PATH=/usr/bin:/usr/ucb:/etc:. export PATH=$PATH:$ORACLE_HOME/bin:/usr/lbin:$HOME/bin:. ## ## Additional variables ## ## uncomment the following lines for Solaris export SITE=`/usr/ucb/hostname` ## uncomment the following lines for AIX 4 # export SITE=`hostname -s` ## uncomment the following lines for HP-UX # export SITE=`/usr/bin/hostname` 30 ClearQuest: Oracle ## ## Other variables that are optional, but make the system easier to use. ## export PS1='$LOGNAME@$SITE:$PWD (!) ' export FCEDIT='/usr/bin/vi' export EDITOR='/usr/bin/vi' export SHELL='/usr/bin/ksh' set -o vi # use 'vi' as command editor (only for ksh) ## ## Other variables related to the Terminal type (optional) ## They might be different in your site. ## ## uncomment the following for Solaris: stty erase '5H' stty intr '5C' ## uncomment the following for HP-UX: # stty erase "5H" kill "5U" intr "5C" eof "5D" # stty hupcl ixon ixoff The value for the ORACLE_TBLSP and ORACLE_NDXSP environment vari- ables will be used to create the database for the ClearQuest database. After editing the profile, exit your session and login again in order to have a clean environment. 7.4.2 How to create a basic database _____________________________________ ClearQuest needs 2 databases, one for the schema and another for the user database. The ClearQuest administrator needs to create these 2 databases manually to prepare the way for the rest of the ClearQuest installation. Each database needs to be created in its own user id. For example, cqschema for the Schema Database and cquserdb for the User Database. It is necessary to create a basic database in Oracle for each of these user ids, as follows: 1. Login to either cqshema or cquserdb (for example). 2. Ensure that the .profile has the environment variables men- tioned in 7.4.1, "Profile for a ClearQuest user id" on page 30. Creating a database for a ClearQuest database 31 3. Ensure that the table space and index space are appropriable created by the Oracle user id. See 7.2, "Activities to be performed by the Oracle user id." on page 28. 4. Start sqlplus: sqlplus $ORACLE_DBA 5. Create the database named "cqschema" (from $LOGNAME) with a password of "password" from ($DATABASE_PASS). SQL> grant connect,resource to cqschema identified by password 2> ; The answer should say: Grant succeeded. 6. 7.5 HOW TO FIND OUT WHICH TABLE AND INDEX SPACES ARE DEFINED 1. Login into the Oracle user id. 2. sqlplus system/system 3. To list the names and default storage parameters of all tablespaces: select tablespace_name "TABLESPACE", initial_extent "INITIAL_EXT", next_extent "NEXT_EXT", min_extents "MIN_EXT", max_extents "MAX_EXT", pct_increase from sys.dba_tablespaces; 4. To list the names, sizes and associated tablespaces: select file_name, bytes, tablespace_name from sys.dba_data_files; quit; An example of the output is shown below: 32 ClearQuest: Oracle FILE_NAME ------------------------------------------------------------------------ BYTES TABLESPACE_NAME ---------- ------------------------------ /export/home/ora722/dbs/systsid.dbf 41943040 SYSTEM /export/home/ora722/dbs/rbssid.dbf 8388608 RBS /export/home/ora722/dbs/toolsid.dbf 15728640 TOOLS 7.6 HOW TO ADD ANOTHER FILE TO THE SYSTEM TABLE IN ORACLE? 1. Login into the Oracle user id. 2. sqlplus system/system 3. Perform the following to expand the database space by 20 MB: alter tablespace spaceName add datafile '/xxx/yyy.dbf' size 20M; Where "spaceName" is the name of the tablespace that you want to expand, and the argument for datafile (/xxx/yyy.dbf) is the full path of the file. 7.7 HOW TO FIND OUT THE SIZE OF A TABLE OR INDEX SPACE 1. Login into the Oracle user id. 2. sqlplus system/system 3. Enter: SQL> select tablespace_name, file_id, 2 COUNT(*) "PIECES", 3 MAX(blocks) "MAXIMUM", 4 MIN(blocks) "MINIMUM", 5 AVG(blocks) "AVERAGE", 6 SUM(blocks) "TOTAL" 7 FROM sys.dba_free_space 8 WHERE tablespace_name = 'SYSTEM' 9 GROUP by tablespace_name, file_id; 4. An example of the output is shown below: Creating a database for a ClearQuest database 33 TABLESPACE_NAME FILE_ID PIECES MAXIMUM MINIM ------------------------------ ---------- ---------- ---------- -------- AVERAGE TOTAL ---------- ---------- SYSTEM 1 2 15271 7675.5 15351 The free space is shown in blocks of 2048 bytes each. 34 ClearQuest: Oracle 8.0 BACKUP AND RESTORE This chapter describes how to perform a backup of the database and how to restore it. 8.1.1 Backup/restore an Oracle database ________________________________________ 8.1.1.1 Backup of Oracle databases 1. Login as the ClearQuest administrator ($LOGNAME): 2. Stop the ClearQuest server. 3. Create a directory where to store the backup files: $ $HOME/backup $ chmod 777 $HOME/backup 4. Use the EXP command to export the database to a file prior to backing up the HOME directory for the server. For example (in one single line) $ORACLE_HOME/bin/exp $ORACLE_DBA buffer=40000 \ file=$HOME/backup/oracle.dmp grants=n indexes=y rows=y constraints=n \ compress=y full=n record=n owner=$LOGNAME 5. Wait for the backup to be completed: Export terminated successfully without warnings 8.1.1.2 Restore of Oracle databases 1. If you are restoring into the same user id, then drop the existing database: $ rmdb 2. Create an Oracle userid with the same name as your ClearQuest server. This userid has the password kept in the ORACLE_PASS environment variable. For example: $ORACLE_HOME/bin/sqlplus $ORACLE_DBA GRANT CONNECT, RESOURCE TO databaseName IDENTIFIED BY oracle_pass; Backup and restore 35 Where you need to provide the actual values for databaseName (from $LOGNAME) and oracle_pass (from $ORACLE_PASS). 3. If you have tables stored in a different tablespace, alter the Oracle userid and make its default tablespace to be the one kept in ORACLE_TBLSP environment variable. For example: ALTER USER databaseName DEFAULT TABLESPACE oracle_tblsp; EXIT Where you need to provide the actual values for databaseName (from $LOGNAME) and oracle_tblsp (from $ORACLE_TBLSP). 4. Use the IMP command to import the tables, indexes and views for your database. For example (in one single line): $ORACLE_HOME/bin/imp $ORACLE_DBA buffer=40000 \ file=$HOME/backup/oracle.dmp commit=y show=n ignore=n grants=n \ indexes=y rows=y destroy=n full=n fromuser=$LOGNAME touser=$LOGNAME 5. If you have indexes stored in a different tablespace, do not import the indexes, create them after the tables have been imported. For example (in one single line): sed "s/TABLESPACENAME/$ORACLE_NDXSP/g" $CQ_HOME/install/index.db | \ $CQ_HOME/bin/sqlplus $LOGNAME/$ORACLE_PASS 36 ClearQuest: Oracle 9.0 ACCESSING THE ORACLE DATABASE TABLES This chapter provides procedures for accessing the database tables and views used by ClearQuest. The concrete example of adding a new host list entry is shown; although we discourage the manual modification of the database, in some situations (such as after renaming the hostname) it is necessary to add a host list entry directly into the database. The section 9.3, "How to use a script to execute SQL commands" on page 38 shows a Korn shell script that can be used to query the database. 9.1 HOW TO USE SQLPLUS TO GAIN ACCESS TO THE DATABASE Sometimes it is necessary to access directly the Oracle database used by the ClearQuest server in order to query the entries for a certain table or view. To gain access to the Oracle database, do the following: 1. Login to an account that has access to Oracle, such as the ClearQuest user id or the Oracle user id. 2. Enter: $ORACLE_HOME/bin/sqlplus databaseName/$ORACLE_PASS 3. After this, you will see the prompt: SQL> 4. Now you can enter other SQL commands. 5. Use "quit" to exit from sqlplus. 9.2 HOW TO EXIT FROM THE DATABASE 1. Perform the following statements: SQL> quit Accessing the Oracle database tables 37 9.3 HOW TO USE A SCRIPT TO EXECUTE SQL COMMANDS The Korn shell script shown in 9.3.1, "Korn shell script: oracleScript" on page 38 can be used to interact with the Oracle sqlplus tool to issue SQL commands and to store the output in an output file. In this example, the following SQL command will be executed (show all users, sorted by login): select * from Users where login like '%' order by login Actually, the above command needs to be specified between 2 spe- cific lines in the Korn script; each line contains the delimiter !!!. The following lines show the correct location; notice that the select statement needs to terminate with a semicolon, to allow multiple SQL commands to be issued in the same script: sqlplus $1 >> $LOG 2>&1 <> $LOG 2>&1 <