Oracle Scripts

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 …

Delete the 500 Oldest Files in a Directory (Oracle DBA) Purpose Oracle's diagnostic destination (DIAGNOSTIC_DEST), audit file destination (AUDIT_FILE_DEST, also known as adump), and per-process trace directories collect thousands of small files over time. Inode counts, not bytes, are usually the limit that bites first …

List Files Opened by an Oracle Process with lsof -p Purpose lsof (LiSt Open Files) is the UNIX tool for inspecting which processes have which files open. For an Oracle DBA it is the fastest answer to four common questions: which datafiles is this server process touching, which trace file is PMON writing into right now, …