Providing You With Handy Scripts
Most of our senior consultants have been working with Oracle technology for over ten years. As you can imagine, they’ve developed quite a few tools and scripts that they carry with them from project to project. Below, you’ll find a sampling of our handy database administration scripts that are yours for the taking. Feel free to download and use them. But first, here’s the part our lawyer wants you to read:
* These scripts are provided by Database Specialists, Inc. for individual use and not for sale. Database Specialists, Inc. does not warrant the scripts in any way and will not be responsible for any loss arising out of their use. By downloading or using these scripts you are agreeing to these terms and conditions.
- Deploy Recovery Manager (RMAN) with this handy script and command file
RMAN is the obvious choice for performing hot backups and managing the flash recovery area (FRA) but the plethora of options go well beyond a basic “backup database” command. Rather than starting from scratch with scripting, adding options one at a time, and going through several cycles of needless debugging, why not use our Linux bash shell script and accompanying RMAN command file complete with error handling and comments. The RMAN command file works like a template, making it easy to use the options you want and remove the one you don’t. The embedded comments help to clarify your backup strategy and serve as a training tool if you’re not totally comfortable with RMAN yet.
- Take a fast hot backup of your database using your filer’s snapshot facility
This script puts all tablespaces of the database into backup mode and calls upon your filer’s snapshot facility to take a snapshot of the database. This allows you to take an “instantaneous” hot backup—you can then copy the snapshot to tape for archival purposes if desired. This script makes sure the snapshot backup will include a copy of the server parameter file, the control file, and a trace listing of the control file. A detailed log report indicates exactly what operations were performed and when, and which archived redo logs would be required to recover the database from the backup set.
- Compare data in two Oracle schemas, table by table and row by row
This script reads all the rows in a specified list of tables in one Oracle schema and compares the data row for row against tables with the same names in another schema. You’ll get a report with summary counts of differing rows, and SQL queries you can run to view all of the data discrepancies. This script is invaluable in situations such as comparing seed data in separate databases, validating regression test data in a QA environment, or testing data load scripts.
- Compare two Oracle schemas and get a report of all the discrepancies
This script is a godsend when you’ve got several schemas that are supposed to be identical, but you just aren’t sure if a little schema change might have crept into one without being replicated to all of the others. It is also useful for validating your schema creation scripts –run your creation scripts in a test environment and then use this tool to compare what you’ve just created against the reference schema already in use.
- Export your database using Oracle Datapump
Every DBA should include Datapump, Oracle’s cross-platform data migration utility, in his toolbox, especially since the legacy export utility is slated for obsolescence. Datapump expdp has a variety of uses, from database upgrades to QA refreshes and logical backups. A great place to start is with this script which has everything you need to do a full, consistent database export including data or just metadata. Of course we’ve taken care of the part of scripting that everyone loves, error handling.
- Drop all schema objects, roles, synonyms, and such
This is really handy when you want to wipe a test or replicated database clean and reload it from scratch, without having to rerun the catalog scripts and recreate the tablespaces.
- Check index fragmentation and bloat before your next tablespace reorg
This script analyzes every index in the tablespace you specify, telling you the index’s current size, number of extents, largest single extent, and (of most interest) the estimated size of the index if you were to rebuild it.
- Take a quick and easy hot backup
This shell script reads the data dictionary in your database to make sure it is including all data files in the backup. The control file and parameter files are backed up, too. This script works on Solaris and Linux, and probably other variations of Unix as well. It’s a quick, effective way to protect your data if you don’t already have a comprehensive backup plan in place. Read the comments at the top of the script for important information. You’ll also need two supporting scripts — get them here and here.
- Export the database, or just the schema definitions, to a compressed file
This is a Korn shell script (written for Solaris, but reasonably portable to other platforms) that is intended to be run by cron on a scheduled basis. The script is pretty robust; it checks for errors every step of the way and sends email if something goes wrong.
- Check the database for fragmentation, lack of free space, and other unpleasant things
This is a Korn shell script (written for Solaris, but reasonably portable to other platforms) that is intended to be run by cron on a scheduled basis. The script checks for a few common problems that could be brewing in your database, and sends email if it spots something that requires DBA attention.