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

Advertisements

17 Responses to Simple batch file for Oracle database backup

  1. kashif malik says:

    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 says:

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

    kindly help

  3. kashif malik says:

    my date format
    —————-
    dd/mm/yyyyy
    ——————

  4. windows7bugs says:

    Try changing the date format to ‘dd/mm/yy’ and run the export. Report if you have issues.

  5. windows7bugs says:

    if it doesn’t work, try the following format as well for the short date

    M/d/yyyy

  6. raju says:

    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

  7. Naveed says:

    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.

    • windows7bugs says:

      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

  8. Naveed says:

    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

  9. sreejesh says:

    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

  10. oracle developer says:

    i also used this code and it worked easily
    it is nice
    helped me a lot
    thanks

  11. […] This is a follow up post to my previous post about using a simple batch script for creating a dump export file on regular basis for Oracle database. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: