4 Replies Latest reply on Nov 3, 2011 3:06 PM by flavioc

    Cannot import data pump export into XE11 - bug?

    flavioc
      Hi,
      I've just installed XE11g on Windows 2008R2 standard server edition and everything went apparently well.
      After fixing certain firewall rules I managed to connect to XE from SQL Developer running on a Mac and also to Apex, I could successfully create workspaces and import apex applications without problems.
      Then, when I tried to import a dump made from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production by user SYSTEM for a user schema, I am getting the following message in the import.log:

      ORA-39097: Data Pump job encountered unexpected error -30094
      ORA-39065: unexpected master process exception in DISPATCH
      ORA-30094: failed to find the time zone data file for version 11 in $ORACLE_HOME/oracore/zoneinfo

      A brief web search returned a handful of pages where someone mentions a similar problem on the full blown 11gR2 requiring a patch to be installed and in the following page the user is getting the same error in a similar situation after installing XE11 on Windows 7 :

      http://mashuka.dreamwidth.org/6689.html

      Given that XE doesn't normally get any patch being unsupported, does this mean that data pump imports are to be avoided forever?

      I also tried importing the same export dump as the expected schema user, but in that case I am getting a very informative ORA-01031: insufficient privileges.

      Any clues?
      Thanks

      ----
      http://oraclequirks.blogspot.com
      http://www.yocoya.com
        • 1. Re: Cannot import data pump export into XE11 - bug?
          clcarter
          Check the timezone file setting in your XE target, it should contain timezone files up through v14, an unpatched 11.2.0.1 install has timezone files through v11. A directory listing of OH/oracore/zoneinfo should show several timezone_<n>.dat and timezlrg_<n>.dat files, its that simple to see which timezone versions are included.
          $ sqlplus /nolog
          host dir <drive>:\...\oracore\zoneinfo
          ... readme.txt ... timezlrg_11.dat ... timezone_11.dat ... 
          connect system
          ... password ... Connected.
          select * from v$timezone_file;
          FILENAME                VERSION
          -------------------- ----------
          timezlrg_14.dat              14
          The timezone files only affect how the engine handles TIMESTAMP WITH [LOCAL] TIMEZONE data types, and if the schema(s) at the export source don't include those data types, it should not be relevant at all.

          Unless there is something not quite right with the registry settings, does your XE host have another ORACLE_HOME installed? In Windows its possible that programs could be seeing a different OH.

          And for an ora-1031 ... insufficient privileges running impdp ... the user does not have the privileges needed. Not sure how else the utility could gripe to be more clear.

          Does the user have permission to read and write in the data pump directory? ==> grant read, write on directory <directory> to <user>;

          Does the user have sufficient privileges to allow creating tables/procedures/other schema objects? ==> grant resource to <user>;

          Does the user have sufficient space remaining in their tablespace quotas? ==> the resource role includes the 'unlimited tablespace' privilege.
          1 person found this helpful
          • 2. Re: Cannot import data pump export into XE11 - bug?
            flavioc
            Hi,
            in the oracore/zoneinfo folder I find only the following 4 files:

            readme
            timezdif.csv
            timezlrg_14.dat
            timezone_14.dat

            This is apparently what comes with Oracle XE11g, so timezone files of earlier versions are not included (but should they?).
            SELECT * from v$timezone_file;
            
            FILENAME             VERSION
            -------------------- ----------------------
            timezlrg_14.dat      14
            I read some more blog postings concerning the various DST upgrades, but I fail to understand how I am supposed to patch XE11 if there is no support for this.
            At any rate, looking at the error message it seems that the required DST version is 11, which must be the version installed in the customer's DB, what I don't know is if I can ask him to send me the related dat files and simply put them in the oracore/zoneinfo directory or if I need to run some utility to let Oracle acknowledge their presence.


            Concerning the "insufficient privileges" error, I've a tons of ideas on how to make it clearer, what about "ORA:01031: insufficient privileges, CREATE TABLE privilege not granted"? or "ORA:01031: insufficient privileges, failed to CREATE PROCEDURE xyz."?
            It is that simple.
            I mean, "insufficient privileges" as it is, it's more or less like saying "hey, there is a problem with something somewhere", go figure.

            At any rate, this morning at least I could import the metadata without content from the dump using the CONTENT=METADATA_ONLY parameter, attempting to load the data separately is still failing.

            Thanks
            Flavio

            ----
            http://oraclequirks.blogspot.com
            http://www.yocoya.com
            • 3. Re: Cannot import data pump export into XE11 - bug?
              clcarter
              (but should they?)
              Yes they should- on my winXP box, I only have the v14 files. Hadn't noticed that before, and the all files versions 1 thru 14 should be in there for 11.2.0.2.

              May be able to just grab an install for the same platform under a trial license, and copy over the missing ones to the XE home. Or just the v11 ones that might get you past the TZ error.

              Well, no argument the ora-1031 could be a bit more helpful. But read/write on the data pump directory and the ability to create objects by a user ... got to have those privs if its a non-dba role user doing the import.
              there is a problem with something somewhere ... go figure.
              Yeah, that's the fun part! ;)
              1 person found this helpful
              • 4. Re: Cannot import data pump export into XE11 - bug?
                flavioc
                Sorry for the delay in replying but I've been hindered by an annoying problem with the forum login.

                After installing all the missing .dat files in the oracore/zoneinfo folder, I could import the dump without any problems, even if I am still missing a couple of versions, namely 12 and 13 that I could not find anywhere.

                I still don't understand why Oracle didn't provide the files in the distribution of XE.

                Thanks!

                Flavio

                ----
                http://oraclequirks.blogspot.com
                http://www.yocoya.com