Database Administration

Multiplexing means keeping more than one identical copy of a critical file, each on a separate disk or location, so that Oracle writes to all copies at the same time. In Oracle it is mostly applied to the online redo log files and the control files.

Why it is needed

• Redo logs record every change made to the database. If the only copy is lost (disk failure), the instance can crash and committed changes since the last backup may be unrecoverable.
• With multiplexing, a group has several members on different disks. LGWR writes the same redo to every member of the current group, so losing one member does not stop the database.
• It removes a single point of failure and gives higher availability and safer recovery.

Steps to multiplex online redo log files

1. Check the existing groups and members:

SELECT group#, member, status FROM v$logfile;
SELECT group#, members, bytes, status FROM v$log;

2. Make sure the new location (a different disk or mount point) exists and the Oracle OS user can write to it.
3. Add a new member to each existing group:

ALTER DATABASE ADD LOGFILE MEMBER '/disk2/oradata/orcl/redo01b.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/disk2/oradata/orcl/redo02b.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/disk2/oradata/orcl/redo03b.log' TO GROUP 3;

4. Switch the log and checkpoint so that the new members are used and become valid:

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

5. Verify with SELECT group#, member, status FROM v$logfile;. Every group should now show at least two members with no INVALID status.

Each group needs the same size for all its members, and the members of one group should be on different disks.

Oracle instance

An Oracle instance is the set of memory structures and background processes that Oracle starts to access and manage a database. Instance = SGA + background processes (such as DBWn, LGWR, CKPT, SMON, PMON, ARCn). The database is the set of physical files on disk (datafiles, control files, redo log files), while the instance operates on it. A database is opened by exactly one instance (or by several in RAC). Startup creates the instance first (NOMOUNT), then mounts and opens the database.

Data structures in the SGA (System Global Area)

The SGA is a shared memory region allocated at instance startup and shared by all server and background processes. Its main components are:

1. Database buffer cache: Holds copies of data blocks read from datafiles. It reduces disk I/O; blocks are managed with an LRU algorithm, and modified ("dirty") blocks are written by DBWn.
2. Redo log buffer: A circular buffer holding redo entries (changes made to the database) before LGWR writes them to the online redo log files.
3. Shared pool: Contains the library cache (parsed SQL and PL/SQL, execution plans), the data dictionary cache (metadata such as table and user definitions), the result cache and control structures.
4. Large pool: Optional area for large allocations such as RMAN backup/restore, shared-server session memory and parallel execution.
5. Java pool: Memory for Java code and data inside the database.
6. Streams pool: Used by Oracle Streams / GoldenGate.
7. Fixed SGA: Small area with internal housekeeping information about the instance.

Automatic Shared Memory Management uses the SGA_TARGET parameter to size these components dynamically.

Synonym

A synonym is an alias, an alternative name, for a database object such as a table, view, sequence, procedure, function or package. It stores only the name reference, not the data. Synonyms may be private (belonging to one schema) or public (accessible to all users, created by a DBA with CREATE PUBLIC SYNONYM).

Importance

• Location transparency: hides the owner, schema and even the database link of the object, so users need not write schema.object@dblink.
• Simplifies SQL: shorter, easier names for users and applications.
• Easy maintenance: if the underlying object is renamed or moved, only the synonym needs redefining and applications need no changes.
• Security: hides the real object name and owner (access still requires privileges).
• Convenience: a public synonym lets all users access a shared object without prefixing it.

Example

-- hr owns EMPLOYEES; user scott wants to access it simply
GRANT SELECT ON hr.employees TO scott;

-- Private synonym for scott
CREATE SYNONYM emp FOR hr.employees;
SELECT * FROM emp;          -- same as SELECT * FROM hr.employees

-- Public synonym by DBA
CREATE PUBLIC SYNONYM employees FOR hr.employees;

-- Remove
DROP SYNONYM emp;
DROP PUBLIC SYNONYM employees;

Database archiving

In Oracle, the online redo log files are used in a circular fashion. In ARCHIVELOG mode, before a redo log group is overwritten, the ARCn background process copies it to an archive destination as an archived redo log. In NOARCHIVELOG mode the logs are overwritten and not saved.

Importance

• Allows point-in-time and complete recovery up to the moment of failure by applying archived redo to a backup.
• Allows online (hot) backups while the database is open and in use.
• Required for Data Guard standby databases, LogMiner, Flashback features and replication.
• Protects against data loss after media failure.

Steps to enable archive log mode

1. Connect as SYSDBA and check the current mode:

sqlplus / as sysdba
ARCHIVE LOG LIST;
SELECT log_mode FROM v$database;

2. (Optional) Set the archive destination:

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/arch' SCOPE=BOTH;

3. Shut the database down cleanly:

SHUTDOWN IMMEDIATE;

4. Start it in mount mode:

STARTUP MOUNT;

5. Enable archiving:

ALTER DATABASE ARCHIVELOG;

6. Open the database:

ALTER DATABASE OPEN;

7. Verify and test:

ARCHIVE LOG LIST;
ALTER SYSTEM SWITCH LOGFILE;

Database administrator (DBA)

A database administrator is the person (or team) responsible for installing, configuring, securing, maintaining, monitoring and tuning the database system, and for making sure data is available, consistent and protected. In Oracle, the DBA works with the privileged accounts SYS and SYSTEM.

Roles of a DBA

1. Installation and configuration: install Oracle software, create databases, apply patches and upgrades.
2. Storage and space management: create and manage tablespaces, datafiles, and plan capacity.
3. User and security management: create users, roles, profiles, assign privileges, enforce passwords, auditing and encryption.
4. Backup and recovery: plan backup policy, take backups (RMAN, Data Pump), test and perform recovery after failure.
5. Performance monitoring and tuning: tune SQL, memory (SGA/PGA), indexes, and diagnose bottlenecks with AWR/ADDM.
6. Database design support: work with developers on schema design, normalization and object creation.
7. High availability and disaster recovery: set up RAC, Data Guard, standby and failover.
8. Data migration and loading: import/export, SQL*Loader, data movement between systems.
9. Troubleshooting and maintenance: analyse alert logs, fix errors, schedule jobs, and apply routine maintenance.
10. Documentation and policy: maintain standards, documents, licences and compliance.

CDB and PDB in Oracle multitenant architecture

From Oracle 12c, the multitenant architecture lets a single database hold many separate databases.

CDB (Container Database): The main database that contains the Oracle-supplied metadata, common users, and one or more pluggable databases. It has one set of background processes, one SGA, control files, redo logs and undo (in the common model). It includes:

• CDB$ROOT: the root container with Oracle system metadata and common objects.
• PDB$SEED: a read-only template used to create new PDBs.

PDB (Pluggable Database): A portable collection of schemas, objects and tablespaces that appears to applications as an independent, complete database. Each PDB has its own local users, its own datafiles and its own service name, and can be unplugged and plugged into another CDB, cloned, or opened/closed independently.

Relationship

• A CDB contains zero, one or many PDBs (up to 252 in 12c, and 4096 in later releases).
• PDBs share the CDB's instance (memory, background processes, redo and control files), which saves resources compared with a separate instance for each database.
• Common users (names beginning with C##) are created in the root and exist in all PDBs; local users exist in one PDB only.
• Administration, patching and backup can be done once at CDB level, while isolation of data and users is kept at PDB level.

CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdbadmin IDENTIFIED BY pwd
  FILE_NAME_CONVERT=('/pdbseed/','/pdb1/');
ALTER PLUGGABLE DATABASE pdb1 OPEN;

User profile

A profile is a named set of resource limits and password parameters that can be assigned to database users. It lets the DBA control resource usage (CPU, sessions, idle time) and enforce password policy (expiry, reuse, failed login attempts, complexity). Every user is assigned a profile; if none is given, the DEFAULT profile applies.

Commands

1. Create the profile:

CREATE PROFILE clerk_profile LIMIT
  SESSIONS_PER_USER          2
  CPU_PER_SESSION            UNLIMITED
  IDLE_TIME                  30
  CONNECT_TIME               480
  FAILED_LOGIN_ATTEMPTS      3
  PASSWORD_LIFE_TIME         60
  PASSWORD_REUSE_TIME        180
  PASSWORD_LOCK_TIME         1;

2. Assign the profile when creating a user:

CREATE USER ram IDENTIFIED BY ram123
  PROFILE clerk_profile;

3. Assign it to an existing user:

ALTER USER ram PROFILE clerk_profile;

4. Modify or drop:

ALTER PROFILE clerk_profile LIMIT SESSIONS_PER_USER 3;
DROP PROFILE clerk_profile CASCADE;

5. Verify:

SELECT username, profile FROM dba_users WHERE username='RAM';

Resource limits are enforced only if RESOURCE_LIMIT=TRUE is set (ALTER SYSTEM SET resource_limit=TRUE;); password limits are always enforced.

Job scheduling in Oracle

Job scheduling is the automatic execution of tasks (PL/SQL blocks, stored procedures, executable scripts, backups) at a specified time or when a specified event happens, without manual intervention. Oracle provides it through the DBMS_SCHEDULER package (replacing the older DBMS_JOB). Its components are the job (what to run), schedule (when), program (the action), and optionally job classes, windows and chains.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'nightly_stats',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''HR''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled         => TRUE);
END;
/

Difference between time-based and event-based schedule

An event-based job example:

DBMS_SCHEDULER.CREATE_JOB(
  job_name => 'load_on_file', job_type => 'STORED_PROCEDURE',
  job_action => 'load_proc',
  event_condition => 'tab.user_data.event_name = ''FILE_ARRIVED''',
  queue_spec => 'event_q', enabled => TRUE);

Database management system (DBMS)

A DBMS is software that lets users define, create, store, retrieve, update and secure data in a database, providing data independence, integrity, concurrency control, backup/recovery and security. Oracle Database is a relational DBMS (RDBMS).

Oracle architecture

An Oracle server consists of an instance (memory structures + background processes) and a database (physical files).

 User process --> Server process (PGA)
                       |
 +---------------- INSTANCE ----------------+
 | SGA: buffer cache | shared pool | redo log buffer | large/java pools |
 | Background: DBWn  LGWR  CKPT  SMON  PMON  ARCn  |
 +------------------------------------------+
                       |
 +---------------- DATABASE ----------------+
 | Datafiles | Control files | Online redo logs | (archived logs, SPFILE, password file) |
 +------------------------------------------+

1. Memory structures

• SGA (System Global Area): shared by all processes. Components: database buffer cache (cached data blocks), shared pool (library cache and data dictionary cache), redo log buffer (change records), large pool, Java pool, streams pool.
• PGA (Program Global Area): private memory for each server process, holding sort areas, session variables and cursor state.

2. Processes

• User process: the client application that requests a connection.
• Server process: executes SQL on behalf of the user and reads data into the buffer cache.
• Background processes:
  • DBWn writes dirty buffers to datafiles.
  • LGWR writes the redo buffer to online redo logs (at commit, every 3 seconds, or when one-third full).
  • CKPT signals checkpoints and updates headers of datafiles and control files.
  • SMON performs instance recovery and cleans temporary segments.
  • PMON cleans up failed processes and releases their locks.
  • ARCn copies filled redo logs to archive storage (ARCHIVELOG mode).
  • LREG registers services with the listener.

3. Files

• Datafiles: store actual data (tables, indexes) in tablespaces.
• Control file: binary file holding the database name, structure, file locations and checkpoint/SCN information; needed to mount the database.
• Online redo log files: record all changes for recovery.
• Archived redo logs: copies of filled redo logs.
• Parameter files (PFILE/SPFILE), password file, alert and trace logs, backup files.

Logical structure: database - tablespaces - segments - extents - data blocks.

Flow: A user's SQL is sent to the server process, which parses it in the shared pool, reads blocks into the buffer cache, changes them and generates redo. LGWR writes redo at commit; DBWn later writes changed blocks to datafiles.

Backup, restore and recovery

• Backup: making a copy of data (datafiles, control files, archived logs, SPFILE) that can be used to reconstruct the data after failure.
• Restore: copying the backup files back to disk (to their original or new location).
• Recovery: applying redo (archived and online redo logs) to the restored files to bring the database to a consistent, current state or to a chosen point in time.

Types of backup

• Physical backup: copies of files (RMAN or OS copy). Logical backup: export of objects and data (Data Pump).
• Consistent (cold) backup: taken with the database shut down. Inconsistent (hot) backup: taken while the database is open; requires ARCHIVELOG mode.
• Full, incremental (level 0 and level 1; differential or cumulative), image copy and backup set.

Types of recovery

• Complete recovery: restore the lost file and apply all redo; no data loss.
• Incomplete (point-in-time) recovery: recover until a time, SCN or log sequence (UNTIL TIME), used after logical errors; open with RESETLOGS.
• Instance recovery: automatic by SMON after a crash. Media recovery: after loss of files.

RMAN examples

rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;                  # full backup
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;              # base
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;              # changes since level 0
RMAN> BACKUP TABLESPACE users;
RMAN> BACKUP AS COPY DATAFILE 4;
RMAN> LIST BACKUP SUMMARY;

Complete recovery of a lost datafile:

RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
RMAN> RESTORE DATAFILE 4;
RMAN> RECOVER DATAFILE 4;
RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';

Whole database point-in-time recovery:

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

Data Pump (logical backup and restore)

Create a directory object first:

CREATE DIRECTORY dp_dir AS '/u01/dpump';
GRANT READ, WRITE ON DIRECTORY dp_dir TO hr;

Export and import:

expdp hr/hr DIRECTORY=dp_dir DUMPFILE=hr.dmp LOGFILE=hr_exp.log SCHEMAS=hr
expdp system/pwd DIRECTORY=dp_dir DUMPFILE=full.dmp FULL=Y
expdp hr/hr DIRECTORY=dp_dir DUMPFILE=emp.dmp TABLES=employees

impdp hr/hr DIRECTORY=dp_dir DUMPFILE=hr.dmp LOGFILE=hr_imp.log SCHEMAS=hr
impdp hr/hr DIRECTORY=dp_dir DUMPFILE=emp.dmp TABLES=employees TABLE_EXISTS_ACTION=REPLACE

Comparison: RMAN gives physical, block-level, incremental backups and complete or point-in-time recovery of the whole database; Data Pump is best for moving or restoring individual schemas and tables, and does not support applying redo.

(i) Create table emp

CREATE TABLE emp (
  emp_id  NUMBER(5),
  name    VARCHAR2(30),
  address VARCHAR2(50),
  dob     DATE,
  salary  NUMBER(10,2)
);

(ii) Add primary key

ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY (emp_id);

(iii) Check constraint on salary (> 10000)

ALTER TABLE emp ADD CONSTRAINT ck_emp_salary CHECK (salary > 10000);

(iv) Rename the table

RENAME emp TO employee;
-- or: ALTER TABLE emp RENAME TO employee;

(v) Insert rows and delete one record

INSERT INTO employee VALUES (1, 'Ram Sharma', 'Kathmandu', DATE '1998-05-12', 25000);
INSERT INTO employee VALUES (2, 'Sita Thapa', 'Pokhara', DATE '2000-09-30', 32000);
INSERT INTO employee VALUES (3, 'Hari Karki', 'Butwal', DATE '1995-01-20', 18000);
COMMIT;

DELETE FROM employee WHERE emp_id = 3;
COMMIT;

(vi) Logical backup using expdp

A directory object is needed:

CREATE DIRECTORY dp_dir AS '/u01/backup';
GRANT READ, WRITE ON DIRECTORY dp_dir TO hr;

Then run at the OS prompt (the owner schema is hr here):

expdp hr/hr TABLES=employee DIRECTORY=dp_dir DUMPFILE=employee.dmp LOGFILE=employee_exp.log

(vii) Give select and update privileges to User1

GRANT SELECT, UPDATE ON employee TO user1;

(viii) Add department_id as a foreign key referencing department

Assuming department(department_id) is its primary key:

ALTER TABLE employee ADD (department_id NUMBER(5));
ALTER TABLE employee ADD CONSTRAINT fk_emp_dept
  FOREIGN KEY (department_id) REFERENCES department(department_id);

(ix) Increase the length of the name field

ALTER TABLE employee MODIFY (name VARCHAR2(60));

(x) Rename the name field to ename

ALTER TABLE employee RENAME COLUMN name TO ename;

Partitioning

Partitioning is dividing a large table or index into smaller, more manageable pieces called partitions, each stored as a separate segment (and possibly in a different tablespace), while still appearing to applications as a single table. A partition key column(s) decides which partition a row goes to.

Benefits: better performance (partition pruning scans only the relevant partitions), easier administration (backup, load or drop a single partition), higher availability (other partitions remain usable if one is offline) and parallel processing.

Methods of table partitioning

1. Range partitioning: Rows are placed by ranges of the partition key (often dates).

CREATE TABLE sales (id NUMBER, sale_date DATE, amount NUMBER)
PARTITION BY RANGE (sale_date) (
  PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
  PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION pmax  VALUES LESS THAN (MAXVALUE));

2. List partitioning: Rows are placed by explicit lists of key values.

CREATE TABLE customer (id NUMBER, region VARCHAR2(10))
PARTITION BY LIST (region) (
  PARTITION p_east VALUES ('EAST'),
  PARTITION p_west VALUES ('WEST'),
  PARTITION p_other VALUES (DEFAULT));

3. Hash partitioning: A hash of the key spreads rows evenly across a fixed number of partitions; used when there is no natural range or list.

CREATE TABLE orders (id NUMBER, cust NUMBER)
PARTITION BY HASH (id) PARTITIONS 4;

4. Composite partitioning: Partition then subpartition, e.g. range-hash, range-list, list-hash.

PARTITION BY RANGE (sale_date) SUBPARTITION BY HASH (id) SUBPARTITIONS 4 (...)

5. Other methods: interval partitioning (range partitions created automatically as data arrives), reference partitioning (child table follows parent's partitioning via foreign key), and system partitioning.

Defining a backup policy as a DBA

A backup policy is a documented plan stating what is backed up, how, how often, where it is stored, for how long, and how it is tested. It is designed to meet the business requirements for data loss and downtime.

Steps to define it

1. Assess business requirements: Identify critical databases and data and set the RPO (Recovery Point Objective: acceptable data loss) and RTO (Recovery Time Objective: acceptable downtime). Consider legal retention rules.
2. Choose the database mode: Use ARCHIVELOG mode for production so that hot backups and point-in-time recovery are possible; NOARCHIVELOG only for non-critical data.
3. Select the backup types and tools: Use RMAN for physical backups (full or level 0 weekly, incremental level 1 daily, archived logs backup every few hours) and Data Pump for logical backups of schemas and tables. Back up control file and SPFILE automatically (CONFIGURE CONTROLFILE AUTOBACKUP ON).
4. Set the schedule: Run backups in low-load periods (for example, a full backup on Sunday night, incrementals on other nights), automated with the scheduler or cron.
5. Define storage and location: Keep backups on disk (fast recovery area) and a copy on tape or cloud, with at least one copy offsite for disaster protection; encrypt and compress where needed.
6. Define the retention policy: For example, CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS; and delete obsolete backups.
7. Test and validate: Regularly run RESTORE ... VALIDATE, CROSSCHECK and full recovery drills to confirm backups are usable.
8. Monitor and report: Check logs and alerts for failures, and notify the DBA team.
9. Secure and document: Restrict access to backups, assign responsibilities, document procedures and review the policy when the system or requirements change.

Indexing is the creation of a separate database structure (usually a B-tree) that stores the values of one or more columns together with the ROWID of the matching rows. Like the index of a book, it lets Oracle find rows without scanning the whole table.

Why it is important

• It greatly speeds up SELECT queries with WHERE conditions, joins and ORDER BY, by avoiding full table scans.
• It reduces disk I/O and response time on large tables.
• Unique indexes enforce uniqueness (the primary key and unique constraints use them).
• The cost is extra storage and slower INSERT, UPDATE and DELETE, because the index must be maintained.

Guidelines for creating indexes

1. Index columns that are frequently used in WHERE clauses and join conditions, and foreign key columns.
2. Index columns with high selectivity (many distinct values). Use bitmap indexes for low-cardinality columns in read-mostly (data warehouse) tables.
3. Create indexes after loading bulk data, not before, to make the load faster.
4. Do not index small tables, or tables that are updated much more than they are queried.
5. Do not over-index. Each extra index slows DML and uses space.
6. For multi-column (composite) indexes, put the most frequently used and most selective column first.
7. Place indexes in a separate tablespace from the table, preferably on a different disk, to reduce I/O contention.
8. Use function-based indexes when queries filter on an expression such as UPPER(name).
9. Monitor and rebuild or drop indexes that are unused or badly fragmented.

Example:

CREATE INDEX idx_emp_name ON employees(last_name) TABLESPACE idx_ts;

Oracle multitenant architecture (introduced in 12c) lets one Oracle database hold many separate databases. The container database (CDB) provides the shared Oracle instance and infrastructure, and each pluggable database (PDB) looks to applications like an independent database.

Container Database (CDB)

• A CDB is the actual database that has one set of background processes and memory (a single instance), plus redo logs, control files, undo (in the default setup) and the common data dictionary.
• It contains the root container (CDB$ROOT)**, the **seed PDB (PDB$SEED) and zero or more user-created PDBs.
• CDB$ROOT stores Oracle-supplied metadata and common users. The seed is a read-only template used to create new PDBs quickly.

Pluggable Database (PDB)

• A PDB is a portable collection of schemas, objects and data that has its own tablespaces, datafiles, local users and local dictionary.
• Applications connect to a PDB through a service name, and it behaves like a normal non-CDB database.
• A PDB can be created from the seed, cloned from another PDB, unplugged from one CDB and plugged into another, which makes moving and upgrading easy.

Features and benefits

• Many databases share one instance, so memory and background processes are used efficiently (consolidation, lower cost).
• Patching and upgrading is done once at the CDB level.
• Each PDB is isolated: its users and data are separate, and resource limits can be set per PDB using Resource Manager.
• Quick provisioning by cloning, and easy administration since backups can be taken for the whole CDB or for one PDB.

Users are of two kinds: common users (names start with C##, exist in all containers) and local users (exist in one PDB only).

Database administration is the work of installing, configuring, securing, monitoring, maintaining and recovering a database system so that it is available, reliable and performs well. The person who does it is the Database Administrator (DBA).

Roles and responsibilities of a DBA

1. Installation and configuration: install Oracle software, create databases, configure the instance and network (listener, tnsnames).
2. Storage management: create and manage tablespaces, datafiles, redo logs and control files, and plan space growth.
3. Security management: create users, roles and profiles, grant and revoke privileges, and set up auditing and password policies.
4. Backup and recovery: design a backup strategy (RMAN, archiving), test it, and recover the database after failure.
5. Performance tuning: monitor memory, I/O and SQL, create indexes, tune the instance and queries.
6. Monitoring and troubleshooting: watch alert logs, sessions, locks and space; solve problems quickly.
7. Database design support: work with developers and analysts on schema design, constraints and data integrity.
8. Patching and upgrades: apply patches and migrate to new versions with minimum downtime.
9. High availability and disaster recovery: set up standby databases (Data Guard), clustering and failover plans.
10. Documentation and user support: keep records of the configuration and procedures, and train and assist users.
11. Capacity planning and data movement: import/export, data loading and migration between databases.

Oracle Net Services is the set of software components that allows client applications to connect to an Oracle database over a network, and allows databases to communicate with each other. Its main components are:

1. Oracle Net Listener: A server-side process (lsnrctl, configured in listener.ora) that listens on a port (default 1521) for incoming connection requests and hands each request to the database instance (or a dispatcher). It can be started, stopped and checked with lsnrctl start|stop|status.
2. Service names / net service names: A logical name (e.g. ORCL) that clients use instead of host, port and SID. It is resolved to a connect descriptor containing the protocol, host, port and service name.
3. Naming methods (name resolution): The way a service name is converted to a connect descriptor: local naming (tnsnames.ora), easy connect (host:port/service), directory naming (LDAP) and external naming.
4. Configuration files: listener.ora (server listener settings), tnsnames.ora (client service names), sqlnet.ora (client/server parameters such as naming order, encryption, timeouts).
5. Oracle Net foundation layer: Establishes and maintains the connection between client and server and handles data exchange.
6. Protocol support layer: Maps Oracle Net to network protocols such as TCP/IP and TCP/IP with SSL.
7. Connection Manager (CMAN): Optional proxy that provides connection concentration, multiplexing and access control.
8. Administration tools: Oracle Net Configuration Assistant (NETCA), Oracle Net Manager and the lsnrctl and tnsping utilities.

Connection flow: the client resolves the service name, contacts the listener, the listener spawns or assigns a server process, and the client then talks directly to that server process.

Database recovery is the process of restoring a database to a consistent, correct state after a failure, using backups, redo logs and archived redo logs, so that no committed data is lost.

Situations when recovery is needed

• Instance failure (power loss, OS crash, SHUTDOWN ABORT).
• Media failure: a datafile, control file, redo log or whole disk is lost or corrupted.
• User error: tables dropped or wrong data deleted or updated.
• Statement or process failure that leaves incomplete transactions.
• Loss of the whole server or site (disaster), or corruption of blocks.

Crash recovery vs media recovery

Steps in media recovery (datafile lost, database in ARCHIVELOG mode)

1. Identify the failed file (alert log, V$RECOVER_FILE).
2. Take the affected datafile or tablespace offline (or mount the database if it is a system file).
3. Restore the file from the latest backup:

RMAN> RESTORE DATAFILE 4;

4. Apply the archived and online redo to bring it up to date:

RMAN> RECOVER DATAFILE 4;

5. Bring the file or tablespace back online (ALTER DATABASE DATAFILE 4 ONLINE;) or open the database (ALTER DATABASE OPEN;).
6. Take a fresh backup and verify the data.

For incomplete recovery (until a time or SCN) the database is opened with RESETLOGS.

Memory structure of an Oracle database

Oracle uses memory in two main structures: the System Global Area (SGA), which is shared, and the Program Global Area (PGA), which is private to each server process.

Oracle instance

An instance is the combination of the SGA and the background processes (PMON, SMON, DBWn, LGWR, CKPT, ARCn etc.). It is created when the database is started (STARTUP NOMOUNT) and it manages the physical database files. One instance opens one database (except in RAC).

Components of the SGA

1. Database buffer cache: holds copies of data blocks read from the datafiles. Frequently used blocks stay in memory, which reduces disk I/O. DBWn writes dirty blocks back.
2. Shared pool: contains the library cache (parsed SQL and PL/SQL), data dictionary cache (metadata), and the result cache.
3. Redo log buffer: a circular buffer holding redo entries for changes before LGWR writes them to the online redo logs.
4. Large pool: optional area for backup/restore (RMAN), shared server session memory and parallel execution.
5. Java pool: memory for Java code running in the database.
6. Streams pool: used by Oracle Streams / GoldenGate.
7. Fixed SGA: small area holding internal housekeeping information.

PGA vs SGA

Privilege

A privilege is a right to execute a particular SQL statement or to access another user's object. Privileges are required so that the DBA can control who may do what, protecting data security and integrity (principle of least privilege).

System privileges give the right to perform actions on the database as a whole, for example:

• CREATE SESSION (log in), CREATE USER, ALTER USER, DROP USER
• CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE ANY TABLE
• ALTER SYSTEM, ALTER DATABASE, CREATE TABLESPACE
• SYSDBA and SYSOPER (administrative privileges)

Object privileges give the right to operate on a specific object (table, view, sequence, procedure):

• SELECT, INSERT, UPDATE, DELETE on tables
• EXECUTE on procedures and functions
• ALTER, INDEX, REFERENCES

They are important because they control access to sensitive data and let the DBA limit users to only the operations they need.

Commands

(a) Common user and local user

-- common user (connected to CDB$ROOT)
CREATE USER c##ram IDENTIFIED BY ram123 CONTAINER=ALL;
-- local user (connected to a PDB)
ALTER SESSION SET CONTAINER = pdb1;
CREATE USER sita IDENTIFIED BY sita123;

(b) Role with two system privileges, assigned to a user

CREATE ROLE app_role;
GRANT CREATE SESSION, CREATE TABLE TO app_role;
GRANT app_role TO sita;

(c) Unlimited space on USERS tablespace

ALTER USER sita QUOTA UNLIMITED ON users;

(d) Profile

CREATE PROFILE my_prof LIMIT
  FAILED_LOGIN_ATTEMPTS 2
  IDLE_TIME 5
  SESSIONS_PER_USER 3;

(e) Assign the profile to the user

ALTER USER sita PROFILE my_prof;

Data files

A data file is a physical file on disk (or ASM) that stores the actual data of the database: tables, indexes, undo and the data dictionary. Each datafile belongs to exactly one tablespace and one database.

Relation with tablespace

• A tablespace is a logical storage unit. It is made up of one or more datafiles, which are its physical storage.
• Segments (tables, indexes) are stored in a tablespace, and Oracle spreads their extents over the datafiles of that tablespace.
• One tablespace can have many datafiles, but one datafile can belong to only one tablespace.
• A tablespace's size is the total size of its datafiles; to enlarge it the DBA adds a datafile or resizes/auto-extends an existing one.

Command to add a datafile to an existing tablespace

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

For a temporary tablespace, use ALTER TABLESPACE temp ADD TEMPFILE '...' SIZE 100M;. The files can be checked with SELECT file_name, tablespace_name FROM dba_data_files;.

Oracle Net Services lets a client find and connect to an Oracle database through a naming method. The main methods of network configuration are:

1. Easy Connect (EZCONNECT): No configuration file is needed. The client gives host, port and service name directly.

sqlplus scott/tiger@//dbserver:1521/orcl

It is simple and good for small setups, but it is limited and hard to change when many clients use it.

2. Local naming (tnsnames.ora): A service name is defined in the client's tnsnames.ora file and mapped to a connect descriptor.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = orcl)))

Then use sqlplus scott/tiger@ORCL. It is the most common method, but the file must be maintained on every client.

3. Directory naming (LDAP): Service names are stored centrally in an LDAP directory (Oracle Internet Directory, Active Directory). It gives central administration for many clients.

4. External naming: Uses third-party naming services such as NIS or CDS.

Server side: the listener is configured in listener.ora (using NETCA, Net Manager or by editing the file), then started with lsnrctl start. The database registers its services with the listener (dynamic registration), and the connection is tested with tnsping orcl. The order in which the naming methods are tried is set in sqlnet.ora by NAMES.DIRECTORY_PATH.

A privilege is a right to execute a SQL statement or to access an object. Oracle has two kinds.

1. System privileges: the right to perform an action on the database or a class of objects, for example CREATE SESSION, CREATE TABLE, CREATE USER, ALTER SYSTEM, DROP ANY TABLE.

2. Object privileges: the right to perform an action on one specific object, for example SELECT, INSERT, UPDATE, DELETE on a table, EXECUTE on a procedure, REFERENCES and ALTER.

Privileges can be given to users directly or through roles, which are named groups of privileges that are easier to manage.

Granting privileges (GRANT)

GRANT CREATE SESSION, CREATE TABLE TO ram;
GRANT SELECT, INSERT ON hr.employees TO ram;
GRANT UPDATE ON hr.employees TO ram WITH GRANT OPTION;
GRANT CREATE SESSION TO ram WITH ADMIN OPTION;

WITH GRANT OPTION (object privileges) and WITH ADMIN OPTION (system privileges) allow the grantee to pass the privilege on to others.

Revoking privileges (REVOKE)

REVOKE CREATE TABLE FROM ram;
REVOKE INSERT ON hr.employees FROM ram;

Revoking a system privilege does not cascade to what the grantee has granted to others, while revoking an object privilege granted with WITH GRANT OPTION does cascade.

Privileges can be checked using the views DBA_SYS_PRIVS, DBA_TAB_PRIVS and DBA_ROLE_PRIVS.

Profile

A profile is a named set of resource limits and password rules in Oracle that can be assigned to database users. Every user has a profile; if none is assigned, the DEFAULT profile applies.

Importance

• Controls system resources used by each user (CPU per session, sessions per user, idle time, connect time, logical reads), so one user cannot degrade the system.
• Enforces password policy: failed login attempts, password lifetime, reuse rules, lock time, complexity function.
• Improves security and is easy to manage, since changing the profile changes all users who have it.

Main parameters

Resource: SESSIONS_PER_USER, CPU_PER_SESSION, CONNECT_TIME, IDLE_TIME, LOGICAL_READS_PER_SESSION. Password: FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_REUSE_TIME, PASSWORD_LOCK_TIME, PASSWORD_GRACE_TIME.

Example

CREATE PROFILE student_prof LIMIT
  SESSIONS_PER_USER 2
  IDLE_TIME 10
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LIFE_TIME 60
  PASSWORD_LOCK_TIME 1;

ALTER USER ram PROFILE student_prof;

Here ram can have at most 2 sessions, is disconnected after 10 idle minutes, and the account is locked for 1 day after 3 wrong passwords. Resource limits are enforced only when RESOURCE_LIMIT=TRUE is set. A profile is removed with DROP PROFILE student_prof CASCADE;.

Relationship between PDBs and CDB

In the multitenant architecture, the container database (CDB) is the single database that contains the root container CDB$ROOT, the seed PDB$SEED and one or more pluggable databases (PDBs). A PDB is a self-contained set of schemas and data that is plugged into the CDB. Each PDB is a container inside the CDB, so a CDB is the "parent" which hosts and manages its PDBs, and PDBs can be created, cloned, opened, closed, unplugged and plugged into another CDB.

To applications and users, each PDB looks like a normal separate database with its own service name, local users, tablespaces and datafiles.

Things shared between the CDB and its PDBs

• The same Oracle instance: one SGA and one set of background processes.
• The same redo log files and control files (one set for the CDB).
• The undo tablespace (in shared undo mode, the default) and the temporary tablespace can be shared (a PDB may also have its own temp tablespace).
• The common data dictionary metadata for Oracle-supplied objects, stored in the root and referenced by every PDB.
• Common users and roles (names with C##) defined in the root are visible in all PDBs.
• The same Oracle software, version and patch level, and the same character set by default.
• The same server resources, divided among PDBs by Resource Manager.

Not shared: the PDB's own application data, local users, local tablespaces and datafiles, and local privileges, which keeps the data of each PDB isolated.

Sequence

A sequence is a schema object that generates a series of unique numeric values automatically. It is mostly used to generate primary key values, so that no application code has to compute the next number and several users can insert at the same time without getting duplicates.

Uses

• Auto-generated primary keys (employee id, order number).
• Safe for concurrent users because Oracle serialises number generation.
• Values are stored in memory (the CACHE option), which makes access fast. Gaps may appear after rollbacks or a restart.

Syntax and example

CREATE SEQUENCE emp_seq
  START WITH 1
  INCREMENT BY 1
  MAXVALUE 99999
  NOCYCLE
  CACHE 20;

Using it:

INSERT INTO employee(emp_id, name)
VALUES (emp_seq.NEXTVAL, 'Ram');

SELECT emp_seq.CURRVAL FROM dual;

• NEXTVAL returns the next value and increments the sequence.
• CURRVAL returns the current value for the session (only after NEXTVAL has been called).

Modify with ALTER SEQUENCE emp_seq INCREMENT BY 5; and remove with DROP SEQUENCE emp_seq;.

Backup, restore and recovery

• Backup is a copy of database files (datafiles, control file, archived redo logs, SPFILE) kept in a safe place to protect against data loss.
• Restore is copying the files back from the backup to the disk after loss or damage.
• Recovery is applying redo (archived and online) to the restored files to bring the database up to date, or to a chosen point in time, and making it consistent.

Backups can be physical (RMAN, user-managed copies) or logical (Data Pump export), full or incremental, hot (online, needs ARCHIVELOG mode) or cold (offline, consistent).

Backup and recovery strategy as a DBA

1. Run the production database in ARCHIVELOG mode so that every change can be recovered.
2. Use RMAN: a full (level 0) backup weekly, a cumulative or differential incremental (level 1) backup daily, and archived redo log backups every few hours.
3. Multiplex control files and redo logs, and enable control file autobackup.
4. Keep backups on disk (fast recovery area) and a copy on tape or at an offsite/cloud location; define a retention policy (for example recovery window of 14 days).
5. Use Data Pump for logical backup of schemas, and Data Guard standby for disaster recovery.
6. Regularly validate backups (RESTORE VALIDATE, BACKUP VALIDATE) and test recovery procedures; document them.
7. Decide the RTO and RPO with the business and choose frequency accordingly.

Recovery for different types of failure

Process of media recovery

1. Identify the failed file (alert log, V$RECOVER_FILE).
2. Take the file or tablespace offline, or mount the database if it is a critical file.
3. Restore the file from the latest backup: RESTORE DATAFILE n;
4. Recover it by applying archived and online redo: RECOVER DATAFILE n;
5. Bring it online or open the database: ALTER DATABASE OPEN;
6. Take a new backup and check the data.

Complete recovery applies all redo; incomplete (point-in-time) recovery uses RECOVER DATABASE UNTIL TIME ... followed by OPEN RESETLOGS.

An Oracle database system consists of the database (physical files) and the instance (memory and processes). Its architecture has three parts: memory, process and storage structures.

1. Memory structures

SGA (System Global Area) is shared memory allocated when the instance starts. It contains:

• Database buffer cache: caches data blocks read from datafiles.
• Shared pool: library cache (parsed SQL/PLSQL) and data dictionary cache.
• Redo log buffer: stores redo entries before they are written to the redo logs.
• Large pool, Java pool, Streams pool: optional areas for backup, shared server, Java and replication.

PGA (Program Global Area) is private memory for each server process, holding the sort area, session information and cursor state.

2. Process structures

• User process: the client application (SQL*Plus, application) that requests work.
• Server process: parses and executes SQL on behalf of the user process and returns the results.
• Background processes:
  • DBWn writes dirty buffers from the cache to datafiles.
  • LGWR writes redo buffer entries to the online redo logs (at commit).
  • CKPT signals checkpoints and updates headers of datafiles and control files.
  • SMON performs instance recovery at startup and cleans temporary segments.
  • PMON cleans up failed user processes and releases locks and resources.
  • ARCn copies filled online redo logs to archive destination (ARCHIVELOG mode).
  • LREG, MMON, MMNL and others handle listener registration and statistics.

3. Storage structures

Logical structures: database, tablespaces, segments, extents and data blocks. A tablespace contains segments (table, index, undo, temporary), a segment consists of extents, and an extent of contiguous data blocks.

Physical structures (files):

• Datafiles: hold the data and are linked to tablespaces.
• Control file: holds the database name, structure, file locations, SCN and checkpoint information.
• Online redo log files: record all changes for recovery.
• Archived redo logs: copies of filled redo logs.
• Parameter file (SPFILE/PFILE), password file, alert and trace files, backup files.

Working: a user connects through the listener, a server process is assigned, SQL is parsed in the shared pool, data blocks are read into the buffer cache, changes are recorded in the redo buffer, LGWR writes redo at commit, and DBWn later writes the changed blocks to the datafiles.

Scheduler

The Oracle Scheduler (DBMS_SCHEDULER) is a built-in feature of the database that lets the DBA create, run and manage jobs automatically at a given time or when an event occurs. A job can be a PL/SQL block, a stored procedure, or an external script/executable.

Why it is important

• Automates routine tasks such as backups, statistics gathering, data loading, purging and reports, which removes manual work and human errors.
• Runs jobs inside the database, with logging, monitoring and error reporting.
• Handles dependencies, priorities and resource allocation (through job classes and Resource Manager).
• Supports complex calendars, chains of jobs, and jobs on remote systems.

Components and workflow

1. Job: the unit of work: what to run (action) and when (schedule). Created with DBMS_SCHEDULER.CREATE_JOB.
2. Program: a reusable definition of what is to be executed (the action and its arguments), so many jobs can share it.
3. Schedule: defines when and how often a job runs, using a start time, repeat interval and end date (calendar expression like FREQ=DAILY;BYHOUR=2).
4. Job class: groups jobs to assign common attributes such as resource consumer group, logging level and service.
5. Window: a time interval with an associated resource plan during which jobs run with specific resource allocation.
6. Chain: a set of steps (programs) linked by rules so that jobs run in order or depend on the success of earlier steps.
7. Event: a signal that starts a job.

Workflow: create a program and a schedule, create a job that references them, enable it, the job coordinator process (CJQ0) picks it when it is due, a job slave process (J00n) executes it, and the status, run time and errors are logged in DBA_SCHEDULER_JOB_RUN_DETAILS.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'NIGHTLY_STATS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''HR''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=2',
    enabled         => TRUE);
END;
/

Time-based vs event-based scheduling