SkillBuilders | Oracle 9i Administration | Lesson 2: Oracle Architecture Part 1

Instructor's Notes

(If applicable)

Lesson 2: Oracle 9i Architecture, Part 1
A successful DBA must understand the architecture of the system. This lesson provides you with an introduction to Oracle architecture, including a discussion of instance and database, plus an in-depth look at the physical files that make up the database. You will begin to understand the “big picture”, i.e. you will be introduced to the memory areas, files, and processes that make the database work. Next, we will teach you about the difference between the terms “instance” and “database” (so you will understand when someone says “is the instance started?”). Lastly, this lesson will teach you about the operating system files that make up an Oracle database. This will increase your ability to create, control and tune the database!
This lesson is one in three in SkillBuilders Oracle Administration course dedicated to Oracle Architecture.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

In order to understand the concepts taught in this chapter, you will need to know several things (many of which can be learned in SkillBuilders’ Introduction to Oracle9i class):

Basic computer concepts such as input/output (I/O), memory and disk files.
Basic Oracle concepts such as objects (table, row, view).
Simple SQL SELECT operations.
SQL*Plus commands such as DESCRIBE
How to query the data dictionary (see the lesson titled “A DBAs Introduction to the Data Dictionary” in SkillBuilders’ Oracle Database Administration course).

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Though we often use the phrase “Oracle database”, understanding the architecture requires that we understand that there are two critical concepts to an Oracle database, the instance and the database.
The database is a collection of operating system files that contain the data and control information required to:

present the data in the logical format of a table.
keep track of the objects that make up the database
recover the database

The database is identified by a database name. The database name is defined with the CREATE DATABASE command; it can be checked by querying a dynamic performance table:
SQL> SELECT name FROM v$database;
NAME
------------------------------
CLASS2

Notes for this slide continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

An instance is memory structures and processes (software) that are required to access the database. An instance must be started in order to access the database. An instance can point to one and only one database; however, a database can be pointed to by more than one instance - this is called Real Application Clusters (formerly Oracle Parallel Server). See the next page for more information on RAC.
The instance is identified by the Site Identifier, or SID. The SID can be checked by querying a dynamic performance table:
SQL> SELECT instance_name FROM v$instance;

INSTANCE_NAME
----------------
class2

Supplemental Notes
- The SID is defined when the instance is created; under Windows this is done with the ORADIM utility. Under Unix, simply set the OS environment variable ORACLE_SID. See the lesson Creating a New Database in SkillBuilders'’ Oracle Database Administration course for more information.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Oracle9i Enterprise Edition offers a feature called “Real Application Clusters” (formerly called Oracle Parallel Server). When this feature is configured, multiple instances (typically on separate servers) access the same set of database files. This provides high-availability – if one instance crashes there are others still available. This is also a scalability feature – more sessions and processing can be supported by distributing the workload across the multiple instances/servers.
See the Oracle9i Concepts Guide and the Oracle9i Real Application Clusters Concepts Guide for more information on RAC.

Other Text:
(Examples or comments displayed on slide, if any).

Real Application Cluster environment

Instructor's Notes

(If applicable)

The illustration above shows many of the processes, memory structures and operating system files that make an Oracle database work. The three lessons on Oracle architecture provided in this course will teach you the purpose of each structure. This remainder of lesson will focus on the operating system files.
Illustration KEY:

The ovals (see CKPT) represent the background processes (i.e. programs) that are active for the database.
The boxes (see “Buffer Cache”) represent memory areas used by the database.
The background processes (ovals) together with the memory areas (boxes) constitute the Oracle instance.
PGA - Program Global Area memory areas.
SC – Sub-caches

Notes for this slide continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

DBWn
CKPT
PMON
Dedicated Servers
to clients
SGA
Buffer Cache
Shared Pool
Redo Buffer
SC
SC
Large Pool
Java Pool
I/O
I/O
RECO
SMON
to remote DB
K
R
DISP
Shared Servers
to clients

Instructor's Notes

(If applicable)

Notes continued from the previous page:

K – Keep Pool
R – Recycle Pool
CONTROL – Control File(s)
DATA – Database Files
LOGS – Redo (transaction) Log Files, sometimes called online (redo) logs
A. Logs – Archived Redo Log Files, sometimes called offline (redo) logs

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The database files (A.K.A. data files) contain the user and system data stored in the database. ( Note that there may be, at a minimum, just one database file, containing the SYSTEM tablespace. However, “real” databases always have more than just one datafile.)
As shown above, a database file is created with the CREATE TABLESPACE command. Then, you can create objects (tables and indexes for example) in the tablespace – which directs Oracle to put the object in the related database file.
Don’t worry at this time about the syntax of the CREATE TABLESPACE and CREATE TABLE commands – we will cover those in detail later in the lessons Tablespaces and Tables.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> create tablespace testts
2 datafile 'C:\oracle\oradata\class2\testts.dbf' size 1m;

Tablespace created.

SQL> create table test
2 (c1 number)
3 tablespace testts;

Table created.

Instructor's Notes

(If applicable)

Database files contain objects in a container called a segment. A segment is the storage used by one database object. (Exceptions: clusters can combine rows of two tables into the same segment; bitmap join indexes combine row information from two tables.)
Segments are comprised of one or more extents of disk storage. An extent is a contiguous area of disk storage.
Data files are formatted by the Oracle software into blocks. The default Oracle block size is set when the database is first created and can not be changed. The block size ranges from 2K to 32K. These Oracle blocks are the smallest unit of allocation and I/O.
Supplemental Notes
- Oracle9i introduced support for multiple blocksizes in a single database. Refer to the section “Multiple Blocksize Support” in the lesson Tablespaces for more information.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Two data dictionary views (at least!) are useful for this discussion, DBA_DATA_FILES (see above) and DBA_SEGMENTS (see next page).
DBA_DATA_FILES contains one row for every data file allocated to the database. In this example we can see that tablespace EXAMPLE contains two datafiles, EXAMPLE01.DBF and EXAMPLE02.DBF.
Supplemental Notes
- Note that when we create an object such as a table, we do not have control over which datafile(s) within the tablespace our object is placed in.
- See the supplied script DATAFILES.SQL for a working example of the queries shown in this section of the lesson.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select tablespace_name, file_name from dba_data_files
2 order by 1
3 /

TABLESPACE_NAME FILE_NAME
-------------------- -------------------------------------
EXAMPLE C:\ORACLE\ORADATA\DAVE\EXAMPLE01.DBF
EXAMPLE C:\ORACLE\ORADATA\DAVE\EXAMPLE02.DBF
INDX C:\ORACLE\ORADATA\DAVE\INDX01.DBF
SH C:\ORACLE\ORADATA\DAVE\SH.DBF
SYSTEM C:\ORACLE\ORADATA\DAVE\SYSTEM01.DBF
TOOLS C:\ORACLE\ORADATA\DAVE\TOOLS01.DBF
UNDOTBS1 C:\ORACLE\ORADATA\DAVE\UNDOTBS01.DBF
USERS C:\ORACLE\ORADATA\DAVE\USERS01.DBF

Instructor's Notes

(If applicable)

The example above shows some of the segments within my USERS tablespace.
We can see that the tablespace contains multiple segments, the segment name is the same as the object name, the segment type is generally TABLE or INDEX (there are several more types including INDEX PARTITION, TABLE PARTITION, ROLLBACK, UNDO. See the Oracle9i Database Reference for a complete list of segment types.)
Note that by dividing BYTES by BLOCKS we compute the block size of the segment. The block size is the smallest unit of I/O and allocation. (This can also be found in DBA_TABLESPACES.BLOCK_SIZE column.) All segments in the same tablespace will always have the same block size.
Supplemental Notes
- There is also a dictionary view called DBA_EXTENTS, which contains one row for every extent in every segment in the database.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select segment_name, segment_type AS type , extents,
2 blocks, bytes, bytes/blocks AS blksize
3 from dba_segments
4 where tablespace_name = 'USERS'
5 and segment_name like 'B%'
6 /

SEGMENT_NAME TYPE EXTENTS BLOCKS BYTES BLKSIZE
------------ ----- -------- ---------- ---------- ----------
BIG_TABLE TABLE 120 49664 406847488 8192
BONUS TABLE 1 8 65536 8192
BIG_IDX INDEX 27 1536 12582912 8192

Instructor's Notes

(If applicable)

All changes to blocks in the buffer cache are automatically logged to the redo log buffer. The redo log buffer is written to disk when a COMMIT is issued. Specifically, the changes are recorded as REDO (a record of the change made) and are written to the redo log files when a COMMIT is issued.
In the illustration above, note that at time 3 (COMMIT), the redo log buffer is written to the redo log file (disk) and that the dirty blocks in the buffer cache are not written to the data file(s). Since the dirty blocks in the buffer cache are not written to disk at COMMIT time, this process of writing redo to the redo log files is a critical recovery mechanism. If the database were to crash before the datafiles are updated, the redo log files would be used to reapply all committed transactions (known as “instance recovery”).
Supplemental Notes
- A log entry consists of the old and new values of any change. A log entry is also made for commit, rollback or checkpoint operations. The LOGMINER utility is available for viewing and extracting the contents of the redo logs.

Notes for this slide continue on the next page…

Other Text:
(Examples or comments displayed on slide, if any).

Used during recovery
Protects all committed transactions

Instructor's Notes

(If applicable)

Supplemental Notes (continued from the previous page)
The redo log files have a sequence number which is incremented when a log fills and Oracle begins writing to the next log file. These redo log sequence numbers increment by one. These redo log sequence numbers are recorded in the very first block of each database file and also in the redo log files and are used to ensure that all the files which make up the database are in synchronization when the instance is first started.
A number of dynamic performance views are available for interrogating the status of the online and archived logs:

V$LOG – Sequence number, SCN and status of online logs.
V$LOGFILE – Actual OS file name and status.
V$ARCHIVED_LOG – Actual OS file name of archived log, sequence number, SCN

Note that Oracle actually writes “batches” of redo log entries from the redo log buffer to the redo log file. This increases the performance of this operation.
Some operations support the NOLOGGING feature. This significantly reduces the amount of REDO generated.
Refer to the lesson Managing Redo Logs for an in-depth learning experience on adding, removing and managing redo log files.

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

Logs are used by Oracle in a cyclical fashion. When an online log is filled, a redo log switch occurs. This is the process of stopping the writing of REDO to one log and starting the writing to another. A log switch initiates a “checkpoint” operation. Checkpoint is the process of writing all dirty blocks to disk. Since the log entries are protecting the changes made in the blocks until such time that the blocks are written to disk, Oracle will not overwrite and destroy a log until the checkpoint completes. Therefore, we want the checkpoint to complete before the remaining logs fill and Oracle needs the log again. If not Oracle will suspend database activity temporarily and write a warning to the alert log. This is a tuning issue; adding more redo log files, increasing the size of the log files, and/or adding more database writers can help solve this issue.
Oracle requires that a minimum of 2 redo log files be created (see the CREATE DATABASE command). Redo log files can be multiplexed (A.K.A. mirrored) by Oracle via creating logfile groups. See the lesson on Managing Redo Logs for more information.

Other Text:
(Examples or comments displayed on slide, if any).

Log Switch

Instructor's Notes

(If applicable)

