Perl for Oracle DBAs

Perl for Oracle DBAs

by Andy Duncan, Jared Still
Released August 2002
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596002107

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

Perl is a very powerful tool for Oracle database administrators, but too few DBAs realize how helpful Perl can be in managing, monitoring, and tuning Oracle databases. Whether you're responsible for Oracle9i, Oracle8i, or earlier databases, you'll find Perl an invaluable addition to your database administration arsenal.You don't need to be a Perl expert to use the excellent applications and scripts described in Perl for Oracle DBAs. The book explains what you need to know about Perl, provides a wealth of ready-to-use scripts developed especially for Oracle DBAs, and suggests many resources for further exploration. The book covers:

  • The Perl language -- an introduction to Perl, its rich history and culture, and its extensive text processing and data transformation capabilities.
  • The Perl/Oracle architecture -- Detailed information about Perl DBI, DBD::Oracle, the Oracle Call Interface (OCI), Oracle::OCI, extproc_perl, and mod_perl, the modules that allow Perl programs to communicate with Oracle databases.
  • Perl applications for Oracle DBAs -- Profiles of the best Perl open source applications available for use and customization by Oracle DBAs: Perl/Tk, OraExplain, StatsView, Orac, DDL::Oracle, SchemaDiff, Senora, DBD::Chart, SchemaView-Plus, Oracletool, Karma, Embperl, and Mason.
  • The Perl Database Administration (PDBA) Toolkit -- a comprehensive suite of specialized, ready-to-use scripts designed to help Oracle DBAs perform both routine and special-purpose administrative tasks: monitoring the Oracle alert log and databases, creating and managing Oracle user accounts, maintaining indexes and extents, extracting DDL and data, troubleshooting and tuning database problems, and much more.
The book also explains how Oracle DBAs and developers can extend the toolkit and solve their own database administration problems using Perl.

Table of contents

  1. Perl for Oracle DBAs
  2. A Note Regarding Supplemental Files
  3. Preface
    1. Audience for This Book
    2. Which Platform and Version?
    3. Structure of This Book
    4. About the Perl DBA Toolkit and Examples
    5. Conventions Used in This Book
    6. Comments and Questions
    7. Acknowledgments
      1. From Andy
      2. From Jared
  4. I. Introducing Perl for Oracle
    1. 1. Perl Meets Oracle
      1. What is Perl?
        1. The Origins of Perl
          1. Perl on Win32
          2. CPAN (the Comprehensive Perl Archive Network)
          3. Perl and the corporate world
        2. The Perl Advantage
          1. Flat learning curve
          2. Expression
          3. There’s more than one way to do it (TMTOWTDI)
          4. Flexibility
          5. Ambiguity
          6. Acceptance of the real world
          7. Simplicity
          8. Cooperation and divergence
      2. The Perl/Oracle Architecture
        1. Perl DBI and DBD::Oracle
          1. The origins of Perl DBI
          2. The Perl DBI API
        2. The Oracle Call Interface
      3. Perl for Oracle DBAs
      4. For Further Information
        1. Further Information on Perl
          1. Perl web sites
          2. Perl mailing lists
          3. Perl books
        2. Further Information on Perl DBI
          1. Perl DBI web sites
          2. Perl DBI mailing lists
          3. Perl DBI books
    2. 2. Installing Perl
      1. Installing Perl
        1. Installing Perl on Unix
          1. Finding Perl already installed on your system
          2. Installing Perl from a prebuilt package
          3. Installing Perl from source
            1. Obtaining the required C compiler
            2. Obtaining the source for Perl.
        2. Installing Perl on Win32
      2. Installing Perl DBI
        1. Methods for Installing Perl Modules
          1. The traditional method
          2. The CPAN method
          3. The interactive CPAN shell
          4. CPAN from the command line
          5. The traditional method
        2. Installing Perl DBI on Unix
          1. Installing Perl DBI
          2. Installing DBD::Oracle
        3. Installing Perl DBI on Win32
          1. Running PPM
          2. Getting the latest PPD files
      3. Running Perl DBI
        1. Running a Perl Script on Unix
        2. Running a Perl Script on Win32
        3. DBI by Proxy
      4. Installing Cygwin
        1. Installing Perl under Cygwin
        2. Installing Perl DBI under Cygwin
        3. Installing DBD::Oracle under Cygwin
  5. II. Extending Perl
    1. 3. Perl GUI Extensions
      1. Perl/Tk
        1. Installing Perl/Tk under Unix
        2. Installing Perl/Tk on Win32
        3. Combining Perl/Tk and Perl DBI
      2. OraExplain
      3. StatsView
        1. Installing Tk::GBARR
        2. Downloading StatsView
          1. The need for PNG
          2. Installing zlib
          3. Installing PNG
          4. Installing gnuplot
          5. Installing StatsView
      4. Orac
        1. Installing Orac
          1. Installing Orac on Unix
          2. Installing Orac on Win32
        2. Personalizing Orac
        3. Running Orac
      5. DDL::Oracle
        1. Installing DDL::Oracle on Unix
          1. Using DDL::Oracle with Orac
        2. Installing DDL::Oracle on Win32
        3. Using DDL::Oracle as a Batch and List Processor
        4. defrag.pl
      6. SchemaDiff
        1. Installing SchemaDiff
        2. Running SchemaDiff
      7. Senora
        1. Installing Senora
        2. Senora and SQL*Plus
        3. Running Senora
      8. DBD::Chart
        1. Preparing DBD::Chart
        2. Installing DBD::Chart on Unix
          1. JPEG
          2. The gd library
          3. GD.pm
          4. Completing the DBD::Chart installation
          5. Tk::PNG
          6. Tk::JPEG
        3. Installing DBD::Chart on Win32
          1. Loading DBD::Chart for ActivePerl
      9. SchemaView-Plus
        1. Installing SchemaView-Plus on Unix
        2. Installing SchemaView-Plus on Win32
      10. Open Source Perl IDEs
      11. Open Source Perl GUI Debuggers
    2. 4. Perl Web Extensions
      1. Apache
        1. Installing Apache on Unix
        2. Installing Apache on Win32
        3. Using DBD::Chart with Apache
      2. Oracletool
        1. Installing Oracletool
          1. Installing Oracletool on Unix
          2. Installing Oracletool on Win32
          3. Preferences and privileges
          4. Enhanced security
        2. Using Oracletool
          1. My Oracletool
          2. Oracletool monitoring
      3. Karma
        1. Installing Karma on Unix
          1. Installing TermReadKey.pm
          2. Installing MailTools.pm
          3. Configuring Karma
        2. Installing Karma on Win32
          1. Configuring Karma on Win32
          2. Running Karma on Win32
          3. Extra Perl modules for Win32
    3. 5. Embedding Perl into Apache with mod_perl
      1. mod_perl
        1. Installing mod_perl on Unix
          1. LWP-Library for WWW access in Perl
          2. SSL — Secure Sockets Layer
          3. Installing mod_perl
          4. Specifying the mod_perl Apache library
        2. Apache Perl Modules
          1. Apache::Registry
          2. Apache::DBI
          3. Apache and ORACLE_HOME
        3. Installing mod_perl on Win32
          1. Configuring Apache on Win32
          2. Testing on Win32
          3. HelloWin32.pm
      2. Apache::OWA
        1. Installing Apache::OWA on Unix
        2. Installing Apache::OWA on Win32
        3. Configuring Apache::OWA
    4. 6. Embedded Perl Web Scripting
      1. Embperl
        1. Installing Embperl on Unix
        2. Installing Embperl on Win32
        3. Deploying HTML::Embperl
        4. Looking at Embperl Syntax
          1. Controlling template-driven program flow
          2. Strict variable naming
          3. Useful table tricks
        5. Embperl Forms Handling and Apache::Session
      2. Mason
        1. Installing Mason on Unix
        2. Installing Mason on Win32
          1. Installing Params::Validate
        3. Configuring Mason for Apache
    5. 7. Invoking the Oracle Call Interface with Oracle::OCI
      1. What is Oracle::OCI?
      2. What Is OCI?
        1. Why Oracle::OCI Instead of C?
        2. For More Information on OCI
        3. OCI Functions
      3. Installing Oracle::OCI
        1. Installing Oracle::OCI on Win32
        2. Precursor Modules
        3. Setting the Oracle::OCI Environment
        4. Installing Oracle::OCI on Unix
        5. For Further Help with Oracle::OCI
      4. Coding with Oracle::OCI
        1. Pure Oracle::OCI Code
        2. Pure Perl DBI and DBD::Oracle
        3. Mixing and Matching Oracle::OCI, Perl DBI, and DBD::Oracle
      5. The Future of Oracle::OCI
        1. Oracle::PLSQL
        2. Contributing to the Oracle::OCI Project
        3. Demo Programs
    6. 8. Embedding Perl into PL/SQL
      1. Communication Between Perl and PL/SQL
        1. What are External Procedures?
        2. Embedding Perl in C
        3. Calling the Embedded Perl C Library from PL/SQL
      2. Embedding Perl Within Oracle
        1. EXTPROC security
          1. extproc_perl and Win32
        2. A Detailed Look at extproc_perl
        3. Downloading extproc_perl
        4. Setting Up External Procedures
        5. Debugging External Procedure Listeners
        6. Building a New Perl
          1. The need for a shared libperl
          2. Building Perl for the oracle user
        7. Perl DBI and DBD::Oracle
          1. The importance of OCIExtProcContext
          2. Patching DBD::Oracle
          3. Connecting back to the host database
        8. Installing extproc_perl
          1. ora_perl_boot.pl
          2. Installation steps
        9. Deploying extproc_perl
        10. Testing extproc_perl
  6. III. The Perl DBA Toolkit
    1. 9. Installing the PDBA Toolkit
      1. Introducing the PDBA Toolkit
        1. Supporting Modules
      2. Toolkit Modules
        1. PDBA::CM (Connection Manager)
          1. Special login cases for SYSDBA and SYSOPER
          2. RaiseError and AutoCommit
        2. PDBA::DBA (DBA Methods)
          1. Creating user accounts
        3. PDBA::ConfigFile (Configuration File Handler)
          1. Simplifying configuration
          2. Automatic file searching
        4. PDBA::ConfigLoad (Configuration File Loader)
          1. Loading a Perl configuration script
          2. Referring to configuration variables by package name
        5. PDBA::Daemon and Win32::Daemon (Background Programs)
          1. PDBA::Daemon: Creating a Unix daemon process in Perl
          2. Win32::Daemon: Creating a Win32 daemon in Perl
          3. Using Unix Daemons and Win32 services in Perl
        6. PDBA::GQ (Generic Query)
        7. PDBA::LogFile (Logfile Handler)
          1. PERMS attribute
        8. PDBA::OPT (Option Handler)
        9. PDBA::PidFile (Program Id Handler)
        10. PDBA::PWD (Password Daemon)
          1. Batch job password problems
        11. PDBA::PWC (Password Client)
        12. PDBA::PWDNT (Password Client for NT)
        13. PDBA (PDBA Utilities)
          1. pathsep
          2. osname
          3. pdbaHome
          4. email
      3. Installing the PDBA Toolkit for Unix
        1. Setting the PDBA Environment
          1. Setting PDBA_HOME from the command line
        2. Installing the PDBA Perl Modules and Scripts
          1. Installing additional modules
          2. Determining installation locations for Perl modules
          3. Determining installation locations for Perl scripts
          4. INSTALLSITELIB
          5. Ready to install
        3. Installing PDBA Unix Configuration Files
      4. Installing the PDBA Toolkit for Win32
        1. PDBA Registry Settings
        2. Installing Additional Perl Modules
      5. Configuring the PDBA Toolkit
        1. PDBA Module Configuration
        2. PDBA::CM Module Configuration
        3. Password Server Configuration
          1. $port: Setting the TCP port for the password server
          2. %pwd: Setting the passwords for password server
          3. %users: Setting up password server users
          4. %encryption: Encrypting passwords
          5. %instanceAuth: Setting up per-account authorization
          6. Securing pwd.conf
          7. Running the password server on Unix
          8. Running the password server on Win32
          9. Starting the service
        4. Password Client Configuration
          1. Using PDBA::PWC in your own Perl scripts
    2. 10. Performing Routine DBA Tasks with the PDBA Toolkit
      1. Managing User Accounts
        1. Creating Accounts the Old Way
        2. Creating a Single Account with create_user.pl
          1. Scenario #1
          2. Scenario #2
          3. The create_user.conf configuration file
          4. Tablespaces
          5. create_user.pl
        3. Creating a Single Account With dup_user.pl
        4. Creating Multiple Accounts with mucr8.pl
          1. mucr8.msg
          2. Running mucr8.pl
          3. Account creation dry run
        5. Dropping Oracle Accounts
      2. Maintaining Indexes
        1. Looking at Oracle Space Problems
        2. Rebuilding Indexes with idxr.pl
          1. Fragmentation
          2. Testing idxr.pl
          3. Tracking
      3. Killing Sniped Sessions
        1. Limiting Resource Consumption
        2. Installing kss.pl on Unix
        3. Installing kss_NT.pl on Win32
        4. Configuring kss.pl
      4. Managing Extent Usage
        1. Locally Managed Tablespaces (LMTs)
        2. Examining Object Space with maxext.pl
      5. Extracting DDL and Data
        1. Extracting Data With sqlunldr.pl
          1. Dumping and reloading SCOTT’s schema
          2. Dumping binary data
        2. Extracting DDL with ddl_oracle.pl
    3. 11. Monitoring the Database with the PDBA Toolkit
      1. Monitoring the Alert Log
        1. Where is the Alert Log?
        2. Monitoring with chkalert.pl
        3. Installing and Configuring chkalert on Unix
          1. chkalert.conf
          2. Running chkalert.pl
          3. Testing with a real alert log
        4. Installing and Configuring chkalert_NT.pl for Win32
          1. Starting the service
          2. Testing Oracle_SID_AlertLogMon
      2. Monitoring the Databases
        1. Monitoring Database Connectivity with dbup.pl and dbpu_NT.pl
        2. Installing Additional Modules
          1. Installing Date::Manip on Unix
          2. Installing Date::Manip on Win32
          3. TZ — Time Zones
        3. Using the PDBA::OPT and PDBA::PWC Modules
          1. The password server
          2. Configuring Getopt::Long for pass-through mode
          3. Loading the password client parameters
        4. Configuring dbup.pl and dbup_NT.pl
          1. Win32 preparation
          2. Unix preparation
          3. Configuration on both platforms
          4. Examining uptime requirements
          5. Looking at upDays and upHours parameters
          6. Setting up pager and email addresses
          7. Who’s on Third?
        5. Running the Connectivity Monitor
          1. Testing the monitor
          2. Command-line options
    4. 12. Building a Database Repository with the PDBA Toolkit
      1. Repository Table Structure
        1. Tables from the Oracle Data Dictionary
        2. Specialized Repository Tables
      2. Installing the Repository
      3. Loading the Repository with Data
        1. Collecting Baseline Data
        2. Viewing Repository Data
      4. Reporting on Database Changes
        1. Database Changes
        2. Reporting on Parameter Changes with spdrvr.pl
        3. More Report Examples
        4. spdrvr.pl Implementation
        5. Predefined spdrvr.pl Reports
        6. Command-line Options for spdrvr.pl
          1. Common command-line options
          2. Report-specific command-line options
          3. Using the -report_list option
        7. Options and Tags
          1. Date options
          2. Text options
      5. Reporting on SQL Execution Plans
        1. SXP (SQL EXecution Plan) Scripts and Tables
        2. SXP Limitations
        3. Collecting SQL with sxp.pl
          1. Unique constraint error
          2. Password and privilege messages
        4. Reporting Execution Plans
        5. Checksums
        6. Example SPX Report
          1. Comparing execution plans
          2. Looking at the output
    5. 13. Extending the PDBA Toolkit
      1. Modifying a Script in the Toolkit
        1. The Standard Approach
        2. Checking on Scheduled Jobs with the dba_jobsm.pl Script
          1. Configuring dba_jobsm.pl
          2. dba_jobsm.pl: A walkthrough of the main script
          3. dba_jobsm.pl: A walkthrough of functions and formats
        3. Modifying the dba_jobsm.pl Script
          1. Configuring parameters
          2. Adding passwords to the configuration file
      2. Modifying a Module in the Toolkit
        1. Modifying the PDBA Module to Add a Method
          1. Installing PDBAx on Unix
          2. Installing PDBAx on Win32
        2. Adding a Usage Method
        3. Modifying the PDBA::GQ Module to Deal with NULL Columns
          1. Oracle and NULL values
          2. Testing the use of NULLs
          3. Considering changes to the script
          4. Modifying the PDBA::GQ module
          5. Taking one more step
  7. IV. Appendixes
    1. A. The Essential Guide to Perl
      1. Obtaining Online Information
      2. Running Perl Scripts
      3. Perl Variable Types: Scalars, Arrays, and Hashes
        1. Scalars
        2. Arrays
        3. Hashes
        4. Array and Hash Array Slices
      4. Perl Contexts: Void, Scalar, List, and Boolean
        1. Void
        2. Scalar
        3. List
        4. Boolean
      5. Program and Subroutine Parameters
        1. Environmental Variable Access
        2. Variable Types
        3. Taint Mode
      6. Perl References
        1. Arrow Notation
        2. The ref Operator
        3. Anonymous Arrays and Hashes
      7. Perl’s Object Orientation
        1. Packages
        2. Bless this Object
    2. B. The Essential Guide to Perl DBI
      1. DBI Class Methods
        1. connect
          1. Looking inside the $dbh variable
          2. Alternative Oracle connection scenarios
          3. Oracle-specific connection attributes
        2. connect_cached
        3. available_drivers
        4. data_sources
        5. trace
      2. Database Handles — Preparation
        1. ping
        2. prepare
        3. prepare_cached
        4. quote
      3. Statement Handle Methods
        1. bind_param
        2. bind_param_inout
        3. execute
        4. fetchrow_array
        5. fetchrow_arrayref
        6. fetchrow_hashref
        7. fetchall_arrayref
        8. fetchall_hashref
        9. finish
        10. rows
        11. bind_col
        12. bind_columns
        13. dump_results
      4. Database Handles — SQL and Cleanup
        1. do
        2. selectrow_array
        3. selectall_arrayref
        4. selectall_hashref
        5. selectcol_arrayref
        6. commit
        7. rollback
        8. begin_work
        9. disconnect
      5. Metadata
        1. Statement Handle Metadata
      6. Oracle-Specific Methods
        1. DBMS_OUTPUT Methods
        2. Handling LOBs
        3. Binding Cursors
    3. C. The Essential Guide to Regular Expressions
      1. The Origins of Regular Expressions
        1. The Early History
        2. qed, ed, and vi
        3. Enter Perl
        4. Perl vs. grep
      2. Built-in String Handling Functions
      3. Regular Expression Concepts
        1. Matching, Substitution, and Translation
          1. Regular expression input
          2. The implicit use of $_
        2. The Implicit Left-to-Right Assumption
        3. Regular Expression Architectures
      4. Metacharacters
        1. Character Class Shortcuts
        2. Boundaries
        3. Greediness
        4. Interpolated Strings
        5. Scalar or List Context Results
        6. Alternation and Memory
          1. Capturing backreferences
      5. Match Suffixes
        1. /i — Ignore Case
        2. /g — Global Matching
        3. /s & /m — Single- and Multiple-Line Matching
        4. /o — Compile Only Once
        5. /e — Evaluations
        6. /x — The Expressive Modifier
    4. D. The Essential Guide to Perl Data Munging
      1. What Is Data Munging?
        1. How Data Munging Works
        2. The Art of Algorithms
        3. Enter the Real World
      2. Data-Munging Example: An Inter-Database Transfer
        1. The MySQL Source
        2. The Oracle Sink
      3. Numeric Modules
        1. Number::Format
        2. Mathematics Modules
      4. Date Modules
        1. Date::Calc and Date::Calendar
        2. The Date-Calc-5.0 API
      5. Conversion Modules
        1. Convert::Recode and GNU recode
        2. Text Conversion Modules
          1. Text::Abbrev
          2. Text::ParseWords
          3. Text::Soundex
      6. XML Modules
        1. General Perl XML Parsers
          1. XML::Parser
          2. XML::LibXML
          3. XML::LibXSLT
        2. XML Database Facilities
          1. XML::Generator::DBI
          2. XML::XPath
          3. XML::XMLtoDBMS
          4. Source mapping
          5. Source output
          6. Sink mapping
          7. Sink input
          8. Results
  8. Index
  9. About the Authors
  10. Colophon
  11. Copyright

Product information

  • Title: Perl for Oracle DBAs
  • Author(s): Andy Duncan, Jared Still
  • Release date: August 2002
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596002107