Oracle Database Tips by Donald Burleson

Administration of Oracle

(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 (MOSC.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:

See Code Depot

www.dba-oracle.com/oracle_scripts.htm