PIA Training Centre Prime Communications

NOTES:

Database Administration (ORACLE 8)

Oracle Architectural Components
Users can be connected to an Oracle server in various configurations, including a direct host based
connection such as UNIX telnet session, a two-tiered client-server type connection such as pc-connection
to a UNIX or NT server using SQLNet or a 3-tier configuration where the client machine is connected to
an application server which in turn is connected to the Oracle database server.

A user process is spawned on a client machine in a client server configuration, when a tool such as
SQLPlus or a user developed application is started on a client. User process includes the User Program
Interface(UPI).

A server process is spawned that communicates with the Oracle Server on the host machine when a tool or
application runs on the same machine as the Oracle server(host based connection). The server process uses
an area of shared memory described as the Program Global Area(PGA). Server process uses the Oracle
Program Interface(OPI) to communicate with the Oracle server.

An Oracle server contains and Oracle instance and an Oracle database. The Oracle instance is a set of
memory structures and a group of background processes. The Oracle database is a set of files.
ORACLE_SID is an O/S environment variable that is set to define the Oracle instance name. An Oracle
instance can only reference one Oracle database at a time.

Oracle Instance Memory Structures

SGA: (System Global Area, sometimes call the Shared Global Area) is a group of shared memory
structures. The SGA is made up of the following structures:
1) The Shared Pool - Comprised of the Library Cache (also know as the Shared SQL Area) and the Row Cache
(also know as the Dictionary Cache or Data Dictionary Cache). Sized specified by SHARED_POOL_SIZE
variable in the parameter file.
 Library Cache - Contains the following for the most recently used SQL statement: Statement
text, parsed code (parse tree), and execution plan.

 Row Cache(also known as the data dictionary cache or dictionary cache) - Contains most recently
used Oracle data dictionary tables definitions and the system catalog and privileges of all users. Accessed
during the parse phase to resolve object names used in SQL statements and validate access privileges.

2) Database Buffer Cache - Contains x amount of database buffers as specified by the DB_BLOCK_BUFFERS
initialization parameter in the size of DB_BLOCK_SIZE. Also contains the dirty buffer write queue, which
holds dirty block buffers (i.e. already written to) until they can be written to disk. Least recently used(LRU)
algorithm used to decide which buffers to move out of the buffer cache to make room for new buffers to be read in.

3) Redo Log Buffer - The size of the redo log buffer is specified by the LOG_BUFFER initialization parameter.
Circular buffer containing information about changes made to the database. Records are written from the redo log
buffer to the online redo log files by the LGWR process.

!!Note!!: User session info is stored in Shared Pool when Oracle running in MTS (Multithreaded
Server) mode. Otherwise user info stored in the PGA.

PGA (Program Global Area) - Used by a single server or background process. Is allocated and
deallocated when a user process is started and terminated. In a dedicated server configuration the PGA
has and does the following:

Page 1
PIA Training Centre Prime Communications

 Area for sorting rows before returning them to a user process.

 Stages of cursor states for current session.
 Stores real variables instead of bind variables for executing SQL statements.
 The stack space is memory allocated to hold session variables and arrays.
 The user session data is additional memory for a users session.
 The PGA is writeable and non-shared.
!!Note!!: User session info is stored in Shared Pool when Oracle running in MTS (Multithreaded
Server) mode. Otherwise user info stored in the PGA in dedicated server environment.

Oracle Background Processes

There are four mandatory processes. If any of these processes fail the instance will crash and have to be
restarted!
PMON (Process Monitor)
 Cleans up abnormally terminated user sessions
 Rolls back any uncommitted transactions
 Releases locks held by a terminated process
 Frees SGA resources allocated to the failed process
 Restarts failed shared server and dispatcher processes
 Identified deadlocks
SMON (System Monitor)
 Performs automatic instance recovery
 Reclaims space used by temporary segments no longer in use
 Merges contiguous areas of free space in the datafiles. Known as defragging or coalescing.
DBWR (Database Writer)
 Writes all changed (i.e. dirty) buffers to datafiles.
 Uses a LRU (least recently used) algorithm to keep most recently used blocks in memory.
 Defers writes for I/O optimization.
