Oracle DBA

Quickly locate large files to free up disk space

1

$ du -k * | sort -nr | cut -f2 | xargs -d '\n' du -sh | more

Oracle Database Connection Tracing

Querying Client IP by Oracle SID

1

$ netstat -anpT | grep oracleSID | awk '{print $5}' | grep -o -E '1.*:' | awk -F ':' '{print $1}' | sort

Query the client IP by listening port 1521

1

$ netstat -anpT | grep 1521 | awk '{print $5}' | grep -o -E '1.*:' | awk -F ':' '{print $1}' | sort

Disconnecting a non-locally connected Oracle session

1

$ kill -9 `ps -ef | grep oracleSID | grep LOCAL=NO | grep -v grep | awk '{print $2}'`

Monitor the number of network connections

Query the number of processes connected to port 1521

1

$ netstat -pan | grep 1521 | wc -l

Query the number of processes connected from a server 192.168.1.1

1

$ netstat -pan | grep 192.168.1.1 | wc -l

Analyze the connection frequency of each client

1

$ netstat -apnT | grep 1521 | awk '{print $5}' | sort -u | grep -v 1521 | grep -v '*' | awk -F ':' '{print $4}' | uniq -c | sort -nr

Show files or directories that take up the most space

1

$ du -s * | sort -nr | head

Calculates the total size of the file for the specified date

1

$ ls --full-time `find ./* -name "log_*.bak"` | grep '2024-04-09' | awk '{print $9}' | xargs du -ck

Document cleanup

Delete all .trc files in the /oracle directory

1

$ find /oracle -name "*trc" -print | xargs rm -rf

Delete all .log files in the /oracle directory 3 days ago

1

$ find /oracle -name "*.log" -mtime +3 -print | xargs rm -rf

Process resource utilization analysis

Top 10 processes with the highest CPU usage

1

$ ps auxw | head -1; ps auxw | sort -rn -k3 | head -10

Top 10 processes with the highest memory consumption

1

$ ps auxw | head -1; ps auxw | sort -rn -k4 | head -10

Top 10 processes that use the most virtual memory

1

$ ps auxw | head -1; ps auxw | sort -rn -k5 | head -10

Real-time monitoring of I/O performance

1

$ iostat -d -x -m 1 3

Audit CPU utilization

1

$ sar -s 08:00:00 -e 10:00:00