Oracle DBA Scripts Collection — Standby, Archive Sync, Space & Performance

#!/bin/ksh
set -x

display_msg() { echo "------------>>> "$1; }

sendMail() {
  MAIL_TXT="sendMail.txt"
  {
    echo "Subject: Status of STANDBY BUILD script"
    echo "To: dba-team@company.com"
    echo "From: oracle-alerts@company.com"
    echo "MIME-Version: 1.0"
    echo "Content-Type: text/html"
    echo "This is an automated email - please DO NOT reply."
    echo "Standby build script completed. Log: /oracle/backup/${SID_Name}/duplicate.log"
    lines=$(tail -20 /oracle/backup/${SID_Name}/duplicate.log)
    echo "${lines}"
  } > $MAIL_TXT
  cat $MAIL_TXT | /usr/sbin/sendmail -t
  [[ $? -eq 0 ]] && display_msg "mail sent" || display_msg "something went wrong"
}

# ---- MAIN ----
SID_Name=$1
CMO_DB_Name=$2

export ORACLE_SID=${SID_Name}
ORAHOME=$(cat /home/oracle/scripts/environment/dbtab \
  | grep -v ^# | grep "${SID_Name}" | head -1 | awk -F: '{print $3}')

export ORACLE_HOME=$ORAHOME
export PATH=$PATH:$ORACLE_HOME/bin
export LOG=/oracle/backup/${SID_Name}/duplicate.log

display_msg "ORACLE HOME set to ${ORACLE_HOME}"
echo "Restore started at: $(date)" > $LOG

sqlplus / as sysdba >> $LOG << EOF
  -- pre-checks
  EXIT
EOF

sendMail

export ORACLE_SID=STBYDB
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LOG=/oracle/backup/STBYDB/STBYDB.log

echo "Duplication started at: $(date)" > $LOG

$ORACLE_HOME/bin/rman \
  target sys/<password>@PRIMARYDB \
  AUXILIARY sys/<password>@STBYDB >> $LOG << EOF
set echo on;
run {
  allocate channel CH1 type disk;
  allocate channel CH2 type disk;
  allocate channel CH3 type disk;
  allocate channel CH4 type disk;
  allocate auxiliary channel AUX1 type disk;
  allocate auxiliary channel AUX2 type disk;
  allocate auxiliary channel AUX3 type disk;
  allocate auxiliary channel AUX4 type disk;
  DUPLICATE TARGET DATABASE FOR STANDBY
    FROM ACTIVE DATABASE
    DORECOVER
    NOFILENAMECHECK;
}
exit;
EOF

-- Standby database details
SELECT name, created, instance_name, host_name,
       database_role, CURRENT_SCN
FROM v$database, v$instance;

-- Archive gap between primary and standby
SELECT DISTINCT
  ARCH.THREAD#        AS Thread,
  ARCH.SEQUENCE#      AS Last_Received,
  APPL.SEQUENCE#      AS Last_Applied,
  (ARCH.SEQUENCE# - APPL.SEQUENCE#) AS Gap
FROM
  (SELECT THREAD#, MAX(SEQUENCE#) SEQUENCE#
   FROM v$archived_log
   WHERE (THREAD#, FIRST_TIME) IN
     (SELECT THREAD#, MAX(FIRST_TIME) FROM v$archived_log GROUP BY THREAD#)
   GROUP BY THREAD#) ARCH,
  (SELECT THREAD#, MAX(SEQUENCE#) SEQUENCE#
   FROM v$log_history
   WHERE (THREAD#, FIRST_TIME) IN
     (SELECT THREAD#, MAX(FIRST_TIME) FROM v$log_history GROUP BY THREAD#)
   GROUP BY THREAD#) APPL
WHERE ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

-- MRP process status
SELECT process, status, sequence#, thread#, blocks, block#
FROM gv$managed_standby;

-- Last applied and received time
SELECT 'Last Applied : ' label,
       TO_CHAR(next_time,'DD-MON-YY HH24:MI:SS') time
FROM v$archived_log
WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES')
UNION
SELECT 'Last Received: ',
       TO_CHAR(next_time,'DD-MON-YY HH24:MI:SS')
FROM v$archived_log
WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log);

-- Check for archive gap
SELECT thread#, low_sequence#, high_sequence#
FROM v$archive_gap;

-- Data Guard status messages
SELECT message FROM gv$dataguard_status;

-- Stop MRP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-- Start MRP (disconnect from session)
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

# Human-readable filesystem usage
df -Pk | awk '{ if (NR==1){next}
  if (NF==6){print}
  if (NF==1){getline record; $0=$0 record; print $0}
}' | awk '
BEGIN {
  print "Filesystem                    Mount Point        Total GB   Avail GB   Used GB   Used%"
  print "---------------------------------------------------------------------------------------------"
}
/dev/ {
  printf ("%-30s %-18s %10.2f %10.2f %10.2f %4.0f%%\n",
    $1,$6,$2/1024/1024,$4/1024/1024,$3/1024/1024,$5)
}'

# Top 10 largest directories under Oracle backup location
du -a /oracle/backup | sort -n -r | head -n 10

-- Real-time RMAN progress
SELECT sid, serial#, context, sofar, totalwork,
       ROUND(sofar/totalwork*100, 2) "% Complete",
       TO_CHAR(sysdate, 'DD-MON-YY HH24:MI') current_time
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
  AND opname NOT LIKE '%aggregate%'
  AND totalwork != 0
  AND sofar <> totalwork;

-- RMAN job history
SELECT session_key, input_type, status,
       TO_CHAR(start_time, 'mm-dd-yyyy hh24:mi:ss')  start_time,
       TO_CHAR(end_time,   'mm-dd-yyyy hh24:mi:ss')  end_time,
       elapsed_seconds/3600 hours
FROM v$rman_backup_job_details
ORDER BY session_key;

-- Total DB size
SELECT SUM(bytes)/1024/1024/1024 size_GB FROM dba_segments;

-- ASM diskgroup space
SELECT name,
       ROUND(total_mb/1024, 2) total_GB,
       ROUND(free_mb/1024, 2)  free_GB,
       ROUND(free_mb/total_mb*100) "FREE%"
FROM v$asm_diskgroup;

-- Character set
SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

-- Tablespace usage with file breakdown
SELECT df.tablespace_name,
       df.file_name,
       ROUND(df.bytes/1024/1024)      total_MB,
       NVL(ROUND(ext.used_bytes/1024/1024), 0) used_MB,
       NVL(ROUND(free.free_bytes/1024/1024), 0) free_MB,
       NVL(ROUND(free.free_bytes/df.bytes*100), 0) free_pct,
       df.autoextensible
FROM dba_data_files df
LEFT JOIN (SELECT file_id, SUM(bytes) used_bytes FROM dba_extents GROUP BY file_id) ext
  ON df.file_id = ext.file_id
LEFT JOIN (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space GROUP BY file_id) free
  ON df.file_id = free.file_id
ORDER BY df.tablespace_name, df.file_name;

-- Tablespace read/write mode
SELECT tablespace_name, status FROM dba_tablespaces;

-- TEMP tablespace usage
SELECT tablespace_name, bytes/1024/1024 size_MB,
       bytes_used/1024/1024 used_MB, bytes_free/1024/1024 free_MB
FROM sys.v_$temp_space_header, v$tempfile;

-- All schemas sorted by size
SELECT owner, ROUND(SUM(bytes)/1024/1024/1024, 2) schema_size_GB
FROM dba_segments
GROUP BY owner
ORDER BY 2 DESC;

-- Specific schema size
SELECT owner, ROUND(SUM(bytes)/1024/1024/1024, 2) schema_size_GB
FROM dba_segments
WHERE owner = 'YOUR_SCHEMA'
GROUP BY owner;

-- Largest tables in DB
SELECT * FROM (
  SELECT segment_name, segment_type,
         ROUND(bytes/1024/1024/1024, 2) size_GB,
         tablespace_name
  FROM dba_segments
  WHERE segment_type = 'TABLE'
  ORDER BY 3 DESC
) WHERE ROWNUM <= 10;

-- Active sessions with SQL text
SELECT s.sid, s.username, s.status,
       q.optimizer_mode, q.cpu_time, q.elapsed_time,
       SUBSTR(q.sql_text, 1, 100) sql_text
FROM gv$sqlarea q
JOIN gv$session s
  ON s.sql_hash_value = q.hash_value
 AND s.sql_address    = q.address
WHERE s.username IS NOT NULL;

-- I/O per session
SELECT io.sid, io.block_gets, io.consistent_gets,
       io.physical_reads, io.block_changes
FROM v$sess_io io
JOIN v$session s ON s.sid = io.sid
WHERE s.username IS NOT NULL;

SELECT
  (SELECT name FROM v$database) "DB Name",
  ROUND(SUM(used.bytes)/1024/1024, 2) || ' MB' "Total Size",
  ROUND(SUM(used.bytes)/1024/1024 - free.p/1024/1024, 2) || ' MB' "Used",
  ROUND(free.p/1024/1024, 2) || ' MB' "Free",
  ROUND((SUM(used.bytes)/1024/1024)
    / (SELECT sysdate - MIN(creation_time) FROM v$datafile), 2)
    || ' MB/day' "Daily Growth"
FROM (
  SELECT bytes FROM v$datafile
  UNION ALL SELECT bytes FROM v$tempfile
  UNION ALL SELECT bytes FROM v$log
) used,
(SELECT SUM(bytes) AS p FROM dba_free_space) free
GROUP BY free.p;

-- FRA usage by file type
SELECT file_type, percent_space_used, percent_space_reclaimable, number_of_files
FROM v$recovery_area_usage
ORDER BY 1;

-- FRA size and used
SELECT name,
       ROUND(space_limit/1024/1024, 0)  size_MB,
       ROUND(space_used/1024/1024, 0)   used_MB,
       DECODE(NVL(space_used,0), 0, 0,
         ROUND(space_used/space_limit*100)) pct_used
FROM v$recovery_file_dest;

-- Find duplicate datafile names (different paths, same filename)
SELECT SUBSTR(file_name, INSTR(file_name,'/',-1)) file_name, COUNT(*)
FROM dba_data_files
GROUP BY SUBSTR(file_name, INSTR(file_name,'/',-1))
HAVING COUNT(*) > 1;

-- Get full path of duplicate files
SELECT file_name, status, tablespace_name
FROM dba_data_files
WHERE SUBSTR(file_name, INSTR(file_name,'/',-1)) IN (
  SELECT SUBSTR(file_name, INSTR(file_name,'/',-1))
  FROM dba_data_files
  GROUP BY SUBSTR(file_name, INSTR(file_name,'/',-1))
  HAVING COUNT(*) > 1
);

# Check cluster resource status
crsctl stat res -t

# Database operations
srvctl status database -d PRODDB
srvctl start  database -d PRODDB
srvctl stop   database -d PRODDB
srvctl start  database -d PRODDB -o mount

# Instance level
srvctl start instance -d PRODDB -i PRODDB1
srvctl stop  instance -d PRODDB -i PRODDB1

# ASM disk check
oracleasm querydisk -d DISK_NAME

# ASM diskgroup space
SELECT name,
       ROUND(total_mb/1024, 2) total_GB,
       ROUND(free_mb/1024, 2)  free_GB,
       ROUND(free_mb/total_mb*100) "FREE%"
FROM v$asm_diskgroup
WHERE name LIKE '%DATA%';

-- Show current SGA settings
SHOW PARAMETER sga;

-- Modify SGA
ALTER SYSTEM SET sga_target   = 2G;
ALTER SYSTEM SET sga_max_size = 2G SCOPE=SPFILE;

-- Show and modify PGA
SHOW PARAMETER pga;
ALTER SYSTEM SET pga_aggregate_target = 1G;

-- Find specific archive log files on primary
SELECT name FROM v$archived_log
WHERE thread# = 1
  AND dest_id  = 1
  AND sequence# BETWEEN 460 AND 464;

-- Current archive max sequence
SELECT MAX(sequence#) FROM v$archived_log;

-- Applied sequences per thread
SELECT thread#, MAX(sequence#) applied_seq
FROM v$archived_log
WHERE applied = 'YES'
GROUP BY thread#;

-- Find user details
SELECT username, created, account_status, profile,
       lock_date, expiry_date
FROM dba_users
WHERE username LIKE UPPER('%&user_name%');

-- Object count per owner
SELECT owner, object_type, COUNT(*)
FROM dba_objects
WHERE owner = 'YOUR_SCHEMA'
GROUP BY owner, object_type
ORDER BY owner, object_type;