10 Replies Latest reply: Apr 24, 2013 10:37 AM by R_Bober RSS

    Data Pump 11g Network Import

    R_Bober
      I have a need to perform a network mode DataPump import from a 10.2.0.4 database on my old server (HPUX 11.11) to my new 11.2.0.3 database on a new server (HPUX 11.31). What I'd REALLY like to do is to perform the import directly from my Physical Standby database (running in READ_ONLY mode while I'm doing the import) rather than having to quiet my production database for a couple of hours while I do the import from there.

      What I'd like to know is if the Data Pump network mode import running on 11.2.0.3 on the new server will create a Data Pump extract job in the old database as part of this direct network link import. If so, I won't be able to use the Physical Standby as the source of my import because Data Pump won't be able to create the master table in the old database. I can't find anything at all in any Oracle documentation about using a Physical Standby as a source. I know I can't do a regular Data Pump export on that database, but I really would like to know if anyone has any experience trying to do this.

      Any feedback would be greatly appreciated.
        • 1. Re: Data Pump 11g Network Import
          L-MachineGun
          889858 wrote:
          . . . E t c . . .

          ... If so, I won't be able to use the Physical Standby as the source of my import ...
          Correct.
          :p
          • 2. Re: Data Pump 11g Network Import
            Richard Harrison .
            Hi,
            Just did a simple test - i opened a database read only and tried it - you get this error:

            ORA-39126: Worker unexpected fatal error in KUPW$WORKER.POST_MT_INIT [SELECT SYS.KUPM$MCP.GET_ENDIANNESS@pumpy FROM SYS.DUAL]
            ORA-02047: cannot join the distributed transaction in progress

            So it looks like it's not possible.......

            Can you open the database as a 'snapshot standby' (can't remember if that was available directly in 10.2 or not) temporarily and then convert back to a standby after the impdp over the network_link has finished?

            Regards,
            Harry
            • 3. Re: Data Pump 11g Network Import
              R_Bober
              Actually, I did set up a small database on one 10.2.0.4 server, created a physical standby on another 10.2.0.4 server, and ran a network Data Pump direct import on an 11.2.0.3 server. Worked flawlessly! The physical standby was running in READ_ONLY mode, and I set up a database link in the 11.2.0.3 database and tested it to make sure I could read the tables in the physical standby. I then kicked off an IMPDP run on the 11.2.0.3 server to import a schema from the 10.2.0.4 physical standby, using the NETWORK_LINK parameter in the parfile. I logged into the database as SYS/SYSDBA for the IMPDP, and it grabbed the schema from the physical standby and imported it into my 11.2.0.3 database. Not sure how this worked, but it's obvious that it the job isn't writing to the physical standby! Didn't think it would work, but every once in a while, you get a pleasant surprise!
              • 4. Re: Data Pump 11g Network Import
                Richard Harrison .
                Hmmm - interesting

                There must be some subtle difference then between a primary database open 'read only' database and a 'read only' standby

                Regards,
                Harry
                • 5. Re: Data Pump 11g Network Import
                  R_Bober
                  I know an EXPDP won't work, because that has to build the master table and put entries in the data pump job tables and so on, but by running an IMPDP on a different database and ONLY accessing the physical standby via the NETWORK_LINK, it must do all of the data pump management on the target database and not touch the physical standby other than to read the metadata and associated data. A handy thing for me, takes all of the pressure off trying to quiet the production primary database when doing this! Thanks for your responses.
                  • 6. Re: Data Pump 11g Network Import
                    Richard Harrison .
                    Hi,
                    I agree with that - it just didn't work for me with a network_link using impdp when the source was a 'read only' normal database - the master table is created in the destination but in the initial datapump processing it wants to set up a distributed transaction - this fails. For your example though when the source is a read only standby rather than a read only 'normal' database that distributed transaction error doesnt occur - and I'm not sure why.
                    Needs further testing i think.....

                    Is it possible you could try your test again but with the source as a normal database in read only mode (as opposed to a read only standby) and see if you get the same error i did?

                    so just do:

                    shutdown;
                    startup mount;
                    alter database open read only;

                    And then try an impdp with network_link to this?

                    It would take me a while to set up a primary/standby to test the case you had where it works OK......

                    Cheers,
                    Harry
                    • 7. Re: Data Pump 11g Network Import
                      R_Bober
                      Bad news, Harry - it worked for me on a standard database opened in READ ONLY mode. Not sure what is different between your environment and mine, but there must be something.... The read only database is a 10.2.0.4 database running on an HP PA-RISC box under HP-UX 11.11. The target database is running under 11.2.0.3, on an HP Itanium box under HP-UX 11.31. The user I logged into on the target database has IMP_FULL_DATABASE privs, and it is the same user id used for the DB_LINK and also the same user id on the source database (which, of course, follows!). This user also has EXP_FULL_DATABASE privs. My par file looks like this:

                      TABLES=AC_LIAB_%
                      NETWORK_LINK=ARCH_LINK
                      DIRECTORY=DATA_PUMP_DIR
                      JOB_NAME=AMIBASE_IMPDP_ARCHDB
                      LOGFILE=DATA_PUMP_DIR:base_impdp_archdb.log
                      REMAP_TABLESPACE=ARCHIVE_BEFORE2003:H_DATA
                      REMAP_TABLESPACE=ARCHIVE_2003:H_DATA
                      REMAP_TABLESPACE=ARCHIVE_2004:H_DATA
                      REMAP_TABLESPACE=ARCHIVE_2005:H_DATA
                      REMAP_TABLESPACE=ARCHIVE_2006:H_DATA
                      REMAP_TABLESPACE=ARCHIVE_2007:H_DATA
                      REMAP_TABLESPACE=ARCHIVE_2008:H_DATA
                      REMAP_TABLESPACE=ARCHIVE_INDEXES:H_INDEXES
                      REUSE_DATAFILES=NO
                      SKIP_UNUSABLE_INDEXES=Y
                      TABLE_EXISTS_ACTION=REPLACE
                      • 8. Re: Data Pump 11g Network Import
                        Richard Harrison .
                        :-)

                        Ok - i had another look - seems it's an 11.2.0.1 bug

                        I had my read only source as 11.2.0.3 and my destinatuion as 11.2.0.1 - this fails with the error above. If i make them both 11.2.0.3 it works perfectly!

                        Nice to know - i think this might be useful for lots of people.

                        Cheers,
                        Harru
                        • 9. Re: Data Pump 11g Network Import
                          R_Bober
                          Yes, 11.2.0.1 is REALLY buggy - we had some very interesting issues on that version, which prompted us to upgrade to the 11.2.0.3 version within a few weeks of installing 11.2.0.1. The 11.2.0.3 version appears to be pretty stable and clean, at least in the areas that we use.
                          • 10. Re: Data Pump 11g Network Import
                            R_Bober
                            Just as an FYI, I was able to get this working. My physical standby is running on HP PA-RISC, HP-UX 11.11, and Oracle 10.2.0.4. My target database is running on HP Itanium, HP-UX 11.31, and Oracle 11.2.0.3. Apparently, impdp only creates job entries on the target database, and only accesses the data/metadata through the NETWORK_LINK. I understand that this can't be done in previous versions, so it looks like this might be an 11.2.0.3 (and up, hopefully) facility.