Oracle Database Security Hardening & Performance Tuning

 

1. Introduction

Modern enterprise databases must balance two critical priorities: strong security and optimal performance. Database administrators are responsible not only for protecting data but also for ensuring efficient query execution, workload stability, and proactive monitoring.

This document provides a practical, hands-on guide to Oracle Database Security Hardening and Performance Tuning within a multi-tenant (CDB/PDB) architecture. It covers key administrative domains including user and role management, database security controls, DBSAT assessment, SQL tuning, AWR analysis, and ADDM recommendations.


The goal of this guide is to present a real-world, production-oriented approach that reflects actual Oracle DBA responsibilities. It demonstrates how to secure database environments, assess risks, monitor workload behavior, and optimize SQL performance using native Oracle tools.

Based on practical implementation scenarios, this documentation serves as a structured reference for Oracle DBAs and learners who want a clear understanding of both database hardening and performance optimization in modern enterprise environments.


2. Objective

The primary objective of this document is to:

  • Implement database security hardening best practices
  • Perform privilege and role management
  • Assess database security posture using DBSAT
  • Identify and resolve SQL performance issues
  • Analyze database workload using AWR and ADDM
  • Improve overall database performance and stability
  • Provide a structured, real-world DBA reference guide

3. Scope

This document covers:

  • User and role management best practices
  • Privilege control and least privilege model
  • Database security hardening techniques
  • Oracle Database Security Assessment Tool (DBSAT) usage
  • SQL performance tuning techniques
  • AWR report analysis
  • ADDM diagnostics and recommendations
  • End-to-end DBA operational workflow

4. Environment Details

Component

Details

Database Architecture

Multi-tenant (CDB/PDB)

CDB Name

PROD

PDBs

PRODPDB,  TESTPDB

Storage

ASM Disk Groups

Deployment

Clustered Database Environment

OS User

oracle

Monitoring Tools

AWR, ADDM, ASH

Security Tool

DBSAT

 

5. Prerequisites

Before implementation, ensure the following:

  • Oracle Database installed and configured
  • Multi-tenant architecture enabled (CDB/PDB)
  • SYSDBA privileges available
  • AWR snapshots enabled
  • Diagnostic Pack licensed (for AWR/ADDM usage)
  • DBSAT utility downloaded and configured
  • OS-level access for running DBSAT collector
  • Basic understanding of Oracle performance views

6. Use Cases

This implementation is useful in the following scenarios:

  • Database security audits
  • Pre-production hardening
  • Compliance readiness (ISO, SOC, etc.)
  • Performance troubleshooting
  • Production health checks
  • Migration readiness validation
  • DBA knowledge building and documentation

7. Methodology

The implementation follows a structured and layered approach:

1.    Secure user and role configuration

2.    Apply database security hardening measures

3.    Run DBSAT to assess security posture

4.    Identify risky privileges and sensitive data exposure

5.    Generate and analyze AWR reports

6.    Identify top SQL performance bottlenecks

7.    Use ADDM findings for root cause analysis

8.    Apply SQL tuning and configuration improvements

9.    Validate performance improvements

8. Implementation Steps

This section describes the practical implementation workflow for Oracle Database Security Hardening and Performance Tuning in a multi-tenant environment.

 

Environment Validation and Database Readiness Check



All critical components were validated successfully:

✅ Clusterware healthy
✅ RAC nodes operational
✅ ASM disk groups mounted
✅ Database instances running on both nodes
✅ Multi-tenant architecture validated
✅ PDBs open and operational

The environment is confirmed as:

Production-ready and stable for security hardening and performance tuning implementation.

 

8.1: User and Role Management

1. Overview

The first phase of database security hardening focuses on implementing strong identity and access management using the least privilege principle. Proper user and role management ensures that only authorized users can access database resources with minimal required privileges.

In multi-tenant architectures, access control must be carefully designed at both the Container Database (CDB) and Pluggable Database (PDB) levels to prevent privilege escalation and reduce attack surfaces.

 

2. Objective

The objective of this phase is to:

  • Implement secure password policies
  • Enforce role-based access control (RBAC)
  • Create application users with minimal privileges
  • Separate system users and application users
  • Enforce security controls at the PDB level

This ensures controlled and auditable database access.

 

3. Why This Is Important

Improper user management is one of the most common security risks in Oracle environments. Overprivileged users can lead to:

  • Unauthorized data access
  • Privilege escalation
  • Compliance violations
  • Increased attack surface

Implementing RBAC and password policies ensures a secure and compliant environment.

 

4. Implementation Steps

Step 1: Switch to Target PDB

User and role creation was performed at the pluggable database level to isolate application access.

This ensures that the security configuration is applied locally within the PDB.

 

Step 2: Create Secure Password Profile

A strong password profile was created to enforce authentication policies.

Security Controls Implemented:

  • Account lock after 5 failed logins
  • Mandatory password rotation (90 days)
  • Password reuse restrictions
  • Complexity validation using Oracle verify function

This enforces enterprise-grade password security.

 

Step 3: Create Application User

A dedicated application user was created with controlled resource usage.

This ensures:

  • Resource isolation
  • Password policy enforcement
  • Controlled tablespace usage

 

Step 4: Grant Minimum Required Privileges

The user was granted only the required login privilege.

This follows the least privilege model.

 

Step 5: Implement Role-Based Access Control (RBAC)

A read-only role was created to avoid direct privilege assignment.

Benefits of RBAC:

  • Centralized privilege management
  • Easier audits
  • Simplified privilege revocation
  • Improved scalability

 

