How to check pdb database name and dbid in oracle 19c ?

 check pdb database name and dbid in oracle

SQL> 

SQL> select dbid , name from v$pdbs;

      DBID NAME

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

3326623803 PDB

SQL> 

How to start pluggable database in oracle 19c ?

 Start pluggable database in oracle 

SQL> 

SQL> alter session set container=pdb$seed;

Session altered.

SQL> 

SQL> show con_name

CON_NAME

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

PDB$SEED

SQL> 

SQL> 

SQL> alter pluggable database pdb open read write;

alter pluggable database pdb open read write

*

ERROR at line 1:

ORA-65118: operation affecting a pluggable database cannot be performed from

another pluggable database

SQL> alter session set container=pdb;

Session altered.

SQL> show con_name

CON_NAME

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

PDB

SQL> 

SQL> alter pluggable database pdb open read write;

Pluggable database altered.

SQL> 

SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

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

3 PDB   READ WRITE NO

SQL> 

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).