Will write dirty blocks when one of the following occurs:
1. The dirty list reaches a threshold value
2. A process scans a specified number of buffers in the LRU list without finding a free buffer
3. A time-out occurs
4. A checkpoint occurs
LGWR (Log Writer) - Writes information from the redo log buffer to the redo log files.
 There is only one redo log writer per instance.
 A commit confirmation is not issued until the transaction has been recorded in the redo log file.
 Commits performed by other users before LGWR flushes the buffer on behalf of a user's commit
are piggybacked to achieve an average of less than one I/O per commit.
 During very long transactions, the redo log buffer pool can become more than one-third full
before LGWR writes it to the redo log file.
Will write redo log buffer entries to the redo log files when:
1. A commit occurs.
2. The redo log buffer pool becomes one-third full.
3. The DBWR completes cleaning the buffer blocks as during a checkpoint.
4. A LGWR time-out occurs(every 3 seconds).

Other Oracle background processes

CKPT (Checkpoint Process) improves the performance of databases with many database files. Is
mandatory starting with Oracle 8.
 Is enabled by setting the initialization parameter CHECKPOINT_PROCESS equal to TRUE.
The default is FALSE.

Page 2
PIA Training Centre Prime Communications

 Takes over LGWR's tasks of updating files at a checkpoint.

 Headers of datafiles and control files are updated at the end of a checkpoint.
 More frequent checkpoints will reduce the time required for instance recovery, but potentially at
the cost of reduced system performance.
ARCH (Archiver Process) copies online redo log files to a designated storage device when LGWR
switches to a new group.
 Copies redo log files to tape or disk for media failure recovery.
 Operates only when a log switch occurs.
 Is only needed when the database is running in ARCHIVELOG mode.

Oracle Database Files

Data Files:
 Stores the data dictionary
 User objects
 Before image data of current transactions
Redo Logs:
 Record all changes made to the database and are used for data recovery.
 Written in a circular fashion.
 There must be at least 2 redo log groups.
 Each redo log group should contain the same number of members.
Control Files:
 A binary file that describes the structure of the database.
 Required to mount, open, and access the database.
 Synchronization info needed for recovery is stored in the control file
 Recommended configuration is a min of 2 control files on separate disks.
Parameter File:
 Used to size the SGA and locate the control files at instance start up.
 Contains all the database initialization parameters.
Password File:
 Stores passwords for users with admin privileges.
 Used only with database authentication, not OS level authentication.
 V$PWFILE_USERS tells what users are in the password file.
 Naming convention = orapw<sid>.pwd
Command Syntax: ORAPWD file=... password =.. entries= ...
REMOTE_LOGIN_PASSWORDFILE parameter values:
1. NONE - no unsecured privileged sessions because no password file exists.
2. EXCLUSIVE - Password file used for that instance only. Needed for remote-nonsecure
connections.
3. SHARED - password file only allows access for admin option by SYS and the DBA using
CONNECT INTERNAL.
If a secure connection can be obtained you can use OS authentication. If no secure connection is possible
you must use oracle method.
Archived redo log files:
 Offline versions of the redo log files.
 Used for database recovery from media failure
Alert File:
 Logs all internal (ORA-600), block corruption (ORA-1578), and deadlock (ORA-60) errors.
 Logs all DDL and Server Manager commands such as STARTUP, SHUTDOWN, ARCHIVE
LOG and RECOVER.

Page 3
PIA Training Centre Prime Communications

 Logs the values of all non-default initialization parameters at the time of database and instance
startup.
 Located in destination specified by BACKGROUND_DUMP_DEST.
 Should be checked at least daily.
Trace Files:
 Contains internal errors detected by server or background processes.
 If dumped by a background process the file is located in destination specified by initialization
parameter BACKGROUND_DUMP_DEST.
 If dumped by server process the file located in destination specified by initialization parameter
USER_DUMP_DEST.
 Are created if SQL_TRACE parameter is TRUE.
 Are created if SQL_TRACE is enabled for a session.

SQL Execution Cycle (simple)

1) Parse - Compile SQL statement in shared pool and check user privileges.
2) Execute - Parsed code is executed
3) Fetch - Data retrieved and returned to the user.
SQL Execution Cycle (detail)
1) OPEN SQL Statement
a) Obtain cursor or memory address in library cache of shared pool, this is where the SQL statement will
execute. Cursor also holds the status info for each sql statement.
b) Obtain private sql area in PGA where statement return values will be stored.
2) PARSE SQL Statement
a) Check syntax
b) Create execution plan (also called a parse tree) and put into the shared pool.
3) Create BIND variables

