ORACLE DATABASE ARCHITECTURE
Here's an easier way to understand the Oracle Database Architecture:
=============================================1. Oracle Instance :
==============
An Oracle instance is like the "brain" of the database 🧠. It includes the memory and background processes that make the database work. The instance is what connects users to the actual data stored on disks 💾.
2. Memory Structures:
================
SGA and PGA
System Global Area (SGA) 🧠: This is shared memory used by everyone connected to the database. It contains important sections like:
Database Buffer Cache 📚: Where data is temporarily stored after being read from disk.
Redo Log Buffer 🔄: Temporarily holds changes to the database for recovery purposes.
Shared Pool 💡: Caches frequently used SQL queries and database info.
Library Cache 📜: Stores parsed SQL statements (so the database doesn’t have to re-parse the same query).
Data Dictionary Cache 🗂️: Stores info about database objects (like tables, users).
Large Pool: Used for things like backups and large queries.
Java Pool: Memory for running Java code in the database.
Streams Pool: Helps with data replication and messaging.
Program Global Area (PGA) 🛠️: This memory is specific to each user session and isn't shared. It contains:
Private SQL Area 🔐: Stores user-specific data like variables in SQL queries.
Sort Area 📊: Handles sorting that can't be done in the shared memory.
3. Background Processes :
=================
These processes keep the database running smoothly:
DBWn (Database Writer) 🖊️: Writes changes from memory to the disk.
LGWR (Log Writer) 📝: Writes change records (redo logs) to disk to help with recovery.
CKPT (Checkpoint Process) ✅: Makes sure data is saved and consistent.
SMON (System Monitor) 🚨: Fixes the database after a crash.
PMON (Process Monitor) 🔄: Cleans up failed processes and releases resources.
4. Physical Storage Structures :
====================
Oracle stores data in physical files on the disk:
Redo Log Files 🛡️: Record every change to the database for recovery.
Control Files ⚙️: Track the structure of the database and its components.
5. Logical Storage Structures :
====================
This is how Oracle organizes data logically:
Tablespaces 📦: Logical containers that hold data like tables or indexes.
Segments: Larger units inside tablespaces, made of extents.
Extents and Data Blocks: Extents are made up of Oracle blocks, the smallest unit of storage.
6. Oracle Net :
===========
Oracle Net 🌐 helps users and applications communicate with the Oracle database over a network using protocols like SQL*Net.
7. User and Server Processes :
===================
User Process 🙋♂️: This is what you interact with—it sends SQL queries to the Oracle instance.
Server Process 🖥️: The behind-the-scenes worker that runs your SQL queries and manages memory and processes in the instance.
-----------------------------------------------------------------------------------------------
==========================================
unzip -qo /tmp/LINUX.X64_193000_db_home.zip
#for silent installation of ORACLE DATABASE 19c
==============================================
export CV_ASSUME_DISTID=OEL7.6
./runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile /u01/app/oracle/product/19.0.0/dbhome_1/install/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=${devdb} \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en,en_GB \
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin: devdb
dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname devdb -sid devdb -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword SysPassword1 \
-systemPassword SysPassword1 \
-createAsContainerDatabase true \
-numberOfPDBs 1 \
-pdbName pdb \
-pdbAdminPassword PdbPassword1 \
-databaseType MULTIPURPOSE \
-memoryMgmtType auto_sga \
-totalMemory 2000 \
-storageType FS \
-datafileDestination "/u02/oradata" \
-redoLogFileSize 50 \
-emConfiguration NONE \
-ignorePreReqs
Manually db creation steps :
----------------------------
Creating an Oracle database manually involves several steps, including setting up environment variables, initializing the instance, and executing SQL scripts. Below is a step-by-step guide.
Step 1: Set Environment Variables
Before creating a database, set the required environment variables.
On Linux:
export ORACLE_SID=mydb
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
On Windows (Command Prompt):
set ORACLE_SID=mydb
set ORACLE_HOME=C:\oracle\product\19.3.0\dbhome_1
Step 2: Create Required Directories
Ensure the directories for datafiles, control files, and logs exist:
mkdir -p /u01/oradata/mydb
mkdir -p /u01/redo/mydb
mkdir -p /u01/fra/mydb
chown -R oracle:dba /u01/oradata /u01/redo /u01/fra
chmod -R 775 /u01/oradata /u01/redo /u01/fra
Step 3: Create an Initialization File (init.ora)
Create a file named initmydb.ora in $ORACLE_HOME/dbs (C:\oracle\product\19.3.0\dbhome_1\database on Windows):
DB_NAME='mydb'
CONTROL_FILES='/u01/oradata/mydb/control01.ctl','/u01/oradata/mydb/control02.ctl'
DB_BLOCK_SIZE=8192
UNDO_TABLESPACE=undotbs1
DB_CREATE_FILE_DEST='/u01/oradata/mydb'
DB_CREATE_ONLINE_LOG_DEST_1='/u01/redo/mydb'
DB_RECOVERY_FILE_DEST='/u01/fra/mydb'
DB_RECOVERY_FILE_DEST_SIZE=10G
COMPATIBLE='19.0.0'
Step 4: Start the Instance in NOMOUNT Mode
Connect to SQL*Plus as SYSDBA:
sqlplus / as sysdba
Start the instance:
STARTUP NOMOUNT PFILE='/u01/app/oracle/product/19.3.0/dbhome_1/dbs/initmydb.ora';
Step 5: Create the Database
Run the following SQL command:
CREATE DATABASE mydb
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/redo/mydb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/redo/mydb/redo02.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
DATAFILE '/u01/oradata/mydb/system01.dbf' SIZE 700M AUTOEXTEND ON
SYSAUX DATAFILE '/u01/oradata/mydb/sysaux01.dbf' SIZE 500M AUTOEXTEND ON
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/mydb/temp01.dbf' SIZE 50M AUTOEXTEND ON
UNDO TABLESPACE undotbs1
DATAFILE '/u01/oradata/mydb/undotbs01.dbf' SIZE 300M AUTOEXTEND ON;
Step 6: Run Necessary Scripts
After the database is created, run the essential scripts:
@?/rdbms/admin/catalog.sql --> catalog.sql Creates data dictionary views Yes
@?/rdbms/admin/catproc.sql ---> catproc.sql Installs PL/SQL system packages Yes
@?/rdbms/admin/utlrp.sql --> utlrp.sql Recompiles invalid objects oracle Recommended
Step 7: Create and Configure Users
CREATE USER myuser IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO myuser;
------------------------------
what does root. Sh file while installation :
--------------------------------------------
The root.sh script is a crucial script run during the installation of Oracle Grid Infrastructure (including Oracle RAC) and the Oracle Database. This script performs various necessary post-installation tasks that involve configuring system-level settings, permissions, and initializing the Oracle environment to ensure proper functioning.
The script must be executed as the root user, and it is typically run at specific points during the installation process, either during the installation of Oracle Grid Infrastructure or after database creation.
3. Run Installer:
• Switch to the Oracle user and start the installer:
su - oracle
cd /u01/app/oracle/product/19c/dbhome_1
./runInstaller
4. Follow GUI Steps:
• Choose installation type (e.g., "Set up Software Only" or "Create and Configure a Database").
• Specify Oracle base and home directories.
• Resolve any prerequisites flagged by the installer.
• Run root scripts as prompted:
sudo /u01/app/oracle/product/19c/dbhome_1/root.sh
5. Verify Installation:
• Check the Oracle Inventory log and database status:
$ORACLE_HOME/bin/sqlplus / as sysdba
SQL> SELECT * FROM v$version;
The Database Configuration Assistant (DBCA) is a tool provided by Oracle to create, configure, and manage databases. Here's a step-by-step guide for using DBCA during Oracle database installation:
-----------------------------------
1. Launch DBCA
Open a terminal or GUI.
Run the command:
dbca
2. Select Operation
Choose Create a Database from the list of options.
Other options include Configure Database Options, Delete a Database, and Manage Templates.
3. Database Deployment Type
Select the type of database you want to create:
Oracle Single Instance Database (default).
Oracle RAC Database (if setting up RAC).
4. Creation Mode
Typical Configuration: Simplified setup with default parameters.
Advanced Configuration: Allows more granular control over settings like storage, memory, character sets, etc.
5. Database Type
Choose the database type:
General Purpose / Transaction Processing.
Data Warehouse.
Custom Database (for a manual setup).
6. Specify Database Name
Enter the Global Database Name (e.g., orcl.example.com) and SID (e.g., orcl).
7. Storage Configuration
Select the storage type:
File System: Standard OS storage.
Oracle ASM: Use Automatic Storage Management.
Database Files Location: Specify file locations or use Oracle-managed storage.
8. Management Options
Configure database management options:
Enable Enterprise Manager (EM) (if applicable).
Specify EM Cloud Control or Database Control settings.
9. User Credentials
Set passwords for administrative users:
SYS.
SYSTEM.
Optionally, configure the DBSNMP and SYSMAN accounts for monitoring.
10. Network Configuration
Configure listener settings (if necessary).
Choose an existing listener or create a new one.
11. Initialization Parameters
Memory Settings: Choose automatic memory management (AMM) or manual setup.
Character Set: Select the database character set and national character set.
Connection Mode: Dedicated or shared server mode.
12. Database Storage
Review and configure the storage layout:
Datafiles.
Control files.
Redo log files.
13. Recovery Options
Enable or disable recovery options:
Configure Fast Recovery Area (FRA).
Specify FRA size and location.
Enable ARCHIVELOG mode if required.
14. Scripts Execution
Review the summary and generate scripts for manual execution, if desired.
DBCA can also execute scripts automatically during database creation.
15. Create Database
Click Finish to start the database creation process.
Monitor the progress in the wizard.
16. Post-Creation Tasks
After the database is created:
Verify the database status using:
sqlplus / as sysdba
SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
===========================
Oracle Processes Overview :
===========================
Query Data flow :
-------------------
At 1, the User issues a SELECT statement which is carried over to the database (only after connection established using listeners) by the user process.
At 2, as there will be a server process created for this user in DEDICATED server architecture, SQL is passed into this PGA and in turn, creates a CURSOR in it. So, every SQL statement will have the cursor in the memory until query execution is completed.
At 3, after the cursor is created, the syntax check will let the query move to SGA.
At 4,5,6,7, now the system will verify if the query is already cached. If yes, picks up the execution plan and if no, parsing happens and a new execution plan is created by the optimizer.
At 8,9, now data is pulled into buffer cache with the help of a large pool to perform I/O operations smoothly.
At 10,11, in the case of the ORDER BY clause, sorting takes place in the server process that is PGA and data is sent back to the user process. Now that query executed successfully, the cursor area is released on the database server.
Query Flow Summary :
===================
Parsing → Validate SQL syntax & objects. - Shared pool( Library cache + dictionary cache)
Transformation → Rewrite query for optimization (if needed).
Optimization → Choose the best execution plan. - Shared pool
Execution → Retrieve & process data. - Shared pool
Fetching Data → Return results to the user. - Datafiles
Commit/Rollback (For DML Queries) → Finalize changes.
Components of oracle db:
------------------------
Oracle Database is composed of several core components, which can be categorized into
physical structures, memory structures, and bg processes.
What is the difference between Crash recovery and Instance recovery? :
### **Example Scenarios:**
- **Crash Recovery Example**: If the database server crashes due to a power outage, upon restart, crash recovery will apply the redo logs and undo uncommitted transactions to restore the database.
- **Instance Recovery Example**: In an Oracle RAC setup, if one instance crashes, Oracle will perform instance recovery to ensure that the affected instance is brought back online with no data loss, while other instances continue functioning.
Crash Recovery :
----------------
Crash recovery refers to the process that Oracle performs when the database crashes unexpectedly, typically due to hardware or software failures, power outages, or other unexpected events. The database was running when it failed, and crash recovery ensures that the database is brought back to a consistent state upon restart.
Steps Involved:
----------------
Redo Log Replay: Oracle applies the redo log files (containing all committed transactions that have not yet been written to disk) to bring the database up to the point of failure.
Undo Log Processing: Oracle also uses undo logs to roll back any uncommitted transactions at the time of the crash. This ensures that the database is consistent by undoing the effects of transactions that were in progress when the failure occurred.
Key Points:
Automated: This recovery happens automatically during the database restart, and users are usually unaware of it.
No User Intervention: The process is transparent to users.
Duration: The recovery duration depends on the volume of changes that were pending to be written to disk.
Cause: Happens after an unexpected shutdown, like a crash or power failure.
Steps Oracle Uses to Identify the Need for Crash Recovery
===========================================
Checking the SCN in the Datafiles and Control File:
Each datafile and the control file store a System Change Number (SCN), which represents the last checkpoint or commit recorded in that file.
When the database starts up, Oracle compares the SCN in the control file with the SCN in each datafile.
If the SCNs are mismatched (control file SCN > datafile SCN), it indicates that some transactions have not been applied to the datafiles, signaling the need for crash recovery.
Redo Log Files Inspection:
Oracle scans the redo log files to check for uncommitted changes or incomplete transactions.
If Oracle detects that redo logs contain changes that were not written to the datafiles (also known as dirty buffers), it determines that crash recovery is necessary to synchronize the database state.
Database Shutdown Type:
Clean Shutdown (e.g., SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL): Oracle ensures that all dirty buffers are written to disk, checkpoints are updated, and redo logs are cleared. Crash recovery is not needed.
Abnormal Shutdown (e.g., SHUTDOWN ABORT, server crash, or power failure): Changes in the buffer cache may not have been written to the datafiles, requiring crash recovery during the next startup.
Alert Log and Trace Files:
Oracle writes a message to the alert log file indicating whether crash recovery is required. For example:
Instance recovery required
The redo logs are analyzed to recover uncommitted transactions and replay changes
Instance Recovery :
-----------------
Instance recovery occurs when an Oracle instance (a running database process) fails due to issues like an Oracle process crash, system failure, or database instance termination. It is essentially a sub-type of crash recovery that specifically happens when an individual instance fails, especially in Real Application Clusters (RAC) environments.
Steps Involved :
Redo Log Replay: Similar to crash recovery, the redo logs are applied to ensure all committed transactions are recovered.
Undo Log Processing: Uncommitted transactions are rolled back using undo logs.
However, the key difference in instance recovery is that it's concerned with a single instance in a clustered environment (or on a single system), and it might involve recovering just one node's data, rather than the entire system.
Key Points:
Automatic: The recovery happens automatically when the instance is restarted.
RAC and Single Instance: While instance recovery can occur in a RAC setup or in a single-instance Oracle database, it primarily comes into play in environments like Oracle RAC when one of the database nodes (instances) fails.
No Data Loss: As with crash recovery, the goal is to ensure that the database is consistent and there is no data loss
### **Key Differences Between Crash Recovery and Instance Recovery**
| **Feature** | **Crash Recovery** | **Instance Recovery** |
|---------------------------|----------------------------------------------|----------------------------------------------|
| **Cause of Failure** | Unexpected shutdown due to hardware failure, power loss, or OS crash. | Failure of an individual Oracle instance, like a crash or instance shutdown. |
| **Scope** | Involves recovery of the entire database. | Involves recovery of a specific instance (typically in a RAC environment or on a single-instance system).
| **Transaction Rollback** | Rolls back uncommitted transactions and applies redo logs for committed transactions. | Similar: Rolls back uncommitted transactions and applies redo logs for committed transactions. |
| **Data Consistency** | Ensures data consistency after a crash by using redo and undo logs. | Ensures data consistency for the specific instance after its failure. |
| **Usage** | Primarily in situations where the entire database has crashed or become inconsistent. | Used to recover a failed Oracle instance (relevant in both RAC and single-instance setups). |
---
### **How Oracle Handles These Recovery Processes:**
- Oracle handles **both crash and instance recovery automatically** when the database is restarted.
- It checks for incomplete transactions and applies necessary redo log files and undoes any in-progress transactions to restore the database to a consistent state.
Oracle Database operates with two main types of processes:
----------------------------------------------------------
User Processes: Initiated by users or applications to interact with the database.
Background Processes: Automatically managed by Oracle to ensure smooth database operations.
1. User Processes :
===================
User processes are initiated by the end user or an application to communicate with the Oracle database.
SQL Queries: Users connect to the database to run SQL commands.
Communication with Server Processes: These processes interact with Oracle’s server processes to execute SQL, retrieve data, and perform transactions.
Example: When you run a query in an application, it starts a user process that interacts with the database to retrieve the results.
Server Process in Oracle :
=========================
A server process in Oracle Database is responsible for handling client requests and performing the necessary operations to interact with the database. It acts as the intermediary between the user or application and the Oracle instance, processing SQL queries, managing transactions, and returning results.
Types of Server Processes
Oracle supports two types of server processes depending on the connection model:
Dedicated Server Process:
A dedicated server process is created for each client session.
Suitable for applications that require persistent connections.
Each user session gets a separate process, ensuring isolation and reliability.
Shared Server Process:
In a shared server architecture, multiple client sessions share a pool of server processes.
Reduces resource consumption by allowing fewer server processes to serve a larger number of client sessions.
Requires the Dispatcher Process (Dnnn) to route client requests to shared server processes.
Responsibilities of a Server Process :
-------------------------------------
Query Parsing:
Parses SQL statements and checks for syntax and semantic correctness.
Determines execution plans (in conjunction with the Oracle Optimizer).
Executing SQL Statements:
Executes the parsed SQL statements against the database.
Handles DML, DDL, and SELECT operations.
Data Retrieval:
Reads data from data blocks in the buffer cache (or directly from disk if not cached).
Applies necessary filters and sorting to provide query results.
Transaction Management:
Manages transactions, including commits, rollbacks, and savepoints.
Interacts with undo segments for rollback operations.
Interaction with Database Buffer Cache:
Writes data to the database buffer cache (if available) or fetches it directly from disk.
Lock Management:
Handles row-level and table-level locking to maintain data consistency.
Error Handling:
Detects and reports errors during query processing.
How a Server Process is Spawned
-------------------------------
User Connection:
When a client or application connects to the database, the connection is accepted by the Listener Process.
Listener Action:
The listener spawns a dedicated server process or assigns a session to a shared server process, depending on the configuration.
Connection Established:
The server process interacts with the Oracle instance on behalf of the client.
2. Background Processes :
=========================
Background processes are essential for the internal functioning of the database, performing tasks such as writing data to disk, recovering the system, and maintaining database consistency. Some of the key background processes include:
DBWn (Database Writer) 1-36 default - 1
======================
Purpose: Writes modified data blocks (dirty blocks) from memory (Buffer Cache) to disk.
Role: Frees up space in memory for new operations.
Summary of Buffer Cache Block Types :
Type Description
Dirty Modified blocks that have not yet been written to disk.
Clean Blocks that are unmodified or already written to disk.
Used Recently accessed blocks, still in use but may be evicted.
Free Unused blocks, available for new data.
Pinned Blocks that are explicitly prevented from being evicted.
Writes when :
- After lgwr written
- Every 3sec checkpoint
- when Buffer threshold limit reaches
- when Tablespace goes offline or read only mode
- when database cleanly shutdown
LGWR (Log Writer)
=================
Purpose: Writes redo log entries from the Redo Log Buffer to the online redo log files.
Role: Ensures that all changes are recorded for recovery in case of failure.
Summary of Redo Log Types
Type Description
Active Redo Log Redo log groups required for recovery (not yet checkpointed). stores the most recent changes.Multiple redo log groups can be ACTIVE
Current Redo Log The log currently being written to by LGWR.Only one redo log group can be CURRENT.
Inactive Redo Log No longer in use but still not archived; waiting to be archived.
Archived Redo Log Redo logs that have been archived after being filled.
Redo Log Buffer A memory area that temporarily holds redo entries before writing them to disk.
Redo Log Groups A set of redo log files that Oracle writes to in a circular fashion
Key Differences active and current redo :
------------------------------------------
Aspect Current Redo Log Active Redo Log
Definition The redo log group actively being written by LGWR. Redo log groups required for recovery (not yet checkpointed).
State CURRENT ACTIVE
Purpose Captures and records all ongoing changes in real-time. Ensures database recoverability during instance failure.
Number Only one redo log group can be CURRENT. Multiple redo log groups can be ACTIVE.
Checkpoint Dependency Not dependent on checkpoints. Becomes INACTIVE only after checkpointing.
Use in Recovery May or may not be needed for recovery (depends on checkpointing). Always needed for instance or media recovery.
Writes when :
- Before dbwr
- Every 3sec checkpoint
- 1mb of redo generates
- When 1/3 of redo fills
- every commits
SMON (System Monitor)
=====================
Purpose: Handles instance recovery after a crash.
Role: Cleans up temporary segments and manages free space in the database.
SYSTEM MONITORING:- SMON performs recovery at instance startup and cleans unused recovery segments. It checks regularly to see whether the process is needed.
Performs recovery with instance startup.
Clean up unused temporary segments.
PMON (Process Monitor)
======================
Purpose: Cleans up after failed processes, rolling back uncommitted transactions.
Role: Frees resources held by failed processes and ensures system stability.
PROCESS MONITORING:- PMON takes care all bg process. When the user disconnects some issue then PMON clears all resources.
Performs process recovery when a user process fails.
Clean up the database buffer cache.
Monitor sessions for idle session timeout.
Dynamically registers database services with the listener.
CKPT (Checkpoint Process)
=========================
Purpose: Updates control files and datafile headers with the latest checkpoint information.
Role: Reduces recovery time by periodically writing consistent data to disk.
CHECKPOINT:- Checkpoint mainly controls the control file information and data-file header. When a checkpoint occurs, the Oracle database updates the header of all data files to record the details of the checkpoint. The CKPT process does not write blocks to disk.
check point types :
In Oracle, a checkpoint is a database event that synchronizes the database's memory (SGA) with the physical datafiles, ensuring data consistency and recovery. Checkpoints occur at various times and for different reasons, and there are several types of checkpoints in Oracle.
Types of Checkpoints
1. Full Checkpoint (Global Checkpoint) :
Involves writing all dirty buffers (modified data in memory) in the database buffer cache to the datafiles. It ensures the consistency of the entire database.
When It Happens:
Database shutdown (clean shutdown).
Altering the database state to MOUNT or READ ONLY.
Explicitly triggered using the command:
ALTER SYSTEM CHECKPOINT;
Purpose:
Guarantees that all modified data is written to disk.
Ensures a consistent state across all datafiles.
2. Incremental Checkpoint
Writes dirty buffers to datafiles incrementally rather than all at once.
When It Happens:
Regularly triggered by the database background process (DBWn) based on time and workload.
Purpose:
Reduces the time required for instance recovery by ensuring more up-to-date data on disk.
Helps in maintaining a manageable checkpoint queue.
3. Log Switch Checkpoint
Occurs when a redo log switch happens, marking the current redo log group as inactive and ensuring all changes associated with the redo entries in that group are written to disk.
When It Happens:
A redo log group becomes full, and a log switch occurs.
Manually triggered using:
ALTER SYSTEM SWITCH LOGFILE;
Purpose:
Makes the redo log group reusable by ensuring that all changes in the group are persisted to datafiles.
4. Tablespace Checkpoint
Description: Flushes all dirty buffers related to a specific tablespace to its corresponding datafiles.
When It Happens:
A tablespace is put into READ ONLY or OFFLINE mode.
Manually triggered using:
ALTER TABLESPACE tablespace_name CHECKPOINT;
Purpose:
Ensures that the tablespace is consistent and up to date on disk.
5. Object Checkpoint
Writes all dirty buffers related to a specific object (e.g., table, index) to disk.
When It Happens:
During operations like DROP TABLE, TRUNCATE, or ALTER TABLE.
Purpose:
Ensures data integrity for the specific object being modified.
6. Thread Checkpoint
Applies to Real Application Clusters (RAC). Ensures consistency for a particular thread of redo log files.
When It Happens:
During log switches or manual intervention.
Purpose:
Ensures data consistency for a single RAC instance in a clustered environment.
7. File Checkpoint :
Description: Writes dirty buffers of a specific datafile to disk.
When It Happens:
When a datafile is taken offline.
During backup operations.
Purpose:
Ensures that the specific file is synchronized with the database.
ARCn (Archiver)
===============
Purpose: Copies redo log files to archive storage after a log switch.
Role: Assists with database recovery and backups by keeping historical logs.
Importance for a DBA
Understanding and managing these processes helps a DBA to:
Diagnose performance issues: Such as slow disk writes (DBWn) or redo log waits (LGWR).
Optimize system performance: By adjusting parameters related to these processes.
Ensure system reliability: By monitoring processes like SMON, PMON, and ARCn to handle recovery or cleanup tasks efficiently.
This knowledge is crucial for maintaining a healthy and efficient database system.
--------------------
Redo log buffer :
------------------
The Redo Log Buffer is a memory area in the System Global Area (SGA) of an Oracle database that temporarily stores
redo entries before they are written to the redo log files on disk. These redo entries contain a record of changes
made to the database, which are crucial for data recovery and maintaining data integrity.
Why Use the Redo Log Buffer?
Performance Optimization:
Buffering Changes: The redo log buffer allows Oracle to batch multiple changes together before writing
them to the redo log files, reducing the number of I/O operations required. This buffering helps improve
the performance of transaction processing.
Minimized I/O Overhead: Writing to memory (the redo log buffer) is significantly faster than writing to disk
(redo log files). By first storing changes in the redo log buffer, Oracle can reduce the immediate I/O overhead
and improve overall system throughput.
They store all changes made to the database, including changes from both committed and uncommitted transactions. This comprehensive logging is essential for ensuring data integrity and enabling recovery in case of a failure.
Data Integrity and Recovery:
Durability: Before a transaction is considered committed, the associated redo entries must be successfully written
to the redo log buffer and then to the redo log files. This ensures that in case of a system failure, all committed
transactions can be recovered by replaying the redo entries from the redo log files.
Crash Recovery: In the event of a crash, Oracle uses the redo log buffer entries (written to redo log files)
to reconstruct the state of the database up to the point of failure. This ability to recover ensures the ACID
properties (Atomicity, Consistency, Isolation, Durability) of transactions.
Efficient Transaction Management:
Checkpointing: The checkpoint process writes all dirty buffers from the database buffer cache to the data files,
ensuring that all modified data blocks are written to disk. The redo log buffer plays a critical role during
checkpointing by ensuring that all redo entries are written to the redo log files, making sure the database is in a consistent state.
How the Redo Log Buffer Works
Generating Redo Entries:
When a DML operation (INSERT, UPDATE, DELETE) or DDL operation (CREATE, ALTER, DROP) is performed,
Oracle generates redo entries that describe the changes made.
These redo entries are first stored in the redo log buffer.
Writing to Redo Log Files:
The log writer process (LGWR) is responsible for writing the redo entries from the redo log buffer to the redo log files on disk.
LGWR writes the redo entries to the redo log files under the following conditions:
When a transaction commits.
When the redo log buffer is one-third full.
When there is a commit or rollback operation.
Before the database writer process (DBWR) writes dirty buffers to disk.
Every 3 seconds (default interval).
Flushing Redo Entries:
LGWR writes synchronously to the redo log files to ensure data durability. This means that the writing process waits
until the data is securely written to disk before considering the write operation complete.
Once the redo entries are written to the redo log files, the redo log buffer space is freed up for new redo entries.
Important Parameters
LOG_BUFFER:
------------
This initialization parameter determines the size of the redo log buffer.
Tuning the LOG_BUFFER parameter can help improve performance in systems with high transaction rates. However, setting it too large can lead to unnecessary memory usage
Background Process in Oracle Database Architecture:
--------------------------------------------------
Client processes: These processes are created and maintained to run the software code of an application program or an Oracle tool. Most environments have separate computers for client processes.
Background Processes: Background processes asynchronously perform I/O and monitor other Oracle Database processes to provide increased parallelism for better performance and reliability.
Server Processes: These processes communicate with client processes and interact with Oracle Database to fulfill requests. The processes that perform activities on behalf of clients-that is the processes that actually executes the user’s SQL statements-are referred to as server processes.
When a client program wants to execute a SQL, it relies on a server process and returns the results.
Dedicated servers- A dedicated server performs tasks on behalf of one client. This sort of server is started when the clients connect and are destroyed when the client disconnects. Dedicated servers are usually the default.
Shared servers– Shared servers perform tasks on behalf of multiple clients.
Parallel Query servers– Oracle can use multiple parallel processes to execute a single SQL statement.
Job and Scheduler servers– The job and scheduler servers execute commands in the background and schedule as required.
What is Oracle Physical Structure?
----------------------------------
An oracle physical structure consists of the following files: –
Data Files: These files store the database information that a firm or organization needs in order to operate.
Control File: These files store database creation date and time, SCN information, backup and recovery information, and others.
CONTROL_FILE_RECORD_KEEP_TIME Parameter :
Purpose: Specifies the minimum number of days that Oracle keeps records of backups and archived redo logs in the control file before overwriting them.
Default Value: 7 days.
Valid Range: 0 to 365 days.
ALTER SYSTEM SET CONTROL_FILE_RECORD_KEEP_TIME = 30 SCOPE=BOTH;
Redo log files: These files are used to recover the database in the event of any failures.
Archived redo log files: These files are used to recover the database if there is some failure in the disk.
Parameter files: These files used to specify parameters for configuring an Oracle instance when it starts up.
Password files: Authenticates only to the special users of the database.
Alert and trace log files: These files store information about faults and steps taken, that affect the configuration of the database.
/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/alert_orcl1.log
Logical Structure:
------------------
An oracle logical structure consists of the following files: –
Table Space: A tablespace is a logical container for storing objects such as tables, indexes, sequences, clusters, and database objects. Each tablespace has at least one physical data file that actually stores the tablespace at the operating system level. Large table space may have more than one data file allocated for storing objects assigned to that tablespace.
Segment: When a logical storage object is created within a tablespace, for example, an employee table, a segment is allocated to the object.
Extent: Every object has one segment which is a physical collection of extents. Extents are collections of contiguous disk storage blocks. A logical storage object like a table or index every time consists of at least one extent. Preferably, the initial extent allocated to an object will be large enough to store all data that is initially loaded.
Block: The oracle server manages data at the smallest unit which is called a block or data block. Data are actually stored in the blocks.
Database Memory Structure Background Process :
----------------------------------------------
The memory structure includes two areas of memory:
System Global Area (SGA): SGA allocates when an oracle instance starts up.
Program Global Area (PGA): PGA allocates when a server process starts up.
System Global Area (SGA):
System Global Area (SGA) is a memory area that stores information shared by database processes and by users of the database. It is a read and writes memory area. It is allocated when an oracle instance (database) is started up based on values specified in the initialization parameter file (either PFILE and SPFILE).
Components of SGA:
The SGA has the following memory structures:
1. Database Buffer Cache :
---------------------------
The database buffer cache is the largest memory area in the SGA. This memory area stores the copies of the data blocks that are read from the database discs.
As this area is in the SGA, this data is available for all the users.
So when the first user runs a query, the related data is read from the disc and stored into the buffer cache.
When any other user, or maybe the same user wants to reach the same data, before checking the disc the server checks the buffer cache first.
If all the data we need is already in the buffer cache, it can read the data from the buffer cache directly.
If some of the data is not there but some of it is already in the buffer cache, it goes to the discs only for the absent blocks.
If there is no related data with our query in the buffer cache, the server reads all the data from the disc into the buffer cache.
2. Redo Log Buffer :
--------------------
The redo log buffer is the part of the System Global Area (SGA) that holds information about changes made to the database. Each of these changes generates a 'redo entry'. Redo entries are needed to reconstruct these changes during the recovery process.
What is a redo log ?
When we make any change on the table data by an insert, update delete, create, alter, drop operations.
It is very important to capture the database consistency to maintain the changes.
3. Java Pool :
---------------
The Java Pool is a component of the System Global Area (SGA) in Oracle Database, specifically allocated for Java-based operations. It provides memory for parsing and executing Java code within the Oracle JVM (Java Virtual Machine). The Java Pool is required for running Java applications, Java stored procedures, Java triggers, and other Java objects in the database.
4. Streams Pool :
---------------
It is used to allocate memory for Oracle Streams and Oracle GoldenGate Integrated Capture and Integrated Apply operations. These are advanced features for data replication, message queuing, and data integration.
5. Shared Pool – includes two components
Library Cache :
-----------------
It is responsible for storing and managing the execution plans, parsed SQL and PL/SQL statements, and other executable objects, ensuring efficient reuse and reducing the overhead of repeatedly parsing and compiling these objects.
What is Shared SQL Area?
-------------------------
The Shared SQL Area is a memory structure in the Library Cache, a part of the Shared Pool in the System Global Area (SGA) of an Oracle database. It stores parsed SQL statements and their associated execution plans, making them available for reuse by multiple sessions.
How Does the Shared SQL Area Work?
Query Parsing:
When a SQL statement is submitted, Oracle checks the Shared SQL Area for an identical statement.
SQL Match:
If a match is found (same SQL text, structure, and bind variables), the existing execution plan is reused (soft parse).
If no match is found, Oracle creates a new entry in the Shared SQL Area (hard parse).
Execution:
Sessions execute the statement using the shared execution plan while maintaining their own session-specific data in the Private SQL Area.
The Private SQL Area :
---------------------
It is a memory structure within the Program Global Area (PGA) that holds session-specific information about the execution of a SQL statement. Each session executing a SQL statement has its own Private SQL Area, even if the SQL statement resides in the Shared SQL Area of the Library Cache.
What is the Server Result Cache in Oracle?
------------------------------------------
The Server Result Cache is a component of the System Global Area (SGA) in Oracle databases that stores the results of SQL queries and PL/SQL function calls. The cache enables these results to be reused, reducing the need for re-executing the queries or function calls.
What is the Reserved Pool in Oracle?
------------------------------------
The Reserved Pool is a subcomponent of the Shared Pool in the System Global Area (SGA). It is specifically designed to handle memory allocation for large objects or operations that cannot find sufficient contiguous memory in the Shared Pool.
Key Contents:
Large execution plans or PL/SQL code objects.
Benefits:
Prevents fragmentation by providing space for large objects.
Ensures smooth execution of large queries.
Parameter for Configuration:
SHARED_POOL_RESERVED_SIZE: Sets the size of the reserved area.
User Global Area (UGA) Components (When Using Shared Server) :
Purpose: Stores session-specific data when using shared server connections.
Key Contents:
Session variables.
Temporary data.
Benefits:
Centralizes session data for shared server processes.
Data Dictionary Cache :
------------------------
The Data Dictionary Cache, also known as the Row Cache, is a part of the Shared Pool in Oracle's System Global Area (SGA). It stores metadata about database objects, helping the database engine quickly retrieve frequently accessed information without repeatedly querying the data dictionary tables stored on disk.
Additional memory structures in the SGA include:
1. Large Pool
----------------
The large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool.
The large pool can provide large memory allocations for the following:
UGA for the shared server and the Oracle XA interface (used where transactions interact with multiple databases)
Message buffers used in parallel execution
Buffers for Recovery Manager (RMAN) I/O slaves
Buffers for deferred inserts (inserts with the MEMOPTIMIZE_WRITE hint)
Program Global Area:
--------------------
PGA is a non-shared memory region that contains data and control information exclusively for use by an oracle process. It is created by the Oracle database when an oracle process is started, only one PGA exists for each Server Process and each Background Process. It stores data and control information for a single background process. This is not a shared part of memory, one PGA to each process only. The total instance PGA is the collection of individual PGAs
When we are talking about the individual PGA there under the PGA different components are there one is session memory one is a persistent area a runtime area that is called a private SQL area that is the small area size, hash area, bitmap merge area that all called as a SQL work area. When the multiple PGAs are allocated that is called one instance PGA.
The content of the PGA includes the following :
Private SQL Area: Stores information for a parsed SQL statement – stores behind variable values runtime memory allocations.
Session Memory: Memory that holds session variables and other session information.
SQL Work Area: Memory area allocated for sort, hash-join, bitmap merge, and bitmap create types of operations.
Dedicated Server Environment – It is the private SQL area is located in the Program Global Area.
Shared Server Environment – It is the private SQL area is located in the System Global Area.
-------------------------------------
Schema Management in Oracle Database :
------------------------------------
Schema management involves organizing, maintaining, and securing database objects within a user-defined namespace (schema). In Oracle, a schema corresponds to a database user and includes objects like tables, indexes, views, procedures, and more.
Key Components of Schema Management
1. Schema Creation
A schema is implicitly created when you create a user.
CREATE USER my_schema IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;
Schema Object Management
Creating Objects
Objects like tables, views, and indexes are created under a schema.
CREATE TABLE my_schema.employees (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
hire_date DATE
);
2)Modifying Objects
You can modify schema objects as needed:
- ALTER TABLE my_schema.employees ADD (salary NUMBER);
Dropping Objects
Remove objects when no longer needed:
- DROP TABLE my_schema.employees CASCADE CONSTRAINTS;
3)Schema Permissions
Granting Privileges
Privileges control what users can do with schema objects.
- GRANT SELECT, INSERT ON my_schema.employees TO user1;
Revoking Privileges
Remove access when no longer required.
- REVOKE INSERT ON my_schema.employees FROM user1;
4)Granting Roles
Roles simplify privilege management for multiple users.
- GRANT dba TO admin_user;
5)Schema Backup and Recovery
Backup Schema
Use Data Pump to export schema objects.
expdp system/password schemas=my_schema directory=DATA_PUMP_DIR dumpfile=my_schema.dmp logfile=my_schema.log
Restore Schema
Import the schema using Data Pump.
impdp system/password schemas=my_schema directory=DATA_PUMP_DIR dumpfile=my_schema.dmp logfile=my_schema_import.log
6)Locking/Unlocking Schema
Lock a user to prevent unauthorized access.
ALTER USER my_schema ACCOUNT LOCK;
Unlock when needed:
ALTER USER my_schema ACCOUNT UNLOCK;
Auditing Schema Activities
Enable auditing for critical schema objects:
AUDIT SELECT ON my_schema.employees;
--------------------------
Here's the difference between **schema** and **user** in table format for easy copying:
| **Aspect** | **Schema** | **User** |
|------------------------|---------------------------------------------------------------------------|----------------------------------------------------------------------|
| **Definition** | A logical collection of database objects (tables, views, procedures, etc.) owned by a user. | A database account used for authentication and access to the db
| **Purpose** | Organizes and stores database objects. | Manages access and permissions to the database and its objects. |
| **Creation** | Automatically created when a user is created. | Explicitly created using the `CREATE USER` command. |
| **Physical/Logical | Represents the logical structure of database objects. | Represents the physical account for authentication and authorization.|
| **Access** | Objects in a schema are accessed using the schema name (e.g., `hr.employees`). | Users access their own schema directly or other schemas with privileges. |
| **Security** | Controls access to its objects by granting privileges to other users. | Requires privileges to access objects in its own or other schemas. |
| **Dependency** | Cannot exist without an associated user. | Exists independently and automatically owns a schema. |
| **Dropping** | Dropping a user removes its schema and all associated objects. | Dropped explicitly using the `DROP USER` command. |
=================================
TRUNCATE :
----------
Features:
Removes all rows from a table, leaving the structure intact.
DDL (Data Definition Language) command.
Does not log individual row deletions, making it faster.
Cannot be rolled back (in most databases unless wrapped in a transaction in some systems).
Resets any auto-increment counters (like in MySQL).
Does not trigger ON DELETE triggers.
ex - TRUNCATE TABLE table_name;
2. DELETE
Features:
Removes specific rows based on a WHERE clause or all rows if WHERE is omitted.
DML (Data Manipulation Language) command.
Logs individual row deletions, making it slower than TRUNCATE.
Can be rolled back (if within a transaction).
Does trigger ON DELETE triggers.
Does not reset auto-increment counters.
DELETE FROM table_name WHERE condition;
-- Or to delete all rows
DELETE FROM table_name;
Difference Between DROP and TRUNCATE
Feature DROP TRUNCATE
Type DDL Command DDL Command
Purpose Deletes the table structure and data Deletes only the table's data
Recoverability Irreversible Irreversible
Performance Slower (drops dependencies) Faster
Usage To remove the entire object To empty the table quickly
------------------------------
a. Tablespaces
• Definition: A logical storage unit in Oracle that groups related data.
• Purpose: Manages data organization and storage allocation for database objects like tables,
indexes, etc.
• Types of Tablespaces:
1. System Tablespace:
▪ Contains essential data dictionary tables and metadata.
▪ Created automatically during database creation.
2. SYSAUX Tablespace:
▪ Stores auxiliary data like metadata for features such as AWR and ASH.
3. Temporary Tablespace:
▪ Used for sorting operations and temporary data during SQL execution.
4. Undo Tablespace:
▪ Stores undo records for transaction rollback and read consistency.
5. User Tablespace:
▪ Holds application and user-created objects.
• Key Benefits:
o Easier management of storage for different applications or users.
o Control over disk space usage.
******************Adding datafile into tablespace****************
column "Tablespace" format a30
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
set lines 999 pages 999
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free",
100 - round(100 * (fs.freespace / df.totalspace)) "Pct. Utilized"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name
order by 6;
----------- to see latest file added in tablespace in last 5 days
set lines 999 pages 999
col file_name for a100
select vd.file#,vd.creation_time,d.file_name,d.autoextensible,
d.bytes/1024/1024/1024 "size_GB",d.maxbytes/1024/1024/1024 "maxsize_GB",
increment_by*(select block_size from dba_tablespaces where tablespace_name='&tbs')/1024/1024 "Next_extent"
from v$datafile vd join dba_data_files d
on vd.file#=d.file_id
where d.tablespace_name='&tbs'
and vd.creation_time > sysdate-5;
--------------------------
Before proceeding with file addition , first confirm the ASM DISKGROUP NAME for the tablespace.
Select * from
(Select file_id,file_name,bytes/1024/1024/1024,autoextensible from dba_data_files where tablespace_name='&tbs_name' order by file_id desc)
where rownum<6;
--------------------------------------
Adding datafiles.
alter tablespace SYSTEM add datafile '+DATAC1' size 1g autoextend on next 100m;
Resize datafile -
alter database datafile 9 resize 18g;
FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
9 +DATAC1/CX2OGCSS_G67_FRA/043803694DBD569EE063FA947D0A767A/DATAFILE/system.2171.1146313017
Checking Bigfile from tablespace
desc dba_tablespaces;
select tablespace_name , BIGFILE from dba_tablespaces;
Checking user privilages in DB
SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;
===================
Find Datafiles Associated with Tablespaces
To find datafiles associated with tablespaces
select tablespace_name, file_name, bytes/1024/1024
from dba_data_files where tablespace_name='&tablespace_name';
/u02/oradata/PROD/users01.dbf
To find temp files associated with a temp tablespace
SQL> select file_name, bytes/1024/1024 from dba_temp_files;
Create Tablespace
To create new tablespace inside database
SQL> Create tablespace test_tbs datafile '/u02/oradata/PROD/test_tbs01.dbf' size 50m;
Tablespace created.
Add Space to Tablespace
There are two ways to add space to a tablespace:
Resize existing datafile
Add new datafile
Make sure you have space at OS level before resizing or adding new datafile
Use below command to resize a datafile
SQL> Alter database datafile '/u02/oradata/PROD/test_tbs01.dbf' resize 60m;
Database altered.
Use below command to add new datafile to tablespace
SQL> Alter tablespace test_tbs add datafile '/u02/oradata/PROD/test_tbs02.dbf' size 10m;
Tablespace altered.
Drop Tablespace
Below command will drop tablespace including all its contents and associated datafiles
SQL> drop tablespace test_tbs including contents and datafiles;
Tablespace dropped.
Tablespace Coalesce
Tablespace Coalesce combines all contiguous free extents into larger contiguous extents inside all datafiles
It takes any free extents that are right next to some other free extent and make one bigger free extent
SMON will perform this coalescing in the background but if you need it to happen right now, coalesce will do it
SQL> ALTER TABLESPACE USERS COALESCE;
Steps to Drop a Datafile in a Permanent Tablespace :
-------------------------------------------------
Oracle does not provide a direct DROP DATAFILE command. Instead, you need to remove the datafile logically by performing the following steps:
1. Check Tablespace and Datafile Information
Before dropping a datafile, identify the tablespace and ensure the datafile can be removed.
SELECT file_name, tablespace_name, status
FROM dba_data_files
WHERE file_name LIKE '%your_datafile_name%';
2. Move or Drop Objects in the Datafile
If the datafile contains objects, move them to another tablespace:
ALTER TABLE table_name MOVE TABLESPACE new_tablespace_name;
Use ALTER INDEX for indexes or ALTER TABLE for LOB segments.
If the datafile is empty, proceed with dropping it.
3. Offline the Datafile
Take the datafile offline to prevent further access.
ALTER DATABASE DATAFILE '/path_to_datafile/datafile_name.dbf' OFFLINE;
4. Resize the Datafile to Zero
Reduce the datafile size to zero to release space.
ALTER DATABASE DATAFILE '/path_to_datafile/datafile_name.dbf' RESIZE 1M;
5. Drop the Datafile
Remove the datafile entry from the control file.
ALTER DATABASE DATAFILE '/path_to_datafile/datafile_name.dbf' DROP;
---------------------
Smallfile Tablespaces:
---------------------
A smallfile tablespace can contain up to 1022 datafiles.
Database Block Size:
The size of the database block (e.g., 8 KB, 16 KB) affects the maximum size of a datafile.
For example, with an 8 KB block size:
Maximum size of a smallfile datafile is 32 GB.
Maximum size of a bigfile datafile is 128 TB.
4KB blocks Not common for most Oracle databases, which default to 8 KB for general-purpose use.
A smallfile tablespace can have multiple datafiles, but the maximum size of each datafile is limited as follows:
Block Size Maximum Size of One Datafile
8KB 32GB
16 KB 64 GB
32 KB 128 GB
64 KB 256 GB
Bigfile Tablespace :
-------------------
A bigfile tablespace contains only one datafile, but the datafile size can be much larger:
Block Size Maximum Size of One Datafile
8kb 32 TB
16 KB 128 TB
32 KB 256 TB
64 KB 512 TB
Bigfile Tablespaces :
---------------------
Oracle Database enables the creation of bigfile tablespaces. A bigfile tablespace consists of a single data or temporary file which can be up to 128 TB. The use of bigfile tablespaces can significantly reduce the number of data files for your database. Oracle Database supports parallel RMAN backup and restore on single data files.
A bigfile tablespace in oracle with 8k blocks can contain a 32 terabyte datafile. A big file tablespace with 32k blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an oracle database is limited (usually to 64k files). Therefore, big file tablespaces in oracle can significantly enhance the storage capacity of an oracle database.
Bigfile tablespace in oracle can reduce the number of datafiles needed for a database.
An additional benefit is that the db_files initialization parameter and maxdatafiles parameter of the create a database and create control file statements can be adjusted to reduce the amount of sga space required for datafile information and the size of the control file.
Bigfile tablespaces simplify database management by providing datafile transparency. SQL syntax for the alter tablespace statement lets you perform operations on tablespaces, rather than the underlying individual data files.
ex : create bigfile tablespace bigtbs Datafile ‘/u02/oracle/data/bigtbs01.dbf’ size 50g ;
Sql> alter tablespace bigtbs autoextend on next 20g;
You can specify size in kilobytes (k), megabytes (m), gigabytes (g), or terabytes (t).
=====================================
Here’s a comprehensive comparison of the shutdown modes in Oracle:
Normal :
- Waits for all users to disconnect.
- Users must disconnect manually.
- No new connections allowed; waits for transactions to finish.
- Instance stops cleanly; all resources are released.
- No recovery needed.
- When shutting down during maintenance with no active users.
Immediate :
- Disconnects users gracefully and stops the instance.
- Users are disconnected automatically.
- Rolls back active transactions before shutting down.
- Instance stops cleanly after rollback.
- No recovery needed.
- When you need to shut down quickly but still ensure rollback of active transactions.
Transactional :
- Waits for active transactions to complete before shutting down.
- Disconnects users only after their active transactions are completed.
- Waits for active transactions to commit or rollback.
- Instance stops cleanly after completing active transactions.
- No recovery needed.
- When you want to avoid disrupting active transactions but disallow new ones.
Abort :
- Abruptly shuts down the instance.
- All users are forcibly disconnected.
- All transactions are terminated without rollback.
- Instance may require recovery during next startup.
- Instance recovery required (redo log application).
- In emergency situations, such as system failure or unresponsive database.
=================================
Oracle database startup sequence:
=================================
1. Nomount Stage:
Oracle reads the parameter file (PFILE or SPFILE) located in $ORACLE_HOME/dbs.
The System Global Area (SGA) is allocated, and background processes are started.
The instance is started (Instance = SGA + background processes).
Oracle opens the alert log and trace files to log startup information.
No database is associated with the instance at this stage.
You can create a database or switch between PFILE and SPFILE.
2. Mount Stage:
Oracle looks for the control file, which contains metadata about the database (like the locations of data files and redo log files). The control file’s location is defined in the parameter file.
Oracle reads the control file to locate the data files and redo log files.
The database is not fully available for regular operations, only the SYS user can access it.
In this stage, you can perform tasks like enabling archive log mode, database recovery, and some RMAN operations.
3. Open Stage:
Oracle opens the headers of data files and redo log files and compares their SCN (System Change Number) with the SCN in the control file.
If the SCNs match, the database opens; otherwise, Oracle may require media recovery.
All data files and tablespaces are now available, and the database opens in read-write mode by default.
Any valid user can connect and start working with the database.
Trace file in Oracle :
======================
A trace file in Oracle is a diagnostic file generated by the database to help troubleshoot issues and monitor database behavior. It contains detailed information about database operations, errors, and performance metrics, which can be used for debugging and optimization.
Types of Trace Files
--------------------
Background Process Trace Files:
Generated by background processes (e.g., DBWR, LGWR, SMON, PMON).
Located in the BACKGROUND_DUMP_DEST directory.
Used for diagnosing database-wide issues.
User/Session Trace Files:
Generated for specific user sessions.
Located in the USER_DUMP_DEST directory.
Enable session tracing using:
ALTER SESSION SET SQL_TRACE = TRUE;
Location of Trace Files :
------------------------
The trace files are stored in the directory specified by:
DIAGNOSTIC_DEST in Oracle 11g and later.
USER_DUMP_DEST or BACKGROUND_DUMP_DEST in earlier versions.
You can find the directory path using:
SHOW PARAMETER dump;
Analyzing Trace Files
TKPROF for SQL Trace Files:
Use the tkprof utility to analyze raw trace files and generate a readable report:
tkprof tracefile.trc outputfile.txt explain=username/password
Direct Inspection:
Open trace files in a text editor to inspect detailed error messages, SQL statements, and process information.
====================================
Find Blocked Sessions in Oracle
===================================
Create Database Lock
Let us create a simple lock inside database and try to create a lock situation
create table lock_test(
id# number primary key,
value varchar2(20)
);
insert into lock_test values (1, 'Insert lock test');
Open a new session and try to run the same INSERT command
-- run it in a new session
SQL> insert into lock_test values (1, 'Insert lock test');
Note
In session 1, we did not commit the transaction
Session 2 is trying to insert a record with same primary key value
Session 2 goes on waiting until session 1 issues COMMIT / ROLLBACK
======================
Find Blocked Sessions
========================
Open a new sessions and run below query to find Blocking and Blocked session
select a.SID "Blocking Session", b.SID "Blocked Session"
from v$lock a, v$lock b
where a.SID != b.SID and a.ID1 = b.ID1 and a.ID2 = b.ID2 and
b.request > 0 and a.block = 1;
Blocking Session Blocked Session
---------------- ---------------
13 135
=============================================
Another query that can help you with finding the blocking and blocked sessions
=================================
col blocking_status for a120;
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine
|| ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
>BLOCKING_STATUS
--------------------------------------------------------------------------------
SYS@test.com ( SID=13 ) is blocking SYS@test.com ( SID=135 )
======================
Find Lock Wait Time
===================
To find how long the blocked session is waiting (in minutes)
SELECT
blocking_session "BLOCKING_SESSION",
sid "BLOCKED_SESSION",
serial# "BLOCKED_SERIAL#",
seconds_in_wait/60 "WAIT_TIME(MINUTES)"
FROM v$session
WHERE blocking_session is not NULL
ORDER BY blocking_session;
BLOCKING_SESSION BLOCKED_SESSION BLOCKED_SERIAL# WAIT_TIME(MINUTES)
---------------- --------------- --------------- ------------------
13 135 52665 4.55
========================
Find Blocked SQL
========================
To check what SQL is being run by the BLOCKED SESSION inside the database OR which SQL command is waiting
SELECT SES.SID, SES.SERIAL# SER#, SES.PROCESS OS_ID, SES.STATUS, SQL.SQL_FULLTEXT
FROM V$SESSION SES, V$SQL SQL, V$PROCESS PRC
WHERE
SES.SQL_ID=SQL.SQL_ID AND
SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND
SES.PADDR=PRC.ADDR AND
SES.SID=&Enter_blocked_session_SID;
SID SER# OS_ID STATUS SQL_FULLTEXT
---------- ---------- ------------------------ -------- --------------------------------------------------------------------------------
135 52665 4461 ACTIVE insert into emp values (15,'ABC',15)
==========================================
Find Locked Table
=====================================
Run below query to find the table locked, table owner, lock type and other details
col session_id head 'Sid' form 9999
col object_name head "Table|Locked" form a30
col oracle_username head "Oracle|Username" form a10 truncate
col os_user_name head "OS|Username" form a10 truncate
col process head "Client|Process|ID" form 99999999
col owner head "Table|Owner" form a10
col mode_held form a15
select lo.session_id,lo.oracle_username,lo.os_user_name,
lo.process,do.object_name,do.owner,
decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)',
3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive',
to_char(lo.locked_mode)) mode_held
from gv$locked_object lo, dba_objects do
where lo.object_id = do.object_id
order by 5
/
>
Client
Oracle OS Process Table Table
Sid Username Username ID Locked Owner MODE_HELD
----- ---------- ---------- ------------------------ ------------------------------ ---------- ---------------
135 SYS oracle 4461 EMP SYS Row Excl (SX)
14 SYS oracle 5036 EMP SYS Row Excl (SX)
=============================
Resolving Locks in Oracle
=============================
As per Oracle, the blocked (or waiting) session will continue to wait until
Blocking session issues a COMMIT
Blocking session issues a ROLLBACK
Blocking session disconnects from the database
As a DBA, the only way you can help resolve a lock conflict is by killing either the blocking session or by killing the blocked (waiting) session.
Before you decide which session to kill, you must send the information to application team to get their approval on which session to be killed.
Use below query to kill a session inside Oracle
SQL> alter system kill session '35,52665';
--actual syntax
SQL> alter system kill session 'SID,Serial#';
If you don’t know the serial# then query v$session
SQL> select serial# from v$session where SID=135;
SERIAL#
----------
52665
====================
SELECT FOR UPDATE
SELECT FOR UPDATE command will select the specific records from the table and lock those records. This prevents other users from updating the records
as the lock on those records is held by the current session.
SELECT * FROM EMP WHERE ID=10 FOR UPDATE;
Let us assume that some other session is holding lock on the records returned by – SELECT * FROM EMP WHERE DEPTNO=10. The above query will wait until the lock is released.
Locking a Table:
SQL> LOCK TABLE emp IN EXCLUSIVE MODE;
Table(s) Locked.
=======================================
Removing Deadlocks in Oracle
=======================================
You locked row A, another session locked row B. Now, you want to lock row B but you are blocked by the other session.
The other session wants to lock row A but they are blocked by you. Hence, deadlock!
Create a Deadlock
Resolving Deadlock
Read our article on finding locked objects inside database and killing sessions!
Deadlock is a situation where two or more transactions are waiting for one another to give up locks.
ex-
SQL> update emp set name='MNL' where id =10;
1 row updated.
SQL> update emp set name='SCOT' where id =11;
----waiting session
SQL> update emp set name='SCOT' where id =11;
1 row updated.
SQL> update emp set name='MNL' where id =10;
update emp set name='MNL' where id =10
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
==================
Resolving Deadlock
==================
Oracle is smart and it is able to find deadlock situation within 3 seconds. Below are the options to avoid deadlocks inside the database
Ask the session getting deadlock error ORA-00060 to issue either COMMIT or ROLLBACK
Ask the waiting session to kill the SQL / transaction
Look inside alert log / trace file for the sessions involved in deadlock and inform application team to improve the code
As a DBA, whenever you get a Deadlock alert, immediately contact application team and inform them.
===============================
Hard Parsing :
=============
A hard parse occurs when Oracle cannot reuse an existing SQL execution plan and needs to create a new one. This process is resource-intensive.
Key Characteristics:
Occurs When:
A SQL statement is executed for the first time.
There is no matching statement in the shared SQL area of the library cache.
SQL statements differ in structure, literals, or bind variables.
Process:
Syntax check: Oracle verifies the SQL syntax.
Semantic check: Oracle checks for object existence and user permissions.
Query transformation: Optimizer rewrites the query if needed.
Optimization: The optimizer generates the best execution plan.
Execution plan creation and storage: The plan is stored in the shared SQL area.
Impact:
High CPU and memory usage.
Increased contention for shared resources like the library cache and latch mechanisms.
Example:
SELECT * FROM employees WHERE department_id = 10;
If this statement is run for the first time, Oracle will perform a hard parse.
Soft Parsing :
==============
A soft parse occurs when Oracle can reuse an existing execution plan from the shared SQL area. This process is much faster and less resource-intensive than a hard parse.
Key Characteristics:
Occurs When:
A SQL statement matches an existing one in the shared SQL area (same text, case, and structure).
Bind variables are used consistently.
SQL cursor sharing is enabled.
Process:
Oracle checks the shared SQL area for a matching execution plan.
If found, the execution plan is reused without re-optimization.
If minor differences are detected (e.g., different session settings), Oracle may perform lightweight validation.
Impact:
Reduced CPU and memory usage.
Faster execution as no new execution plan is created.
Example:
SELECT * FROM employees WHERE department_id = :dept_id;
If this statement has been executed before with the same bind variable, Oracle performs a soft parse.
Best Practices to Minimize Hard Parsing :
-----------------------------------------
Use Bind Variables:
Replace literals with bind variables to encourage plan reuse.
Example:
SELECT * FROM employees WHERE department_id = :dept_id;
Enable Cursor Sharing:
Set the CURSOR_SHARING parameter to FORCE or SIMILAR to allow statements with literals to be reused.
Optimize Application Code:
Avoid dynamic SQL unless necessary.
Use stored procedures to encapsulate repetitive SQL logic.
Monitor and Tune:
Use Oracle tools like AWR, ADDM, or V$SQLAREA to monitor parsing statistics.
Identify and resolve high hard-parse scenarios.
----------------------------------
What is Cursor Sharing in Oracle?
----------------------------------
Cursor Sharing refers to Oracle's ability to reuse shared SQL cursors for similar SQL statements by generalizing literal values in SQL queries. This feature helps to optimize memory usage in the Shared Pool and improve query performance by reducing the need for hard parsing.
Why is Cursor Sharing Important?
Reduces Hard Parsing:
When a SQL statement is executed for the first time, Oracle performs hard parsing, which involves syntax analysis, semantic checking, and query plan generation.
With cursor sharing, Oracle minimizes hard parsing by reusing existing execution plans for similar queries.
Improves Performance:
Reduces CPU overhead associated with hard parsing.
Speeds up query execution by reusing execution plans.
Conserves Shared Pool Memory:
Avoids creating multiple copies of SQL statements that differ only in literals, saving space in the Shared Pool.
Enhances Scalability:
Reduces contention for shared resources in high-concurrency environments.
Cursor Sharing Modes :
======================
Oracle provides three modes for cursor sharing, controlled by the CURSOR_SHARING initialization parameter:
EXACT (Default) :
Requires an exact match of SQL text (including literals) to reuse a shared cursor.
Suitable for applications that use bind variables extensively.
Example:
SELECT * FROM employees WHERE employee_id = 101; -- Creates a new cursor
SELECT * FROM employees WHERE employee_id = 102; -- Creates another cursor
FORCE:
------
Forces Oracle to replace literals with bind variables internally, allowing similar queries to share the same cursor.
Ideal for applications that do not use bind variables.
Example:
SELECT * FROM employees WHERE employee_id = 101; -- Creates a new cursor
SELECT * FROM employees WHERE employee_id = 102; -- Reuses the previous cursor
SIMILAR (Deprecated in Oracle 12c and later):
-------------
Similar to FORCE, but Oracle generates different execution plans if literal values lead to different query results (e.g., due to histograms).
Example:
SELECT * FROM employees WHERE employee_id = 101; -- Creates a new cursor
SELECT * FROM employees WHERE employee_id = 102; -- May create another cursor if the plan differs
When to Use Cursor Sharing?
EXACT:
When applications use bind variables consistently.
Provides the most efficient and predictable performance.
FORCE:
When applications do not use bind variables and performance suffers due to excessive hard parsing.
Helps improve performance without modifying application code.
How to Set Cursor Sharing :
------------------------
At the System Level:
Set CURSOR_SHARING using ALTER SYSTEM:
ALTER SYSTEM SET CURSOR_SHARING = FORCE;
At the Session Level:
Modify CURSOR_SHARING for a specific session:
ALTER SESSION SET CURSOR_SHARING = FORCE;
Persistent Configuration:
Update the parameter in the SPFILE or PFILE:
ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE = BOTH;
----------------------------------------------------------
What are Bind Variables in Oracle?
----------------------------------
Bind variables are placeholders in SQL statements that are replaced with actual values at runtime. They are represented by a colon (:) followed by a variable name. Bind variables allow the database to reuse a single parsed SQL statement for multiple executions with different values, improving performance and reducing memory usage.
How Bind Variables Work
SQL Statement with Bind Variables:
Instead of hardcoding literal values, placeholders are used:
Step 3: Using Bind Variables in SQL*Plus
Define Bind Variable and Execute Query
-- Define a bind variable
VARIABLE emp_id NUMBER;
-- Assign a value to the bind variable
EXEC :emp_id := 101;
-- Execute a query using the bind variable
SELECT first_name, last_name FROM employees WHERE employee_id = :emp_id;
SQL>
FIRST_NAME LAST_NAME
-------------------- --------------------
John Doe
----------------------------------------------------
In Oracle Database, Checkpoint and System Change Number (SCN) are related but distinct concepts:
1. Checkpoint
A checkpoint is a process that synchronizes the database buffer cache with the datafiles on disk. It ensures that all modified (dirty) buffers in memory are written to disk up to a certain point in time.
Purpose of Checkpoint:
Reduces instance recovery time in case of failure.
Ensures that modified data is safely stored on disk.
Updates the control file and datafile headers with the latest checkpoint SCN.
Types of Checkpoints:
Full Checkpoint: Writes all dirty buffers to disk.
Incremental Checkpoint: Writes some dirty buffers periodically.
Log Switch Checkpoint: Occurs when a redo log switch happens.
Fast-Start Checkpointing: Helps reduce recovery time by continuously writing dirty buffers to disk.
2. System Change Number (SCN)
An SCN (System Change Number) is a unique sequential number that Oracle assigns to every committed transaction. It serves as a logical timestamp for database changes.
Purpose of SCN:
Helps track committed transactions.
Ensures consistency across the database.
Used for recovery and point-in-time recovery operations.
Helps in maintaining data consistency during read consistency operations.
Key Properties of SCN:
Stored in datafile headers, control files, and redo logs.
Used in Oracle Recovery Mechanisms to determine which transactions to apply or undo.
Every transaction has a unique SCN.
Multiplexing in Oracle :
=========================
Multiplexing in Oracle refers to maintaining multiple copies of critical database files (such as control files and redo log files) to enhance data reliability and fault tolerance.
Oracle multiplexes these files to prevent a single point of failure. If one copy is corrupted or lost, the database can continue running using the remaining copies.
1. Multiplexing Control Files
A control file contains essential metadata about the database, including:
Database structure
Checkpoint information
Redo log file locations
Why Multiplex Control Files?
If a control file is lost or corrupted and there is no backup, the database may become unrecoverable.
How to Enable Control File Multiplexing?
You can specify multiple control files in the CONTROL_FILES parameter in the initialization parameter file (pfile/spfile).
Example: Adding Multiple Control Files
Modify the CONTROL_FILES parameter:
ALTER SYSTEM SET CONTROL_FILES =
'/u01/oracle/oradata/control01.ctl',
'/u02/oracle/oradata/control02.ctl',
'/u03/oracle/oradata/control03.ctl'
SCOPE=SPFILE;
Then, restart the database:
SHUTDOWN IMMEDIATE;
STARTUP;
Benefits of Multiplexing Control Files:
✔ Prevents database failure due to control file corruption.
✔ Allows recovery using an intact copy if one is lost.
2. Multiplexing Redo Log Files :
---------------------------------
Redo log files store transaction changes before they are written to data files. Oracle recommends multiplexing redo logs to prevent data loss in case of a disk failure.
Why Multiplex Redo Log Files?
If a redo log file is lost or corrupted, transactions that rely on it for recovery may be lost.
How to Enable Redo Log Multiplexing?
Each redo log group can have multiple members stored on different disks.
Example: Adding a Multiplexed Redo Log File
ALTER DATABASE ADD LOGFILE MEMBER '/u02/oracle/redo02.log'
TO GROUP 1;
Checking Existing Redo Log Members:
SELECT GROUP#, MEMBER FROM V$LOGFILE;
Benefits of Multiplexing Redo Logs:
✔ Prevents transaction loss if a redo log file is corrupted.
✔ Ensures continuous database operation during disk failures.
=============================
Profile management :
=========================
Profile management in Oracle refers to the process of creating and managing user profiles to control and limit the database resources and password policies that can be used by individual users or groups of users. Profiles are a way to enforce security policies and resource limits across the database.
Key Parameters in Oracle Profile Management
Profiles in Oracle can be used to manage two main types of limits:
Resource Limits: These limits control the amount of system resources that a user can consume.
Password Management: These parameters control password policies, such as password expiration, complexity, and reuse.
Resource Limit Parameters
SESSIONS_PER_USER: Limits the number of concurrent sessions a user can have.
CPU_PER_SESSION: Limits the CPU time (in hundredths of a second) that a session can use.
CPU_PER_CALL: Limits the CPU time (in hundredths of a second) that a single SQL statement can use.
CONNECT_TIME: Limits the total elapsed time (in minutes) that a session can be connected.
IDLE_TIME: Limits the amount of continuous idle time (in minutes) allowed for a session.
LOGICAL_READS_PER_SESSION: Limits the number of data blocks read (both from memory and disk) in a session.
LOGICAL_READS_PER_CALL: Limits the number of data blocks read (both from memory and disk) for a single SQL statement.
PRIVATE_SGA: Limits the amount of private space (in bytes) that a session can allocate in the Shared Global Area (SGA) for private SQL areas (relevant for shared server architecture).
COMPOSITE_LIMIT: A weighted sum of several resource limits (CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA).
Password Management Parameters
FAILED_LOGIN_ATTEMPTS: Specifies the number of failed login attempts before the account is locked.
PASSWORD_LIFE_TIME: Specifies the number of days a password can be used before it expires.
PASSWORD_REUSE_TIME: Specifies the number of days before a password can be reused.
PASSWORD_REUSE_MAX: Specifies the number of password changes required before a password can be reused.
PASSWORD_LOCK_TIME: Specifies the number of days an account will be locked after exceeding the failed login attempts.
PASSWORD_GRACE_TIME: Specifies the number of days after password expiration during which the user can still log in and change the password.
PASSWORD_VERIFY_FUNCTION: Specifies a PL/SQL function to enforce password complexity rules.
Creating and Managing Profiles
To create a profile, you use the CREATE PROFILE statement. For example:
CREATE PROFILE user_profile LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 100000
CONNECT_TIME 60
IDLE_TIME 10
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 180
PASSWORD_REUSE_MAX 5
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7;
To assign a profile to a user, you use the ALTER USER statement:
ALTER USER username PROFILE user_profile;
To modify an existing profile, you use the ALTER PROFILE statement:
ALTER PROFILE user_profile LIMIT
CPU_PER_SESSION UNLIMITED
PASSWORD_LIFE_TIME 120;
To drop a profile, you use the DROP PROFILE statement:
DROP PROFILE user_profile CASCADE;
The CASCADE option is necessary if the profile is currently assigned to any users. It reassigns those users to the default profile.
Default Profile
Oracle provides a default profile named DEFAULT. If no profile is explicitly assigned to a user, the user will inherit the limits defined in the DEFAULT profile. You can modify the DEFAULT profile to change the default behavior for all users.
Enabling Resource Limits
Resource limits are enforced only if the RESOURCE_LIMIT initialization parameter is set to TRUE. You can check and set this parameter as follows:
SHOW PARAMETER RESOURCE_LIMIT;
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
================================
ACID properties :
==================
In Oracle Database, ACID properties are fundamental principles that ensure the reliability and consistency of transactions. ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties guarantee that database transactions are processed reliably, even in the event of system failures or concurrent access.
1. Atomicity
Atomicity ensures that a transaction is treated as a single, indivisible unit of work. Either all the operations in the transaction are completed successfully, or none of them are applied. If any part of the transaction fails, the entire transaction is rolled back to its original state.
How Oracle Ensures Atomicity:
Oracle uses undo segments to store the before-image of data. If a transaction fails, Oracle uses the undo data to roll back the changes.
Example:
BEGIN
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT; -- Both updates are applied
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- Both updates are rolled back
END;
2. Consistency
Consistency ensures that a transaction brings the database from one valid state to another, maintaining database integrity. All constraints, rules, and relationships defined in the database must be satisfied after the transaction.
How Oracle Ensures Consistency:
Oracle enforces integrity constraints (e.g., primary keys, foreign keys, unique keys, check constraints) to ensure data validity.
If a transaction violates any constraint, Oracle rolls back the transaction.
Example:
ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);
-- If a transaction tries to set a negative balance, it will fail.
3. Isolation
Isolation ensures that concurrent transactions do not interfere with each other. Each transaction operates in isolation, as if it were the only transaction running in the system. This prevents issues like dirty reads, non-repeatable reads, and phantom reads.
How Oracle Ensures Isolation:
Oracle uses multi-version concurrency control (MVCC) to provide transaction isolation.
Different isolation levels can be set using the SET TRANSACTION statement:
READ COMMITTED (default): Ensures that only committed data is read.
SERIALIZABLE: Ensures complete isolation, as if transactions were executed serially.
Example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
4. Durability
Durability ensures that once a transaction is committed, its changes are permanent and will survive system failures (e.g., power outages, crashes). The changes are stored in non-volatile storage (e.g., disk).
How Oracle Ensures Durability:
Oracle uses the redo log to record all changes made by transactions. The redo log is written to disk before the transaction is committed.
In the event of a failure, Oracle uses the redo log to recover committed transactions.
Example:
sql
Copy
COMMIT; -- Changes are written to the redo log and made permanent.
How Oracle Implements ACID Properties
Undo Segments:
Store the before-image of data for rollback and read consistency.
Redo Logs:
Record all changes made by transactions for recovery and durability.
Locking Mechanism:
Prevents conflicts between concurrent transactions.
Oracle uses row-level locking to minimize contention.
Multi-Version Concurrency Control (MVCC):
Allows readers to access consistent data without blocking writers, and vice versa.
Checkpoints:
Periodically write dirty buffers from memory to disk to ensure durability.
Example of ACID in Oracle
Consider a banking transaction where money is transferred from one account to another:
BEGIN
-- Deduct $100 from Account 1
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Add $100 to Account 2
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- Commit the transaction
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- Rollback if any error occurs
ROLLBACK;
END;
Atomicity: Both updates are treated as a single unit. If either fails, both are rolled back.
Consistency: The total balance across accounts remains the same.
Isolation: Other transactions cannot see the intermediate state of the accounts.
Durability: Once committed, the changes are permanent and survive system failures.
Isolation Levels in Oracle
Oracle supports the following isolation levels:
READ COMMITTED (Default):
Ensures that only committed data is read.
Prevents dirty reads but allows non-repeatable reads and phantom reads.
SERIALIZABLE:
Ensures complete isolation.
Prevents dirty reads, non-repeatable reads, and phantom reads.
READ ONLY:
Allows only read operations within the transaction.
Ensures that the data remains unchanged during the transaction.
Example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
==============================================
If redo log corruption occurs in Oracle, the recovery steps depend on the severity of the corruption and whether the database is in ARCHIVELOG or NOARCHIVELOG mode. Here’s how you can handle it:
1. Identify the Problem
Check the alert log and trace files for redo log corruption errors. Common error messages include:
ORA-00353: log corruption near block
ORA-00312: online log
ORA-00368: checksum error in redo log
Run the following query to identify corrupted redo logs:
SELECT GROUP#, STATUS, MEMBER
FROM V$LOGFILE;
2. Recover from Redo Log Corruption
Scenario 1: Database is in ARCHIVELOG Mode
If your database is in ARCHIVELOG mode, you can recover from backups and archived logs.
a. Try a Normal Recovery
If the instance crashed due to redo corruption, restart the database:
SHUTDOWN IMMEDIATE;
STARTUP;
If corruption persists, perform media recovery:
RECOVER DATABASE;
ALTER DATABASE OPEN;
b. Drop and Recreate the Corrupt Redo Log Group
If the database is running and a non-current redo log is corrupted:
Identify the corrupt redo log group:
SELECT GROUP#, STATUS FROM V$LOG;
Drop and recreate it:
ALTER DATABASE CLEAR LOGFILE GROUP <group_number>;
If clearing fails, use:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group_number>;
⚠ Warning: This removes redo entries and may lead to data loss.
Recreate the log group:
ALTER DATABASE ADD LOGFILE GROUP <group_number> ('/path/to/new/logfile1.log', '/path/to/new/logfile2.log') SIZE 50M;
Scenario 2: Database is in NOARCHIVELOG Mode
---------------------------------------------
If your database is in NOARCHIVELOG mode, recovery is more difficult because redo logs are crucial for instance recovery.
a. Try to Open the Database
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN;
If it fails with redo corruption errors, try:
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP <group_number>;
If the corruption affects the current redo log, instance recovery might fail, and you may need to restore from the last cold backup.
b. Restore from Backup (If Needed)
If redo corruption prevents recovery, restore from the last full database backup:
SHUTDOWN IMMEDIATE;
RESTORE DATABASE FROM BACKUP;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
⚠ Note: RESETLOGS initializes redo logs and should be used carefully.
3. Prevent Future Redo Log Corruption
To reduce the risk of redo log corruption:
Use Multiplexed Redo Logs
Add multiple members to each redo log group:
ALTER DATABASE ADD LOGFILE MEMBER '/path/to/new/logfile.log' TO GROUP <group_number>;
Use Reliable Storage
Store redo logs on separate, high-quality storage to minimize hardware-related corruption.
Monitor Logs Regularly
Check for redo log issues:
SELECT * FROM V$LOG;
SELECT * FROM V$LOGFILE;
====================================
Comments
Post a Comment