Oracle Forensics : 2026

Saturday, April 4, 2026

Shell Script to Generate RMAN Backup Monitoring Report for All Databases on a Single Server

[oracle@srv1 ~]$ cat rman_backup_report_enhanced.sh

#!/bin/bash

# ===== CONFIGURATION =====

REPORT_DIR="/tmp"

DATE_TAG=$(date +%Y%m%d_%H%M%S)

TEXT_REPORT="$REPORT_DIR/rman_report_$DATE_TAG.log"

HTML_REPORT="$REPORT_DIR/rman_report_$DATE_TAG.html"

# Email (optional)

EMAIL_TO="your_email@example.com"

SEND_EMAIL=false # change to true if mailx configured

# ===== INIT =====

echo "RMAN Backup Report - $(date)" > $TEXT_REPORT

# HTML Header

cat <<EOF > $HTML_REPORT

<html>

<head>

<style>

body { font-family: Arial; }

table { border-collapse: collapse; width: 100%; }

th, td { border: 1px solid black; padding: 6px; text-align: center; }

th { background-color: #333; color: white; }

.success { background-color: #c6efce; }

.failed { background-color: #ffc7ce; }

</style>

</head>

<body>

<h2>RMAN Backup Report</h2>

<p>Generated on: $(date)</p>

<table>

<tr>

<th>Input Type</th>

<th>Status</th>

<th>Start Time</th>

<th>Duration (Min)</th>

</tr>

EOF

TOTAL_DB=0

FAILED_COUNT=0

# ===== LOOP DATABASES =====

grep -v '^#' /etc/oratab | grep -v '^$' | while IFS=: read ORACLE_SID ORACLE_HOME REST

export ORACLE_SID

export ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

TOTAL_DB=$((TOTAL_DB+1))

echo "Checking DB: $ORACLE_SID" >> $TEXT_REPORT

RESULT=$(sqlplus -s / as sysdba <<EOF

SET HEADING OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 300

SELECT

'$ORACLE_SID' || '|' ||

INPUT_TYPE || '|' ||

STATUS || '|' ||

TO_CHAR(START_TIME,'DD-MON-YYYY HH24:MI') || '|' ||

TO_CHAR(END_TIME,'DD-MON-YYYY HH24:MI') || '|' ||

ROUND((END_TIME - START_TIME)*24*60) || '|' ||

ROUND(OUTPUT_BYTES/1024/1024)

FROM

V\$RMAN_BACKUP_JOB_DETAILS

WHERE

START_TIME > SYSDATE - 1

ORDER BY START_TIME DESC;

EXIT;

EOF

)

echo "$RESULT" | while IFS="|" read DB INPUT STATUS START END DURATION SIZE

[[ -z "$DB" ]] && continue

# Failure detection

CLASS="success"

if [[ "$STATUS" != "COMPLETED" ]]; then

CLASS="failed"

FAILED_COUNT=$((FAILED_COUNT+1))

# Text report

printf "%-10s %-12s %-10s %-18s %-18s %-8s %-8s\n" \

"$DB" "$INPUT" "$STATUS" "$START" "$END" "$DURATION" "$SIZE" >> $TEXT_REPORT

# HTML report

cat <<ROW >> $HTML_REPORT

<td>$INPUT</td>

<td>$STATUS</td>

<td>$START</td>

<td>$DURATION</td>

</tr>

ROW

done

# ===== SUMMARY =====

cat <<EOF >> $HTML_REPORT

</table>

<h3>Summary</h3>

<p>Total Databases Checked: $TOTAL_DB</p>

<p>Failed Jobs: $FAILED_COUNT</p>

</body>

</html>

EOF

echo "====================================" >> $TEXT_REPORT

echo "Total DB Checked: $TOTAL_DB" >> $TEXT_REPORT

echo "Failed Jobs: $FAILED_COUNT" >> $TEXT_REPORT

# ===== EMAIL =====

if [ "$SEND_EMAIL" = true ]; then

mailx -s "RMAN Backup Report" -a "$HTML_REPORT" "$EMAIL_TO" < $TEXT_REPORT

# ===== OUTPUT =====

echo "Text Report : $TEXT_REPORT"

echo "HTML Report : $HTML_REPORT"

Script to monitor the RMAN backup

chmod +x rman_backup_report.sh

./rman_backup_report.sh

[oracle@srv1 ~]$ cat rman_backup_report.sh

#!/bin/bash

# Output file

REPORT_FILE="/tmp/rman_backup_report_$(date +%Y%m%d_%H%M%S).log"

echo "RMAN Backup Consolidated Report" > $REPORT_FILE

echo "Generated on: $(date)" >> $REPORT_FILE

echo "=========================================" >> $REPORT_FILE

# Loop through databases from oratab

grep -v '^#' /etc/oratab | grep -v '^$' | while IFS=: read ORACLE_SID ORACLE_HOME REST

export ORACLE_SID

export ORACLE_HOME

export PATH=$ORACLE_HOME/bin:$PATH

echo "" >> $REPORT_FILE

echo "-----------------------------------------" >> $REPORT_FILE

echo "Database: $ORACLE_SID" >> $REPORT_FILE

echo "-----------------------------------------" >> $REPORT_FILE

sqlplus -s / as sysdba <<EOF >> $REPORT_FILE

SET LINESIZE 200

SET PAGESIZE 100

SET TRIMOUT ON

SET TRIMSPOOL ON

COLUMN STATUS FORMAT A10

COLUMN INPUT_TYPE FORMAT A15

COLUMN START_TIME FORMAT A20

COLUMN END_TIME FORMAT A20

SELECT

INPUT_TYPE,

STATUS,

TO_CHAR(START_TIME,'DD-MON-YYYY HH24:MI') START_TIME,

TO_CHAR(END_TIME,'DD-MON-YYYY HH24:MI') END_TIME

FROM

V\$RMAN_BACKUP_JOB_DETAILS

WHERE

START_TIME > SYSDATE - 2

ORDER BY

START_TIME DESC;

EXIT;

EOF

done

echo "" >> $REPORT_FILE

echo "=========================================" >> $REPORT_FILE

echo "End of Report" >> $REPORT_FILE

# Print location

echo "Report generated at: $REPORT_FILE"

Saturday, March 7, 2026

SWAP usage

grep VmSwap /proc/[0-9]*/status | sort -nk2

ps p procesid

for FILE in `ls /proc/[0-9]*/status`; do awk '/VmSwap|Name/ {printf $2 " " $3} END {print ""}' "$FILE" ; done

for FILE in `ls /proc/[0-9]*/status`; do awk '/VmSwap|Name/ {printf $2 " " $3} END {print ""}' "$FILE" ; done | sort -nk2 |grep kB >/tmp/swap.log

HOW TO FIND THE SESSION CONSUMING HIGH MEMORY AT DATABASE LEVEL.

###########Top 10 highest memory sessions#############

SELECT s.sid,
s.serial#,
s.username,
s.program,
p.spid AS os_pid,
ROUND(st.value/1024/1024,2) AS pga_memory_mb
FROM v$session s
JOIN v$sesstat st ON s.sid = st.sid
JOIN v$statname sn ON st.statistic# = sn.statistic#
JOIN v$process p ON s.paddr = p.addr
WHERE sn.name = 'session pga memory'
---and s.program not like '%ORACLE%'
ORDER BY st.value DESC;

For Multi Tennant DB

col PDB_NAME form a15
col USERNAME form a15
col MACHINE form a18
col Module form a15
col OS_PID form a10
SELECT
c.name AS pdb_name,
s.inst_id,
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.module,
s.sql_id,
p.spid AS os_pid,
ROUND(st.value/1024/1024,2) AS pga_memory_mb,
s.logon_time
FROM gv$session s
JOIN gv$sesstat st
ON s.sid = st.sid
AND s.inst_id = st.inst_id
JOIN gv$statname sn
ON st.statistic# = sn.statistic#
AND st.inst_id = sn.inst_id
JOIN gv$process p
ON s.paddr = p.addr
AND s.inst_id = p.inst_id
LEFT JOIN v$containers c
ON s.con_id = c.con_id
WHERE sn.name = 'session pga memory'
AND s.type = 'USER'
AND s.program NOT LIKE '%ORACLE%'
ORDER BY st.value DESC;

#####This shows sessions active about 1 hour ago with PGA usage###########

SELECT
ash.sample_time,
ash.session_id,
ash.session_serial#,
ash.sql_id,
ash.program,
ROUND(ash.pga_allocated/1024/1024,2) AS pga_mb
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN SYSDATE - 1/24 AND SYSDATE - 55/1440
ORDER BY ash.pga_allocated DESC;

###Top SQL by PGA Memory Usage

SELECT
c.name AS pdb_name,
s.sid,
s.serial#,
s.username,
s.sql_id,
s.program,
ROUND(st.value/1024/1024,2) AS pga_mb
FROM gv$session s
JOIN gv$sesstat st
ON s.sid = st.sid
AND s.inst_id = st.inst_id
JOIN gv$statname sn
ON st.statistic# = sn.statistic#
AND st.inst_id = sn.inst_id
LEFT JOIN v$containers c
ON s.con_id = c.con_id
WHERE sn.name = 'session pga memory'
AND s.type='USER'
ORDER BY st.value DESC;

##Query: SQL_ID Consuming Highest Memory in a Time Window

SELECT
c.name AS pdb_name,
ash.sql_id,
COUNT(*) samples,
ROUND(MAX(ash.pga_allocated)/1024/1024,2) AS max_pga_mb,
ROUND(AVG(ash.pga_allocated)/1024/1024,2) AS avg_pga_mb
FROM dba_hist_active_sess_history ash
LEFT JOIN v$containers c
ON ash.con_id = c.con_id
WHERE ash.sample_time BETWEEN
TO_DATE('08-MAR-2026 01:00','DD-MON-YYYY HH24:MI')
AND TO_DATE('08-MAR-2026 02:30','DD-MON-YYYY HH24:MI')
AND ash.sql_id IS NOT NULL
GROUP BY c.name, ash.sql_id
ORDER BY max_pga_mb DESC;

--OR only for PDBS

SELECT
c.name AS pdb_name,
ash.sql_id,
COUNT(*) samples,
ROUND(MAX(ash.pga_allocated)/1024/1024,2) AS max_pga_mb,
ROUND(AVG(ash.pga_allocated)/1024/1024,2) AS avg_pga_mb
FROM dba_hist_active_sess_history ash
LEFT JOIN v$containers c
ON ash.con_id = c.con_id
WHERE ash.sample_time BETWEEN
TO_DATE('08-MAR-2026 01:00','DD-MON-YYYY HH24:MI')
AND TO_DATE('08-MAR-2026 02:30','DD-MON-YYYY HH24:MI')
AND ash.sql_id IS NOT NULL AND c.name <>'CDB$ROOT'
GROUP BY c.name, ash.sql_id
ORDER BY max_pga_mb DESC;

Saturday, January 17, 2026

Performance Tuning Oracle 19c (Finding CPU Consuming Query) - Multitenant DB

### Find the SNAPID

select CON_ID,SNAP_ID,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot order by SNAP_ID;

####Below Query will find the sqlid which consumes the most CPU during mention time.######

SELECT
p.name AS pdb_name,
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
ash.sql_id,
COUNT(*) AS cpu_samples
FROM
dba_hist_active_sess_history ash,
v$pdbs p
WHERE
ash.sample_time BETWEEN
to_date('11-JAN-2026 23:00','DD-MON-YYYY HH24:MI')
AND
to_date('12-JAN-2026 00:30','DD-MON-YYYY HH24:MI')
AND ash.session_state = 'ON CPU'
AND ash.con_id = p.con_id
GROUP BY
p.name,
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
ash.sql_id
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY;

######Once we have details of SQLID we can get the details of sql text fron history view as below:#######

SELECT sql_id, sql_text FROM dba_hist_sqltext WHERE sql_id='2yhkvj9bru81z';

###For Current session:

select con_id,SERVICE_NAME,sid from v$session where sql_id='247cszx5pfbqa';

SELECT

s.sql_id,
s.executions_total,
ROUND(s.cpu_time_total/1000000,2) cpu_seconds,
t.sql_text
FROM
dba_hist_sqlstat s,
dba_hist_sqltext t
WHERE
s.sql_id = t.sql_id
AND s.sql_id = '2yhkvj9bru81z';

#######Search SQL text by keyword: Suppose you remember only part of query#######

SELECT sql_id, sql_text
FROM dba_hist_sqltext
WHERE LOWER(sql_text) LIKE '%material_records%';

###To find which SQL was running at a certain time:###

--This gives the most active SQLs in last 1 hour with full text.

SELECT
h.sql_id,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text,
COUNT(*) AS occurrences
FROM
dba_hist_active_sess_history h,
dba_hist_sqltext t
WHERE
h.sql_id = t.sql_id
AND h.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
h.sql_id,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY occurrences DESC
FETCH FIRST 5 ROWS ONLY;

####Find SQL Text from Specific Snapshot--If you know snapshot range from AWR. Here we have snap_id 58 and 59.##

SELECT
t.sql_id,

DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
dba_hist_sqltext t
WHERE
t.sql_id IN (
SELECT DISTINCT sql_id
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN 58 AND 59
);

###########If you want only SQL from a specific PDB:########

SELECT
t.sql_id,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
dba_hist_sqltext t
WHERE
t.sql_id IN (
SELECT DISTINCT sql_id
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN 58 AND 59
AND con_id = 3 -- your PDB con_id
);

####If You Know the name of PDB:####

--Query for PDB = TRAINING

SELECT
t.sql_id,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
dba_hist_sqltext t
WHERE
t.sql_id IN (
SELECT DISTINCT s.sql_id
FROM dba_hist_sqlstat s,
v$pdbs p
WHERE s.con_id = p.con_id
AND p.name = 'TRAINING'
AND s.snap_id BETWEEN 58 AND 59
);

###If You Want Top CPU SQL in TRAINING PDB###

SELECT
s.sql_id,
ROUND(SUM(s.cpu_time_delta)/1000000,2) AS cpu_seconds,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
dba_hist_sqlstat s,
dba_hist_sqltext t,
v$pdbs p
WHERE
s.sql_id = t.sql_id
AND s.con_id = p.con_id
AND p.name = 'TRAINING'
AND s.snap_id BETWEEN 58 AND 59
GROUP BY
s.sql_id,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY cpu_seconds DESC
FETCH FIRST 5 ROWS ONLY;

###If you want ALL SQL executed in last 1 hour in TRAINING###

SELECT
ash.sql_id,
COUNT(*) AS samples,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
dba_hist_active_sess_history ash,
dba_hist_sqltext t,
v$pdbs p
WHERE
ash.sql_id = t.sql_id
AND ash.con_id = p.con_id
AND p.name = 'TRAINING'
AND ash.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
ash.sql_id,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

###If you want to know who ran those SQLs:###

SELECT
ash.sql_id,
u.username,
ash.program,
COUNT(*) AS cpu_samples,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
dba_hist_active_sess_history ash,
dba_hist_sqltext t,
v$pdbs p,
dba_users u
WHERE
ash.sql_id = t.sql_id
AND ash.con_id = p.con_id
AND ash.user_id = u.user_id
AND p.name = 'TRAINING'
AND ash.session_state = 'ON CPU'
AND ash.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
ash.sql_id,
u.username,
ash.program,
DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY;

Important Notes:

DBA_HIST_SQLTEXT keeps SQL as long as AWR retention is set

Sunday, January 11, 2026

Daily Scripts

1) To find I/O order by Tablespces:

select name, phyrds, phyrds * 100 / (select sum(phyrds) from v$filestat)
phywrts,  phywrts * 100 /(select sum(phywrts) from v$filestat)
from  v$datafile df, v$filestat fs
where df.file# = fs.file# order by phyrds desc;

2) Memory Hungry Oracle Process:


select   sid,name,value from   v$statname n,v$sesstat s where   n.STATISTIC# = s.STATISTIC# and   name like 'session%memory%'order by 3 desc;

select sum(bytes)/1024/1024 Mb from (select bytes from v$sgastat union select value bytes from 
v$sesstat s,v$statname n where n.STATISTIC# = s.STATISTIC# and  n.name = 'session pga memory');

3) To see OS Version using PL/SQL

select DBMS_UTILITY.PORT_STRING from dual;

Script to find Top CPU consuming PDBs (last few minutes)

SET LINES 200

COL pdb_name FORMAT A20

SELECT

p.name AS pdb_name,

ROUND(SUM(s.value)/100,2) AS cpu_seconds,

COUNT(*) AS sessions

FROM

v$sesstat s,

v$statname n,

v$session se,

v$pdbs p

WHERE

s.statistic# = n.statistic#

AND s.sid = se.sid

AND n.name = 'CPU used by this session'

AND se.con_id = p.con_id

GROUP BY p.name

ORDER BY cpu_seconds DESC;

==============or================

SET LINES 200
COL pdb_name FORMAT A15
COL username FORMAT A15
COL program FORMAT A30
COL service FORMAT A20
COL cpu_mins FORMAT 99990.99
SELECT
ROWNUM AS rank,
x.*
FROM
(
SELECT
p.name AS pdb_name,
s.sid,
s.serial#,
s.username,
s.service_name AS service,
s.program,
s.sql_id,
ROUND(v.value / (100*60), 2) AS cpu_mins
FROM
v$statname n,
v$sesstat v,
v$session s,
v$pdbs p
WHERE
n.name = 'CPU used by this session'
AND v.statistic# = n.statistic#
AND v.sid = s.sid
AND s.con_id = p.con_id
AND v.value > 0
ORDER BY v.value DESC
) x
WHERE ROWNUM <= 10;

Sunday, January 4, 2026

Automation to execute revoke statement

#!/bin/bash

CSV_FILE="revoke_access.csv"
ORACLE_HOME="/u01/app/oracle/product/19c/dbhome_1"
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME PATH
# Skip header
tail -n +2 "$CSV_FILE" | while IFS=',' read -r CDB PDB GRANTEE OWNER OBJECT PRIV
do
echo "Processing: $CDB | $PDB | $GRANTEE | $OWNER.$OBJECT | $PRIV"
export ORACLE_SID=$CDB
sqlplus -s / as sysdba <<EOF
SET FEEDBACK OFF
SET HEADING OFF
SET ECHO OFF
SET SERVEROUTPUT ON
ALTER SESSION SET CONTAINER=$PDB;
DECLARE
v_status VARCHAR2(10);
v_error VARCHAR2(4000);
BEGIN
BEGIN
EXECUTE IMMEDIATE
'REVOKE $PRIV ON $OWNER.$OBJECT FROM $GRANTEE';
v_status := 'SUCCESS';
v_error := NULL;
EXCEPTION
WHEN OTHERS THEN
v_status := 'FAILED';
v_error := SUBSTR(SQLERRM,1,4000);
END;
INSERT INTO SEC_AUDIT.REVOKE_CONTROL_LOG
(
CDB_NAME,
PDB_NAME,
GRANTEE,
OBJECT_OWNER,
OBJECT_NAME,
PRIVILEGE,
STATUS,
ERROR_MESSAGE
)
VALUES
(
'$CDB',
'$PDB',
'$GRANTEE',
'$OWNER',
'$OBJECT',
'$PRIV',
v_status,
v_error
);
COMMIT;
x
END;
/
EXIT;
EOF
done

Total Pageviews

Saturday, April 4, 2026

Saturday, March 7, 2026

Saturday, January 17, 2026

Sunday, January 11, 2026

Sunday, January 4, 2026