D17325gc10 Setup Doc

Oracle University Technical Setup Document

Introduction to Oracle Database 10g for Experienced SQL Users Production 1.0

Global Course Code: D17325GC10 Date: 01-Sep-2004

Copyright © 2004, Oracle Corporation. All rights reserved.

Overview Introduction Welcome, and thank you for choosing Oracle University. This technical document describes the necessary steps to follow in order to replicate the technical environment for which this course was designed. Before proceeding, you may find it helpful to print this document for ease of reference. This is the setup document for the course Introduction to Oracle Database 10g for Experienced SQL Users. Instructions are provided to set up the server, database accounts, client, middle tier, and class files. Overall, one database on the server is recommended. The sample schema shipped with the database is used for this course and cloned for several users using scripts provided. . SQL*Plus is set up on the client as interface to the server data. iSQL*Plus is set up on middle tier as an interface to the server data. The database and midtier server setup is based on Oracle Database 10g for Linux. The client/middle tier software is based on Oracle Database 10g for Windows 2000/NT.

Alternative Configurations We strongly recommend following the exact configurations described in this document. However, we realize that with the abundance of hardware, software, and networking configurations it will not always be possible to follow these steps to the letter. As a result, here is a brief overview of some additional configurations that may work. Please be sure to test these configurations thoroughly and discuss them in detail with your Oracle University Representative before utilizing them to support the course.

Software Licenses & Support Please note the following as it relates to software licenses and support: • •

Oracle University does not provide product software or licenses for on-site training events. This document assumes possession of all applicable Oracle software and associated licenses. It is not possible to provide a complete and thorough installation guide in this document, though effort is made to include important directions to follow when performing the installation. Customers with product installation issues should open a Technical Assistance Request (TAR) with Oracle World Wide Support for resolution.

