module2-oracle_server.htm; updated 5/20/2013.  Some figures shown in these notes are from various Oracle documents including the Oracle® Database Concepts 11g Release 2 (11.2), Oracle® Enterprise Manager Concepts 11g Release 2 (11.2), Oracle® Universal Installer and OPatch User's Guide 11g Release 2 (11.2) for Windows and UNIX

 

Module 2 – Oracle Server

 

Objectives

 

These notes familiarize you with database administration software used by a DBA including:

·        Oracle Universal Installer (OUI)

·        Oracle SQL*Plus

·        Oracle Database Configuration Assistant (DBCA)

·        Oracle Enterprise Manager (OEM)

·        Database Upgrade Assistant (DBUA)

 

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.

SQL*Plus

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 11g.

 

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 Oracle Universal Installer (OUI) and Database Configuration Assistant (DBCA) both prompt for a password during software installation.

·        SYS is the owner of the data dictionary.

·        SYSDBA and SYSOPER Privileges. 

o   When you connect to a database as SYS, it is made by specifying that the connection is made as either SYSDBA or SYSOPER. 

o   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: <the password does not display>

Last login: Mon May 20 12:25:16 2013 from 24-207-183-37.dhcp.stls.mo.charter.com

ORACLE_SID = [dbock] ? DBORCL          <Note: This sets the ORACLE_SID for the database>

ORACLE_SID  = DBORCL                   <The ORACLE_SID and ORACLE_HOME echo>

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

 

 

/home/dbock

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

 

<here the user connects to sqlplus in nolog mode>

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

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 20 23:25:33 2013

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

login.sql loaded.

SQL>

 

<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>

<here the user is executing an SQL SELECT statement to retrieve database data>

SQL> select table_name from dba_tables

  2  where owner='DBOCK';

More...

 

TABLE_NAME

------------------------------

ROOM

BEDCLASSIFICATION

BED

PATIENT

PATIENTNOTE

DEPARTMENT

EMPLOYEE

DEPENDENT

SPECIALTY

EMPLOYEESPECIALTY

PROJECT

More...

 

conn

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 and administrative scripts used to track database usage.

·        Here the database user connects as SYSTEM using role SYSDBA.

 

SQL> connect system as sysdba

Enter password:

Connected.

login.sql loaded.

SQL>

 

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 after creating an 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 it easier to work with databases. 

 

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 11.2.0.3.0 Production on Mon May 20 23:32:58 2013

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

login.sql loaded.

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 Universal Installer

 

The Installation Manual for the Oracle Universal Installer (OUI) is available online at: http://docs.oracle.com/cd/E11857_01/em.111/e12255/oui1_introduction.htm

 

For users with CD-ROM with the Oracle Enterprise Server software, the OUI is part of the CD-ROM bundle

 

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

 

It requires about 200Mb for OUI files on Windows, and 116Mb for Unix and Linux installations.

 

Oracle Universal Installer 11g Release 2 (11.2) offers the following features:

 

·         An XML-based centralized inventory.  The XML format enables third-party Java applications to query the inventory for information about installed software.

 

·         Cloning of existing Oracle home. 

o    Enables copying an existing Oracle home to another location and "fix it up" by updating the installation configuration to be specific to the new environment.

o    Cloning makes it easy to propagate a standard setup without having to install and configure after installation.

 

·         Better support for cluster environments

o    Oracle Universal Installer now replicates its inventory to all nodes that participate in a cluster-based installation.

o    You can invoke Oracle Universal Installer from any node on the cluster that is part of the installation.

o    You can then upgrade, remove, or patch existing software from any node.

 

·         True silent capability

o    When running Oracle Universal Installer in silent mode on a character mode console, you no longer need to specify an X-server or set the DISPLAY environment variable on UNIX.

o    No GUI classes are instantiated, making the silent mode truly silent.

 

·         Ability to record your Oracle Universal Installer session to a response file

o    This feature makes it easy to duplicate the results of a successful installation on multiple systems.

o    All the options you selected during the installation are saved in the resulting response file.

 

·         More accurate disk space calculations

o    Oracle Universal Installer now uses a more accurate method of calculating the disk space your Oracle products require.

o    This feature reduces the risk of running out of disk space during an installation.

 

·         Automatically launched software after installation

o    Some Oracle products now take advantage of a new feature that enables the software to launch automatically immediately after the installation.

 

·         Cleaner deinstallation and upgrades

o    Deinstallation completely removes all software, leaving no "bits" behind.

o    This also completely removes files associated with configuration assistants and patchsets.

o    Oracle homes can also be removed from the inventory and registry.

o    For deinstalling 11.2 Oracle Clusterware, Database, and client homes, OUI prompts you to run the deinstall/deconfig utility from the home.

 

·         Integrated prerequisite checking

o    Provides a prerequisite checking tool to diagnose the readiness of an environment for installation.

o    The prerequisite checks are run as part of the installation process, but can also be run as a separate application.

 

·         Support for Desktop Class and Server Class.  The following installation types are available for the database:

o    Desktop Class

§  Choose this option if you are installing on a laptop or desktop class system.

§  This option includes a starter database and provides minimal configuration.

§  This option is designed for users that want to quickly bring up and run the database.

 

o    Server Class

§  Choose this option if you are installing on a server class system, such as what you would use when deploying Oracle in a production data center.

§  This option provides more advanced configuration options.

§  Advanced configuration options available using this installation type include Oracle RAC, Automatic Storage Management, backup and recovery configuration, integration with Enterprise Manager Grid Control, and more fine-grained memory tuning, as well as other options.

