Learn Oracle


I have created this learn oracle "The step 10 process" Oracle Tutorial to help Database aspirants to accomplish their dream of working as Oracle Database administrator. I have tried to keep it simple and Oracle DBA friendly. Use NEXT and PREVIOUS Button to navaigate away and to a chapter. Since i myself had been a Database adminstrator trainer and has got real life experience of working as a Oracle DBA, I would welcome any sort of queries and doubts or modifications to webmaster@oracleonline.info.

Step 5 - Accessing the database

Learn Oracle - Accessing the Database

The database instance can be started (made available) only by an authorized database administrator who has a special type of connection privilege to the Oracle instance. After the database instance has been started, it is usually open for access by normal users for whom database user accounts have been created.

This section describes the process of starting the Oracle instance and database and the creation of server processes that enable user access.

How the Oracle Instance and Database are Started

The administrator who starts up the Oracle instance and database must connect to the instance with a special kind of connection privilege. There are two of these privileges: SYSDBA for fully empowered database administrators and SYSOPER for users who operate the database, but have no privileges to access user objects. Authorization to use these privileges occurs either through the operating system or through a special password file.

When Oracle DBA create an Oracle database, there are two primary administrative user accounts who are automatically created: SYS and SYSTEM. Both of these users have full database administration privileges granted to them, but only user SYS can initially connect with the SYSDBA privilege.

The Startup Process

The process of starting the instance and database is as follows:

Oracle DBA start the instance in one of the following ways:

Use Oracle Enterprise Manager. See "Shutting Down and Restarting the Instance and Database".

Use the STARTUP statement using SQL*Plus.

On Windows, Oracle DBA can start the Oracle services. See "Starting and Shutting Down the Database Instance on Windows".

Oracle reads the initialization parameter file, allocates SGA memory, and starts the background processes for the instance.

. If Oracle DBA also specify that the database is to be mounted, then the instance opens the database control file. The database is now said to be in the mount state. This state enables administrators to perform certain administrative functions that cannot be performed when other users are accessing the database.

. If Oracle DBA specify that the database is to be opened, then the instance opens the redo log files and datafiles for the database. The database is now open and available for all user access.

The default startup behavior is to complete the three stages as described earlier. Unless explicitly specified otherwise, the instance will be started, database mounted and opened.

The Shutdown Process

The process is reversed when Oracle DBA shut down the database.

You issue the statement to shutdown the database by similar means as explained in "The Startup Process" . Any modified data blocks that are cached in the SGA and have not been written to disk are now written to disk. The redo log buffer is similarly flushed. Datafiles are checkpointed and their headers are

Overview of an Instance and Instance Management

Datafiles and log files are closed. Users can no longer access the database.

The Oracle instance dismounts the database and updates relevant entries in the control file to record a clean shutdown. The control file is closed. The database is now closed and only the instance remains.

The Oracle instance stops the background processes of the instance and deallocates the shared memory used by the SGA. Server and Client Processes

In addition to background processes, Oracle creates server processes that handle the requests of user or client processes that connect to the instance. A user connection is composed of two distinct pieces:

A client program acting on behalf of the user, such as Oracle Enterprise Manager, SQL*Plus, or an application

A server process that handles the connection to the database on behalf of the client program

In some situations when the client and Oracle operate on the same machine, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead. However, when the client and Oracle operate on different machines, a user process always communicates with Oracle through a separate server process.

Server processes can be either dedicated or shared. In dedicated server mode, each client process has its own server process. While a dedicated server process is good for long running queries and administrative tasks, an idle process or too many dedicated processes can result in an inefficient use of resources. Shared server mode eliminates the need for a dedicated server process for each connection. A dispatcher directs multiple incoming network session requests to a pool of shared server processes. An idle shared server process from a shared pool of server processes picks up a request from a common queue, which means a small number of shared servers can perform the same amount of processing as many dedicated servers. Also, because the amount of memory required for each user is relatively small, less memory and process management are required, and more users can be supported. Shared server mode is more efficient at supporting multiple users and clients making frequent short-running queries.

Network Connections

Oracle Net is a layer of software that allows different physical machines to communicate to accessing an Oracle database. A version of Oracle Net runs on the client machine and on the database server. The client machine and the database server are often the same machine.

Oracle Net enables the client and server to communicate over a network using many popular network protocols, and it provides location transparency such that the client machine does not need to know the serverís location. When the database is moved to another location, Oracle DBA only need to reconfigure Oracle Net. No changes are necessary to client applications.

Oracle Net must be separately configured and started for it to handle client connections to the database. You can configure Oracle Net using Oracle Enterprise Manager or with a separately launched GUI tool called the Oracle Net Manager.

When an instance starts, a listener process establishes a communication pathway to Oracle. When a user process makes a connection request, the listener determines whether it should use a shared server dispatcher process or a dedicated server process and establishes an appropriate connection.

About Initialization Parameters

Instance management involves configuring parameters that affect the basic operation of the database instance. These parameters are called initialization parameters. The Oracle database server reads these parameters at database startup and monitors them while the database is running. They are stored in memory, where many of them can be changed dynamically. There are two types of parameter files, and whether these dynamic changes are persistent across database shutdown and startup depends upon the type of parameter file Oracle DBA are using.

Server parameter file

This, the preferred form of initialization parameter file, is contained in a binary file that can be written to and read by the database server. It must not be edited manually. It resides on the machine that Oracle is running on, and is persistent across shutdown and startup.

Text initialization parameter file

This type of initialization parameter file can be read by the database server, but it is not written to by the server. In this file, you can set initialization parameters with a text editor for them to be persistent across shutdown and startup.
Oracle Tutorial - BLOGS

STEP 5 - Next Topics
This step discusses starting and stopping the database instance, and managing initialization parameters.Learn Oracle DBA Step 5 by clicking links below:
Instance Memory StructureConfiguring Network
Oracle Background ProcessesBackground processes
Accessing the DatabaseAccessing the Database
Shuting down and Restarting the instance and Database
Viewing and Modifying Initialization
Managing Memory Parameters
Oracle Database - PODCASTS
More Tutorials on Oracle dba ...

Liked it ? Want to share it ? Social Bookmarking
Add to: Mr. Wong Add to: BoniTrust Add to: Newsider Add to: Digg Add to: Del.icio.us Add to: Reddit Add to: Jumptags Add to: StumbleUpon Add to: Slashdot Add to: Netscape Add to: Furl Add to: Yahoo Add to: Spurl Add to: Google Add to: Blinklist Add to: Technorati Add to: Newsvine Information

Source : Oracle Documentation | Return to Learn Oracle

Want to share or request Oracle Tutorial articles to become a Oracle DBA. Direct your requests to webmaster@oracleonline.info