Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

"Original" IMP transportable tablespace. IMP wants a datafile?

70 views
Skip to first unread message

owens...@gmail.com

unread,
Sep 15, 2007, 5:34:01 AM9/15/07
to
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

sybr...@hccnet.nl

unread,
Sep 15, 2007, 6:21:45 AM9/15/07
to

What you need to do is read the *manuals* and trying to *understand*
them, instead of posting this simultaneously at cdos and the OTN
forums.

Transportable tablespaces are transportable tablespaces are
transportable tablespace.
You actually move a tablespace, which consists of *existing* datafiles
*containing data*. The export will only contain part of the
datadictionary to sync the datadictionary of the target tablespace.
Is that in the docs? Sure, that is in the docs?
Did you *read* those docs? No, you didn't read them, you only *looked*
at them, without even *trying* to make sense of the words.

Next time: PLEASE try to be a bit more industrious, before you start
wasting people's time all over the planet.

--
Sybrand Bakker
Senior Oracle DBA

owens...@gmail.com

unread,
Sep 15, 2007, 3:16:38 PM9/15/07
to
Mr B,

I dont understand why you are so mad.

I list the datafile in the command line.

Here, I'll show you again:

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

I'm just doing what the manuals say.

Also I understand what they say.

The concept of a transportable tablespace is quite simple.

I am not stupid.

I am stumped though.

Help anyone?

-Owen

sybr...@hccnet.nl

unread,
Sep 15, 2007, 6:26:54 PM9/15/07
to

Mr S,

Actually you are stupid.
I explained to you the concept of a transportable tablespace. This
means an existing tablespace including existing datafiles is going to
be transported to a target database.
This means your datafile /h/oracle/oradata/orcl/example01.dfb

(Probably this is a typo, and dfb should be dbf)
is going to be part of the target database.
So what in that message is too difficult for you to understand, and
why do you think you need to create a datafile.

I'm waiting for your apologies, but I'm not holding my breath.

owens...@gmail.com

unread,
Sep 15, 2007, 8:03:15 PM9/15/07
to
Dear Mr B.

I'm sorry that I have upset you.

Perhaps if I explain myself better, I could motivate you to help me.

My understanding is that TTS depends on 2 types of data:

1. meta data stored in an export file
2. "the data" stored in a data base file

My reading of the Docs suggests that a small amount of work needs to
go
into the export.

Once that is done, I have the two types of files I need.

Then, I copy them to the target box.

There, I craft an imp command line to attach the data to the target
DB.

In my situation, I have no control over the export activity.

It was done by Oracle.

If you have a 10gR2 DB, you will find the files in question here:

$ORACLE_HOME/assistants/dbca/templates/

I too thought it odd that the data file was called this:

example01.dfb

Again here is the imp command line I crafted:

cd $ORACLE_HOME/assistants/dbca/templates/
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 banner from my Oracle kernel:

03:23:43 SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release


10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

bash sol oracle ~ 2 $

Here is some info about my platform:

bash sol oracle ~ 3 $
bash sol oracle ~ 3 $
bash sol oracle ~ 3 $ uname -a
SunOS sol 5.10 Generic_118855-14 i86pc i386 i86pc
bash sol oracle ~ 4 $
bash sol oracle ~ 4 $
bash sol oracle ~ 4 $

Thank you for taking the time to read my posts.

I value any opinion or comments you might have.

-Owen

owens...@gmail.com

unread,
Sep 16, 2007, 1:53:18 AM9/16/07
to
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

owens...@gmail.com

unread,
Sep 16, 2007, 2:52:11 AM9/16/07
to
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

0 new messages