Day-1 : Architecture


What is SGA?  
The SGA is a read/write memory area that, along with the Oracle background processes, makes up a database instance. All server processes that execute on behalf of users can read information in the instance SGA. Several processes write to the SGA during database operation. Each database instance has its own SGA. Oracle Database automatically allocates memory for an SGA at instance startup and reclaims the memory at instance shutdown. 
--------------------------------------------------------------------------------------------------------------------------
What background process refreshes materialized views?
The Job Queue Processes (J000, Jnnn)
--------------------------------------------------------------------------------------------------------------------------
What are important components of SGA?
The most important SGA components are the following:
Database Buffer Cache
Redo Log Buffer
Shared Pool
Large Pool
Java Pool
Streams Pool
Fixed SGA
--------------------------------------------------------------------------------------------------------------------------
What is shared pool?
The shared pool caches various types of program data. For example, the shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool is involved in almost every operation that occurs in the database. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool.

The shared pool is divided into several subcomponents:
• Library Cache
• Data Dictionary Cache
• Server Result Cache
• Reserved Pool
--------------------------------------------------------------------------------------------------------------------------
What is PMON process? And state its functions?
Process Monitor Process (PMON)
The process monitor (PMON) monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally.
PMON is responsible for cleaning up the database buffer cache and freeing resources that the client process was using. For example, PMON resets the status of the active transaction table, releases locks that are no longer required, and removes the process ID from the list of active processes.
PMON also registers information about the instance and dispatcher processes with the Oracle Net listener (see "The Oracle Net Listener"). When an instance starts, PMON polls the listener to determine whether it is running. If the listener is running, then PMON passes it relevant parameters. If it is not running, then PMON periodically attempts to contact it.

--------------------------------------------------------------------------------------------------------------------------
What is SMON process? and state its functions?
System Monitor Process (SMON)
The system monitor process (SMON) is in charge of a variety of system-level cleanup duties. The duties assigned to SMON include:
• Performing instance recovery, if necessary, at instance startup. In an Oracle RAC database, the SMON process of one database instance can perform instance recovery for a failed instance.
• Recovering terminated transactions that were skipped during instance recovery because of file-read or tablespace offline errors. SMON recovers the transactions when the tablespace or file is brought back online.
• Cleaning up unused temporary segments. For example, Oracle Database allocates extents when creating an index. If the operation fails, then SMON cleans up the temporary space.
• Coalescing contiguous free extents within dictionary-managed tablespaces.
SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it.
--------------------------------------------------------------------------------------------------------------------------
What are mandatory background processes of oracle database in 11g?
The following are oracle instance mandatory background processes:
• Process Monitor Process (PMON)
• System Monitor Process (SMON)
• Database Writer Process (DBWn)
• Log Writer Process (LGWR)
• Checkpoint Process (CKPT)
• Manageability Monitor Processes (MMON and MMNL)
• Recoverer Process (RECO)
these process starts automatically when database  instance is started. the other database background processes are optional.
--------------------------------------------------------------------------------------------------------------------------
What is DBWn process? And state its functions?
DBWR - Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from the database block cache to the database data files. Generally, DBWR only writes blocks back to the data files on commit, or when the cache is full and space has to be made for more blocks. The possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.
--------------------------------------------------------------------------------------------------------------------------
What is LGWR process? And state its functions?
LGWR - Log Writer process is responsible for writing the log buffers out to the redo logs. In RAC, each RAC instance has its own LGWR process that maintains that instance’s thread of redo logs.
--------------------------------------------------------------------------------------------------------------------------
What is MMNL process? And state its functions?
The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full.
--------------------------------------------------------------------------------------------------------------------------
What is MMON process? And state its functions?
The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.
--------------------------------------------------------------------------------------------------------------------------
What is RECO process? And state its functions?
Recoverer Process (RECO):
In a distributed database, the recoverer process (RECO) automatically resolves failures in distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When RECO reestablishes a connection between the databases, it automatically resolves all in-doubt transactions, removing from each database's pending transaction table any rows that correspond to the resolved transactions.
--------------------------------------------------------------------------------------------------------------------------
What is CKPT process? And state its functions?
The checkpoint process (CKPT) updates the control file and data file headers with checkpoint information and signals DBWn to write blocks to disk. Checkpoint information includes the checkpoint position, SCN, location in online redo log to begin recovery, and so on.  CKPT does not write data blocks to data files or redo blocks to online redo log files.
-------------------------------------------------------------------------------------------------------------------------
What is SCN (System Change Number) ?
The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.
SCN is a value that is incremented whenever a dirty read occurs.
SCN is incremented whenever a deadlock occurs.
SCN is a value that keeps track of explicit locks.
SCN is a value that is incremented whenever database changes are made.
Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file.
--------------------------------------------------------------------------------------------------------------------------
What is a Checkpoint?
A Checkpoint is a database event which synchronizes the modified data blocks in memory with the datafiles on disk.
It offers Oracle the means for ensuring the consistency of data modified by transactions.
A checkpoint occurs on five types of events:
  - At each switch of the redo log files.
  - When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
  - When the size in bytes corresponding to :
     (LOG_CHECKPOINT_INTERVAL* size of IO OS blocks)
     is written on the current redo log file.
  - Directly by the ALTER SYSTEM SWITCH LOGFILE command.
  - Directly with the ALTER SYSTEM CHECKPOINT command.