5. Validation

The user and role creation were verified successfully at the PDB level.

Key validations performed:

  • Profile assigned correctly
  • Role inheritance confirmed
  • Session creation privileges validated

This confirms successful RBAC implementation.

 

6. Best Practices Followed

  • Created users at PDB level (not CDB root)
  • Implemented strong password policies
  • Used role-based privilege assignment
  • Avoided direct system privileges
  • Followed least privilege model

These practices align with enterprise Oracle security standards.

 

7. Key Outcome

After this implementation:

✅ Secure authentication enforced
✅ Controlled application access
✅ Role-based privilege model implemented
✅ Reduced privilege exposure

This establishes a strong identity and access management foundation for further hardening.

 

8.2: Security Hardening

1. Overview

Account hardening is a critical step in Oracle database security that focuses on minimizing the attack surface by disabling unused, default, and sample accounts. Many Oracle installations include pre-created schemas and legacy accounts that may pose security risks if left enabled.

This implementation ensures that only necessary and actively used accounts remain accessible across both Container Database (CDB) and Pluggable Databases (PDBs).

 

2. Objective

The objective of account hardening is to:

  • Identify Oracle-maintained and custom accounts
  • Lock unused default accounts
  • Disable legacy schemas
  • Harden both CDB and PDB layers
  • Prevent unauthorized lateral access

This significantly reduces database exposure.

 

3. Why Account Lockdown Is Important

Unused accounts are a common entry point for attackers due to:

  • Default credentials
  • Weak password policies
  • Legacy dependencies
  • Misconfigurations

Locking unused accounts ensures:

  • Reduced attack surface
  • Better compliance readiness
  • Improved audit posture
  • Stronger access governance

 

4. Implementation Steps

Step 1: Identify Oracle-Maintained Accounts

Switched to root container to identify Oracle-managed users.

 

 

This distinguishes system-managed accounts from user-created accounts.

Step 2: Identify Non-Oracle Accounts

Custom users were identified for review.



This helped identify:

  • Application schemas
  • Test users
  • Sample schemas (HR, SCOTT)

 

 

Step 3: Lock Unused System Accounts

Non-required system accounts were locked at the CDB level.

This prevents unauthorized use of legacy accounts.

 

Step 4: Lock Sample Schemas in PDB

Switched to the target PDB and locked sample users.



This eliminates risks from:

  • Demo schemas
  • Default passwords
  • Legacy objects

 

Step 5: Cross-Container Validation

Performed container-wide validation to ensure consistent lockdown.

 

This confirmed account lockdown across:

  • CDB$ROOT
  • PRODS
  • TESTPDB

 

5. Best Practices Followed

  • Locked unused default accounts
  • Disabled sample schemas
  • Hardened both CDB and PDB levels
  • Verified lockdown across containers
  • Avoided locking critical Oracle-managed accounts blindly

 

6. Key Outcome

After implementing account hardening:

✅ Reduced attack surface
✅ Disabled legacy and unused accounts
✅ Eliminated risks from sample schemas
✅ Enforced container-wide lockdown

This establishes a secure baseline before enabling auditing and compliance controls.

 

8.3: Database Auditing

1. Overview

Database auditing provides visibility into user activities and privileged operations. It enables tracking of security-sensitive actions such as configuration changes, user management, and data access, which is essential for compliance and forensic analysis.

In this implementation, traditional Oracle auditing was configured to monitor critical database activities.

Unified Auditing is centralized, efficient, and easier to manage, while Traditional Auditing is older, fragmented, and less performant.

 

2. Objective

The objective of auditing implementation is to:

  • Validate current auditing mode
  • Enable auditing for privileged operations
  • Track user and schema changes
  • Monitor sensitive data access
  • Establish audit visibility for compliance

This improves traceability and accountability.

 

3. Auditing Strategy

The auditing approach focused on:

  • Privileged operations monitoring
  • User lifecycle auditing
  • Data access auditing
  • Minimal performance overhead

This ensures balanced security and performance.

 

4. Implementation Steps

Step 1: Verify Auditing Mode

Validated whether Unified Auditing is enabled.



Result:
Unified Auditing was disabled.

Step 2: Check Traditional Auditing Configuration

Verified database auditing parameter.



Result:

  • audit_trail = DB
  • Traditional auditing enabled

This confirms audit records are stored in database tables.

 

Step 3: Enable Statement Auditing

Enabled auditing for critical administrative actions.

These actions help detect:

  • Unauthorized configuration changes
  • Suspicious user creation
  • Privilege misuse

 

Step 4: Enable Data Access Auditing

Enabled auditing for table access.


 


This ensures visibility into sensitive data access patterns.

 

Step 5: Validate Audit Configuration

Audit settings were verified using data dictionary views.

Validation confirmed:

  • Auditing enabled for both success and failure
  • Coverage for high-risk operations

5. Best Practices Followed

  • Verified auditing mode before configuration
  • Enabled targeted auditing (not blanket auditing)
  • Balanced visibility and performance
  • Audited high-risk operations first
  • Validated audit policies post-implementation

 

6. Key Outcome

After implementing auditing:

✅ Visibility into privileged operations
✅ Traceability for user management actions
✅ Monitoring of sensitive data access
✅ Improved compliance readiness
✅ Stronger forensic capabilities

This completes the foundational database security layer.

 

8.4: Run DBSAT Assessment

Oracle's Database Security Assessment Tool (DBSAT) is a utility designed to help database administrators evaluate the security posture of Oracle databases. It performs a comprehensive security scan and generates reports highlighting configuration issues, account weaknesses, and auditing gaps.

