Introduction to DBA fundamentals (ORACLE ( 9i)
Oracle Instance : Means to access oracle database. When instance is started
1) SGA allocated
2) Background process are started
• Instance can open and use only one database at a time.
Oracle Server consists of
• Oracle Instance
• Oracle Database
User process -> Server process ->Session is created
Server Process communicates with Instance on behalf Of user process.
Establishing Connection & creating a session
• Single Tier ( same machine ->Interprocess Communication mechanism)
• Client Server (over Network)
• Three Tier (user computer=>Netwrok Server => Oracle Machine)
SESSION
•Session starts when user is validated and ends when user logs out or when there is abnormal termination.
•Many concurrent sessions are possible.
ORACLE DATATBASE Consists of
1) Physical Files
o Data Files
o Online Redo log files
o Control Files
2) Logical Files
o Tablespace
o Segments
o Extents
o Blocks
3) Other structures (key file structures)
o Parameter file
o Password File
o Archived Redo log
ORACLE MEMORY STRUCTURES
• SGA- System Global Area
• PGA- Program Global Area
SGA has
• Fixed Structures (remain of same size)
• Variable structures (grow and shrink according to requirement)
Allocated in virtual memory. It is dynamic. (Config. Can be changed without shutting down the Instance)
SGA consists of:
1.Shared_Pool_Size (libraray Cache & Data Dictionary Cache) if 64 BIT then 64MB else 16MB
2.Db_Cache_Size Database buffer Cache size 8MB
3.Log_Buffer Redo log Buffer
4.Large_Pool_Size Large Pool Size 0
5.Java_Pool_Size Java Pool Size 24MB
Shared Pool-> Library cache is used for Shared SQL,PL/SQL.
SGA can grow upto SGA_MAX_SIZE
SQL> SHOW SGA
SGA_MAX_SIZE= Fixed Size+Variable size+Database Buffer+ Redo Buffers
GRANULE SIZE based on total estimated SGA_MAX_SIZE
If estimated SGA size<128 MB then GRANULE size=4MB else 16MB
Minimum SGA config is 3 GRANULES.
• One GRANULE for fixed SGA (includes redo files)
• One GRANULE for Database Buffer Cache.
• One GRANULE for Shared Pool.
SGA grows and shrinks based on GRANULE boundaries.
COMMANDS
1) ALTER SYSTEM SET SHARED_POOL_SIZE=64M;
2) ALTER SYSTEM SET DB_CACHE_SIZE=96M;
BUFFER CACHE ADVICE :DB_CACHE_ADVICE
Info provided by these statistics help you size the database buffer cache.
1. ALTER SYSTEM SET DB_CACHE_ADVICE=READY (…….MEMORY ALLOCATED)
2. ALTER SYSTEM SET DB_CACHE_ADVICE=ON
VIEWS
V$DB_CACHE_ADVICE
LARGE_POOL
Large pool relieves the burden placed on the Shared Pool *check.
PGA is divided into STACK SPACE and UGA (user session memory, runtime memory). If large pool is configured then UGA shifts to LARGE POOL.
LARGE POOL IS USED FOR:
1. SESSION MEMORY (UGA) for the shared server)
2. I/O SERVER PROCESS
3. BACKUP & RECOVERY(RMAN)
4. PARALLEL EXECUTION MESSAGE BUFFERS
CONFIGURING LARGE POOL
1. ALTER SYSTEM SET LARGE_POOL_SIZE=24MB
Large pool is used if:-
ALTER SYSTEM SET PARRELLEL_AUTOMATIC_TUNIC=TRUE
Backup & Recovery Manager(RMAN) uses LargePool when
ALTER SYSTEM SET BACKUP_DISK_IO=n
ALTER SYSTEM SET BACKUP_TAPE_IO_SLAVE=TRUE
If large pool is configured but size is small then RMAN writes error message to alert log file.
JAVA POOL
It is required if installing & using Java.
ALTER SYSTEM SET JAVA_POOL_SIZE=58MB;
PGA
• Private SQL Area
Persistent Area =>Bind info
Runtime Area => Insert/update/Delete or Cursor area/ SQL work area/Sort area.
• Session Memory
• SQL work area
Contents of PGA varies for
• Shared Server
• Dedicated Server
PGA contains information about server process or single background process.
PGA is allocated when process is created and deallocated when the process is terminated.
PGA is an area that is used by only one process where as SGA is shared by many processes.
TYPES OF ORACLE PROCESS
• USER PROCESS
When user request for information , It generated calls through UPI( user programming interface), which creates a session and starts server process.
• SERVER PROCESS
When user establishes a connection, server process communicates with oracle using OPI (Oracle Program Interface). A server can be dedicated or shared.
• BACKGROUND PROCESS
When Oracle Instance is started, it maintains & enforces relationship between physical & memory structures. (DBWn, PMON< SMON, LGWr, CKPT)
Background Process
DBWn
Database buffer Cache ->DBWN(writes dirty buffers) -> Data files
Writes when
1) Checkpoint Occurs
2) Dirty Buffers reach threshold
3) There are no free buffers
4) Time out Occurs
5) Ping request in RAC (real Application Cluster)
6) Tablespaces is taken offline (Normal or Temp)
7) Drop or Truncate Table
8) Tablespace begin Backup
LGWR
Redo Log Buffer -> LGWr -> Online Redo Files
Writes when
1) 1/3rd log buffer is full
2) More than 1MB changes in Redo Log Buffer
3) Every three Seconds
4) Before DBWr writes
5) At Commit
CHKPT
Writes in the headers of control & Data files to identify that place in the online redo file where recovery is to begin which is called checkpoint.
1) CHKPT position 2) System change number (SCN) 3)Info abt logs.
Checkpoints are initiated to
a) to ensure that modified data blocks in memory are written to disk.
b) To reduce time required for instance recovery => only redo entries after checkpoint need to recover.
c) To ensure all committed data has been written to the data files during shut down.
Three types of checkpoints
1) Full Checkpoint
2) Partial Checkpoint
3) Incremental Checkpoint
SMON
Responsible for Instance recovery.
1) Instance Recovery
a) Rolls forward changes in online redo log files.
b) Opens database for users access.
c) Roll back uncommitted transactions.
2) Space Maintenance Functions
a) Coalesces free space in the data files.
b) Deallocates temporary segments to return them as free space in data files.
PMON
Responsible for server process recovery
1) Rollback the transaction
2) Release locks
3) Release other resources
4) Restarting dead dispactchers.
ARCn
• The ARCn process initiates backing up , or archiving of filled log group at every log switch.
• Enables recovery of databases to the point of failure even if disk drive is damaged.
ARCHIVELOG MODE
• Complete recovery in case of disk failure or loss
• Online redo log files are archived before they are overwritten.
NOARCHIVELOG MODE
• Committed data can be recovered i.e instance recovery is possible.
• Not archived. LGWr does not overwrites online redo log until checkpoint for that group occur.
Note:
• DB_BLOCK_SIZE initailisation parameter should be multiple of the OS block_size to avoid unnecessary I/O.
• One or more extents make segment.
Processing SQL Statements
Parse=> Bind=>execute=>fetch
Parse
1) search for identical statement
2) syntax
3) object name & privileges
4) create & store execution plan
5) lock objects
Bind
Obtain value for variable.
Execute
Process statement
Fetch
Return rows.
Lesson 2
Database Administration Tools
OUI Oracle Universal Installer
• Install
• Upgrade
• Remove Software Components
• Create Database
Oracle Database Configuration Assistant
• To create , delete or modify database.
SQL * PLUS => To access database
Database Administrator Users.
sys => change_on_install->own data dictionary.
system=> manager=> Own additional internal tables and views used by oracle tools.
OEM Oracle Enterprise Manager -> System Management Tool.
• To administer
• Monitor
• Tune one or more database
OEM -> JAVA BASED CONSOLE (Hierarchial tree and graphical representation)
Includes
• Tools and services
• Network Management Servers
• Intelligent agent
Services
• Job scheduling & management
• Event Management
• Database discovery & Management
• Service discovery & management
Includes integrated applications
Perform Advance Administration task
Diagnostic Pack
Tuning Pack
Change Mgmt Pack
Oracle Net Manager
Spatial Index Advisor
Text Manager
OEM Utilises THREE TIER ARCHITECTURE
It also uses TWO TIER ARCHITECTURE
FIRST TIER SECOND TIER THIRD TIER
Console Oracle Mgmt Server Intelligent Agent
ORACLE CLIENT & Database Repository ORACLE SERVER
