Oracle DBA Checklists

Michael R. Ault is an OCP-certified Oracle7, Oracle8, and Oracle8i DBA with over 15 years of experience. He has participated in the Oracle8 and Oracle8i beta programs. Mike is the author of Oracle8i Administration and Manage- ment ( John Wiley & Sons) as well as several other Oracle books and numerous articles on Oracle. He is a partner in The DBAGroup LLC, a consulting firm providing DBA and training services on Oracle projects. He is also the Sysop for the RevealNet DBA Pipeline (http://www.revealnet.com). He is a frequent contributor to DBMS, Oracle, DBPD, and other magazines, as well as a frequent presenter at Oracle Open World, IOUG-A, and ECO. Thomas B. Cox is a former Oracle employee and author of the Oracle Workgroup Server Handbook (Oracle Press), as well as the Low Administration Oracle Specification, the Oracle DBA Checklist, the DBA Maturity Model, and many other white papers and articles. He now works for PricewaterhouseCoopers. Jonathan Gennick is an Oracle Certified Professional and writer. Jonathan has written or coauthored a number of Oracle books, including Oracle SQL*Plus: The Definitive Guide (O’Reilly), Oracle Net8 Configuration and Trouble- shooting (O’Reilly), and Oracle SQL*Loader: The Definitive Guide (O’Reilly). He recently joined O’Reilly as an associ- ate editor specializing in Oracle books. Jim Lopatosky is an Information Technology Consultant for the Maine State Government’s Bureau of Information Services (Augusta, ME), specializing in Oracle database administration. Jim has been involved actively with Oracle User Groups. He took office as President of the Northeast Oracle Users Group (NOUG) in October of 1999. Previously he founded, and presided for three years over, Maine’s Oracle Users Group (MSOUG). Hugo Toledo is Director of Engineering at DaVinci Soft- ware in Chicago. Hugo has worked extensively with

Oracle’s connectivity technologies since 1989 and is a fre- quent speaker at industry conferences. His latest book is Oracle Net8 Configuration and Troubleshooting, written with Jonathan Gennick (O’Reilly). We would also like to thank our reviewers: Stephen Andert reviewed the Net8 section of this book. He is a DBA for First Health Group Corporation and has 10 years of experience working with database technologies. Stephen’s Net8 expertise contributed greatly to the accuracy and relevance of the Net8 material in this book. Victor Slootsky is a Senior Oracle DBA at BAE Systems in Rockville, MD. He is an OCP-certified Oracle7, Oracle8, and Oracle8i DBA with over 20 years of IT experience. Victor is a member of the faculty of the Johns Hopkins University ( JHU) and founder of an Oracle educational environment at the Montgomery County Campus of JHU. There, he has authored and coauthored a number of educational materials about Oracle database administration. He also has authored 11 publications in various scientific journals.

Oracle database management is the first major part of an Oracle DBA’s job. It involves three key tasks: maintaining existing databases, putting up new databases, and fixing broken ones. This section takes a systematic approach to database maintenance and management. It contains check- lists that will help you develop a database management regimen, avoid costly errors when it comes time to move a database into production, and assist with database recovery when trouble strikes and you lose a database object.

– Identify space-bound objects. The NEXT_EXTENT values for space-bound objects are bigger than the largest extent that the tablespace can offer. Space-bound objects can harm database performance. If you encounter such objects, you first need to investigate the situation. Then you can either add another datafile or manually defragment the tablespace using the COALESCE clause of the ALTER TABLESPACE command: Look for segments in the database that are running out of resources (e.g., extents) or growing at an excessive rate. You may need to adjust the storage parameters of these segments. For example, if any object has reached 200 as the number of current extents, upgrade the MAX_EXTENTS parameter in the INIT.ORA file to a value of UNLIMITED.