9i Architecture

The machine running your Oracle database has an Oracle database and an Oracle instance. The Oracle Database is the setof files containing the data. The Oracle Instance is the background processes and memory structures that act on the database.

The instance is identified through an environment variable ORACLE_SID in the operating system. The instance can therefore only reference one database at any time.

Memory Structures

The Oracle Instance Memory Structure can be broken down into three main components.

• The System Global Area (SGA)
• The Large Pool
• The Program Global Area (PGA)

SGA

The System Global Area, in turn, is comprised of a group of shared memory structures.

• The Shared Pool
• Database Buffer Cache
• Redo Log Buffer
• Java Pool

The SGA is also known as the Shared Global Area. The Shared Pool is further divided into two sections:

• Library Cache
• Row Cache

The Library Cache contains all the recently executed SQL statements, the execution plans, parse trees etc.

The Row Cache holds the most recently accessed database object definitions, files, indexes, tables, users, privileges, views, etc... A statement being parsed will be processed here to resolve object names and privileges.

Database Buffer Cache

Redo Log Buffer

Java Pool

The Large Pool

PGA

Background Processes

PMON

SMON

LGWR

DBWR

CKPT

ARCH

RECO

LCKn

Pnnn

SNPn

S000 - S999

D000 - D999

Authentication

The following methods for authenticating replace the CONNECT INTERNAL syntax

• operating system authentication
• Password files

CONNECT INTERNAL continues to be supported for backward compatibility. The OSOPER and OSDBA roles can be granted to a user through the operating system, NOT through a GRANT statement. The OSOPER and OSDBA roles cannot be revoked or dropped.

OSOPER permits user to perform: STARTUP and SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, RECOVER

OSDBA: contains all system privileges with ADMIN OPTION, contains the OSOPER role, permits CREATE DATABASE and time-based recovery.

You can create a password file using the password file creation utility

ORAPWD. You may set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to: NONE - causes Oracle to behave as if the password file does not exist. It is the default value. EXCLUSIVE - the password file can be used with only one database. Only such file can contain the names of users other than SYSOPER and SYSDBA. SHARED - the password file can be used by multiple databases. Only SYSDBA and SYSOPER are recognized by a SHARED password file.

Control Files every Oracle database has one created at the same time as the database, you should create two or more copies for redundancy, records the physical structure of the database, contains the following:

• Database name
  
• Names and locations of associated databases and online redo log files
  
• Timestamp of the database creation
  
• Current log sequence number
  
• Checkpoint information.

You can create a new control file for a database using the CREATE CONTROLFILE statement.

You MUST create a new control file in order to change one of the permanent database settings, such as:

• database's name
  
• MAXLOGFILES
  
• MAXLOGMEMBERS
  
• MAXLOGHISTORY
  
• MAXDATAFILES
  
• MAXINSTANCES

To drop control files from the database (steps):

1. Shut down the database.
2. Edit the CONTROL_FILES parameter in the database's initialization parameter file to delete the old control file's name.
3. Restart the database.

Initialization Parameters

DB_NAME and DB_DOMAIN - for creating a database's global database name

example: DB_NAME = MYTEST DB_DOMAIN = US.ABC.COM.

CONTROL_FILES holds a list of control filenames to use for the new database,

if you want Oracle to create new operating system files, make sure that the filenames listed do not match any filenames that currently exist on your system, and vice versa when there is no filenames listed, Oracle uses a default filename. You should list at least two filenames here.

DB_BLOCK_SIZE default data block size for every Oracle server is operating system-specific, determines the size of the database buffers in the buffer cache of the System Global Area (SGA). Larger data block size provides greater efficiency in disk and memory I/O, block size is set during database creation, block size cannot be changed after database creation, except by re-creating the database.

DB_BLOCK_BUFFERS determines the number of buffers in the buffer cache in the System Global Area (SGA), larger cache sizes reduce the number of disk writes of modified data, large cache consumes memory and induce memory paging, you should beforehand estimate the number of data blocks that your application accesses most frequently

PROCESSES determines the maximum number of operating system processes that can be connected to Oracle concurrently, you need 5 for the background processes and 1 for each user process.

ROLLBACK_SEGMENTS a list of the rollback segments an Oracle instance acquires at database startup.

Database Administration

