1Z0-042
Oracle Database 10g: Administration I
Oracle 1Z0-042 Study Guide
© 2005 Self Test Software, a Kaplan IT Company. All rights reserved. No part of this study guide may be used or reproduced in any manner whatsoever without written permission of the copyright holder. The information contained herein is for the personal use of the reader and may not be incorporated in any commercial programs, other books, databases, or any kind of software without written consent of the publisher. Making copies of this study guide or any portion for any purpose other than your own is a violation of United States Copyright laws.
Information has been obtained by Self Test Software from sources believed to be reliable. However, because of the possibility of human error by our sources, Self Test Software, or others, Self Test Software does not guarantee the accuracy, adequacy, or completeness of any information in this study guide and is not responsible for any errors or omissions or the results obtained from use of such information.
Oracle® is a registered trademark of Oracle in the United States and/or other countries.
Self Test Software A Kaplan IT Company 500 Northridge Road Suite 240 Atlanta, Georgia 30350 800-244-7330 www.selftestsoftware.com
Contents
Contents... 3
Pass the Exam with the Self Test Study Guide ... 5
Introducing Oracle 10g Database: Oracle 10g Administration I... 6
Installing Oracle 10g Database... 7
Identifying the Components of Oracle Architecture ... 13
Review Checklist: Introducing Oracle 10g Database: Oracle 10g Administration I ... 21
Creating and Controlling Databases: Oracle 10g Administration I... 22
Describing Oracle Enterprise Manager... 23
Using Database Configuration Assistant ... 25
Controlling a Database... 36
Review Checklist: Creating and Controlling a Database: Oracle 10g Administration I ... 41
Managing Database Storage and Schema Objects: Oracle 10g Administration I . 42
Managing Database Storage ... 43Working with Schema Objects ... 50
Review Checklist: Managing Database Storage and Schema Objects: Oracle 10g Administration I ... 58
Managing Data and Programming with PL/SQL: Oracle 10g Administration I... 59
Using Database Interfaces... 60
Manipulating Data using Data Manipulation Language Statements... 62
Using Data Pump and SQL*Loader ... 65
Programming with PL/SQL... 72
Review Checklist: Managing Data and Programming with PL/SQL: Oracle 10g Administration I... 78
Managing Users and Securing Databases: Oracle 10g Administration I ... 79
Administering Database Users ... 80
Securing Databases... 90
Review Checklist: Managing Users and Securing Databases: Oracle 10g Administration I ... 99
Working with Oracle Net Services: Oracle 10g Administration I... 100
Working with Oracle Net Services ...101
Managing Oracle Listeners and Configuring Oracle Net Services on Oracle Server...104
Configuring Client and Middle-tier Connection ...117
Review Checklist: Oracle 10g Administration 1: Working with Oracle Net Services ...119
Working with Oracle Shared Server: Oracle 10g Administration I... 120
Specifying the Need and Architecture of Shared Server ...121
Configuring Oracle Shared Server...123
Monitoring Oracle Shared Server ...129
Review Checklist: Working with Oracle Shared Server: Oracle 10g Administration I ...133
Performance Monitoring and Proactive Maintenance: Oracle 10g Administration I
... 134
Performance Monitoring...135
Proactive Maintenance...143
Review Checklist: Performance Monitoring and Proactive Maintenance: Oracle 10g Administration I....153
Maintaining Consistency and Concurrency in Databases: Oracle 10g
Administration I... 154
Performing Undo Management...155
Review Checklist: Maintaining Consistency and Concurrency in Database: Oracle 10g Administration I
...165
Performing Database Backup and Recovery: Oracle Database 10g Administration
I ... 166
Describing the Basics of Database Backup, Restore, and Recovery...167
Performing and Managing Database Backups ...170
Performing Database Recovery...179
Review Checklist: Performing Database Backup and Recovery: Oracle Database 10g Administration I 189 Taking the Test Strategies ...190
Pass the Exam with the Self Test Study Guide
As a certification candidate, you’re on the lookout for as many test preparation resources as possible, but your time is at a premium. So, we put together the Study Guide with your study goals and busy schedule in mind.
Create a study plan – and stick to it. Don’t try to cram. Set aside specific study times so you can thoroughly prepare for your exam. In our experience, following a disciplined prep schedule leads to success on Exam Day. To thoroughly gain the benefits of our Study Guide, we recommend that you start preparing about six weeks prior to taking your exam.
Use all the prep tools in your Study Guide. Your Guide contains a full suite of products to help you thoroughly prepare for your exam. Each one is designed with a specific study purpose in mind. Here’s how we recommend you use the products together:
1. Perform a baseline checkup. Set your personal baseline by taking the Self Test Practice Test in
Certification Mode. It is a timed test that simulates the real exam. Objective-based scoring shows you the areas you are relatively strong in, and those you need to devote additional time to.
2. Concentrate your studies by objective. Since your study time has to be in chunks, use the exam’s
structure by objective to help you organize your study sessions. Use each product to study at the objective level with particular emphasis on the objectives where you did not score 100% in your baseline checkup. • Study Guide. Read the Study Guide by objective to familiarize yourself with the exam content. The
Study Guide is objective-driven and contains a Scope and Focused Explanation for each objective. At the end of each major objective is a Review Checklist that lists the key points covered in this area of the exam.
• Self Test Flash Cards. Drill through the Flash Cards by objective to be sure you know the fundamental
concepts. Make Personal Study Notes with these cards to supplement your learning.
• Self Test Practice Test. Use the Practice Test in Learning Mode by objective. Answer the questions,
read the tutorials, and use the Personal Study Notes to supplement your learning and/or highlight items that you’ll want to review before the exam.
• References. Use your favorite references (books, web references, etc.) to get additional materials on
more complex subject matter.
3. Track your progress. You’ve completed your objective-driven study plan. Now you’re ready to see how you’ve progressed. Take the Self Test Practice Test in Certification Mode again. Did you score 100%? If not, go back to your objective study plan and focus on your weaknesses. Keep checking yourself, highlighting objectives that you’ll need to study, until you consistently score 100%.
Do your final preparation. Print the Review Checklists from the Study Guide and the Personal Study Notes from the Self Test Practice Test and Flash Cards. Use these as your condensed final review before taking the real exam. And, before taking the real exam, read the Test-Taking Strategies at the end of this guide to get specific techniques on approaching the different question types you may encounter.
Finally, some last words of advice. During your studies and practice test drills, concentrate on the process - not totally on performance. What matters most is that you are using a disciplined approach that covers the materials on the exam and you know what to expect on exam day. Stay the course of your study plan and you will be ready to PASS
Introducing Oracle 10g Database:
Oracle 10g Administration I
Installing Oracle 10g Database
Scope
• Identify system requirements for installing Oracle 10g.
• Learn how to plan the Oracle 10g installation using Optimal Flexible Architecture (OFA) model. • Understand the benefits of installing Oracle 10g using Oracle Universal Installer (OUI).
Focused Explanation
Oracle 10g Installation Requirements
To install and run an instance of Oracle 10g, you need to verify various installation requirements, such as the release level of the operating system on which installation will be performed, memory requirements, CPU resources, and disk storage space. The following are some hardware requirements for installing Oracle 10g on the Windows platform:
• Memory: Requires a minimum of 256 MB of RAM, but the recommended memory is 512 MB. • Free disk space: Requires a minimum of 1.5 GB available disk space for the Oracle 10g
installation. Additionally, Database Configuration Assistant (DBCA) requires 1 GB for creating a database.
• Swap space: Requires 1 GB or two times the storage capacity of RAM. • Temporary space: Requires 100 MB of disk space in the temp directory.
The following are the hardware requirements for installing Oracle 10g on the Linux and UNIX platforms: • Memory: Requires a minimum of 512 MB of RAM.
• Free disk space: Requires a minimum of 2.5 GB available disk space for the Oracle 10g software installation. Additionally, DBCA requires 1.2 GB for creating a database.
• Swap space: Requires 1 GB or two times the storage capacity of RAM. • Temporary space: Requires 400 MB of disk space in the temp directory.
Oracle Universal Installer (OUI) checks for installation requirements before installing Oracle 10g. OUI aborts and stops the installation process if the installation requirements for Oracle 10g are not fulfilled. You can override this and install the software without the prerequisites being met by providing the ignoreSysPrereqs option.
Planning Installation using Optimal Flexible Architecture Model
You can plan the installation of Oracle 10g using the Optimal Flexible Architecture (OFA) model. Though mainly used for managing Oracle installation on UNIX, this model can also be used for managing
installation on Windows. The OFA model provides naming conventions for UNIX file systems, mount points, directory paths, database files, and Oracle-related files.
Every file on the UNIX platform belongs to an operating system user account. Therefore, before installing Oracle 10g on UNIX, you need to create an operating system user account. Each operating system user account on UNIX belongs to one or more operating system groups.
You need to create an operating system group for the operating system user accounts of Oracle files. The common names recommended in the OFA model for the operating system user accounts of Oracle 10g are oracle, oracle10g, and ora101. The operating system group that owns the operating system user accounts of Oracle is called dba.
UNIX installs Oracle files in logical storage areas called volumes. A UNIX system administrator mounts or attaches these logical storage areas to mount points or directories. The OFA model suggests a naming convention for these directories and mount points. This naming convention specifies that the name of directories and mount points should consist of a combination of character and numeric values. For example, you can specify names, such as /mnt01 and /d01, for mount points and directories on UNIX. The OFA model specifies that the directory structure under the mount points should use meaningful naming conventions. For example, a directory structure under a mount point represented as
/u01/app/oracle shows that the Oracle files are installed as an application under mount point u01 of UNIX.
The OFA model also specifies operating system environment variable names for some of the directory paths. For example, the $ORACLE_HOME environment variable is used to represent the directory path where Oracle 10g is installed. These environment variables help to navigate and provide portability of the directory structure. For example, for the top-level directory of Oracle software on a host server, the OFA model specifies environment variables $ORACLE_BASE on UNIX and %ORACLE_BASE% on Windows. The following are some environment variables:
• ORACLE_BASE – Represents the top-level directory of Oracle software on the host server. • ORACLE_HOME – Represents the directory into which the Oracle 10g software is installed. • ORACLE_SID – Defines the database instance to which an operating system user session
can connect.
• TNS_ADMIN – Specifies the directory where Oracle Net configuration files are stored. • LD_LIBRARY_PATH – Is used on a UNIX operating system to specify the location of the
Oracle shared object library.
The OFA model also provides naming conventions for physical database files. Oracle contains three types of physical database files:
• control files • datafiles • redo log files
Control files use names such as controln.ctl, where n specifies a unique number for duplicate copies of the control file. For example, control01.ctl, control02.ctl, and control03.ctl are the names for multiple copies of a control file.
Datafiles use names such as filenamen.dbf, where n is the number of the datafile in a tablespace. A tablespace is a logical storage area that can contain one or more physical datafiles to store data. The name of datafiles should also describe the tablespace to which they belong. For example, if a tablespace, USERS, is composed of three datafiles, the name of these datafiles will be users01.dbf,
users02.dbf, and users03.dbf.
The OFA model specifies the name redogm.log for redo log files, where g specifies the group number of the redo log and m is the number of the member file in a particular redo log group. For example, if the database has three redo log groups and each redo log contains two members, the name of files for the first redo log group will be redo1a.log and redo1b.log. Similarly, the name of files for the second redo log group will be redo2a.log and redo2b.log.
Installing Oracle 10g Using Oracle Universal Installer on Windows
You can install Oracle 10g using Oracle Universal Installer (OUI), which is a Java-based Graphical User Interface (GUI) application. When you insert the Oracle 10g CD, OUI is invoked automatically on the Windows platform. You can also invoke OUI manually by double-clicking the setup.exe file icon, which is located in the root folder of the Oracle 10g CD. On the UNIX platform, you need to run the
runInstaller script to install Oracle 10g.
Before installing Oracle 10g, the OUI performs some installation checks to install both Oracle 10g software and database from a single CD or multiple CDs. These checks verify whether the operating system required to install Oracle 10g is configured or not. OUI checks all the resources required to install Oracle 10g before installing Oracle 10g. OUI verifies the following resources before installing Oracle 10g:
• Verifies whether the required version of the operating system, supported by Oracle 10g, is installed. For example, you can install Oracle 10g only on a Solaris 2.8, or later, version on the Sun platform.
• Verifies all the kernel parameters required to install Oracle 10g on all UNIX platforms. • Verifies that the required swap space and temporary disk space is available.
• Verifies that the $ORACLE_HOME folder, where you want to install Oracle 10g, is either empty or contains the supported version of software components.
• Verifies that the required operating system patches are installed.
• Verifies that 32-bit Oracle 10g components are not installed in the folder where you want to install 64-bit Oracle 10g, and vice versa.
To install Oracle 10g on Windows using OUI, complete the following steps:
1. Double-click the icon of the setup.exe file for Oracle 10g. The Welcome to the Oracle
Database 10g Installation screen is displayed.
2. Click Next to continue. The Specify File Locations screen is displayed. Figure 1-1 shows the Specify File Locations screen.
Figure 1-1: The Specify File Locations Screen
3. In the Path text box of the Destination pane, type the location to which to install Oracle 10g
database software.
4. Click Next to continue. The Select Installation Type screen appears. This screen displays the
following options:
• Standard Edition – Installs an integrated set of management tools, Web features, and products for creating business-related applications.
• Enterprise Edition – Installs licensable Oracle Database options and database configuration and management tools, in addition to all the products installed during the Standard Edition installation. This edition also installs the products for data warehousing and transaction processing applications.
• Personal Edition – Supports only a single-user development environment. • Custom – Allows you to install components of Oracle 10g individually. 5. Select the Enterprise Edition option for installing all available Oracle 10g features.
6. Click Next to continue to the Select Database Configuration screen. This screen shows the
options to create a starter database.
7. Select the Do not create a starter database option.
8. Click Next to continue to the Summary screen. This screen shows all the options that you
selected on previous screens and lists all the components that will be installed.
9. Click Install to continue to the Install screen. This screen shows a progress bar to indicate the
progress of the installation process. When the installation is complete, the End of Installation screen is displayed.
10. Click Exit to exit the installation.
Installing Oracle 10g on Linux Using OUI
To install Oracle 10g on Linux using OUI, complete the following steps: 1. Set the following environment variables:
• $ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
• $ORACLE_HOME=$ORACLE_BASE/product/10.1.0; export ORACLE_HOME • $ORACLE_SID=orcl; export ORACLE_SID
• $PATH=$ORACLE_HOME/bin:$PATH; export PATH
• $LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH 2. Start the OUI using the runInstaller.sh command:
$ /mnt/cdrom/runInstaller
This command starts the OUI, which then checks for installation requirements to install Oracle 10g.
The Specify Inventory directory and credentials screen is displayed. Type the full path of the inventory directory and operating system group name in the Enter the full path of the inventory
directory text box. The inventory directory stores the installer files to install Oracle products. A
pop-up window is displayed. This window shows that you need to run the orainstRoot.sh script as root from the directory specified in this pop-up window.
4. Run the orainstRoot.sh script from the Linux command line. This script file creates the Inventory pointer file, named oraInst.loc, which directs OUI to the directory path of the inventory location.
5. Click Next to continue. The Specify File Locations screen is displayed.
6. In the Path text box of the Destination pane, type the location to which to install Oracle 10g
database software.
7. Click Next to continue. The Select Installation Type screen is displayed.
8. Select the Enterprise Edition option for installing all available Oracle 10g features.
9. Click Next to continue. The Product-specific Prerequisite screen is displayed. This screen
shows the verification of operating system version and configuration for installing the Enterprise Edition of Oracle 10g.
10. Click Next to continue. The Select Database Configuration screen is displayed. This screen shows the options to create a starter database.
11. Select the Do not create a starter database option.
12. Click Next to continue. The Summary screen is displayed. This screen shows all the options that you selected and lists all the components that will be installed.
13. Click Install to continue. The Install screen is displayed.
This screen shows a progress bar to indicate the progress of the installation process. A pop-up window is displayed at the end of the installation process, displaying the configuration
information.
14. Click OK to continue. The Setup Privileges pop-up window is displayed. This window shows that you need to run a configuration script, root.sh, as root from a directory path specified in this window.
15. Run the root.sh script from the Linux command line. This script prompts for the location of the local /bin directory. Accept the default location by pressing Enter.
16. Return to the Setup Privileges window and click OK to continue. The End of Installation screen is displayed.
Identifying the Components of Oracle Architecture
Scope
• Describe the architecture of an Oracle instance and Oracle database.
Focused Explanation
Oracle Instance Architecture
Oracle instance architecture represents the logical memory structure of the Oracle Server. The Oracle instance contains two components:
• System Global Area (SGA) • Oracle background processes
SGA
The SGA is a shared memory area that Oracle uses to store data and control information for an Oracle instance. It is allocated when an Oracle instance is initiated, and de-allocated when the Oracle instance shuts down. The SGA represents a set of memory blocks that are shared among all the users connected to the Oracle database. The components of the SGA contain data and control information of the Oracle database. The following are the various components of the SGA:
• Shared pool
• Database buffer cache • Redo Log buffer • Java pool • Large pool • Streams pool
Shared pool – Represents the memory area that stores data dictionary cache and library cache. The
data dictionary cache contains structural and data information, such as names of datafiles, names of segments, locations of extents, descriptions of tables, and privileges. The library cache stores the
execution plan of a SQL query and the parse tree of commonly used SQL statements. The shared pool is a required component of the SGA.
Database buffer cache – Represents the memory area that caches the most recently used data blocks.
This component is a required component of the SGA. The database buffer cache contains four types of buffers:
• Dirty Buffers – Contain data that has been modified but not written to the disk.
• Pinned Buffers – Represent buffer blocks that are currently being accessed.
• Cleaned Buffers – Represent the buffers that are unpinned. These buffers can be aged out if their contents are not referenced again.
Note: Oracle uses the Least Recently Used (LRU) algorithm to manage the shared pool and database
buffer cache. This algorithm identifies the least recently used SQL statements or data blocks and uses their space to store new SQL statements or data blocks.
Redo log buffer – Represents the memory area that stores information about database transactions.
This component of the SGA contains information about the changes made to the data in a database. The information in this component is used for recovery from a database failure. The redo log buffer is a required component of an Oracle instance.
Java pool – Represents the memory area that stores information about most recently-used application
code and objects of Java. This component is an optional component of the SGA. The Java pool is used when you work with a Java application that interacts with the Oracle database.
Large pool – Represents an optional memory area of the SGA that caches information for operations,
such as database backup and restore. This component of the SGA also caches message buffers for parallel query.
Streams pool – Represents an optional component of the SGA that stores information about queued
message requests when the advanced queuing option of Oracle is used.
Managing the SGA
Oracle provides two ways to manage the size of the SGA components: • Manual shared memory management
• Automatic shared memory management
With manual shared memory management, you need to specify the initialization value for each
component of the SGA. You can change the initialization values for SGA components according to the requirements of the application that interacts with the Oracle database.
Oracle 10g also provides Automatic Shared Memory Management for managing the SGA. This shared memory management feature automatically sizes database buffer cache, shared pool cache, large pool, and Java pool components of the SGA. These components of the SGA are called auto-tuned
components. You can manage other SGA components manually. These components are called manually-managed SGA components.
Oracle divides memory allocated to the SGA into chunks called granules. The size of the granules is determined according to the value of the SGA_MAX_SIZE parameter. The value of this parameter specifies the maximum size of the SGA. The size of granules is 4 MB if the total size of the SGA is less than 128 MB, and 16 MB if the total size of the SGA is greater than 128 MB.
Oracle Background Processes
Oracle background processes help manage an Oracle instance. The background processes help to maintain and enforce the relationship between physical file structures and logical memory structures of a database. An Oracle instance contains five required Oracle processes and several optional background processes. The following are the required Oracle background processes:
• System Monitor (SMON) • Process Monitor (PMON) • Database Writer (DBWR) • Log Writer (LGWR) • Check Points (CKPT)
The SMON process uses online redo log files to perform instance or crash recovery. This process also performs monitoring of temporary segments and extents. The SMON process cleans up the temporary segments that are no longer in use. This process also collects the contiguous free space from the tablespace to create larger free extents. Oracle periodically calls the SMON process to check whether this process is needed. Other processes can call the SMON process if they detect a need for this process. The PMON process performs clean up and resource recovery of failed user processes. This process takes back resources from failed user processes and releases the locks on tables and rows held by a database user. Oracle periodically calls the PMON process to check whether this process is required.
The DBWR process writes dirty buffers from database buffer cache to database datafiles. This process writes to database datafiles when:
• A transaction is committed
• The database buffer cache is full and needs free space to store new data blocks • A checkpoint occurs
• A timeout occurs
• A tablespace is changed to read-only • A tablespace is placed offline
• A table is dropped or truncated
• A tablespace is placed in backup mode
An Oracle instance contains at least one or more DBWn processes. An Oracle instance can have a maximum of 20 DBWn processes, where the value of n is from 0 to 9 and a to j. For example, if an Oracle instance contains three DBWn processes, the name of these processes will be DBW0, DBW1, and DBW2.
The LGWR process writes the information from the redo log buffer component of the SGA to online redo log files. This process writes information when:
• A transaction is committed.
• The redo log buffer is one-third full.
• A DBWn process writes dirty buffer blocks to datafiles. • A time period of three seconds has elapsed.
• The size of redo records exceeds 1 MB.
• The LGWR process performs two operations simultaneously: o Writes the redo log buffer information to a redo log file.
o Allows the Oracle Server processes to write to the redo log buffer.
The CKPT process updates the datafiles and control files when a checkpoint event occurs. This event flushes dirty blocks from the buffer cache to database datafiles.
Checkpoint events take place when a log switch occurs. A log switch occurs when an online redo log file
fills and Oracle starts writing to the next redo log file.
The following are some of the optional background processes in Oracle 10g:
• Archiver (ARCn) – Copies the information written by LGWR to online redo log files and to a secondary location for recovery.
• Recoverer (RECO) – Recovers failed transactions that are distributed across multiple databases when using the distributed database feature of Oracle.
• Job Queue Monitor (CJQN) – Allocates jobs to job queue processes, using the job-scheduling feature of Oracle.
• Job Queue (Jnnn) – Executes jobs that are scheduled using the job-scheduling feature of Oracle. • Queue Monitor (QMNn) – Monitors messages in a message queue when the Advanced Queuing
(AQ) feature of Oracle is used.
• Parallel Query Slave (Qnnn) – Executes parts of a large query when the parallel query feature of Oracle is used.
• Dispatcher (Dnnn) – Minimizes resources required by handling multiple connections to the database using a limited number of server processes. Dispatcher processes assign database requests of users to a queue where they are processed by Oracle shared server processes. Shared server processes are used when the shared server feature of Oracle is used.
• Shared Server (Snnn) – Creates server processes that are shared among database users when the shared server feature of Oracle is used.
• Memory Manager (MMAN) – Manages auto-tuned components of the SGA when the Automatic Shared Memory Management feature of Oracle is used.
• Memory Monitor (MMON) – Collects and analyzes statistics used by the Automatic Workload Repository (AWR) feature of Oracle.
• Recovery Writer (RVWR) – Writes recovery information to the disk when the flashback recovery feature of Oracle is used.
• Change Tracking Writer (CTWR) – Maintains a record of database blocks that have changed when the incremental recovery feature of Oracle is used.
Configuring an Oracle Instance
An Oracle instance is configured according to the settings in a configuration file, called a parameter initialization file. This file can be of two types:
• Parameter Files (PFILES)
• Server Parameter Files (SPFILES)
Oracle 10g provides more than 250 parameters for configuring Oracle instances. These parameters are stored in a parameter initialization file. Configuration parameters are categorized into two groups:
• Basic initialization parameters – Represent a set of approximately 30 initialization parameters, which are recommended by Oracle to be set manually.
• Advanced initialization parameters – Represents the other initialization parameters, which are stored with their default values. These parameters are set manually only if Oracle support recommends them to meet the specific needs of your application.
The following are some basic initialization parameters specified in the parameter initialization file: • CLUSTER_DATABASE
–
Specifies whether Real Application Clusters (RAC) is enabled. The defaultvalue of this parameter is FALSE.
Note: RAC is a computing environment that uses the processing power of multiple, interconnected
computers on a network.
• COMPATIBLE
–
Enables you to use the new version of Oracle, while maintaining backward-compatibility with the earlier versions installed on your computer.• CONTROL_FILES
–
Specifies the name of the control file(s) associated with a database. • DB_BLOCK_SIZE–
Specifies the size of a database block, in bytes.• DB_CREATE_ONLINE_LOG_DEST_n
–
Specifies the default location of control files and online redo logs.• DB_DOMAIN
–
Specifies the location of a database on a computer network. • DB_NAME–
Specifies the name of a database.• DB_RECOVERY_FILE_DEST
–
Specifies the default location of the flash recovery area. The flash recovery area contains duplicate copies of current control files, online redo logs, archived redo logs, and backup files.• DB_RECOVERY_FILE_DEST_SIZE
–
Specifies the size of the flash recovery area, in bytes. • DB_UNIQUE_NAME–
Specifies the globally-unique name of a database. Databases with the sameDB_NAME parameter are identified with the DB_UNIQUE_NAME parameter.
• INSTANCE_NUMBER
–
Specifies a number that maps a database instance to an integer. This integer is used to identify a database instance in a RAC environment.• JOB_QUEUE_PROCESSES
–
Specifies the maximum number of processes that can be created by Oracle 10g for executing jobs submitted through Enterprise Manager or DBMS_JOBS.• LOG_ARCHIVE_DEST_n
–
Specifies archive log destination locations. You can specify up to 10 archive log destinations.• LOG_ARCHIVE_DEST_STATE_n
–
Specifies the availability state of the corresponding archive log destination locations.• NLS_TERRITORY
–
Specifies the name of the geographical location whose conventions are to be followed for day and week numbering in Oracle 10g.• NLS_LANGUAGE
–
Specifies the default language of a database.• OPEN_CURSORS
–
Specifies the maximum number of open cursors in a user session.• PGA_AGGREGATE_TARGET
–
Specifies the Program Global Area (PGA) memory available to all the processes associated with a database instance.• PROCESSES
–
Specifies the number of operating system processes that can connect to Oracle 10g simultaneously.• REMOTE_LISTENER
–
Specifies a network name that maps to the addresses of Oracle Net Remote listeners.• REMOTE_LOGIN_PASSWORDFILE
–
Specifies whether Oracle 10g checks for a password file. This parameter also specifies the number of databases that can be used in that password file.• SESSIONS
–
Specifies the maximum number of sessions that can connect to a database instance. • SGA_TARGET–
Specifies the size of the System Global Area (SGA) for auto-tuned SGA components,when the Automatic Shared Memory Management feature of Oracle is used.
• SHARED_SERVERS
–
Specifies the maximum number of shared server processes that are created when a database instance is started.• STAR_TRANSFORMATION_ENABLED
–
Specifies whether a cost-based query transformation will be applied when database queries are executed.• UNDO_MANAGEMENT
–
Specifies whether the undo space management is automatic or manual. • UNDO_TABLESPACE–
Specifies the name of the undo tablespace that Oracle 10g uses when adatabase instance is started.
Oracle Database Architecture
Oracle database architecture represents the physical file structure of the Oracle Server. The Oracle database contains physical files, which are stored on the disk drive of a host server. An Oracle database contains three types of physical files:
• Control files • Datafiles • Redo log files
Control files contain information, such as database name, database block size, database character set,
database recovery information, and locations of datafiles and redo log files. Control files are created at the locations specified in the CONTROL_FILES parameter of the initialization parameter file. Oracle stores multiple copies of control files at multiple locations to facilitate database recovery. The CKPT background process automatically updates all the copies of the control files required to maintain consistency of information in all copies of the control files. You can view the names and locations of all the control files by querying the V$CONTROLFILE view.
Datafiles store the data inserted into the tables of a database. The size of a datafile is proportional to the
amount of table data that it stores. Every Oracle database needs to have one or more datafiles. Each datafile belongs to a particular tablespace, which can contain more than one datafile.
The database logically groups related data into tablespaces. For example, a database groups data related to a particular application or function in one or more tablespaces. The tablespaces are composed of segments. Any database object that requires physical storage is called a segment. A database
segment can be a table, an index, a rollback, or a partition. Each segment is composed of extents. A segment contains a minimum of one extent and can have a maximum two billion extents. The extents are composed of a minimum of five data blocks. The data blocks are of sizes 2 KB, 4 KB, 8 KB, or 16 KB. A data block is the smallest unit of storage used by an Oracle database. The operating system files can be of sizes 512 bytes to 2 KB.
Every Oracle 10g database must contain the following three tablespaces: • SYSTEM – Contains data dictionary tables and PL/SQL code.
• SYSAUX – Contains segments used for database options, such as Automatic Workload Repository (AWR) and Online Analytical Processing (OLAP).
• TEMP – Contains temporary data that Oracle generates when processing queries, sorting, and performing database updates. This tablespace is required if the SYSTEM tablespace is created as a locally-managed tablespace.
The Oracle database can also contain other tablespaces, such as TOOLS, USERS, and UNDOTBS1.
Redo log files contain information about all changes to datafiles in a database. Whenever a change to a
datafile takes place in a database, the information to reproduce the change is stored in the redo log buffers. If recovery is necessary, Oracle uses this information to return the database to its prior state. The LGWR background process writes this information to online redo log files.
Oracle creates multiple copies of redo log files at multiple locations because these files are used in recovery of an Oracle database. The set of copies of a redo log file is called a redo log group, and each redo log file in this set is called a redo log group member.
Review Checklist: Introducing Oracle 10g Database: Oracle 10g
Administration I
Identify the installation requirements for Oracle 10g. Discuss planning installation using OFA model. Describe Oracle 10g installation using OUI. Describe Oracle instance architecture. Describe Oracle database architecture.
Creating and Controlling Databases:
Oracle 10g Administration I
Describing Oracle Enterprise Manager
Scope
• Describe various components of Oracle Enterprise Manager.
Focused Explanation
Introducing Oracle Enterprise Manager
Oracle Enterprise Manager is a framework, which provides a set of tools that you can use to create Oracle databases and Oracle database objects. You also use the Enterprise Manager to manage Oracle database space and monitor the performance of an Oracle database. This framework provides Web-based interfaces for managing Oracle components. An Oracle component could be an Oracle database, an Oracle application server, or an application that interacts with an Oracle database. These Oracle components are called managed targets.
Oracle Enterprise Manager uses Management Agents to communicate with managed targets. A Management Agent is a background process, which runs on host computers that contain managed targets. You can use the Management Agent to manage complex databases, such as a database distributed over geographical regions.
Components of Oracle Enterprise Manager
The following are the various components of Oracle Enterprise Manager:
• Managed Targets – Represents Oracle components that are managed using Oracle Enterprise Manager.
• Oracle Management Service – Represents the interface, which is used to monitor and control managed targets. This interface is a Java-based Web component.
• Management Repository – Stores monitoring and configuration information about managed targets. The management repository consists of two tablespaces in an Oracle database. • Oracle Enterprise Manager 10g Grid Control – Provides a Web-based interface to manage
Oracle Server services, such as Oracle application servers, Oracle databases, and Oracle applications, from a centralized location.
• Oracle Enterprise Manager 10g Database Control – Provides a Web-based interface to manage an Oracle database instance or a Real Application Cluster (RAC).
Note: RAC is an Oracle database component that allows a database to be installed across
multiple servers.
• Application Server Control – Provides a Web-based interface to manage an instance of an Oracle application server, a collection of instances of Oracle application servers, or a cluster of instances of Oracle application servers.
Controlling Oracle Management Agents
An Oracle Management Agent collects information about all managed targets and communicates with Oracle Management Service to store this information in the management repository. For example, a Management Agent monitors an Oracle database and sends an alert to the management service, if the database is not available. The Oracle Management Service transfers information collected by
Management Agents to Oracle Enterprise Manager Grid Control. You must start the Management Agent on each host computer of the managed targets to collect information about them.
You use the emctl command-line utility to start and stop the Management Agent. This utility is stored in the ORACLE_HOME/bin directory. You can issue the emctl start agent command to start the Management Agent, and the emctl stop agent command to stop the Management Agent.
Note: ORACLE_HOME is the directory where Oracle 10g software is installed. Using Oracle Enterprise Manager 10g Database Control
Oracle Enterprise Manager 10g Database Control is a Web-based tool that helps to manage an instance of a database. To access this tool, you must start the Database Control Agent, which is a background process that collects information about an instance of a database.
The emctl start dbconsole command is used to start the Database Control Agent. The emctl program is stored in the ORACLE_HOME/bin directory. You can issue the emctl stop dbconsole command to stop the Database Control Agent, and the emctl status dbconsole command to determine whether or not the Database Control Agent is running.
After the Database Control Agent is running, you can access the Oracle Enterprise Manager 10g Database Control tool. To access it, type the address http://hostname:portnumber/em in the Address bar of the Web browser. You can use this tool to perform various administrative tasks, such as starting and shutting down a database.
Note: The default port for Database Control is 5500. You can obtain the information about the port
numbers being used by various Oracle components, from the portlist.ini file. This file is located in the ORACLE_HOME/install directory.
Using Database Configuration Assistant
Scope
• Identify the steps to create, configure, and drop a database.
• Understand how to manage database templates using Database Configuration Assistant (DBCA).
Focused Explanation
Introducing Database Configuration Assistant
Oracle Database Configuration Assistant (DBCA) is a Java-based tool that provides a Graphical User Interface (GUI) to create, configure, and drop databases. This tool can also be used for managing database templates. A database template is an XML file that stores the definition of the database configuration.
You can invoke the DBCA tool on Windows by selecting Start -> Programs -> Oracle Oracle Home ->
Configuration and Migration tools -> Database Configuration Assistant. You can also invoke this
tool from the command line by issuing the dbca command. The DBCA utility is stored in the ORACLE_HOME/bin directory.
Creating and Configuring a Database
To create a database using DBCA, complete the following steps:
1. Invoke the Database Configuration Assistant. The Welcome screen is displayed.
2. Click Next to continue. The Operations screen is displayed. It displays the following options:
• Create a Database – Creates a database. You can create a database using an existing template or create a customized database according to the requirements of your organization.
• Configure Database Options – Enables you to change the configuration definition of an Oracle server to move it from a dedicated Oracle server to a shared Oracle server. This option enables you to add database options that you have not previously configured for the database.
• Delete a Database – Removes a database and all its associated files. • Manage Templates – Manages database templates.
4. Click Next to continue. The Database Templates screen is displayed. The following are the
database options available on the Database Templates screen: • Custom
• Data Warehouse • General Purpose • Transaction Processing
These options are used to create databases that are optimized for a particular workload. For example, you can select the Transaction Processing option to create an Oracle database optimized for transaction processing or the Data Warehouse option for creating an Oracle database optimized for data warehousing.
5. You can select an option from the Database Templates screen and click Show View to view the configuration definition of a template. The configuration definition of a template contains the following fields:
• Common Options – Shows which options will be installed for a database.
• Initialization Parameters – Shows initialization parameters for a database and their settings.
• Character files – Displays the character set used in a database.
• Tablespaces – Shows the name and type of tablespaces in a database.
• Datafiles – Shows the name and size of datafiles for each tablespace in a database. • Redo Log groups – Shows the number and size of redo logs for a database. 6. Select the option from the Database Templates screen to create a new database.
7. Click Next to continue. The Database Identification screen is displayed. This screen contains Global Database Name and SID text boxes.
The Global Database Name text box contains the fully-qualified name of a database. The format of this name is name.domain, where name represents the name of the database and domain represents the domain of the database within an enterprise. The database domain is the same as the network domain within the enterprise. A global database name needs to be unique within a network domain.
The SID text box contains the Oracle System Identification (SID) name that represents the name of the database instance attached to a database. Each database instance associated with an Oracle database should have a unique SID name. The SID name can be a maximum of eight characters.
8. Type the database name and database SID name in the Global Database Name and SID text boxes, respectively.
9. Click Next to continue. The Management Options screen is displayed. Figure 2-1 shows the Management Options screen.
Figure 2-1: The Management Options Screen
This screen displays the options that you can use to manage an Oracle database. There are two ways to manage the database:
• Manage each Oracle database centrally using Oracle Enterprise Manager 10g Grid Control
• Manage each Oracle database as a separate entity using Oracle Enterprise Manager Database Control
Oracle 10g Enterprise Manager Grid control manages each database through a Management Agent. If the Management Agent is installed, DBCA detects the installed Oracle Management Agent and lists the names of the management services for the Management Agent. You can
select one of these management services to manage the database through the installed Management Agent.
If the Management Agent is not installed and you want to manage the database as a separate entity, select the Use Database Control for Database Management option.
From the Management Options screen, you can select Enable Email Notifications to enable the e-mail notification service. This service sends an e-mail message when database thresholds, such as the maximum number of database sessions, are reached. To configure the e-mail notification service, you must type the name of your SMTP mail server in the Outgoing Mail
(SMTP) Server text box, and the e-mail address to which the notification should be sent in the Email Address text box.
You can also select the Enable Daily Backup option to enable daily backups of your database. You must specify the backup start time and an operating system user name and password to configure backup options. Oracle Enterprise Manager starts the backup process at the specified start time using the given user account and password of the operating system.
10. Click Next to continue. The Database Credentials screen is displayed.
This screen contains options to specify the passwords for the user accounts that will be created in the new database. The following are the user accounts created in a new database:
• SYS – Contains the data dictionary.
• SYSTEM – Contains additional administrative views and tables.
• DBSNMP – Provides a user account to Oracle Enterprise Manager for collecting performance statistics about the database.
• SYSMAN – Creates and modifies administrator accounts of Oracle Enterprise Manager. You can select the Use the Same Password for All Accounts option to specify the same password for all user accounts. You can select the Use Different Passwords option to specify a different password for each user account.
Figure 2-2 shows the Storage Options screen.
Figure 2-2: Storage Options Screen
This screen provides options for configuring disk storage areas of a database. The following are the options for configuring the disk storage areas:
• File System – Specifies that the file system will be used for database storage. With this configuration, you can specify the names of datafiles and the locations for storing these datafiles. The File System option is selected by default.
• Automatic Storage Management (ASM) – Specifies that ASM disk groups will be used for database storage. With this configuration, you can define disk groups instead of individually managing the datafiles. A disk group is a logical unit of one or more datafiles. ASM is a new feature available in Oracle 10g. If you select the ASM option, Oracle creates an additional database instance. This database instance manages the storage definition of the database to be created and keeps track of disk group allocation. This additional database instance is known as a secondary ASM database instance.
• Raw Devices – Specifies that raw disk partitions and volumes will be used for database storage. You can use this option if you have a disk that does not contain an operating system-managed file system. The read and write activities on the disk are performed by Oracle.
12. Accept the default option and click Next to continue. The Database File Location screen is displayed. This screen provides the following options:
• Use Database File Locations From Template – Stores the datafiles on locations specified in the configuration definition of the template selected for creating a new database. If you select this option, you can review and change the filenames and locations later when creating the database.
• Use Common Location for All Database Files – Specifies a common directory for all datafiles. If you select this option, you can review and modify the file location later when creating the database.
• Use Oracle-Managed files – Stores the datafiles to an area managed by a secondary ASM database instance. This option is used when you select the Automatic Storage
Management (ASM) option on the Storage Options screen. This option does not allow
you to modify the location of datafiles when creating a database.
13. Accept the default option and click Next to continue. The Recovery Configuration screen is displayed. This screen contains two options for database recovery:
• Specify Flash Recovery Area – Is a new option in Oracle 10g that uses the flash recovery area to perform database recovery. A flash recovery area is an area of the disk that stores and manages files required for database recovery.
• Enable Archiving – Enables the archive logging process. This process enables you to perform point-of-failure recovery of database. After selecting Enable Archiving, you can view and modify the parameters that are used to configure archive logging.
If you select the Custom option from the Database Templates screen, the Database Content screen will contain the options shown in Figure 2-3.
Figure 2-3: Database Content Screen
You can use the options on this screen to select the Oracle database components that you want to install. The following are the various options on this screen:
• Oracle Text – Supports multimedia content, such as audio and video.
• Oracle OLAP – Provides support for creating and deploying online analytical processing applications.
• Oracle Spatial – Manages geographical and spatial information, such as map coordinates.
• Oracle Data Mining – Provides a set of tools and algorithms to support data mining. • Oracle Ultra Search – Provides search capabilities across different data repositories,
including Oracle databases, mail servers, Web servers, and file systems. • Oracle Label Security – Secures information stored in a database.
• Sample Schemas – Creates sample schemas that provide examples on how to configure and use features of the Oracle database.
• Enterprise Manager Repository – Provides the location of the schema that is used for managing the Management Repository.
If you select the General Purpose option on Database Templates screen, the Database
Content Screen will contain the options shown in Figure 2-4.
Figure 2-4: Database Content Screen
15. Select the Sample Schemas check box in the Sample Schemas tab page of the Database
Content screen to install the sample schemas.
16. Accept the default option and click Next to continue. The Initialization Parameters screen is displayed.
This screen helps you to set the various initialization parameters for configuring the database instance. This screen contains four tabs:
• Memory – Contains two options, Typical and Custom. The Typical option allocates memory to the components of the System Global Area (SGA) and the Process Global Area (PGA). The memory allocation process is automatic and uses a percentage of total memory available on the server. The default percentage of memory used by this process is 40 percent. You can click the Show Memory Distribution button to view the memory distribution between the SGA and PGA. The Custom option allows you to manually allocate the memory to the components of the SGA and PGA. These components are the shared pool, the buffer cache, the Java pool, the large pool, and PGA size.
• Sizing – Provides options to specify the block size of a database and the maximum number of simultaneous Oracle processes that can connect to this database.
• Character Sets – Provides the options to set the character set, the national character set, the default language, and the default date format. A character set defines how characters are stored and displayed within an Oracle database. The national character set determines the view of Unicode characters in an Oracle database, if the Oracle database does not use a Unicode-enabled character set. The default language setting determines how an Oracle database displays time and monetary values. The default date setting determines how an Oracle database displays dates.
• Connection Mode – Provides two options that specify the type of connection to be used for this database. The following are the options for the database connection mode:
o Dedicated Server Mode – Allocates a resource dedicated to a client connection to the database. This mode is used when the number of client connections is small and the client makes persistent and long-running requests to the database.
o Shared Server Mode – Shares a pool of resources among the client connections to the database. This mode is used when the number of client connections is large.
17. Select an option from the Connection Mode tab page.
18. Click Next to continue. The Database Storage screen is displayed.
You can review and change the location of the datafiles, control files, and redo log files on this screen.
19. Click Next to continue. The Creation Options screen is displayed. This screen provides two options:
• Create Database – Creates a database immediately.
• Save as a Database Template – Saves a database definition as a template, which you can later use to create a database.
20. Select the Create Database option.
21. Click Finish. The Confirmation screen is displayed. This screen shows the summary of the configuration options that you selected for creating a database.
Configuring a Database Using DBCA
You can review and change the configuration of an existing Oracle database using DBCA. To change the configuration of an existing database, perform the following steps:
1. Start the DBCA tool. The Welcome screen is displayed.
2. Accept the default option and click Next to continue. The Operations screen is displayed. Select the Configure Database Options option on the Operations screen.
If the database is not started, the DBCA utility starts the database automatically. To configure the
database, you must connect to the database as a user that has DBA authority. After selecting and starting the database, you can add options that are not currently installed in your database.
Deleting a Database Using DBCA
You can delete an existing database using DBCA. To delete a database, perform the following steps: 1. Start the DBCA tool. The Welcome screen is displayed.
2. Accept the default option and click Next to continue. The Operations screen is displayed. 3. Select the Delete A Database option on the Operations screen.
4. Accept the default option and click Next to continue. DBCA shows a list of all the existing databases.
5. Select the database to be deleted.
6. Click Finish to delete the database and remove all files on the disk associated with the selected
database.
Managing Database Templates Using DBCA
DBCA stores the configuration definition of an Oracle database instance in an XML file, which is called a
database template. You can use an existing database template for creating a new database and
The default storage location for a database template is ORACLE_HOME/assistants/dbca/templates. DBCA uses two types of database templates:
• Seed Template – Contains database definition information, datafiles, and redo log files. DBCA makes copies of datafiles and redo log files present in the database definition file. These datafiles contain database schema, which speeds up the process of creating a new database. Seed templates have the .dbc extension. The datafiles and redo log files associated with the seed template are stored with the .djf extension. When you use a seed template, you can change the name of a database, configuration of initialization parameters, and locations of control and redo log files.
• Non-seed Template – Contains only the database definition and does not store datafiles and redo log files. This template has the .dbt extension. When you use a non-seed template, you cannot change the name of a database, configuration of initialization parameters, or locations of control and redo log files.
Creating a Template Definition
To create a template definition, select the Manage Templates option on the Operations screen and click
Next. The Template Management screen is displayed. This screen contains three options to create a
template definition:
• From an existing template – Creates a template definition from an existing template definition. This option enables you to review and modify settings, such as initialization parameter settings and datafiles storage characteristics, of an existing template. However, to create a template from an existing database, you must connect to the database to enable DBCA to obtain information about the database.
• From an existing database (structure only) – Creates a template according to the structure of an existing database. This option is used to create a template for a database that has the same structure as another existing database but contains no data.
• From an existing database (structure as well as data) – Creates a template according to the structure of an existing database. This template is used when you want to create an exact copy of an existing database.
After selecting an option to create a template definition, you can use DBCA to configure the template definition. DBCA presents a set of screens containing fields to configure the template definition.
Deleting a Template Definition using DBCA
You can delete an existing database template using DBCA. To delete an existing database template, select the Delete a database template option from the Template management screen. You can then select the template that you want to delete. When you delete the database template, DBCA removes the XML file of the deleted template from the database.
Controlling a Database
Scope
• Define how to handle parameter initialization files.
• Identify the steps to start up and shut down an Oracle database. • Identify the type of information stored in the alert log file.
Focused Explanation
Handling Parameter Initialization Files
Each Oracle instance is configured according to the settings of a configuration file, called the parameter
initialization file. This file stores the initialization parameters used when an Oracle database instance
starts. Initialization parameter files are of two types:
• Parameter Files (PFILES) – Are text files that store initialization parameters.
• Server Parameter Files (SPFILES) – Are binary files that store initialization parameters. The default location for PFILES and SPFILES is $ORACLE_HOME\dbs on UNIX, and
%ORACLE_HOME/database on Windows.
You can specify a name for the initialization parameter file to use, when you issue a STARTUP command to start a database. At database startup, Oracle searches for the parameter initialization file to configure the Oracle database instance. If you do not specify the name of a parameter initialization file to use at startup, Oracle first searches for the spfileSID.ora file. The SID is the unique name of the database instance. If Oracle cannot locate this file, it searches for the spfile.ora file. Finally, if the server file is not found, Oracle searches for the initSID.ora parameter file.
You can set the initialization parameters manually or use Enterprise Manager Database Control to modify the parameters.
Startup Modes and Options of Oracle 10g Database
An Oracle database can be started in one of several different modes, depending on the option used with the STARTUP command to start the instance.
The following summarizes the STARTUP commands that can be used to start an Oracle database: • STARTUP NOMOUNT – Starts a database instance without mounting the database. A database
started in this mode reads the parameter file and initiates Oracle background processes and Oracle memory structures. In this mode, the background processes and Oracle memory
structures do not establish communication with the disk structure of the database. If a database is started using the STARTUP NOMOUNT command, you can perform tasks such as running a script that creates a database.
• STARTUP MOUNT – Starts a database instance and establishes communication between background processes and Oracle database memory structures. In this mode, you can perform all the tasks that can be performed if the database was started with the STARTUP NOMOUNT command, as well as some administrative tasks, such as recovery of a database, changing file locations, and enabling the archive log mode.
• STARTUP OPEN – Starts a database instance and makes the database available to all users. This is the default startup mode, if you do not specify a startup mode option with the STARTUP
command.
The following are some special STARTUP commands used to start an Oracle database:
• STARTUP FORCE – Performs a shutdown abort and then restarts the database instance. This STARTUP command is used if normal startup does not work. This startup mode option can be used with the STARTUP command for every database.
• STARTUP RESTRICT – Starts a database instance and mounts the database in a restricted mode to provide access only to users who have the RESTRICTED SESSION privilege. When the database is started in this mode, you can perform various tasks, such as maintaining the
database while the database is open, and importing and exporting the database. You can disable the restriction using the ALTER SYSTEM DISABLE RESTRICTED SESSION statement.
Executing this statement allows every user to connect to the database.
Shutdown Options in Oracle 10g
An Oracle database can be shut down using various methods. The following provides a summary of the SHUTDOWN commands you can use to shut down a database:
• SHUTDOWN NORMAL – Shuts down the database normally. Using this SHUTDOWN command ensures that no new connections are established after the command is issued. The database waits until all user connections are disconnected from the database. This option is the default SHUTDOWN command option.
• SHUTDOWN TRANSACTIONAL – Shuts down the database normally, while allowing active transactions to complete. Using this SHUTDOWN command ensures that no new connections are established after the command is issued. If all active transactions are complete, all client connections are disconnected.
• SHUTDOWN IMMEDIATE – Shuts down the database immediately. Using this SHUTDOWN command ensures that no new connections are established after the command is issued, rollbacks occur for all uncommitted transactions, and the Oracle database does not wait for clients to disconnect.
• SHUTDOWN ABORT – Shuts down the database immediately by aborting the database. Using this SHUTDOWN command ensures that no new connections are established after the command is issued. In addition, all SQL statements are terminated, regardless of their state. With this shutdown mode, the Oracle database does not roll back the uncommitted transactions. This shutdown mode disconnects all the client connections immediately. When you shut down the Oracle database using this command, you would need to perform database recovery when you restart the database.
The shutdown commands SHUTDOWN NORMAL, SHUTDOWN TRANSACTIONAL, and SHUTDOWN IMMEDIATE do not require database recovery when you restart the database. These shutdowns are called clean shutdowns.
Shutting Down Oracle Using Enterprise Manager Database Control
To shut down Oracle 10g using Enterprise Manager Database Control, complete the following steps: 1. Invoke Enterprise Manager Database Control using the Web browser.
2. Type the user name and password in the User Name and Password text boxes and connect as a SYSDBA user. The Database:oracle page is displayed.
3. Click the Shutdown button under the general section of the Database:oracle page. The Startup/Shutdown: Specify Host and Target Database Credentials page is displayed.
You must provide the user name and password of the operating system and target database computer, as shown in Figure 2-5.
Figure 2-5: Startup/Shutdown: Specify Host and Target Database Credentials Page
After authentication, the Startup/Shutdown: Confirmation page is displayed. The default shutdown option for the database is SHUTDOWN IMMEDIATE.
Database Alert Log
A database alert log is a file that contains information about certain activities, such as database startup and shutdown and internal Oracle errors.
The following are various events and actions stored in the alert log file: • Startup and shutdown information
• Information about administrative actions that have occurred, such as issuing ALTER SYSTEM and ALTER DATABASE statements
• Information about Oracle internal errors that have occurred
• Information about each initialization parameter having a different value from its default value The location of the alert log file is specified by the BACKGROUND_DUMP_DEST initialization parameter.