The following are the parameter that will be used  to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.
During a checkpoint the following occurs: 
 -  The database writer (DBWR) writes all modified database blocks in the buffer cache back to datafiles,
 -  Checkpoint process (ckpt) updates the headers of all the datafiles to indicate when the last checkpoint occurred (SCN)
--------------------------------------------------------------------------------------------------------------------------
What are Various types of checkpoints  in Oracle?
Full checkpoint:Writes block images to  the database for all dirty buffers from all instances
Thread checkpoint: Writes block images to the database for all dirty buffers from one instance
File checkpoint: Writes block images to the database for all dirty buffers for all files of a tablespace from all instances(generally happens when When a tablespace is put into backup mode or take it offline)
Parallel Query checkpoint:Writes block images to the database for all dirty buffers belonging to objects accessed by the query from all instances(happens when we use parallel option in query)
Object checkpoint:Writes block images to the database for all dirty buffers belonging to an object from all instances(happens when table is truncated, dropped)
Log switch checkpoint:Writes the contents of the  dirty buffers whose information is protected by a redo log to the database
Incremental checkpoint:incremental checkpointing triggeres DBWR to write some dirty blocks from time to time so as to advance the checkpoint and reduce the instance recovery time.
--------------------------------------------------------------------------------------------------------------------------
What is LOG_CHECKPOINT_TIMEOUT?
log_checkpoint_timeout is the number of seconds since last checkpoint before another is performed.
its defalut value is 1800 seconds (30 minutes)
if log_checkpoints_to_alert is set to true then it writes checkpoints to alert log
use alter system set log_checkpoint_timeout=0 scope=both to eliminate time-based checkpoints
--------------------------------------------------------------------------------------------------------------------------
What is LOG_CHECKPOINT_INTERVAL?
LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks.
If it is set greater than redo log file size, checkpoints occur at log switches thus reduces the occurrence of checkpoint.it will be Ignored if set to zero.
--------------------------------------------------------------------------------------------------------------------------
What is large pool? and where it is used?
Oracle Large Pool is an optional memory component of the oracle database SGA. This area is used for providing large memory allocations in many situations that arise during the operations of an oracle database instance.
1. Session memory for the a shared server and the Oracle XA Interface when distributed transactions are involved
2. I/O Server Processes
3. Parallel Query Buffers
4. Oracle Backup and Restore Operations using RMAN
Large Pool plays an important role in Oracle Database Tuning since the allocation of the memory for the above components otherwise is done from the shared pool. Also due to the large memory requirements for I/O and Rman operations, the large pool is better able to satisfy the requirements instead of depending on the Shared Pool Area.
Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead casued by shrinking the SQL Cache Area
--------------------------------------------------------------------------------------------------------------------------
What are logfile states?
CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.
If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redolog group contains no redo records belonging to a dirty buffer it is in an “INACTIVE” state. These inactive redolog can be overwritten.
One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.
--------------------------------------------------------------------------------------------------------------------------
What is log switch?
A Log Switch is an event in which the log writer (LGWR process) stops logging in one log group and jumps to another log group. It is normal that a log group fills up with records. When records fill up the log group, Log Switch event is triggered. You can actually trigger the switch manually by issuing the command:
ALTER SYSTEM SWITCH LOGFILE;
--------------------------------------------------------------------------------------------------------------------------
What is the difference between PGA and UGA?
When you are running dedicated server then process information stored inside the process global area (PGA)
When you are using shared server then the process information stored inside user global area (UGA).
--------------------------------------------------------------------------------------------------------------------------
If any one of those mandatory background processes is killed/not running, the instance will be aborted ?
Yes, instance will be aborted if we kill any of PMON,SMON,CKPT,DBWn,LGWR
--------------------------------------------------------------------------------------------------------------------------
What are the components of physical database structure of Oracle database?
Control, redo log and data files.
--------------------------------------------------------------------------------------------------------------------------
What are the components of logical database structure of Oracle database?
Table, index, views and tablespace
--------------------------------------------------------------------------------------------------------------------------
What is an Oracle Instance?
An oracle instance is a combination of memory structures and background processes
--------------------------------------------------------------------------------------------------------------------------
When you start an Oracle DB which file is accessed first?
Parameter file (PFILE/SPFILE) in nomount state
--------------------------------------------------------------------------------------------------------------------------
Which Process reads data from Datafiles?
Server process
--------------------------------------------------------------------------------------------------------------------------
Which Process writes data in Datafiles?
DBWR
--------------------------------------------------------------------------------------------------------------------------
What is DB Buffer Cache Advisor?
DB Buffer Cache Advisor Advices about the optimum size of buffer cache. Related view V$DB_CACHE_ADVICE
--------------------------------------------------------------------------------------------------------------------------
Can you change SHARED_POOL_SIZE online?
Yes. Range of values Minimum: the granule size(84M for 64-bit, 32M for 32-bit) Maximum: operating system-dependent
--------------------------------------------------------------------------------------------------------------------------
How to change shared pool size?
ALTER SYSTEM SET SHARED_POOL_SIZE =500M;
--------------------------------------------------------------------------------------------------------------------------
Name some memory areas Oracle uses,and state their purpose?
Memory Areas in oracle SGA (System Global Area)+PGA(Program Global Area)
SGA=DBBC (Database Buffer Cache)+RLB(Redo log Buffer)+Shared pool Large Pool + Streams pool + JAVA pool +Fixed SGA

