This content has been marked as final. Show 10 replies
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?
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 22.214.171.124 server. Worked flawlessly! The physical standby was running in READ_ONLY mode, and I set up a database link in the 126.96.36.199 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 188.8.131.52 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 184.108.40.206 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!
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.
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:
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......
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 220.127.116.11, 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:
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 18.104.22.168. 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 22.214.171.124 (and up, hopefully) facility.