10 Replies Latest reply on Apr 24, 2013 3:37 PM by R_Bober

    Data Pump 11g Network Import

      I have a need to perform a network mode DataPump import from a database on my old server (HPUX 11.11) to my new 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 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
          889858 wrote:
          . . . E t c . . .

          ... If so, I won't be able to use the Physical Standby as the source of my import ...
          • 2. Re: Data Pump 11g Network Import
            Richard Harrison .
            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?

            1 person found this helpful
            • 3. Re: Data Pump 11g Network Import
              Actually, I did set up a small database on one server, created a physical standby on another server, and ran a network Data Pump direct import on an server. Worked flawlessly! The physical standby was running in READ_ONLY mode, and I set up a database link in the 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 server to import a schema from the 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 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

                • 5. Re: Data Pump 11g Network Import
                  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 .
                    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:

                    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......

                    • 7. Re: Data Pump 11g Network Import
                      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 database running on an HP PA-RISC box under HP-UX 11.11. The target database is running under, 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:

                      • 8. Re: Data Pump 11g Network Import
                        Richard Harrison .

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

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

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

                        • 9. Re: Data Pump 11g Network Import
                          Yes, is REALLY buggy - we had some very interesting issues on that version, which prompted us to upgrade to the version within a few weeks of installing The version appears to be pretty stable and clean, at least in the areas that we use.
                          • 10. Re: Data Pump 11g Network Import
                            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 My target database is running on HP Itanium, HP-UX 11.31, and Oracle 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 (and up, hopefully) facility.