Database Administration

An Oracle database is the set of physical files on disk (datafiles, control files, redo log files) that store the data. An Oracle instance is the set of memory structures (SGA) and background processes that run on the server to access and manage that database. An instance opens one database (two or more in RAC).

Initialization parameter files (PFILE, a text file, or SPFILE, a binary server parameter file) hold the configuration settings the instance reads at startup, such as memory sizes, control file locations, db_name, processes and undo settings. They define how the instance is built and run.

A tablespace is a logical storage unit made of one or more physical datafiles. Database objects such as tables and indexes are stored in segments of a tablespace, and Oracle allocates their extents from the free space in the tablespace's datafiles. Adding a datafile increases the tablespace capacity.

The control file records the database structure (datafile and redo log locations, checkpoint and SCN information), and the database cannot be mounted without it. Multiplexing keeps identical copies on different disks, so the loss or corruption of one copy does not stop the database or force recovery, which improves reliability.

A schema-only account owns objects (tables, views and so on) but has no password and cannot log in (CREATE USER app_owner NO AUTHENTICATION). Other users access its objects through granted privileges, which improves security. A regular user account has credentials, can connect and open sessions, and is granted privileges to work directly.

The Oracle Net Listener is a process that listens on a port (default 1521) for incoming client connection requests. It receives the request, finds the requested service, and either hands the connection to a dedicated server process (or dispatcher in shared server mode) or spawns one. After that the client communicates directly with the server process.

Undo retention is the minimum time (in seconds, set by the UNDO_RETENTION parameter) that Oracle tries to keep committed undo data before it can be overwritten. It supports flashback queries and read consistency for long-running queries.

A flashback query uses undo data to return rows as they were at an earlier time or SCN, without restoring a backup. For example:

SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

It lets users see previous states of data and recover from accidental changes, within the undo retention period.

EXPLAIN PLAN shows the execution plan the optimizer chooses for a SQL statement (access paths, join methods, order of operations, estimated cost and rows) without running it. It is used in SQL tuning to find full table scans or poor joins. Use EXPLAIN PLAN FOR <sql>; and then SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);.

Oracle Resource Manager controls how CPU, I/O, parallelism and sessions are shared among users and workloads. Sessions are mapped to consumer groups, and resource plans with directives set priorities and limits. This stops heavy sessions from starving important ones, and it can also limit runaway queries.

Oracle uses a multiprocess architecture with three kinds of processes:

• Client (user) processes: run the application or tool that requests database work.
• Server processes: act on behalf of a client by parsing and executing SQL and returning results. They are dedicated (one per session) or shared.
• Background processes: started with the instance to perform maintenance. Key ones are DBWn (writes dirty buffers to datafiles), LGWR (writes the redo log buffer to redo logs), CKPT (signals checkpoints and updates headers), SMON (instance recovery and space cleanup), PMON (cleans up failed processes) and ARCn (archives redo logs).

SQL to display running background processes:

SELECT pname, spid FROM v$process WHERE background = 1;

Role of LGWR: it writes redo entries from the redo log buffer to the online redo log files at commit and at other triggers, so committed changes can be recovered after a failure.

Control files

SELECT name FROM v$controlfile;
SELECT * FROM v$controlfile_record_section;
SHOW PARAMETER control_files;

Redo log files

SELECT group#, thread#, sequence#, bytes/1024/1024 AS mb, members, status FROM v$log;
SELECT group#, member, type FROM v$logfile;

Archive logs

ARCHIVE LOG LIST;
SELECT log_mode FROM v$database;
SELECT name, sequence#, first_time FROM v$archived_log;
SHOW PARAMETER log_archive_dest;

Authentication verifies a user's identity before a session is created. Oracle supports:

1. Database authentication: the user name and password are stored (hashed) in the database. CREATE USER scott IDENTIFIED BY tiger;
2. Operating system authentication: the OS login is trusted (IDENTIFIED EXTERNALLY, or CONNECT / AS SYSDBA for OS groups such as dba).
3. Network authentication: third-party services such as Kerberos, RADIUS, SSL/TLS certificates, and PKI.
4. Global (enterprise) authentication: users are managed centrally in a directory such as Oracle Internet Directory or Active Directory (IDENTIFIED GLOBALLY).
5. Proxy authentication: a middle-tier application connects on behalf of a user.
6. Password file authentication: used for administrators such as SYSDBA when the database is not open.

Passwords are further protected with profiles (complexity, expiry, lockout).

Database auditing records database actions (logons, DDL, access to sensitive tables, privilege use) to monitor security, support accountability, and meet compliance needs. It detects misuse and investigates suspicious activity.

Unified audit trail (Oracle 12c onwards) gathers audit records from all sources (standard, fine-grained, SYS operations, RMAN, Data Pump and others) into one trail, viewed through UNIFIED_AUDIT_TRAIL. Audit policies are created with CREATE AUDIT POLICY and turned on with AUDIT POLICY:

CREATE AUDIT POLICY emp_pol ACTIONS SELECT, UPDATE ON hr.employees;
AUDIT POLICY emp_pol;
SELECT dbusername, action_name, event_timestamp FROM unified_audit_trail;

Benefits are a single place to look, better performance (records are written to a secure read-only table), and simpler management.

A naming method resolves a connect identifier into a connect descriptor (host, port, service name).

1. Easy Connect: no configuration file; the full address is given in the command. sqlplus hr/hr@//dbhost:1521/orclpdb
2. Local naming (tnsnames.ora): aliases are stored on the client.

   ORCL =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
       (CONNECT_DATA = (SERVICE_NAME = orclpdb)))
   

   sqlplus hr/hr@ORCL
3. Directory naming: the connect identifiers are stored centrally in an LDAP directory (OID, Active Directory), so changes are made once.
4. External naming: uses a third-party naming service such as NIS or DCE CDS.

The method order is set in sqlnet.ora through NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT).

Data Pump is Oracle's server-based, high-speed utility for moving data and metadata. It replaces the old exp and imp.

• Export (expdp): writes database objects and data into dump files in a directory object on the server.
• Import (impdp): reads those dump files and loads them into the same or another database.

Setup and examples:

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

expdp hr/hr DIRECTORY=dp_dir DUMPFILE=hr.dmp LOGFILE=hr_exp.log SCHEMAS=hr
impdp hr/hr DIRECTORY=dp_dir DUMPFILE=hr.dmp LOGFILE=hr_imp.log REMAP_SCHEMA=hr:hr_test

Modes include full, schema, table and tablespace. Features include parallelism (PARALLEL), REMAP_* options, INCLUDE/EXCLUDE filters, QUERY filters, and restartable jobs. It is used for backups of logical data, migration and cloning.

Oracle memory is made up of the SGA, the PGA and (optionally) the UGA.

System Global Area (SGA): shared memory allocated at instance startup, used by all processes. Components:

• Database buffer cache: holds copies of data blocks read from datafiles, to avoid disk I/O.
• Shared pool: contains the library cache (parsed SQL and PL/SQL), the data dictionary cache, and the result cache.
• Redo log buffer: holds redo entries until LGWR writes them to the redo logs.
• Large pool: optional memory for backup/restore, shared server and parallel execution.
• Java pool: memory for Java code in the database.
• Streams pool: used by Oracle Streams and GoldenGate.

Program Global Area (PGA): private memory for each server process, not shared. It holds the sort area, hash area, session variables and cursor state. It is allocated when the process starts and freed when it ends. Its total size is governed by PGA_AGGREGATE_TARGET.

Management: Automatic Memory Management (MEMORY_TARGET) or Automatic Shared Memory Management (SGA_TARGET) tunes the sizes automatically.

A tablespace is a logical storage area made of one or more datafiles. The steps are:

1. Plan: decide the type (permanent, temporary, undo), the size, the file location and whether it is bigfile or smallfile.
2. Create the tablespace with its first datafile:

CREATE TABLESPACE biz_data
  DATAFILE '/u01/oradata/orcl/biz_data01.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 50M MAXSIZE 2G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

3. Add a datafile when more space is needed:

ALTER TABLESPACE biz_data
  ADD DATAFILE '/u01/oradata/orcl/biz_data02.dbf' SIZE 500M;

4. Resize or change a datafile:

ALTER DATABASE DATAFILE '/u01/oradata/orcl/biz_data01.dbf' RESIZE 1G;
ALTER DATABASE DATAFILE '/u01/oradata/orcl/biz_data01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

5. Other alterations:

ALTER TABLESPACE biz_data READ ONLY;
ALTER TABLESPACE biz_data OFFLINE;
ALTER TABLESPACE biz_data RENAME TO business_data;

6. Assign the tablespace to users and check:

ALTER USER bizuser DEFAULT TABLESPACE business_data QUOTA UNLIMITED ON business_data;
SELECT tablespace_name, file_name, bytes/1024/1024 AS mb FROM dba_data_files;

7. Drop when no longer needed: DROP TABLESPACE business_data INCLUDING CONTENTS AND DATAFILES;

Data concurrency means many users accessing the same data at the same time while keeping it consistent. Oracle uses multiversion read consistency (undo data), so readers do not block writers and writers do not block readers. Locks protect against conflicting writes.

Locking mechanisms

• DML (row-level) locks (TX): when a row is updated, Oracle locks just that row until commit or rollback. A table lock (TM) is also taken in row-exclusive mode to prevent DDL changes.
• DDL locks: protect object definitions while they are changed.
• Latches and internal locks: protect memory structures.
• Explicit locking: SELECT ... FOR UPDATE [NOWAIT | WAIT n] and LOCK TABLE.
• Isolation levels: READ COMMITTED (default) and SERIALIZABLE.

Lock contention occurs when one session holds a lock and another needs the same row.

Detection

SELECT blocking_session, sid, serial#, event, seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL;
SELECT * FROM v$lock WHERE block = 1;

Enterprise Manager and AWR also show lock waits.

Resolution

• The blocker commits or rolls back, which releases the lock.
• The DBA kills the blocking session: ALTER SYSTEM KILL SESSION 'sid,serial#';
• Oracle automatically detects deadlocks (two sessions waiting on each other), rolls back one statement and raises ORA-00060.
• Prevention: short transactions, commit early, consistent access order, NOWAIT or WAIT clauses, and proper indexing on foreign keys.

SQL performance tuning techniques

• Find the slow SQL with AWR, ADDM, SQL Trace/TKPROF, V$SQL, or SQL Monitor.
• Study the plan with EXPLAIN PLAN/DBMS_XPLAN.
• Add or change indexes, and avoid functions on indexed columns.
• Rewrite SQL: avoid SELECT *, use bind variables, and use proper joins.
• Keep optimizer statistics current (DBMS_STATS.GATHER_TABLE_STATS).
• Use partitioning, materialized views, hints, and the SQL Tuning Advisor with SQL profiles.

Oracle Optimizer: the cost-based optimizer (CBO) generates candidate execution plans for a statement and picks the one with the lowest estimated cost, using statistics on tables, indexes and system. It decides access paths (index or full scan), join methods and join order. Accurate statistics give good plans.

SQL Plan Directives: stored notes created when the optimizer finds its estimates were wrong (for example because of column correlation or data skew). On later parses the optimizer uses them to collect extra statistics (dynamic sampling, extended statistics), so better plans are produced.

Adaptive Execution Plans: the plan can change during execution. A plan includes sub-plans, and statistics collectors check actual row counts at run time. If they differ from the estimates, the optimizer switches the join method (for example nested loops to hash join) or the parallel distribution method for the rest of that execution, without recompiling.

Together they reduce bad plans caused by misestimates and improve query speed.

An Oracle database system consists of an instance (memory plus processes) and a database (files on disk), with users connecting through client and server processes.

1. Database instance

The instance is created at startup from the parameter file. It has the SGA and background processes. It mounts and opens one database. Startup stages are NOMOUNT (instance only), MOUNT (control file read) and OPEN (datafiles and redo logs opened).

2. Memory structures

• SGA (shared): database buffer cache, shared pool (library cache, data dictionary cache), redo log buffer, large pool, Java pool and streams pool.
• PGA (private per server process): sort and hash areas, session memory and cursor state.

3. Process architecture

• Client processes: the application or tool.
• Server processes: execute client SQL (dedicated or shared server).
• Background processes: DBWn writes dirty buffers, LGWR writes redo, CKPT triggers checkpoints, SMON does instance recovery, PMON cleans up failed processes, ARCn archives redo, and the listener accepts connections.

4. Storage architecture

• Physical structures: datafiles (data), control files (structure and SCN information), online redo log files (changes for recovery), archived redo logs, parameter file, password file and alert/trace logs.
• Logical structures: database, tablespace, segment, extent and data block. Tables and indexes are segments stored in tablespaces.

5. Interaction of client and server processes

1. The client sends a connection request to the Listener.
2. The listener starts (or assigns) a server process and hands over the connection. The user is authenticated and a session is created with a PGA.
3. The client sends SQL. The server process parses it in the shared pool (reusing the cached plan if one exists), then reads the needed blocks from the buffer cache (reading from datafiles if not cached).
4. For DML, changes are made in the buffer cache and redo is written to the redo log buffer. At commit, LGWR writes the redo to disk.
5. DBWn later writes the changed blocks to datafiles.
6. The server process returns results to the client. On disconnection the session ends and resources are released.

Many users are served at the same time, with concurrency controlled by locks and undo-based read consistency.

Backup and recovery in Oracle

Backup protects against media failure, user error and disasters; recovery restores the database to a consistent state by applying backups plus redo.

Types of failure: statement and process failure, user error, instance failure (recovered automatically by SMON using redo), media failure (needs restore) and network failure.

Backup strategies

• Physical vs logical: physical backups copy datafiles, control files and archived logs; logical backups use Data Pump export.
• Whole vs partial: whole database, or tablespace/datafile level.
• Consistent (cold) backup: taken after a clean shutdown. Inconsistent (hot) backup: taken while open; requires ARCHIVELOG mode.
• Full, incremental (level 0 and level 1; differential or cumulative): incremental saves time and space.
• Archivelog mode keeps archived redo, which allows point-in-time recovery.
• Rule of thumb: multiple copies, offsite storage and regular restore testing.

Recovery techniques

• Complete recovery: restore the lost file and apply all redo, so no data is lost.
• Incomplete (point-in-time) recovery: RECOVER DATABASE UNTIL TIME/SCN/CANCEL, then OPEN RESETLOGS.
• Block media recovery for corrupted blocks.
• Instance recovery automatic roll forward and roll back at restart.

RMAN

Recovery Manager is Oracle's backup and recovery tool. It does full and incremental backups, compression, encryption, validation, block-level corruption checks and a recovery catalog. Example:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> RESTORE DATABASE; RECOVER DATABASE;

Flashback Database

It rewinds the database to a past point in time using flashback logs kept in the fast recovery area (FLASHBACK DATABASE TO TIMESTAMP ...). It recovers from logical errors much faster than restore and recover. Other flashback features are Flashback Query, Table and Drop.

High availability

• Data Guard: maintains one or more standby databases kept in sync by shipping and applying redo from the primary. On failure a switchover or failover gives continued service and protects data (modes: maximum protection, availability, performance). Standbys can also be used for reporting.
• Oracle RAC (Real Application Clusters): many instances on different servers open the same shared database. If a node fails, the others continue, giving availability, load balancing and scalability.

Together, RMAN and flashback protect data, while Data Guard (disaster protection) and RAC (instance and server protection) maintain availability.