Simple batch file for Oracle database backup

Hi guys
We are too busy now days. Issues @work keeping us working around the clock and giving us hardly any time to update this blog. Quite recently, we decided to expand this forum with more Oracle technology related posts, as we realize, we get maximum traffic towards the posts related to Oracle.

To begin with, we are providing a simple script for exporting the entire database using “system” user today for Microsoft Windows based implementation. This batch file exports a .dmp file to a user specified directory.

FOR /F "tokens=2-4 delims=/ " %%a IN ('date/t') DO exp system/password@connectionstring full = y file=d:\backup\exp_%%b%%a%%c.dmp

Save this script inside a .bat or .cmd file (eg: myorabackupdaily.bat or myorabackupdaily.cmd). An Administrator can easily create a scheduled job to insure that the batch file runs everyday at a particular time. Preferably during nights while transactions are none or less. Particularly, there is no need to shutdown the database in order to facilitate the export.

Once exported to .dmp file, it should bear a nomenclature as following:
exp_22012011.dmp

exp -> Export
22012011-> Date stamp

hope this article/ batch file is useful for few out there.

For Windows7bugs,

Admin

16 thoughts on “Simple batch file for Oracle database backup

  1. kashif malik

    FOR /F “tokens=2-4 delims=/ ” %%a IN (‘date/t’) DO exp system/@ full = y file=d:\backup\exp_%%b%%a%%c.dmp
    errorr———————
    invalid username and password
    —————————–
    kindly help

  2. kashif malik

    one more thing the file save by bat
    ————————————
    exp_201104
    ———————————
    date is not coming

    kindly help

  3. raju

    i used this batch file in windows 7 , but it is not working , please help me .

    whenever i run the batch file then the cmd window is appearing just 2 seconds and going , but oracle backup file is not creating

  4. Naveed

    I want to take backup of every hour i can do it really well.
    But i want to make a main folder Backup then when the script runs it should make New folder with Current Hour like “5” then it should take dump.

    1. Surprised, would you explain the actual business reasons why you need to take backups every hour? You know already taking the backup against a live database clearly affects the performance??
      Why don’t you enable archive logging instead? Do let me know and we will see whether we will able to provide you additional scripting which would meet your requirements. Please read some notes about archive logging (RMAN) and re-define your backup requirements.

      Admin

  5. Naveed

    Actually this is my script its works really fine but it makes folder with this date format 19.2011 but i want this format 19.11.2011
    Actually we want to keep backup of every hour i know this effects the transactions but it is our company requirement.

    for /f “tokens=2,3,4 delims=:/” %%i in (“%DATE%”) do set DATE=%%i.%%j.%%k
    md “c:\ORABACKUP\PERM\%DATE%”
    for /f “tokens=2,3,4 delims=/ ” %%a in (‘date /t’) do set fdate=%%a%%b%%c
    exp userid=HR/HR@pchrora file=”c:\ORABACKUP\PERM\%DATE%”\HR.DMP log=”c:\ORABACKUP\PERM\%DATE%”\HR%fdate%%ftime%.log
    exp userid=GL/GL@pchrora file=”c:\ORABACKUP\TODAY\%DATE%”\GL.DMP log=”c:\ORABACKUP\PERM\%DATE%”\GL%fdate%%ftime%.log

  6. sreejesh

    Hi Guys,

    I am also a facing a problem as Raju Explained
    “whenever i run the batch file then the cmd window is appearing just 2 seconds and going , but oracle backup file is not creating”.

    and my script is as follows,Please give a reply
    EXP system/oracle@spi FULL=Y FILE=D:\SPI\Backup\orabkp\EXP1.DMP BUFFER=5000000 LOG=D:\SPI\Backup\orabkp\EXP1.LOG

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.