1 Overview

DBSAT provides:

  • Analysis of database configuration against security best practices.
  • Identification of weak passwords, user privileges, and roles.
  • Review of auditing and system privileges.
  • Exportable reports in HTML, TXT, JSON, and XLSX formats.

DBSAT supports both on-premises and cloud databases (like Oracle 19c in a multi-tenant environment).

 

2 Installation & Setup

1.    Prepare Environment

Ensure Java 1.8 is installed and set:

 

2.    Unpack DBSAT

 

3 Running DBSAT Collection

  • Execute the collector to scan the database:

Notes:

  • The collector connects as SYSDBA to retrieve configuration, user, privilege, and audit information.
  • Some OS-level checks may be skipped depending on environment permissions.
  • Output is generated in a compressed .zip file (dbsat_out.zip) and can be encrypted with a password.

 

4 Generating DBSAT Reports

  • After collection, generate the security report:

  • Reports are created in multiple formats:

Ø dbsat_out_report.html — Viewable in any browser.

Ø dbsat_out_report.txt — Plain text summary.

Ø dbsat_out_report.xlsx — Excel for analysis.

Ø dbsat_out_report.json — Machine-readable format.

The report can be transferred to a Windows workstation using WinSCP, FileZilla, or similar tools to review the HTML or TXT output.

 

 

5 Benefits of DBSAT

  • Provides comprehensive security assessment without manual querying.
  • Identifies misconfigurations and excessive privileges.
  • Helps ensure compliance with company and regulatory security policies.
  • Generates readable and actionable reports for auditors or management.

 

6 Best Practices

  • Run DBSAT regularly as part of database security audits.
  • Use the generated HTML/Excel reports to track remediation progress.
  • Avoid running on production during peak hours to reduce load.
  • Encrypt output files to protect sensitive information.

 

 

Security Remediation

Based on DBSAT findings, remediation is performed.

Common Remediation Tasks

  • Revoke excessive privileges
  • Mask sensitive data exposure
  • Implement auditing controls
  • Remove insecure configurations
  • Enforce password complexity

 

Example

REVOKE ANY TABLE FROM app_user;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 90;

 

Validation

  • Re-run DBSAT
  • Compare risk reduction

8.5: AWR Report Generation

AWR (Automatic Workload Repository) captures and stores performance statistics for Oracle databases. AWR reports help DBAs identify performance issues, workload patterns, and resource bottlenecks.

 

1 Overview

AWR (Automatic Workload Repository) is an Oracle database performance monitoring and tuning tool that collects, processes, and maintains performance statistics. It provides detailed insights into database workloads, helping DBAs identify bottlenecks and optimize system performance.

 

2 Objectives

  • Track database performance trends over time.
  • Identify high-load SQL statements and resource bottlenecks.
  • Support proactive performance tuning and capacity planning.
  • Provide historical performance data for troubleshooting.

 

3 Why Use AWR

  • Helps in diagnosing performance issues quickly.
  • Enables data-driven decisions for tuning SQL, memory, and I/O.
  • Facilitates trend analysis and historical comparisons.
  • Required for Enterprise Manager performance dashboards.

 

4 How It Works

1.    AWR captures snapshots of database performance statistics periodically (default every 60 minutes).

2.    Snapshots are stored in the SYSAUX tablespace within the DBA_HIST views.

3.    Reports are generated by selecting begin and end snapshot IDs for a given time period.

4.    Output formats: HTML, text, active-html.

 

5 Use Case

  • Multi-node RAC database experiencing intermittent performance degradation.
  • DBA wants a 7-day performance summary and to identify which SQL statements or resources are consuming the most time.

 

8.5.6 Implementation Steps

 

  • Report is generated in HTML format.
  • Transfer to local machine via WinSCP for review in a browser.

 

7 Best Practices

  • Run AWR reports periodically (weekly or monthly) for trend analysis.
  • For RAC, include all instances for complete performance visibility.
  • Archive AWR reports for historical reference.
  • Focus on top SQL, wait events, and load statistics.

 

8 Key Outcomes

  • Identifies high-load SQL and performance bottlenecks.
  • Provides resource utilization insights for tuning.
  • Helps in forecasting system scaling requirements.
  • Serves as a reference for ADDM analysis.

 

8.6: ADDM Analysis

1 Overview

ADDM (Automatic Database Diagnostic Monitor) analyzes AWR snapshots to provide actionable recommendations for improving Oracle database performance. ADDM highlights root causes of performance issues, eliminating manual analysis complexity.

 

2 Objectives

  • Automatically diagnose database performance issues.
  • Recommend tuning actions for SQL, memory, and I/O.
  • Quantify impact of performance problems.
  • Prioritize issues based on resource consumption.

 

3 Why Use ADDM

  • Reduces time and effort spent on manual performance analysis.
  • Provides instance-level and RAC-wide insights.
  • Helps proactively optimize system before end-user impact.
  • Supports capacity planning with workload impact projections.

 

4 How It Works

1.    ADDM reads AWR snapshots and calculates metrics such as:

o   Wait times

o   SQL execution times

o   I/O and memory statistics

2.    It generates recommendations such as:

o   SQL tuning

o   Index suggestions

o   Memory allocation adjustments

o   Load balancing across RAC nodes

3.    Reports are generated as text or HTML files.

5 Use Case

  • RAC database showing high CPU usage on prod1 node.
  • DBA runs ADDM for snapshots 100 → 103 to understand performance degradation and identify actionable recommendations.

 

