Clone PDB Over DBLink Process Hangs Intermittently With Long Wait...

刘泽宇

现象：
使用dblink克隆pdb的方式创建pdb

create pluggable database NEWDB from OLDDB@db_link ....
The process appears to hang and never finishes. The process seems to be stuck waiting on remote db file read event. A hang analyze dump would show the long wait on remote db file read event, suggesting the cloning process is hung. The process never finishes and the sessions have to be killed.

=> Oracle session identified by:
{
instance: 1 (............)
os id: 21571
process id: 45, oracle@....... (P005)
session id: 219
session serial #: 50661
module name: 1 (DBMS_SCHEDULER05 (TNS V1-V3))
pdb id: 1 (CDB$ROOT)
}
which is waiting for 'remote db file read' with wait info:
{
p1: 'clientid'=0x3
p2: 'count'=0x100000
p3: 'intr'=0x0
time in wait: 1839 min 50 sec
heur. time in wait: 1839 min 50 sec
timeout after: never
wait id: 403087
blocking: 1 session
current sql_id: 793231238
current sql: /* SQL Analyze(69,2) */ SELECT /*+PARALLEL(20) NO_STATEMENT_QUEUING */ * FROM X$KXFTASK /*kpdbfParallelCopyOrMove,PDB_FILE_COPY*/
short stack: ksedsts()+981<-ksdxfstk()+35<-ksdxcb()+6715<-sspuser()+368<-__sighndlr()+6<-call_user_handler()+971<-sigacthandler()+375<-__readv()+10<-nttvrd()+245<-nsvntrc()+296<-nsvdorc()+281<-nsvrecv()+3156<-niovrc()+269<-ttciovconv()+3497<-ksrpc_ttcrcvcbk()+377<-ttcdrv()+1838<-nioqwa()+67<-upirtrc()+4377<-kpurcsc()+110<-ksrpccqread()+772<-ksfqrd()+2508<-kpdbfCopyTaskCbk()+3847<-kxftInvokeTaskCbk()+782<-rpiswu2()+726<-kxftaskload()+575<-qerfxFetch()+5380<-qergiFetch()+566<-qertqoFetch()+1384<-qerpxSlaveFetch()+1846<-qerpxFetc
wait history:
* time between current wait and wait #1: 0.000574 sec
1. event: 'remote db file read'
time waited: 0.000773 sec
wait id: 403086 p1: 'clientid'=0x3
p2: 'count'=0x100000
p3: 'intr'=0x0
* time between wait #1 and #2: 0.000653 sec
2. event: 'remote db file read'
time waited: 0.052549 sec
wait id: 403085 p1: 'clientid'=0x3
p2: 'count'=0x100000
p3: 'intr'=0x0
* time between wait #2 and #3: 0.004632 sec
3. event: 'remote db file read'
time waited: 0.019136 sec
wait id: 403084 p1: 'clientid'=0x3
p2: 'count'=0x100000
p3: 'intr'=0x0
}

Chain 1 Signature: 'remote db file read'<='PX Deq: Execute Reply'
Chain 1 Signature Hash: 0x5c57b818
------------------------------------------------------------------------------



原因：
The issue does not have a clear cause determined.

解决方法：
Collecting fixed objects stats and dictionary stats on the source database before starting the cloning process proved to avoid the hanging issue. For a repeatable cloning process, the stats gathering should also be repeated before each cloning operation would be started.

Fixed objects stats
exec dbms_stats.gather_fixed_objects_stats; (this can be impacting to overall system performance and should be run off-peak interval)

2) Dictionary stats
exec dbms_stats.gather_schema_stats ('SYSTEM')
exec dbms_stats.gather_schema_stats ('SYS');
OR
exec dbms_stats.gather_dictionary_stats;