Sunday, August 26, 2018

ORA-65028 When Creating Pluggable Database Via DBLINK


Symptom:

    We try to take whole data refresh from PROD DB to Stage DB to have more accurate env for developers.  We use dblink to create a pluggable DB from read only PROD DB

SQL>CREATE PLUGGABLE DATABASE TESTPDB FROM NON$CDB@PROD_DBLINK;
ERROR at line 1:
ORA-65028: Unable to open plugin data file at path
ORA-27036: translation error, unable to expand file name
Additional information: 7202
Additional information: 2

In the alert logs, only see
**************************************************************
Undo Create of Pluggable Database TESTPDB with pdb id - 3.
**************************************************************
Deleted Oracle managed file +DATAC1/CASCDBTEST/7000B69622B37FE2E053A27D4598FB7A/DATAFILE/system.552.980410169
ORA-65028 signalled during: CREATE PLUGGABLE DATABASE TESTPDBFROM NON$CDB@PROD_DBLINK...

When you check trace directory of alert log. You will find there are other trace files like diag trc..... which give you more helpful information
There are such error in the diag trc
Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'remote db file read'<='PX Deq: Execute Reply'
     Chain 1 Signature Hash: 0x5c57b818
 [b] Chain 2 Signature: 'PX Deq: Execution Msg'
     Chain 2 Signature Hash: 0x6319d684
 [c] Chain 3 Signature: 'remote db file read'
     Chain 3 Signature Hash: 0x2e6fafb0

So the error is related to DB hang and DB hang is related to parallel execution of creating pdb and remote db file read.

Solution:

     Double check tnsnames.ora are in sync between the rac nodes.
     Shutdown 2nd node and parallel execution as workaround

No comments: