Oracle version: 11.2.0.1 /Red Hat Enterprise Linux Server release 5.4 (Tikanga)

Hi,
impdp is a command line utility in oracle to import data into a DB schema.

Typically impdp command along with its parameters is run in a single line like

impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp-prod-WMS-12-Sep-2012.dmp parallel=1 LOGFILE=WMS_SIT-imp.log remap_schema=WMS:WMS_SIT remap_tablespace = WMS_DATA:DEV_WMS_01_DATA 

For better readability , I thought of putting each parameter on a separate line by using ' \ ' character as the next line indictor (not sure what is this called in Shell scripting terminology ). Is it New line indicator/separator or Next line separator ?

When I run the below shellscript which has three impdp commands , the second impdp fails with below error (error message is misleading, I think) but the 1st and 3rd impdp commands were succesfull. I have faced this wierd issue several times.

impdp \'/ as sysdba\' \
DIRECTORY=DATA_PUMP_DIR \
DUMPFILE=expdp-prod-WMS-12-Sep-2012.dmp \
parallel=1 \
LOGFILE=WMS_SIT-imp.log \
remap_schema=WMS:WMS_SIT \
remap_tablespace = WMS_DATA:DEV_WMS_01_DATA \  # -------> is there anything wrong with this particular next line indicator ' \' shown in red
 
impdp \'/ as sysdba\' \                         # -------------------> the second imdp command which failed
DIRECTORY=DATA_PUMP_DIR \ 
DUMPFILE=expdp-prod-WMS-12-Sep-2012.dmp \
parallel=1 \
LOGFILE=WMS_SIP_SIT-imp.log \
remap_schema=WMS:WMS_SIP_SIT \
remap_tablespace = WMS_DATA:DEV_WMS_01_DATA \
 
impdp \'/ as sysdba\' \
DIRECTORY=DATA_PUMP_DIR \
DUMPFILE=expdp-prod-WMS-12-Sep-2012.dmp \
parallel=1 \
LOGFILE=TEST_WMS_01-imp.log \
remap_schema=WMS:TEST_WMS_01 \
remap_tablespace = WMS_DATA:TEST_WMS_01_DATA \



nohup threeImports.sh > threeImportsnohup.log &

error I received for the second import:

Import: Release 11.2.0.1.0 - Production on Thu Sep 13 05:29:22 2012
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u07/wmsdata/expdat.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

I don't understand why I get "unable to open dump file" because the dumpfile is present and moreover the two other impdp commands which uses the same dumpfile (1st and 3rd) were succesfull.

Additional Info

Info on very similair issue I had last week

Last week , I had very similair issue. I put 5 impdp commands in a similair script. I don't know if it is a co-incidence , but then also , it was the second impdp that failed with the same error. This is why I am suspecting that something has between the 1st sucessfull impdp and the second impdp command. Anything wrong with the new line indicator shown in red above ?

if impdp can be executed in multiple lines then i really dont think you need \ at the end of each line. You definately dont need \ at the end of impdp command or else it will join next line to current line..

Thank you Vidhyadhar. But I have had similar scripts with 5 impdp commands. But only the second one failed. If ' \ ' character at the last parameter is causing the issue , then all the remaining 4 commands would have errored out. Right ?

I am surprise to see below error

 
ORA-31640: unable to open dump file "/u07/wmsdata/expdat.dmp" for read

check below
Check the permissions on this file & owned user.
Check is file exact location.
If not Might dumpfile could have corrupted.
Are you performing export & import in same versions?

what is the word for ' \ ' (which I call 'next line indicator' ) in shell scripting terminology ? I need to do some googling on how this character behaves within a shell script.

Its just instruct the shell that commands will be continued in the next line..

Hope below will explain it more clearly for you

 
/home/lscpvbf > cat v
echo "vi\
dya\
dhar"
da\
te
/home/lscpvbf > v
vidyadhar
Thu 13 Sep 16:21:45 2012

I don't have so much to say about using "\" more than it's an escape for the following newline character, like esacping e.g. "$" with "\$". So perhaps it's important that there is no character (space etc.) after the "\".

This line:

remap_tablespace = WMS_DATA:DEV_WMS_01_DATA \  # -------> is there anything wrong with this particular next line indicator ' \' shown in red

Perhaps the spaces surrounding "=" is causing problems, and if you should use "\", don't have it at this last line.
Don't know if this is helpful...

@224an has the solution.
In the second instance there is definitely a trailing space at the end of the second line.

To inspect the script, try this sed command which is designed to make end-of-line characters visible (they show as a dollar sign) which in turn lets you see any spurious characters after the \ character.

sed -n l scriptname

Ps. The reverse solidus is known (somewhat confusingly) as the escape character. In this case the \ protects the next character from interpretation by the Shell. The next character being a line-feed, it stops the Shell ending the command string.

Thanks Methyl.
I had same issue again today. So , I ran the sed script on the script.

--Original script

 
$ cat impdpContinued.sh
impdp WMS_OAT_01/WMS_OAT_01_123 \
DIRECTORY=BRCF_EFX_DIR \
DUMPFILE=BRCF_LM_WMS_SCHEMAS-December7-2012.dmp \
LOGFILE=BRCF_LM_WMS_SCHEMAS-SIT.log \
REMAP_SCHEMA=WMS_OAT_01:WMS_SIT_02 \
REMAP_SCHEMA=WMS_OAT_SESS_01:WMS_SIT_SESS_02
 
impdp WMS_OAT_01/WMS_OAT_01_123 \
DIRECTORY=BRCF_EFX_DIR \
DUMPFILE=BRCF_LM_WMS_SCHEMAS-December7-2012.dmp \
LOGFILE=BRCF_LM_WMS_SCHEMAS-UAT.log \
REMAP_SCHEMA=WMS_OAT_01:WMS_UAT_02 \
REMAP_SCHEMA=WMS_OAT_SESS_01:WMS_UAT_SESS_02

--- Running the sed command as methyl suggested to find unwanted characters

 
$ sed -n l impdpContinued.sh
impdp WMS_OAT_01/WMS_OAT_01_123 \\$
DIRECTORY=BRCF_EFX_DIR \\ $
DUMPFILE=BRCF_LM_WMS_SCHEMAS-December7-2012.dmp \\$
LOGFILE=BRCF_LM_WMS_SCHEMAS-SIT.log \\$
REMAP_SCHEMA=WMS_OAT_01:WMS_SIT_02 \\$
REMAP_SCHEMA=WMS_OAT_SESS_01:WMS_SIT_SESS_02$
$
$
impdp WMS_OAT_01/WMS_OAT_01_123 \\$
DIRECTORY=BRCF_EFX_DIR \\ $
DUMPFILE=BRCF_LM_WMS_SCHEMAS-December7-2012.dmp \\$
LOGFILE=BRCF_LM_WMS_SCHEMAS-UAT.log \\$
REMAP_SCHEMA=WMS_OAT_01:WMS_UAT_02 \\$
REMAP_SCHEMA=WMS_OAT_SESS_01:WMS_UAT_SESS_02$

So, do u see any unwanted characters in the sed output? What does the extra second slash before the dollar sign (generated by sed ) mean ?

Ignore the underline for [\\$](file://\\$) . It is some formatting thing with this forum's editor , which I can't seem to remove.