Advanced Database Concepts: ADR, Multitenant, Security, Tuning & Networking

TAKEAWAYS

• Oracle’s Automatic Diagnostic Repository (ADR) centralizes logs, traces, and alerts for troubleshooting, replacing manual file management.
• Multitenant architecture (CDB/PDB) isolates databases as pluggable containers, enabling efficient resource sharing and compliance (e.g., NEPSE’s regulatory separation).
• Security relies on roles, privileges, and profiles (e.g., RESOURCE role for Daraz’s order-processing users).
• Performance tuning targets SGA/PGA memory, SQL execution plans, and indexes (e.g., optimizing Ncell’s billing queries).
• Networking uses protocols (TCP/IP, Oracle Net) and listeners to connect remote clients (e.g., eSewa’s API calls to bank databases).
• Job scheduling automates backups and maintenance (e.g., Khalti’s nightly transaction reconciliation).

1. Automatic Diagnostic Repository (ADR): Oracle’s Troubleshooting Hub

Oracle’s ADR is a filesystem-based repository that automatically collects:

• Alert logs (critical errors, crashes)
• Trace files (SQL execution details)
• Incident dumps (memory corruption, hangs)
• Configuration snapshots

How ADR Works

stateDiagram-v2
    [*] --> ADR_HOME: Created on DB startup
    ADR_HOME --> ADRCI: Command-line tool
    ADRCI --> ADR: Logs/Traces
    ADR --> DBA: Alerts via EM or SQL
    ADR --> [*]: Retained for 30 days (configurable)

Key Components:

Example: If Ncell’s database crashes during peak hours, ADR’s incident_12345.trc reveals a missing index on customer_transactions, guiding the DBA to rebuild it.

2. Multitenant Architecture: CDB and PDB

Oracle’s Container Database (CDB) hosts multiple Pluggable Databases (PDBs), enabling:

• Resource isolation (e.g., NEPSE’s regulatory PDB vs. internal analytics PDB).
• Simplified upgrades (patch one CDB, not each database).
• Efficient storage (shared undo/redo tablespaces).

CDB vs. PDB: Key Differences

Worked Example: Creating a CDB for a Nepalese bank

-- Step 1: Create CDB (as SYSDBA)
CREATE DATABASE CDB_BANK
USER SYS IDENTIFIED BY manager123
USER SYSTEM IDENTIFIED BY admin123
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/CDB_BANK/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/CDB_BANK/redo02.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/CDB_BANK/system01.dbf' SIZE 2G AUTOEXTEND ON
SYSAUX DATAFILE '/u01/app/oracle/oradata/CDB_BANK/sysaux01.dbf' SIZE 2G AUTOEXTEND ON
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/CDB_BANK/users01.dbf' SIZE 1G AUTOEXTEND ON
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/CDB_BANK/temp01.dbf' SIZE 500M AUTOEXTEND ON
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/CDB_BANK/undotbs01.dbf' SIZE 1G AUTOEXTEND ON;

-- Step 2: Create a PDB for retail loans
CREATE PLUGGABLE DATABASE PDB_RETAIL_LOANS
ADMIN USER retail_admin IDENTIFIED BY loan123
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB_BANK/', '/u01/app/oracle/oradata/CDB_BANK/pdb_retail/')
STORAGE (MAX_SIZE 10G)
ROLE=SEED;

-- Step 3: Verify
ALTER SESSION SET CONTAINER = PDB_RETAIL_LOANS;
SELECT name, open_mode FROM v$pdbs;

3. Database Security: Roles, Privileges, and Profiles

Oracle enforces security via:

1. System Privileges: CREATE SESSION, DROP TABLE.
2. Object Privileges: SELECT on customers, EXECUTE on procedures.
3. Roles: Predefined (CONNECT, RESOURCE) or custom (e.g., DARAZ_ORDER_ROLE).
4. Profiles: Password policies (e.g., PASSWORD_LIFE_TIME=90).

Granting Privileges with Examples

-- Grant a role to a user (e.g., Daraz’s order processor)
GRANT CONNECT, RESOURCE TO daraz_user;

-- Grant object privileges (e.g., read-only access to NEPSE’s stock data)
GRANT SELECT ON nse.stock_prices TO nse_analyst;

-- Create a custom role for Pathao’s drivers
CREATE ROLE pathao_driver_role;
GRANT SELECT, INSERT ON trips TO pathao_driver_role;
GRANT pathao_driver_role TO driver_ram;

Real-World Tie: Ncell’s Billing System

• Role: BILLING_AUDITOR (grants SELECT on customer_bills but not UPDATE).
• Profile: PASSWORD_REUSE_TIME=180 (forces password changes every 6 months).

4. Performance Tuning: SGA, PGA, and SQL Optimization

Memory Structures

Key Tuning Parameters:

