ACID properties

 

To maintain the integrity of the data, there are four properties described in the database management system, which are known as the ACID properties

ACID properties are a set of principles that ensure reliable transaction processing in a database. They stand for:

1. Atomicity – A transaction is either fully completed or fully rolled back. If one part of the transaction fails, the entire transaction is undone to maintain database integrity.
• Example: If you transfer money from one account to another, both debit and credit operations must succeed. If the debit succeeds but the credit fails, the entire transaction should be rolled back.

1. Consistency – A transaction must ensure that the database remains in a valid state before and after execution, maintaining all defined rules and constraints.
• Example: If a banking transaction deducts money from one account, it must ensure that the total balance across accounts remains consistent with predefined constraints.

1. Isolation – Transactions should execute independently without interference. Intermediate states of a transaction should not be visible to other concurrent transactions.
• Example: If two users try to book the last seat in a movie theater simultaneously, only one transaction should succeed to prevent overbooking.

1. Durability – Once a transaction is committed, the changes should be permanent, even in the case of system failures.
• Example: If a customer purchases an item online and the system crashes, the order details should remain intact in the database after recovery.

These ACID properties ensure database reliability, especially in multi-user and distributed environments.

Oracle Database vs Database Instance

 

Oracle Database vs. Oracle Database Instance

Oracle uses a multi-layered architecture where the database and database instance are distinct yet interconnected. Understanding their differences is key for Oracle DBAs.

1. Oracle Database 📂

An Oracle Database is a collection of physical files that store data and metadata. It consists of:
✅ Datafiles (.dbf) – Store actual user and system data.
✅ Control files (.ctl) – Track database structure and SCN.
✅ Redo log files (.log) – Record all changes for recovery.
✅ Temporary & Undo tablespaces – Handle transactions and rollback.
✅ Archived logs – Used for database recovery (in ARCHIVELOG mode).

✅ Password File  

✅ Alert logs logs 

✅ Trace logs logs 

🔹 The database persists even when the Oracle instance is shut down.

---

2. Oracle Database Instance 🚀

An Oracle Instance is a combination of memory structures (SGA & PGA) and background processes that interact with the database.

Instance Components:

✅ SGA (System Global Area) – Shared memory that holds cached data and SQL execution plans.
✅ PGA (Program Global Area) – Memory specific to each session/process.
✅ Background Processes – Key ones include:

• DBWR (Database Writer) – Writes data from memory to disk.
• LGWR (Log Writer) – Writes redo logs to disk for recovery.
• SMON (System Monitor) – Handles recovery and maintenance.
• PMON (Process Monitor) – Manages user connections and process cleanup.

🔹 The instance exists only when the database is running.

---

Key Differences:

Feature	Oracle Database	Oracle Instance
Definition	A collection of physical storage files	A combination of memory structures & background processes
Persistence	Permanent (even after shutdown)	Temporary (exists only when running)
Components	Datafiles, Control files, Redo logs, Tablespaces	SGA, PGA, Background Processes (DBWR, LGWR, PMON, SMON, etc.)
Location	Stored on disk	Resides in RAM (memory)
Startup Required?	Exists even when shut down	Created only when the database starts
Example	/u01/oradata/orcl/system01.dbf	orcl (instance name) managed by pmon

---

Real-World Example:

• When you start an Oracle database, an instance is created in memory.
• When you shut down the database, the instance is removed, but the database files remain.
• If a server crashes, the database files remain intact, but the instance must be restarted.

Command to Check Database & Instance:

SELECT name, open_mode FROM v$database;  -- Checks database status
SELECT instance_name, status FROM v$instance;  -- Checks instance status


Oracle Database Server Configuration 

Single Instance Database 

A Single Instance Database is the most common Oracle Database deployment where one 
database is managed by one instance on a single server


Architecture of a Single Instance Database
A Single Instance Database consists of:
✅ One Oracle Database (datafiles, control files, redo logs, archive logs).
✅ One Oracle Instance (SGA, PGA, background processes like DBWR, LGWR, SMON, PMON).
✅ One Server hosting the instance and database.


