Database Management System (DBMS) and Oracle Database Architecture

What is a Database Management System (DBMS)?

A Database Management System (DBMS) is a collection of interrelated data and a set of programs to access and manage that data. It is software that acts as an interface between the user and the database, allowing users to create, retrieve, update, and delete data in an organized and efficient manner.

Key Characteristics of DBMS:

• Data Abstraction: Hides the complexity of data storage from users
• Data Independence: Changes in data structure do not affect application programs
• Data Integrity: Ensures accuracy and consistency of data
• Data Security: Provides access control and authorization mechanisms
• Concurrent Access: Allows multiple users to access data simultaneously
• Backup and Recovery: Provides mechanisms to recover data after failures

Examples:

Oracle, MySQL, PostgreSQL, Microsoft SQL Server, MongoDB

Oracle Database Architecture

Oracle Database has a well-defined architecture consisting of two major components:

Oracle Database = Instance + Database (Physical Files)

1. Oracle Instance

An Oracle Instance is a combination of Memory Structures and Background Processes that manage database files. It exists in RAM and is created when the database is started.

A. Memory Structures (SGA - System Global Area)

The System Global Area (SGA) is a shared memory region allocated when an Oracle instance starts. It contains:

Additionally, each user session has a PGA (Program Global Area) - a private memory area for individual server processes.

B. Background Processes

Oracle uses several background processes to manage the database:

2. Oracle Database (Physical Storage Structures)

The physical database consists of OS files stored on disk:

A. Datafiles (.dbf)

• Store the actual data (tables, indexes, etc.)
• Each database has one or more datafiles
• Belong to a tablespace

B. Control Files

• Small binary files that record the physical structure of the database
• Contain database name, datafile locations, redo log file locations, and checkpoint information
• Critical for database startup and recovery

C. Redo Log Files

• Record all changes made to the database
• Used for database recovery in case of failure
• Oracle writes to redo log files in a circular fashion
• At least two redo log groups are required

D. Archive Log Files (Optional)

• Archived copies of filled redo log files
• Used for complete media recovery
• Required when database runs in ARCHIVELOG mode

E. Parameter File (SPFILE / PFILE)

• Contains initialization parameters for the Oracle instance
• Defines SGA size, number of processes, database name, etc.

F. Password File

• Stores passwords for privileged users (SYSDBA, SYSOPER)

3. Logical Storage Structures

Oracle also has logical structures that map to physical storage:

Database
   └── Tablespace (SYSTEM, SYSAUX, USERS, TEMP, UNDO)
         └── Segment (Table Segment, Index Segment)
               └── Extent (collection of contiguous blocks)
                     └── Data Block (smallest unit, e.g., 8KB)

Oracle Architecture Diagram (Simplified)

+--------------------------------------------------+
|              ORACLE INSTANCE (RAM)               |
|  +--------------------+  +--------------------+  |
|  |   SGA              |  | Background         |  |
|  | - Buffer Cache     |  | Processes:         |  |
|  | - Shared Pool      |  | SMON, PMON, DBWn   |  |
|  | - Redo Log Buffer  |  | LGWR, CKPT, ARCn   |  |
|  | - Large Pool       |  |                    |  |
|  +--------------------+  +--------------------+  |
+--------------------------------------------------+
              |                    |
              v                    v
+--------------------------------------------------+
|           ORACLE DATABASE (DISK)                 |
|  Datafiles | Control Files | Redo Log Files      |
|  Archive Logs | Parameter File | Password File    |
+--------------------------------------------------+

Summary

Note: The separation of Instance (memory + processes) and Database (disk files) is the fundamental design principle of Oracle, enabling features like crash recovery, high availability, and Oracle RAC (Real Application Clusters).

Backup, Restore, and Recovery in Oracle Database

Note: Reference notes were not available for this topic. The following answer is based on standard Oracle Database concepts as taught in BSc CSIT curriculum.

1. Key Terminology

Backup

A backup is a copy of data (database files, control files, archived redo logs, etc.) that can be used to reconstruct lost or damaged data. It protects the database against data loss due to hardware failure, user errors, or disasters.

"A backup is a copy of data that can be used to reconstruct data."

Restore

Restore is the process of copying backup files back to their original (or new) location. It is the physical act of placing backup files onto disk so that recovery can proceed.

Example: Copying a datafile from tape/backup location back to the original directory.

Recovery

Recovery is the process of applying redo logs (archived and online) to a restored backup to bring the database to a consistent and up-to-date state. Recovery happens after restore.

Backup --> Restore (copy files back) --> Recovery (apply redo logs) --> Database Online

2. Types of Backups in Oracle

A. Physical Backup

A copy of the actual physical files used in storing and recovering the database.

i. Cold Backup (Offline Backup)

• Database is shut down before taking backup.
• All datafiles, control files, and redo log files are copied.
• Simple but causes downtime.

Example:

SHUTDOWN IMMEDIATE;
-- Copy all datafiles, controlfiles, redo logs to backup location
-- (OS level copy)
STARTUP;

ii. Hot Backup (Online Backup)

• Database remains open and available during backup.
• Requires database to be in ARCHIVELOG mode.
• Used for 24x7 production systems.

Example using RMAN:

RMAN> BACKUP DATABASE;

B. Logical Backup

A backup of the logical data (tables, schemas, procedures) using Oracle utilities like Data Pump (expdp/impdp) or the older exp/imp.

Example:

expdp system/password FULL=Y DUMPFILE=fullbackup.dmp LOGFILE=backup.log

To restore:

impdp system/password FULL=Y DUMPFILE=fullbackup.dmp LOGFILE=restore.log

C. Full Backup

A backup of the entire database -- all datafiles, control files, and archived redo logs.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

D. Incremental Backup

Only backs up data blocks that have changed since the last backup. Saves time and storage.

Example:

-- Level 0 (Full baseline)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

-- Level 1 (Changed blocks only)
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

E. Cumulative vs Differential Incremental

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

3. Recovery Process in Oracle

A. Complete Recovery

Recovers the database to the most current state -- no data loss. All redo logs are applied.

Steps:

-- Step 1: Restore the database
RMAN> RESTORE DATABASE;

-- Step 2: Recover (apply all redo logs)
RMAN> RECOVER DATABASE;

-- Step 3: Open the database
RMAN> ALTER DATABASE OPEN;

B. Incomplete Recovery (Point-in-Time Recovery)

Recovers the database to a specific point in time before a failure or user error (e.g., accidental table drop).

Types:

Example (Time-based):

RMAN> RUN {
  SET UNTIL TIME "TO_DATE('2024-01-15 10:00:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN RESETLOGS;
}

Note: After incomplete recovery, database must be opened with RESETLOGS.

C. Tablespace Recovery

Recover a single tablespace without affecting the rest of the database.

RMAN> RESTORE TABLESPACE users;
RMAN> RECOVER TABLESPACE users;
RMAN> ALTER TABLESPACE users ONLINE;

D. Datafile Recovery

Recover a single damaged or missing datafile.

RMAN> RESTORE DATAFILE '/u01/oradata/users01.dbf';
RMAN> RECOVER DATAFILE '/u01/oradata/users01.dbf';

4. ARCHIVELOG Mode vs NOARCHIVELOG Mode

To enable it:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

5. A Complete Worked Example

Suppose the datafile users01.dbf of a production database is lost at 11:00 AM. A Level 0 backup exists from Sunday, Level 1 backups exist from each weekday night, and the database runs in ARCHIVELOG mode.

RMAN> SQL 'ALTER DATABASE DATAFILE ''/u01/oradata/users01.dbf'' OFFLINE';
RMAN> RESTORE DATAFILE '/u01/oradata/users01.dbf';   -- restore: copy from backup
RMAN> RECOVER DATAFILE '/u01/oradata/users01.dbf';   -- recovery: apply redo
RMAN> SQL 'ALTER DATABASE DATAFILE ''/u01/oradata/users01.dbf'' ONLINE';

RMAN restores the datafile from the Level 0 image, rolls it forward with the Level 1 incrementals, then applies the archived and online redo logs generated since that backup, so the datafile is brought back to 11:00 AM with no committed transaction lost. The rest of the database stays open for users throughout.

If instead a user had dropped a critical table at 10:00 AM, the loss is logical rather than physical, so an incomplete (point-in-time) recovery to 09:59 AM would be used and the database opened with RESETLOGS.

6. Conclusion

Backup, restore and recovery are three distinct stages of one protection strategy: backup creates the redundant copy in advance, restore puts that copy back on disk, and recovery applies redo to make it consistent and current. Oracle supports physical backups (cold and hot) and logical backups (Data Pump), full and incremental levels, and complete or point-in-time recovery. Running the database in ARCHIVELOG mode is the single decision that makes online backup and complete recovery possible, which is why every production Oracle database is configured that way.

Data Files and Their Relationship with Tablespace

What are Data Files?

Data files are the physical operating system files that actually store the data of an Oracle database on disk. Every piece of data stored in an Oracle database (tables, indexes, rollback segments, etc.) is ultimately written to one or more data files.

Key characteristics of data files:

• They have a physical existence on the disk (e.g., .dbf files)
• They store data in Oracle's internal block format
• A single data file belongs to only one tablespace
• They grow in size as more data is inserted into the database
• They can be configured to auto-extend when full

Relationship Between Data Files and Tablespace

The relationship between data files and tablespaces is a logical-to-physical mapping:

• A tablespace is a logical storage container in Oracle that groups related database objects (tables, indexes, etc.).
• A data file is the physical file on the OS that backs the tablespace.
• When a tablespace runs out of space, a new data file can be added to extend its capacity.

Diagram:

DATABASE
   |
   +-- TABLESPACE (Logical)
           |
           +-- Data File 1 (.dbf)  [Physical]
           |
           +-- Data File 2 (.dbf)  [Physical]

Command to Add a Data File to Existing Tablespace "USERS"

ALTER TABLESPACE users
ADD DATAFILE '/u01/oradata/orcl/users02.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 500M;

Explanation of each clause:

Note: The path must be accessible by the Oracle database server process. On Windows, the path would be like 'C:\oradata\users02.dbf'.