Database administration

In short

Database administration (DBA work) is the set of activities that keep a database system available, secure, fast and recoverable: installing and configuring the server, creating users and granting privileges, allocating storage, backing up and restoring, monitoring performance, patching, and planning capacity. It sits between the schema designer and the system administrator — the DBA owns the running instance, not the application logic. University syllabi examine it under "Database Administration and Security" (users, roles, grants, views as a security mechanism, backup types, recovery, the data dictionary), and GATE touches recovery and the log. In India it is a large hiring area in itself: TCS, Infosys, Wipro, HCLTech, Cognizant, Capgemini and LTIMindtree staff Oracle/SQL Server/PostgreSQL DBA and "database reliability engineer" roles for banks and telecoms, and DPDP Act compliance has made access control and audit logging an interview subject rather than a footnote.

Explained

What a DBA actually does, and the kinds of DBA

• Installation and configuration: install the engine, set memory parameters (Oracle SGA/PGA, MySQL innodb_buffer_pool_size, PostgreSQL shared_buffers), character set/collation, port, data directory, and the initial parameter file.
• User and privilege management: create database users/logins, build roles, grant the minimum needed, revoke on exit.
• Storage management: tablespaces/filegroups, datafile sizing and autoextend, archive log destination, free-space monitoring.
• Backup and recovery: choose a strategy that meets the agreed RPO/RTO, run it, and — the part most often skipped — test restores.
• Performance monitoring and tuning: find slow statements, read plans, add or drop indexes, refresh optimiser statistics, size the cache.
• Availability: replication, standby databases, failover/switchover, cluster health.
• Change and patch management: apply security patches and version upgrades in a maintenance window, with a rollback plan.
• Auditing and compliance: who read or changed what, retention of logs, masking of personal data in non-production copies.
• Classification by focus: System DBA (engine, OS, storage), Application/Development DBA (schema, SQL, stored code), Data Architect (models and standards), Database Reliability Engineer / DevOps DBA (automation, IaC, CI/CD for schema), Cloud DBA (RDS, Azure SQL, Cloud SQL — the vendor handles patching and backup plumbing, the DBA still owns schema, access, cost and tuning).
• Distinguish the roles examined in theory papers: DBA (administers), Database Designer (schema, normalisation), Application Programmer, End user (naive/sophisticated).

Users, privileges, roles and views as security

• Authentication is "who are you" (password, OS authentication, LDAP/Active Directory, certificate, IAM token). Authorisation is "what may you do" (privileges). Keep the two separate in answers.
• Discretionary Access Control (DAC): the standard SQL model — the object owner grants privileges. Mandatory Access Control (MAC): labels/clearances (Bell-LaPadula style), used in defence-grade systems and Oracle Label Security. Role-Based Access Control (RBAC): privileges collected into roles, roles granted to users — the practical model everywhere.
• Privilege types: *system privileges* (CREATE TABLE, CREATE SESSION, SHUTDOWN) and *object privileges* (SELECT, INSERT, UPDATE, DELETE, REFERENCES, EXECUTE on a specific object).
• GRANT SELECT, INSERT ON orders TO clerk_role; and REVOKE INSERT ON orders FROM clerk_role;
• WITH GRANT OPTION lets the grantee pass the privilege on; revoking it cascades — dependent grants disappear. This cascade is a favourite exam question.
• WITH ADMIN OPTION is the same idea for system privileges and roles.
• Views as a security mechanism: grant SELECT on a view that hides columns (salary) or rows (WHERE branch = 'Kochi') instead of on the base table — column-level and row-level security without changing the table. Modern engines also offer native Row-Level Security policies (PostgreSQL, SQL Server) and dynamic data masking.
• Least privilege and separation of duties: application connects as a low-privilege user; no application uses root/sa/SYSTEM; DBA accounts are personal and audited, not shared.
• Hardening checklist: drop sample schemas, close default accounts, force TLS for client connections, encrypt data at rest (TDE), restrict network access to the DB port, password policy/profiles, no DB server exposed to the public internet.

Backup, recovery and the log

• Why the log matters: recovery is driven by the write-ahead log (Oracle redo, PostgreSQL WAL, MySQL binlog + InnoDB redo, SQL Server transaction log). On restart the engine redoes committed work and undoes uncommitted work; checkpoints bound how much redo must be replayed.
• Backup types:
  • Physical — copies of datafiles/blocks (Oracle RMAN, pg_basebackup, Percona XtraBackup, SQL Server BACKUP DATABASE). Fast to restore, engine/version specific.
  • Logical — exported SQL or rows (mysqldump, pg_dump, Oracle Data Pump expdp). Portable, selective, slow to restore large data.
  • Full / Differential / Incremental: full = everything; differential = all changes since the last full; incremental = changes since the last backup of any type. Restore chain: full + latest differential (+ logs), or full + every incremental in order (+ logs).
  • Hot (online) backup with the database open versus cold (offline) backup with it shut down; consistent versus inconsistent (an inconsistent backup needs archived logs to become usable).
• Archive/continuous log shipping is what makes point-in-time recovery (PITR) possible: restore the last full backup, then replay logs up to the instant *before* the bad transaction (RECOVER UNTIL TIME, PostgreSQL recovery_target_time, SQL Server STOPAT).
• RPO (recovery point objective) = how much data you can afford to lose; RTO (recovery time objective) = how long you may be down. These two numbers drive the whole strategy — log shipping every 5 minutes gives RPO ≈ 5 min; synchronous standby gives RPO ≈ 0.
• Failure types examined: transaction failure (rollback), system/instance failure (crash recovery from the log), media failure (restore + recover), and user error/logical corruption (PITR, or Oracle Flashback).
• 3-2-1 rule: three copies, two media, one off-site/different region — and a backup is not a backup until a restore has been rehearsed, with the restore time measured.
• Retention and law: financial records in India are generally kept for several years (Companies Act 2013 requires books of account to be preserved for eight years; tax and sectoral rules may be longer) — the DBA implements retention, legal decides it.

Storage, space and the data dictionary

• Logical to physical mapping (Oracle terms, the ones usually examined): database → tablespaces → segments (a table, an index) → extents → data blocks; a tablespace maps to one or more datafiles. SQL Server uses filegroups/files/pages (8 KB); PostgreSQL uses tablespaces and 8 KB pages; InnoDB uses tablespaces and 16 KB pages.
• Sizing tasks: monitor free space, autoextend and MAXSIZE, high-water mark, table and index growth per month, archive-log destination fill (a full archive destination hangs an Oracle database — a classic incident).
• Fragmentation and bloat: deletes leave space behind. PostgreSQL needs VACUUM (to reclaim dead tuples and prevent transaction-ID wraparound) and ANALYZE (statistics); VACUUM FULL rewrites the table and takes an exclusive lock. Oracle/SQL Server rebuild or reorganise indexes.
• Data dictionary / system catalog: metadata the DBA queries daily — Oracle DBA_TABLES, DBA_USERS, DBA_FREE_SPACE, V$SESSION, V$SQL; PostgreSQL pg_stat_activity, pg_class, information_schema; MySQL INFORMATION_SCHEMA, performance_schema; SQL Server sys.dm_exec_requests. The INFORMATION_SCHEMA views are the SQL-standard portable subset.
• Partitioning (range, list, hash) is an administrative tool as much as a design one: drop last year's partition instead of deleting 400 million rows, and back up/compress old partitions separately.

Performance administration

• Workflow: measure → find the top consumers → read the plan → fix the biggest item → re-measure. Never tune by changing many parameters at once.
• Where time goes: CPU, I/O waits, lock/latch waits, network round trips. Identify the wait class first (Oracle ASH/AWR, pg_stat_activity.wait_event, SQL Server wait stats, MySQL performance_schema).
• Slow-query capture: MySQL slow query log + pt-query-digest, PostgreSQL log_min_duration_statement and pg_stat_statements, Oracle AWR top SQL.
• Plans: EXPLAIN / EXPLAIN ANALYZE / EXPLAIN PLAN FOR + DBMS_XPLAN. Watch for full scans on large tables, wrong join order, nested loops over big row counts, and a large gap between *estimated* and *actual* rows — which usually means stale statistics.
• Optimiser statistics (row counts, distinct values, histograms) are maintained by the DBA: ANALYZE, DBMS_STATS.GATHER_TABLE_STATS, autovacuum's analyze. A cost-based optimiser with stale stats picks bad plans.
• Memory and cache: buffer cache/buffer pool hit ratio, sort area, work_mem; a low hit ratio with heavy physical reads usually means an undersized cache or a missing index — hit ratio alone is a weak metric, use waits.
• Connection management: too many connections cost memory and context switches; use a pooler (HikariCP, PgBouncer, ProxySQL) rather than raising max_connections indefinitely.
• Locking and blocking: find the blocker, not the victim; set lock timeouts; keep transactions short; watch long-running idle-in-transaction sessions (they also block VACUUM).
• Maintenance jobs: statistics refresh, index rebuild, purge/archival, backup verification, log rotation — scheduled off-peak via DBMS_SCHEDULER, cron, pg_cron or SQL Agent.