Required Files The following files are needed for the purposes of completing this setup. If you are missing all or part of these files please contact your Oracle University Representative: contents of each file are specified in the relevant sections in this document. 1. D17325GC10_setup_doc.zip (This document, which contains server (Linux), and client(Win2K) side setup instructions for both software and course material

Copyright © 2004, Oracle Corporation. All rights reserved.

2

2. D17325GC10_setup_files.zip (zip file that contains any course specific setup files – course lab files not included) 3. D17325GC10_labs.zip (zip file that contains course specific files for both the instruction machine and the student machine on both the server side and client side)

Glossary of Terminology Application Server – Middle Tier in a 3-Tier or N-Tier architecture. Could consist of the Oracle HTTP Server, iSQL*Plus, Forms Service, Reports Service and Discoverer Server. Client Workstation – The computer where “client” software is installed. Client software might include SQL*Plus, Oracle Forms and Reports Developer, Oracle Designer, JDeveloper, Procedure Builder and an HTML browser. In a 3-Tier or N-Tier architecture this would be the Client Tier. In the training environment, this is sometimes known as the student machine. Database - Collection of data that is treated as a unit. The purpose of a database is to store and retrieve related information. The database consists of operating system files: data files, control files and redo log files. Database Instance - Every time a database is started, a system global area (SGA) is allocated and Oracle background processes are started. The system global area is an area of memory used for database information shared by the database users. The combination of the background processes and memory buffers is called an Oracle instance. Database Server - Computer where database is created. In a 3-Tier or N-Tier architecture this would be the Database Tier. DMP files – files created buy the Oracle Export utility. These DMP files are then used by the Oracle Import utility to import a database, a specific database users objects or a specific set of tables. Intelligent Agent (IA) - A process running on the server that accepts and runs job requests from the Oracle Enterprise Manager Console. Oracle Enterprise Manager (OEM) - a Graphical User Interface tool used to manage oracle databases. OEM Repository - database that stores information about resources managed through OEM. Oracle Management Server - provides a scaleable middle tier for processing system management tasks. It is the optional middle tier of Oracle Enterprise Manager (OEM) n-tier architecture. ORACLE_HOME - operating system variable that points to the directory where oracle has been installed ORACLE_SID – operating system variable that determines the instance an Oracle application connects to by default. iSQLPlus – Oracle tool for SQL and PL/SQL application development

Copyright © 2004, Oracle Corporation. All rights reserved.

3

Setup Instructions Hardware Requirements for Database Server Minimum Operating System

Red Hat Enterprise Linux AS 3.0 or equivalent Linux release (Kernel 25) with FTP, VNC, SSH

Memory

256 MB RAM or more

Hard Drive

Three times the amount of Physical Memory for Swap space (unless the system exceeds 1 GB of Physical Memory, where two times the amount of Physical Memory for Swap space is sufficient) Recommended to use the equivalent of Dell 1650 1.4Ghz x 2 processor(Pentium III) 4Gb memory and 2 - 36gb drives internally. Minimum: 1GB RAM, 2GB Swap, 5GB HDD.

See Database Server installation guide for further details

Hardware Requirements for Client Workstation Minimum Operating System

Windows 2000 Workstation

CPU

Pentium III 500 MHz or higher

Memory

512 MB RAM or more

Hard Drive

400 MB or greater free

Video

65536 colors, set to at least 1024 X 768 resolution

Hardware Requirements for Middle Tier Server For this course, the Middle Tier server resides on the Database server, which is explained above

Copyright © 2004, Oracle Corporation. All rights reserved.

4

Database and Midtier Server Setup Instructions: Software Setup Instructions Overview: In this phase you will install software and configure the network on the CLASSROOM SERVER. Requirements: Ensure CLASSROOM SERVER has Red Hat Enterprise Linux AS 3.0 or later (or equivalent) and TCP/IP installed and the host name is resolvable through local host file or a Domain Name Server. Task: Obtain Course Materials Obtain all the zip files as specified in the “Required Files” section above. The files for the classroom setup contain both scripts for running on the Classroom Server, as well as files for the Classroom Workstations. You may want to extract the zip files to a windows machine and ftp and/or copy files as necessary. Task: Install Oracle

Database 10g Database Server

Overview: In this phase you will install the database and middle tier (iSQL*Plus) on a Linux server Duration: 3 hours Oracle_Home Details: Oracle 10G RTM user name/owner = ora10102 ORACLE_HOME=/oracle/ora10102 owner UID = 10138 owner GID = 10138 owner group name = grp10102 owner group number = 10138 owner dba group name = dba10102 owner dba group number = 10139 Oracle Home / Database Installation : 1. Create user and groups as shown above. 2. Make sure the /etc/sysctl.conf has the following. kernel.sem = 250 32000 100 128 kernel.shmmax = 2147483684 kernel.shmmni = 4096 kernel.shmall = 2097152 kernel.msgmnb = 360000 kernel.msgmni = 2878 kernel.msgmax = 8192 fs.file-max = 65536 net.ipv4.ip_local_port_range = 1024 65000 3 a. Create /etc/ocr.loc with the entries below: ocrconfig_loc=/oracle/ora10102/cdata/localhost/local.ocr local_only=TRUE

Copyright © 2004, Oracle Corporation. All rights reserved.

5

b. Create /etc/oraInst.loc with the entries below: inventory_loc=/oracle/ora10102/oraInventory inst_group=grp10102 4. Make sure the .profile contain the following PATHs set as shown in the following example. You can customize the SID to fit your environment. unset PATH; export PATH unset USERNAME PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib umask 022 ORACLE_BASE=/oracle/ora10102 ORACLE_HOME=/oracle/ora10102 ORACLE_SID=T5 LD_LIBRARY_PATH=$ORACLE_HOME/jdk/jre/lib/i386:$ORACLE_HOME/jdk/jre/lib/i386/ server:$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH PATH=$ORACLE_HOME/bin:$PATH export PATH LD_LIBRARY_PATH export ORACLE_BASE ORACLE_HOME ORACLE_SID 5. Using 10G RTM CD to install the Oracle home (EE) and create the general purpose database . (Note : iSQL*Plus will be installed and configured with port 5561 automatically. Once configured, it can be accessed in the browser by typing the url: iSQL*Plus URL:
In the “Welcome” screen select "Next". In the “File Location” screen enter the path of the Oracle10g ORACLE_HOME then select “Next” The products list will now load. In the “Available Products” screen select “Oracle Database 10g”, which includes a preconfigured ‘starter’ database, and select “Next”. In the “Installation Types" screen select "Enterprise Edition (2.47GB)" then select "Next". In the “Database Configuration" screen select "General Purpose" then select "Next". In the Database Identification screen, enter the Global Database Name ‘ORCL.WORLD’. The SID name will be ORCL. Click Next. Accept the default directory for database files and select Next. Select the default character set and select Next. In the "Summary" screen select "Install". Insert disk2 and disk3 when prompted, changing the path if necessary. Run root.sh as root if prompted The Configuration Assistant will appear to create the sample database. When it is complete, select Exit. In the "End of Installation" screen select "Exit". Set the ORACLE_HOME, ORACLE_SID, and PATH environment variables. This document takes an example where ORACLE_SID is set to “ORCL”. PATH should point to the $ORACLE_HOME/bin directory.

Special Directions:

Copyright © 2004, Oracle Corporation. All rights reserved.

6

The following course specific parameters need to be set in the init.ora file: query_rewrite_enabled

= TRUE

Task: Test Database Server Installation 1. 2. 3. 4.

Open SQL*Plus from command prompt by typing sqlplus. Connect to database as ora1/oracle Issue the following: SELECT * from EMPLOYEES; The query should return 107 rows successfully. Make sure the URL is working to test the Oracle_Home and iSQL*Plus configured correctly. iSQL*Plus URL : http://:5560/isqlplus ( Note : To start the isqlplus service, type isqlplusctl start, To stop it, type isqlplusctl stop ) Connect as ora1/oracle with a connect string as your Oracle sid, such as T5.

5.

Issue the following: SELECT * from EMPLOYEES; The query should return 107 rows successfully.

Task: Install iSQL*Plus patch Overview: This tasks installs iSQL*Plus patch on the server. Following are the known problems in iSQL*Plus Release 10.1.0.2: 3123309 - iSQL*Plus stops responding when loading some scripts 3350738 - iSQL*Plus Save Script saves unnecessary header information in scripts In order to fix these problems, first download and install OPatch version 1.0.0.0.48: 1. Open a browser and go to http://metalink.oracle.com/ 2. Log in using your metalink login 3. Select "Patches" 4. Select "Simple Search" 5. Enter "2617419" in the Search By field. Click the Go button. The patch download should start, with a file name of p2617419_220_GENERIC.zip. To install OPatch version 1.0.0.0.48: 1. Back up the previous OPatch directory (if required). For example: cd $ORACLE_HOME mv OPatch OPatch.1.47 2. Unzip p2617419_220_GENERIC.zip into $ORACLE_HOME: cd $ORACLE_HOME unzip p2617419_220_GENERIC.zip Now install the iSQL*Plus Interim Patch (for the two know problems listed above): 1. Open a browser and go to http://metalink.oracle.com/

Copyright © 2004, Oracle Corporation. All rights reserved.

7

2. Log in using your metalink login 3. Select "Patches" 4. Select "Simple Search" 5. Enter "3471948" in the Search By field. Select your operating system. Click the Go button. 6. Unzip the patch file 7. Read README.txt for installation instructions. These problems will be fixed in iSQL*Plus Release 10.1.0.3 (the next patch set). Task: Install Courseware on the server side Overview: In this phase you will run the scripts to create the user accounts and tables for the course. Duration: 10 min 1. 2.

Shutdown the database. Unzip the setup files supplied into user’s home directory. 3. Replace init.ora with the course specific int.ora ( such as initT5.ora). Start the database using course specific init.ora. 4. Create data01 tablespace with 80MB 5. Unlock system user account and change the system user password to manager. 6. Use the script creschem.sql and pass to it the number of accounts you want created, such as 20. This files will create 20 user accounts named ora1 through ora20 and a teach account with a smaller subset HR schema data. 7. Create the crecf.sql using alter database backup controlfile syntax 8. Shutdown the database with normal option and backup *.dbf files for cloning purposes. 9. This database is good for the course that require full HR Schema. 10. Update the tnsnames.ora and listener.ora with your server name. 11. Start the listener. 12. Append tnsnames.ora to $ORACLE_HOME/network/admin/tnsnames.ora Ex : $ cat tnsnames.ora >> $ORACLE_HOME/network/admin/tnsnames.ora For the Instructor account on the server you must create a O/S directory called EMP_DIR under $USER_HOME and place the file emp.dat there Task: Create Directories for Experienced SQL Users course 1. 2. 3.

Unzip the D17325GC10_setup_files.zip file to any directory on server and extract the script files. These files are course specific that are used to create necessary directories and other objects Log into SQL*Plus as sysdba Run the script cre_dir.sql from the directory where the above zip file was extracted.

CREATE DIRECTORY emp_dir as ' <Specify path here> /EMP_DIR'; GRANT READ ON DIRECTORY emp_dir to PUBLIC; Note: If unable to do the FTP check that the listener on the FTP port has been started and make sure the file was uploaded in binary mode. Task: Test Courseware Installation on server 1. Open SQL*Plus if necessary 2. Connect to database as ora1/oracle 3. Issue the following: SELECT * from EMPLOYEES; The query should return 107 rows successfully.

Copyright © 2004, Oracle Corporation. All rights reserved.

8

4. Select * from ALL_DIRECTORIES and verify that EMP_DIR directory is listed.

Client Setup Instructions Software Setup Instructions (Client) Software CDs Oracle Database 10g for Microsoft Windows NT/2000/XP; Instructions The client will have a database installation plus have Oracle Enterprise Manager installed. Non-Oracle Software Prior to starting the install, have Internet Explorer 6.0.2800.1106, WinZip, Notepad or Wordpad available, and create desktop shortcuts for each. Also recommended: telnet software - Optional Microsoft word – VIEWER Microsoft PowerPoint -VIEWER FTP software - Optional Installation instructions for software Log into the Windows system as ADMINISTRATOR account/user and perform the following steps: 1.

Place the Oracle 10g Client for Windows NT/2000 CD into the CD-ROM. Login as the operating system user created above, change directory to the CD and start the setup.exe executable.

2.

In the Oracle Universal Installer Welcome window, click Next.

3.

In the Specify Inventory Directory window, accept the default directory path for the Inventory directory. Then click “Next” to continue.

4.

In the Specify File Location window, accept the default Name (OraClient10g_home1) for the Oracle Home name and type E:\oracle\ora10g (you may change the E: drive to one appropriate for you site.) for the Oracle Home directory for installation files. Then click “Next” to continue.

5.

At the Select Installation Type window, select the Administrator (460MB) install. IMPORTANT NOTE: Do not click the “Next” button yet, you are not finished with this screen yet. Select the Product Languages from this same screen, a Language Selection window will pop up, from the Language Selection window, select all languages and then click “OK”, then click “Next” to continue to the next screen.

6.

At the Summary window select Install to install the Oracle 10g Client products chosen. After the installation process is completed, you should see the End of Installation window with the following text message: The installation of Oracle Client was successful.

7.

Navigate to E:\oracle\ora10g\bin directory (or whichever drive on which you installed the software), Right-click on sqlplusw.exe and select ‘Create Shortcut’, move the shortcut to the desktop.

Copyright © 2004, Oracle Corporation. All rights reserved.

9

Task: Configure Net Service Names 1. 2. 3.

Configure the tnsnames.ora by entering the tns entry for the database. Copy the tnsnames.ora to e:\oracle\ora10\network\admin directory (or whichever drive on which you installed the software), Edit e:\oracle\ora10\network\admin tnsnames.ora and modify it to reflect the SID you have set up.

Task: Install Client Software 1. 2.

Install Internet Explorer 6.0, WinZip, Notepad or WordPad. Create desktop shortcuts for each. Create an icon for SQL*Plus 3. Navigate to %ORACLE_HOME%\bin 4. Right-click on sqlplusw.exe and select ‘Create Shortcut’ 5. Move the shortcut to the desktop 3. Have an Internet Explorer icon on desktop Task: Install Courseware on client Overview: In this phase you will install courseware on the client machines. Duration: 1 hour All code examples, demos, labs, and solution files are placed on each CLASSROOM WORKSTATION during the course setup. A folder structure is created that is course specific. The following folder structure is an example of a typical Client/Server course. For the client side courseware files, use the zip file D17325GC10_labs.zip this contains two files, namely D17325GC10_instructor_labs.zip and D17325GC10_student_labs.zip. For the Instructor client machine, unzip the D17325GC10_instructor_labs.zip file. It creates the following folders and files: E:\labs\ExpSQL

\code_ex \demos \labs \soln

For each student client machine, unzip the D17325GC10_student_labs.zip. It creates the following folders with class files: E:\labs\ExpSQL

\code_ex \ demo \labs \soln

Copyright © 2004, Oracle Corporation. All rights reserved.

10

Task: Test Courseware Installation on client connecting to server Start Internet explorer and type the IP address provided for the database as URL. This opens iSQL*Plus login screen. Connect using any username(example: ORA7) and password oracle. Connect string should match the Oracle SID you created in a step as mentioned earlier in this document. 1.

Execute the commands: select count(*) from EMPLOYEES; You should get 107 rows returned

Clean Up Server Clean-Up 1.

2. 3.

To remove courseware, delete every ora user you created as such: SQL> drop user ora1 cascade; Alternatively, you can run the dropsche.sql file provided to you in the D17216GC10_setup_files.zip to have the users dropped programmatically. Pass the number of users you want dropped to this files. To remove the database, insert the Oracle Database 10g CD and select deinstall. To remove course specific files, delete the directories code_ex, demo, labs, and soln.

Client and Middle-Tier Clean-Up 1. 2.

To remove the database, insert the Oracle Database 10g CD and select deinstall. To remove course specific files, delete the directories code_ex, demo, labs, and soln.

We hope your Oracle University training experience was a positive one! For information on additional training services and opportunities within , please visit us at http://education.oracle.com. We look forward to serving you again in the future.

Copyright © 2004, Oracle Corporation. All rights reserved.

11