Oracle Database Architecture

By Sandeep Desai (http://www.thedesai.net)

email: s_desai@hotmail.com

 

Books

·           Oracle 10g The Complete Reference

Web

·           http://otn.oracle.com

·           http://technet.oracle.com/documentation/content.html (Oracle Documentation)

·           www.orafaq.net (Lot of Info and OCP)

·           www.jlcomp.demon.co.uk/faq/ind_faq.html (Users Coop FAQ)

·           http://www.dbasupport.com Lots of Oracle Information (OCP Info)

·           http://www.ixora.com.au Good information on DBA queries

·           http://home.clara.net/dwotton/dba/index.htm DBA Information

·           www.oraclepressbooks.com

·           www.orablog.com links to various Oracle Blogs

·           www.rittman.net  Oracle Datawarehouse/OLAP/DBA Blog

·           Utility to dump table to flat file

Database Server Architecture

 

DBA Fundamentals 1 Exam tips

·           Understand DBA_USERS and DBA_PROFILES

·           Role related dictionary tables

 

Instance is memory and process

Database is physical storage, tables, indexes etc

 

SGA (System Global Area)

Buffer Cache

In units of db_block_size,

Java Pool

 

Large Pool

 

Redo log buffer

Information stored to redo DML statements

Shared Pool

·           mandatory library cache consisting of parsed SQL statements and its execution plan.

·           mandatory dictionary cache (row cache) table, column, user, password info, privilege

·           optional  session information about user process

 

PGA (Program Global Area) Users Private Global Memory

·           User session data

·           Cursor state

·           SQL bind variable values

 

For Shared Server (MTS) User data is stored in SGA instead of PGA

 

Dedicated Server, a process is created for every user session. In Shared Server (MTS) a pool of process is created which serves multiple users (Good for large number of users)

 

Background Processes

ARCH
 
Archiver
 
Periodically Archive Redo logs. Slows down database. Require archivelog option to be set
 
DBW0
 
Database Writer (max 10 process allowed)
 
write dirty data blocks from buffers to disk, writes are done 
·           when server needs more space for new requests
·           when LGWR makes request (checkpoint)
·           Every three seconds due to a timeout
·           number of dirty buffer reaches threshold value
 
 
LGWR
 
Log Writer 
write red log entries from redo log buffer to redo log files, write are done
·           when a transaction commits
·           when buffer is 1/3 full
·           when more than one megabyte of changes recorded
·           before DBW0 writes modified blocks to data files
 
CKPT
 
Checkpoint
 
 
 
PMON
 
Process Monitor
 
 
 
SMON
 
System Monitor
 
Handles instance recovery and instance startup and periodically coalesces free space in tablespace
 
User
 
 
 
 
 
Server
 
 
 
Reads data from disk
 
Dispatcher
 
 
 
Used in MTS (shared server) use a pool of process to handle user session
 
 
Listener Process creates new dedicated server process on user connection. If MTS then listener forwards request to dispatcher process which places the request in the request queue in the SGA. Requests are serviced in FIFO. Response is placed in dispatchers response queue. Dispatcher returns response back to user process

 

Processing a Select statement

  1. Search shared pool for parsed SQL query in library cache
  2. Validate statement
  3. Validate data sources (table and columns valid)
  4. Acquire locks
  5. Check privileges
  6. Parse statement if not in library cache then create execution plan and place in library cache
  7. Execute statement: retrieve from disk to cache
  8. Fetch values from cursor

 

Processing a DML statement

  1. Parse statement, if statement not in library cache, then create execution plan and place in library cache
  2. Execute statement: For update or delete, retrieve data from disk to buffer cache, implicitly acquire lock on data being changed. Make change in data cache. For insert, retrieve block from disk that has enough space for new data. Write old version of data to the undo segment acquired for this transaction. Lock is required on undo segment
  3. Generate redo information: store redo information so we can recover from a database crash or disk file damage

 

Oracle generates a SCN (System Change number for each transaction)

Processing a commit

·           does not safely write data to disk only checkpoint, timeout, or buffer full does that

·           Release table/row locks acquired by transaction

·           Release undo segment locks acquired by transaction

·           Generate redo for committed transaction by user process. Flush redo log buffer to files both committed as well as uncommited

Oracle Administration tools

Graphical tools use Java and run on all platform

Universal Installer

 
Install software Runs Database Configuration Assistant at the end. Store log of installation activity in oraInventory directory. Can install automatically with a response file

Database Configuration Assistant 

Create database templates. Create database from templates

Network Configuration Assistant 

Create and configure files listed below
<OH>/network/admin/tnsnames.ora client side file has alias for database connection. 
<OH>/network/admin/listener.ora server side files lists port and database for which to accept connections

Migration Assistant 

Migrate lower version database to Oracle9i

Enterprise Manager 

Administrative tool
Database Wizards: backup, data management, analyze etc
Change Management Pack:
Database Applications : SQL*Plus Worksheet
Diagnostic Pack: monitor locks, top session, top SQL etc
Service Management: Net Manager to manage listener.ora and tnsnames.ora and LDAP
Standard Management Pack: Change Manager & Performance Manager
Tuning Pack:
 
OEM can be run standalone or via OMS (Oracle Management Server) which is a middleware server
OMS connects to a database repository. It also connects to an Intelligent Agent for scheduling jobs and events. Intelligent Agent is also used for discovering targets such as database, Application Server etc. 
Run emca to create OMS repository

DBA Studio

Administrative tool lets you browse and update instance, schema, users, storage etc

 

Command line

SQL*Plus 

<OH>/bin/sqlplus scott/tiger@orcl

SQL*Loader

 
<OH>/bin/sqlldr

 

Optimal Flexible Architecture: Standard way to setup Oracle database and software

·           Designed to minimize I/O contention for large amounts of database data files and software

·           Designed to be flexibly grow the database

·           Encourage consistent database file-naming conventions. So DBA can distinguish between data files, control files and other files. Also associate data files to tablespace

·           Keep contents of tablespace separate to minimize fragmentation and I/O contention e.g. separate tablespaces for tables and indexes

·           Support multiple Oracle Homes, allows you to run multiple databases at the same time

·           Allow one listener to support connections to multiple databases

·           Keep administration information of each database separate

 

Default Installed Database Accounts

olapsys

manager

OLAP Catalog schema (CWM2)

scott

tiger

Demo schema contains couple of tables emp, dept etc

sh

sh

Sales History Demo Schema

system

manager

 

sys

change_on_install

Most powerful user, owns all system tables

sys as sysdba

change_on_install

Connect with sysdba privilege

 

 

 

 

Operating system authentication: User can login as sqlplus /. Requires creation of an OS group called dba. osoper and osdba OS groups for sysoper and sysdba. sysoper allows you to stop/start instance mount, open, backup database, initiate archiving redo logs

 

Windows step for connecting as sysdba for a user

·           Create new Windows NT user group ORA_<SID>_DBA and ORA_<SID>_OPER for specific instance or create ORA_DBA and ORA_OPER for any instance

·           Add a user to the group

·           Add SQLNET.AUTHENTICATION_SERVICES=(NTS) in sqlnet.ora

·           set REMOTE_LOGIN_PASSWORFILE=NONE in init.ora

·           sqlplus "<user> as sysdba"

 

Authentication with password file

·           orapwd FILE=d:\orahome\o92\dbs\orapwdorcl.pwd PASSWORD=rhea entries=5. Note password for user sys will become rhea

·           default location is <ORACLE_HOME>/dbs on UNIX and <ORACLE_HOME>\database on Windows

·           remote_login_passwordfile=exclusive password file belongs to one instance

·           remote_login_passwordfile=shared in init.ora means only sys can connect remotely also password file shared by multiple instances

·           create user rhea identified by rhea

·           grant sysdba to rhea

·           connect rhea/rhea@sbdesai-sun as sysdba

·           v$pwfile_users contains list of users who have sysdba, sysoper privilege

 

How does instance figure out location and name of password file?

 

Oracle Managed Files

·           Do not need to specify datafile names while creating tablespaces

·           Deletes data files after tablespace dropped

·           set db_create_file_dest parameter in init.ora, directory location get used in create tablespace

·           set db_create_online_log_dest_<n> parameter to set online redo log directory, if not specified save control files in db_create_file_dest directory

·           If both set and control_files not set then control files are placed in db_create_online_log_dest_1 and db_create_online_log_dest_2

·           Control files are named ora_%u.ctl where u is oracle generated unique number e.g ora_cmr3u45r.ctl

·            

 

Startup/Shutdown database (requires sysdba privilege)

·           sqlplus "sys as sysdba"

·           startup 

·           shutdown

startup nomount [PFILE=/dir/init.ora]

 
Does not mount database (only start instance)
·           read init.ora
·           allocate SGA
·           start background processes
·           open alert file and trace files

startup mount [PFILE=/dir/init.ora]

 
Useful for recovering database
·           start instance
·           read control files
·           attach database (does not open)
alter database mount

startup open [PFILE=/dir/init.ora] (default)

 
start instance and attach database and open database

startup force [PFILE=/dir/init.ora]

 
use when errors

startup recover

 
 

startup restrict

 
only DBA can use database, same effect can be achieved by alter system quiesce restricted

startup migrate

 
 

alter database mount

 
·           associate database with previously mounted instance

·           locate and open control files listed in init.ora

·           read control files to obtain name and status of datafiles and redo log files

 

shutdown normal (default)

 
low priority shutdown
·           wait for all users to logout
·           new users not allowed
·           not instance recovery required

shutdown immediate

 
new users not allowed
terminate all user connections immediately
rollback uncommitted transactions

shutdown abort

 
return immediately (used when disk failure)
·           abort all transactions
·           disconnect all users 
·           do not roll back transactions
·           requires instance recovery

shutdown transactional

 
·           finish all transactions
·           no new transactions allowed
·           client disconnected at end of transaction
·           shutdown immediate

 

alter system suspend (allow existing I/O to finish, halt all I/O to datafiles, suspend all new requests)

alter system resume

 

Diagnostic files

Trace Files, are generated by network or background processes when something goes wrong. File name is process name concatenated with instance_name from init.ora and are stored in backgroud_dump_dest specified in init.ora e.g <OH>/rdbms/log/r92031_pmon_966.trc. Setting for where to dump listener trace files in <OH>/network/admin/listener.ora

 

Log Files, most important is alert_<sid>.log records health of database, records start/stop of database, creation of new redo logs, creation of tablespace, addition of datafiles to tablespace and errors generated by Oracle. Important ORA- errors logged here

Database creation

Prerequisites

·           Whether machine has minimum system requirements in terms of memory, hard disk and CPU power

·           Has all proper OS patches installed

·           Has three separately controlled disk resources (for better performance) can be done with one.

·           Configure environment variables such as ORACLE_BASE, ORACLE_HOME, ORACLE_SID, ORA_NLS33, LD_LIBRARY_PATH etc

·           Shut down and backup other databases running on host

·           Perform UNIX specific tasks, such as editing oratab entries to include name of database created so that database will automatically start on system startup

·           Use OFA (Optimal Flexible Architecture)

o         create admin directory under ORACLE_BASE

·           Important init.ora parameters to configure

o         DB_NAME: local name of database

o         DB_DOMAIN: domain location of database e.g oracle.com

o         DB_BLOCK_SIZE: size in bytes of data block support four additional non standard block size. Standard block size for system tablespace

o         CONTROL_FILES: name or list of files, contain list of all data files used by the database, files overwritten during database creation

o         DB_BLOCK_BUFFERS: Size in number of buffer cache in SGA

o         DB_CACHE_SIZE: size in bytes of default buffer pool for buffers with standard block size. Replacement for DB_BLOCK_BUFFERS

o         LOG_BUFFER: Size of redo log buffer in bytes

o         UNDO_MANAGEMENT: set to AUTO so that Oracle will handle undo management automatically. Undo segment is same as rollback segment

o         UNDO_TABLESPACE: name of tablespace that has undo segments

o         PROCESSES: Number of process that can connect to Oracle at any give time. Includes background process (atleast 5)

 

Server Parameter file

·           Create init.ora

·           Create database

·           create spfile from pfile='/oracle/admin/oracle/pfile/init.ora'

·           alter system set share_pool_size scope = spfile|memory|both

·           startup will read server parameter file everytime

 

Databases can be created using Database Configuration Assistant (DBCA)

·           Create Database from predefined template.

·           Generate scripts to create database

·           Delete Database

·           Clone Database with and without data

·           Create/Manage Delete Templates

o         A template is a definition of a database

o         Contains location of control files, redo log file, database datafiles

o         location of administration files

o         size of SGA

o         init.ora parameters

o         Can use variables like ORACLE_BASE, SID etc

 

Creating Database manually

·           create init.ora

·           verify directories exist for BACKGROUND_DUMP_DEST, USER_DUMP_DEST, CORE_DUMP_DEST, UTL_FILE_DIR init.ora parameters

·           sqlplus "sys as sysdba"

·           startup nomount

·           create database rheadb  // ? where to specify default temporary tablespace

o         controlfile reuse

o         logfile

o         group 1 ('/oracle/redo1a.log', '/oracle/redo1b.log) size 5m

o         group 2 ('/oracle/redo2a.log', '/oracle/redo2b.log) size 5m

o         maxlogfiles 40

o         datafile '/oracle/sys01.dbf' size 50m autoextend on next 30m maxsize 150m

o         maxdatafiles 240

o         characterset WE8ISO8859P1;

o         exit ;

·           sys and system account are created

·           maxdatafiles: is max number of data files that can be open. DB_FILES init.ora parameter is real max data files

·           maxlogfiles: max number of redo log file groups

·           A single undo segment is create in the system tablespace

·           alter database mount rheadb

·           alter database open rheadb

·           create tablespace for undo segments, users, temp and other data

·           create data dictionary run <OH>/rdbms/admin catalog.sql (sys as sysdba) runs scripts listed below. Data Dictionary base tables starting with X$ are stored in the sys schema in the System tablespace. Tables accessible by sys only. Also creates user-friendly views for public. Cached in dictionary cache

§          catauditsql: create SYS.AUD$ table, which tracks all audit trail information

§          catldr.sql: create views for SQL*Loader

§          catexp.sql: create views used by EXPORT/IMPORT utilities

§          catpart.sql: create views used by Partitioning

§          catadt.sql: create views that support user defined types (ADT) and object components

§          standard.sql: create standard package which stores Oracle scalar datatypes e.g NUMBER, VARCHAR2, BLOB also built in SQL functions like decode

o         run catproc.sql to create database PL/SQL packages, runs .sql and .plb files (PL/SQL code) e.g dbms_output,

 

Data Dictionary base tables starting with X$ are stored in the sys schema in the System tablespace. They are created by catalog.sql. Accessible by sys only

 

Control files are read (CONTROL_FILES in init.ora) when database is opened, typically located in <OH>/oradata/<database_name>/control<nn>.ctl where n is between 01 and 03, Recommend that multiple Oracle Control files be put on multiple disks, they are updated when

·           when new tablespaces are created (new physical disk resource created)

·           when a datafile is added to a tablespace

·           when LGWR stops writing one online redo log file and starts writing another (log switch)

·           recommend that they be stored on different disks

·           alter database backup controlfile to '/bak/c1.ctl'

Note if OMF used then there is no need to specify CONTROL_FILES parameter

 

 

Control file contents (name and status in v$controlfile, v$database, v$controlfile_record_section)

·           Database name and identifier

·           Database creation date and time

·           Data files and redo log files location

·           Tablespace Names and associations between tablespace and data files

·           History of archive logs

·           Backup history

·           Current online redo log sequence number

·           Current checkpoint information

 

Re-creating control file (reasons could be to rename database, change settings like maxlogfile)

·           alter database backup controlfile to trace (creates in trace file for existing session in directory specified in USER_DUMP_DEST in init.ora)

·           script example

o         startup nomount

o         create controlfile reuse database "orclsid" noresetlogs noarchivelog

§          maxlogfiles 50

§          maxlogmembers 5

§          maxdatafiles 100

§          maxinstances 1

§          maxloghistory 226

o         logfiles

§          group 1 'd:\oh\oradata\orclsid\redo01.log size 100m,

§          group 2 'd:\oh\oradata\orclsid\redo02.log size 100m,

§          group 3 'd:\oh\oradata\orclsid\redo03.log size 100m,

o         datafile

§          d:\oh\oradata\orclsid\system01.dbf

·          shutdown normal|immediate

·           copy all data files and redo logs to alternate directory

·           update the script generate by step 1 to point to files in new location

·           update init.ora to point to new control file location

·           startup nomount

·           run create controlfile script

·           recover database -- only if shutdown abort

·          alter database mount

·          alter database open

·           startup open

 

Multiplex control files (create additional copies of control files)

·          sqlplus sys as sysdba

·          shutdown normal|immediate|transactional

·           cp /oh/c1.ctl /oh/c2.ctl

·           add new control file to init.ora control_files parameter

·          startup

 

Maintaining Redo Log Files

·           Size specified in create database statement. Finite in size

·           Data changes stored in redo log buffer in SGA as a redo log entry

·           Redo log files also called as Redo log group

·           Redo log files can be mirrored, they are called member of a group

·           LGWR writes to disk

·           Minimum 2 redo log files required

·           Commit causes flush of redo log buffer to disk

·           LGWR writes redo log entries to the active group and when it fills up it switches to the next (log switch) (alter system switch logfile manually cause log file switch)

o         Stop writing redo log that is full

o         generate a new sequence number for online redo log

o         perform a checkpoint

§          ckpt process updates header of data files and control files

§          signals DBWn to flush the dirty buffers to the disk

§          number of buffer written by DBWn determined by FAST_START_IO_TARGET (or FAST_START_MTTR_TARGET) init.ora parameter

§          If instance failure then dirty blocks not written to disk have to be recovered from redo logs

·           In NOARCHIVELOG mode LGWR writes into each log group then loops back to the first log group and overwrites what it contains. This means that database cannot recover to point of failure, Database runs faster good for read only database, development database

·           In ARCHIVELOG mode, the redo log group is backed up to a different directory. Database runs slower

·           To switch to archivelog mode

o         sqlplus sys as sysdba

o         shutdown normal|immediate

o         alter database archivelog|noarchivelog

o         shutdown

o         backup database

o         alter database open

 

Checkpoint Frequency

If redo logs are very large, checkpoints should be done more often by setting LOG_CHECKPOINT_INTERVAL (OS Blocks to write) or LOG_CHECKPOINT_TIMEOUT (timeout in seconds) in init.ora. Can do manually by running alter system checkpoint

 

Multiplexing and Maintaining Redo Log Files

·           Store each redo log member on different disk

·           Store multiple copies of redo log members

·           v$log.group# has group numbers (Also see v$logfile)

·          alter database add logfile group 3 '/oh/oracle/dbs/t_log3a.f' -- create new group

·          alter database add logfile member '/oh/oracle/dbs/t_log3b.f' to group 3

·           

·          alter database drop logfile group 3 -- does not remove files

·           Rename/Move redo log files

o         Backup Control files

o         sqlplus sys as sysdba

o         shutdown

o         copy redo log files to new location

o         startup nomount -- do not open database

o         alter database rename file

o         alter database open

 
Redo logs with OMF
·           Default size is 100MB
·          DB_CREATE_ONLINE_LOG_DEST_n specifies where to multiplex redo log
·          If DB_CREATE_ONLINE_LOG_DEST_n not specified redo logs create in DB_CREATE_FILE_DEST. Log member created in each directory
·          OMF generated redo log file name format is ora_%g_%u.log e.g ora_3_asdf1234.log

Tablespaces

·           Dictionary managed tablespace. Dictionary tables in System tablespace track free space. System tablespace is always dictionary managed

·           max 64000 tablespace per database

·           Locally managed tablespace (9i feature) Bitmaps in the header of datafile keep track of free space. Better performance

·           Temporary tablespace. Useful for sort or short lived data (create default temporary tablespace also in create database) Not good to store temporary segments in permanent tablespace

·           In create user default temporary tablespace is system

·           Useful types of tablespace

§          SYSTEM: Is required when creating a database

§          DATA: For table data

§          INDEX:

§          UNDOTBS: For Undo (rollback) segments

§          TEMP

§          TOOLS: Used for storing administrative objects

·          create tablespace data1

§          logging -- generate redo logs

§          datafile

§          '/oh/oracle/dbs/d01.dat' size 20m, -- max can be 64GB with 16K blocksize

§          '/oh/oracle/dbs/d02.dat' size 30m

§          autoextend on next 5m maxsize 100m -- maxsize optional

§          extent management local|dictionary -- local is freespace management in tablespace

§          segment space management auto -- objects automatically manage freespace

§          permanent  -- default can be temporary same as create temporary tablespace

§          online -- default

·           drop tablespace data1

·           drop tablespace data1 including contents cascade constraints and datafiles

·           alter tablespace data1 online

·           alter tablespace data1 permanent

·           alter tablespace data1 temporary – if it has permanent objects (e.g. table) will give error

·           alter tablespace data1 offline

·           alter tablespace data1 offline normal

·           alter tablespace data1 offline immediate – will require media recovery

·           alter tablespace data1 offline temporary

·           alter tablespace data1 read only –if active transactions statement waits. Must not be in online backup mode, cannot contain undo segements.  can drop tables and index, cannot create or alter table, index

·           alter tablespace data1 read write

·           create temporary tablespace temp -- temporary segments, see DBA_TEMP_FILES

§          tempfile '/oh/oracle/dbs/temp.dbf' size 100m

§          extent management local

·           alter tablespace data1 add datafile '/oh/oracle/dbs/n.dbf' size 10m

·           alter tablespace data1 add datafile '/oh/oracle/dbs/n.dbf' resize 20m

·           alter database datafile '/oh/oracle/dbs/n.dbf' offline drop

·           alter database datafile '/oh/oracle/dbs/n.dbf' autoextend on next 10m maxsize 100m

·           After a disk sort completes, SMON automatically drops temporary segments

·           Temporary segments in temporary tablespaces, allocates one sort segment for the first statement requiring a disk sort. All subsequent users share that segment. No limit to extents that can be acquired by the segment. After sort is over extents are eliminated. Sort segment space management is done in the SGA sort extent pool. Sort segment is released at instance shutdown.

·           create temporary tablespace temp tempfile '/oh/oracles/dbs/t.dbf' size 100m

§          extent management local

§          uniform size 10m --  all objects have same extent management

·           create table t1 (c1 varchar2(10)) storage (initial 10k next 10k minextents 10 maxextents 200 pctincrease 10 buffer_pool keep) // tables and index can have their own storage clause

·           If user specifies default storage clause in temporary tablespaces, data written to disk will equal SORT_AREA_SIZE, your extents must be at least that large. Initial sort segment = n x SORT_AREA_SIZE + DB_BLOCK_SIZE. Set initial equal to next so that all extents are the same size. Set pctincrease to zero. Do not use maxextents

·           Different temporary tablespaces can be created for different users based on different sort needs

·           See DBA_SEGMENTS, V$SORT_SEGMENT, V$SORT_USAGE

·           alter tablespace data1 default storage (initial 2m next 1m) – storage settings for create table, does not affect old objects

·           Relocating datafiles with alter database

§          sqlplus / as sysdba

§          shutdown

§          host mv '\oh\db\t1.data' '\oh\db\t2.data'

§          startup mount PFILE=init.ora

§          alter database rename file '\oh\db\t1.data' to '\oh\db\t2.data'

§          alter database open

§          backup database and control file

·           Relocating files with alter tablespace

§          alter tablespace data1 offline normal

§          host mv \oh\db\t1.df \oh\db\t2.df

§          alter tablespace data1 rename datafile '\oh\db\t1.df ' to '\oh\db\t2.df'

§          alter tablespace data1 online

§          backup database and control file

 

 

Undo Segments

 

LOB Segments

Cluster Segments: Two or more tables in a common segment around a common index

IOT Segments (Index organized table)