Chapter 1

Introducing Relational Databases and Oracle8

• What's a Database Management System?
• Oracle Database Files
  • The Initialization Parameter File
  • The Control File
  • The Data File
  • Redo Log Files
  • Archived Redo Log Files
• Understanding Database Instances
  • Starting and Stopping Instances
• Oracle8's Tools
  • Oracle Enterprise Manager (OEM)
  • SQL*Plus
  • PL/SQL
  • Net8
  • Precompilers
  • Developer/2000
• The Oracle8 Data Dictionary
  • Statistics and the Data Dictionary
  • Dynamic Performance Tables

• Functions performed by a database management system
• Physical architecture of an Oracle database
• Identify the major components of an Oracle instance
• Overview of database tools: Oracle Enterprise Manager, SQL*Plus, PL/SQL, Net8, Developer 2000, and precompilers
• Understand the Oracle8 data dictionary and dynamic performance views

What's a Database Management System?

A database can be defined as a collection of information organized in such a way that it can be retrieved and used. A database management system (DBMS) can further be defined as the tool that enables us to manage and interact with the database.

Most DBMSs perform the following functions:

• Store data
• Create and maintain data structures
• Allow concurrent access to many users
• Enforce security and privacy
• Allow extraction and manipulation of stored data
• Enable data entry and data loading
• Provide an efficient indexing mechanism for fast extraction of selected data
• Provide consistency among different records
• Protect stored data from loss by backup and recovery process

Several different types of DBMSs have been developed to support these requirements. These systems can broadly be classified in the following classes:

• A hierarchical DBMS stores data in a tree-like structure. It assumes a parent-child relationship between the data. The top of the tree, known as the root, can have any number of dependents. Dependents, in turn, can have any number of subdependents, and so on. Hierarchical database systems are now obsolete.
• A network DBMS stores data in the form of records and links. This system allows more flexible many-to-many relationship than do hierarchical DBMSs. Network DBMSs are very fast and storage-efficient. Network database management systems allowed complex data structures but were very inflexible and required tedious design. An airline reservation system is one example of this type of DBMS system.
• Relational DBMSs (RDBMSs) probably have the simplest structure a database can have. In an RDBMS, data is organized in tables. Tables, in turn, consist of records, and records of fields. Each field corresponds to one data item. Two or more tables can be linked (joined) if they have one or more fields in common. RDBMSs are easy to use and have flourished in the last decade. They're commonly used on low-end computer systems. In the last few years, however, their use has expanded to more powerful computer systems. Oracle, Informix, and Sybase are some popular RDBMSs available in the market.

• Object-oriented DBMSs were designed to handle data such as numbers and words. During recent years, however, object-oriented DBMSs are emerging. These systems can handle objects such as videos, images, pictures, and so on.

Oracle Database Files

An Oracle database physically resides in various files. Figure 1.1 shows the physical structure of an Oracle database.

Figure 1.1 : An Oracle database system.

The Initialization Parameter File

The parameter file, commonly known as INIT.ORA, contains initialization parameters that control the behavior and characteristics of the database and the instance that accesses the database. You can edit this text file in your favorite editor.

Oracle supplies a sample INIT.ORA file in the $ORACLE_HOME/dbs directory. $ORACLE_HOME is the top-level directory under which Oracle software is installed; it doesn't need to be the user Oracle's home directory. The default name of an instance's parameter file is initSID.ora, in which SID (System IDentifier) is a character string that uniquely identifies the instance on the system.

You can override the defaults by using the PFILE parameter of the Server Manager's startup command. The IFILE parameter in this file allows you to nest multiple initialization files for the same instance.

The Control File

The control file contains information about the database's physical structure and status. It has information about several things: the total number of data files; log files; redo log groups; redo log members; current redo log to which the database is writing; name and location of each data file and online redo log files; archived log history; and so on. Starting from Oracle8, the control file also contains information about the backup of the database.

The control_file initialization parameter specifies the name and location of a database's control file. It's strongly recommended that you specify multiple files in the control_file initialization parameter to mirror the control file to multiple locations. The V$CONTROL_FILE data dictionary view contains information about the database's control file.

The V$CONTROLFILE_RECORD_SECTION dynamic performance view contains detailed structure information about the control file. This view gives the information about all records contained in the control file.

The Data File

An Oracle database stores user information in physical data files. A data file can contain tables, indexes, clusters, sequences, data dictionary, rollback segments, temporary segments, and so on. At the logical level, Oracle manages space in terms of tablespace (a group of one or more data files). When an Oracle database is created, it has only one tablespace: SYSTEM. Other tablespaces and the associated data files are added later, as needed.

You can specify the name, location, and size of a data file while creating the tablespace to which the data file belongs. Oracle uses control files to store the name and location of the data files. Use the data dictionary views V$DATAFILE and DBA_DATA_FILES to retrieve the information about a database's data files.

Redo Log Files

Oracle records all changes against the database in the redo log file and uses the contents of the redo log file to regenerate the transaction changes in case of failure. An Oracle database has two or more redo log files. Oracle allows you to mirror the redo log files, thus a redo log group contains one or more files (members). Oracle writes to all the members of a redo log group simultaneously. An Oracle instance writes to redo log groups in cyclical order-that is, it writes to one redo log group and then to the next when the earlier one is filled up. When the last available redo log group is filled, it switches over to the first one.

SEE ALSO
For detailed information about redo log files,

You can specify the name, location, and size of the redo log files during database creation. The V$LOGFILE data dictionary view contains redo log files' information. You can also add, delete, and relocate redo log files by using the ALTER DATABASE command.

Archived Redo Log Files

The archived log file contains a copy of the redo log file. Archived redo log files are useful in recovering the database and all committed transactions in case of failures (such as disk failure). When an Oracle database is operating in archive log mode, it needs to archive the recently filled redo log file before it can reuse it.

You can enable automatic archiving by setting the initialization parameter LOG_ARCHIVE_START to TRUE or by issuing the archive log start command after the instance startup. When automatic archiving is enabled, the ARCH (archiver) process copies the filled redo log files to the directory specified by LOG_ARCHIVE_DEST. The LOG_ARCHIVE_FORMAT parameter defines the default names of archived log files.

Understanding Database Instances

An Oracle database stores data in physical data files and allows controlled user-access to these files through a set of operating system processes. These processes are started during the instance startup. Because they work silently, without direct user interaction, they're known as background processes. To enable efficient data manipulation and communication among the various processes, Oracle uses shared memory, known as Shared Global Area (SGA). These background processes and the shared memory segment together are referred as an Oracle instance. In a parallel server environment, a database can be accessed by multiple instances running on different machines.

An Oracle instance consists of the following background processes:

• The process monitor process (PMON). This background process cleans up after a user process terminates abnormally. It rolls back the uncommitted transaction left behind and releases the resources locked by the process that no longer exists.

