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 SecurityHardening 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_RECOMMEN

D');

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. 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

10. 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 theoverall security posture. The use of DBSAT provided deeper visibility into potential risks, sensitive data exposure, and privilege-related vulnerabilities, enabling informed remediationstrategies 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.

11. 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.

No comments:

Post a Comment