High availability, scaling and cloud

• Replication: *synchronous* (commit waits for the standby — RPO 0, higher latency) versus *asynchronous* (fast, small data-loss window). *Physical/streaming* (block or WAL level, whole cluster) versus *logical* (per table/row; allows cross-version and selective replication).
• Topologies: primary–standby with automatic failover (Patroni, Oracle Data Guard + Fast-Start Failover, SQL Server Always On availability groups, MySQL InnoDB Cluster/Group Replication); shared-storage clustering (Oracle RAC — active-active instances on one database).
• Read scaling: route reports to read replicas; accept replica lag (monitor it — an eventually consistent read replica must not serve a balance check).
• Sharding splits data across independent databases by key; the DBA then owns N databases, resharding and cross-shard consistency.
• Switchover is planned, failover is unplanned; split-brain is two primaries accepting writes — prevented by fencing/quorum.
• Managed cloud databases (Amazon RDS/Aurora, Azure SQL Database, Google Cloud SQL) remove patching, backups and failover plumbing; the DBA still owns schema, indexes, access, parameter groups, cost (IOPS and instance class) and monitoring. Indian deployments commonly use Mumbai (ap-south-1) and Hyderabad regions.
• Indian regulatory context: the Digital Personal Data Protection Act, 2023 governs personal data — purpose limitation, security safeguards, breach notification to the Data Protection Board, and erasure on withdrawal of consent; its rules were notified in 2025 with phased timelines, so check the current commencement dates before quoting deadlines. RBI's 2018 payment-data storage directive requires payment system data to be stored only in India. CERT-In's 2022 directions require reporting specified cyber incidents within 6 hours and retaining ICT logs for 180 days within India. Sectoral rules (RBI, SEBI, IRDAI, UIDAI/Aadhaar) add their own audit and encryption requirements; ISO/IEC 27001 is the usual certification and IS/ISO/IEC 27001 is its Indian adoption.

Key points

• DBA - owns the running database instance: availability, security, recoverability, performance, capacity.
• Authentication vs authorisation - identity verification versus permitted operations; never use the words interchangeably.
• RBAC - grant privileges to roles, roles to users; revoking a role instantly removes everyone's access.
• GRANT … WITH GRANT OPTION - lets the grantee re-grant; a REVOKE then cascades to all dependent grants.
• View-based security - a view with selected columns/rows gives fine-grained access without touching the base table.
• Least privilege - applications connect with the smallest privilege set; no app runs as root/sa/SYSTEM.
• Write-ahead log - every change is logged before it is applied, which is what makes redo/undo recovery possible.
• Checkpoint - a known-good point that limits how much log must be replayed after a crash.
• Physical vs logical backup - datafile copies (fast restore, version-bound) versus exported SQL (portable, slow for big data).
• Full / differential / incremental - differential grows but needs only one restore step; incremental is smallest but needs the whole chain.
• PITR - full backup + archived logs replayed to a chosen instant; the only cure for a wrong DELETE that was committed.
• RPO / RTO - allowable data loss and allowable downtime; they, not habit, decide the backup and HA design.
• Untested backup - not a backup; rehearse restores and record the measured restore time.
• Data dictionary - metadata catalog (DBA_*/V$, pg_stat_*, INFORMATION_SCHEMA) that the DBA queries to see the system.
• Optimiser statistics - stale stats are the most common cause of a suddenly slow query; a big estimate-vs-actual row gap is the signal.
• Synchronous vs asynchronous replication - RPO 0 at the cost of commit latency, versus fast commits with a loss window.
• Switchover vs failover - planned role change versus unplanned promotion after failure; split-brain is the hazard.
• VACUUM / ANALYZE - PostgreSQL reclaims dead tuples (and avoids XID wraparound) and refreshes statistics.
• DPDP Act 2023, CERT-In 6-hour reporting, 180-day log retention, RBI payment-data localisation - the Indian compliance frame the DBA implements.

Formulas and examples

Example 1 — availability and downtime budget. A banking database fails on average once every 2,500 hours and takes 2.5 hours to recover.

• A = 2,500 / (2,500 + 2.5) = 2,500 / 2,502.5 = 0.999001 → 99.9001 %.
• Allowed downtime at 99.9 % = 0.001 × 8,760 = 8.76 h/year; this system loses about 8.75 h/year, so it just meets a 99.9 % SLA and fails a 99.95 % one (4.38 h).
• To reach 99.99 % (0.876 h/year) with the same MTBF, MTTR must fall to about 0.25 h — i.e. automatic failover, not manual restore.