This step is only performed for SELECTs. Not for INSERT, UPDATE, or DELETEs.

4) EXECUTE the statement.

a) Applies parse tree to data buffers.
b) Performs physical reads.
c) Performs constraint checking.
d) Changes data if needed.

5) FETCH
For SELECT only. Returns dataset into BIND variables.
Criteria for reuse of execution plan:
1. Current SQL statement must be identical to statement already parsed, including upper/lower case,
white space, and commas.
2. Referenced objects are identical.
3. The types and names of variables are identical.
4. The parsed execution plan must be in the shared pool at the same time the current sql statement
trying to reuse it is looking for it.

The SELECT(Query) operation:

 If the block is in memory, perform a logical read.
 If the block is not in memory, perform a physical read.
 If an index exists the server process reads in index blocks and the actual data blocks are read into
the database buffer using the ROWID.

Page 4
PIA Training Centre Prime Communications

 If no index exists, full table scan is performed. Every block of a table is read into buffer cache
until required row is found.
 Blocks read into db buffer cache using an index stay in cache longer than those from a full table
scan. This is called the modified LRU (least recently used) algorithm.

The UPDATE (DML) operation:

 Acquire data blocks into the db buffer cache.
 Acquire rollback blocks into the database buffer cache.
 Place exclusive row locks on rows that are about to change.
 Store record in the redo log buffer for before and after image.
 Save rollback data into a rollback segment block buffer.
 Applies changes to the database block buffer.

Using Administration Tools

For this section you must understand which OEM tools to use for various dba activities. Also for this
section you must be familiar with various Server Manager line mode commands. In addition you should be
familiar with what the SQLLoader, Import/Export utility and Password File utility are used for.
Be sure you have fully examined all of these tools and know how to use them! Don't
expect to just memorize what's in this document. This section presents a summary only
and you should really know these tools inside and out! This is the "bread and butter" of
any Oracle DBA worth their salt.
Server Manager Line Mode - Command utility used to perform all DBA tasks. Good for running unattended
"batch" operations because it can be executed from within a script. Can execute all SQL or PL/SQL statements. The
backslash "\" is the "server manager command" line-continuation character. No continuation character is needed for
multiple line SQL statements. Execute a multiple line SQL statement using a semi-colon or a forward slash "/" on the
last line by it self. Can use @ command to run a script.

Some server manager commands that do not require a connection to the database are: EXIT REMARK SET SHOW
SPOOL. Know all the SHOW commands and what they display.
Oracle Enterprise Manager - GUI used to tune, monitor and administer databases. Contains various components
such as the OEM Console which contains a menu, launch palette, navigator, map, job system, and event system. The
OEM Repository is a special set of tables used by OEM within a database for each administrator. Standard OEM
applications include the Instance Manager, Schema Manager, Security Manager, Storage Manager, SQL Worksheet,
Backup Manager, and Data Manager. Be sure you know what each of these modules does! A Performance Pack can
also be purchased with OEM. The Performance Pack contains the following additional modules: Top Session
Monitor, Lock Manager, Advanced Events, Tablespace Manager, Trace, and Expert.
SQLLoader - Oracle's tool for loading data from "flat" text files into the Oracle database.
Import/Export Utility - Oracles tool for exporting and importing data to and from Oracle-formatted files.
Password Utility File - Used to password file for a database.

Managing an Oracle Instance

Simple database startup events: 1) Start instance 2) Mount database 3) Open database
Simple database shutdown events: 1) Close database 2) Dismount database 3) Shutdown instance
The two automatically created DBA userid's are SYS and SYSTEM. Both are granted the DBA role. The
SYS userid default password is change_on_install and SYS is the owner of the database data dictionary
tables and views. The SYSTEM userid default password is manager and SYSTEM is the owner of
additional internal tables and views used by Oracle tools.

Page 5
PIA Training Centre Prime Communications

