Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 14663 invoked from network); 15 Sep 2007 05:22:40 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 15 Sep 2007 05:22:40 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 49A817435A5; Sat, 15 Sep 2007 05:43:57 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21835-01; Sat, 15 Sep 2007 05:43:57 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8BD927435DD; Sat, 15 Sep 2007 05:43:55 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Sat, 15 Sep 2007 04:58:55 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D345E744034 for ; Sat, 15 Sep 2007 04:58:54 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 03339-01-2 for ; Sat, 15 Sep 2007 04:58:54 -0400 (EDT) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.176]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D6BE4744222 for ; Sat, 15 Sep 2007 04:58:53 -0400 (EDT) Received: by wa-out-1112.google.com with SMTP id k22so1392963waf for ; Sat, 15 Sep 2007 02:37:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=AFp+zV7ARBVvoTNcBr1/Hj5c8EPWx3TvC876rhqdxJU=; b=Ao577HWR6xayQj2MbB5IJl3GilvN2LloCGKsSy88opkIun4mbbuGgJR7umxNvfx5b1Box2SWv2cm45Kw82Wq/pxteFrPZ1UrhnMWVjVEljj/8fnhO1toXx3NP/7qBPJmjlPJXDH3WJSy8gn3X4TsAN6kzrAXkfdKU5e6xo5jrmE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=IRa0BHSdJ7yo9Gs3LGL1E8gqCCw4jUXChKdt83ksrzREieOgk1HZXHxITCkXUUXny4olUL7uJTJSRDpdnDMTbJnQvD7inqY8pwmJIKjvI3y0aFaEOr05ouILcGG6EMEE063MFEZcYGFhzEOS5TDkTZS1WMKEpmAtJLSo5diIetc= Received: by 10.142.139.6 with SMTP id m6mr677339wfd.1189849056791; Sat, 15 Sep 2007 02:37:36 -0700 (PDT) Received: by 10.142.204.6 with HTTP; Sat, 15 Sep 2007 02:37:36 -0700 (PDT) Message-ID: Date: Sat, 15 Sep 2007 02:37:36 -0700 From: "Owen Smith" To: Oracle-L@freelists.org Subject: "Original" IMP transportable tablespace. IMP wants a datafile? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_129_23559124.1189849056796" X-archive-position: 1591 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: owensmith99@gmail.com Precedence: normal Reply-to: owensmith99@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_129_23559124.1189849056796 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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.dmpdatafiles=/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 ------=_Part_129_23559124.1189849056796 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_129_23559124.1189849056796-- -- http://www.freelists.org/webpage/oracle-l