A production database should be configured to run in ARCHIVELOG mode. This means that whenever an on-line redo log fills, it will be copied (saved) to an archive destination. The database will not overwrite the log file until it has been archived. (In fact, the database will “hang” until the log is archived.)
THE DATABASE CAN NOT BE FULLY RECOVERED UNLESS ARCHIVING IS ACTIVE.
Mini-Workshop
1. Check the log mode of your database with the query shown above. (If you are in NOARCHIVELOG mode, don’t worry; you will fix that in the Managing Redo Logs lesson.
2. Determine if the ARCH background process has been started to automatically archive the filled online logs. Use the following SQL*Plus command:
- show parameter log_archive_start

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select name, log_mode
2 from v$database;

NAME LOG_MODE
--------- ------------
DAVE ARCHIVELOG
Do the mini-workshop

Instructor's Notes

(If applicable)

The control file is a relatively small file (e.g. 10MB) which holds control information for the database. This includes the names of the database files, the names of the redo log files as well as synchronization information (checkpoint SCN) required to ensure that all the files belonging to the database are kept in synchronization. The name of the database is also kept within the control file. If the Recovery Manager (RMAN) utility is used to backup and recover the database, the control file is also used as a repository of backup history information. (Refer to the User-Managed Backup and Recovery lesson for more information.)

Other Text:
(Examples or comments displayed on slide, if any).

Instructor's Notes

(If applicable)

The control file is used when the instance is first started (during the “MOUNT” phase) to locate the names and locations of the datafiles and redo log files. (You will learn more about “mounting” the database later in the lesson Starting and Stopping the Instance.)
If the control file is not available the instance cannot be started. Therefore, I recommend keeping three mirror-image copies of this file. We’ll see in the lesson Managing Control Files that this is very easy to do.
Oracle will perform a small amount of I/O to the control file during normal database operations to record any new files added to the database and to keep recovery information in sync.
The V$CONTROLFILE view contains the location and names of all control files in use.
Mini-Workshop
1. Use the query shown above to determine the number and location of the control files in use by your database.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select name from v$controlfile;

NAME
------------------------------------
C:\ORACLE\ORADATA\DAVE\CONTROL01.CTL
C:\ORACLE\ORADATA\DAVE\CONTROL02.CTL
C:\ORACLE\ORADATA\DAVE\CONTROL03.CTL
Best to have multiple, mirrored copies
Oracle will mirror for you
Recommend 3 copies on different disks
Do the mini-workshop

Instructor's Notes

(If applicable)

Temporary files are used by Oracle to support large sorts or result sets that cannot be held entirely in the PGA memory (you will learn more about PGA memory in the lesson “Architecture, Part II”. The ORDER BY clause and CREATE INDEX statement are examples of operations that might cause sort operations to occur.
Temporary files are connected to the database via a temporary tablespace. Every user created in the database has a temporary tablespace assigned (assigned with CREATE or ALTER USER). Use the DBA_USERS dictionary view to determine the temporary tablespace assigned to a user; query the DBA_TEMP_FILES view to see all temp files and the tablespace to which they belong.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select username, temporary_tablespace
2 from dba_users
3 order by 1;

USERNAME TEMPORARY_TABLESPACE
---------- ------------------------------
ADMIN1 TEMP2
APPTUNE TEMP2
DBSNMP TEMP2

Instructor's Notes

(If applicable)

As of Oracle8i, Oracle supports the concept of a temporary table (created with the CREATE GLOBAL TEMPORARY TABLE statement). The data for all temporary tables and indexes is held within the users temporary tablespace.
Oracle will not allow permanent objects to be created within a temporary tablespace.
SQL> create table test
2 (c1 number)
3 tablespace temp2;
create table test
*
ERROR at line 1:
ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace
DBAs should not waste time and resources backing up a temporary tablespace as its contents cannot be restored.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select tablespace_name , contents
2 from dba_tablespaces
3 /

TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
UNDOTBS1 UNDO
TEMP TEMPORARY

Instructor's Notes

(If applicable)

Query the DBA_TEMP_FILES view to see all temp files and the tablespace to which they belong.

Other Text:
(Examples or comments displayed on slide, if any).

SQL> select tablespace_name, file_name
2 from dba_temp_files
SQL> /

TABLESPACE_NAME FILE_NAME
-------------------- -----------------------------------
TEMP C:\ORACLE\ORADATA\CLASS2\TEMP01.DBF

Instructor's Notes

(If applicable)

There are several types of parameter files associated with an Oracle database. They contain startup parameters that affect the database and network option environments.
The networking-related parameter files control SQL*Net, the networking software supplied with Oracle. (SQL*Net is actually now called “Oracle Net