Oracle Scripts

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, …

Oracle CREATE DIRECTORY Command Explained The Oracle CREATE DIRECTORY command creates a directory object — a logical alias inside the Oracle database that points to a real folder on the server's file system. This is one of the most used setup steps for Oracle Data Pump exports and imports, UTL_FILE file operations, …

Oracle Data Pump: Create the DATA_PUMP_DIR Directory Before you can run any Oracle Data Pump export (expdp) or import (impdp) job, Oracle needs to know where to write and read dump files and log files on the server. You do this by creating a directory object inside the database that points to a real folder on the …