6 Implementation Steps

  • Output: addm_rac_rpt.txt
  • Transfer to local machine for detailed review.

 

7 Best Practices

  • Always run AWR before ADDM to have snapshot data.
  • Use ADDM reports to implement recommendations incrementally.
  • Archive reports for audit and trend analysis.
  • Prioritize high-impact recommendations first (CPU, I/O, top SQL).

 

8 Key Outcomes

  • Provides root-cause analysis for performance issues.
  • Offers practical tuning recommendations.
  • Highlights system bottlenecks for proactive action.
  • Improves RAC instance balance and resource utilization.

 

 

 

AWR Remediation

Overview: Analyze database performance using AWR reports to identify bottlenecks.
Objective: Improve SQL, memory, and I/O efficiency.
Common Tasks:

  • Tune high-load SQL
  • Adjust SGA/PGA memory
  • Optimize I/O and storage
  • Balance RAC instance load

Example:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
CREATE INDEX idx_emp_dept ON emp(department_id);
ALTER SYSTEM SET sga_target=4G SCOPE=BOTH;

Validation:

  • Re-run AWR reports
  • Check CPU, I/O, and SQL improvements

 

ADDM Remediation

Overview: Use ADDM to get actionable performance recommendations.
Objective: Apply targeted fixes to reduce wait events and improve throughput.
Common Tasks:

  • Fix top wait events
  • Tune SQL statements
  • Adjust memory parameters
  • Implement ADDM suggested changes

Example:

-- Apply ADDM recommendations
EXEC DBMS_ADVISOR.IMPLEMENT_RECOMMENDATIONS('ADDMSQL_RECOMMEND');

Validation:

  • Re-run ADDM analysis
  • Compare improvements in response time and resource usage

8.7: SQL Tuning

Overview:
SQL Tuning improves query performance by reducing execution time, optimizing resource usage, and ensuring consistent execution plans.

 

Objective:

  • Reduce CPU, I/O, and memory usage
  • Minimize hard parses
  • Ensure consistent and efficient execution plans

 

Why SQL Tuning:
Even the same SQL can generate different PLAN_HASH_VALUEs due to changes in:

  • Table/index statistics
  • Execution plan changes (optimizer decisions)
  • Bind variable usage vs literals

 

Use Case:

  • High OLTP transaction systems
  • Reporting queries with large datasets
  • Queries showing variable execution times

 

Implementation Steps:

All SQL statements have to go through the following stages of execution:

1.Parse

  a.syntactic

  b.semantic check

2.Bind

3.Execute

4.Fetch

1) PARSE: Every SQL statement has to be parsed which includes checking the syntax, validating, ensuring that all references to objects are correct and ensuring that relevant privileges to those object exist.

 

Parsing -

1.syntactic

2.semantic check

 

u1-sql> select empname, job, sal, manager from emp where deptno=20;

 

syntactic check - standard for writing query

semantic check - object, users and their permission exist in DD

(The semantics check to confirm that the correct object name is used and also whether the user has the proper privileges to execute the query)

 

Hard Parse and Soft Parse –

If there is a fresh(new) query and its hash code does not exist in shared pool then that query has to pass through from the additional steps known as hard parsing otherwise if hash code exists then query does not passes through additional steps. It just passes directly to execution engine. This is known as soft parsing.

 

2) BIND: After parsing, the oracle server knows the meaning of the oracle statement but still may not have enough info(values for variables) to execute the statement. The process of obtaining these value is called as bind values.

 

SQL> select emp, ename, sal from emp where empno=7099;     

SQL> select emp, ename, sal from Emp where empno=7900;

SQL> select emp, ename, Sal from emp where deptno=8712;

 

SQL> variable deptno number

SQL> exec :deptno := 10          (exec : deptno := text1.value)

SQL> select emp, ename, sal from emp where deptno = :deptno;

 

 

3) EXECUTE: After binding, the Oracle server executes the statement.

 

4) FETCH: In the fetch stage, rows are selected and ordered and each successive row retrieves another row of the result until the last row has been fetched. This stage is only for certain DQL statements like SELECT.

 

Whenever a SQL statement is executed, Oracle Engine performs below actions on it:

 

1. Validate the Syntax

2. Validate the objects being referenced in the statement

3. Privileges assigned to user executing the Job

4. Verifying if statement is already available in the shared pool. Oracle engine will calculate the hash value    for the SQL statement and look in

 

  select empno, ename from emp where deptno=10;

 

5. If statement is not already present then allocate shared memory and create a cursor in shared pool

6. Generate the Execution Plan

SQL_ID - A unique 13-character alphanumeric string that identifies a normalized SQL statement.

 

HASH_VALUE - What it is: A numeric hash of the SQL text, generated using Oracle's internal algorithm.

 

PLAN_HASH_VALUE - A numeric hash representing the execution plan structure.

Purpose: Identifies whether a SQL is using the same or different execution plan over time.

                                                                                                                   

Usage: Helps in performance tuning, especially when the same SQL has multiple execution plans         (adaptive, stats change, bind variables, etc.)

 

 

select empno, ename, job, sal from emp; 

 

select empno, ename, job, sal from Emp;

 

select empno, Ename, job, sal from emp;

 

select empno, ename, job, sal from emP;

 

select empno, ename, job, sal from emp where empno=7839;

 

select sql_id, address, hash_value, plan_hash_value from v$sqlarea where sql_text=<string to identify my query>

 

select sql_id, SQL_TEXT, address, hash_value, plan_hash_value from v$sqlarea where sql_text like '%select empno%';

 

set lines 200

set pages 20