O/S Authentication
On UNIX user must be a member of the UNIX dba group.
Use when a secure connection is not possible.
Set REMOTE_LOGIN_PASSWORDFILE parameter to NONE. This is the default for this parameter.
CONNECT / AS (SYSDBA or SYSOPER)
**See Oracle notes for NT "group" and "userid" set up requirements to use O/S authentication. Oracle8 no
longer support setting the DBA_AUTHORIZATION=BYPASS parameter in the registry to allow
connections without using a password.
Password Authentication
Create the password file using ORAPWD on UNIX or ORAPWD80 on NT.
Set REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE (only one instance can use the
password file and the file contains other userids besides SYS and SYSTEM) or SHARED (more than one
instance can use the password file. SYS and INTERNAL are the only users known to the password file)..

Parameter File
Referred to as the init<SID>.ora file. If sid=orcl then file name = initorcl.ora . The file is read at time of
system startup. Changes to the parameter file will take effect only after the database has been stopped and
restarted.
Default Location in UNIX = /$ORACLE_HOME/dbs. Default location in NT = %ORACLE_HOME
%\database.
OEM needs to have a local copy of the parameters file to start a remote instance or db. However, OEM can
also store multiple database startup configurations in it's registry and this removes the need to have versions
of multiple parameter files stored on the machine acting as the OEM console.
For the test, be sure and know the uses and rules for setting the various parameters that live in the
init<sid>.ora file. You should know and be very familiar with the following parameters:
BACKGROUND_DUMP_DEST , COMPATIBLE , CONTROL_FILES , DB_BLOCK_BUFFERS ,
DB_NAME , SHARED_POOL_SIZE , USER_DUMP_DEST , IFILE , LOG_BUFFER ,
MAX_DUMP_FILE_SIZE , PROCESSES , SQL_TRACE , TIMED_STATISTICS .

Start Up and Shut Down an Instance

Database Startup Stages
 NOMOUNT - The instance is started. Parameter file read, SGA created, background processes
started, trace and alter files opened. Use for database creation.
 MOUNT - Control file opened for this instance. Used for DBA maintenance activities including
full database recovery, renaming datafiles, enabling or disabling redo logging. The DBA can alter the
control file in this mode. Control file is opened and info read but datafile and redo log files are NOT
verified in this step.
 OPEN - All files opened as described by the control file for this instance. Used for database
access for all users. Datafiles and redo log files are verified and opened.
TABLE for Database Startup Stages:
Stages What Happens When Used Tasks Involved
Starting Instance is Used at the time of  Reads the parameter file init<SID>.ora
an started either the creation of  Allocates the SGA
Instance the database or the  Starts the background processes
recreation of control  Opens the TRACE and ALERT files
files
Mounting Instance is Used while performing  Database associated with previously
the started and the specific Database started instance
Database Database is maintenance tasks  Locate the control files

Page 6
PIA Training Centre Prime Communications

mounted  Open the control files

 Reads the control files to obtain the
names and status of the data files and
the redo log files
Open the Instance is Used when Database is  Oracle verify that the data files and the
Database started, Database to be accessed by users redo log files can be opened
is mounted and  Oracle opens the data files and the
opened redo log files
 Oracle returns an error and does not
open the database if any of the data
files and redo log files are not present
 Oracle checks the database
consistency
 SMON initiates instance recovery

Instance Recovery
SMON will automatically perform instance recovery when opening up the database if database crashed or
was shut down using abort.
1. Rolls forward txns recorded in online redo log that have not been recorded in the data files.
2. Opens database before all roll forward txns have been applied.
3. SMON rolls back any uncommitted txns.
Database Shutdown Stages:
 CLOSE - Write buffer cache and redo buffer changes, close online redo log and data files. Control
file remains open.
 DISMOUNT - Control files are closed.
 SHUTDOWN - Trace and Alert files closed, SGA deallocated, background processes are stopped.
Additional Database Startup "Modes"
 Should always issue SHUTDOWN NORMAL and STARTUP NORMAL afterwards.

Database Shutdown options

 NORMAL - No new connections, waits for all users to disconnect, closes and dismounts database
before instance shutdown, no instance recovery required upon next db startup.
 IMMEDIATE - Rolls back all current uncommitted txns and disconnects users,closes and
dismounts database before instance shutdown, no instance recovery required upon next db startup.
 TRANSACTIONAL - No new connections, users disconnected when their current txn is
completed, when all "current txns are complete shutdown immediate is executed.
 ABORT - All txns are terminated, no txns are rolled back, instance terminated without closing the
