This content has been marked as final. Show 10 replies
. . . E t c . . .
... If so, I won't be able to use the Physical Standby as the source of my import ...
Hi,1 person found this helpful
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 18.104.22.168 server. Worked flawlessly! The physical standby was running in READ_ONLY mode, and I set up a database link in the 22.214.171.124 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 126.96.36.199 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 188.8.131.52 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!
Hmmm - interesting
There must be some subtle difference then between a primary database open 'read only' database and a 'read only' standby
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 184.108.40.206, 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:
Ok - i had another look - seems it's an 220.127.116.11 bug
I had my read only source as 18.104.22.168 and my destinatuion as 22.214.171.124 - this fails with the error above. If i make them both 126.96.36.199 it works perfectly!
Nice to know - i think this might be useful for lots of people.
Yes, 188.8.131.52 is REALLY buggy - we had some very interesting issues on that version, which prompted us to upgrade to the 184.108.40.206 version within a few weeks of installing 220.127.116.11. The 18.104.22.168 version appears to be pretty stable and clean, at least in the areas that we use.
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 22.214.171.124. 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 126.96.36.199 (and up, hopefully) facility.