9 Replies Latest reply: Feb 6, 2013 4:00 AM by 974427 RSS

    error while using transportable tablespace

    974427
      Hi all,

      I am trying to relocate/ migrate a tablespace from windows to linux (oel5), i exported a user schema n while importing i am getting below error... i have placed the datafile,dump,log in linux but when i give import i am getting error.. kindly help me

      Syntax for IMPORT

      [oracle@localhost u01]$ imp userid='test/test as sysdba' transport_tablespace=y datafiles='/u01/app/oracle/oradata/oracle/test.dbf' tablespaces=test file=test.dmp log=testimp.log
      LRM-00112: multiple values not allowed for parameter 'userid'

      IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
      IMP-00000: Import terminated unsuccessfully

      Syntax for EXPORT (@windows)

      C:\Users\Administrator>exp userid='test/test as sysdba' transport_tablespace=y t
      ablespaces=test file=test.dmp log=test.log

      Edited by: 971424 on Feb 5, 2013 3:16 AM
        • 1. Re: error while using transportable tablespace
          Nadeem M
          Your import command is failing with LRM-00112: multiple values not allowed for parameter 'userid' error. Used double quotes like below.
          imp userid="test/test as sysdba" transport_tablespace=y datafiles='/u01/app/oracle/oradata/oracle/test.dbf' tablespaces=test file=test.dmp log=testimp.log
          • 2. Re: error while using transportable tablespace
            asahide
            Hi,

            \ for escape.
            imp userid=\'test/test as sysdba\' transport_tablespace=y datafiles='/u01/app/oracle/oradata/oracle/test.dbf' tablespaces=test file=test.dmp log=testimp.log
            Regards,
            • 3. Re: error while using transportable tablespace
              P.Forstmann
              First you don't need to use SYSDBA privileges to transport tablespaces: any user account with DBA role such as SYSTEM should do the job (use only SYSDBA when it is needed for startup/shutdown/upgrade/patch/backup/restore).

              Second unless you are using Oracle 8 or 9 you should use Data Pump instead of (old) exp/imp.
              • 4. Re: error while using transportable tablespace
                974427
                hi, thanks for the information about sysdba etc i shall look into it.
                actually i forgot to mention in windoes i have 11.1.6 and in oel 11.2 is there any problem due to this?( there wont be as per my little knowledge..)

                also i tried using "\" symbol getting a new error..

                [oracle@localhost u01]$ imp userid=\'test/test as sysdba\' transport_tablespace=y datafiles='/u01/app/oracle/oradata/oracle\test.dbf' tablespaces=test file=test.dmp log=testimp.log

                Import: Release 11.2.0.1.0 - Production on Sat Feb 2 11:56:14 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 - 64bit Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options

                Export file created by EXPORT:V11.01.00 via conventional path
                About to import transportable tablespace(s) metadata...
                import done in US7ASCII character set and AL16UTF16 NCHAR character set
                import server uses WE8MSWIN1252 character set (possible charset conversion)
                export client uses WE8MSWIN1252 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 1565:
                "BEGIN sys.dbms_plugts.beginImpTablespace('TEST',8,'SYS',1,0,8192,1,122079"
                "3,1,2147483645,8,128,8,0,1,2147483645,8,1679341012,1,4129,1219772,NULL,0,0,"
                "NULL,NULL); END;"
                IMP-00003: ORACLE error 1565 encountered
                ORA-01565: error in identifying file '/u01/app/oracle/oradata/oracle\test.dbf'
                ORA-27037: unable to obtain file status
                Linux-x86_64 Error: 2: No such file or directory
                Additional information: 3
                ORA-06512: at "SYS.DBMS_PLUGTS", line 2588
                ORA-06512: at "SYS.DBMS_PLUGTS", line 1619
                ORA-06512: at "SYS.DBMS_PLUGTS", line 1818
                ORA-06512: at line 1
                IMP-00000: Import terminated unsuccessfully
                • 5. Re: error while using transportable tablespace
                  Srini Chavali-Oracle
                  971424 wrote:
                  hi, thanks for the information about sysdba etc i shall look into it.
                  actually i forgot to mention in windoes i have 11.1.6 and in oel 11.2 is there any problem due to this?( there wont be as per my little knowledge..)

                  also i tried using "\" symbol getting a new error..

                  [oracle@localhost u01]$ imp userid=\'test/test as sysdba\' transport_tablespace=y datafiles='/u01/app/oracle/oradata/oracle\test.dbf' tablespaces=test file=test.dmp log=testimp.log

                  Import: Release 11.2.0.1.0 - Production on Sat Feb 2 11:56:14 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 - 64bit Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options

                  Export file created by EXPORT:V11.01.00 via conventional path
                  About to import transportable tablespace(s) metadata...
                  import done in US7ASCII character set and AL16UTF16 NCHAR character set
                  import server uses WE8MSWIN1252 character set (possible charset conversion)
                  export client uses WE8MSWIN1252 character set (possible charset conversion)
                  This indicates that NLS_LANG has not been set (or set incorrectly) before the imp command was invoked. It needs to be set to WE8MSWIN1252.
                  . importing SYS's objects into SYS
                  . importing SYS's objects into SYS
                  IMP-00017: following statement failed with ORACLE error 1565:
                  "BEGIN sys.dbms_plugts.beginImpTablespace('TEST',8,'SYS',1,0,8192,1,122079"
                  "3,1,2147483645,8,128,8,0,1,2147483645,8,1679341012,1,4129,1219772,NULL,0,0,"
                  "NULL,NULL); END;"
                  IMP-00003: ORACLE error 1565 encountered
                  ORA-01565: error in identifying file '/u01/app/oracle/oradata/oracle\test.dbf'
                  ORA-27037: unable to obtain file status
                  Linux-x86_64 Error: 2: No such file or directory
                  The error is self-explanatory - imp cannot find the file you specified. Verify that it exists in the location specified and that the permissions on the file/directory are appropriate
                  Additional information: 3
                  ORA-06512: at "SYS.DBMS_PLUGTS", line 2588
                  ORA-06512: at "SYS.DBMS_PLUGTS", line 1619
                  ORA-06512: at "SYS.DBMS_PLUGTS", line 1818
                  ORA-06512: at line 1
                  IMP-00000: Import terminated unsuccessfully
                  HTH
                  Srini
                  • 6. Re: error while using transportable tablespace
                    Dean Gagne
                    I may be mis-remembering here, but I thought for exp and imp, you were supposed to use "/as sysdba" when using transportable. I thought this was a requirement. For expdp/impdp, the requirement has been removed. I usually work with Data Pump, so my memory is a little more up to date on that product.

                    Dean
                    • 7. Re: error while using transportable tablespace
                      974427
                      ll try out with different user n check...

                      Edited by: 971424 on Feb 6, 2013 1:39 AM
                      • 8. Re: error while using transportable tablespace
                        974427
                        not sure may be because i used exp imp instead of expdp impdp i was unsuccessfull, wrt directories i had an issue(O-H/admin/dpdump), also i set the environment then imported. data got imported successfully :)

                        thanks all for your valuable suggestions.

                        Edited by: 971424 on Feb 6, 2013 1:59 AM

                        Edited by: 971424 on Feb 6, 2013 2:01 AM
                        • 9. Re: error while using transportable tablespace
                          974427
                          problem is solved :) thanks all