Oracle 9i installation

Table of contents

• 1 Installation Oracle Server / Single-User Workstation - (Standard - and Personal Edition)
• 2 Creating a IDL Konsis-Supply Database
• 2.1 Creating for an empty Oracle Database
• 2.2 Tip for editing in the file INIT.ORA by an Unicode Database
• 3 Configuration the database connection
• 3.1 Listener - Configuration
• 3.2 Local Net Service Name Configuration
• 4 Manuel Setup a Database for IDL Konsis
• 4.1 Creating own tablespace for IDL Konsis
• 4.2 Creating Database-User
• 4.3 Loading the IDL Konsis Database-Content
• 4.4 Loading the Database with IMPORT Program of Oracle
• 4.5 Information for the installation of a database to an existing instance
• 5 Establishing an Oracle Client
• 5.1 Installation of the Oracle Client Software
• 5.2 Configuration of the Client Database-Connection
• 5.3 Oracle-Client on Windows 98

( Screen - Shots of Oracle Version 9.2.0.1.0 )

After the CD is stored in the CD-Device the installation-program is running automatically.

Tip: Only the most important masks of the "Oracle Universal Installer" are shown here.

Entering the installation-path

Choosing the product, here "Oracle 9i Database 9.2.0.1.0"

Choosing the type of installation of your oracle license, here "Standard Edition" for Server and "Personal Edition" for Single-User Installation

Selection here "only software", in order to provide the data base later with "The Database Configuration Assistant".

If you want to provide equal data base with, take "universal", and check you under 2.2.1 for the Database Configuration Assistant. Note in this case the transacted data for SID, hostname and Net service name for the later Client installation!

"Oracle Services for Microsoft Transaction Server" and "Overview" in each case confirm with "next".

After the files are copied from the CD, confirm still the end.

Automatical runs the Enterprise Manager.

If you have choosen "Only Software", ask the system for "Hostname", "SID" und "Net Service Name". If you have already running a Oracle database in your network, you can here set your own entries, otherwise cancel and close the Enterprise Manager, and go to capitel 2.2

If you have choosen "Universal" for datenbase-configuration, the Installation is completed and you can start the client installation.

Start the Database Configuration Assistant under Start / All Programs / Oracle-OraHome92 / Configuration and Migration Tools.

Confirm the greetings with "Next" and arrive in such at step 1 to 7.

Select "Create a database" at step 1

Select "General Purpose" at step 2

Global Database Name and SID are best of all: IDLDB

Select "Dedicated Server Mode" at step 4

Select standard as memory and enter a percentage, which corresponds to at least 124 MB of the physical memory.

An example for the correct percentage by 512 MB physical memory.

The other 4 tab-pages control

If the language under the operating system is "WE8MSWIN1252" choose Use the default as "Character Set. Otherwise "Choose from the list of character sets" this character set. If you want use Unicode, choose "Unicode (AL32UTF-8)".

Set under "DB Sizing‚" 8 MB for sort area size.

Confirm tab-page "File Locations".

Archiving is not enabled, unless your local DBA wants it.

Confirm Database storage.

Finish at step 7.

Summary of all common options for Creation of Database. Confirm with < OK >.

At least enter the passwords for the Oracle System administrator "SYS" and "SYSTEM".

If you have choosen UNICODE (AL32UTF-8) for your database, insert into INIT.ORA following parameter:

NLS_LENGTH_SEMANTICS=CHAR

shutdown and restart the instance before you are creating the tables.

Start the " Net Configuration Assistant " under Start / All Programs / Oracle - OraDb10g_home1 / Configuration and Migration Tools.

Step 1: Welcome

Select "Listener - configuration".

Step 2: Listener

Select "Add"

Step 3: Listener - Name

The listener get a name, e.g.: "Listener". Later you find this name under Windows General view "Services" and shoud be started.

Step 4: Select Protocols

Confirm the selected protocol "TCP".

Step 5: TCP/IP - Protocol

Confirm the standard Port number of 1521.

Step 6: More Listeners ?

"Wood you like to configure another Listener ?" confirm with "NO".

Step 7: Configuration done

Confirm the "Listener Configuration complete!", and continue automatically with 3.2.2 "Add" under "Local Net Service Name Configuration".

Start the "Net Configuration Assistant" under Start / All Programs / Oracle - OraDb10g_home1 / Configuration and Migration Tools.

Step 1: Welcome

Select "Local Net Service Name Configuration".

Step 2: Net Service Name Configuration

Select "Add".

Step 3: Select Protocols

Confirm the selected protocol "TCP".

Step 4: TCP/IP Protocol

Enter the host-name for the computer where the database is located. The Host should be reachable via ping in a Prompt (DOS) window! Select standard port number 1521".

Step 5: Test

A connection test can be performed to the database.

Step 6: Net Service Name

Enter "IDLDB" for this net service name. Later you find this name under Windows General View "Services" and shoud be started.

Step 7: Another Net Service Name

Confirm with "NO" for another net service name.

Step 8: Done

Confirm the message "Net service name configuration complete!" and close the program.

Create Tablespace KONSIS:

1. SQL-Plus is used to register SYSTEM/PASSWORD of the database. To find under Start / All Programs / Oracle - OraDb10g_home1 / application development.
2. " Execute the SQL-Command by customising the path of the parameter DATAFILE and the size of SIZE:
3. CREATE TABLESPACE KONSIS LOGGING DATAFILE 'D:\ORACLE\ORADATA\IDLDB\KONSIS.DBF' SIZE 800M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 32767M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
4. " closing SQL-Plus by : disconnect or continue with point 4.2.2.

There should exist a database-user named idldb with the authority of RESOURCE. This is the master of all IDL Konsis-Tables.

The table-space KONSIS is assigned to the user idldb. In this space all IDL Konsis-Tables shall be generated.

Running of IDL Konsis requires one user IDLADMIN and all IDL Konsis-User with CONNECT-rights to the database. The maximum lengths of the User-Id and Password should not exceed 8 digits. It is recommended to allocate Temporary Table-space for all user explicitly TEMP.

1. SQL-Plus is used to register SYSTEM/PASSWORD of the database. To find under Start / All Programs / Oracle - OraDb10g_home1 / application development.
2. Is the user IDLDB still existent after an aborted installtion, delete the user IDLDB with the order: DROP USER idldb cascade; for a clean status please.
3. Creating the user IDLDB:
4. CREATE USER IDLDB PROFILE DEFAULT IDENTIFIED BY idldb DEFAULT TABLESPACE KONSIS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
5. Assigning rights:
6. GRANT CONNECT TO IDLDB;
7. GRANT RESOURCE TO IDLDB;
8. GRANT UNLIMITED TABLESPACE TO IDLDB;
9. GRANT CREATE VIEW TO IDLDB;
10. Creating the user IDLADMIN:
11. CREATE USER IDLADMIN PROFILE DEFAULT IDENTIFIED BY admin DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
12. Assigning rights:
13. GRANT CONNECT TO IDLADMIN;
14. Closing SQL-Plus by disconnect.

1. On a computer with Windows put the IDL Konsis-CD "Delivery Database" in the CD device.
2. Start "LieferDB_Oracle.cmd" in the directory "LieferDB" and it opens a "Input-Window".
3. Enter the database name (for example IDLDB).
4. Enter the user name IDLDB.
5. Enter the password for user IDLDB.

Repeat this inputs for two times.

If there exists an export-file of the Oracle database the Import-option of Oracle can be used to load the database. Standard naming of the export-file on IDL Konsis-Installation CD is IDLDB_EXP.DMP, and the Version of the exported Oracle DB is standing in the name of the directory!

The Export-file should be copied before importing it to for reading it from there, because the CD isn't so fast as a disc-device when the IMPORT-Command is activated.

Tip: When you want change datas between two databases with Import and Export, you have look implicitly that the character set are equal of the two databases. Example: Character set "Unicode" and "Not Unicode" are incompatible.

• The User idldb should exist now and be empty!
• If the User idldb isn't empty, the User idldb should be deleted and created new as described in Chapter 4.2.
• The following instructions should be entered into a Prompt (DOS) Window; the path for the parameter FILE and LOG should be customised before:
• - set oracle_sid=idldb
• - imp system/passwort full=y file=C:\idl\import\idldb_exp.dmp log=C:\idl\import\idldbimp.log

The advantage of this procedure is speed and simplicity.

During installations to an existing authority occasionally the request is needed to update the user service class. All tables and views are set to PUBLIC. Through this every user can access all tables. The users of the other authority also therefore come about SQL orders from the IDL Konsis system to the data. To stop this, a role of KONSIS laid out which gets all rights to the tables and views. The PUBLIC rights can be withdrawn from the tables and views again with that. Only the users which will work directly with IDL Konsis get the role of KONSIS allocated now. Scripts (packages) were, made these in the SQL -- can plus be executed for this:

For handling this situation there are defined scripts (packages), which can be executed under SQLPlus.

Konsis_rolle_xx.sql

- generates the role of KONSIS and takes the DBA rights away from the user IDLDB if allocated.

Pck_admin_table_privileges.sql

- generates a package to remove the rights of one user to another user.

Pck_move_index,sql

- generates a package for moving the indicies of one to the other tablespace.

exec_move_public.sql

- example script for moving the Grant rights of PUBLIC to the role of KONSIS.

exec_move_all_index.sql

- example script for moving the indices of Tablespace KONSIS to INDX.

The user IDLADMIN and all further users need the role of KONSIS.

Moving the indices to the Tablespace INDX only is needed if for performance reasons the Tablespace was laid out on another disc.

ATTENTION:

After every IDL Konsis-update, the modification of the rights of the role of KONSIS has to be maintained by using SQL-Plus services again.

The required files are on the IDL Konsis CD "Delivery" in the folder < \DBSAVE\ORACLE\KONSIS-ROLLE-xx-ENG.ZIP. >

Required is the CD: "Oracle 9i Client" Release 2 (9.2.0.1.0)

After mounting the CD the installation-program starts automatically.

Option of target directory:

Select Custom as type of installation.

Available Product Components:

Select additional available product components:

• Oracle Network Utilities
• SQL*Plus
• Oracle Java Utilities (Includes Oracle JDBC/OCI Interfaces and Java Tools)
• Oracle Windows Interfaces (Includes ODBC and OLE DB Provider)
• Oracle Universal Installer 2.2.0.12.0

Component Locations are not to change.

Confirm the "Oracle Services for Microsoft Transaction Server" with Port Number: 2030.

Confirm the summary of selected components to start the installation procedure.

After the installation starts automatically "Net Configuration Assistant" with chapter 5.2.1 - "Welcome".

Welcome: Don't choose "Perform typical configuration".

Directory Usage Configuration: Ignore it with "No....".

Confirm "Naming Methods Configuration.." with < Next >.

Confirm "No, I will create net services names myself...."

Choose "Oracle 8i- or later database..."

"Configuration of Service-name:" Enter the "Service name" from the Oracle database , which is running on the server. Default name is IDLDB

Select the protocol TCP used for the database you want to access.

Enter the host-name for the computer where the database is located. The Host should be reachable via ping in a DOS window! Select standard port number 1521.

A connection test can be performed to the database.

Change the password with the button "Change Login".

Please enter the right password, which you have given by the Oracle server installation for User SYSTEM.

So you get the message Connection...Testsuccessful.

Failing connections can be stored anyhow and can be configured and tested later via the "Net Assistant".

Enter "IDLDB" for this net service name.

Confirm with "NO" for another net service name.

Confirm the message "Net service name configuration complete!" and close the program.

Working connections to the server on Windows 95/98-platforms requires the parameter SQLNET.AUTHENTICATION_SERVICES=(NONE)

in the file < Oracle.Installation.Path >\ Network\Admin\sqlnet.ora.