module2-oracle_server.htm; updated 5/28/2009. Figures shown in these notes are from Oracle document D11321GC11, Production 1.1, December 2001, D34286, Oracle® Database Concepts 10g Release 1 (10.1) Part Number B10743-01, and Oracle® Enterprise Manager Concepts 10g Release 1 (10.1) Part No. B12016-02.

Module 2 – Oracle Server

Objectives

These notes familiarize you with database administration software used by a DBA including Oracle Universal Installer, SQLPlus, and the Oracle Enterprise Manager software.

Database Administration Software

This table details the Oracle Database Administration Software.

DBA Software	Software Description
Oracle Universal Installer	This software is the standard software used to install, modify (upgrade), and remove Oracle software components for all Oracle products.
Oracle Database Configuration Assistant	This is a GUI tool that can be used to create, delete, or modify databases; however, it does not provide a lot of control on the database creation process.
SQLPlus	Used by the DBA and system users to access data in an Oracle database.
Oracle Enterprise Manager	A GUI tool for administering one or more databases.
Database Upgrade Assistant (DBUA)	Can be started in command line mode (command is dbua) for LINUX, or by selecting the DBUA from the Oracle Configuration and Migrations Tools menu option – this upgrades Oracle databases to version 10g.

Oracle Universal Installer

The Installation Manual for the Oracle Universal Installer (OUI) is available on CD-ROM with the Oracle Enterprise Server software CD-ROM bundle. It is also available by download from the web at: http://docs.oracle.com

The OUI is Java-based and enables installation for all Java-enabled operating system platforms – this makes the installation process common across platforms.

The OUI performs the following tasks:

v Provides an XML-Based centralized inventory – the XML format allows third-party Java applications to query the inventory for information about installed software.

v Detects currently installed components including dependencies among the components and installs software as appropriate – this works for all nodes in a clustered installation.

v Enables installation remotely over HTTP.

v Is used to remove installed products – use this instead of the Windows Operating System Software Install/Uninstall.

v Keeps an inventory of products on the client including product names, versions, and the names of the Oracle Home directory where software is installed.

v Can be used in either interactive or non-interactive mode using a response file.

The ORAPARAM.INI File

The oraparam.ini file is used to provide initialization parameters for the OUI. These parameters specify the behavior of specific OUI parameters, and each product installation has a unique oraparam.ini file.

Generally you will not need to edit the oraparam.ini file, but understanding its contents can help you to troubleshoot problems that may occur. For example:

· OUI provides a default value for most installations on the File Locations page that points to the location of the product's installation kit or stage. This default value is stored in the oraparam.ini file.

· The oraparam.ini file also identifies the location of the Java Runtime Environment (JRE) required for the installation.

· In the staging area, it is located in the same directory as the executable file.

Installation Modes – OUI supports installation in 3 modes

· Interactive:

§ Use the graphical user interface to walk through the installation by responding to dialog prompts.

§ A good mode for installing a small number of products on a small number of computers

· Suppressed:

§ Provide installation information by using a combination of a response file or command line entries with certain interactive dialogs.

§ You can choose which dialogs to suppress by supplying the information at the command line.

§ This method is most useful when an installation has a common set of parameters that can be captured in a response file, in addition to custom information that must be input by hand.

· Silent:

§ Use OUI's silent installation mode to bypass the graphical user interface and supply the necessary information in a response file.

§ This method is most useful when installing the same product multiple times on multiple machines.

Startup

Initially the OUI performs environment checks to see if the environment meets the requirements of the software to be installed. Results of prerequisite checks are logged to all results are logged in the installActions<timestamp>.log file

On a LINUX/LINUX server, the installation program is runInstaller on the INSTALL\install\linux directory of the CD-ROM provided by Oracle when you negotiate a lease for the software. The command to install Oracle is:

$ ./runInstaller

On Windows NT or 2000, the installation program is setup.exe on the install/win32 directory of the CD-ROM. The command to install Oracle is:

D:\> setup.exe

If a response file approach is desired, file templates are available for LINUX in the stage/response directory and for Windows in the Response directory. The commands to install using a response file for LINUX and Windows are:

$ ./runInstaller –responsefile filename [-silent] [-nowelcome]

D:\> setup.exe –responsefile filename [-silent]

where filename = the name of the response file; silent runs the installer in silent mode without feedback; and nowelcome means the Welcome window does not display.

A sample response file set of commands for LINUX is shown here—you do not need to try to memorize this set of commands—they are simply provided so that you will have some idea of what the response files looks like.

[General]

RESPONSEFILE_VERSION=1.7.0

[Session]

LINUX_GROUP_NAME="dba"

FROM_LOCATION='/u01/app/oracle/product/10g/inventory/Scripts/install1.jar"

ORACLE_HOME='/u01/app/oracle/product/10g"

ORACLE_HOME_NAME="Ora10g"

TOPLEVEL_COMPONENT={"oracle.server", "10.1.0.2.0"}

SHOW_COMPONENT_LOCATIONS_PAGE=false

SHOW_SUMMARY_PAGE=false

SHOW_INSTALL_PROGRESS_PAGE=false

SHOW_REQUIRED_CONFIG_TOOL_PAGE=false

SHOW_OPTIONAL_CONFIG_TOOL_PAGE=false

SHOW_END_SESSION_PAGE=false

NEXT_SESSION=true

SHOW_SPLASH_SCREEN=true

SHOW_WELCOME_PAGE=false

SHOW_ROOTSH_CONFIRMATION=true #Causes the root.sh script to run.

SHOW_EXIT_CONFIRMATION=true

INSTALL_TYPE="Typical”

s_GlobalDBName="u01.us.oracle.com"

