1. Managing database availability
2. Planning and creating databases
3. Managing physical structures
4. Managing storage on design
5. Managing Security
6. Network Administration
7. Backup and Recovery
8. Database tuning

The Oracle server is an object-relational database management system that provides an open, comprehensive, integrated approach to information management.

A database user can connect to an Oracle server in any one of the following ways:

1. Logging in directly to the host
2. Using a two-tiered (client-server) connection, where the machine on which the user is logged in is connected directly to the machine running the Oracle server.
3. Using a three-tiered connection, where the user’s machine communicates to an application or a network server, which in turn is connected through a network to the machine running the Oracle server.

1. The user starts a tool such as SQL-Plus, or runs an application developed using a tool such as Developer 2000 Forms, spawning a user process. The tool or application runs on the client machine.
2. The user logs on to the Oracle server by specifying a username, a password and a database, a process is created on the machine that is running the Oracle server. This process is called a server process.

If the user runs the tool or the application on the same machine as the Oracle server, the communication pathway is established using the inter process communication mechanisms available on the machine. If the user runs the tool on a client machine, network software is used to communicate between the user and the Oracle server using the network.

A session is a specific connection of a user to an Oracle server. A session commences when the user is validated by the Oracle server and it ends when the user logs out or when there is an abnormal termination.

ORA-01034: Oracle not available – occurs when a user or application attempts to connect to an oracle server that is not available for use.

1. Runs on the client machine
2. Is spawned when an application or tool is invoked.
3. Runs the tool or application
4. Includes the User Program Interface (UPI)
5. Generates calls to the Oracle server.

1. Runs on the server machine (host)
2. Services a single user process in the dedicated server configuration.
3. Uses an exclusive PGA.
4. Includes the Oracle Program Interface (OPI)
5. Processes calls generated by the client.
6. Returns results to the client.

1. Is a means to access an Oracle database
2. Always opens one and only one database.

Oracle server consists of an Oracle instance and an Oracle database. An Oracle instance consists of a memory structure, called the System Global Area (SGA), and background processes used by an Oracle server to manage a database. An Oracle instance, which is identified by setting the ORACLE_SID at the operating system, can open and use only one database at any point in time.

Contains data and control information for the Oracle server. The SGA comprises several memory structures, including:

1. Shared Pool – used to store information such as the most recently executed SQL and the most recently used data from the data dictionary.
2. Database buffer cache – used to store the most recently used data.
3. Redo Log buffer – used to register changes made to the database using the instance

Background Processes

Is required to service the requests from several concurrent users, without compromising the integrity and performance of the whole system. Every instance comprises these five background processes by default:

1. DBWR – Database Writer – responsible for writing changed data to the database
2. LGWR – Log writer – records changes registered in the redo log buffer to the database.
3. SMON – whose primary function is to check for consistency and initiate recovery of the database when the database is opened,.
4. PMON – Process monitor – cleans up the resources if one of the processes fails.
5. CKPT – Checkpoint process – responsible for updating the database status information whenever changes in the buffer cache are permanently recorded in the database.

Contains user data and additional information that is needed to ensure proper database operation.