col SQL_TEXT for a60

select sql_id, SQL_TEXT, plan_hash_value from v$sqlarea where sql_text like '%select empno%';

 

SQL> alter system flush shared_pool;

 

System altered.

 

sql> select empno, ename, job, sal from emp;

 

SQL> select sql_id, address, hash_value, plan_hash_value from v$sqlarea where sql_text='select empno, ename, job, sal from emp';

 

SQL_ID        ADDRESS          HASH_VALUE PLAN_HASH_VALUE

------------- ---------------- ---------- ---------------

1jvn42p3gccuc 0000000076986C10 1190540108      3956160932

 

 

SQL> Select empno, ename, job, sal from EMP;

 

SQL> select sql_id, address, hash_value, plan_hash_value from v$sqlarea where sql_text='Select empno, ename, job, sal from EMP';

 

SQL_ID        ADDRESS          HASH_VALUE PLAN_HASH_VALUE

------------- ---------------- ---------- ---------------

a6fs5r3mzr422 000000007EE6E9E0 3892023362      3956160932

Note:

The SQL ID is a hash of the text of the SQL statement.

Similarly, the hash_value is a numeric hash of the statement itself. You expect these to be the same every time you run a statement.

 

The plan_hash_value is a hash of the execution plan. This will change if you get a different plan.

 

Bind variable

Bind variables are «substitution» variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed.

select empno, ename, job, sal from emp where empno=7839;   hash value

select empno, ename, job, sal from emp where empno=7840;   hash value

 

In SQL*Plus you can use bind variables as follows:

 

SQL> variable deptno number

SQL> exec :deptno := 10

SQL> select * from emp where deptno = :deptno;

 

Note: At run time it will assign value to variable.

 

Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is known as a «hard parse» and for OLTP applications can actually take longer to carry out that the DML instruction itself.

 

The order of Query Execution is following

1)From Clause

2)Where Clause

3)Group By Clause

4)Having Clause

5)Select

6)Order By Clause

 

how the same SQL can result in different PLAN_HASH_VALUEs due to plan changes in Oracle.

 

Creates the table

Inserts data

Runs SQL

create index on that table

Run SQL after index creation

Captures and compares execution plans

 

LAB PLAN_HASH_VALUEs

 

STEP 1: Drop table if exists

 

DROP TABLE plan_demo PURGE;

 

BEGIN

  EXECUTE IMMEDIATE 'DROP TABLE plan_demo PURGE';

EXCEPTION

  WHEN OTHERS THEN

    NULL;

END;

/

 

STEP 2: Create table

CREATE TABLE plan_demo (

  id      NUMBER,

  name    VARCHAR2(50),

  salary  NUMBER

);

 

 

STEP 3: Insert sample data

BEGIN

  FOR i IN 1 .. 100000 LOOP

    INSERT INTO plan_demo VALUES (

      i,

      'Employee ' || i,

      MOD(i, 5000) + 30000

    );

  END LOOP;

  COMMIT;

END;

/

 

explain plan for SELECT * FROM plan_demo WHERE salary = 30001;

select * from table(DBMS_XPLAN.DISPLAY);

 

STEP 4: Gather stats

EXEC DBMS_STATS.GATHER_TABLE_STATS('U1', 'PLAN_DEMO');

 

STEP 5: Run SQL before index (Expect FULL TABLE SCAN)

SELECT * FROM plan_demo WHERE salary = 30001;

 

STEP 6: Find SQL_ID and PLAN_HASH_VALUE

SELECT sql_id, plan_hash_value, sql_text

FROM v$sql

WHERE sql_text LIKE 'SELECT * FROM plan_demo WHERE salary = 30001%';

 

STEP 7: Show Execution Plan

 

explain plan for SELECT * FROM plan_demo WHERE salary = 30001;

select * from table(DBMS_XPLAN.DISPLAY);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

 

STEP 8: Create index

CREATE INDEX idx_plan_salary ON plan_demo(salary);

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PLAN_DEMO');

STEP 9: Re-run the same SQL

SELECT * FROM plan_demo WHERE salary = 30001;

 

STEP 10: Check SQL_ID and PLAN_HASH_VALUE again

SELECT sql_id, plan_hash_value, sql_text

FROM v$sql

WHERE sql_text LIKE 'SELECT * FROM plan_demo WHERE salary = 30001%';

 

STEP 11: Show New Execution Plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

 

STEP 12: Clean up (Optional)

DROP TABLE plan_demo PURGE;

 

Best Practices:

  • Use bind variables to reuse SQL in shared pool (reduces hard parses)
  • Keep statistics up-to-date for optimizer efficiency
  • Avoid unnecessary full table scans; create proper indexes
  • Monitor SQL_ID and PLAN_HASH_VALUE for plan stability

 

Key Outcomes:

  • Faster query execution
  • Reduced CPU and I/O load
  • Consistent execution plans across runs
  • Better use of shared pool and caching

8.8 Session Locking and Blocking Analysis

Overview

In multi-user Oracle databases, concurrent sessions can block each other when accessing the same resources. Understanding session locks and blocking is critical for performance tuning and troubleshooting concurrency issues.

 

Objective

Identify blocking sessions, resolve locks, and ensure smooth transaction processing.

 

Why

  • Prevent long waits or application timeouts caused by uncommitted transactions.
  • Detect and resolve deadlocks or resource contention.
  • Maintain database concurrency and transactional consistency.

 