data files, next startup will require instance recovery.

Trace and Alert Files

Alert file contains a "log" of messages and errors. Alert file written do while Oracle instance is running.
Records all db startups and shut downs. Oracle will create an Alter file upon db startup if one does not
exist. Location defined by the BACKGROUND_DUMP_DEST parameter. Monitor the Alter file to detect
internal and data block corruption errors, monitor database startup and shutdown operations and view the
nondefault values of other initialization parameters.
Trace files contain "dumps" created by background processes due to errors that occur during database
processing. These trace file locations are also defined by the BACKGROUND_DUMP_DEST parameter.
Trace files can also contain session process information dumped at the request of the user i.e. SQL_TRACE
parameter. The USER_DUMP_DEST parameter dictates the location of these "user requested" trace files.

Page 7
PIA Training Centre Prime Communications

MAX_DUMP_FILE_SIZE limits the size of the user requested traces files. This value is specified in O/S
blocks.

Creating an Oracle Database:

Be sure you have actually gone through the process of creating at least 5 different Oracle databases, using
varying logfile configurations and parameter settings!
A fully privileged and authenticated userid account must exist on the O/S. Must have memory and
sufficient disk space to start the instance and create the physical database files.
Create a minimum of 2 control files located on separate physical disks.
Multiplex (place on separate physical disks) groups of redo log files.
Datafile locations should be based upon minimizing fragmentation(separate into different tablesapces based
upon data life span i.e. temp vs perm), disk contention and separating objects (balance I/O by placing tables
and indexes on separate disks).
Know the Oracle software and database file locations based upon the OPTIMAL FLEXIBLE
ARCHITECTURE.

Manual Database Creation

1. Determine unique value to be used for instance and database name and what the database
character set will be.
2. Establish required O/S system variables
3. Create the parameter file - set DB_NAME, CONTROL_FILES and DB_BLOCK_SIZE at a
minimum in this file. If database name in the control file (create database <dbname>) dose not = database
name in parameter file (DB_NAME) instance will start but database will not MOUNT.
4. Create a password file
5. Start the instance (NOMOUNT). - connect as SYSDBA and startup nomount. Use PFILE clause in
the startup command to specify a parameter file that is not in the default location.
6. Run database creation script - KNOW ALL RULES ASSOCIATED WITH THE CREATE
DATABASE COMMAND OPTIONS!!! - If REUSE is specified the file must exist, otherwise use the SIZE
clause for files that don't already exist. MAXLOGMEMBERS, MAXLOGFILES, MAXDATAFILES,
MAXLOGHISTORY, and MAXINSTANCE actually allocate space in the control file. If these values are
changed the control file must be recreated. When a create database command fails, shutdown the instance,
manually delete all the files the unsuccessful create database command created, fix the errors and start
again.
7. Run script to create data dictionary and other post creation tasks.
After database creation through step 6 above, the following exist:
1. system tablespace datafile(s)
2. Redo log and control files
3. SYS and SYSTEM users with their default passwords.
4. System rollback segment
5. Internal tables/no dictionary views.
Creating Data Dictionary Views and Standard Packages :
The SYSTEM tablespace contains the data dictionary in 2 parts...base tables and views.
The 3 view categories are USER_ , ALL_ , and DBA_ . ALL_ and DBA_ usually contain an OWNER
field. A user with select any table privilege can query the DBA_ tables. Be familiar with the difference
between these 3 table types.

Page 8
PIA Training Centre Prime Communications

The DICTIONARY and DICT_COLUMNS views contain information about the data dictionary views and
what columns they contain.

sql.bsq - Creates the data dictionary base tables

catalog.sql - Creates the data dictionary views
catproc.sql - Sets up the ability to use PL/SQL and creates PLSQL packages to extend RDBMS
functionality including views used for LOBS, tablespace point-in-time-recovery and advanced queuing.
utl*.sql scripts - Create additional tables and views.
dbms*.sql - Creates package specifications and usually run during the execution of catproc.sql script.
prvt*.plb - Provide the wrapped package code.
The DBA_OBJECTS view contains the current STATUS of dependent views and procedures.
Maintaining the Control File :
The control file contains:
 database name and identifying information
 data and redo log file locations and names
 tablespace names
 database creation and time stamps
 current log sequence number
 information about checkpoint
 log history
 Recovery Manager backup information
The control file is read when a database is mounted. If a control file specified by the CONTROL_FILES
parameter can not be found the database will not be mounted.
The control file has 2 sections: 1) reusable 2) Non reusable. Reusable section is written to in a circular
manner similar to that for redo-logs.
Must recreate the control file to increase its size based upon new settings for parameters that affect it's size.
Names and locations of all control files for a given instance can be found in V$PARAMETER or
V$CONTROLFILE views.
For info on specific sections and values of parameters that affect the size of the control file query the
V$CONTROLFILE_RECORD_SECTION view.
CONTROL_FILES parameter can specify up to 8 files. To add a control file:
1. Shut down database
2. Use O/S to copy an existing control file to a new location under a new name.
3. Edit the parameter file CONTROL_FILES value to reflect the new file created in step 2.
4. Start up the database.

Maintaining the Redo Log Files

Oracle server requires a minimum of 2 online redo log file groups to function properly.
Each member in a log group is assigned an identical log sequence number each time Oracle starts to write
to a log group. Current log sequence number values are also stored in the control file and all data file
headers in order to synchronize and insure data integrity.
MAXLOGFILES limit is 255. LOG_FILES value can not exceed
MAXLOGFILES*MAXLOGMEMBERS.

Sequential writes to the current online redo log by the LGWR process occur when either of the following
happen:
1. A commit occurs
2. redo log buffer poll gets one-third full

Page 9
PIA Training Centre Prime Communications

3. A LGWR timeout occurs - one every 3 seconds.

4. Before the DBWR process writes dirty blocks from the db buffer cache to the data files.

When a logswitch occurs a checkpoint starts. During a checkpoint:

1. Dirty buffers in the db buffer cache that are identified in the log being checkpointed are written to
data files by DBWR.
2. CKPT updates the headers of all data files and it updates the control file to reflect the successful
completion of the checkpoint.

A checkpoint occurs when:

1. A log switch occurs
2. Instance is shutdown normal, transactional, or immediate
3. As dictated by the LOG_CHECKPOINT_INTERVAL (specified as a number of O/S blocks.
Checkpoint starts when this number of O/S blocks have specified have been written by LGWR. If value
exceeds redo log file size checkpoint will only occur at logswitch) and LOG_CHECKPOINT_TIMEOUT
( specify as a number of seconds. If set to zero (0) disables timeout checkpoints) parameters.
4. Requested on the fly by the dba.
Set the LOG_CHECKPOINTS_TO_ALERT = TRUE to log all checkpoints to the alter file.

To get redo log info:

1. ARCHIVE LOG LIST- server manager command.
2. V$DATABASE - name and log_mode
3. V$INSTANCE - archiver
4. V$THREAD - for group info such as current_group and log sequence number.
5. V$LOG - group#, members (count of), status,sequence#, bytes. Status values:
UNUSED - The group has never been written to.
CURRENT - The group is the current redo log group.
ACTIVE - The group is online and needed for instance recovery, but is not being written to.
INACTIVE - The group is online, but is not needed for instance recovery
6. V$LOGFILE - group#, status, member number. Status values:
INVALID - inaccessible
STALE - incomplete
DELETED - no longer being used
NULL - in use

To relocate a redo log file:

1. shut down database
2. perform O/S copy of redo log files to new location
3. startup mount
4. ALTER DATABASE RENAME FILE command
5. open database (alter database open)

To drop a redo log member:

ALTER DATABASE DROP LOGFILE MEMBER command. If in ARCHIVELOG mode, you cannot drop
an online member until it has been archived. If group is active, must force a log switch before dropping one
of its members. Cannot drop the last valid member of a group. Dropping a member does not remove its O/S
file.
Number of redo log files depends upon your system usage. If seeing many waits on a log group in the alter
log, need to add another group. Place members of groups on different disks. Put online and archive log files
on separate disks to reduce contention between ARCH and LGWR. Place datafiles and online redo files on
separate disks to reduce contention between LGWR and DBWR.

Page 10
PIA Training Centre Prime Communications

Minimum size of an online redo log is 50K. Final size might depend upon the frequency of log switches
and checkpoints, amount of redo generated by application and amount of available storage.

When all members of of the current or next group become inaccessible, the instance will shutdown.
Related info:
 Checkpoints should occur more frequently than log switches.
 If redo logs are big, increase checkpoints
 'ALTER SYSTEM SWITCH LOGFILE' forces a checkpoint.
 'ALTER SYSTEM CHECKPOINT' performs a fast checkpoint.

Related initialization parameters:

LOG_CHECKPOINT_INTERVAL: Number of blocks LGWR should write to redo log after a log
switch and before a checkpoint. Volume based.
LOG_CHECKPOINT_TIMEOUT: Number of seconds transpired before a checkpoint. Time based.
For checkpoints to occur as frequently as log switches:
set LOG_CHECKPOINT_INTERVAL > size of redo log file.
For checkpoints to occur more frequently as log switches:
set LOG_CHECKPOINT_INTERVAL = (((size of redo log file) / (db_block_size)) / desired frequency
of checkpoint between log switch) * number of operating system blocks in one Oracle block.

Maintaining Tablespaces and Datafiles

In this section we are NOT going to list the syntax and explanations of all the commands for creating and
administering tablespaces. This is the bread and butter of any Oracle dba worth anything and you should
know this backwards. I list what you should know. Feel free to take this study guide and enhance it with
syntax and your own examples if you wish, but you have enough practice with these commands that they
come natural to you.
The logical structure of a database includes tablespaces, segments, extents, and data blocks. Do not store
user data in the SYSTEM tablespace.
SYSTEM tablespace and a tablespace with an active rollback segment can NOT be taken offline.
Maxnumber of tablespaces per database is 64K. Number of tablespaces can not exceed the number of
datafiles. Max number of datafiles per tablespace is 1083. MINIMUM EXTENT can only be specified for a
tablespace, not the individual objects in a tablespace.
Know the storage clause inside and out.

Some defaults:
Min size of INITIAL is 2*db_block_size. Default size of INITIAL = 5 db blocks.
Min size of NEXT is one block. Default is 5 db blocks.
The min and default for MINEXTENTS = 1.
Min value of PCTINCREASE = 0 and the default is 50.
Min value of MAXEXTENTS=1 default is a function of the db block size.
Know how to make a tablespace temporary.
Know how to add a data file to a tablespace.
Know how to use the autoextend feature.
Know how to change the size of a data file manually.
Know how to change t he storage settings of a tablespace. ALTER TABLESPACE MINIMUM EXTENT to
change the minimum extent allocated.
Know how to take a tablespace offline.
Some ALTER TABLESPACE offline options:

Page 11
PIA Training Centre Prime Communications

 NORMAL - performs a checkpoint for all datafiles in the tablespace.

 TEMPORARY - performs a checkpoint for just the online datafiles in the tablespace.
 IMMEDIATE - does not ensure that tablespace files are available and does not perform a
checkpoint
Know how to move a datafile associated with a tablespace.
Know how to make a tablespace read-only.
Know how to drop a tablespace with INCLUDING CONTENTS and the CASCADE CONSTRAINTS
clauses. Use the including contents clause if the tablespace contains objects when you go to drop it.
Become familiar with the DBA_TABLESPACES, DBA_DATA_FILES,V$DATAFILE, V$TABLESPACE
views.
Storage Structure and Relationships:

Types of segments:
1. Table Segment - Unclustered or nonpartitioned tables.
2. Table Partition - Data within a table can be stored in multiple partitions and each partition can
exist in a different tablespace if desired. Each partition can have it's own storage parameters. Helps to
reduce I/O contention by distributing the table data. Requires the use of the Oracle8 Partition option.
3. Cluster - Rows are "clustered" together based upon key column value(s). A cluster can contain
multiple tables. Is a type of data segment. Objects in a cluster belong to the same segment and share the
same storage values.
4. Index Organized Table - Data is actually stored in the index. All data retrieved from index tree,
no table lookup needed.
5. Index Partition - Contain index partitions. An single index partition can NOT span multiple
tablespaces. Helps to reduce I/O contention by distributing the index data. Requires the use of the Oracle8
Partition option.
6. Rollback Segment - Holds rollback data for undo changes, read-consistency and recovery.
7. Temporary Segment - Contain intermediate results data such as that used for sort operations.
8. LOB Segment - Large Objects such as pictures, documents or video clips. If the actual value
placed in this column is large oracle actually stores the data in separate LOB segments and the table
segment will only contain a locator or pointer to the LOB segment.
9. LOB Index - Is automatically created when an LOB segment is created. Used to lookup LOB
segment values.
10. Nested Table - A table that is actually stored in a column of a table. Also referred to as an inner
table. Stored in it's own segment separate from the "parent" table. requires the Oracle8 Objects option.

Managing Rollback Segments

Rollback Segments:
 Store uncommitted and "pre-change" data and file and data block ids to support rollback, recovery
and read consistency.
 Contains a header that stores info about current txns using the rollback segment.
 A txn can only use ONE RB segment.
 Many concurrent txns can use the same RB seg.
 At instance startup Oracle requires at least one rollback segment in the

SYSTEM tablespace.
 If a user is going to create tables in other tablespaces besides SYSTEM at least one more rollback
segment is required.
 All rollback segments should be the same size. The PCTINCREASE storage parameter value is
not allowed. Is always equal to zero (0) for rb segs.

Page 12
PIA Training Centre Prime Communications

 Requires a minimum of 2 extents of the same size.

 Always store in their own tablesapce to avoid I/O contention! The TABLESPACE clause is
optional in a CREATE ROLLBACK SEGMENT command. If it is not included, the rollback segment will
be created in the SYSTEM tablespace.

Types of rollback segments:

 System - Created in the system tablespace at time of database creation. Used for changes to
objects in system tablespace only.
 Private(default!!) - Exclusive to a single instance. Must be included in the parm

ROLLBACK_SEGMENTS in the init<sid>.ora file or brought online manually to be recognized by the

instance.
 Public - Forms a pool of rollback segments that can be used by any instance. Useful with the
Parallel Server option.
 Deferred - Created when a rollback txn is executed against a tablespace that is offline. Created
automatically by Oracle in the SYSTEM tablespace and no DBA maintenance is required.
Public and private rollback segments are the only ones that can be created by a DBA.
Need CREATE ROLLBACK SEGMENT system privilege to create rollback segments.
Use SET TRANSACTION USE ROLLBACK SEGMENT <rb_seg> command to assign a txn to a specific
rb segment. Otherwise Oracle selects rb seg based upon the one that is available with the fewest number of
txns in it.

Managing Temporary Segments:

Created and used by Oracle when complete sort operation can not be performed in memory. Some
statements that generate sort activity are : ORDER BY , CREATE INDEX , SELECT DISTINCT , GROUP
BY , UNION .
SORT_AREA_SIZE parameter controls amount of memory used by a process for sorting.
Know how to define a tablespace as TEMPORARY.
Permanent tablespace may contain multiple temporary segments. Not recommended to use a permanent
tablespace for temporary segments because of the amount of fragmentation created by temp segs. SMON
reclaims temp segs for reuse from permanent tabelspaces.
In a temporary tablespace oracle creates a single sort segment for use by all requiring sort space. Extents
can not be shared by multiple sort txns. Sort segment created by first statement requiring it and is
deallocated at instance shut down. No limit to the number of extents a sort segment can have in a temporary
tablespace. The sort extent pool lives in the SGA and contains information about available extents for
sorting.
Create multiple TEMPORARY tablespaces to improve sort performance and decrease frequent allocation
and deallocation of sort space. Use the DEFAULT STORAGE clause to control extent sizes. Guidelines:
1. Set INITIAL and NEXT extent sizes the same.
2. Set them to: (multiple of SORT_AREA_SIZE) + DB_BLOCK_SIZE.
3. Use PCTINCREASE = 0 so that all extents remain the same size.
4. Use different default storage clauses for different temporary tablespaces based upon sorting needs
of users assigned to the temp tablespace.
Use the following views to get info about temporary segment usage:
1. DBA_SEGMENTS - info on both types of temp segs.
2. V$SORT_SEGMENT - status of sort extent pool. Has info on sort segments in TEMPORARY
tablespaces.
3. V$SORT_USAGE - active sort info for instance. Join with V$SESSION on saddr column to see
current session sort activity. Get user name from the V$SESSION view because the V$SORT_USAGE
USER column shows the user querying the view.

Page 13
PIA Training Centre Prime Communications

Page 14