Oracle Database Configuration Assistant (DBCA) launched by the Oracle Universal Installer, can automatically create a starter database for you, provides the simplest means of creating a database.

Create the database manually from a script edit the sample database creation script and the sample initialization parameter file to suit your needs.

Two user accounts are automatically created with the database. SYS - initial password: CHANGE_ON_INSTALL and SYSTEM - initial password: MANAGER - they are granted the DBA role.

CATALOG.SQL creates the views of data dictionary tables, creates the dynamic performance views.

CATPROC.SQL establishes the usage of PL/SQL functionality, creates many of the PL/SQL Oracle supplied packages.

Licensing facilities: related initialization parameters in your initialization parameter file:

• LICENSE_MAX_SESSIONS
• LICENSE_SESSIONS_WARNING
  
• LICENSE_MAX_USERS.

Starting an instance without mounting a database: does not allow access to the database, usually done only for database creation or the recreation of control files.

Starting the instance and mount the database: leave the database closed, does not allow general access to the database.

Starting the instance, and mount and open the database: unrestricted mode: allows access to all users, restricted mode: allows access for database administrators only, all users with the CREATE SESSION system privilege can connect to an open database in unrestricted mode, in restricted mode, database access is allowed only to users with both the CREATE SESSION and RESTRICTED SESSION system privilege, to place a running instance in restricted mode, use the SQL statement ALTER SYSTEM with the ENABLE RESTRICTED SESSION clause.

Startup Force do so only if you cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands , OR when you experience problems when starting an instance.

Starting an instance and mount the database in either exclusive or shared mode: possible only if your operating system supports the Oracle Parallel Server (OPS).

Normal database shutdown: No new connections are allowed, Oracle waits for all currently connected users to disconnect from the database before actually shutting down, at next startup there will be no instance recovery procedures, use the SHUTDOWN command with the NORMAL option.

Immediate database shutdown: should be used in the following situations: A power shutdown will occur, The database is functioning irregularly, Any uncommitted transactions are rolled back, Oracle does not wait for users currently connected to the database to disconnect, at next startup there will be no instance recovery procedures, use the SHUTDOWN command with the IMMEDIATE option.

Planned shutdown of an instance allow active transactions to complete first, no client can start a new transaction on this instance, does not require all users to log off. use the SHUTDOWN command with the TRANSACTIONAL option.

Shut down a database instantaneously via aborting, do so if the database is functioning irregularly and neither of the other types of shutdown works, or if you experience problems when starting the database instance, current client SQL statements being processed are immediately terminated. uncommitted transactions are not rolled back. implicitly disconnects all connected users – no waiting at next startup there will be instance recovery procedures

Oracle Enterprise Manager a separate Oracle product, combines a graphical console, agents, common services, and tools, can be used for administering your database, can be used for starting up and shutting down instances.

Initialization parameter file text file contains a list of instance configuration parameters, often named INIT.ORA or INITsid.ORA, where sid is operating system specific, each Oracle database has at least one initialization parameter file that corresponds only to that database, client for accessing the database must be able to read a database's initialization parameter file to start a database's instance.

Oracle Processes

Database writer (DBWn) writes modified blocks from the database buffer cache to the datafiles one is sufficient for most systems you can configure additional processes to improve write performance valid range: DBW1 through DBW9 use the initialization parameter DB_WRITER_PROCESSES.

Log Writer (LGWR) writes redo log entries to disk sequentially.

Checkpoint (CKPT) happen at specific times all modified database buffers in the system global area are written to the datafiles responsible for signalling DBWn at checkpoints and updating all the datafiles and control files of the database.

System Monitor (SMON) performs crash recovery when a failed instance starts up again cleans up temporary segments that are no longer in use recovers dead transactions skipped during crash and instance recovery coalesces free extents within the database's dictionary-managed tablespaces.

Process Monitor (PMON) performs process recovery when a user process fails responsible for cleaning up the cache responsible for freeing resources previously used checks on dispatcher and server processes and restarts them if they have failed.

Archiver (ARCn) copys the online redo log files to archival storage performs copying when redo log files are full or a log switch occurs.

Recoverer (RECO) resolve distributed transactions that are pending.

Dispatcher (Dnnn) present only when the multi-threaded server (MTS) configuration is used.

Lock (LCK0) works in an Oracle Parallel Server provides inter-instance locking.