Perl and the Oracle DBA

Page 1

Perl and the Oracle DBA Or, How to have your oyster, and eat it too.

Who Am I? Jared Still RadiSys Corporation, Hillsboro Oregon, USA Oracle DBA for 10 years Perl aficionado for 10 years

RadiSys Corporation Confidential

What will you learn today? Why I use Perl A Brief History of Perl Origins of DBI and DBD::Oracle Why you should use Perl How you can use Perl Some examples of Perl in action with Oracle

RadiSys Corporation Confidential

Why I use Perl Background DBA since 1994 Unix user since 1988 Written a few bourne and korn shell scripts in that time Also written a fair number of DOS batch scripts.

Began using Perl in 1994. Purpose for using Perl was initially as a reporting language. Soon realized it could do much more Used to create a job scheduler that ran our reports in the background, notifying users when reports were complete.

After this I was hooked. Perl provided much more control over details than was possible in shell scripts. Does not mean that Shell and SQL*Plus were abandoned. Perl was a powerful new tool in the toolbox. RadiSys Corporation Confidential

A Brief History of Perl – with apologies to Dr. Hawking

Perl 1.0 released on 12/18/1987 From the man page: Larry Wall: “The language is intended to be practical (easy to use, efficient, complete) rather than beautiful (tiny, elegant, minimal). It combines (in the author's opinion, anyway) some of the best features of C, sed, awk, and sh, so people familiar with those languages should have little difficulty with it.”

Perl is actually old enough to be considered ‘Legacy’ Perl has gone through several generations Current version is 5.8.x Perl has since evolved to a much more sophisticated language, with a huge library of available modules RadiSys Corporation Confidential

A Brief History of Perl - Cont. Oraperl was created by Kevin Stock in the early 1990’s Oraperl was separate version of Perl that was linked with Oracle libraries. Worked with version 4 of Perl First interface that allowed direct access to Oracle from Perl. This was very cool at the time. All the power of Perl to access the OS, file management, a powerful procedural language and access to Oracle – without the code, compile, link, test cycle. Contractors that knew C and SQL had no trouble working with Perl.

RadiSys Corporation Confidential

A Brief History of Perl - Cont. DBI and DBD::Oracle – the Perl Gateway to Oracle Released by Tim Bunce in 1994 Created to provide a consistent API to databases. Not just Oracle, but also Sybase, DB2, Ingres, MySQL, PostgreSql, Informix and any database accessible via ODBC.

Provides very fast operations on database Allows multiple simultaneous connections to multiple databases. DBD::Oracle is based on Oracle OCI An easy to use OO interface. This does not require scripts using DBI to be written as OO code.

RadiSys Corporation Confidential

Why you should use Perl Adaptability “Swiss Army Knife” of languages Have you ever had to write routines in Shell, SQL or PL/SQL and run into language limitations? • •

Connections to multiple databases not known until runtime. Complex data structures

There are virtually no limitations in Perl and DBI • • • •

Multiple simultaneous database connections Complex data structures are easy to create If you need to deal with LONG datatypes, you can do so in Perl with DBI ( you can’t do so in PL/SQL with a LONG > 32k ) Virtually anything you need to do can be done in Perl.

RadiSys Corporation Confidential

Why You Should use Perl – cont. Complexity Unix users are probably scripting in ksh, or maybe bash. Finer grain of control • • • • •

Easily trap both OS and Oracle errors Powerful command line argument parsing Direct control of many OS features Sockets, ftp, ssh, file handling Language control structures

Several things that Perl can do that you cannot easily in shell scripts, SQL*Plus or PL/SQL • • • • •

Create multiple simultaneous connections to 1 or more databases Perform complex mass transformations on data outside the database Interact with the user – cannot do this in PL/SQL Interact with the user while performing error and sanity checking on user inputs – cannot do this in SQL*Plus or PL/SQL Some of these features available in shell, but not as robust and not cross platform.

Perl give you the most of the power of C, but in an easier to use form.

RadiSys Corporation Confidential

Why You Should use Perl – cont. Compatibility Scripts that are written in Perl can work the same way regardless of platform I write scripts on Linux; then run them on Win32. The same script will work the same way on multiple versions of unix.

Simplicity You may hear that Perl code resembles line noise. •

This can be true of any language

What can be done in other languages can usually be done in less time and fewer lines of code in Perl. •

Perl has some powerful operators and functions that can accomplish a lot with just a little code. Not required to use them.

RadiSys Corporation Confidential

Example of a simple Perl Script simple.pl I use this, or a variation, to begin most of my Perl scripts that connect to Oracle. Includes Parsing of command line arguments Connecting to the database Preparing a SQL statement Executing the SQL statement Displaying the data A basic help screen

RadiSys Corporation Confidential

Simple.pl Magic cookie, documentation and required modules 1 : #!/usr/bin/perl -w 2 : 3 : =head1 simple.pl 4 : 5 : a simple (but robust) DBI script 6 : 7 : This script queries the table DBA_USERS and outputs 8 : the results in a formatted manner. 9 : 10 : Here's an example: 11 : 12 : simple.pl -database dv01 -username scott -password tiger 13 : 14 : You may change the method of output by finding the 'write' 15 : statement, and inserting a single '#' at the beginning of the 16 : line to comment it out. Next, uncomment the 'print' statement 17 : that is following it. 18 : 19 : =cut 20 : 21 : 22 : use warnings; 23 : use strict; 24 : use DBI; 25 : use Getopt::Long;

RadiSys Corporation Confidential

Simple.pl – cont. Process command line arguments 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

: : : : : : : : : : : : : : : : : : : : : : :

my %optctl = (); Getopt::Long::GetOptions( \%optctl, "database=s", "username=s", "password=s", "help" ); usage(0) if $optctl{help}; my($db, $username, $password) = ( $optctl{database}, $optctl{username}, $optctl{password} ); usage(1) unless ( $db and $username and $password ); RadiSys Corporation Confidential

Simple.pl – cont. Connect to the database 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

: : # create a database handle : my $dbh = DBI->connect( : 'dbi:Oracle:' . $db, : $username, $password, : { : RaiseError => 1, : AutoCommit => 0 : } : ); : : die "Connect to $db failed \n" unless $dbh; : : # set the array fetch size : $dbh->{RowCacheSize} = 100; : : # set default Oracle date format : $dbh->do(q{alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'});

RadiSys Corporation Confidential

Simple.pl – cont. Prepare the SQL 67 : 68 : my $sql=qq{select username, created, lock_date, expiry_date from dba_users}; 69 : 70 : # create a statement handle 71 : my $sth = $dbh->prepare($sql); 72 : 73 : # execute the SQL 74 : $sth->execute;

RadiSys Corporation Confidential

Simple.pl – cont

Fetch and display the data 75 : 76 : #print column names 77 : print join(',',@{$sth->{NAME}}),"\n"; 78 : 79 : my @data=(); 80 : while( my @result = $sth->fetchrow_array ) { 81 : 82 : # this line will cause warnings about uninitialized variables 83 : # due to NULL values in SQL return set 84 : #@data = @result; 85 : 86 : # this one will work correctly 87 : @data = map ( defined($_) ? $_ : 'N/A', @result); 88 : 89 : # the above is the equivalent of this 90 : #foreach my $el ( 0..$#result){ 91 : #$data[$el] = $result[$el] ? $result[$el] : 'N/A'; 92 : #} 93 : 94 : # write the data as formatted output 95 : write; 96 : 97 : # or print it CSV style 98 : #print join(',',@data), "\n"; 99 : 100 : }

RadiSys Corporation Confidential

Simple.pl – cont. Disconnect from the database 101 : 102 : $sth->finish; 103 : $dbh->disconnect;

RadiSys Corporation Confidential

Help or usage subroutine 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123

: : : : : : : : : : : : : : : : : : : :

sub usage { my $exitVal = shift; $exitVal = 0 unless defined $exitVal; use File::Basename; my $basename = basename($0); print qq/ usage: $basename -database -username -password

target instance target instance account name target instance account password

example: $basename -database dv07 -username scott -password tiger -sysdba /; exit $exitVal; };

RadiSys Corporation Confidential

Simple.pl – cont. Data formatting 124 : 125 : format STDOUT = 126 : @<<<<<<<<<<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<<<<<< 127 : @data 128 : . 129 :

RadiSys Corporation Confidential

Using Perl every day - Authentication PDBA Password Server Too many passwords! Eliminate hard coded passwords Returns password to the command line for use in shell scripts Authentication required to use server Can restrict password retrieval to certain users

RadiSys Corporation Confidential

Using Perl every day – cont Monitoring Alert Log Monitor (chkalert.pl) Uptime monitor (dbup.pl)

Log Rotation Copy alert.log to new file and compress Truncate remainder Delete archives after 90 days. (alert_log_cleanup.pl)

RadiSys Corporation Confidential

Using Perl every day – Environment poraenv.pl – Oracle Home utility on Win32 Oracle Home utility on Win32 requires GUI access, not very practical for use in scripts that run unattended Oracle Home utility on Win32 is also rather inconvenient for cmd line use Oracle 10g does not include an Oracle Home utility Oracle Home utility included with previous versions of Oracle do not work with 10g. This is fine, as long as 10g is the only version of Oracle on the server, and there is only one instance of it. (demo use of oraenv.cmd. scripts are oraenv.cmd, poraenv.pl, getoraenv.cmd, and getenvval.pl ( show the code – c:\perl\

RadiSys Corporation Confidential

Using Perl every day – Metrics Statspack produces a lot of data. This data may be useful for tuning and spotting trends when trouble arises. “Data” however is not synonymous with “information” One useful metric that can be derived from statspack data is response time.

YAPPPACK from Miracle A/S Use it to create history of response time averages Found at http://miracleas.dk/en/tech.html Yapp provides method to aggregate response time averages from statspack data Instructions included for using data to create a chart in Excel I thought this would be a good metric to track for several production databases. Automated the creation of the charts via Perl, DBD::Oracle and DBD::Chart DBD::Chart is an innovative module that allows the creation of many types of charts directly from table data. These charts are created daily for our production databases. RadiSys Corporation Confidential

Using Perl every day – Metrics cont.

RadiSys Corporation Confidential

Using Perl to simplify difficult tasks "Perl makes the difficult things easy, and the impossible is made possible" - Larry Wall Auditing Auditors and Managers like to see data in Excel spreadsheets. Dump data to CSV, load, massage data, etc. Very time consuming and tedious. Lots of cut and paste involved. For repetitive tasks (such as audit reports), two modules have proved to be a huge benefit.

RadiSys Corporation Confidential

Audit Reports with Perl SpreadSheet::WriteExcel This Perl module allows creation of Excel Spreadsheets with multiple worksheets. Allows most formatting capabilities of excel: colors, cell formats, etc. Cross platform. Files created on Linux work fine on Windows.

SpreadSheet::ParseExcel Security data may be supplied in Excel spreadsheets. This module allows the data to be parsed from the file at runtime. Alternative is to dump to tables, but this is more work, and a may be a manual process.

RadiSys Corporation Confidential

Audit Reports with Perl – cont. Baseline Audits Auditors want to know what has changed in the database since the previous audit. Each change in the database should have a corresponding Change Control Record in your Change Management System. Generally audited from the database back. ie. Changes in the database are identified, change control records examined. You should be able to determine changes that have occurred since the previous audit. Rather than evaluating tools to collect baseline data and report on it, I created one. It does exactly what I need it to do, and the time spent writing it was less than that needed to locate, evaluate, purchase and implement a canned tool. Saving both time, and money. RadiSys Corporation Confidential

Audit Reports with Perl – cont. Baseline Report

RadiSys Corporation Confidential

Audit Reports with Perl – cont. Permissions Audits

Auditors require reports detailing permissions granted in the database. This is known as account reconciliation. As a DBA, you will be the person asked to provide this data. This is another area where a little extra time spent to automate the task will simplify subsequent future audits. Example types of account reconciliation • • • • • •

List of known roles and purpose of each List of known system accounts and purpose of each List of accounts and purpose of each List of accounts and roles and privileges assigned to each List of roles, and each user they are assigned to List of roles, and privileges assigned to each.

This turns out to be quite a bit of work. Some of our security data is stored in spread sheets and must be retrieved from there, and used to validate the user accounts for employees and contractors. This could be a very intensive manual task, and must be performed several times a year. Spreadsheet::ParseExcel module is used to load data from excel spreadsheets into Perl data structures. These are used to validate database security information at runtime. Unknown accounts, roles, etc. are marked as suspect and must be validated by application owners and the DBA. Spreadsheet::WriteExcel is used to create an Excel spreadsheet that can be used for any remaining validation that must be done. Time to do this manually, even using SQL scripts to dump CSV files, is prohibitive. This is a major time saver for the DBA at our company (me) RadiSys Corporation Confidential

Audit Reports with Perl – cont. Permissions Report

RadiSys Corporation Confidential

Recalcitrant Win32 Services Do you have win32 servers? Do some services fail to automatically restart after a reboot? After being bitten by this a couple of times when alert log monitors didn’t start, and backups weren’t made, it was time to do something about it. Use get_oracle_services.pl to query all servers in our domains and retrieve Oracle services Edit resulting configuration file as needed. Use config file to drive NT_check_services.pl Script is run via Windows job scheduler or cron. An attempt to Services that are not running, and the DBA is notified via email of the restart, and its success or failure. RadiSys Corporation Confidential

More Reasons to use Perl Huge library of modules at cpan.org File handling Networking Error handling Directory walking ( File::Find ) Graphics ( Charts and Graphs) Application Specific ( Oracle, Excel)

If you are working on Win32, you have *much* more reason to use Perl. Default batch language on Win32 is somewhat lackluster There are Many Win32 Specific modules Win32::AdminMisc Win32::Daemon ( run Perl as a service ) Win32::TieRegistry Win32::NetAdmin SpreadSheet::ParseExcel SpreadSheet::WriteExcel

Many more‌ Q: As a DBA, why do I care about this stuff?

RadiSys Corporation Confidential

Tips for using Perl Cross Platform Scripting When writing cross platform scripts, some care does need to be taken Always use the unix form of directory separator in Perl scripts, it works just fine on Windows. Write scripts in DOS format: use line terminators of CR/LF when writing on *nix. This makes them easier to read if using Notepad on Windows.

There are exceptions of course Such as when writing scripts that are OS Specific Writing scripts that run in the background Unix runs as a daemon Win32 runs as a Windows service There is a different mechanism for each.

RadiSys Corporation Confidential

Tips for using Perl – cont. Use the Module Library Use the following resources before ‘rolling your own’ http://search.cpan.org http://freshmeat.net And of course, the google and teoma search engines

You may find that someone has already put together a utility that does what you need. Or at least, modules that will simplify your task.

RadiSys Corporation Confidential

Perl Trivia Tell me the official definition of the PERL acronym Tell my the unofficial definition of the PERL acronym

RadiSys Corporation Confidential

Q&A

Questions?

RadiSys Corporation Confidential

References Programming the Perl DBI http://www.oreilly.com/catalog/perldbi/index.html

Perl for Oracle DBA’s http://www.oreilly.com/catalog/oracleperl/

Programming Perl http://www.oreilly.com/catalog/pperl3/index.html

The Perl Cookbook http://www.oreilly.com/catalog/perlckbk2/index.html

Win32 Perl Scripting: The Administrator’s Handbook http://www.roth.net/books/handbook/

Win32 Perl Programming: The Standard Extensions http://www.roth.net/books/extensions2/

http://www.perl.com http://www.perlmonks.org http://search.cpan.org RadiSys Corporation Confidential

Contact, examples and code

Jared Still jkstill@cybcon.com jkstill@gmail.com

Code and examples All zipped up into one convenient tarball http://www.jaredstill.com

RadiSys Corporation Confidential

Turn static files into dynamic content formats.

Create a flipbook
Issuu converts static files into: digital portfolios, online yearbooks, online catalogs, digital photo albums and more. Sign up and create your flipbook.