How

  • Query v$lock to find blocking and waiting sessions.
  • Query v$session to identify session details (SID, SERIAL#, USERNAME).
  • Kill or terminate blocking sessions if necessary.

 

Use Case

1.    User U1 updates emp table and holds a lock.

2.    User U2 tries to update the same row → session blocks.

3.    SYS checks v$lock and v$session to find blocking session.

4.    SYS kills U1’s session → lock released, U2 continues.

 

Implementation Steps


 


U1:

 

U2:

U2 hangs

Show blocking sessions


Identify session details




Kill blocking session



Confirm locks are released

 

Best Practices

  • Avoid long-running transactions on frequently updated tables.
  • Regularly monitor v$session and v$lock for blocking issues.
  • Use row-level locking instead of table-level where possible.
  • Communicate with users before killing sessions in production.

 

Key Outcomes

  • Quickly identify blocking sessions and resolve locks.
  • Reduce application wait times and prevent deadlocks.
  • Improve concurrency and transaction throughput.

 

Validation and Review

The final step validates both security and performance improvements.

Security Validation

  • Re-run DBSAT
  • Validate privilege models
  • Review audit settings

Performance Validation

  • Compare before/after AWR reports
  • Validate ADDM improvements
  • Monitor wait event reductions
  • Confirm improved SQL execution plans

Operational Checks

  • Validate across all PDBs
  • Monitor application behavior
  • Document baselines

 

Final Outcome

After implementation:

  • Hardened security posture
  • Reduced privilege risks
  • Optimized SQL execution
  • Improved concurrency handling
  • Proactive performance monitoring
  • Production-ready DBA baseline

9. Daily Health Checks (BONUS)

Overview

Daily health checks ensure the Oracle database environment is stable, performant, and free from potential issues. This includes monitoring instance status, tablespace usage, sessions, redo logs, and system resources.

 

Objective

  • Detect and resolve early warnings of database issues.
  • Track resource utilization and growth trends.
  • Validate backup, log, and Data Guard statuses.
  • Ensure all database users and objects are healthy.

 

Why

  • Prevent downtime and performance degradation.
  • Maintain SLA compliance and database reliability.
  • Provide actionable insights for proactive maintenance.

 

How

  • Run a pre-built health_check.sql script daily.
  • Capture instance info, tablespaces, schema sizes, sessions, and system metrics.
  • Review outputs and take corrective action as needed.

 

Use Case

  • Check tablespace usage → prevent out-of-space errors.
  • Monitor active/inactive sessions → identify blocking or long-running queries.
  • Validate FRA, ASM, filesystem, memory, and CPU utilization → ensure adequate resources.
  • Review redo log generation → confirm proper logging for recovery.
  • Verify Data Guard status → ensure primary-standby sync.
  • Review backup and invalid object/indexes → ensure recoverability.

 

I have created a health_check.sql script for automation
This allows a single-click full health check of the database

THIS IS THE SCRIPT :

select current_timestamp from dual;

 

alter session set nls_date_format='mm/dd/yy hh24:mi:ss'

/

 

prompt

prompt DB Information

Prompt

 

set line 200 pages 200

col host_name for a30

col Instance_name for a30

select distinct instance_name,Host_name,open_mode,database_role,log_mode,startup_time,created,edition from gv$instance,gv$database;

 

prompt

prompt "Chek Tablespace Utilization"

Prompt

 

select * from dba_tablespace_usage_metrics order by 4;

 

prompt

prompt Database Size

prompt

select sum(bytes/1024/1024/1024) "USED SPACE GB", (sum(maxbytes/1024/1024/1024)-sum(bytes/1024/1024/1024)) "FREE SPACE GB" ,(sum(bytes/1024/1024/1024)+(sum(maxbytes/1024/1024/1024)-sum(bytes/1024/1024/1024))) "TOTAL SPACE GB"

from dba_data_files;

prompt

prompt

prompt Schemas Size

prompt

col owner for a30

select owner,sum(bytes/1024/1024/1024) GB from dba_segments

where owner not in ('SYS','SYSTEM','DBSNMP','CTXSYS','OJVMSYS','DVSYS','AUDSYS','GSMADMIN_INTERNAL','ORDDATA','MDSYS','LBACSYS','OUTLN','PROFILE_MANAGER','XDB','WMSYS','ORDSYS')  group by owner order by GB desc

/

 

prompt

prompt USER DETAILS

prompt

col USERNAME FOR a15

select username,Account_status,expiry_date from dba_users where common='NO' and Oracle_maintained='N';

 

prompt

prompt Unusable Indexes

prompt

SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';

 

prompt

prompt Invalid Objects

prompt

 

COLUMN owner FORMAT A20

COLUMN object_type FORMAT A20

COLUMN object_name FORMAT A35

 

SELECT owner, object_type, object_name

FROM dba_objects

WHERE status = 'INVALID'

ORDER BY owner, object_type;

 

select owner,object_type,count(status) from dba_objects where status='INVALID' group by owner,object_type;

 

prompt

prompt Invalid Indexes

prompt

select a.owner, a.table_name,a.index_name,a.index_type,a.status,a.blevel,a.clustering_factor,a.distinct_keys,a.partitioned,a.last_analyzed

from dba_indexes a where status not in ('VALID') and owner not in ('SYS','SYSTEM','AUDSYS');

 

prompt

prompt Current Active SESSIONS

prompt

 

col service_name for a30

col username for a30

col machine for a30

select service_name,machine,username,status,count(service_name) as Total from gv$session where status='ACTIVE' group by service_name,machine,username,status

/

 

prompt

prompt Current INACTIVE SESSIONS

prompt

 

col service_name for a30

col username for a30

col machine for a30

select service_name,machine,username,status,count(service_name) as Total from gv$session where status='INACTIVE' group by service_name,machine,username,status

/

 

Prompt

Prompt FRA Utilization

Prompt

 

col name for a30

select name,round(space_limit/1048576) SPACE_LIMIT_MB, round(SPACE_USED/1048576) SPACE_USED_MB, round(((SPACE_USED/1048576) * 100) / (space_limit/1048576), 2) PRC_USED from v$recovery_file_dest;

 

prompt

 

set line 200

select file_type,percent_space_used USED, percent_space_reclaimable RECLAIMABLE, number_of_files as "NUMBER" from v$flash_recovery_area_usage;

 

prompt

prompt ASM Disk Utilization

prompt

SELECT name, total_mb / 1024 AS ALLOCATED_GB, free_mb / 1024 AS EMPTY_GB, ((total_mb - free_mb) / total_mb) * 100 AS PERCENT_USED, state FROM v$asm_diskgroup;

 

SELECT

    name,

    ROUND(total_mb / 1024 / 1024, 2) AS ALLOCATED_TB,

    ROUND(free_mb / 1024 / 1024, 2) AS EMPTY_TB,

    ROUND(((total_mb - free_mb) / total_mb) * 100, 2) AS PERCENT_USED,

    state

FROM

    v$asm_diskgroup;

 

prompt

prompt Show sessions that are blocking each other

prompt

select    'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking from       gv$lock l1, gv$lock l2

where    l1.block =1 and l2.request > 0

and        l1.id1=l2.id1

and        l1.id2=l2.id2

/

 

SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

id1, id2, lmode, request, type

FROM GV$LOCK

WHERE (id1, id2, type) IN

(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)

ORDER BY id1, request

/



col username     format a40

col sess_id          format a10

col object            format a25

col mode_held   format a10

select    oracle_username || ' (' || s.osuser || ')' username

,              s.sid || ',' || s.serial# sess_id

,              owner || '.' ||    object_name object

,              object_type

,              decode(               l.block

               ,              0, 'Not Blocking'

               ,              1, 'Blocking'

               ,              2, 'Global') status

,              decode(v.locked_mode

               ,              0, 'None'

               ,              1, 'Null'

               ,              2, 'Row-S (SS)'

               ,              3, 'Row-X (SX)'

               ,              4, 'Share'

               ,              5, 'S/Row-X (SSX)'

               ,              6, 'Exclusive', TO_CHAR(lmode)) mode_held

from      gv$locked_object v

,              dba_objects d

,              gv$lock l

,              gv$session s

where   v.object_id = d.object_id

and        v.object_id = l.id1

and        v.session_id = s.sid

order by oracle_username

,              session_id

/

prompt

prompt Backup Status

prompt

 

set pages 2000 lines 200

COL STATUS FORMAT a9

COL hrs FORMAT 999.99

set pages 2000 lines 200

COL STATUS FORMAT a9

COL hrs FORMAT 999.99

SELECT

    INPUT_TYPE,

    STATUS,

    TO_CHAR(START_TIME, 'mm/dd/yy hh24:mi') AS start_time,

    TO_CHAR(END_TIME, 'mm/dd/yy hh24:mi') AS end_time,

    ELAPSED_SECONDS/3600 AS hrs,

    INPUT_BYTES/1024/1024/1024 AS SUM_BYTES_BACKED_IN_GB,

    OUTPUT_BYTES/1024/1024/1024 AS SUM_BACKUP_PIECES_IN_GB,

    OUTPUT_DEVICE_TYPE  

FROM

    V$RMAN_BACKUP_JOB_DETAILS

WHERE

    START_TIME > SYSDATE - INTERVAL '2' DAY

ORDER BY

    SESSION_KEY;

 

prompt

prompt Redolog Generation

prompt

set line 200 pages 200

select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size) / 1024 / 1024)) "REDO PER DAY(MB)"

