Configuring Oracle for Access

Chapter 3

Configuring Oracle for Access

(Updated July 2008)

Please note that the details on your computer may be slightly different, depending the operating system and the version of database you installed on your computer. Thus, read with your discretion.

Oracle is a sophisticated DBMS that manages access to almost any size database that you can imagine. Hundreds, thousands, or even tens of thousands of concurrent users can connect to a single database server, locally or across a computer network. To accomplish these extraordinary tasks efficiently and reliably, Oracle creates and uses numerous software structures. This chapter explains these structures, including:

· Database servers and instances

· Oracle server processes and threads

· Nets, Oracle's networking software for distributed processing environments

3.1 Oracle Database Instances

As you learned in Chapter 1, a database instance is the collection of server-side processes and memory areas that Oracle uses for managing access to a database. Figure 3-1 is a basic illustration that shows you the shape of an Oracle database instance's processes and memory areas.

NOTE
Oracle’s documentation provides more information about the memory structures in an Oracle instance.

FIGURE 3-1. An Oracle database instance

3.2 Server Startup and Shutdown

Before anyone can work with an Oracle database, someone must start up the database server. This process includes starting a database instance, mounting (associating) the database to the instance, and opening the database. After a server startup, the database is generally available for use with applications.

Conversely, you can make a database unavailable by performing a database server shutdown. A server shutdown is the reverse of a server startup: you close the database, dismount it from the instance, and then shut down the instance. After a server shutdown, users cannot access the database until after you restart the server.

3.2.1 Server Crashes

A server crash is an abnormal server shutdown. For example, an unfortunate operating system operation or problem could unexpectedly kill one or more of a server's background processes. Consequently, the database server might crash. Oracle has built-in features that protect the work of all committed transactions, and automatically performs the necessary recovery from an instance that crashes. See Chapter 11 for more information about Oracle's database protection mechanisms.

3.2.2 Oracle's Parallel Server Option and High Availability

Many sites use Oracle to support mission critical applications. A mission-critical application, by definition, has stringent high-availability requirements. Such sites can tolerate little or no downtime due to a server crash. In such circumstances, Oracle's Parallel Server option can help. In a parallel server configuration, multiple database instances, running on different nodes of a loosely coupled computer, mount and open the same Oracle database in parallel. Users can work with the database through any instance that is mounted to the database. If an isolated system failure causes one of the instances to crash, other servers remain available so that users can continue work uninterrupted. This course does not discuss Oracle's Parallel Server option in any detail, but mentions this option in the context of certain discussions.

3.3 Controlling Server Startup and Shutdown

Now that you have a basic understanding of database availability, the following exercises teach you how to check the status of and control the availability of Oracle on Windows.

EXERCISE 3.1: Checking the Status of Oracle on Windows

On Windows, an Oracle database instance is a service. The default installation of Oracle automatically configures a database instance as a service that starts up when you start Windows. One way to start and stop the database instance on your Windows is to use the Windows Services dialog box. To check the current status of the Oracle service on your computer, complete the following steps.

1. Click the Start button on the Windows.

2. Click Settings.

3. Click Control Panel.

4. Double-click Services to display the Services dialog box. (Depending upon your operating system, it may take a couple more steps before you can see the Services.)

The Services dialog box, shown in Figure 3-2, includes a scrollable list of all the services installed on your computer, as well as their current status (for example, Started), and their Startup configuration (such as Manual, Automatic, or Disabled).

The service that corresponds to the database instance on your machine is the service with the name OracleServicename, where name is the SID you chose for the starter database during the installation process. If you completed the steps in Chapter 2 exactly as given, your OracleServicename should appear to be OracleServiceORACLE. In Figure 3-2 where the italisized part of service name indicates name of Oracle server ID (or SID) is ORACLE1.

If the current status of OracleServicename is Started, then an Oracle instance is available to provide access to the starter database on your computer. However, if the current status of OracleServicename appears as null (as a blank space),an instance is not running and the starter database is not accessible.

