Perl- sqlplus script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Perl- sqlplus script

Hybrid View

  1. #1
    Join Date
    Feb 2003
    Location
    Tehran/Iran
    Posts
    94

    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.\

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi hamhey

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

    regards
    Hrishy

  3. #3
    Join Date
    Feb 2003
    Location
    Tehran/Iran
    Posts
    94
    Hi,
    Because of department rules .

    //Hamhey

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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/
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  5. #5
    Join Date
    Feb 2003
    Location
    Tehran/Iran
    Posts
    94
    Hi guys,
    Thanks for the help.

    //Hamhey

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width