Archive Gap Check on Standby Database

select database_role,open_mode from v$database;

select inst_id,process,pid,status,thread#,sequence#, block# from gv$managed_standby where process like 'MRP%';

select to_char(sysdate,'DD.MM.RR HH24:MI: SS') time, a.thread#, (select max (sequence#) from v$archived_log where archived='YES' and thread#=a.thread#) archived, max (a.sequence#) applied, (select max (sequence#) from v$archived_log where archived='YES' and thread#=a.thread#)-max (a.sequence#) gap from v$archived_log a where a.applied='YES' group by a.thread#;

PROMPT Oracle Log Apply Gap
SELECT ROWNUM, DEST_ID, Thread, Last_Sequence_Received, Last_Sequence_Applied, Difference FROM (SELECT distinct ARCH.DEST_ID,ARCH.THREAD# Thread, ARCH.SEQUENCE# Last_Sequence_Received, APPL.SEQUENCE# Last_Sequence_Applied, (ARCH.SEQUENCE# - APPL.SEQUENCE#) Difference FROM (SELECT THREAD# ,DEST_ID,SEQUENCE# FROM GV$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM GV$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM GV$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM GV$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD#) ORDER BY 2;

ADRCI - Purge Database Logs/traces

 #!/bin/bash
# Script By Mohamed Fowjil
# Function to purge logs and traces older than 30 days using ADRCI for a specific ADR home
purge_adrci() {
    local home_path="$1"
    echo "Purging logs and traces older than 30 days for ADR home: $home_path"
    adrci exec="set homepath $home_path; purge -age 43200"  # 43200 minutes = 30 days
}
# Function to rotate alert logs
rotate_alert_log() {
    local alert_log="$1"
    if [ -f "$alert_log" ]; then
        echo "Rotating alert log: $alert_log"
        mv "$alert_log" "${alert_log}.$(date +%Y%m%d%H%M%S).bak"
        touch "$alert_log"
    else
        echo "Alert log not found: $alert_log"
    fi
}
# Function to rotate listener logs
rotate_listener_log() {
    local listener_log="$1"
    if [ -f "$listener_log" ]; then
        echo "Rotating listener log: $listener_log"
        mv "$listener_log" "${listener_log}.$(date +%Y%m%d%H%M%S).bak"
        touch "$listener_log"
    else
        echo "Listener log not found: $listener_log"
    fi
}
# Function to purge audit files older than 30 days
purge_audit_files() {
    local audit_directory="$1"
    if [ -d "$audit_directory" ]; then
        echo "Purging audit files older than 30 days in: $audit_directory"
        find "$audit_directory" -type f -name "*.aud" -mtime +30 -exec rm -f {} \;
    else
        echo "Audit directory not found: $audit_directory"
    fi
}
# Main script execution
echo "Starting Oracle Database log management..."
# Fetch the list of ADR homes
adr_homes=$(adrci exec="show homes" | grep -v "ADR Homes:" | grep -v "^$")
# Check if there are any ADR homes to process
if [ -z "$adr_homes" ]; then
    echo "No ADR homes found."
    exit 1
fi
# Purge logs for Oracle Database homes
for home in $adr_homes; do
    if [[ "$home" == *"rdbms"* ]]; then
        purge_adrci "$home"
    fi
done
# Rotate alert logs for database  instances
alert_logs=("/u01/app/oracle/diag/rdbms/*/*/trace/alert*.log")
for log in "${alert_logs[@]}"; do
    for log_file in $log; do
        rotate_alert_log "$log_file"
    done
done
# Rotate listener logs
##listener_logs=("/u01/app/oracle/diag/tnslsnr/*/listener*/alert/log.xml" )
for log in "${listener_logs[@]}"; do
    for log_file in $log; do
        rotate_listener_log "$log_file"
    done
done


# Purge audit files in common directories
audit_directories=("/u01/app/oracle/admin/*/adump")
for dir in "${audit_directories[@]}"; do
    for audit_dir in $dir; do
        purge_audit_files "$audit_dir"
    done
done
echo "Oracle Database log management completed."

ASM Usage

set lines 999;
col diskgroup for a15
col diskname for a15
col path for a35
select a.name DiskGroup,b.name DiskName, ROUND(b.total_mb /1024,2) as Total_GB, ROUND((b.total_mb-b.free_mb) /1024,2)As Used_GB, ROUND(b.free_mb / 1024,2) as Free_GB,b.path,b.header_status, ROUND((b.total_mb - b.free_mb) / b.total_mb * 100, 2) AS "USED%"
from v$asm_disk b, v$asm_diskgroup a
where a.group_number (+) =b.group_number
order by b.group_number,b.name;

FRA Usage

set linesize 500
col NAME for a50
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

Test Database Connection

#!/bin/bash

# Set Oracle environment variables

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/19c/db_1

export ORACLE_SID=DBSPRD

export PATH=$ORACLE_HOME/bin:$PATH

# Log file

LOG_FILE=connection_test.log

# Function to test connection

test_connection() {

  echo "Testing Oracle connection..."

  start_time=$(date +%s.%N)

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

WHENEVER SQLERROR EXIT SQL.SQLCODE

SELECT 'Connected to Oracle' FROM dual;

EXIT;

EOF

  end_time=$(date +%s.%N)

  duration=$(echo "$end_time - $start_time" | bc)

  echo "Connection time: ${duration} seconds" >> $LOG_FILE

}

# Run the test multiple times

for i in {1..10}; do

  test_connection

done

echo "Test completed. Check $LOG_FILE for details."