Perl- sqlplus script

Hi all,
I've been using DBI and Oracle DBD since two years. But at the moment I would like to use perl using sqlplus interface to connect to database for doing backups.

Has any one used perl script for connecting to oracle database like a Bourne shell script. My problem is to connect as sysdba to do some backup tasks on database that is not possible at the moment. The following is the example for Bourne shell script and I would like to change to perl script of some reasons:

#!/bin/sh
ORACLE_HOME=/opt/apps/oracle/product/8.1.5
CONNECT_STRING="$ORACLE_HOME/bin/sqlplus /noLog"

$CONNECT_STRING < connect / as sysdba
....
...
..
EOF

I would like to write same kind of script in perl like:

#!/usr/bin/perl

...
print < ...
..
EOF

Has any one doing such kind of scripts.

Regards
Hamhey







is not functioning.\

Hi hamhey

May i ask why you prefer perl to shell :-).I am curious

regards
Hrishy

Hi,
Because of department rules .

//Hamhey

Hamhey,
I do not have any experience in PERL but I wish to learn it. I have seen this book being recommended by many. It might help you too.
http://www.oreilly.com/catalog/oracleperl/

Hi guys,
Thanks for the help.

//Hamhey

Hi

I found something on the web for you

http://www.pgts.com.au/pgtsj/pgtsj0205b.html

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:

Make sure that the archive files are in the archive destination
Shutdown the database
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
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.