CIS 421b: Database Administration I

Chapter 2: Overview of Database Administrator (DBA) Tools


This chapter introduces major components of Oracle9i. The objectives important to this chapter are:

  1. Oracle9i DBA tools
  2. Configuring Oracle Net for your database
  3. Memory and background process components of your instance
  4. Using Enterprise Manager

The text lists eighteen DBA management tools included in Oracle9i.

Ten tools are available only in Enterprise Manager:

  • Analyze Wizard
  • Backup Wizard
  • Export Wizard
  • Import Wizard
  • Instance Manager
  • Log Miner
  • Schema Manager
  • Security Manager
  • Storage Manager
  • Summary Advisor Wizard

Six tools are available only from a command line:

  • Data Migration (Upgrade) Assistant
  • Database Configuration Assistant
  • Enterprise Manager Configuration Assistant
  • Enterprise Manager Console
  • Net Configuration Assistant
  • SQL*Plus

Only two are available from both Enterprise Manager and from a command line:

  • Net Manager
  • SQL*Plus Worksheet.

The text suggests that some tasks are more easily done with a command line utility, such as changing a password. The command line syntax in SQL*Plus is given:

ALTER USER user_ID IDENTIFIED BY new_password;

This method avoids the usual double entry of a password, which is slower. If you were going to reset several passwords, the command line method might be easier as well as faster.

The text goes on to discuss configuring Oracle Net for your installation. An Oracle Net component will reside on the server, and another component of it will reside on the workstation of any user of the system. Both must be configured to point to the same database, and both must be configured to use the correct protocols for your network. These settings are stored in a file that is stored on the client workstation and on the server: tnsnames.ora. This file stores the setting listed above, as well as pointers to databases on other servers.

On the server side, Oracle Net runs a service called the Listener. This service receives requests from the client, translates them from network protocol to Oracle protocol, and sends the request to the database. The port that the Listener uses, the names of the databases, and the names of the servers holding the databases are saved in the service name of the Listener. When logging on to the database, you will generally be prompted to provide your user ID, your password, and the service name of the Listener for your database. The service name goes in a field labeled Host String. (Just to be inconsistent?) You can get away with not entering the service name, if you are logging on the the server that holds the database. In this case, you leave the Host String field blank, and you will use the Oracle protocol called Bequeath, instead of Oracle Net to make the database connection.

The text reminds us that if we are connecting to a database on our network, we will use Oracle Net, if we are connecting across the Internet, we will use a Java tool such as JDBC, and if we are connecting by logging in on the server itself, we can use Bequeath.

Pages 34 through 40 walk you through creating a service on your database. Be aware of some key points:

  • Start Net Manager
  • Expand the local node, then the service naming node
  • Click the green plus sign tool to start an Add Wizard for service names
  • Name the Listener service, and use TCP/IP for most networks
  • Enter the name of the computer holding the database. In our lab, right click My Computer, click Properties, then click Computer Name. Look for the full name. (If My Computer is not available, use the procedure in the text: Start | Settings | Control Panel | System | Network Identification or Computer Name.)
  • Use the default port assignment.
  • We are using Oracle9i, so select Oracle 8i or later. (Change this if using a version before 8i.) Enter the name of database service.
  • Run the connection test, then close if successful.

These changes are saved in tnsnames.ora, which is saved in ORACLE_HOME\network\admin. The text cautions us to use only a plain text editor when making manual changes to this file. It will not work if you change it with Wordpad or Word, which will save control characters in it.

The text describes several processes that run in the background when you use Oracle. The application that the user runs to access the database is a foreground process. The first background process is the user process, which received requests from the user application. If you are using a dedicated server, your user process will have a dedicated server process. If you have a shared server, you may have several user processes sharing each server process, and connecting with them through a dispatcher. In either case, you use a connection that runs from the user process through the server process to the database.

The two main memory sections of an Oracle instance are the System Global Area and the Program Global Area. Each server process has its own PGA. Several background processes interact with the two sections of memory.

Five components of the SGA are listed:

  • buffer cache - storage for data that has been read into memory, or modified. Buffer blocks are regularly flushed and reused, but if a block is used often, the data stays in memory longer for quicker access.
  • shared pool - storage for SQL commands that have been translated into machine language. Commands are stored for reuse, to avoid the delay of translating them again.
  • redo log buffer - storage for changed data and a copy of the original data, to allow an undo.
  • cursor pool - OPTIONAL component. Stores pointers to groups of data. These pointers are called cursors. (Thanks, Oracle, redefine another well understood term...)
  • large pool - OPTIONAL component. Provides more memory for the use of background, backup, and recovery processes.

Several background processes are described:

  • PMON - Process Monitor; releases resources once processes are done with them. Restarts server and user processes if they appear to have stopped unexpectedly.
  • DBWn - The last character will be a number, designating different Database Writer processes. The default process is DBW0. It writes data from change buffers to datafiles.
  • LGWR - Log Writer; writes information from the redo log buffers to the redo log files. This happens under three circumstances: every three seconds, or when the redo log buffer is a third full, or when the Database Writer writes to a file.
  • CKPT - Checkpoint Process; assigns a System Change Number to redo log entries each time it tells the Database Writer to execute.
  • SMON - System Monitor; handles recovery, and cleans up memory released by the deletion of temporary tables,
  • RECO - Recoverer Process; found only in systems with distributed databases. Fixes errors that were caused by communication problems.
  • ARCn - Archiver; copies redo log files to archive files. Runs only when archiving.
  • Dnnn - Dispatcher; runs only in shared server systems. Distributes user processes among server processes.
  • LMS - Lock Manager Server; runs only when the database is on a cluster. Locks databases when updates must be made to more than one of them.
  • CJQn - Job Queue Coordinator; runs jobs that are submitted to run in the background.

Oracle Enterprise Manager is discussed next. It is referred to as just Enterprise Manager. It can run as the OEM Console, or as the Enterprise Management Server.

Four main features are discussed in detail:

  • Instance Manager - monitors activity in the instance, and can be used to start and stop the database
  • Schema Manager - each user has their own schema, which is defined here as the list of database structures they have created or are given ownership of. This component is for managing those structures.
  • Security Manager - for managing users, passwords, and storage allocations for users
  • Storage Manager - used to allocate storage space for tables