+----------------------+
|      Server         |
|  +--------------+   |
|  |  Instance    |   |  🟢  Instance manages memory (SGA/PGA) & processes
|  | (SGA, PGA)   |   |
|  +--------------+   |
|        |            |
|  +--------------+   |
|  |  Database    |   |  🟢  Database stores data (tablespaces, control files)
|  +--------------+   |
+----------------------+


Key Features of a Single Instance Database
🔹 One-to-One Mapping → One instance manages one database.
🔹 Runs on a Single Server → CPU, memory, and disk resources belong to one system.
🔹 Uses Default Listener → Connects users via Oracle Net Listener (tnsnames.ora).
🔹 No High Availability (HA) Features → If the server fails, the database is down.
🔹 Supports Standby Databases → Can be used with Oracle Data Guard for disaster recovery.

Benefits of a Single Instance Database
✅ Simple Deployment – Easy to install, configure, and manage.
✅ Lower Cost – No need for shared storage or multiple nodes.
✅ Efficient Performance – Best for applications that don’t require high availability.
✅ Easier Backup & Recovery – Managed using RMAN (Recovery Manager).

Limitations of a Single Instance Database
❌ No Automatic Failover – If the instance crashes, the database goes down.
❌ No Load Balancing – Cannot distribute the workload across multiple nodes.
❌ Scaling is Limited – Cannot scale horizontally like Oracle RAC.


Use Cases for a Single Instance Database
✅ Development & Testing Environments – Simple setup for application testing.
✅ Small to Medium Applications – Where high availability is not a critical requirement.
✅ Standalone Systems – Used in banking, healthcare, and retail for isolated systems.

A Single Instance Database is ideal for low to medium workloads, but for 
high availability, 
solutions like Oracle RAC or Data Guard are recommended.

Multi Single Instance database on Same Server 



+----------------------------------------------------------+
|                         Server                          |
|   +----------------+      +----------------+           |
|   | Oracle Home 1  |      | Oracle Home 2  |           |
|   | (Version 19)   |      | (Version 18)   |           |
|   +----------------+      +----------------+           |
|        |                     |                         |
|  +-----+-----+         +-----+-----+                   |
|  | Database 1  |       | Database 2  |                  |
|  | (db1)       |       | (db2)       |                  |
|  +-----+-----+         +-----+-----+                   |
|    |                      |                            |
| +----+----+            +----+----+                      |
| | Datafiles |          | Datafiles |                     |
| | Control   |          | Control   |                     |
| | Redo Log  |          | Redo Log  |                     |
| +----+----+            +----+----+                      |
|   |                      |                             |
|   +-----------------+    +----------------------------+. 
|  | Listener for db1 |    | Listener for db2           |
|  | (Port 1521)      |    | (Port 1522)                |
|  +-----------------+    +----------------------------+
+----------------------------------------------------------+



Oracle RAC  (Real Application Cluster )  



Multiple Oracle Homes
Each database instance on the server can have its own Oracle Home, which is a directory 
that contains the Oracle software binaries, libraries, and executables.
Oracle Home 1: /u01/app/oracle/product/19.0.0/dbhome_1 (for db1)
Oracle Home 2: /u01/app/oracle/product/18.0.0/dbhome_2 (for db2)
These separate Oracle Homes allow each instance to use its own Oracle binaries and 
configurations (even different versions if needed).

Edition of Oracle Database

 

Oracle Database comes in different editions, each designed for specific use cases, from small businesses to large enterprises. Here’s a breakdown of the available Oracle Database Editions:

---

1. Oracle Database Standard Edition 2 (SE2)

🔹 Designed for small to medium-sized businesses.
🔹 Supports basic high availability with Oracle RAC (up to 2 nodes).
🔹 Limited to 16 CPU threads (across a maximum of 2 sockets).
🔹 No advanced features like Partitioning, Data Guard, or In-Memory.
🔹 Cost-effective compared to Enterprise Edition.

---

2. Oracle Database Enterprise Edition (EE)