s_mountPoint="/u01/app/oracle/10g/dbs"

s_dbSid="DBORCL"

b_createDB=true

Oracle Database Configuration Assistant

This assistant is covered in more detail in a later lesson. It allows you to:

· Create a database

· Configure database options

· Delete a database

· Manage templates used for these tasks.

Special Database Administrative Users

Database administrators require extra privileges in order to administer an Oracle database. In a LINUX and Windows environment, some of these privileges are granted by assigning user accounts to special groups. On our LINUX server, you'll find that your account is assigned to the DBA group.

There are two special database user accounts named SYS and SYSTEM that are always created automatically whenever an Oracle database is created. These accounts are granted the role DBA which is a special role that is predefined with every database and has all of the system privileges needed to perform DBA activities.

SYS:

· In the past, the user SYS was identified initially with the password change_on_install; however, now the OUI and DBCA both prompt for a password during software installation.

· SYS is the owner of the data dictionary.

· SYSDBA and SYSOPER Privileges – when you connect to a database as SYS, it is made by specifying that the connection is made as either SYSDBA or SYSOPER. These are two special privilege classifications used to identify DBAs and privileged connections are enabled through use of a password file that is discussed in detail in a later module.

· Here is a sample connection script for a connection to the sobora2.siue.edu server at SIUE by the user dbock, and then to the DBORCL database as the user SYS with the special privileged connection as SYSDBA using SQLPlus. Note that when queried for the ORACLE_SID (Oracle system identifier that identifies the name of the database), that dbock responded with DBORCL.

<invoke a PuTTY window from MS Windows by starting up PuTTY>

login as: dbock

dbock@sobora2.isg.siue.edu's password:

Last login: Thu May 28 11:37:02 2009 from reserved164-19.fh.siue.edu

ORACLE_SID = [dbock] ? DBORCL

ORACLE_SID = DBORCL

ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1

/home/dbock

<here the user connects to sqlplus in nolog mode>

dbock/@sobora2.isg.siue.edu=>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 28 12:46:44 2009

login.sql loaded.

<here the user connects as the user SYS with privileges as SYSDBA>

SQL> connect sys as sysdba

Enter password: <no password is needed by a user validated as a member of the Linux DBA group>

Connected.

login.sql loaded.

SQL>

SYSTEM:

· The user SYSTEM was identified initially by the password manager in the past, but now the OUI and DBCA both prompt for passwords during software installation.

· Tables and views created/owned by the user SYSTEM contain administrative information used by Oracle tools such as Oracle developer and designer.

Additional DBA accounts may be created for a database to perform routine day-to-day administrative duties. The passwords for SYS and SYSTEM should be immediately changed for the Oracle database.

SQLPlus

As you saw above, you can connect to SQLPlus in order to do the following:

· Work with a database.

· Startup and shutdown a database.

· Create and run queries, modify row data, add rows, etc.

SQLPlus includes standard SQL plus additional add-on commands, such as the DESCribe command that Oracle provides to make working with databases easier.

When you use SQLPlus for startup and shutdown of your own database, you will connect using /nolog mode, then connect as SYSDBA. The following sequence fails for databases (such as DBORCL) that are protected by a password file that authorizes special accounts to connect with SYSDBA privileges.

dbockstd/@sobora2=>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 28 12:46:44 2009

SQL> connect / as sysdba

ERROR:

ORA-01031: insufficient privileges

The sequence of commands shown above WILL work in an environment where password files are not used to authorize special account connections – where the DBA has decided the environment is secure enough to rely on belonging to the operating system DBA group to validate database administrator access to the database.

Oracle Enterprise Manager

The Oracle 10g Oracle Enterprise Manager (OEM) is a GUI, Internet-based product that executes inside a web browser such as Internet Explorer. The OEM:

· Enables you to manage a number of Oracle tools and services

· Manage the network of management servers and intelligent agents used to track and manage Oracle databases.

· Manage multiple databases from a single client platform.

The 10g OEM is a Web-based, Grid Control Console with multiple tabs that looks like the following:

· Note the different tabs to access different components of the console.

· You can choose the targets (databases, application servers, etc.) to monitor.

Information that OEM needs in order for a DBA to manage databases is stored in the OEM repository.

· The OEM repository is a database itself of information about databases.

· You can install OEM as a separate database on a server, or as a tablespace within an existing database.

· We have installed OEM at SIUE in a separate database.

The OEM architecture is illustrated here.

This is an n-tier architecture shown in the figure above is used by OEM.

First Tier: The first tier includes client computers that provide a graphical user interface for DBAs.

Second Tier: The second tier includes the Management Service (a J2EE Web App) and the accompanying database repository. The Management Service is a program that executes on the server where the OEM repository/database is located. The Management Service is started on our LINUX machine as shown in these commands. In order to stop the service, you must be a privileged user of the Enterprise Manager repository (which you as students are not).

$ oemctl

Usage: oemctl start oms

oemctl stop oms <EM Username>/<EM Password>

oemctl status oms <EM Username>/<EM Password>[@<OMS-hostname>]

Third Tier: A group of Oracle Management Agents manage various targets such as databases, application servers, listeners, and hosts that can be on different network nodes in the organizational network, and these agents execute tasks from the Management Server. The ID for the intelligent agent is dbsnmp.

The Enterprise Manager is very flexible. Here is an example web page used to manage a Website and to track availability of the page.

This figure shows the Database Management Home Page. It shows the status of the instance, host name, listener, Oracle Home, alert log status, and other facts that a DBA will want to monitor.

The Enterprise Manager is a very complex tool. Oracle corporation offers a 3 to 4 day course of study to teach the detailed usage of the Enterprise Manager product.

END OF NOTES