Home » Infrastructure » Linux » import script doesn't call dump file & oracle_sid (11g, 11.2.01, red-hat 5)
import script doesn't call dump file & oracle_sid [message #592897] Tue, 13 August 2013 00:34 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,

Below are my inputs in batch file -

export ORACLE_HOME=/usr/oracle/app/oracle/product/11.2.0/dbhome_1;
export ORACLE_SID=TAGITEST;
echo "Enter the database name :"
read dbnm
export ORACLE_SID=$dbnm
echo "Enter the datapump name: "
read dmpnm
export DUMP_FILE=$dmpnm


Below is my import script in batch file -
impdp system/oracle dumpfile=%dmpnm% directory=dp_dir logfile=imp_%ORACLE_SID%_`date +"%d%m%y_%H%M"`.log SCHEMAS=INS,REINS,CNFG,RILOG,RNL,CONFSYS,BATJ
OB,DMS,UW,DW,DW_REP,GEN,ACCEXT,RATEENG,MULTCURR,MS


While running SH file, getting below error -
Import: Release 11.2.0.1.0 - Production on Tue Aug 13 11:05:17 2013

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1


Please assist me to resolve the issue. I will also try to find out the solution at my end.

Regards,
Ishika
Re: import script doesn't call dump file & oracle_sid [message #592899 is a reply to message #592897] Tue, 13 August 2013 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
impdp system/oracle dumpfile=%dmpnm%...


%X% is a Windows syntax not a Linux one which is $X

Regards
Michel


Re: import script doesn't call dump file & oracle_sid [message #592902 is a reply to message #592899] Tue, 13 August 2013 01:09 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

Thanks for pointing out the error. I have changed my code as below -

echo .
impdp system/oracle dumpfile=$DUMP_FILE directory=dp_dir logfile=imp_$ORACLE_SID_`date +"%d%m%y_%H%M"`.log SCHEMAS=INS,REINS,CNFG,RILOG,RNL,CONFSYS,B
ATJOB,DMS,UW,DW,DW_REP,GEN,ACCEXT,RATEENG,MULTCURR,MS
echo .
echo .


Even though, script is throwing error.

Import: Release 11.2.0.1.0 - Production on Tue Aug 13 11:50:23 2013

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1



Now, have a glance on it and let me know what else i did mistakes?
Regards
Ishika

[Updated on: Tue, 13 August 2013 01:10]

Report message to a moderator

Re: import script doesn't call dump file & oracle_sid [message #592910 is a reply to message #592902] Tue, 13 August 2013 01:22 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

I saw one solution from the below post but that's doesn't work here.

http://a2zakir.blogspot.in/

UDI-31623: operation generated ORACLE error 31623 / ORA-31623: a job is not attached to this session via the specified handle



While importing a dumpfile to a test server, the following error were raised. Through a tough time searching the solution, I discovered the solution from a post.

My datapump command was like this:

-bash-4.1$ impdp system/sys123 schemas=scott directory=db_pump dumpfile=BEF_EOD.dmp

Import: Release 11.2.0.3.0 - Production on Mon Dec 31 03:30:01 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

The problem was the streams_pool_size was 0. So, according to the post I changed the value to 40M.

SQL> show parameter stream

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0


SQL> alter system set streams_pool_size=40M scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup
ORACLE instance started.


...and it was OK.


Help me out on this.

Regards,
Ishika
Re: import script doesn't call dump file & oracle_sid [message #592911 is a reply to message #592910] Tue, 13 August 2013 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the command is EXACTLY what you posted then it is not valid: several lines, name split in the middle.

Regards
Michel
Re: import script doesn't call dump file & oracle_sid [message #592917 is a reply to message #592911] Tue, 13 August 2013 01:50 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

While coping it from linux, splitting has happened. Import command is in a single line.

My impdp script is itself throwing error.

oracle:/usr/backup>impdp dumpfile=EXPDP_TAGITST1_39_122_220713.DMP directory=dp_dir logfile=imp_TAGITES.log SCHEMAS=INS,REINS,CNFG,RILOG,RNL,CONFSYS,BAUW,DW,DW_REP,GEN,ACCEXT,RATEENG,MULTCURR,MS

Import: Release 11.2.0.1.0 - Production on Tue Aug 13 12:24:23 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1

oracle:/usr/backup>


My TNS entry is -
DBA_TAGITEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbserver2)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TAGITEST)
    )
  )




Please suggest now.

Regards,
Ishika

[Updated on: Tue, 13 August 2013 01:53]

Report message to a moderator

Re: import script doesn't call dump file & oracle_sid [message #592920 is a reply to message #592917] Tue, 13 August 2013 02:07 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

Even for single schema and replacing .dmp with the another one, getting the same error.

oracle:/usr/backup>impdp dumpfile=EXPDP_110813_1648.dmp directory=dp_dir logfile=imp_TAGITES.log SCHEMAS=INS

Import: Release 11.2.0.1.0 - Production on Tue Aug 13 12:44:27 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: system@dba_tagitest
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

UDI-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1


I have shared all my observation. Kindly suggest me how to resolve it. It's not related to database error...rite??

Regards,
Ishika
Re: import script doesn't call dump file & oracle_sid [message #592923 is a reply to message #592920] Tue, 13 August 2013 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see, I advise you to search on MOS.

Regards
Michel
Re: import script doesn't call dump file & oracle_sid [message #592954 is a reply to message #592923] Tue, 13 August 2013 04:58 Go to previous message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

Thanks for your co-operation.

Since, this is my test database and i am struggling lot to cross check my scripts. Script doesn't have any problem.
I have re-created the database and ran the script and it's running. Don't know, what was happened to the database.

My problem is resolved now. Since, it is test database, that's why i have taken confirmation else need to search for solution at MOS.

Regards,
Ishika
Previous Topic: Batch file is not getting executed
Next Topic: RAC and OCFS2
Goto Forum:
  


Current Time: Thu Mar 28 07:29:16 CDT 2024