Worked Example: Optimizing NEPSE’s Stock Query

-- Before (full table scan)
SELECT * FROM stock_prices WHERE trade_date = SYSDATE - 1;

-- After (indexed access)
CREATE INDEX idx_stock_date ON stock_prices(trade_date);
EXPLAIN PLAN FOR SELECT * FROM stock_prices WHERE trade_date = SYSDATE - 1;
-- Plan shows "INDEX RANGE SCAN" instead of "FULL TABLE SCAN".

5. Network Configuration: Connecting to Oracle

Oracle uses Oracle Net Services (formerly Net8) to enable remote connections via:

• TCP/IP (standard for client-server).
• Named Pipes (Windows-specific).
• Bequeath (local OS authentication).

Listener Configuration

sequenceDiagram
    Client->>Listener: Connect (TCP:1521)
    Listener->>Client: Validate (tnsnames.ora)
    Client->>Listener: Authenticate (username/password)
    Listener->>Database: Forward request
    Database->>Listener: Return result
    Listener->>Client: Deliver response

Key Files:

Real-World Tie: eSewa’s API Calls

• Uses tnsnames.ora to connect to Nabil Bank’s PDB via eSewa=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bank.nabil.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=NABIL_CDB))).
• SSL: SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1) ensures secure transactions.

6. Job Scheduling: Automating Database Tasks

Oracle’s DBMS_SCHEDULER automates:

• Backups (e.g., Khalti’s nightly transaction logs).
• Index rebuilds (e.g., Daraz’s daily sales data).
• Purges (e.g., Ncell’s old call-detail records).

Example: Schedule a backup at 2 AM

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'NIGHTLY_BACKUP',
    job_type        => 'SQL_SCRIPT',
    job_action      => 'BACKUP DATABASE PLUS ARCHIVELOG',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled         => TRUE,
    comments        => 'Full backup for Ncell database'
  );
END;
/

In the Real World

1. NEPSE’s Multitenant Setup

   • Uses a CDB with PDBs for:
     • PDB_REGULATORY (audit trails for SEBON compliance).
     • PDB_ANALYTICS (stock trend models).
   • Why? Isolates sensitive data while sharing the same Oracle license.

2. Khalti’s Security Model

   • Roles:
     • MERCHANT_ROLE (grants INSERT on transactions).
     • AUDITOR_ROLE (grants SELECT on fraud_logs).
   • Profiles:
     • PASSWORD_VERIFY_FUNCTION=ORACLE12C (enforces complexity rules).

3. Ncell’s Performance Tuning

   • SGA: SHARED_POOL_SIZE=4G to cache frequent SQL (e.g., SELECT * FROM customer_bills WHERE status='ACTIVE').
   • PGA: PGA_AGGREGATE_TARGET=8G for parallel query execution during peak hours (6–9 PM).

Exam Tip

1. ADR Questions

   • Expect commands like ADRCI SHOW HOME or ADRCI SHOW ALERT -tail.
   • Common Pitfall: Forgetting to enable ADR (ALTER SYSTEM SET diagnostic_dest='...').

2. Multitenant (CDB/PDB)

   • Must-Know Commands:

     -- Create CDB
     CREATE DATABASE CDB_NAME ...
     -- Plug/unplug PDB
     CREATE PLUGGABLE DATABASE PDB_NAME ADMIN USER ...
     ALTER PLUGGABLE DATABASE PDB_NAME OPEN;
     

   • Exam Trap: Confusing ALTER SESSION SET CONTAINER=PDB_X with ALTER DATABASE.

3. Security

   • Privilege Hierarchy: SYSTEM → DBA → RESOURCE → CONNECT → Object Privileges.
   • Example Question: "How would you grant a user read-only access to NEPSE’s stock_prices table?" Answer:

     GRANT SELECT ON nse.stock_prices TO analyst_user;
     

4. Performance Tuning

   • AWR vs. ASH:
     • AWR (Automatic Workload Repository): Historical metrics (retention 8 days).
     • ASH (Active Session History): Real-time snapshots (1-minute granularity).
   • Exam Tip: Use DBMS_WORKLOAD_REPOSITORY to enable AWR if disabled.

5. Networking

   • Listener Troubleshooting:

     -- Check listener status
     LSNRCTL STATUS
     -- Test connectivity
     TNSPING nepse.db
     

   • Common Error: ORA-12541: TNS:no listener → Fix: Restart listener (LSNRCTL START).

6. Job Scheduling

   • Key Views:
     • USER_SCHEDULER_JOBS (list jobs).
     • DBA_SCHEDULER_JOB_LOG (check execution history).
   • Exam Question: "How would you disable a job named DAILY_BACKUP?" Answer:

     BEGIN
       DBMS_SCHEDULER.DISABLE('DAILY_BACKUP');
     END;