from v$archived_log

group by trunc(completion_time)

order by 1;

 

prompt

prompt Dataguard Status

prompt

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

 

select thread#,max(sequence#) from v$log where status='CURRENT' group by thread# order by 1;

 

prompt

prompt DB USERS VALIDATION

prompt

set line 200 pages 200

col username for a25

col ACCOUNT_STATUS for a25

col CREATED for a20

col EXPIRY_DATE for a20

col LAST_LOGIN for a40

 SELECT

    username,

    account_status,

    created,

    expiry_date,

    last_login

FROM

    dba_users

WHERE

    username IN ('SYS','SYSTEM','C##VAPTUSER','CMSUSER','C##VAPTUSER','PDBUSER','C##DBLCMUSER','DBSNMP');

 

prompt

prompt DB USERS VALIDATION

prompt

set line 200 pages 200

select severity, username, expiry_date from (

select 'Warning' as SEVERITY, username, to_char(expiry_date,'DD-MON-YYYY') as EXPIRY_DATE

from dba_users

where account_status='OPEN' and (expiry_date > sysdate+15) and (expiry_date <= sysdate+14)

union

select 'Critical' as SEVERITY, username, to_char(expiry_date,'DD-MON-YYYY') as EXPIRY_DATE

from dba_users

where (account_status = 'OPEN' and (expiry_date<= sysdate+10)) or account_status='EXPIRED(GRACE)'

) order by 3;




Prompt

prompt Filesystem Utilization

Prompt

!df -h

prompt

prompt

prompt Memory Utilization

prompt

!free -h

prompt

prompt

prompt CPU Utilization

prompt

!sar 1 10