• The database writer process (DBWR). To ensure efficient and concurrent data manipulation, Oracle doesn't allow a user process to directly modify a data block on the disk. The blocks that need to be modified or in which the data is inserted are first fetched in a common pool of buffers, known as buffer cache. These blocks are then written to the disk in batches by the DBWR background process. Thus, DBWR is the only process with write access to the Oracle data files.
• The log writer process (LGWR). Whenever an Oracle process modifies an Oracle data block, it also writes these changes to the redo log buffers. It's the responsibility of the LGWR process to write the redo log buffers to the online redo log file. This process reads the contents of the redo log buffers in batches and writes them to the online redo log file in sequential fashion. Note that LGWR is the only process writing to the online redo log files. Oracle's transaction commit algorithm ensures that the contents of redo log buffers are flushed to the online redo log file whenever a transaction is committed.
• The system monitor process (SMON). This background process does operations such as freeing up the sort space and coalescing the adjacent free extents in one big extent. SMON is also responsible for performing transaction recovery during the instance recovery (during instance startup after a crash or shutdown abort). In a parallel server environment, it also detects and performs instance recovery for another failed instance.
• The archiver process (ARCH). This process is started when the database is in archive log mode and automatic archiving is enabled. It copies the recently filled online redo log file to an assigned backup destination.
• The checkpoint process (CKPT). During a checkpoint, the DBWR process writes all the modified blocks to disk. DBWR also tells LGWR to update the header information of all data files with the checkpoint information. Because a database containing a larger amount of data files might be a time-consuming task for LGWR, the CKPT process starts during instance startup to help LGWR update the file headers during the checkpoint. This process is started only when the CHECKPOINT_PROCESS parameter is set to TRUE or when the number of data files in the database is more than a certain number.
• The recoverer process (RECO). This process is responsible for recovering the in-doubt transaction in a distributed database environment. This process is started only when the initialization parameter DISTRIBUTED_TRANSACTION is set to greater than 0.
• The parallel query slave processes (pxxx). Under favorable conditions, Oracle can reduce the execution time for certain SQL operations by dividing the operation among several dedicated processes. The processes used for parallel execution of SQL statements are known as parallel query slaves.
• The snapshot process (SNPn). The snapshot or the job queue processes are started when the parameter JOB_QUEUE_PROCESSES is set more than 0. These processes execute jobs in the job queue, refresh any snapshot that's configured for automatic refresh, and so on.
• The dispatcher process (Dxxx). Oracle supports multithreaded servers on some operating systems. When enabled, these processes receive the user request and put it in the request queues for execution. They also collect the results of the execution from the dispatcher queues and pass them back to users.
• The shared server process (Sxxx). In a multithreaded server environment, the dedicated server process executes the SQL operations from the request queues and puts back the results in the corresponding dispatcher queue.

If you're running Oracle's parallel server option, you also see the following background processes on each instance:

• The lock process (Lckn). This Oracle parallel server process coordinates all the lock requests from the local and remote instances. It communicates with user processes and the lock daemon process.
• The lock monitor process (LMON). This Oracle parallel server process is responsible for reconfiguring the Integrated Distributed Lock Manager (IDLM) during instance startup and shutdown in an OPS environment. It also performs lock cleanup after abnormal death of a user process.
• The lock daemon process (LMD0). This Oracle parallel server process is part of the IDLM. It handles all lock requests from remote instances for the locks held by a local instance.

Figure 1.2 shows the components of an Oracle instance: the SGA and background processes.

Figure 1.2 : An Oracle instance consists of the SGA and background processes.

Starting and Stopping Instances

An Oracle database isn't accessible to users until it's opened by an Oracle instance. In an Oracle parallel server environment, an Oracle database is accessed by more than one instance. Each instance has its own set of background processes and the SGA. An instance startup operation involves starting all the background processes and allocating the shared memory area (see Figure 1.3).

Figure 1.3 : Oracle instance startup consists of three steps.

How Oracle starts instances

1. Oracle starts all the background processes and allocates the SGA. Oracle reads the initialization parameter file during this step.
2. Oracle reads the control file and associates the control with the instances. It detects the conditions of the database from the last shutdown/crash.
3. Oracle reads the file headers of all the data files and redo log files. It ensures consistency among all data files. If the instance is being started after a crash or shutdown abort, Oracle also applies all the redo log since the last successful checkpoint. Oracle database is accessible to users after completing this step.

If the instance is started after a crash or shutdown abort, Oracle needs to perform rollback operations for the uncommitted transaction. This operation is performed by SMON in the background while the database is open and available for use.

An Oracle instance shutdown closes the database, dismounts it, and then removes the SGA and the background processes. Shutdown offers three modes: normal, immediate, and abort. Shutdown normal and shutdown immediate are used most often, whereas shutdown abort should be used with caution. During shutdown normal, Oracle waits for all users to disconnect, writes all modified data to the data files, and then updates files headers, online redo log files, and control files. Shutdown immediate disconnects all users and then proceeds similarly to shutdown immediate. Shutdown abort just removes all the background processes and the SGA; all cleanup work is done during the next startup.

