PGTS Journal, 01 May, 2002

[Journal] Click here for: PGTS Journal Front Page

Hot Backup of a Microsoft Oracle Server with Perl

By Gerry Patterson

Writing Your Own Backup Script

Ok, some people would never dream of doing the mechanical repairs on their own car or removing their own appendix. Similarly it might be argued. Why would anyone want to write their own backup script for Oracle? This is especially true for administrators who have grown up with Microsoft systems, and inherited a click and point mentality. There is a considerable amount of existing software that handles the job for you, including software from Oracle. However there are good reasons to write your own Oracle backup:

  • Education. Writing your own script increases your understanding of how the hot backup process works. This would have to be the most compelling reason to attempt writing and practice using backup scripts, even if you don't intend to use them on your production system. It is possible to schedule and plan for many things in IT. Disasters, however are always unexpected. That's what makes them disasters. And when disaster strikes you may encounter problems during the recovery which are just as unexpected. A disaster following on the heels of disaster can be a real disaster! And it may call for some remedial action. If you had written the procedures yourself, had practiced recovering from them and you understood them, you would be able to make informed decisions and be better equipped to navigate your way through a crisis. If you have only a cook book approach to recovery and your particular disaster has its' own unique ingredients, your destination may be USC (Up the Proverbial Creek in a barbed-wire canoe without paddles).
  • Control. Writing your own scripts gives you the maximum control over your backup. This makes it easier to change the backup to suit changing circumstances at your site.
  • Cost. Depending on which backup software you choose, you may have to pay a license and/or maintenance fee. In most cases, this will not be a priority. The cost savings are minimal and if your organisation can afford to pay for an Oracle system on Windows 2000 Server, you will not be greatly concerned about the the cost of backup software. Nevertheless, if you have written your own backup procedure, you may as well save the cost of buying backup software.

Backup Script

This backup script creates a zipped archive of the database which is transferred to tape in a nightly backup. The reason for taking this approach was for simplicity. Most sites do their backup from a single backup server, and it is difficult to get batch software. If your site has enough disk space (the cheapest of all IT resources these days) then you need only reserve enough space to hold the backup archive. Of course, there is a caveat. In order to be useful, the backup should finish before the tape archive procedure gets around to archiving the zip files. On the other hand if your backup software allows the running of batch files and has a command line utility that allows writing to tape then this job could be changed to write to tape. Alternatively you may just opt for Oracle's RMAN utility.

It is assumed that a site will use more than one procedure, so common paths and procedures are defined in the common script. This is where the backup directory, log directory, temporary directory and compression or archive commands are defined. This particular example uses the pkzip commands in order to create a zip archive. The winzip command line will work just as well, however you need to download the winzip command line support add-on in order to use the winzip batch commands. The hotarch script contains a function LogMsg() which writes a log string to a log file that has a name in the format SID_basename_yymmdd.log in the the folder logdir. Where SID is the oracle SID of the instance, basename is the program name (in this case hotarch) and yymmdd is the date.

In order to restore from a backup you will need to:

  1. Make sure that the archive files are in the archive destination
  2. Shutdown the database
  3. Run the command hotrest. (you must supply system password and oracle_sid on command line). This script will move any existing control files and log files into a temporary zip file. Check the script to see the locations
  4. Perform a standard media recover as follows: 
    	svrmgrl
	connect internal   (supply the sys password)
	startup mount
	recover database until cancel using backup controlfile
	...
	(Now apply the archive log files ... if you want to while away some
	extra time, you can do this manually. However most times you will
	find it much easier to type 'auto' and let the system apply the
	logfiles till it runs out -- you can ignore the final error message)
	...
	alter database open resetlogs
	quit

Note: These scripts assume certain naming conventions for the archive log files. If your site uses naming conventions that are not compatible with the scripts (e.g. thread number in log file etc). You will have to modify the scripts accordingly. Just read the code and check your parameters. Alternatively maybe you would like me to do it for you. Send an e-mail to feedback, for a reasonable quote.



Home | Services | Journal | Links | Contact

Last Updated: 17-Sep-2002 08:43 GMT
To contact us:
Phone: 03 9888 0117
Fax: 03 9888 0042
Email: Click Here
Last Updated: 17-Sep-2002 08:43 GMT

1091 Riversdale Rd
Surrey Hills
VIC 3127