Consists of System Global Area, Background processes, Database files, Redolog files, control files & Parameter files
I. SYSTEM GLOBAL AREA consists of Shared Pool (Shared SQL Area), Database buffer Cache, Redolog buffer.
Shared Pool consists of Library cache & Data Dictionary cache, Parsed form of the SQL or PL/SQL statements, Execution Plan for SQL and PL/SQL statements, Data dictionary cache containing rows of datadictionary information.
Three Phases of processing SQL statements.
I. PARSE :Checks syntax, Queries the datadictionary for object resolution, security privileges and the most effective search path, Determines the parse tree or Execution Plan.
II. EXECUTE :Applies parse tree to data buffers, performs physical reads or logical read writes.
III. FETCH :Retrieves row of data for a select statement.
Database buffer Cache : The database buffer cache hold copies of data blocks read from disk, shared by all ORACLE user processes concurrently connected to the instance, the size is determined by the parameter DB_BLOCK_SIZE. The number of block cached in memory is determined by the parameter DB_BLOCK_BUFFERS.
REDOLOG Buffer :The redo log buffer is a circular buffer containing information about changes made to the database. Stores all changes made to the database in the redo entries of redo log buffer, used to reconstruct or rollback changes made to the database when recovery is necessary. The size of redo log buffer is determined by the parameter
LOG_BUFFER.
II. BACKGROUND PROCESSES :
1)DBWR :Modified data blocks are written back to disk by DBWR process.
2)User :A user process is created when a user runs an application program
3)LGWR :Oracle records all changes made to the database in the redolog buffer. LGWR process writes the information in the redo log buffer to disk when a commit occurs, the redo log buffer pool reaches one third full threshold, the DBWR needs to clean the buffer blocks for a checkpoint or a time out(3 sec) occurs.
4)ARCH :The Archiver process copies online redo log files to a designated storage device once they become full.
5)PMON :The Process Monitor cleans up abnormally terminated connections, Roll back uncommitted transactions, Releases locks held by terminated process. Free SGA resources allocated to the failed processes, Detects deadlocks and automatically resolves by rolling back the transaction.
6)SMON :Performs automatic instance recovery, Reclaims sort table space.
7)RECO :The Recoverer process resolves failures involving a distributed transaction.
8)LCkn :The Lock process performs inter instance locking in a parallel server system.
9)Dnnn :The Dispatcher process gets the request from user processes and puts in request que in SGA, & also gets the response from response que in SGA and passes back to user process.
10)LISTENER :The listener process identifies the path and protocol in MTS environment.
11)Server :A server process communicates with user process and SGA. & datafiles. Parses and executes SQL statements, Reads datablock from disk into the shared database buffers of the SGA, returns the results of SQL statements to the userprocesses.
12)CKPT :process updates headers of data and control files after check point has been completed, more frequent checkpoint will reduce the time necessary for recovering from instance failure, at the expense of performance, the check point process is enabled through the parameter CHECKPOINT_PROCESS.
13)SNPn Snapshot refresher
SGA :Every time ORACLE is started, the SGA is allocated and the ORACLE background processes are started. The combination of memory buffers and background processes is called an ORACLE instance
ORACLE database is composed of Control files, and the database and redo log files named in control files.
III Data files : Contain all the database data, logical structures such as tables and indexes, are physically stored in the datafiles.
Redolog files :Transaction logs record all changes made to the database and are used for data recovery, if the redo log files are mirrored, the same redolog information is written to multiple online redo log files. The redo log files are written to in a circular fashion, there must be at least two redo log groups.
Log Switches :A log switch occurs when ORACLE switches from one relog to another, when LGWR has filled one log file group, A log switch can be forced by a DBA when current redo log needs to be archived ( ALTER SYSTEM SWITCH LOGFILE), At a log switch the current redo log is assigned a log sequence number that identifies the information stored in that redo log and is also used for synchronization, A checkpoint automatically occurs at logswitch.
CHECKPOINT :During a check point DBWR writes all dirty buffer in the database buffer cache to disk, guaranteeing that all data blocks are modified since the Previous checkpoint are actually written to disk.
A Check point occurs at every log switch, a specified number of seconds after the last database checkpoint, when a predetermined number of redo log blocks have been written to disk since the last check point, at instance shutdown, when forced by DBA (ALTER SYSTEM CHECKPOINT) , when a table space is taken offline.
During a check point and after the log switch LGWR will update the headers of database and control files, unless check point process has been started, The parameter LOG_CHECKPOINT_TIMEOUT determines the interval of time after which another check point occurs, The parameter LOG_CHECKPOINT_INTERVAL determines the number of newly filled redo log file blocks needed to initiate a checkpoint.
CONTROL FILES :The control file is a small binary file that describes the structure of the database, All necessary database files and log files are identified by the control files, the name of the database is stored in the control file, the control file is required to open and access the database, synchronization information needed for recovery is stored inside the control file. It is advised to have minimum of two control files on different disks. The parameter CONTROL_FILES identifies the Control files. Parameter file points to control file, Control file points to redo log files and database files.
STARTUP STEPS :
1)The Init.ora is read, the SGA is created, the background processes are started and the instance is started.
2)The control file is read and the database is mounted.
3)Rolling forward occurs to recover the data that has not been recovered in the database files, yet has been recorded in the online redo log, including the contents of rollback segments.
4)Transaction that have been explicitly rolled back or have not been committed are rolled back, as indicated by rollback segments regenerated in prior step.
5)Any resources held by pending transactions at the time of failure are released.
6)Any pending distributed transactions are resolved undergoing a two phase commit at the time of instance failure.
7)Once the SMON processes has synchronzied the database and all the outstanding information in the redo log files have been applied to all the data files, the database is open for users to log into the database.
I. SYSTEM GLOBAL AREA consists of Shared Pool (Shared SQL Area), Database buffer Cache, Redolog buffer.
Shared Pool consists of Library cache & Data Dictionary cache, Parsed form of the SQL or PL/SQL statements, Execution Plan for SQL and PL/SQL statements, Data dictionary cache containing rows of datadictionary information.
Three Phases of processing SQL statements.
I. PARSE :Checks syntax, Queries the datadictionary for object resolution, security privileges and the most effective search path, Determines the parse tree or Execution Plan.
II. EXECUTE :Applies parse tree to data buffers, performs physical reads or logical read writes.
III. FETCH :Retrieves row of data for a select statement.
Database buffer Cache : The database buffer cache hold copies of data blocks read from disk, shared by all ORACLE user processes concurrently connected to the instance, the size is determined by the parameter DB_BLOCK_SIZE. The number of block cached in memory is determined by the parameter DB_BLOCK_BUFFERS.
REDOLOG Buffer :The redo log buffer is a circular buffer containing information about changes made to the database. Stores all changes made to the database in the redo entries of redo log buffer, used to reconstruct or rollback changes made to the database when recovery is necessary. The size of redo log buffer is determined by the parameter
LOG_BUFFER.
II. BACKGROUND PROCESSES :
1)DBWR :Modified data blocks are written back to disk by DBWR process.
2)User :A user process is created when a user runs an application program
3)LGWR :Oracle records all changes made to the database in the redolog buffer. LGWR process writes the information in the redo log buffer to disk when a commit occurs, the redo log buffer pool reaches one third full threshold, the DBWR needs to clean the buffer blocks for a checkpoint or a time out(3 sec) occurs.
4)ARCH :The Archiver process copies online redo log files to a designated storage device once they become full.
5)PMON :The Process Monitor cleans up abnormally terminated connections, Roll back uncommitted transactions, Releases locks held by terminated process. Free SGA resources allocated to the failed processes, Detects deadlocks and automatically resolves by rolling back the transaction.
6)SMON :Performs automatic instance recovery, Reclaims sort table space.
7)RECO :The Recoverer process resolves failures involving a distributed transaction.
8)LCkn :The Lock process performs inter instance locking in a parallel server system.
9)Dnnn :The Dispatcher process gets the request from user processes and puts in request que in SGA, & also gets the response from response que in SGA and passes back to user process.
10)LISTENER :The listener process identifies the path and protocol in MTS environment.
11)Server :A server process communicates with user process and SGA. & datafiles. Parses and executes SQL statements, Reads datablock from disk into the shared database buffers of the SGA, returns the results of SQL statements to the userprocesses.
12)CKPT :process updates headers of data and control files after check point has been completed, more frequent checkpoint will reduce the time necessary for recovering from instance failure, at the expense of performance, the check point process is enabled through the parameter CHECKPOINT_PROCESS.
13)SNPn Snapshot refresher
SGA :Every time ORACLE is started, the SGA is allocated and the ORACLE background processes are started. The combination of memory buffers and background processes is called an ORACLE instance
ORACLE database is composed of Control files, and the database and redo log files named in control files.
III Data files : Contain all the database data, logical structures such as tables and indexes, are physically stored in the datafiles.
Redolog files :Transaction logs record all changes made to the database and are used for data recovery, if the redo log files are mirrored, the same redolog information is written to multiple online redo log files. The redo log files are written to in a circular fashion, there must be at least two redo log groups.
Log Switches :A log switch occurs when ORACLE switches from one relog to another, when LGWR has filled one log file group, A log switch can be forced by a DBA when current redo log needs to be archived ( ALTER SYSTEM SWITCH LOGFILE), At a log switch the current redo log is assigned a log sequence number that identifies the information stored in that redo log and is also used for synchronization, A checkpoint automatically occurs at logswitch.
CHECKPOINT :During a check point DBWR writes all dirty buffer in the database buffer cache to disk, guaranteeing that all data blocks are modified since the Previous checkpoint are actually written to disk.
A Check point occurs at every log switch, a specified number of seconds after the last database checkpoint, when a predetermined number of redo log blocks have been written to disk since the last check point, at instance shutdown, when forced by DBA (ALTER SYSTEM CHECKPOINT) , when a table space is taken offline.
During a check point and after the log switch LGWR will update the headers of database and control files, unless check point process has been started, The parameter LOG_CHECKPOINT_TIMEOUT determines the interval of time after which another check point occurs, The parameter LOG_CHECKPOINT_INTERVAL determines the number of newly filled redo log file blocks needed to initiate a checkpoint.
CONTROL FILES :The control file is a small binary file that describes the structure of the database, All necessary database files and log files are identified by the control files, the name of the database is stored in the control file, the control file is required to open and access the database, synchronization information needed for recovery is stored inside the control file. It is advised to have minimum of two control files on different disks. The parameter CONTROL_FILES identifies the Control files. Parameter file points to control file, Control file points to redo log files and database files.
STARTUP STEPS :
1)The Init.ora is read, the SGA is created, the background processes are started and the instance is started.
2)The control file is read and the database is mounted.
3)Rolling forward occurs to recover the data that has not been recovered in the database files, yet has been recorded in the online redo log, including the contents of rollback segments.
4)Transaction that have been explicitly rolled back or have not been committed are rolled back, as indicated by rollback segments regenerated in prior step.
5)Any resources held by pending transactions at the time of failure are released.
6)Any pending distributed transactions are resolved undergoing a two phase commit at the time of instance failure.
7)Once the SMON processes has synchronzied the database and all the outstanding information in the redo log files have been applied to all the data files, the database is open for users to log into the database.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.