Script to backup db

Thank you for the responce. I do not know much about Oracle and i am not full time into.
Basically what i am looking for is, we have a Oracle database which is not production or anything critical, but it is good to have a back-up.

I read some instructions online and was using the regular export command and it works fine. But the export runs for more than 4-5 hours , after some research online i found that if there are lot of objects with BLOB it takes forever with regular export and data pump is a better alternative and i need a script that does the data pump export nightly.

Use mrjoltcola's command with parallel option, something like this:

expdp system/admin DUMPFILE=DATA_PUMP_DIR:nightly_expdp%U.dmp PARALLEL=4
                                              

1:

Thank you! I will try it and let you know.

Hi, This is how my .bat file looks,

SET ORACLE_HOME=C:\Oracle\10.2
SET ORACLE_SID=ABC
rem +-- Set the path:
SET PATH=C:\oracle\product\10.2.0\BIN
expdp system/admin DUMPFILE=DATA_PUMP_DIR:nightly_expdp_ABC.dmp PARALLEL=4
exit 0
---------

I am sure this is not complete, can you please tell me what else am i missing?

How do i delete the old file? a del command on the top?

Your script is missing the detail in Mike's solution, which is a %U in the filename, which will generate a new filename. How you delete past dump files is a decision for you, and how much space you have and how you are moving the exports off to external backup media.

so basically i change it as shown below? bear with me i am new to this.
expdp system/admin DUMPFILE=DATA_PUMP_DIR:nightly_expdp_%U_ABC.dmp PARALLEL=4

I just want to delete the previous file , what is the command and where do i add it in the script.

It depends on where DATA_PUMP_DIR maps to.

Yes it would be a simple "del" but you need to use the full path. DATA_PUMP_DIR is only known to Oracle as a logical directory. From the host shell, you need to use the full filesystem path.

Prior to the expdp line, you could do:

del C:\dumps\nightly_expdp_*.dmp

Thanks, i changed it as follows,

SET ORACLE_HOME=C:\Oracle\10.2
SET ORACLE_SID=QQC2
rem +-- Set the path:
SET PATH=C:\oracle\product\10.2.0\BIN
del C:\export\nightly_dp_exports\nightly_expdp_%U_QQC2.dmp
expdp system/admin DUMPFILE=C:\export\nightly_dp_exports\nightly_expdp_%U_QQC2.dmp PARALLEL=4
exit 0

--------
have dumb question, dont i have to to grant a user read,write privileges on directory data_pump_dir?
where do i specify the user?

ignore the user privilege question, that was really a dumb one,  obviously it is SYSTEM.

Yes, for SYSTEM (and other dbas) you don't have to specify DIRECTORY=DATA_PUMP_DIR because that is the default, and a DBA has rights to it. For regular users, however, they need privs, and/or must specify a different directory on the expdp command line.

Correct your PATH grasshopper:

SET PATH=C:\oracle\product\10.2.0\BIN;%PATH%

The following is what i have now, it runs ok first time, but only exports system objects, what am i doing wrong. Also, the second time it throws the below shown error, please advice.

rem +-- --------------------------
rem +-- nightly_expdp.cmd - Nightly database expdp backup
rem +-- --------------------------
rem + Set the environment (customize to your locations):
SET ORACLE_HOME=C:\oracle\product\10.2.0
SET ORACLE_SID=ABC
rem +-- Set the path:
SET PATH=C:\oracle\product\10.2.0\BIN
del C:\export\nightly_dp_exports\nightly_expdp_%U_ABC.dmp
del C:\export\nightly_dp_exports\nightly_expdp_%U_ABC2.log
expdp system/tea4two DUMPFILE=DATA_PUMP_DIR:nightly_expdp_%U_ABC.dmp FULL=Y LOGFILE=DATA_PUMP_DIR:nightly_expdp_%U_ABC.log
pause

*********
Error
*******
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:\oracle\product/admin/abc/dpdump/nightly_expdp_nightly
_expdp_U_abc.log"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

I figured that FULL=Y has to be specified before the Dumpfile=, i did it and now it is exporting all the schemas. but still have an issue when i run it the second time,

Connected to: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:\oracle\product/admin/ABC2/dpdump/nightly_expdp_ABC2U.d
mp"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

Oops, %U is for multiple files in a single export session, not a complete new filename, as in RMAN (which is why I confused it).

Unless Mike shows otherwise, I don't think data pump supports those format specifiers. I have always generated the filename from the script first, before passing to exp/expdp.

well, i took out the %U and used,

expdp system/tea4two FULL=y DUMPFILE=DATA_PUMP_DIR:nightly_expdp_ABC.dmp

But the problem arraises when it run the second time saying that the file already exists.
apparently , the "del" statement before the expdp doesnt work, what now?

It helps to post your script after making changes, so we can see.

rem +-- --------------------------
rem + Set the environment (customize to your locations):
SET ORACLE_HOME=C:\oracle\product\10.2.0
SET ORACLE_SID=ABC
rem +-- Set the path:
SET PATH=C:\oracle\product\10.2.0\BIN
del C:\export\nightly_dp_exports\nightly_expdp_ABC.dmp
expdp system/tea4two FULL=y DUMPFILE=DATA_PUMP_DIR:nightly_expdp_ABC.dmp
pause

And the user that is running the script must have OS privileges to delete that file.

yes, the user is a admin on the network.

oh yeah, i just checked, DATA_PUMP_DIR is mapped to,

DATA_PUMP_DIR C:\oracle\product/admin/ABC/dpdump/

i need to map it to C:\export\nightly_dp_exports\, correct?

yep, thats the problem. Drop the directory and re-create.

no luck, still the same problem, the old file doesnt get delete, the export fails on the second run :-(

i was wrong , it is working on the, i used direct path to delete file instead of the DATA_PUMP_IMP directory, as shown below

del C:\export\nightly_dp_exports\nightly_expdp_ABC.dmp
instead of
del DATA_PUMP_DIR:nightly_expdp_ABC.dmp

now i need to get the date on the file ;-).
Thanks for all the help!

Easy:

rem +-- --------------------------
rem +-- nightly_expdp.cmd - Nightly database expdp backup
rem +-- --------------------------
rem + Set the environment (customize to your locations):
SET ORACLE_HOME=C:\oracle\product\10.2.0\
SET ORACLE_SID=ABC
SET PATH=%ORACLE_HOME%;%PATH%
rem
rem +-- Set the DATA_PUMP_DIR (must be same as directory object in database)
SET BKPDIR=C:\export\nightly_dp_exports
for /F "tokens=2-4 delims=/ " %%d in ('date /t') do SET crdt=%%f%%d%%e
rem
rem +-- Remove file if exists
rem %BKPDIR%\nightly_expdp_ABC%crdt%*
rem
rem +-- Export the data (note the current date %crdt% and %%U)
rem
expdp system/tea4two DUMPFILE=DATA_PUMP_DIR:nightly_expdp_ABC%crdt%_%%U.dmp FULL=Y LOGFILE=DATA_PUMP_DIR:nightly_expdp_ABC%crdt%.log
pause
exit 0
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20: