How to Automate Oracle Backup
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: How to Automate Oracle Backup

  1. #1
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    How to Automate Oracle Backup

    Hi ,

    Its Great Pleasure to share this Automation of Oracle Cold Backup as a Scheduled job to run as per the requirement of the DBA for Windows Platform.

    Oracle 8.1.7 DB Cold Backup on Win2000.

    To Automate a Coldbackup of a DB implement the following steps.
    ===============================================================

    Require 3 files.

    Backup.bat;shutdown.sql;startup.sql the contents of each files is given below.

    Save the files with Proper extension as mentioned above.

    Also Place them in a Proper directory... as in this case c:\

    Check where are the oracle DB files located as Per your installation and mention them in Backup.bat file.

    backup.bat file Content:
    ========================

    echo Backing up DB in Cold Backup mode... >c:\Backup.log
    call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\shutdown.sql" >>c:\Backup.log
    copy c:\oracle\oradata\orcl\*.* c:\backup >>c:\Backup.log
    copy C:\oracle\admin\orcl\pfile\*.* c:\backup >>c:\Backup.log
    copy C:\oracle\admin\DEVDB\bdump\*.* c:\backup >>c:\Backup.log
    call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\startup.sql" >>c:\Backup.log

    Backup.log file is created and all the execution steps in .bat file are traced to it.

    Shutdown.sql Content
    ====================
    connect internal/password
    shutdown immediate
    exit

    Startup.sql Content
    ===================
    connect internal/password
    startup pfile=C:\oracle\ora81\database\initorcl.ora
    exit

    Place all this file in C:\ or D:\ wherever you like.

    Now go to Start==>Settings==>Control Panel==>Scheduled Tasks==>Add Scheduled Task==>Open(double click)==>A Scheduled Task Wizard is Opened==>Click Next==>Browse==>Choose the Batfile ==>Now Plan how you want the Backup Daily,Weekly as per your requirement==>It will ask for the System Administrator Pwd twice==>If you want open the Advance option to have a Look.

    The Scheduled Task for Cold Backup is finished.

    Ensure the destination Cold Backup has enough space.

    The above coldbackup still can be re written as per the following requirements:

    1)Also we can include Compress the Backed up files after copying
    2)Delete the old copies (say not more then 3 days old backup)
    3)Cold back up first to disk and then Moving it to Tape drives etc

    In coming day Hot Backup Automation etc will be posted in the same Thread.

    To Automate the Analyze for a User in the Prod DB as a Nightly Job.
    ===================================================================
    To do a Nightly Analyze of Schema in a Production DB.
    Require 2 files.

    ANALYZE_SYS.bat & Analyze.sql
    Save the files with Proper extension as mentioned above.
    Also Place them in a Proper directory... as in this case c:\

    ANALYZE_SYS.bat
    ===============
    echo Analyzing SYS Schema... >c:\Analyze.log
    call c:\oracle\ora81\bin\svrmgrl.exe command="@c:\analyze.sql" >>c:\Analyze.log

    Analyze.sql
    ===========
    connect internal/password@opsdb
    execute DBMS_UTILITY.ANALYZE_SCHEMA('SYS','COMPUTE')
    exit

    The Analyze.log will trace all the Scheduled Job as Trace file.

    For any Job to be Automated after Placing the files as said in the above cases.Scheduling is done as follows
    ===================================================================
    Now go to Start==>Settings==>Control Panel==>Scheduled Tasks==>Add Scheduled Task==>Open(double click)==>A Scheduled Task Wizard is Opened==>Click Next==>Browse==>Choose the Batfile (backup.bat or ANALYZE_SYS.bat) ==>Now Plan how you want the Backup or Analyze Daily,Weekly as per your requirement==>It will ask for the System Administrator Pwd twice==>If you want open the Advance option to have a Look.

    Cheers

    Padmam
    Last edited by padmam; 12-04-2002 at 02:49 PM.
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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