SQL exp dump file is missing

Question

I am using a batch script to export a .dmp from my database using the 'exp' command.

Everything runs fine and logs correctly (creating a date stamped log file as well) but the .DMP file itself is nowhere to be found. Not in the designated 'FILE="c:/foo/bar.dmp"' location or anywhere else.

The exporter says it has completed correctly with no errors, but alas, no DMP file.

It's not a permissions problem, the same script generates the .log and temporary .txt files fine.

The script is being run on Windows Server 2008, and even running the exp manually through command prompt doesn't create a file, with no errors.

Really stumped on this one. Extremely difficult to diagnose since everything swears blind it worked fine. Hopefully someone has seen something like this before and can help.

Thanks in advance.

Example of script:

@echo off
REM setlocal enabledelayedexpansion

::Bunch of variable declarations here

echo **Backup run on %DATE% at %TIME%** >> "E:\Backup Logs\FULL_db_%Ffullstamp%_log.txt"
echo **Backing up FULL database** >> "E:\Backup Logs\FULL_db_%Ffullstamp%_log.txt"

exp xxxx/yyyy@zzzz full=Y log="E:\Backup Logs\FULL_TMP.log" file="E:\Backups\FULL\FULL_db_%fullstamp%.dmp"

type "E:\Backup Logs\FULL_TMP.log" >> "E:\Backup Logs\FULL_db_%Ffullstamp%_log.txt"
del "E:\Backup Logs\FULL_TMP.log"

I've removed a bit of fluff and identifying parts but that's the bread and butter of it. The TXT and LOG files are being created and deleted perfectly, but no DMP file.

Presumably this is Oracle not SQL -Server, if so please retag the question. — Alex K., Oct 20 '15 at 11:07

score 0 · Answer 1 · edited Oct 20 '15 at 07:14

0

on windows wouldn't your path require backslashes instead of forward slashes? i.e. FILE="c:\foo\bar.dmp"'?

edited Oct 20 '15 at 07:14

Bartłomiej Semańczyk

59,234
49
233
358

answered Oct 20 '15 at 00:47

TinMonkey

1,832
1
10
7

Batch can handle both forward slashes and backslashes. – SomethingDark Oct 20 '15 at 01:27

score 0 · Answer 2 · answered Oct 21 '15 at 02:04

Try using expdp which invokes an export using the Oracle Data Pump utility. Your data output file will reside in the directory defined by the Oracle parameter DATA_PUMP_DIR. To find the value of DATA_PUMP_DIR, log into SQL*Plus as 'SYS' and run the following:

select * 
from dba_directories 
where DIRECTORY_NAME = 'DATA_PUMP_DIR';

Also, be sure that your schema has the EXP_FULL_DATABASE role. The Oracle online documentation can give you further clarification.

https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL100

I would try doing the expdp (with the appropriate parameters) from the command-line first to make sure it works. Then insert the expdp command-line into your script.

Hope that helps.

score 0 · Answer 3 · answered Oct 21 '15 at 02:39

0

Solved! Though I feel silly.

Having spaces in the 'FILE=' parameter was the issue.

A path of

"E:\ClientDocuments\Data Extration\ProgressDump.dmp"

Created this

"E:\ClientDocuments\Data.dmp"

Odd, but very fixable.

answered Oct 21 '15 at 02:39

Nicholas Ferrar

1
2

Glad you got your issue solved. I would recommend you start using Data Pump as an alternative solution and get used to that Oracle Utility. (From Oracle docs) : Oracle recommends that you use the new Data Pump Export and Import utilities because they support all Oracle Database 10g features, except for XML schemas and XML schema-based tables. Original Export and Import support the full set of Oracle database release 9.2 features. Also, the design of Data Pump Export and Import results in greatly enhanced data movement performance over the original Export and Import utilities. – tale852150 Oct 21 '15 at 17:25
1

@tale852150 Thanks for the advice, I'll update the procedures for next time. – Nicholas Ferrar Oct 22 '15 at 23:42

score 0 · Answer 4 · answered Nov 14 '19 at 10:52

The proper solution is to triple quote your paths, if they contain spaces etc.:

@Echo Off
Rem SetLocal EnableDelayedExpansion

::Bunch of variable declarations here

(   Echo **Backup run on %DATE% at %TIME%**
    Echo **Backing up FULL database**)>>"E:\Backup Logs\FULL_db_%Ffullstamp%_log.txt"

exp xxxx/yyyy@zzzz full=Y log="""E:\Backup Logs\FULL_TMP.log""" file="""E:\Backups\FULL\FULL_db_%fullstamp%.dmp"""

Type "E:\Backup Logs\FULL_TMP.log">>"E:\Backup Logs\FULL_db_%Ffullstamp%_log.txt"
Del "E:\Backup Logs\FULL_TMP.log"

This method can be seen within Chapter 7 of the Oracle Docs, more specifically, 7.3.3.2 Exporting the Full Oracle Database.

SQL exp dump file is missing

4 Answers4