Example 2 — RPO/RTO from a backup plan. Full backup Sunday 01:00 (900 GB), differential daily 01:00 (grows ~60 GB/day), archived logs shipped every 15 minutes. Corruption is detected Thursday at 14:40; the last log arrived at 14:30.

• Restore chain: Sunday full + Thursday differential (the latest, taken 01:00) + logs from Thursday 01:00 to the chosen stop point.
• Data at risk = changes after 14:30 → RPO ≈ 10 minutes at the moment of failure (worst case 15 min).
• RTO: restore 900 GB at 250 MB/s = 900,000/250 = 3,600 s = 1.0 h; differential 240 GB (4 days × 60 GB) at 250 MB/s = 960 s = 0.3 h; replay 13.7 h of logs at 10× speed ≈ 1.4 h; checks 0.4 h → RTO ≈ 3.1 h.
• If the business demands RTO 1 h, the fix is a standby database (promote in minutes), not a faster tape.

Example 3 — backup storage sizing. Database 900 GB, daily change 3 %, one weekly full + six daily incrementals, retention 4 weeks, compression 2:1.

• Daily incremental ≈ 0.03 × 900 = 27 GB → 6 × 27 = 162 GB per week of incrementals.
• One week's set = 900 + 162 = 1,062 GB; four weeks = 4,248 GB uncompressed.
• At 2:1 compression → 2,124 GB ≈ 2.1 TB. On cloud object storage at ₹2 per GB-month, ≈ 2,124 × 2 = ₹4,248 per month (illustrative rate — current list prices vary by provider and storage class).

Example 4 — growth and capacity planning. A table holds 40 million rows; average row 180 bytes plus 20 bytes overhead; two indexes add 35 % of table size; data grows 6 % per month.

• Table ≈ 40,000,000 × 200 B = 8,000,000,000 B = 8.0 GB; indexes = 0.35 × 8.0 = 2.8 GB → 10.8 GB today.
• After 12 months: 10.8 × (1.06)¹² = 10.8 × 2.0122 = 21.7 GB; allow 1.3× free space → provision about 28 GB for this object.
• At 6 % per month the data doubles in about 12 months (1.06¹² ≈ 2.01) — the number to quote in a capacity review.

Example 5 — reading a plan after a statistics refresh. A query on a 20-million-row orders table returns 1,200 rows but the plan shows a full table scan with an estimate of 2 million rows.

• Estimated/actual = 2,000,000/1,200 ≈ 1,667× over-estimate → stale or missing statistics on the filtered column.
• ANALYZE orders; (or DBMS_STATS.GATHER_TABLE_STATS), re-plan: optimiser now estimates ~1,300 rows and chooses an index range scan.
• Measured effect: logical reads fall from 250,000 to 1,400; if a block read costs 8 µs, time falls from 2.0 s to about 0.011 s.

-- role-based access, least privilege
CREATE ROLE app_rw;
GRANT SELECT, INSERT, UPDATE ON orders, order_items TO app_rw;
CREATE USER app_user WITH PASSWORD '***';
GRANT app_rw TO app_user;                 -- app never owns the tables

-- column security through a view
CREATE VIEW emp_public AS
  SELECT emp_id, name, dept, city FROM employee;   -- salary not exposed
GRANT SELECT ON emp_public TO analyst_role;

-- revoke cascades to grants the clerk passed on
REVOKE SELECT ON orders FROM clerk_role CASCADE;

-- who is running what, and for how long (PostgreSQL)
SELECT pid, usename, state, now() - query_start AS runtime, query
FROM pg_stat_activity
WHERE state <> 'idle' ORDER BY runtime DESC;

-- point-in-time recovery, PostgreSQL recovery settings
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-11-04 14:29:00+05:30'
recovery_target_action = 'promote'

Where it is used

• Banking and payments: core banking on Oracle RAC/Data Guard, UPI and switch databases with synchronous standbys; RBI payment-data localisation and audit trails — HDFC Bank, SBI, ICICI, NPCI and their vendors (TCS BaNCS, Infosys Finacle) hire DBAs heavily.
• Telecom and e-commerce: Jio, Airtel, Flipkart, Zomato, Swiggy run sharded MySQL/PostgreSQL fleets with read replicas, pooling and automated failover — "database reliability engineer" roles.
• IT services and GCCs: TCS, Infosys, Wipro, HCLTech, Cognizant, Capgemini, LTIMindtree staff 24×7 DBA support pods for overseas clients (shift roster, ticket SLAs, patch calendars).
• Government and public digital infrastructure: Aadhaar/UIDAI, GSTN, IRCTC, NIC data centres and state e-governance databases, with CERT-In incident reporting and log-retention duties.
• Health, insurance and SaaS products: per-tenant schemas, data masking in test copies, PITR for tenant-level mistakes; DPDP-driven consent and erasure handling.
• Certifications that recruiters ask for: Oracle Certified Professional DBA, Microsoft Azure Database Administrator Associate, AWS Database Specialty, PostgreSQL professional certifications.