Table 1.1 lists Server Manager commands to start and stop an Oracle instance.

SEE ALSO
To learn how to start and stop database instances with Oracle Enterprise Manager,

Oracle8's Tools

Oracle provides various tools for application development and for performing administrative functions:

• Oracle Enterprise Manager (OEM)
• SQL*Plus
• PL/SQL
• Net8
• Developer 2000
• Precompilers

Oracle Enterprise Manager (OEM)

Oracle Enterprise Manager is a graphical system management tool that allows you to perform multiple tasks in a complicated database environment. OEM comes with several components. Some components, such as Oracle Expert and Performance Manager, are priced separately. Chapter 4 "Managing with Oracle Enterprise Manager (OEM)," explains how to use these components. OEM's major components are as follows (see Figure 1.4):

Figure 1.4 : Oracle Enterprise Manager consists of several modules.

	Backup Manager		Software Manager
	Security Manager		Oracle Expert
	Data Manager		Lock Manager
	Storage Manager		TopSession Monitor
	Instance Manager		Performance Manager
	Schema Manager		Tablespace Manager
	SQL Worksheet

• Backup Manager lets you perform backup and recovery operations associated with the database. It lets you interface with Oracle8's advanced backup and recovery utility, the Recovery Manager.
• Data Manager, a data transfer and loading tool, lets you invoke export, import, and load utilities. Use the Export utility to extract data in Oracle's operating system-independent format. The exported data can be loaded in another Oracle database or later in the same database. You also can use Export as the database's logical backup. The Loader utility is used to insert data in the Oracle database from text files.
• Instance Manager lets you manage instances, user sessions, and in-doubt transactions. It lets you start and shut down Oracle instances. You can manage multiple instances in an Oracle parallel server environment. It also lets you manage the initialization parameter file used during instance startup.
• Lock Manager lets you view the locks held in an instance. It's a helpful tool for analyzing hung sessions and other, similar situations.
• Oracle Expert lets you tune instance and database performance. It generates a listing of recommendations that can be implemented automatically to improve the performance.
• Performance Manager lets you monitor an Oracle instance performance. It provides you with graphical representation of various performance statistics.
• Schema Manager lets you perform Data Definition Language (DDL) operations, which let you create, alter, drop, and view database objects such as tables, indexes, clusters, triggers, and sequences.
• Security Manager lets you perform user-management tasks such as adding, altering, and dropping users, roles, and profiles.
• Software Manager allows you to administer in an distributed environment and to automate database administration tasks.
• SQL Worksheet behaves mostly similar to a SQL*Plus session. You can use it to enter and execute SQL commands.
• Storage Manager lets you perform database space-management tasks such as creating, altering, and dropping tablespaces. It also lets you create online/offline and drop rollback segments.
• Tablespace Manager lets you view the space usage within a tablespace at object level. You can also get information about used and free space within the database.
• TopSession Monitor lets you monitor active user sessions and view user resource utilization. This information can be used to address slow performance.
• Oracle Trace lets you drill down the execution of SQL statements to improve performance of the system.

SQL*Plus

The only interface available between end users and an RDBMS is Structured Query Language (SQL). All other applications and tools that users utilize to interact with the RDBMS act as translators/interpreters. These tools generate SQL commands based on a user's request and pass the generated SQL commands on to the RDBMS.

SQL*Plus, Oracle's version of SQL, is one of the most commonly used Oracle tools. SQL*Plus enables users to instruct the Oracle instance to perform the following SQL functions:

• Data definition or DDL operations, such as creating, altering, and dropping database objects
• Data query to select or retrieve the stored data
• Data manipulation or the DML operations to insert, update, and delete data
• Access and transfer data between the databases
• Allow user to enter data interactively
• DBA functions or the database administrative tasks such as managing users (creating, altering, and dropping users), managing space (creating, altering, and dropping tablespaces), and backup and recovery

In addition to these basic SQL functions, SQL*Plus also provides several editing and formatting functions that enable users to print query results in report format.

Setting Up the SQL*Plus Environment

SQL*Plus has many advanced functions that you can use to present data in a visually pleasing format. You can set various environment variables in order to control the way SQL*Plus outputs a query. Table 1.2 lists some of the most common commands to set up the environment, which you can enter at the SQLPLUS> prompt.

PL/SQL

PL/SQL stands for Procedural Language/Structured Query Language. It allows a user to utilize structured programming constructs similar to third-generation languages such as C, Fortran, and COBOL. PL/SQL enhances SQL by adding the following capabilities:

• Define and use variables
• Control the program flow (IF, IF...THEN...ELSE, and FOR LOOP constructs)
• Use of cursors and arrays
• File I/O
• Functions and procedures
• PL/SQL tables to move larger amount of data

With PL/SQL, you can use SQL commands to manipulate data in an Oracle database and also use structured programming constructs to process the data.

Net8

Net8, formerly known as SQL*Net, is Oracle's networking interface. It allows communication between various Oracle products residing on different machines. It enables communication among client, server, and Oracle databases in a distributed environment. At the client end, the client application code passes messages on to the Net8 residing locally, and the local Net8 transfers messages to the remote Net8 via the underlying transport protocol. These messages are received by Net8 at the server, which sends them to the database server for execution. The server executes the request and responds to the client following the same path. Figure 1.5 shows the communication between client and server using Net8.

Figure 1.5 : The client and the server communicate with each other through Net8.

Net8 has many enhancements over its predecessor SQL*Net, such as connection pooling, multiplexing, listener load balancing, and caching the network addresses at the client end. Net8 is backward-compatible and can coexist with SQL*Net version 2.

Precompilers

A third-generation language compiler doesn't recognize the SQL needed to interface with the RDBMS. Therefore, if you need power and flexibility of a language such as C, C++, Fortran, or COBOL and also want it to interface with the Oracle8 RDBMS, you need a tool that can convert the SQL statements to the calls that a language compiler can understand. As Figure 1.6 shows, a precompiler program reads structured source code and generates a source file that a language compiler can process. Oracle provides several precompilers, such as Pro*C, Pro*Cobol, Pro*Fortran, and Pro*Pascal.

Figure 1.6 : You develop programs by using a precompiler.

You might want to use precompilers to get better performance while developing long-running batch programs and time-critical programs. You can do the following by using precompilers:

• Use dynamic SQL.
• Better control cursor and program flow.
• Develop program libraries and use them in multiple applications.
• Concurrently access the data from multiple databases.
• Write multithreaded applications by forking processes.

Developer/2000

Developer/2000 provides the complete set of tools to develop applications that access an Oracle database. It consists of tools for creating forms, reports, charts, queries, and procedures. It also enables you to deploy existing and new applications on the Web. Developer/2000 consists of the following component tools:

• Project Builder. You can track and control application documents, source code files, charts, forms, reports, queries, and so on.
• Forms Builder. Forms are one of the easiest and most popular means for end users to interact with the database. End users totally unfamiliar with the database and the SQL language can easily learn a forms-based application to access the Oracle database. Forms Builder is the tool application developers can use to develop forms.
• Report Builder. Although forms give users online/immediate interaction with the database, the Report Builder allows users to queue the data extraction request in a queue at a remote report server. The report server, in turn, interacts with the database to generate the reports. You can also embed the reports in other online tools such as Web browsers, graphics, and forms.
• Graphics Builder. Graphical and visual representation of the data is much more effective than raw data. You can use Graphics Builder to produce interactive graphical displays. Graphics Builder also allows you to include graphs in forms and reports.
• Query Builder. Query Builder allows you to interact with database tables in tabular form onscreen. The tables involved in the query are available onscreen, and users can construct the desired query by pointing and clicking. Formatted query results are also displayed.
• Schema Builder. Schema Builder is a graphical DDL (data definition language) tool. You can use it to create, alter, and drop database objects such as tables, indexes, clusters, and sequences.
• Procedure Builder. The Procedure Builder helps you build procedures interactively. You can use its graphical interface to create, edit, debug, and compile PL/SQL programs. The PL/SQL programs unit, which can be generated with the Procedure Builder, includes packages, triggers, functions, and program libraries.
• Translation Builder. The Translation Builder allows you to extract translatable strings from Oracle or non-Oracle resources and perform the desired transaction. For example, you can translate Microsoft Windows (.RC) and HTML files to Oracle resource files.

