Before going deeply into the the database architecture however, need to be clear about a fundamental concept:
Difference between an Oracle instance and an Oracle database.
Instance: A set of Oracle background processes/threads and a shared memory area, which is memory that is shared across those threads/processes running on a single computer.A database instance can exist without any disk storage whatsoever. An instance may mount and open a single database at any point in time.An instance can be divided in 3 part:
- System Global Area (SGA)
- Program Global Area (PGA)
- Background processes
Database: A collection of physical operating system files or disk.
A database is just a collection of files (data files, temporary files, redo log files, and control files).
we may have many instances simultaneously mounting and opening this one database,
To recap,
* An instance is a set of background processes and shared memory.
* A database is a collection of data stored on disk.
* An instance can mount and open only a single database, ever.
* A database may be mounted and opened by one or more instances (using RAC).
Three major piece of database :
1. Storage
2.Memory
3.Process
Oracle Database Structures:
Mainly two:
- Logical structures:(which primarily include tablespaces, segments, extents, and blocks)
- Physical structures:(Datafiles,ontrol files,redolog files,sp files ,pfiles )
A set of related logical objects in a database is called a schema.Oracle database objects, such as tables, indexes, and packaged SQL code, are actually logical entities.Dividing a database’s objects among various schemas promotes ease of management and a higher level of security.
Logical Structures:
Data blocks:The smallest logical component of an Oracle database is the data block. Data blocks are defined in terms of bytes. Oracle data block can bein units of 2KB, 4KB, 8KB, 16KB, or
32KB (or even larger chunks).It is at the foundation of the database storage.A data block consists of a number of bytes of disk space. All Oracle’s space allocation and usage is in terms of Oracle data blocks.
How Big Should the Oracle Block Size Be?
Parameter to define block size is DB_BLOCK_SIZE.it can range from 2KB to 32KB.
Think of the block size as the minimum unit for conducting Oracle’s business of updating, selecting, or inserting data. When a user selects data from a table, the select operation will “read,” or fetch, data from the database files in units of Oracle blocks.
Oracle block size of 8KB, means data block will have exactly 8,192 bytes.
The operating system also has a disk block size, and the operating system reads and writes information in whole blocks. Ideally, the Oracle block size should be a multiple of the disk block size,if not, you may be wasting time reading and writing whole disk blocks while only making use of part of the data on each I/O.
Oracle guidelines for choosing the database block size:
• Choose a smaller block size if your rows are small and access is predominantly random.
• Choose a larger block size if the rows are small and access is mostly sequential (or random and sequential), or if you have large rows.
All data blocks can be divided into two main parts:
- row data portion :contains the data stored in the tables or their indexes.
- free space portion:it is the space left in the Oracle block for new data to be inserted or for existing rows in the block to be extended.
- There are also other smaller areas, such as overhead and header space for maintenance purposes.
Let’s look at what’s actually in an Oracle data block. This can be achieved by “dumping” the block contents.
Query to Identify File and Block IDs:
SELECT segment_name,
file_id,
block_id
FROM dba_extents
WHERE owner = 'MA'
AND segment_name LIKE 'CAME%';
SEGMENT_NAME FILE_ID BLOCK_ID
------------------------- ---------- -------------------------------
CAME 397 32811
SQL>
or
SELECT header_file,header_block FROM dba_segments
WHERE segment_name = 'MAN';
HEADER_FILE HEADER_BLOCK
----------- ------------
397 32811
Next issue the following command, using the appropriate file and block numbers, to get a
dump of the block you need:
SQL> ALTER SYSTEM DUMP DATAFILE 397 BLOCK 32811;
System altered.
SQL>
SQL> SELECT name
2 FROM sys.obj$
3* WHERE obj#='4916681';
NAME
---------------
MAN
Extents:An extent is two or more contiguous Oracle data blocks, and this is the unit of space allocation.
Segments:A segment is a set of extents that you allocate to a logical structure like a table or
an index (or some other object).
Tablespaces: An oracle tablespace is a logical entity that contains the physical data files. A tablespace is a set of one or more data files, and usually consists of related segments. The data files contain the data of all the logical structures that are part of a tablespace, like tables and indexes.
The following five tablespaces are generally the default tablespaces that all databases
must have:
• System tablespace>cannot rename or drop the SYSTEM tablespace or take it offline.Contain information data dictionary and the system rollback segment.
• Sysaux tablespace>acts as an auxiliary tablespace to the SYSTEM tablespace,annot rename or drop it.
• Undo tablespace
UNDO are used for various purpose such as
Rollback a particular transaction
Recover database from crash
Provide read consistency
For Flashback feature in Oracle DB
• Temporary tablespace:
• Default permanent tablespace
Why Tablespace?
Tablespaces perform a number of key functions in an Oracle database, but the concept of a tablespace is not common to all relational databases. For instance, the Microsoft SQL Server database doesn’t use this concept at all.
benefits of using tablespaces:
• Tablespaces make it easier to allocate space quotas to various users in the database.
• Tablespaces enable you to perform partial backups and recoveries based on the tablespace as a unit.
• Because a large object like a data warehouse partitioned table can be spread over several tablespaces, you can increase performance by spanning the tablespace over several disks and controllers.
• You can take a tablespace offline without having to bring down the entire database.
• Tablespaces are an easy way to allocate database space.
• You can import or export specific application data by using the import and export utilities at the tablespace level.
Physical Database Structures:
The Oracle database consists of the following three main types of files:
• Data files: These files store the table and index data.
• Control files: These files record changes to all database structures.
• Redo log files: These online files contain the changes made to table data.
include initialization files (like init.ora and the SPFILE), network administration files (like tnsnames.ora and listener.ora), alert log files, trace files, and the password file.
Oracle Processes:
A process is essentially a connection or thread to the operating system that performs a task or job.
divided into two general:
1.User processes:responsible for running the application that connects the user to the database instance.
2.Oracle processes:perform the Oracle server’s tasks,further divide into two major categories: server processes and background processes.These together perform all the actual work of the database, from managing connections to writing to logs and data files to monitoring the user processes.
Background processes:
background process started when an Oracle Instance is started.
Background Processes Maintains and enforces relationships between physical and memory structures
There are two types of database processes:
1. Mandatory background processes
2. Optional background processes
Mandatory background processes:
– DBWn, PMON, CKPT, LGWR, SMON
Optional background processes:
– ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn
DBWn writes when:
• Checkpoint occurs
• Dirty buffers reach threshold
• There are no free buffers
• Timeout occurs
• RAC ping request is made
• Tablespace OFFLINE
• Tablespace READ ONLY
• Table DROP or TRUNCATE
• Tablespace BEGIN BACKUP
Log Writer (LGWR) writes:
• At commit
• When 1/3rd full
• When there is 1 MB of redo
• Every 3 seconds
• Before DBWn writes
System Monitor (SMON) Responsibilities:
• Instance recovery
– Rolls forward changes in redo logs
– Opens database for user access
– Rolls back uncommitted transactions
• Coalesces free space
• Deallocates temporary segments.
Process Monitor (PMON) Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarting dead dispatchers
Checkpoint (CKPT) Responsible for:
• Signaling DBWn at checkpoints
• Updating datafile headers with checkpoint information
• Updating control files with checkpoint information
Archiver (ARCn)
• Optional background process
• Automatically archives online redo logs when ARCHIVELOG mode is set
• Preserves the record of all changes made to the database
Server Process:
The server process is the process that services an individual user process.Each user connected
to the database has a separate server process created for the duration of the session.The server
process is created to service the user’s process and is used by the user process to communicate with
the Oracle database server.When the user submits a request to select data, for example, the server
process created for that user’s application checks the syntax of the code and executes the SQL code.
It then reads the data from the data files into the memory blocks. (If another user intends to read
the same data, the second user’s server process will read it not from disk again, but from Oracle’s
memory, where the data usually remains for a while.) Finally, the server process returns the
requested data to the user.
Oracle Memory Structures:
Oracle uses two kinds of memory structures:
system global area (SGA):part of total memory that all server processes (including background processes) share.
Program global area(PGA):process-specific part of the memory.
The SGA contains five main areas.
The fixed area.
The variable area.
The database buffer cache.
The log buffer.
The resource directory for a RAC system.
The fixed area of the SGA contains several thousand atomic variables. These are small data structures, such as latches and pointers, which refer to other areas of the SGA. The size of the fixed area is static. It also contains general information about the state of the database and the instance which the background processes need to access.
The fixed SGA is a component of the SGA that varies in size from platform to platform and release to release.Think of this area as a bootstrap section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.
This is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.The fixed SGA is an internal housekeeping area.
The variable part of the SGA is made up of a large pool and a shared pool. All memory in the large pool is dynamically allocated, whereas the shared pool contains both dynamically managed memory and a permanent memory.
The database buffer cache is where database block copies are held for processing. All user processes concurrently connected to the instance share access to the database buffer cache. There are many groups of buffers within the SGA.
SQL> SHOW SGA
SQL> SELECT * FROM v$sga;
SQL> select * from v$sgainfo;
SQL> select NAME,BYTES/(1024*1024) as "Size In MB",RESIZEABLE from v$sgainfo;
SQL> Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB From V$sgastat Where Name Like '%free memory%';
SQL> select * from v$sga_dynamic_components;
System global area (SGA):
The SGA’s purpose is to speed up query performance and to enable a high amount of concurrent
database activity.Because processing in memory is much faster than disk I/O.When you start an instance in Oracle, the instance takes a certain amount of memory from the operating system’s RAM—the amount is based on the size of the SGA component in the initialization file.When the instance is shut down, the memory used by the SGA goes back to the host system.
The SGA isn’t a homogeneous entity; rather, it’s a combination of several memory structures.
Database buffer cache: Holds copies of data blocks read from data files.The buffer cache thus contains both the original blocks read from disk and the changed blocks that have to be written back to disk.
dirty buffers, which contain data which was read from disk and then modified but has not yet been written to disk.
Free buffers :do not contain any useful data and are available for reuse to hold new data read from disk.
Pinned buffers: are currently being accessed.
The first time an Oracle user process requires a particular piece of data, it searches for the data
in the database buffer cache. If the process finds the data already in the cache (a cache hit),
it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data.Accessing data through a cache hit is faster than data access through a cache miss.
Shared pool: Contains the library cache for storing SQL and PL/SQL parsed code in order to
share it among users. It also contains the data dictionary cache, which holds key data dictionary
information.
Consists of two key performance-related memory structures Library Cache and Data Dictionary Cache.
1.Library Cache:
Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.
The Library Cache holds all users’ SQL statements, Functions, Procedures, and Packages. It stores parsed SQL statement with its execution plan for reuse.
2.Data Dictionary Cache :
Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.
The Dictionary Cache, sometimes also referred to as the Row Cache, holds the Oracle repository data information such as tables, indexes, and columns definitions, usernames, passwords, synonyms, views, procedures, functions, packages, and privileges information.
Redo log buffer: Contains the information necessary to reconstruct changes made to the
database by DML operations. This information is then recorded in the redo logs by the log
writer.Records all changes made to the database data blocks, Primary purpose is recovery.
Redo entries contain information to reconstruct or redo changes.
Java pool: Keeps the state of Java program execution.
Large pool: Stores large memory allocations, such as RMAN backup buffers.
Streams pool: Supports the Oracle Streams feature.
NORESETLOGS
The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.
NORESETLOGS when doing complete recovery (when the entire redo stream was applied). Oracle will continue using the existing (valid) log files.
What happens when you run ALTER DATABASE OPEN RESETLOGS ?
online redo logs are given a new time stamp and SCN.
new database incarnation is created,
the log sequence number is reset to 1
The current online redo logs are archived
The reason to do the open the database with the resetlogs is that after doing an incomplete recovery , the data files and control files still don't come to the same point of the redo log files. And as long as the database is not consistent within all the three file-data, redo and control, you can't open the database. The resetlogs clause would reset the log sequence numbers within the log files and would start them from 0 thus enabling you to open the database but on the cost of losing all what was there in the redo log files.
Use RESETLOGS after incomplete recovery (when the entire redo stream wasn't applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database.
Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!
Before using the RESETLOGS option take an offline backup of the database.
The RESETLOGS option clears all the online redo logs and modifies all the online data files to indicate no recovery is needed. After resetting the redo logs none of the existing log files or data file backups can be used. In the control file, the log sequence number is modified, which is very important for recovery purposes. The recovery will be applied only to the log files whose sequence number is greater than log sequence number in the control file. One has to be very cautious when using RESETLOGS option. It is important to remember that all datafiles must be online otherwise they will become useless once the database is up
In what scenarios open resetlogs required ?
An ALTER DATABASE OPEN RESETLOGS statement is required,
1.after incomplete recovery (Point in Time Recovery) or
2.recovery with a backup control file.
3. recovery with a control file recreated with the reset logs option.
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.
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 Database Incarnation ?
Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”.
Database incarnation falls into following category Current, Parent, Ancestor and Sibling
i) Current Incarnation : The database incarnation in which the database is currently generating redo.
ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.
iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.
iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.
How to view Database Incarnation history of Database ?
Using SQL> select * from v$database_incarnation;
Using RMAN>LIST INCARNATION;
The Checkpoint Process
The checkpoint process is responsible for updating file headers in the database datafiles. A checkpoint occurs when Oracle moves new or updated blocks (called dirty blocks) from the RAM buffer cache to the database datafiles. A checkpoint keeps the database buffer cache and the database datafiles synchronized. This synchronization is part of the mechanism that Oracle uses to ensure that your database can always be recovered.
Bigfile Tablespaces in Oracle database
A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size. This was introduced in Oracle 10g.
Benefits of Bigfile tablespaces:
- you can storage more data in the DB
- less files to manage
- reduce the SGA space required for datafile information
- reduce the size of the control file (store info about data files)
