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