Interview questions

• Q: What is the difference between authentication and authorisation? A: Authentication verifies identity (password, LDAP, certificate); authorisation decides which operations that identity may perform, through privileges and roles.
• Q: Why use roles instead of granting privileges directly to users? A: Roles centralise the privilege set, so adding or removing a person is one statement and an audit can be done per role rather than per user.
• Q: What happens when you revoke a privilege that was granted WITH GRANT OPTION? A: The revoke cascades — privileges the grantee had passed on are also removed, which can unexpectedly break other users' access.
• Q: How can you stop a user seeing the salary column without splitting the table? A: Grant SELECT on a view that excludes salary (or use column-level privileges / dynamic data masking) and remove direct privileges on the base table.
• Q: Differentiate differential and incremental backups. A: A differential holds all changes since the last full (restore = full + latest differential); an incremental holds changes since the previous backup of any type (restore = full + every incremental in order).
• Q: A committed DELETE removed 50,000 rows an hour ago. How do you recover? A: Point-in-time recovery — restore the last full backup and replay archived logs to just before the DELETE (or use Oracle Flashback Table/Query if available), ideally into a clone and then copy the rows back.
• Q: What are RPO and RTO, and who sets them? A: RPO is the acceptable data loss, RTO the acceptable downtime; the business sets them and the DBA designs the backup/HA architecture to meet them.
• Q: Why do people say an untested backup is not a backup? A: Backups fail silently — missing files, broken incremental chains, unreadable media — and only a rehearsed restore proves recoverability and measures the real RTO.
• Q: A query that ran in 50 ms now takes 30 s with no code change. First checks? A: Compare the execution plan with the earlier one, check optimiser statistics and data volume growth, look for a dropped/invisible index, blocking locks, and parameter or memory changes.
• Q: Is a high buffer cache hit ratio proof of good tuning? A: No — a badly written query doing repeated full scans of cached blocks shows a high ratio while wasting CPU; wait events and logical-read counts are better evidence.
• Q: Difference between switchover and failover, and what is split-brain? A: Switchover is a planned, lossless role change; failover is an unplanned promotion after failure; split-brain is two nodes accepting writes at once, prevented by quorum/fencing.
• Q: What does VACUUM do in PostgreSQL and why can't you skip it? A: It reclaims space from dead tuples left by updates/deletes, updates visibility information and prevents transaction-ID wraparound; skipping it causes bloat and eventually forced shutdown.
• Q: Which Indian rules affect how you run a production database? A: The DPDP Act, 2023 (security safeguards, breach reporting, erasure - from 13 May 2027 under the DPDP Rules 2025; until then IT Act s.43A and the SPDI Rules 2011), CERT-In's 2022 directions (incident reporting within 6 hours, 180-day log retention in India) and RBI's payment-data localisation directive for payment system data.

Common mistakes

• Confusing authorisation with authentication, or claiming a view "encrypts" data — a view restricts visibility, it does not protect data at rest.
• Writing that an incremental restore needs only the last incremental — the entire chain since the full is required.
• Forgetting that archived/transaction logs, not the backup alone, provide point-in-time recovery; an inconsistent hot backup without logs is unusable.
• Treating a backup job's "success" message as proof of recoverability, and never rehearsing a restore or measuring RTO.
• Letting applications connect as a superuser (root/sa/SYSTEM) "for convenience", then being unable to audit or limit damage.
• Tuning by changing several memory parameters or adding many indexes at once, with no baseline measurement — and forgetting that every extra index slows INSERT/UPDATE.
• Ignoring maintenance work that is invisible until it bites: stale statistics, PostgreSQL VACUUM, a full archive-log destination, expiring TLS certificates, unapplied security patches.
• Quoting availability figures without stating MTTR — 99.99 % is unreachable with a manual multi-hour restore however good the backups are.

Related topics

Transactions, ACID and indexing · SQL queries and joins · NoSQL databases · Memory management and virtual memory · Processes and threads · Data warehousing and ETL