CMVC FREQUENTLY ASKED QUESTIONS: HOW TO PERFORM TYPICAL OPERATIONS WITH ORACLE 7 Document Number TR 29.3285 Angel Rivera, William C. Anderson CMVC Direct Customer Support IBM Software Solutions Research Triangle Park, North Carolina, USA Copyright (C) 2000, IBM All rights reserved. DISCLAIMER: This technical report is not an official publication from the CMVC group. The authors are solely responsible for its contents. ii CMVC FAQ: Oracle ABSTRACT This technical report provides a collection of hints and tips for CMVC family administrators that use the Oracle database manage- ment system (DBMS), Version 7. Some of the scenarios are: o Installing and configuring Oracle. o Starting and stopping Oracle. o Creating a CMVC family under Oracle. o Backup and restore of Oracle databases. o Accessing the CMVC tables. o Error messages and how recover from them. ITIRC KEYWORDS o CMVC o Oracle ABSTRACT iii iv CMVC FAQ: Oracle ABOUT THE AUTHORS ANGEL RIVERA Mr. Rivera is an Advisory Software Engineer and team lead for the CMVC Direct Customer Support team. He joined IBM in 1989 and since then has worked in the development 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. WILLIAM C. ANDERSON Mr. Anderson is a Senior Systems Management Integration Profes- sional in IBM's Software Solutions Group in Research Triangle Park, Raleigh, NC. He is responsible for UNIX operating system support, UNIX software tool support, and administration for AIX, Solaris, and HP-UX platforms. Mr. Anderson has enjoyed a successful fourteen year career working in the development, test, and support of various software platforms and products. He has a Bachelor of Science Degree from Park College, in Parkville, Missouri. ABOUT THE AUTHORS v vi CMVC FAQ: Oracle CONTENTS ABSTRACT . . . . . . . . . . . . . . . . . . . . . . . . . III ITIRC KEYWORDS . . . . . . . . . . . . . . . . . . . . . iii ABOUT THE AUTHORS . . . . . . . . . . . . . . . . . . . . . . V Angel Rivera . . . . . . . . . . . . . . . . . . . . . . . v William C. Anderson . . . . . . . . . . . . . . . . . . . . v FIGURES . . . . . . . . . . . . . . . . . . . . . . . . . VIII 1.0 INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . 1 1.1 User roles in Oracle and CMVC . . . . . . . . . . . . 1 1.2 Updates to the CMVC User's Reference manual . . . . . 2 1.3 Disclaimers . . . . . . . . . . . . . . . . . . . . . 2 1.4 Year 2000 readiness for CMVC and Oracle . . . . . . . 3 1.4.1 CMVC 2.3.1 provides support for the Year 2000 . . . 3 1.4.2 Oracle 7.3.4 is Year 2000 ready . . . . . . . . . . 3 1.5 How to get the most up to date version of this technical report. . . . . . . . . . . . . . . . . . . . . . 3 1.6 Acknowledgements . . . . . . . . . . . . . . . . . . . 4 2.0 INSTALLING ORACLE . . . . . . . . . . . . . . . . . . . 5 2.1 Number of Oracle licenses needed by CMVC . . . . . . . 5 2.2 Correction to the CMVC Server manual . . . . . . . . . 6 2.3 Pre-installation tasks performed by root . . . . . . . 6 2.4 Pre-installation tasks performed by the Oracle user id 7 2.5 Installation tasks performed by root - part 1 . . . . 8 2.6 Installation tasks performed by the Oracle user id - part 1 . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.7 Installation tasks performed by root - part 2 . . . . 9 2.8 Installation tasks performed by the Oracle user id - part 2 . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.9 Post-Installation tasks performed by root . . . . . 15 2.10 Post-Installation tasks performed by the Oracle user id . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 3.0 CONFIGURING ORACLE . . . . . . . . . . . . . . . . . . 17 3.1 Configuration activities performed by root . . . . . 17 4.0 STARTING AND STOPPING THE ORACLE DBMS . . . . . . . . 19 4.1 State diagram for Oracle DBMSs . . . . . . . . . . . 19 4.2 How to start the Oracle DBMS . . . . . . . . . . . . 19 4.3 How to stop the Oracle DBMS . . . . . . . . . . . . 20 4.4 How to query the status of the Oracle DBMS . . . . . 20 4.5 How to find out the version of the Oracle DBMS . . . 21 5.0 CREATING A DATABASE FOR A CMVC FAMILY . . . . . . . . 23 5.1 Activities to be performed by root . . . . . . . . . 23 5.2 Activities to be performed by the Oracle user id. . 23 5.3 Activities to be performed by the CMVC family user id. 24 Contents vii 5.4 How to find out which table and index spaces are defined . . . . . . . . . . . . . . . . . . . . . . . . . 26 5.5 How to add another file to the system table in Oracle? 27 5.6 How to find out the size of a table or index space . 27 6.0 BACKUP AND RESTORE . . . . . . . . . . . . . . . . . . 29 6.1 Backup of Oracle databases . . . . . . . . . . . . . 29 6.2 Restore of Oracle databases . . . . . . . . . . . . 29 7.0 ACCESSING THE ORACLE DATABASE TABLES . . . . . . . . . 31 7.1 How to use sqlplus to gain access to the database . 31 7.2 How to insert a new Host entry into the Hosts table 31 7.3 How to look at the contents of Hosts table for first user . . . . . . . . . . . . . . . . . . . . . . . . . . 32 7.4 How to exit from the database . . . . . . . . . . . 33 7.5 How to use a script to execute SQL commands . . . . 33 7.5.1 Korn shell script: oracleScript . . . . . . . . . 34 7.6 How to use isql with a file with SQL commands . . . 35 8.0 COMMON ERROR MESSAGES WITH ORACLE . . . . . . . . . . 37 8.1 Segmentation fault when starting cmvcd under Oracle 7.3.x . . . . . . . . . . . . . . . . . . . . . . . . . . 37 8.2 ORA-07429: smsgsg: shmget() failed . . . . . . . . . 37 8.3 /usr/lib/dld.sl: Call to mmap() failed - TEXT /usr/oracle73/lib/libclntsh . . . . . . . . . . . . . . . 38 8.4 NCMN-6401: when starting cmvcd . . . . . . . . . . . 38 9.0 COPYRIGHTS, TRADEMARKS AND SERVICE MARKS . . . . . . . 41 FIGURES 1. Sample .profile for the Oracle user id . . . . . . . . . 8 2. Stage diagram for Oracle DBMSs . . . . . . . . . . . . 19 viii CMVC FAQ: Oracle 1.0 INTRODUCTION This technical report provides a collection of hints and tips for CMVC family administrators that use the Oracle database manage- ment system (DBMS), version 7. The specific product is Oracle Server Enterprise Edition. The chapters in this TR are organized in the expected sequence of utilization of Oracle from a CMVC point of view: o Chapter 2.0, "Installing Oracle" on page 5 provides some overall recommendations for installing Oracle. o Chapter 3.0, "Configuring Oracle" on page 17 describes how to configure Oracle in order to properly create and support CMVC 2.3.1 families. o Chapter 4.0, "Starting and stopping the Oracle DBMS" on page 19 describes how to start, stop, query the status, and find out the version of the Oracle DBMS. Chapter 5.0, "Cre- ating a database for a CMVC family" on page 23 describes how to create a CMVC family under Oracle. o Chapter 6.0, "Backup and restore" on page 29 describes how to perform a backup of the database and how to restore it. o Chapter 7.0, "Accessing the Oracle database tables" on page 31 provides procedures for accessing the database tables and views used by CMVC. o Chapter 8.0, "Common error messages with Oracle" on page 37 describes the most common error messages that a CMVC family administrator may encounter when working with Oracle, and how to recover from them. 1.1 USER ROLES IN ORACLE AND CMVC o Operating System Administrator (root). This user maintains the operating system with superuser or "root" privileges. o Oracle user id It is a UNIX login that owns all the Oracle installation directories and files, and performs the installation and upgrading of the Oracle instance. o CMVC family user id Introduction 1 It is a UNIX login that owns a particular CMVC family, including the directories and files and database, and per- forms maintenance activities. 1.2 UPDATES TO THE CMVC USER'S REFERENCE MANUAL In the CMVC User's Reference manual, the note on page 53 of Chapter 7 is incorrect (section "4.4.2 Database information on CMVC Tables for queries"). The following is the correct information: ORACLE 7 INFORMIX SYBASE DB2 -------- ------------- ------- ------------ number integer int int varchar2 varchar varchar varchar long text in table text long varchar 1.3 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 CMVC group. The authors are solely responsible for its con- tents. o This technical report was prepared when working with Oracle 7.3.4 in AIX 4.2.1, in HP-UX 10.20 and in Solaris 2.5.1. Therefore, if you have a different version of the mentioned software, then you may expect some differences in the infor- mation or in the procedures described in this technical report. o This technical report covers information that the authors have gathered thru the years while working with the CMVC technical support team. o It is the intention of this technical report to provide recommendations and guidelines that can be helpful to CMVC administrators when using CMVC families where the database is 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. 2 CMVC FAQ: Oracle o It is assumed that the reader has knowledge of CMVC, Oracle and the appropriate operating system. This technical report is not a substitute to the information provided by Oracle, CMVC and the appropriate operating system. Please refer to the appropriate documentation pro- vided with the corresponding software. 1.4 YEAR 2000 READINESS FOR CMVC AND ORACLE 1.4.1 CMVC 2.3.1 provides support for the Year 2000 ____________________________________________________ CMVC 2.3.1 is a new version-release-modification of CMVC (branched from CMVC 2.3.0.24) which uses 4 digits to represent the years instead of the 2 digits used in 2.3.0 and previous ver- sions. Thus, CMVC 2.3.1 provides support for the Year 2000. For more details on CMVC and its Year 2000 readiness status, see the file: ftp://ftp.software.ibm.com/ps/products/cmvc/README.year2000.txt 1.4.2 Oracle 7.3.4 is Year 2000 ready ______________________________________ Oracle 7.3.4 is Year 2000 ready, according to the information provided in: http://www.oracle.com/year2000/ 1.5 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 CMVC ftp site at URL: ftp://ftp.software.ibm.com/ps/products/cmvc/doc/tr/trcmfora.txt For the list of available technical reports, see the file: ftp://ftp.software.ibm.com/ps/products/cmvc/doc/tr/README.index.txt Introduction 3 1.6 ACKNOWLEDGEMENTS Many of the questions and answers that are compiled in this tech- nical report were obtained from the CMVC forum in the IBMPC con- ferencing disk and from the CMVC6000 forum in the IBMUNIX conferencing disk. We want to thank the main participants in these electronic forums for their support! We want to thank in particular the following co-workers: o Edna Wong Kyu, OEM Lab in IBM RTP, North Carolina, USA. o Keith Purcell, OEM Lab in IBM RTP, North Carolina, USA. o Lee Perlov, Websphere/VisualAge TeamConnection Services, IBM RTP, North Carolina, USA. 4 CMVC FAQ: Oracle 2.0 INSTALLING ORACLE 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.4 for AIX 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/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 NUMBER OF ORACLE LICENSES NEEDED BY CMVC The minimum number of user licenses required by Oracle to run 4 CMVC daemons and all the other CMVC tools is shown below: 1 license for sqlplus to query the database 1 license for the notify daemon 1 license for chfield 1 license for other CMVC tools, such as vcPath 4 one license for each cmvcd daemon. --- 8 Total However, the number of user licenses required depends on the number of CMVC server daemons started by the CMVC administrator. This number varies depending on the number of CMVC clients that simultaneously request service from the CMVC server. Installing Oracle 5 2.2 CORRECTION TO THE CMVC SERVER MANUAL The component ORACLE (Transaction Processing Options) TPO (Runtime) is part of the DBMS beginning with Release 7 and there- fore is not an option to install, and in fact it is automatically installed with Oracle RDBMS. Thus, page 8 of the CMVC Server Administration and Installation manual for CMVC 2.3 has a redundant entry. 2.3 PRE-INSTALLATION TASKS PERFORMED BY ROOT These pre-installation instructions are focused to AIX. Please make the necessary adjustments for HP-UX and Solaris. 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 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. We created a separate file system and called it 'u01' and used /disk3/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 /disk3/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. 6 CMVC FAQ: Oracle We are not going to create the optional group "oper" because it is not needed for using CMVC. 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: /disk3/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 /disk3/u01/app/oracle/product/7.3.4 10. Now you can change the ownership of the Oracle directory structure: chown -R oracle:dba /disk3/u01 11. Proceed with 2.4, "Pre-installation tasks performed by the Oracle user id." 2.4 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 on page 8. Installing Oracle 7 #!/usr/bin/ksh # Set up environment variables for Oracle export ORACLE_HOME=/disk3/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=/disk3/u01/app/oracle export ORACLE_DOC=$ORACLE_BASE/doc export ORACLE_PATH=.:$ORACLE_HOME/bin:$ORACLE_HOME/obackup/bin:/opt/bin:/bin: export ORACLE_PATH=$ORACLE_PATH:/usr/bin:/usr/ccs/bin export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:. export PATH=.&colon$ORACLE_HOME/bin:$ORACLE_HOME/rdbms/install/rdbms:$PATH 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.5, "Installation tasks performed by root - part 1." 2.5 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. Create the Oracle link directory and set the permissions to make it accessible to all users: mkdir /disk3/olink chmod 777 /disk3/olink 8 CMVC FAQ: Oracle This directory requires up to 80 MB of free space and may be deleted after completing your installation. 4. Proceed with 2.6, "Installation tasks performed by the Oracle user id - part 1." 2.6 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.8, "Installation tasks performed by the Oracle user id - part 2" on page 10. 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: /disk3/olink 5. Proceed with 2.7, "Installation tasks performed by root - part 2." 2.7 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.8, "Installation tasks performed by the Oracle user id - part 2" on page 10. 1. Login as root. 2. Change to the Oracle link directory: cd /disk3/olink 3. Run the rootpre.sh script: ./rootpre.sh Installing Oracle 9 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.8, "Installation tasks performed by the Oracle user id - part 2." 2.8 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 /disk3/olink o For Solaris Change to: cd /cdrom/oracle/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 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: 10 CMVC FAQ: Oracle 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=/disk3/u01/app/oracle ORACLE_HOME=/disk3/u01/app/oracle/product/7.3.4 ORACLE_SID=sid Click on OK. 8. 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. 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: Installing Oracle 11 American/English Click on OK. 11. You will see the window "Software Asset Manager". Select to install the following products from the left column ("Products available on /disk3/olink"). 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. 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. 12 CMVC FAQ: Oracle 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: /disk3/u01/app/oracle Click on OK. 17. You will see the window "Character Set". Accept the default: US7ASCII 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'?". Accept the default: No Click on No. 22. You will see the window "Create DB Objects (F/S): Control File Locator". Installing Oracle 13 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 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. 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. 29. Change the directory to $ORACLE_HOME: cd $ORACLE_HOME 30. Proceed with 2.9, "Post-Installation tasks performed by root" on page 15. 14 CMVC FAQ: Oracle 2.9 POST-INSTALLATION TASKS PERFORMED BY ROOT 1. Login as root. 2. Change the directory orainst in $ORACLE_HOME, for example: cd /disk3/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 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. Remove the temporary Oracle link directory: rm -fr /disk3/olink 5. Proceed with 2.10, "Post-Installation tasks performed by the Oracle user id." 2.10 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 Installing Oracle 15 16 CMVC FAQ: Oracle 3.0 CONFIGURING ORACLE This chapter describes how to configure Oracle in order to prop- erly support CMVC 2.3.1 families. 3.1 CONFIGURATION ACTIVITIES PERFORMED BY ROOT o Oracle 7.3 libraries It is necessary to perform the following symbolic links in order for the CMVC family server (cmvcd) to work with Oracle 7.3: mkdir -p /usr/oracle73 ln -s $ORACLE_HOME /usr/oracle73 This symbolic link is necessary to avoid the following type of error message when trying to run cmvcd: $ cmvcd cmcc2ora 2 exec(): 0509-036 Cannot load program cmvcd because of the following errors: 0509-022 Cannot load library /usr/oracle73/lib/libclntsh.a[clntsh.o]. 0509-026 System error: A file or directory in the path name does not exist. o In Solaris 2.5.1, we had to perform the following symbolic links: ln -s $ORACLE_HOME/lib/libclntsh.so.1.0 /usr/lib/libclntsh.so.1.0 ln -s $ORACLE_HOME/lib/libsunmath.so.1 /usr/lib/libsunmath.so.1 With this setup, it does not matter if LD_LIBRARY_PATH is set or not. For some reason, it seems that LD_LIBRARY_PATH is ignored by Oracle in Solaris. Configuring Oracle 17 18 CMVC FAQ: Oracle 4.0 STARTING AND STOPPING THE ORACLE DBMS This chapter describes how to start, stop, query the status, 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 4.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. 4.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 19 ./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. 4.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. 4.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". 20 CMVC FAQ: Oracle 4.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 21 22 CMVC FAQ: Oracle 5.0 CREATING A DATABASE FOR A CMVC FAMILY This chapter describes how to create a CMVC family under Oracle. This is an overview on how to create a CMVC family that uses an Oracle database. For more information see the manual CMVC Server Administration and Installation, 2.3. This chapter also explains how to find out which table (see 5.4, "How to find out which table and index spaces are defined" on page 26) and index spaces have been defined, and how to expand them (see 5.5, "How to add another file to the system table in Oracle?" on page 27). 5.1 ACTIVITIES TO BE PERFORMED BY ROOT 1. Login as root. 2. Create the CMVC family user id, which should belong to the same group as the Oracle user id. In this example, the family name is "cmcc2ora" and it belongs to the group "dba". 3. Add the new CMVC family user id in /etc/hosts. 4. Add the new CMVC family user id in /etc/services. 5. Logout as root. 5.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 a directory where to store the regular table and index database spaces, such as under $ORACLE_BASE. You have to choose a place where you have enough file system space for the database. Creating a database for a CMVC family 23 Create first a directory that can be easily recognized, as "cmvc-space", then create a subdirectory with the name of the family: cd /disk3/u01/app/oracle mkdir cmvc-space mkdir cmvc-space/cmcc2ora 3. Create the table and index spaces where to store the regular database: a. Issue: sqlplus system/system b. Specify the table space (in this case, with a starting size of 10 MB): create tablespace cmcc2oratblsp datafile '/disk3/u01/app/oracle/cmvc-space/cmcc2ora/data01.dbf' size 10M default storage (initial 10k next 10k pctincrease 50 minextents 1 maxextents 120) ; c. Specify the index space (starting size of 10 MB): create tablespace cmcc2orandxsp datafile '/disk3/u01/app/oracle/cmvc-space/cmcc2ora/index01.dbf' size 10M default storage (initial 10k next 10k pctincrease 50 minextents 1 maxextents 120) ; 4. Logout as the Oracle user id. 5.3 ACTIVITIES TO BE PERFORMED BY THE CMVC FAMILY USER ID. 1. Login as the CMVC family user id. 2. Copy the sample profile for a CMVC family: cp /usr/lpp/cmvc/install/profile.oracle7 .profile The above command uses the default location for CMVC. All the CMVC related environment variables are already listed and documented in the sample profile for a CMVC family that uses Oracle. 3. Modify the .profile. Follow the instructions in the comments in the header of the file. 24 CMVC FAQ: Oracle 4. Ensure that all the environment variables related to Oracle are defined: ## ## Oracle setup ## ## NOTE: DO NOT USE A LINK for ORACLE_HOME ## You MUST use the original directory path. ## The use of a link causes the CMVC install to fail and the errors from ## Oracle are misleading. export ORACLE_HOME=/disk3/u01/app/oracle/product/7.3.4 export ORACLE_DBA=system/system ## See /etc/oratab to find out the Oracle SID (System ID) export ORACLE_SID=sid ## The following variable must have the password for the CMVC Family ## (and not the password for the Oracle login) export ORACLE_PASS=cmcc2ora ## Specify the table and index spaces to use for the database export ORACLE_TBLSP=cmcc2oratblsp export ORACLE_NDXSP=cmcc2orandxsp ## ## Modification of PATH ## ## The following statements include needed CMVC entries: PATH=$PATH:$CMVC_HOME/install:$CMVC_HOME/bin:$CMVC_HOME/samples PATH=$PATH:$ORACLE_HOME/bin:/usr/lbin:$HOME/bin:. export PATH ## For single-instance machines, invoke oraenv, which is stored ## in the local directory used by Oracle during the installation ## The default is /usr/lbin. export ORAENV_ASK=NO . /usr/lbin/oraenv The value for the ORACLE_TBLSP and ORACLE_NDXSP environment variables will be used to create the database for the CMVC family user id. 5. May need to setup LD_LIBRARY_PATH to $ORACLE_HOME/lib in HP-UX. It might be possible that when running cmvcd on Oracle 7.3, the following variable need to be used for the CMVC family in the .profile: export LD_LIBRARY_PATH=$ORACLE_HOME/lib Creating a database for a CMVC family 25 6. After editing the profile, exit your session and login again in order to have a clean environment. 7. Create the CMVC family. At this time, all the environment variables and the configuration items should be ready for creating and starting the CMVC family with 2 server daemons: a. mkfamily b. mkdb -d c. cmvcd cmcc2ora 2 d. notifyd e. You can now use the CMVC family. f. To monitor the usage of the CMVC daemons do: monitor 1 Press an alphanumerical key to stop the monitor command. g. To stop the CMVC daemons do: stopCMVC cmcc2ora 5.4 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: 26 CMVC FAQ: 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 5.5 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. 5.6 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 CMVC family 27 TABLESPACE_NAME FILE_ID PIECES MAXIMUM) MINIM ------------------------------ ---------- ---------- ---------- -------- AVERAGE TOTAL ---------- ---------- SYSTEM 1 2 15271 7675.5 15351 28 CMVC FAQ: Oracle 6.0 BACKUP AND RESTORE This chapter describes how to perform a backup of the database and how to restore it. 6.1 BACKUP OF ORACLE DATABASES 1. Login as the CMVC family administrator ($LOGNAME): 2. Stop the CMVC family: stopCMVC $LOGNAME 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 CMVC family. 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 6.2 RESTORE OF ORACLE DATABASES 1. If you are restoring into the same CMVC family id, then drop the existing database: $ rmdb 2. Create an Oracle userid with the same name as your CMVC family. This userid has the password kept in the ORACLE_PASS environment variable. For example: $ORACLE_HOME/bin/sqlplus $ORACLE_DBA GRANT CONNECT, RESOURCE TO familyName IDENTIFIED BY oracle_pass; Where you need to provide the actual values for familyName (from $LOGNAME) and the original password when the family was created (from $ORACLE_PASS). Backup and restore 29 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 familyName DEFAULT TABLESPACE oracle_tblsp; EXIT Where you need to provide the actual values for familyName (from $LOGNAME) and oracle_tblsp (from $ORACLE_TBLSP). 4. Use the IMP command to import the tables, indexes and views for your CMVC family. 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" $CMVC_HOME/install/index.db | \ $ORACLE_HOME/bin/sqlplus $LOGNAME/$ORACLE_PASS 30 CMVC FAQ: Oracle 7.0 ACCESSING THE ORACLE DATABASE TABLES This chapter provides procedures for accessing the database tables and views used by CMVC. 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 7.5, "How to use a script to execute SQL commands" on page 33 shows a Korn shell script that can be used to query the database. 7.1 HOW TO USE SQLPLUS TO GAIN ACCESS TO THE DATABASE Sometimes it is necessary to access the Oracle database used by the CMVC family 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 CMVC family user id. 2. Enter: $ORACLE_HOME/bin/sqlplus $LOGNAME/$ORACLE_PASS 3. After this, you will see the prompt: SQL> 4. Now you can enter other SQL commands. You have to add a semicolon at the end of each statement. 5. Use "quit" to exit from sqlplus. 7.2 HOW TO INSERT A NEW HOST ENTRY INTO THE HOSTS TABLE 1. Perform the following statements: SQL> insert into Hosts (userId, name, login, address) values (1, 'example.cary.ibm.com', 'loginId', 0); Accessing the Oracle database tables 31 NOTES: a. b. The userId value of 1 is the id associated with the ori- ginal CMVC_SUPERUSER. If another userid is needed, then follow in a similar manner the step 7.3, "How to look at the contents of Hosts table for first user" on page 32 to show the id value in the Users table for the appropriate user id. This field is numeric. c. During mkdb, the value for "name" (with a value shown here of 'example.cary.ibm.com') is $HOSTNAME, where this value is obtained by means of executing: /usr/bin/hostname $CLIENT_HOSTNAME d. During mkdb, the value for "login" is obtained from $CLIENT_LOGIN 2. Continue with 7.3, "How to look at the contents of Hosts table for first user." 7.3 HOW TO LOOK AT THE CONTENTS OF HOSTS TABLE FOR FIRST USER 1. Perform the following statements: SQL> select name,login from r3fam.Hosts where userId=1; NOTES: a. The Table/View name MUST be preceded by the database name (familyName). b. Use ; (semicolon) at the end of each query. c. Refer to the CMVC User's Reference, Chapters 6 and 7 for the field names and sizes. d. Oracle is NOT case sensitive for any names. The headers are always shown in uppercase, but the information is displayed according to the case. 2. An example of the Output from the SQL command: NAME ---------------------------------------------------------------- LOGIN ------------------------------- carcps08.cary.ibm.com build 32 CMVC FAQ: Oracle This mean that the host name is carcps08.cary.ibm.com and that the login is build. 3. Continue with 7.4, "How to exit from the database." 7.4 HOW TO EXIT FROM THE DATABASE 1. Perform the following statements: SQL> quit 7.5 HOW TO USE A SCRIPT TO EXECUTE SQL COMMANDS The Korn shell script shown in 7.5.1, "Korn shell script: oracleScript" on page 34 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 <