Prompt

 

Implementation Steps

Connect to target database


Run health check script
@/home/oracle/scripts/health_check.sql

Sample checks included in script:
1. Instance and database role info
2. Tablespace utilization
3. Database, schema, and object size
4. Unusable/invalid indexes or objects
5. Active and inactive sessions
6. FRA and ASM disk usage
7. Blocking sessions
8. Backup and redo log status
9. Data Guard lag and status
10. Database users validation
11. Filesystem, memory, and CPU utilization






 

 

 

Best Practices

  • Run the script at a consistent time daily.
  • Send automated reports to DBAs for review.
  • Monitor trends over time to detect anomalies early.
  • Validate critical alerts (tablespace full, blocking sessions, invalid objects) immediately.

 

Key Outcomes

  • Early detection of potential issues and performance bottlenecks.
  • Comprehensive overview of the database and server health.
  • Proactive maintenance, avoiding critical outages or performance degradation.

 

10. Risks and Mitigation

Implementing database security hardening and performance tuning introduces certain operational and architectural risks. Proper planning and mitigation strategies are essential to ensure stability, compliance, and performance consistency.

 

1. Privilege Misconfiguration Risk

Risk:
Incorrect privilege revocation may break application functionality or administrative operations.

Mitigation:

  • Follow least privilege principle gradually
  • Perform privilege review in staging environment
  • Maintain rollback scripts for revoked privileges
  • Use privilege analysis tools before enforcement

 

2. Performance Impact from Security Controls

Risk:
Security measures such as auditing, encryption, or monitoring may introduce performance overhead.

Mitigation:

  • Enable auditing selectively
  • Monitor performance after security changes
  • Use AWR baselines before and after implementation
  • Apply controls incrementally

 

3. Misinterpretation of DBSAT Findings

Risk:
DBSAT reports may highlight risks that require contextual analysis, leading to over-hardening or unnecessary changes.

Mitigation:

  • Review findings based on business context
  • Prioritize high and critical risks first
  • Validate recommendations before implementation
  • Align remediation with security policies

 

4. SQL Tuning Regression Risk

Risk:
Improper SQL tuning or indexing may negatively impact execution plans or other workloads.

Mitigation:

  • Capture execution plan baselines
  • Use SQL Plan Management (SPM) where applicable
  • Test tuning changes in non-production environments
  • Monitor performance after deployment

 

5. AWR/ADDM Licensing and Usage Risk

Risk:
Improper usage of AWR and ADDM without proper licensing may lead to compliance issues.

Mitigation:

  • Ensure Diagnostic Pack licensing compliance
  • Validate feature usage with licensing guidelines
  • Use Statspack as an alternative if required

 

6. Over-Hardening Risk

Risk:
Excessive security hardening may reduce operational flexibility or slow down troubleshooting.

Mitigation:

  • Maintain a balance between security and usability
  • Document security exceptions where required
  • Implement role-based administrative access
  • Follow phased hardening approach

 

7. Inconsistent Multi-tenant Configuration

Risk:
Security and performance configurations may differ across CDB and PDBs, leading to inconsistent behavior.

Mitigation:

  • Standardize configurations across containers
  • Validate settings at both CDB and PDB levels
  • Use common users and roles where applicable
  • Perform container-wide validation checks

 

8. Change Management Risk

Risk:
Untracked changes during hardening and tuning may lead to operational instability.

Mitigation:

  • Follow change management procedures
  • Maintain implementation documentation
  • Enable configuration auditing
  • Use version-controlled scripts

11. Conclusion

This implementation successfully demonstrated a comprehensive approach to Oracle Database Security Hardening and Performance Tuning through practical, real-world scenarios. The guide combined critical administrative domains including user and role management, security controls, DBSAT assessment, AWR and ADDM diagnostics, and SQL performance optimization into a unified operational workflow.

Through this hands-on implementation, essential security practices such as least privilege enforcement, privilege review, and database hardening were applied to strengthen the overall security posture. The use of DBSAT provided deeper visibility into potential risks, sensitive data exposure, and privilege-related vulnerabilities, enabling informed remediation strategies aligned with enterprise security standards.

On the performance side, AWR and ADDM were leveraged to analyze workload behavior, identify bottlenecks, and understand system performance patterns. SQL tuning techniques were then applied to optimize inefficient queries, improve execution plans, and enhance overall database responsiveness and stability.

This practical implementation highlights the importance of balancing security and performance in modern database environments. It reinforces the value of proactive monitoring, periodic security assessments, and continuous tuning as part of a mature Oracle DBA strategy.

Overall, this guide serves as a solid reference for Oracle DBAs seeking to implement structured security hardening and performance tuning practices. It provides a strong foundation for maintaining secure, stable, and high-performing Oracle database environments aligned with enterprise best practices and real-world operational requirements.

12. Limitations

This implementation is performed in a controlled lab environment designed to demonstrate practical Oracle database security and performance tuning concepts. While it effectively covers core administrative practices, it has certain limitations:

  • The implementation does not include enterprise security frameworks such as SIEM integration or centralized identity management
  • Real-time production workloads and large-scale concurrency patterns are not fully simulated
  • Advanced integrations like Data Guard security hardening, Zero Trust architecture, and cross-region deployments are not covered
  • Some performance diagnostics rely on licensed features such as AWR and ADDM
  • External security validation methods such as penetration testing or compliance audits are not included
  • Application-level performance tuning and code optimization are outside the scope of this document

These limitations are acceptable for learning and foundational implementation purposes but should be expanded upon when applying these practices in enterprise production environments.