--------------------------------------------------------------------------------------------------------------------------
What is kept in the Database Buffer Cache?
Data blocks from data files
--------------------------------------------------------------------------------------------------------------------------
What is difference between Multithreaded/Shared Server and Dedicated Server?
In Shared Server architecture MANY sessions (user connections) have ONE server process associated. In Dedicated Server architecture ONLY ONE sessions (user connections) have ONLY ONE server process associated. Because Shared Server architecture use a multi-thread architecture (one server process has more threads), a Shared Server is named MTS (Multi-Thread Server) as well.
--------------------------------------------------------------------------------------------------------------------------
How would you force a log switch manually?
ALTER SYSTEM SWITCH LOGFILE;
--------------------------------------------------------------------------------------------------------------------------
When a user process fails, what background process cleans up after it?
PMON
--------------------------------------------------------------------------------------------------------------------------
How to find out background processes ?
select SID,PROGRAM from v$session where TYPE='BACKGROUND';
--------------------------------------------------------------------------------------------------------------------------
Name some background processes description?

SQL> select name,description from V$bgprocess;
--------------------------------------------------------------------------------------------------------------------------


















No comments:

ORA-600 [kwqitnmphe:ltbagi], [1], [0] reported in the alert log file.

ORA-00600 [kwqitnmphe:ltbagi] Cause: This issue arises in 12.1.0.2. The error occurs because there are still Historical Messages without...