Home » RDBMS Server » Server Utilities » "Original" IMP transportable tablespace. IMP wants a datafile?
"Original" IMP transportable tablespace. IMP wants a datafile? [message #267820] Sat, 15 September 2007 04:30 Go to next message
owensmith99
Messages: 11
Registered: September 2007
Location: Dallas
Junior Member
Hello Oracle People,

I'm trying to figure out how to import a transportable tablespace I found.

The tablespace contains the "sample schema"

The export file and corresponding database file look like this:


bash sol oracle ~/product/10r2/assistants/dbca/templates 31 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 31 $ pwd
/h/oracle/product/10r2/assistants/dbca/templates
bash sol oracle ~/product/10r2/assistants/dbca/templates 32 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 32 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 32 $ ll example*
-rwxr-xr-x 1 oracle dba 980992 Jun 27 2006 example.dmp*
-rwxr-xr-x 1 oracle dba 13115392 Jun 27 2006 example01.dfb*
bash sol oracle ~/product/10r2/assistants/dbca/templates 33 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 33 $ cp -p example01.dfb /h/oracle/oradata/orcl/
bash sol oracle ~/product/10r2/assistants/dbca/templates 33 $


I tried a simple command line:

imp transport_tablespace=y file=example.dmp datafiles=/h/oracle/oradata/orcl/example01.dfb tablespaces=EXAMPLE tts_owners=HR,IX,OE,PM,SH

Here is the error I see:

Import: Release 10.2.0.2.0 - Production on Sat Sep 15 03:18:40 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 19721:
"BEGIN sys.dbms_plugts.checkDatafile(NULL,3922789679,5,12800,6,5,4194302,8"
"0,531999,547751,1,20971522,NULL,NULL,NULL); END;"
IMP-00003: ORACLE error 19721 encountered

*** lookee here
ORA-19721: Cannot find datafile with absolute file number 5 in tablespace EXAMPLE
*** lookee here

ORA-06512: at "SYS.DBMS_PLUGTS", line 2065
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
bash sol oracle ~/product/10r2/assistants/dbca/templates 34 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 34 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 34 $


Is imp expecting me to pre-create an empty database file before I run the import?

I've looked a bit at the docs. I see no evidence I need to do this.

Also I tried impdp.

It tells me

ORA-39143: dump file "/h/oracle/product/10r2/rdbms/log/example.dmp" may be an original export dump file

So, I need to use imp.


Here is what my database files currently look like:


03:23:06 SQL> select file_id,file_name from dba_data_files;

FILE_ID
----------
FILE_NAME
------------------------------------------------
4
/h/oracle/oradata/orcl/users01.dbf

3
/h/oracle/oradata/orcl/sysaux01.dbf

2
/h/oracle/oradata/orcl/undotbs01.dbf

1
/h/oracle/oradata/orcl/system01.dbf


03:23:35 SQL>

Thoughts anyone?

Do I need to run some kind of data file creation statement before I run the import?

-Owen

Re: "Original" IMP transportable tablespace. IMP wants a datafile? [message #267866 is a reply to message #267820] Sat, 15 September 2007 19:17 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Format your post

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta063.htm
Re: "Original" IMP transportable tablespace. IMP wants a datafile? [message #267869 is a reply to message #267820] Sat, 15 September 2007 21:49 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
datafiles=/h/oracle/oradata/orcl/example01.dfb

What is this ? Is this the the exact name of your datafile?(.dfb)
And how many datafiles you are Plugging? Specify them all.
Re: "Original" IMP transportable tablespace. IMP wants a datafile? [message #267880 is a reply to message #267820] Sun, 16 September 2007 00:55 Go to previous messageGo to next message
owensmith99
Messages: 11
Registered: September 2007
Location: Dallas
Junior Member
ok,

I think I understand what is going on.

I now doubt that
$ORACLE_HOME/assistants/dbca/templates/example01.dfb
is a copy of a database file.

What is it?


bash sol oracle ~/product/10r2/assistants/dbca/templates 67 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 67 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 67 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 67 $ ls -la $ORACLE_HOME/assistants/dbca/templates/example01.dfb
-rwxr-xr-x 1 oracle dba 13115392 Jun 27 2006 /h/oracle/product/10r2/assistants/dbca/templates/example01.dfb*
bash sol oracle ~/product/10r2/assistants/dbca/templates 68 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 68 $
bash sol oracle ~/product/10r2/assistants/dbca/templates 68 $

It is probably a binary file which DBCA can convert into a database file
once DBCA knows which characterset lives inside the target database.

The transportable tablespace feature depends on a matching
of charactersets between the transported data file and the target DB.

-Owen
Re: "Original" IMP transportable tablespace. IMP wants a datafile? [message #267882 is a reply to message #267820] Sun, 16 September 2007 01:13 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
I now doubt that
$ORACLE_HOME/assistants/dbca/templates/example01.dfb
is a copy of a database file.


It should/must be data file of the database while the tablespace is in read only mode.
What you doubt?
Re: "Original" IMP transportable tablespace. IMP wants a datafile? [message #267886 is a reply to message #267880] Sun, 16 September 2007 01:54 Go to previous messageGo to next message
owensmith99
Messages: 11
Registered: September 2007
Location: Dallas
Junior Member
Now I got it figured out.

$ORACLE_HOME/assistants/dbca/templates/example01.dfb

Is a data file backup (.dfb).

It was created by RMAN I suspect.

I wrote the following shell script to get the file restored
and the tablespace transported:

#! /bin/sh

. /h/oracle/.orcl

cd $ORACLE_HOME/demo/schema/

sqlplus '/as sysdba' @mkplug.sql s s s s s s s \
$ORACLE_HOME/assistants/dbca/templates/example.dmp \
$ORACLE_HOME/assistants/dbca/templates/example01.dfb \
$ORACLE_BASE/oradata/orcl/example01.dbf \
$ORACLE_HOME/demo/schema/log

# end


Notice that it calls
$ORACLE_HOME/demo/schema/mkplug.sql

It's a complex SQL script which does many things.

I like the script; it made me happy.

-Owen
Re: "Original" IMP transportable tablespace. IMP wants a datafile? [message #267888 is a reply to message #267820] Sun, 16 September 2007 02:23 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Laughing
Previous Topic: Export Command Related
Next Topic: Exclude a table from an export
Goto Forum:
  


Current Time: Sun Dec 04 14:52:29 CST 2016

Total time taken to generate the page: 0.04541 seconds