FIGURE 3-2. The Windows Services dialog box

EXERCISE 3.2: Manually Starting Oracle

When OracleServicename is not running, you can start up an instance and make the starter database available for access by completing the following steps with the Windows Services dialog box.

1. Select the service OracleServicename.

2. Click the Start button.

After Windows starts the service, the status of OracleServicename should change to Started, which means that an instance is now started and mounted to the starter database, and the database is open for user access.

NOTE
You can also start up an Oracle instance and mount and open the database by using the STARTUP command of SQL*Plus. See your Oracle documentation for more information about this SQL*Plus command, as well as Chapter 11 for several examples of using the STARTUP command.

EXERCISE 3.3: Manually Stopping Oracle

The process of database shutdown is similar to server startup. When OracleServicename is started, you can shut down the instance and make the starter database unavailable by completing the following steps with the Windows Services dialog box.

1. Select the service OracleServicename.

2. Click the Stop button.

NOTE
You can also shut down an Oracle database server using the SHUTDOWN command of SQL*Plus. See your Oracle documentation for more information about this SQL*Plus command, as well as Chapter 11 for several examples of using the SHUTDOWN command.

EXERCISE 3.4: Configuring Oracle for Automatic Startup

You can also configure Windows to automatically start the Oracle database service when Windows starts. To do this, complete the following steps with the Windows Services dialog box.

1. Select the service OracleServicename.

2. Click the Startup button.

3. Select Automatic in the Startup Type options list of the Service dialog box, and then click OK.

When you return to the Services dialog box, the Startup field of the OracleServicename service should now be Automatic. The next time that you restart Windows, the operating system will automatically start the Oracle service so that users can access the starter database.

NOTE
By default, the installer configures OracleServicename for automatic startup with Windows.

3.4 Connecting to Oracle

After an Oracle instance is up and running, you can establish a connection to the server and perform database work. Behind the scenes, the mechanisms of the database instance work to complete your requests and the requests of others. At the same time, the database instance automatically protects the work of all transactions while preserving the integrity of the shared database. The next two exercises show you how to connect to and disconnect from Oracle using SQL*Plus.

EXERCISE 3.5: Connecting to Oracle with SQL*Plus

Once you have the Oracle service started on your Windows workstation or server, you can connect to the instance and access the starter database. To start SQL*Plus and establish a new database session, complete the following steps:

1. Click the Start button on the Windows taskbar.

2. Click Programs.

3. Click Oracle - Oracle - OraDb10g_home.

4. Click Application Development.

5. Click SQL Plus.

After SQL*Plus starts, it automatically presents its Log On dialog box, which you can use to enter a username and password to connect to the starter database, as shown in Figure 3-3. At this point, you can use the following information to establish your first Oracle database session:

· Enter SYSTEM in the User Name field.

· Enter your password for the SYSTEM user in the Password field. (Depending upon the options you selected during the installation of Oracle 10g, it might force you to specify password for various accounts. Therefore, enter the password that you chose during your installation).

· Enter the name of your starter database in the Host String field. If you followed the steps in Chapter 2, you can enter ORACLE (You may leave it empty if you want to connect to the default database.).

FIGURE 3-3. SQL*Plus and its Log On dialog box

If everything works properly, SQL*Plus will start and display a message similar to the following:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL>

The blinking cursor after the "SQL>" in the last line of the display is SQL*Plus's default command prompt. Here, you can interactively type SQL and SQL*Plus commands to perform work. Later on in this chapter, you'll see how to enter some simple SQL commands. The next chapter will provide you with a more thorough introduction to SQL.

EXERCISE 3.6: Disconnecting from Oracle and Exiting SQL* Plus

After you are finished using SQL*Plus, you can disconnect from Oracle and exit SQL*Plus by entering the EXIT command (type EXIT) at the SQL*Plus prompt.

3.5 Oracle Processing Architectures

Now that you know how to get an Oracle instance up and running and connect to the server with SQL*Plus, it is time to learn more about the software architecture of an Oracle database instance and how to investigate it further. These topics are discussed in the following sections.

3.6 Server-Side Background Threads

Every Oracle database instance on Windows executes as a single process that contains multiple lightweight background threads of execution. Each background server thread performs a specialized system function. Figure 3-4 and the following sections explain the most common background threads that you'll find in any Oracle database instance.

NOTE
On operating systems that cannot support the boundary between different threads of execution (for example, Unix), an Oracle instance starts and uses multiple background server processes rather than a single process with multiple threads of execution.

FIGURE 3-4.The background threads of an Oracle database instance

3.6.1 The Database Writer (DBWn)

When you modify some database data (for example, insert, update, or delete a row in a database table), Oracle does not simply modify the data on disk. This type of processing would be extremely inefficient in a large multiuser system because the system would constantly be reading data from and writing data back to the database's data files. Instead, a server thread working on behalf of your session reads one or more data blocks from a data file into the server's memory. Oracle then makes the change that you request in the server's memory. Eventually, the database writer (DBWR) background thread writes modified data blocks from memory back to the database's data files. To consolidate disk accesses, reduce unnecessary overhead, and make Oracle perform optimally, an instance's DBWR writes modified data blocks from memory to disk only in certain situations: when DBWR sits idle for several seconds, when a user thread wants to read a new data block into memory but no free space is available, or when the system performs a checkpoint. The section "The Checkpoint Thread (CKPT)," later in the chapter, explains checkpoints.

NOTE
Every Oracle instance has at least one DBWR thread. Systems that must support high-volume transaction throughput can operate better with two or more DBWR threads (DBWO, DBW1, DBW2, and so on).

3.6.2 The Log Writer (LGWR)

The log writer (LGWR) background thread records information about the changes made by all transactions that commit. Oracle performs transaction logging as follows:

1. As you carry out a transaction, Oracle creates small records called redo entries that contain just enough information necessary to regenerate the changes made by the transaction.

2. Oracle temporarily stores your transaction's redo entries in the server's redo log buffer. The server's redo log buffer is a small memory area that temporarily caches transaction redo entries for all system transactions.

3. When you ask Oracle to commit your transaction, LGWR reads the
corresponding redo entries from the redo log buffer and writes them to the database's transaction log. The database's transaction log or online redo log is a set of files dedicated to logging the redo entries created by all system transactions. Chapter 11 discusses the transaction log and other database protection mechanisms.

NOTE
Oracle does not consider a transaction to be committed until LGWR successfully writes your transaction's redo entries and a commit record to the transaction log.

3.6.3 The Archiver (ARCH)

The archiver (ARCH) background thread automatically backs up the transaction log files after LGWR fills them with redo entries. The sequential set of archived transaction log files that ARCH creates is collectively called the database's archived transaction log or offline redo log. If a database experiences a serious failure (for example, a disk failure), Oracle uses the database backups and the archived transaction log to recover the database and all committed transactions. Chapter 11 explains more about Oracle's protective mechanisms, including database backups and the archived transaction log.

NOTE
Automatic transaction log archiving is an optional feature of Oracle. Therefore, ARCH is present only when you use this feature.

3.6.4 The Checkpoint Thread (CKPT)

Periodically, DBWR performs a checkpoint. During a checkpoint, DBWR writes all modified data blocks in memory back to the database's data files. The purpose of a checkpoint is to establish mileposts of transaction consistency on disk. After performing a checkpoint, the changes made by all committed transactions have been written to the database's data files. Therefore, a checkpoint indicates how much of the transaction log's redo entries Oracle must apply if a server crash occurs and database recovery is necessary. During a checkpoint, the special checkpoint (CKPT) background thread updates the headers in all of the database's data files to indicate the checkpoint.