🔹 Full-featured enterprise-grade edition.
🔹 Supports unlimited CPU threads and large-scale environments.
🔹 Includes advanced features:
✅ Oracle RAC (Real Application Clusters) – High availability and scalability.
✅ Oracle Data Guard – Disaster recovery solution.
✅ Partitioning – Improves performance of large tables.
✅ Advanced Security & Encryption – Transparent Data Encryption (TDE), Data Masking.
✅ Multitenant Architecture – Supports multiple pluggable databases (PDBs).
✅ In-Memory Option – Speeds up analytics and reporting.
🔹 Used by large enterprises for mission-critical applications.

---

3. Oracle Database Express Edition (XE)

🔹 Free version for learning, development, and small-scale applications.
🔹 Limited to 2 CPU threads, 2GB RAM, and 12GB of user data.
🔹 No RAC, Data Guard, or Multitenant support.
🔹 Ideal for students, developers, and small projects.

---

4. Oracle Database Personal Edition (PE)

🔹 Full functionality of Enterprise Edition, but designed for single-user environments.
🔹 No RAC or Data Guard support.
🔹 Used mainly for development and testing.

---

5. Oracle Autonomous Database

🔹 Cloud-based self-driving database available in Oracle Cloud Infrastructure (OCI).
🔹 Manages itself using machine learning (performance tuning, security, patching).
🔹 Two types:
✅ Autonomous Data Warehouse (ADW) – Optimized for analytics and reporting.
✅ Autonomous Transaction Processing (ATP) – Optimized for OLTP workloads.
🔹 Ideal for organizations looking for fully managed, serverless database solutions.

---

Which Edition Should You Choose?

• Small Businesses & Cost-Effective Solution → Standard Edition 2 (SE2)
• Large Enterprises & High Availability Needs → Enterprise Edition (EE)
• Developers, Students & Small Projects → Express Edition (XE)
• Cloud-Based, AI-Driven Solution → Autonomous Database (ATP/ADW)

Introduction to Oracle Database

In this article , I am going to give you brief about what is Oracle database . 

Before going to deep dive let's discuss some basic terminology :-

What is Data ?

Data is fact or figures , Data is any collection of facts, statistics, or information that can be stored, processed, and analyzed. It can exist in various forms, such as numbers, text, images, audio, or video.

In computing and databases, data refers to raw information that is stored in structured or unstructured formats.

• Structured data: Organized in databases, tables, and spreadsheets (e.g., customer records, sales transactions).
• Unstructured data: Includes emails, videos, social media posts, and documents.

In the context of Oracle databases, data is stored in tables within schemas and can be retrieved, modified, and managed using SQL queries.

What is Database ?

A database is an organized collection of data that is stored, managed, and accessed electronically. It allows users to store, retrieve, modify, and manage data efficiently.

Key Characteristics of a Database:

1. Structured Storage – Data is stored in tables (rows and columns) for easy organization.
2. Data Integrity – Ensures accuracy and consistency through constraints and relationships.
3. Security – Provides access control and encryption to protect sensitive data.
4. Scalability – Can handle large volumes of data and support multiple users.
5. Querying & Processing – Uses SQL (Structured Query Language) for data manipulation.

Types of Databases:

• Relational Database (RDBMS) – Uses structured tables with relationships (e.g., Oracle, MySQL, SQL Server).
• NoSQL Database – Handles unstructured or semi-structured data (e.g., MongoDB, Cassandra).
• Cloud Database – Hosted on cloud platforms (e.g., Oracle Autonomous Database, AWS RDS).
• Distributed Database – Data is distributed across multiple locations.

What is DBMS ?

A DBMS (Database Management System) is software that manages databases by enabling users to store, retrieve, update, and delete data efficiently. It provides an interface between the database and end-users or applications, ensuring data is organized, secure, and accessible.

Oracle is relational database  i.e. oracle is RDBMS (Relational Database Management System) 

Key Functions of a DBMS:

1. Data Storage & Retrieval – Organizes data in structured formats (tables, indexes, etc.).
2. Data Security & Integrity – Ensures access control, encryption, and consistency.
3. Transaction Management – Supports ACID properties (Atomicity, Consistency, Isolation, Durability).
4. Concurrency Control – Manages multiple users accessing the data simultaneously.
5. Backup & Recovery – Protects data from failures or corruption.

What is RDBMS ?

An RDBMS (Relational Database Management System) is a type of database management system that stores data in a structured, tabular format with relationships between tables. It follows the relational model proposed by E.F. Codd in 1970.

---

Key Features of RDBMS:

1. Tables as Data Storage – Data is stored in rows and columns.
2. Relationships – Tables are linked using Primary Keys and Foreign Keys.
3. ACID Compliance – Ensures Atomicity, Consistency, Isolation, and Durability for reliable transactions.
4. Data Integrity & Constraints – Enforces rules like Primary Key, Foreign Key, Unique, Not Null, Check.
5. SQL Support – Uses Structured Query Language (SQL) for data operations.
6. Concurrency Control – Manages multiple users accessing data simultaneously.

---

Examples of RDBMS:

• Oracle Database – Enterprise-level RDBMS with features like Data Guard, RAC, and Partitioning.
• MySQL – Open-source RDBMS widely used for web applications.
• SQL Server – Microsoft’s RDBMS with BI and analytics features.
• PostgreSQL – Advanced open-source RDBMS with JSON and NoSQL support.

---

Example of RDBMS Structure (EMPLOYEES & DEPARTMENTS Tables):

EMPLOYEES Table

EMP_ID	EMP_NAME	DEPT_ID	SALARY
101	John Doe	1	80000
102	Jane Roe	2	75000
103	Alex Ray	1	90000

DEPARTMENTS Table

DEPT_ID	DEPT_NAME
1	IT
2	HR

Example Query (Fetching Employees with Department Name)

SELECT e.emp_name, d.dept_name 
FROM employees e 
JOIN departments d ON e.dept_id = d.dept_id;

Oracle as an RDBMS

Oracle Database is a high-performance RDBMS that supports advanced features like:
✅ Partitioning – Improves performance for large tables.
✅ RAC (Real Application Clusters) – Ensures high availability.
✅ Data Guard – Provides disaster recovery solutions.
✅ Multitenant Architecture – Supports multiple databases in a single container.

What is  Table ?

A table in a database is a structured object that stores data in a tabular format using rows (records) and columns (fields). It is the fundamental building block of a Relational Database Management System (RDBMS) like Oracle, MySQL, or SQL Server.

Structure of a Table:

• Columns (Fields) – Define the attributes of data (e.g., EMP_ID, EMP_NAME, SALARY).
• Rows (Records) – Store individual data entries. Each row represents a unique record.
• Data Types – Define the type of data a column can store (e.g., NUMBER, VARCHAR2, DATE).

Example of a Table (EMPLOYEES Table):

EMP_ID	EMP_NAME	DEPARTMENT	SALARY	HIRE_DATE
101	John Doe	IT	80000	2023-01-15
102	Jane Roe	HR	75000	2022-11-10
103	Alex Ray	Finance	90000	2021-09-20

What is SQL ?

SQL is a standard programming language used to manage and manipulate relational databases. It allows users to store, retrieve, update, and delete data efficiently. SQL is used in all Relational Database Management Systems (RDBMS) like Oracle, MySQL, SQL Server, and PostgreSQL.

Type of SQL Statement :

SQL is divided into several categories based on its functionality:

1. Data Query Language (DQL) – Used to Retrieve Data

• SELECT – Fetches data from tables.

SELECT emp_name, salary FROM employees WHERE department = 'IT';

2. Data Definition Language (DDL) – Defines Database Structure

• CREATE – Creates a table, view, or database object.
• ALTER – Modifies an existing table structure.
• DROP – Deletes a table or database object.
• TRUNCATE – Removes all records but keeps the structure.

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100),
    salary NUMBER
);

3. Data Manipulation Language (DML) – Modifies Data

