Database Administration

Database Management System (DBMS)

A DBMS is software that is used to create, store, organize, retrieve, update and secure data in a database, and provides an interface between users/applications and the data. Functions: data definition and manipulation (SQL), concurrency control, security and authorization, backup and recovery, integrity constraints, and reduction of redundancy. Examples: Oracle, MySQL, SQL Server, PostgreSQL.

Oracle database architecture

An Oracle server consists of the database (files on disk) and the instance (memory structures and background processes).

1. Memory structures
   • SGA (System Global Area): shared memory with the database buffer cache, shared pool (library cache, data dictionary cache), redo log buffer, large pool, Java pool.
   • PGA (Program Global Area): private memory for each server process (sort area, session data).
2. Background processes: DBWn (writes dirty buffers to datafiles), LGWR (writes redo log buffer to redo logs), CKPT (checkpoint), SMON (system monitor, instance recovery), PMON (process monitor, cleans failed processes), ARCn (archives redo logs), plus listener-related processes.
3. Physical structures (files): datafiles (store data), control files (database structure, SCN, file locations), online redo log files (record changes for recovery), archived redo logs, parameter file (SPFILE/PFILE), password file, alert and trace files.
4. Logical structures: database > tablespaces > segments > extents > data blocks.
5. Processes: user process, server process (dedicated/shared), and the listener which accepts client connections.

Working: the client connects through the listener, a server process reads data blocks into the buffer cache, changes are recorded in the redo buffer, LGWR writes them at commit, and DBWn later writes the blocks to the datafiles.

Backup, restore and recovery

• Backup: a copy of the database data (datafiles, control files, archived logs, spfile) stored in a safe place, to protect against loss.
• Restore: copying the backup files back to their location after a failure.
• Recovery: applying redo (archived and online redo logs) to the restored files to bring the database to a consistent state, up to the point of failure or a chosen point in time.

Types of backup

1. Physical backup: copies of database files.
   • Cold (offline/closed) backup: database shut down cleanly, files copied by OS commands. Simple, but downtime.
   • Hot (online/open) backup: database in ARCHIVELOG mode; each tablespace is placed in backup mode.

   ALTER TABLESPACE users BEGIN BACKUP;
   -- copy the datafiles at OS level
   ALTER TABLESPACE users END BACKUP;
   

   • RMAN backup (Recovery Manager): full, incremental (level 0/1), whole database or tablespace:

   RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
   RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
   

2. Logical backup: export of objects with Data Pump: expdp system/pwd schemas=hr directory=dp_dir dumpfile=hr.dmp, restored with impdp.

Recovery process

• Instance recovery: automatic by SMON after crash; rolls forward redo and rolls back uncommitted transactions.
• Media recovery (after file loss):
  • Complete recovery: restore damaged datafile and apply all redo.

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

  • Incomplete (point-in-time) recovery: recover to a time/SCN before an error, then open with RESETLOGS:

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

• User-error recovery with Flashback.

ARCHIVELOG mode is necessary for hot backups and complete recovery.

Importance of privileges in database security

A privilege is the right to execute a particular SQL statement or access an object of another user. System privileges (CREATE SESSION, CREATE TABLE) allow actions in the database; object privileges (SELECT, INSERT, UPDATE, DELETE on a table) allow access to specific objects. Privileges are important because they:

• Enforce the principle of least privilege: each user gets only the access needed.
• Prevent unauthorized viewing, modification or deletion of data.
• Control administrative actions and support accountability and auditing.
• Protect confidentiality and integrity of the data.

Roles in Oracle

A role is a named group of related privileges that can be granted to users or to other roles. Instead of granting many privileges to every user, the DBA grants them to a role once and then assigns the role to users. Benefits: simpler administration, dynamic change (changing the role changes all users), roles can be enabled/disabled, and they can be password protected. Predefined roles include CONNECT, RESOURCE and DBA.

Commands

(a) Create role

CREATE ROLE sales_role;

(b) Grant system and object privileges to the role

GRANT CREATE SESSION, CREATE TABLE TO sales_role;
GRANT SELECT, INSERT, UPDATE ON hr.employees TO sales_role;

(c) Create user

CREATE USER ram IDENTIFIED BY ram123
  DEFAULT TABLESPACE users QUOTA 50M ON users;

(d) Assign role to user

GRANT sales_role TO ram;

(e) Drop role

DROP ROLE sales_role;

Data files

Data files are the physical operating-system files on disk (e.g. users01.dbf) in which Oracle stores the database data: tables, indexes and other segments. They contain a header and data blocks, and are read into the buffer cache and written back by DBWn.

Relationship with tablespace

A tablespace is a logical storage unit that groups related segments. Each tablespace is made of one or more data files, but a data file belongs to exactly one tablespace. Logical objects are stored in tablespaces, which are physically stored in their data files. A tablespace can be enlarged by adding data files or resizing them.

Command to add a datafile to the tablespace USERS

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

SQL*Plus

SQLPlus is Oracle's command-line client tool for interacting with an Oracle database. It lets users connect to the database, enter and run SQL statements, PL/SQL blocks and SQLPlus commands, and format the results of queries into reports.

Functionalities used by the Oracle DBA

• Connect as administrator (CONNECT / AS SYSDBA) and start up and shut down the database (STARTUP, SHUTDOWN IMMEDIATE).
• Run SQL and PL/SQL statements and scripts (@script.sql) for administration tasks such as creating users, tablespaces and granting privileges.
• Query data dictionary and dynamic views (DBA_USERS, V$SESSION) to monitor the database.
• Format output (SET LINESIZE, COLUMN, TTITLE, BREAK) and generate reports, spool output to files (SPOOL).
• Edit commands in the buffer, describe objects (DESCRIBE table), define variables (DEFINE, ACCEPT).
• Backup and recovery operations and automation through scripts in batch jobs.
• Display instance parameters (SHOW PARAMETER, SHOW USER).

Oracle RAC (Real Application Clusters) allows several instances, running on different servers (nodes), to open and use a single shared Oracle database at the same time.

• All nodes access the same datafiles, control files and redo logs stored on shared storage.
• Each node has its own instance (SGA and background processes) and its own redo thread and undo tablespace.
• Nodes communicate through a private interconnect using Cache Fusion, which transfers data blocks between instance caches without writing them to disk first.
• Benefits: high availability (if one node fails, others continue), scalability (add nodes as load grows), and load balancing.

Oracle ASM (Automatic Storage Management) is Oracle's integrated volume manager and file system for database files.

• Disks are grouped into disk groups; ASM stripes and mirrors files across them automatically (normal or high redundancy).
• It removes the need for a third-party volume manager or file system and spreads I/O evenly.
• Disks can be added or removed online, and ASM rebalances the data automatically.
• It is run by a separate ASM instance, and is commonly used together with RAC for shared storage.

Oracle Data Guard is a feature that creates, maintains and monitors one or more standby databases so that the production (primary) database is protected from failures, disasters and data corruption. The standby is kept synchronized by transmitting and applying the primary's redo data.

Types of standby

• Physical standby: an exact block-for-block copy, kept current by Redo Apply.
• Logical standby: kept current by SQL Apply; it can be open for reporting while being updated.
• Snapshot standby: a standby temporarily opened read-write for testing.

Protection modes: Maximum Protection, Maximum Availability and Maximum Performance (trade-off between zero data loss and speed).

Importance

• Disaster recovery: on primary failure, a switchover (planned) or failover (unplanned) makes the standby the new primary.
• High availability with minimal downtime.
• Data protection against corruption and human errors.
• Offloads work: backups and reports can run on the standby, reducing load on the primary.
• Easy management with Data Guard Broker.

A Database Administrator (DBA) is the person (or team) responsible for installing, configuring, securing, monitoring, maintaining and recovering the database system so that it is available, consistent and performs well.

Roles of a DBA

• Installation and configuration: install Oracle software, create databases, apply patches and upgrades.
• Storage and space management: create tablespaces and datafiles, plan capacity.
• Security management: create users, roles and profiles, grant and revoke privileges, enable auditing.
• Backup and recovery: plan and test backups (RMAN), restore after failure.
• Performance tuning: monitor the system, tune SQL and memory, use AWR/ADDM.
• Database design support: work with developers on schema design, indexes and constraints.
• Monitoring and troubleshooting: examine alert logs, resolve errors, ensure availability.
• Data migration and high availability: import/export data, set up Data Guard/RAC.
• Documentation and user support.

Job scheduling in Oracle means defining tasks (PL/SQL blocks, stored procedures, executables or scripts) and having the database run them automatically at a given time or on an event. Oracle provides the DBMS_SCHEDULER package for this (a more powerful replacement of DBMS_JOB).

How DBMS_SCHEDULER works

1. The administrator defines a job with DBMS_SCHEDULER.CREATE_JOB, naming what to run and when.
2. The job coordinator background process (CJQ0) checks job metadata and starts due jobs.
3. It assigns jobs to job slave processes (Jnnn), which execute them.
4. Run status and results are written to the data dictionary views (e.g. DBA_SCHEDULER_JOB_RUN_DETAILS).

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

Components of the scheduler

• Job: the task plus its schedule; the unit that runs.
• Program: a reusable definition of what is to be run.
• Schedule: defines when and how often (start date, repeat interval), or an event.
• Job class: groups jobs, linking them to a resource consumer group and a service for resource control.
• Window: a time interval with an assigned resource plan (e.g. a maintenance window).
• Window group: a collection of windows.
• Chain: a set of steps with dependencies between programs.
• Event: a trigger from a queue or file arrival that starts a job.

Oracle Net Services provides connectivity between clients and Oracle databases over a network. Its main components are:

• Oracle Net (Net foundation layer): establishes and maintains the session and transfers data between client and server; works on top of network protocols such as TCP/IP.
• Listener: a server-side process that listens on a port (default 1521) for incoming connection requests and hands them to the database instance. Configured in listener.ora and controlled by lsnrctl.
• tnsnames.ora: client-side file mapping a net service name to the host, port and service name of the database.
• sqlnet.ora: sets general Oracle Net parameters, such as naming methods and tracing.
• Naming methods: ways to resolve a service name: local naming (tnsnames.ora), Easy Connect (host:port/service), directory naming (LDAP) and external naming.
• Connection Manager: a gateway for connection concentration, access control and protocol conversion.
• Oracle Net Configuration Assistant (netca), Net Manager (netmgr): tools to configure these components.
• Protocol support layer: TCP/IP, TCP/IP with SSL, named pipes.

Database auditing is the monitoring and recording of selected database actions (logins, object access, privilege use, DDL/DML) so that suspicious activity can be detected, accountability enforced and security regulations met.

Standard auditing in Oracle is enabled through the AUDIT_TRAIL initialization parameter (DB, DB,EXTENDED, OS, XML, NONE) and the AUDIT statement. There are three kinds:

• Statement auditing: audits SQL statements by type, e.g. AUDIT TABLE; or AUDIT SESSION;.
• Privilege auditing: audits use of a system privilege, e.g. AUDIT CREATE ANY TABLE;.
• Object auditing: audits operations on a specific object, e.g. AUDIT SELECT, UPDATE ON hr.employees BY ACCESS;.

Options: BY SESSION or BY ACCESS, and WHENEVER [NOT] SUCCESSFUL.

Records are stored in the audit trail (SYS.AUD$) and viewed through DBA_AUDIT_TRAIL, DBA_AUDIT_SESSION and DBA_AUDIT_OBJECT. Auditing is turned off with NOAUDIT. (Fine-grained auditing via DBMS_FGA is the more detailed alternative.)

SQL Tuning Advisor is an Oracle tool (part of the Advisor Framework, used with the Automatic Database Diagnostic Monitor and AWR) that analyses high-load SQL statements and recommends ways to make them run faster.

How it tunes

1. Input: a SQL statement, a SQL tuning set, or top SQL from AWR/cursor cache.
2. Statistics analysis: detects missing or stale optimizer statistics and recommends collecting them.
3. SQL profiling: gathers additional information and creates a SQL profile that helps the optimizer pick a better plan, without changing the code.
4. Access path analysis: recommends new indexes that would improve the plan.
5. SQL structure analysis: suggests rewriting statements with bad structure (e.g. poor use of NOT IN, Cartesian products).
6. Output: a report with findings, rationale and benefit, which the DBA accepts (DBMS_SQLTUNE.ACCEPT_SQL_PROFILE) or ignores.

DECLARE t VARCHAR2(30);
BEGIN
  t := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'abcd1234xyz99');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(t);
END;
/
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_NAME') FROM dual;

It can also be run through Enterprise Manager, and runs automatically as the Automatic SQL Tuning task in maintenance windows.

Locking in Oracle is the mechanism that controls concurrent access to shared data so that transactions stay consistent and do not interfere with each other. Oracle uses row-level locking with multiversion read consistency, so readers never block writers and writers never block readers.

Levels of locking

• Row-level (TX) locks: acquired automatically when a row is changed by INSERT, UPDATE, DELETE or SELECT ... FOR UPDATE. Only those rows are locked, giving the highest concurrency. The lock is held until commit or rollback.
• Table-level (TM) locks: acquired along with row locks to protect the table's structure from conflicting DDL. Modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX) and exclusive (X).
• Dictionary locks: protect the definitions of schema objects during DDL (exclusive) and parse (shared).
• Latches and internal locks (system-level): lightweight mechanisms protecting memory structures such as the buffer cache and shared pool.
• (Oracle does not escalate row locks to table locks.)

Methods to acquire locks

• Implicit (automatic) locking: Oracle takes the least restrictive lock needed for each statement, e.g. DML takes a row exclusive table lock and exclusive row locks.
• Explicit locking:
  • SELECT ... FROM emp WHERE ... FOR UPDATE [NOWAIT | WAIT n | SKIP LOCKED]; locks the selected rows.
  • LOCK TABLE emp IN EXCLUSIVE MODE [NOWAIT]; locks a whole table in a chosen mode (also ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE).
• User-defined locks: application locks through the DBMS_LOCK package.
• Deadlocks: when two sessions wait for each other, Oracle detects it and rolls back one statement.

An Oracle database system consists of the database (physical files) and the instance (memory and processes).

                 +---------------- INSTANCE ----------------+
  User  <--->    |  SGA                                     |
  process  Server|  [Shared Pool][DB Buffer Cache][Redo Log |
           process|   Buffer][Large Pool][Java Pool]        |
                 |  Background processes:                   |
                 |  SMON PMON DBWn LGWR CKPT ARCn           |
                 +--------------------+---------------------+
                                      |
                 +--------------------v---------------------+
                 |  DATABASE: datafiles | control files |    |
                 |  online redo logs | archived logs | SPFILE|
                 +------------------------------------------+

1. Memory structures

• SGA (System Global Area): shared by all processes. Includes the database buffer cache (cached data blocks), shared pool (library cache for parsed SQL, data dictionary cache), redo log buffer, large pool and Java pool.
• PGA (Program Global Area): private memory for each server process (sort area, session data).

2. Processes

• User process: the application/tool the user runs.
• Server process: executes SQL on behalf of the user process.
• Background processes:
  • DBWn writes dirty buffers to datafiles.
  • LGWR writes the redo buffer to online redo logs at commit.
  • CKPT signals checkpoints and updates headers.
  • SMON performs instance recovery and cleans temporary segments.
  • PMON cleans up failed processes and releases their resources.
  • ARCn copies filled redo logs to archive storage.

3. Physical storage structures

• Datafiles: hold the data.
• Control files: record the database structure, names and locations of files, and SCN.
• Online redo log files: record all changes for recovery.
• Parameter file (SPFILE/PFILE), password file, archived logs, alert and trace files.

4. Logical storage structures: database, tablespaces, segments, extents, data blocks.

A connection works as follows: the user process connects via the listener, a server process is created, SQL is parsed in the shared pool, data blocks are read into the buffer cache, changes are logged in the redo buffer, and LGWR/DBWn write them to disk.

Space management is the process of allocating, tracking and reclaiming storage in the database's tablespaces, segments, extents and blocks so that space is used efficiently.

How it is done

• Extent management:
  • Locally managed tablespaces (default): extents tracked by bitmaps in the datafile header. Options are AUTOALLOCATE (system chooses extent sizes) or UNIFORM SIZE n.
  • Dictionary-managed tablespaces (old): tracked in data dictionary tables, slower.
• Segment space management: SEGMENT SPACE MANAGEMENT AUTO (ASSM) uses bitmaps to track free space in blocks; MANUAL uses freelists.
• Block-level control: PCTFREE (space reserved for updates) and PCTUSED (manual mode) determine when a block accepts inserts.
• Reclaiming space: ALTER TABLE t SHRINK SPACE;, ALTER TABLE t MOVE, ALTER DATABASE DATAFILE ... RESIZE, and the Segment Advisor for finding reclaimable space.
• Monitoring: DBA_FREE_SPACE, DBA_SEGMENTS, DBA_EXTENTS, and tablespace usage alerts.
• Quotas limit the space each user can use in a tablespace.

Creating tables without segments (deferred segment creation) From Oracle 11gR2, a table's segment is not created until the first row is inserted. This saves space and time when many tables stay empty. It is controlled by the parameter DEFERRED_SEGMENT_CREATION (default TRUE) or by the clause in the statement:

CREATE TABLE student (id NUMBER, name VARCHAR2(30))
  SEGMENT CREATION DEFERRED;

-- force immediate creation
CREATE TABLE teacher (id NUMBER) SEGMENT CREATION IMMEDIATE;

After CREATE TABLE ... SEGMENT CREATION DEFERRED, DBA_SEGMENTS shows no segment; the first INSERT creates it. ALTER TABLE student ALLOCATE EXTENT; can also create it manually. The parameter can be changed with ALTER SYSTEM SET deferred_segment_creation = FALSE;.

The Database Administrator (DBA) is responsible for the overall management of the database. The main roles and responsibilities are:

• Installing and upgrading Oracle software, creating databases and applying patches.
• Planning and managing storage: tablespaces, datafiles, quotas and capacity.
• Managing security: creating users, roles and profiles; granting and revoking privileges; auditing.
• Planning and performing backup and recovery (RMAN), and testing the recovery strategy.
• Monitoring and tuning performance of SQL, memory and I/O.
• Ensuring availability through Data Guard, RAC and failover planning.
• Configuring network connectivity (listener, tnsnames.ora).
• Supporting developers with schema design, and managing data loading/migration (Data Pump, SQL*Loader).
• Troubleshooting using alert logs and trace files, and keeping documentation.

Tablespace: a logical storage unit in Oracle that groups related segments (tables, indexes). Every database has at least SYSTEM and SYSAUX; others include UNDO, TEMP and user tablespaces.

Datafile: a physical operating-system file that stores the data of a tablespace. A tablespace is made of one or more datafiles; each datafile belongs to only one tablespace.

Adding a new tablespace with datafile

CREATE TABLESPACE student_data
  DATAFILE '/u01/oradata/orcl/student01.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE 1G;

Adding a datafile to an existing tablespace

ALTER TABLESPACE student_data
  ADD DATAFILE '/u01/oradata/orcl/student02.dbf' SIZE 100M;

Other useful commands: ALTER DATABASE DATAFILE '...' RESIZE 200M; and the view DBA_DATA_FILES to list datafiles.

A privilege is a right to execute a particular SQL statement or to access another user's object. There are system privileges (e.g. CREATE SESSION, CREATE TABLE) and object privileges (e.g. SELECT, INSERT on a specific table).

-- create user
CREATE USER ram IDENTIFIED BY ram123
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 50M ON users;

-- grant privileges
GRANT CREATE SESSION, CREATE TABLE TO ram;
GRANT SELECT, INSERT ON hr.employees TO ram;

-- revoke privileges
REVOKE CREATE TABLE FROM ram;
REVOKE INSERT ON hr.employees FROM ram;

-- drop user
DROP USER ram CASCADE;

CASCADE also drops all objects owned by the user.

Oracle AWR (Automatic Workload Repository) is a built-in repository that automatically collects and stores performance statistics of the database (wait events, top SQL, system and session statistics, I/O, memory). Snapshots are taken every hour by default (by MMON) and kept for 8 days, and are stored in the SYSAUX tablespace (DBA_HIST_* views). AWR data is used by ADDM, SQL Tuning Advisor and the DBA for diagnosing performance problems by comparing two snapshots.

Viewing the AWR report

• Using SQLPlus scripts* (as a privileged user):

@?/rdbms/admin/awrrpt.sql

Choose the report format (HTML or text), the number of days, the begin and end snapshot IDs, and a report name; the report is generated as a file.

• A snapshot can be created manually with EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;.
• Using Enterprise Manager: Performance > AWR > AWR Report, then select snapshots.
• Report sections include load profile, top wait events, top SQL, instance efficiency and memory statistics.

A role is a named group of related privileges that can be granted to users or to other roles. It simplifies privilege management: instead of granting many privileges to each user, the DBA grants them once to the role, then grants the role to users.

Role in privilege management

• Reduces administrative work and errors.
• Changing the role's privileges instantly changes the access of all users who hold it.
• Roles can be enabled/disabled, password-protected and set as default.
• Roles can be nested (granted to other roles).

Example

CREATE ROLE clerk;
GRANT CREATE SESSION, CREATE VIEW TO clerk;
GRANT SELECT, INSERT, UPDATE ON hr.employees TO clerk;

GRANT clerk TO ram, sita;   -- assign to users

REVOKE UPDATE ON hr.employees FROM clerk;  -- affects all holders
DROP ROLE clerk;

Oracle predefined roles include CONNECT, RESOURCE and DBA.

SQL tuning is the process of improving the performance of SQL statements so that they use fewer resources (CPU, I/O, memory) and return results faster. The main target is to get the optimizer to choose the best execution plan.

SQL tuning process

1. Identify high-load SQL: use AWR, ADDM, V$SQL, or Active Session History to find statements with high elapsed time, CPU or buffer gets.
2. Gather information: collect the execution plan (EXPLAIN PLAN, DBMS_XPLAN.DISPLAY, autotrace) and optimizer statistics.
3. Analyse the plan: look for full table scans, wrong join order or method, high cost and cardinality errors.
4. Tune:
   • refresh optimizer statistics (DBMS_STATS);
   • add or modify indexes;
   • rewrite the SQL (avoid functions on indexed columns, use joins properly, select only needed columns);
   • use hints, SQL profiles, or SQL plan baselines;
   • consider partitioning or materialized views.
5. Use advisors: SQL Tuning Advisor and SQL Access Advisor give recommendations.
6. Test and verify: compare the new plan and timing, then implement; monitor the change.

A user profile is a named set of resource limits and password rules that can be assigned to database users. It controls items such as SESSIONS_PER_USER, CPU_PER_SESSION, IDLE_TIME, CONNECT_TIME, FAILED_LOGIN_ATTEMPTS, PASSWORD_LIFE_TIME, PASSWORD_REUSE_TIME and PASSWORD_LOCK_TIME. Users without an explicit profile use the DEFAULT profile. Resource limits are enforced only if RESOURCE_LIMIT=TRUE.

-- create a profile
CREATE PROFILE student_prof LIMIT
  SESSIONS_PER_USER     2
  IDLE_TIME             30
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LIFE_TIME    60
  PASSWORD_LOCK_TIME    1;

-- enable resource limits
ALTER SYSTEM SET resource_limit = TRUE;

-- assign to a new user
CREATE USER hari IDENTIFIED BY hari123
  PROFILE student_prof;
GRANT CREATE SESSION TO hari;

-- change an existing user
ALTER USER ram PROFILE student_prof;

Listener: a server-side process (managed with lsnrctl start|stop|status) that listens on a network address (default TCP port 1521) for incoming client connection requests and passes them to the appropriate database instance. Instances register with it dynamically (via PMON/LREG) or statically.

listener.ora (server side, in $ORACLE_HOME/network/admin): configuration file of the listener. It defines the listener name, protocol, host and port, and optionally static service information.

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521)))

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC = (GLOBAL_DBNAME = orcl)
                (ORACLE_HOME = /u01/app/oracle/product/19c)
                (SID_NAME = orcl)))

tnsnames.ora (client side): maps a net service name (alias) to a connect descriptor giving the host, port and service name of the database, so users connect with sqlplus scott/tiger@orcl.

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

An event-based schedule starts a scheduler job when an event occurs (for example, a file arrives or a message is enqueued) instead of at a fixed time. Events are raised by applications by enqueuing a message to an Oracle Advanced Queue, or by a file watcher for file arrival.

Steps to create one (queue event)

1. Create a queue table and queue, and start the queue:

BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'evt_tab',
      queue_payload_type => 'SYS.SCHEDULER$_EVENT_INFO');
  DBMS_AQADM.CREATE_QUEUE('evt_q', 'evt_tab');
  DBMS_AQADM.START_QUEUE('evt_q');
END;
/

2. Create the event schedule:

BEGIN
  DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE(
    schedule_name   => 'evt_sched',
    start_date      => SYSTIMESTAMP,
    event_condition => 'tab.user_data.event_name = ''file_loaded''',
    queue_spec      => 'evt_q');
END;
/

3. Create a job that uses the schedule (schedule_name => 'evt_sched') or set queue_spec and event_condition directly on the job, with enabled => TRUE.

When a matching message arrives in the queue, the job runs. A file-arrival event uses CREATE_FILE_WATCHER.

Database administration tasks

• Installing, configuring and upgrading Oracle software and patching.
• Creating and managing databases, tablespaces, datafiles and storage.
• User and security management: users, roles, profiles, privileges, auditing.
• Backup and recovery planning and execution (RMAN).
• Performance monitoring and tuning (AWR, ADDM, SQL tuning).
• Network configuration (listener, tnsnames.ora).
• Job scheduling and automation of routine tasks.
• Data movement (Data Pump, SQL*Loader), capacity planning.
• High availability setup (RAC, Data Guard) and troubleshooting with alert logs.

Oracle Data Guard Data Guard maintains one or more synchronized standby databases to protect a primary database from failure, disaster and corruption. Redo generated on the primary is shipped to the standby and applied there.

• Physical standby (Redo Apply, identical copy), logical standby (SQL Apply, can be used for reporting), snapshot standby.
• Protection modes: Maximum Protection, Maximum Availability, Maximum Performance.
• Role transitions: switchover (planned, no data loss) and failover (after primary failure).
• Standbys can offload backups and reporting. Data Guard Broker simplifies management.

Initialization parameter file: a file read at instance startup that holds the parameters configuring the instance (memory sizes, control file locations, DB_NAME, PROCESSES, etc.). It comes in two forms: PFILE and SPFILE.

Convert with CREATE SPFILE FROM PFILE; and CREATE PFILE FROM SPFILE;.

Oracle-defined roles

• CONNECT: basic login rights (CREATE SESSION).
• RESOURCE: rights to create tables, sequences, procedures, triggers, etc.
• DBA: all system privileges, with admin option.
• SELECT_CATALOG_ROLE: read access to data dictionary views.
• EXP_FULL_DATABASE / IMP_FULL_DATABASE: export/import of the whole database.

Example:

GRANT CONNECT, RESOURCE TO ram;
GRANT SELECT_CATALOG_ROLE TO auditor1;

Tablespace: a logical storage structure that groups related segments of a database; physically it is stored in one or more datafiles. Types: permanent, temporary and undo; and smallfile/bigfile.

Undo retention policy: undo data records the old values of changed rows. UNDO_RETENTION (seconds, default 900) is the minimum time Oracle tries to keep committed undo before it can be overwritten. It supports read consistency for long queries, flashback queries and avoids "snapshot too old" (ORA-01555) errors. With RETENTION GUARANTEE on the undo tablespace, unexpired undo is never overwritten.

ALTER SYSTEM SET undo_retention = 1800;
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

Commands to manage tablespaces

-- create
CREATE TABLESPACE app_data DATAFILE '/u01/oradata/app01.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u01/oradata/temp2.dbf' SIZE 50M;
CREATE UNDO TABLESPACE undotbs2 DATAFILE '/u01/oradata/undo2.dbf' SIZE 100M;

-- add / resize datafile
ALTER TABLESPACE app_data ADD DATAFILE '/u01/oradata/app02.dbf' SIZE 100M;
ALTER DATABASE DATAFILE '/u01/oradata/app01.dbf' RESIZE 200M;

-- take offline / online, read only
ALTER TABLESPACE app_data OFFLINE;
ALTER TABLESPACE app_data ONLINE;
ALTER TABLESPACE app_data READ ONLY;

-- rename
ALTER TABLESPACE app_data RENAME TO app_tbs;

-- drop
DROP TABLESPACE app_tbs INCLUDING CONTENTS AND DATAFILES;

Information: DBA_TABLESPACES, DBA_DATA_FILES, DBA_FREE_SPACE.

Oracle Data Pump is a high-speed utility for moving data and metadata into and out of Oracle databases (replacing the old exp/imp). It runs on the server using jobs, supports parallelism, restart, network mode and filtering. Tools: expdp, impdp, and the DBMS_DATAPUMP API. It uses a directory object for dump files.

Procedure

1. Create a directory object and grant access:

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

2. Export:

expdp hr/hr_pass DIRECTORY=dp_dir DUMPFILE=hr.dmp LOGFILE=hr_exp.log SCHEMAS=hr

(Other modes: TABLES=, FULL=Y, TABLESPACES=.) 3. Copy the dump file to the target server if needed. 4. Import:

impdp hr/hr_pass DIRECTORY=dp_dir DUMPFILE=hr.dmp LOGFILE=hr_imp.log SCHEMAS=hr REMAP_SCHEMA=hr:hr_test TABLE_EXISTS_ACTION=REPLACE

5. Check the log, and monitor with DBA_DATAPUMP_JOBS.

Container database (CDB) and pluggable database (PDB) are the multitenant architecture introduced in Oracle 12c. (The question's "portable database" refers to the pluggable database.)

Container database (CDB): a single database that holds the Oracle-supplied system objects and can contain many pluggable databases. It has one set of background processes, memory (SGA), control files, redo logs and undo, shared by all PDBs. It contains:

• CDB$ROOT: the root container, with Oracle metadata and common users.
• PDB$SEED: a template used to create new PDBs.
• Zero or more PDBs.

Pluggable database (PDB): a portable collection of schemas, objects and data that appears to applications as an independent database. It has its own datafiles, local users and tablespaces. It can be unplugged from one CDB (to an XML manifest) and plugged into another, cloned quickly, or moved.

Advantages: consolidation of many databases on one instance (less memory/CPU), faster provisioning and cloning, simplified patching and upgrades, and separation of administration per PDB.

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

Job: the unit of work in the Oracle Scheduler: a task (PL/SQL block, stored procedure, executable or chain) together with when it runs. Created with DBMS_SCHEDULER.CREATE_JOB. It can be a regular, lightweight, or detached job.

Job class: a group of jobs that share common attributes and are managed together. A class maps jobs to a resource consumer group (for resource allocation), a service, logging level and retention, so that resources can be controlled for the whole group.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS(job_class_name => 'batch_class',
        resource_consumer_group => 'DEFAULT_CONSUMER_GROUP');
END;
/

Creating an event-based schedule

BEGIN
  DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE(
    schedule_name   => 'file_evt_sched',
    start_date      => SYSTIMESTAMP,
    event_condition => 'tab.user_data.event_name = ''data_ready''',
    queue_spec      => 'evt_q');
END;
/

A job then uses it with schedule_name => 'file_evt_sched'. The queue (evt_q) must exist and be started via DBMS_AQADM; when a matching message is enqueued, the job runs.

Extent: a logical unit of storage made up of a number of contiguous data blocks, allocated to a segment. When a segment (e.g. a table) needs more space, Oracle allocates a new extent to it. A segment consists of one or more extents, and a tablespace's datafile holds extents.

Row chaining: occurs when a row is too large to fit in a single data block (e.g. long rows, or more than 255 columns). Oracle stores the row in two or more blocks linked by a row pointer. It happens at insert time and cannot be avoided for rows larger than a block (except by using a larger block size).

Row migration: occurs when an existing row is updated and grows so that it no longer fits in its block (because PCTFREE space is exhausted). Oracle moves the whole row to a new block and leaves a forwarding pointer (the row keeps its original ROWID) in the old block.

Effects: both require extra I/O to read a row, degrading performance. Remedies: set a larger PCTFREE, use a bigger block size, rebuild with ALTER TABLE t MOVE (and rebuild indexes), or use ALTER TABLE t SHRINK SPACE. Detect with ANALYZE TABLE ... LIST CHAINED ROWS and CHAIN_CNT in DBA_TABLES.

A role is a named collection of privileges that can be granted to users or other roles, making privilege management easier.

-- create the role
CREATE ROLE general;

-- grant privileges to the role
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW TO general;
GRANT SELECT, INSERT ON hr.employees TO general;

-- assign the role to a user
GRANT general TO ram;

-- (optional) make it a default role
ALTER USER ram DEFAULT ROLE general;

User ram now has all privileges of general. To remove: REVOKE general FROM ram;. Roles granted are listed in DBA_ROLE_PRIVS, and privileges of the role in DBA_SYS_PRIVS and DBA_TAB_PRIVS.

Database auditing is the recording and review of database activity. It plays a significant role because:

• Security and accountability: it records who did what, when and from where, so users are answerable for their actions.
• Detecting misuse: unauthorized access attempts, privilege abuse and suspicious activity (e.g. repeated failed logins) can be found early.
• Regulatory compliance: standards and laws (SOX, HIPAA, PCI-DSS, banking regulations) require audit trails.
• Investigation and forensics: helps trace the cause of a data breach or unexpected change after the fact.
• Deterrence: users who know they are monitored are less likely to misbehave.
• Monitoring and planning: shows which objects and privileges are used, helping to tighten privileges and tune the system.
• Data integrity: helps detect unauthorized modifications of sensitive data.

In Oracle it is done through standard auditing (statement, privilege, object) and fine-grained auditing (DBMS_FGA), with records kept in the audit trail.

(a) Audit Trail An audit trail is the record of audited events kept by the database. Each entry stores the user, time, action, object, terminal/host and success or failure. In Oracle, the location is set by the AUDIT_TRAIL parameter: the database table SYS.AUD$ (DB), operating-system files (OS) or XML files. It is viewed through DBA_AUDIT_TRAIL, DBA_AUDIT_SESSION, DBA_AUDIT_OBJECT and DBA_FGA_AUDIT_TRAIL. It should be protected and purged regularly (DBMS_AUDIT_MGMT) so it does not fill the SYSTEM tablespace. It is used for security monitoring, compliance and forensic investigation.

(b) SQL Tuning Advisor A tool that examines high-load SQL statements (from AWR, a tuning set, or the cursor cache) and recommends improvements. It checks stale or missing statistics, creates SQL profiles, suggests new indexes (access path analysis) and recommends restructuring the SQL. Run through DBMS_SQLTUNE (CREATE_TUNING_TASK, EXECUTE_TUNING_TASK, REPORT_TUNING_TASK) or Enterprise Manager; recommendations are accepted by the DBA, and the advisor also runs automatically in maintenance windows.