| |
 |
|
Administration of Oracle
Oracle Tips by Burleson Consulting |
(After the Bloom is off the Rose . . .)
If you have reached this point, one of three
things has happened: You successfully installed your Oracle system
using the guidelines in Chapter 1 and are anxiously awaiting further
enlightenment; you failed to successfully install your Oracle system
using the guidelines in Chapter 1 but are big-hearted enough to give
this book another chance at proving that it’s worth the purchase
price; or you don’t have Oracle yet or have an existing system and
just want to see what system administration tools this book can
provide. In any case, the next few chapters are the heart of this book
and are really what you paid for.
In this chapter we will look at Oracle
database-level administration and management in detail. We will cover
the tools available to the DBA and administration of the physical
database. In the following chapters we will examine object, space, and
user administration; techniques for using the tools; tuning issues and
solutions; backup and recovery; and security.
As the title of this chapter implies,
Oracle administration isn’t always a rose garden; sometimes it is the
thorns. Hopefully, by using this book, you can avoid some of the
thorns that have gouged Oracle DBAs in the past. In writing this and
subsequent chapters, use was made of the Oracle documentation set;
articles from Oracle magazine; IOUG (International Oracle User’s
Group) presentations; Oracle Internals magazine; DBMS magazine; Oracle
Corporation Support Forums (metalink.oracle.com and technet.oracle.com);
the RevealNet Web site (www.revealnet.com) with its DBA and PL/SQL
pipelines; www.orafans.com; Internet newsgroups (lazyDBA, OAUG); and
my own real-life experiences and those of several Oracle database
experts.
In order to make full use of this
chapter, it is suggested that the DBA either load the scripts from the
Wiley Web site, or load each by hand. The account used to run these
scripts should have the DBA_UTILITIES package, included with the
scripts, installed and have a default and temporary tablespace other
than SYSTEM; the account should not be the SYSTEM account. It is also
suggested that a small (generally around 10 MB or less) tablespace be
created to hold the temporary tables and permanent tables required to
run the various utilities. The DBA_ views should be created and
available. The install scripts must be run so the V$ views are
available. Once the website scripts are downloaded, the file
CRE_DBAUTIL_GRANTS.SQL script must be run from the SYS account to
provide the needed direct grants. The CREA_DBAUTIL_TABS.SQL script
will create the DBA tables and required views. Finally, the
DBA_UTILITIES9.SQL (or DBA_UTILITIES8.SQL if you are on 8 or 8i)
script should be run to create some needed procedures and functions.
This chapter assumes that the DBA is
familiar with basic SQL and SQL*Plus commands. As we move along,
PL/SQL will also be used, so familiarity with these tools would be
helpful. May I suggest Steve Feuerstein’s excellent book on PL/SQL,
PL/SQL Programming (O’Reilly & Associates, 1995), and the companion
volume, PL/SQL Advanced Programming (O’Reilly & Associates, 1996), as
well as Oracle Provided Packages (O’Reilly & Associates, 1996) by
Feuerstein, Beresniwiecz, and Dunn; and of course Oracle PL/SQL Tips
and Techniques, Joseph Trezzo, (Oracle Press, Osborne-McGraw Hill,
1999.) None of the scripts is overly complex, however, and even if you
aren’t an SQL virtuoso, you should be able to make some sense of them.
Some other good books to have on hand are:
Oracle DBA 101 (Oracle Press Editions, Osborne-McGraw
Hill, 2000) by Marlene Theriault, Rachel Carmichael, and James Viscusi.
Oracle Tuning Tips and Techniques (Oracle Press
Editions, Osborne-McGraw Hill, 1999) by Rich Niemiec, Joe Trezzo and
Brad Brown.
In any case, when proceeding through
this chapter, you should have as references the SQL Language Reference
Manual, the SQL*Plus Reference Manual, the Oracle Database
Administrator’s Guide, the PL/SQL Reference Guide, the Oracle RDBMS
Performance Tuning Guide, and the appropriate installation and
administration guide for your operating system or the equivalent
Oracle9i server guides, . The Oracle document set is detailed and
provides excellent information--if you know where to find the data you
need among all of the material provided. The scripts and procedures
presented in this chapter are being used at several sites to manage
and administer Oracle databases. Each has been tested under Oracle8,
Oracle8i, and Oracle9i, and under VMS, NT, Windows, and UNIX
implementations of Oracle databases. It is suggested that the scripts
be called from a centralized menu, either an operating system script
or Forms application. A sample script for both KORNE and BASH shell
are provided on the Web site. By use of shortcuts, which utilize the
location of the scripts as a working directory on Windows-based
systems, you can achieve the same results. This provides for a single
entry of your Oracle username and password instead of having to invoke
SQL*Plus each time you want to run a script. Under NT, I suggest
creating an SQLPLUS icon that has as its working or start-in directory
the script directory under the SQL script storage directory. The
scripts are kept simple for ease of understanding and to ensure that
those who may not have the transaction-processing option or other
advanced Oracle features implemented may still find this book of use.
Only a few of the scripts use PL/SQL; every attempt has been made to
follow the KISS principle (Keep it simple, stupid!).
Generic Tools Available to All DBAs
In almost every Oracle database, the DBA will
have access to SQL, SQL*Plus, and, until 9i, SVRMGR. Almost all 7.2
and later installations may also have the Oracle Enterprise Manager
toolset. In some runtime versions of Oracle, such as are used with
some CAD/CAM packages for drawing tracking, only SVRMGR may be
provided. The PL/SQL tool is always provided with all post-7.0 Oracle
systems as well.
See
Code Depot for Full Scripts
 |
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