• INSERT – Adds new records.
• UPDATE – Modifies existing records.
• DELETE – Removes specific records.

INSERT INTO employees (emp_id, emp_name, salary) VALUES (101, 'John Doe', 80000);

4. Data Control Language (DCL) – Manages User Permissions

• GRANT – Assigns privileges to users.
• REVOKE – Removes privileges from users.

GRANT SELECT ON employees TO user1;

5. Transaction Control Language (TCL) – Manages Transactions

• COMMIT – Saves changes permanently.
• ROLLBACK – Reverts changes to the last save point.
• SAVEPOINT – Creates intermediate save points in a transaction.

UPDATE employees SET salary = salary + 5000 WHERE emp_id = 101;
COMMIT;

How to set autocommit off in postgreSQL ?

 

postgres=# \set AUTOCOMMIT off

postgres=#

postgres=# \echo :AUTOCOMMIT

off

postgres=#

How to check autocommit in PostgreSQL ?

 

postgres=# \echo :AUTOCOMMIT

on

postgres=#

How to check command history in PostgreSQL ?

 

postgres=# \s

\d

\l

\l

\c

\c postgres

select pg_backend_pid();

alter user postgres password 'abcd12345';

\c demo

\c

\l

\c

\l

select * from pg_database;

show data_directory

show data_directory;

show data_directory;

select setting from pg_settings;

select * from pg_settings;

show data_directory;

\q

\conninfo

SELECT *

FROM pg_settings

WHERE name = 'port';

\c

\!

\! ls -lrt

\s

\d

\l

\s

postgres=#

How to execute OS command from psql prompt ?

 

postgres=#

postgres=# \!

bash-4.4$

bash-4.4$

bash-4.4$ ls -la

total 16

drwx------.  3 postgres postgres   79 Jan 15 08:33 .

drwxr-xr-x. 47 root     root     4096 Jan 10 12:04 ..

drwx------.  4 postgres postgres   51 Jan 10 12:05 16

-rw-------.  1 postgres postgres 1405 Jan 15 12:17 .bash_history

-rwx------.  1 postgres postgres  391 Jan 15 08:33 .bash_profile

-rw-------.  1 postgres postgres  352 Jan 15 10:00 .psql_history

bash-4.4$ 

postgres=#

postgres=#

postgres=#

postgres=# \! ls -lrt

total 0

drwx------. 4 postgres postgres 51 Jan 10 12:05 16

postgres=#

How to check database directory in PostgreSQL ?

 

postgres=# show data_directory;

     data_directory

------------------------

 /var/lib/pgsql/16/data

(1 row)

postgres=#

How to check port in postgreSQL?

 

How to check which port running our postgreSQL database ?

[postgres@myserver ~]$ netstat -plunt |grep postgres

(Not all processes could be identified, non-owned process info

 will not be shown, you would have to be root to see it all.)

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      5963/postgres

tcp6       0      0 ::1:5432                :::*                    LISTEN      5963/postgres

[postgres@myserver ~]$

[postgres@myserver ~]$

postgres=# \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/run/postgresql" at port "5432".

postgres=#

postgres=#

postgres=#

postgres=# SELECT *

postgres-# FROM pg_settings

postgres-# WHERE name = 'port';

 name | setting | unit |                       category                       |                short_desc

     | extra_desc |  context   | vartype | source  | min_val | max_val | enumvals | boot_val | reset_val | sourcefil

e | sourceline | pending_restart

------+---------+------+------------------------------------------------------+-------------------------------------

-----+------------+------------+---------+---------+---------+---------+----------+----------+-----------+----------

--+------------+-----------------

 port | 5432    |      | Connections and Authentication / Connection Settings | Sets the TCP port the server listens

 on. |            | postmaster | integer | default | 1       | 65535   |          | 5432     | 5432      |

  |            | f

(1 row)

postgres=#

How to connect to PostgreSQL database ?

 

[postgres@myserver ~]$ psql -p 5432 -U postgres -d postgres

Password for user postgres:

psql (16.6)

Type "help" for help.

postgres=# exit