Traditionally, Developer/2000 supported the client/server architecture, where the client tools and the application reside on one machine (usually the end-user PC) and the database server resides on another machine. With the proliferation of the Web, however, Oracle has introduced a three-tier architecture in which an additional server that runs the application code has been introduced.

Client/server, or the three-tier, architecture for installing Developer/2000 is highly recommended because the workload is distributed among the client, database server, and application servers in this structure. In addition, the application, Developer/2000, and the database software are independent of each other, thus making maintenance easier. SQL*Net or Net8 needs to be installed on the client and the database server to enable the connectivity between the two.

The Oracle8 Data Dictionary

Oracle stores information about all the objects defined by the users, structural information about the database, and so on in its internal tables. These Oracle internal tables and associated objects are collectively referred as the data dictionary. The data dictionary is owned by the user SYS and always resides in the SYSTEM tablespace.

Information stored in the data dictionary is available to users through data dictionary views. A database administrator or a user can use the data dictionary to view the following information:

• Definitions of the database objects such as tables, partitions, indexes, clusters, views, snapshots, triggers, packages, procedures, functions, sequences, and synonyms
• Users defined in the database
• Storage allocation for the objects in the database and quota assigned to each user
• Integrity constraints
• Database links
• Privileges and roles
• Replicated objects, snapshots, and their refresh characteristics
• Auditing information such as access patterns for various objects
• Jobs in the job queue
• Locks and latches held in the database
• Alerts and table queues (advance queues)
• Rollback segments
• SQL*Loader direct load
• NLS settings

Oracle's data dictionary views can broadly be defined in the following classes:

• Views with the DBA prefix. These views contain information about the entire database. For example, the view DBA_TABLES gives information about all tables in the database. By default, these views are accessible only to users with the DBA role.
• Views with the USER prefix. USER views contain information about the objects owned by the user. For example, USER_TABLES gives information about the tables owned by the user.
• Views with the ALL prefix. These views contain information about all objects accessible to the user. Objects accessible to a user include the objects created by the user plus the objects on which he has received grants from other users. For example, the ALL_TABLES view contains information about all tables accessible to a user.

Table 1.3 lists important Oracle8 data dictionary views. Similar views with DBA and ALL prefixes are available.

Statistics and the Data Dictionary

Several data dictionary views contain columns with statistics information for the object. For example, the USER_TABLES view contains columns NUM_ROWS (number of rows in the table), BLOCKS (number of data blocks used in the table), AVG_ROW_LEN (average row length of a row in the table), and so on. These columns are populated only when you analyze the object by using the ANALYZE command. You should analyze the objects at regular intervals to keep the statistics up-to-date.

Dynamic Performance Tables

An Oracle instance maintains comprehensive information about its current configuration and activity. These statistics are accessible to the database administrator through dynamic performance views. Most of these views are based on in-memory table-like structures known as virtual tables (because they aren't real tables). The majority of these views have names starting with V$. These virtual tables don't require disk storage space and aren't stored in any tablespace. By default, the dynamic performance views are accessible to the SYS user or to the users having a SYSDBA role. Contents of these views are updated continuously while the instance is active.

Table 1.4 describes important dynamic performance views. These views are for Oracle8; some may not exist in Oracle7.

---

© Copyright, Macmillan Computer Publishing. All rights reserved.