1. Data files – stores the data dictionary, user objects and before-images of data that are modified by concurrent transactions. A database has at least one data file per database.
2. Redo Log files – contains a record of changes made to the database to ensure reconstruction of the data in case of failures. (at least 2 redo log files per database)
3. Control files – contains the information needed to maintain and verify database integrity. (at least one control file per database

Apart from database files, an Oracle server also uses other files.

1. Parameter file – used to define the characteristics of an Oracle instance
2. Password file – used to authenticate privileged database users
3. Archived redo log files – offline copies of redo log files that may be necessary to recover from media failures.

Processing a Query :

1. Parse : The server processes checks for the validity of the command and uses the area in the shared pool to compile the statement. In the end, the status of the phase is returned to the user process.
2. Execute : The server prepares to retrieve the data.
3. Fetch : The rows retrieved by the query are returned to the user.

1. Size is determined by SHARED_POOL_SIZE
2. Library cache contains statement text, parsed code and an execution plan of the most recently used SQL statements.
3. Data Dictionary cache contains the most recently used data dictionary information such as table and column definitions and privileges, usernames, passwords.

The Library cache helps improve the performance of the applications.

Is an area in the SGA, that is used to store the most recently used data blocks. Size is determined by DB_BLOCK_SIZE parameter. The number of blocks is determined by DB_BLOCK_BUFFERS parameter. The Oracle server user a LRU algorithm to age out buffers that have been recently accessed.

Is a memory region that contains data and controls information for a single server process or a single background process. It is not shared and writable (in contrast to SGA). The PGA contains,

1. Sort area
2. Session information – user privileges
3. Cursor state – stage of processing of various cursors that are being used in the session
4. Stack Space – contains the cursor variables

Before making a change, the server process saves the old value into a rollback segment. This image is used to

1. undo the changes if the transaction is rolled back.
2. Ensure that other transactions do not see uncommitted changes made by the DML statements (read consistency)
3. Recover the database to a consistent state in the case of failures.

Rollback segments, like tables and indexes, exist in data files and parts of them are brought into the database buffer cache when required.

Is a part of the SGA.

1. its size is determined by LOG_BUFFER parameter
2. It stores redo records, which record changes
3. It is used sequentially, and changes made by one transaction may be interleaved with changes made by other transactions.
4. It is circular buffer that is reused after it is filled up, but only after all the old redo entries are recorded in the redo log files.

The database writer writes the dirty buffers from the database buffer cache to the data files. It ensures that sufficient number of free buffers are available in the database buffer cache. The DBWR defers writing to the data files until

1. The number of dirty buffers reaches a threshold value
2. A process scans a specified number of blocks when scanning for free buffers and cannot find any.
3. A timeout occurs
4. A DBWR checkpoint can be triggered by various events such as closing of the database.

The Log Writer is a background process that writes entries from the redo log buffer into the redo log files. The LGWR performs sequential writes

1. When the redo log buffer is 1/3 full
2. When a timeout occurs (every 3 seconds)
3. Before DBWR writes modified blocks in the database buffer cache to the data files
4. When a transaction commits.

Oracle uses a fast commit mechanism that guarantees that the committed changes can be recovered in case of failures.

SCN

Whenever a transaction commits, Oracle assigns a commit SCN to the transaction, which is monotonically incremented and is unique within the database. The SCN is used as an internal time stamp to synchronize data and to provide read consistency when data is retrieved from the data files.

When a commit is issued,

1. The server process places a commit record, along with the SCN, in the redo log buffer
2. LGWR performs a contiguous write of all the redo log buffer entries up to and including the commit record to the redo log files.
3. The user is informed that the commit is complete.
4. The server process records information to indicate that the transaction is complete and that the resource locks can be released.

Flushing of the dirty buffers in performed independently by the DBWR, and can take place before or after the commit.

Advantages of fast commits:

1. Sequential writes to the log files are faster than writing to different blocks in the data file.
2. Only minimal information that is necessary to record changes are written to the log files, whereas writing to the data files would require whole blocks of data to be written.
3. The database COMMIT piggybacks redo log records from multiple transactions requesting to commit at the same time into a single write.
4. Unless the redo log buffer is partially full, only one synchronous write is required per transaction.
5. The size of the transaction does not affect the amount of time needed for an actual COMMIT operation.

Server Manager line mode is useful for performing unattended operations, such as running nightly batch jobs or scripts.

Starting Server Manager in Line mode : svrmgrl – On UNIX

Svrmgr30 – on Windows NT

Svrmgrl command="CONNECT scott/tiger"

You can run scripts using the @ command. Eg @credb

Consists of a centralized console, intelligent agents and a package of standard applications that provide database administrators the functionality they need to manage their databases.

OEM performance pack provides expert and advanced monitoring, diagnostic and tuning capabilities.

OEM Architecture

OEM Console – is an application that permits a database administrator to manage several databases from one machine. Besides having a navigator, it provides several services such as job scheduling, event management, discovery of hosts and databases, and security.

Intelligent Agent

Is a process that runs on remote nodes in a network. The agent provides autonomy from the console.

It executes jobs and events sent by the console and communicates results back to the console using Net8.

OEM Repository

Is a set of database tables that holds information used by OEM, gives the administrator location flexibility. A separate repository exists for each OEM user.

During a database startup, the following events occur

1. Start an instance
2. Mount the database
3. Open the database

Every time an instance is started, Oracle uses the parameter file, which contains initialization parameters, to allocate the SGA and to start the background process.

The two database administrator users SYS and SYSTEM are automatically created and granted the DBA role.

SYS : password: change_on_install

Owner of the database data dictionary

SYSTEM : Password : manager

Owner of additional internal tables used by Oracle tools.

Authentication methods

Operating system Authentication

1. Set up the user to be authenticated by the operating system
2. Set REMOTE_LOGIN_PASSWORDFILE to NONE
3. Use the following commands to connect to a database

Using Password File authentication

Connects the user to the SYS schema.

1. Create the password file using the password utility:

$orapwd file=$ORACLE_HOME/dbs/orapwU15\

password=admin entries=5

2. Set REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE or SHARED
3. Use the following command to connect to a database

Changing the Internal Password

1. Use the password utility on NT or UNIX to delete and create the password file or,
2. Use the ORADIM80 utility on NT to delete and create a new password file.