Oracle Scripts

Scan Every Oracle Alert and Trace Log for ORA- Errors with grep Purpose Every Oracle environment generates alert logs and process trace files recording instance events, errors, and diagnostics. When a support call comes in — "the database was slow last night, can you check the logs?" — the fastest first pass is a …

Find the Biggest Files First When an Oracle Filesystem Fills Up Purpose A full Oracle filesystem is a five-alarm incident. Whether it is the /u01/oradata mount holding datafiles, the Fast Recovery Area filling with archive logs, or the diag directory overflowing with trace files, the first question is always the same: …

Recursive find and grep to Search Oracle Trace Files for Any String Purpose Oracle's Automatic Diagnostic Repository (ADR) accumulates hundreds or thousands of trace files — one per background process event, one per incident, one per session that hits an error deep enough to dump. Finding the trace file that contains …

Oracle Export Through a Named Pipe with mknod for Space-Constrained Hosts Purpose Oracle's original Export utility (exp) writes a binary dump file. On production hosts where disk space is tight — where staging a full-database dump and its compressed copy simultaneously would exhaust the filesystem — the classic …

Unpacking Oracle Patch and Install Media with cpio Purpose Before Oracle began shipping all patches as .zip files, the standard delivery format for Oracle install media and large patch bundles was cpio — a tape archive format that predates tar and is still the underlying format used inside every RPM package. DBAs …

Diagnose Oracle Net and RAC Interconnect Routing with netstat -r Purpose Oracle Net connections and Oracle RAC private interconnect traffic both depend on correct IP routing. When an Oracle client cannot connect to the listener, when RAC Cache Fusion or heartbeat traffic is routing over the wrong interface, or when …

Find Oracle Archive Logs Older Than N Days with find -mtime Purpose Oracle databases in archivelog mode write a stream of .ARC (archived redo log) files to a destination directory. The directory fills up. When it fills up, the database hangs. The four find -mtime commands below let a DBA list, delete, compress, or move …

Write Text Files from Oracle PL/SQL with UTL_FILE Example Purpose UTL_FILE is the built-in Oracle PL/SQL package for reading and writing operating-system files from inside the database. The procedure below — taken straight from the shutdownabort.com DBA Quick Guides (Andrew Barry, 2007–2013, preserved via the Wayback …

Sample tnsnames.ora Dedicated Server Entry (with ASM) Purpose The tnsnames.ora file is the client-side address book for Oracle Net. Each entry maps a short alias (SCR9) to a full network address — protocol, host, port, and target service. Without a working tnsnames.ora entry, sqlplus, rman, and any application using …

Sample listener.ora with Static SID_LIST and Extproc Purpose The listener.ora file is the server-side configuration for the Oracle Net listener — the process that accepts incoming client connections and hands them off to a database server process. The sample below — taken straight from the shutdownabort.com DBA Quick …