§  For the Server Class option, the Typical Installation method is selected by default.

§  It enables you to quickly install the Oracle Database using minimal input.

§  This method installs the software and optionally creates a general-purpose database using the information that you specify in this dialog.

 

Utilities

Oracle offers two utilities for software deployment:

·         Oracle Universal Installer to install Oracle products

 

·         OPatch to apply interim patches.

o    OPatch is an Oracle-supplied utility that assists you with the process of applying interim patches to Oracle's software.

o    OPatch 11.2 is a Java-based utility that can run on either OUI-based Oracle homes or standalone homes.

o    It works on all operating systems for which Oracle releases software.

o    For more information on OPatch, see the Oracle OPatch User's Guide.

 

Oracle Home

An Oracle home is the system context in which the Oracle products run.

 

The Oracle Universal Installer supports the installation of several active Oracle homes on the same host.

·         An Oracle home is a directory into which all Oracle software is installed.

·         This is pointed to by an environment variable named ORACLE_HOME.

 

This context consists of the following:

 

·         Directory location where the products are installed

·         Corresponding system path setup

·         Program groups associated with the products installed in that home (where applicable)

·         Services running from that home

 

Oracle Base

The Oracle base location is the location where Oracle Database binaries are stored.

 

·         During installation, you are prompted for the Oracle base path.

·         Typically, an Oracle base path for the database is created during Oracle Grid Infrastructure installation.

·         To prepare for installation, Oracle recommends that you only set the ORACLE_BASE environment variable to define paths for Oracle binaries and configuration files.

·         Oracle Universal Installer (OUI) creates other necessary paths and environment variables in accordance with the Optimal Flexible Architecture (OFA) rules for well-structured Oracle software environments.

 

For example, with Oracle Database 11g, Oracle recommends that you do not set an Oracle home environment variable allow OUI to create it instead.

 

If the Oracle base path is /u01/app/oracle, then by default, OUI creates /u01/app/oracle/product/11.2.0/dbhome_1

 as the Oracle home path

 

 

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

 

Note: At SIUE, we use the vncserver and vncviewer products for UNIX/LINUX to provide a graphical user interface environment.  We will not be using these in class; however, they are readily available for use if needed as free downloads from the web.

 

·        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 a Windows Server, the installation program is setup.exe on 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:

 

$ ./runInstallerresponsefile 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/11g/inventory/Scripts/install1.jar"

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

ORACLE_HOME_NAME="Ora10g"

TOPLEVEL_COMPONENT={"oracle.server", "11.2.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="DBORL.isg.siue.edu"

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

s_dbSid="DBORCL"

b_createDB=true

 

Sample Screen Shots - Oracle Database 11gR2 Installer

 

This section provides screen shots of the installation of Oracle Database version 11gR2 software.  The installation is accompished through use of a series of GUI screens that take you through 11 steps.  We skip some of the steps.

 

Step 1 is used to specify your email so you can receive security updates from My Oracle Support.  My Oracle Support is NOT a free site - you must have a paid up license to access the site.

 

 

Step 2 enables downloading the Zip files that contain the binaries for Oracle 11gR2.

 

 

In Step 3 you have three options as shown by the radio buttons. Here the database software only was installed. The other two options enable create and configure a database as well as upgrade an existing database.

 

 

Step 4 enables you to select the type of installation.  Here the single instance database installation option was selected.

 

 

We skipped Step 5--it is used to select the product language. 

Here in Step 6 the edition of the RDBMS software is selected.  Notice the space requirements are given as estimates next to the options.

 

 

Step 7 enables you to specify the values for ORACLE_BASE and ORACLE_HOME in terms of the directories that serve as those values. 

 

 

In Step 8 you can name the DBA administrative group at the operating system level.  Normally you might name the group "dba" - here the administrator installing the software named the group "dba1".

 

 

In Step 9 the installer checks the operating system to determine if the minimum requirements for installation are met.  Mostly it is checking for available memory.

 

 

In Step 10, the screen shows a recap of the installing options selected. 

 

 

Step 11 shows the actual progress of the installation of the product.  For the single instance installation, this took about 40 minutes to install.

 

 

As Step 11 progressed, various script windows would pop up directing the administrator to perform various tasks.  Here the task displayed is to run a script named root.sh.  At SIUE a different group (the operating system folks) has to run this script as the database administrator group does not have "root" level operating system permissions (nor do they want such permissions).

 

 

The Finish screen shot is now shown - it just says you have finished.

 

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.

 

Oracle Enterprise Manager

 

The Oracle 11g 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 OEM is a Web-based, Grid Control Console with multiple tabs that looks like the following:

·        This is the Home screen. 

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

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

·        The management server is monitoring 40 targets. Of these 17 are up, and 15 are down while 7 are unknown. The unknown are probably student databases that no longer exist.

 

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 above is the Targets screen.  This shows two target servers – sobora1.isg.siue.edu and soroba2.isg.siue.edu. 

 

 

 

This screen within Targets shows the Databases (note the menu options on the blue bar).

·        The DBORCL.siue.edu database has a status of up with 0 critical alerts. 

·        The version of Oracle is 11.2.0.3.0. 

·        The ORACLE.siue.edu database also has a status of up with 0 critical alerts and 9 warnings, and it also runs on Oracle RDBMS 11.2.0.3.0. 

·        DBORCL is located on SOBORA2 while the ORACLE database is on SOBORA1.

 

 

 

This is the Deployments screen. It shows that all critical patch advisories for the Oracle RDBMS installations are up to date.  There are two being monitored:  an Oracle 10g version and an Oracle 11g version. 

 

 

 

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