ORA-65198 PDB clone 时 不能新加datafile 以及hang的一个原因

create pluggable database XX  from SS   keystore identified by "YYY" parallel 32 service_name_convert=(' _srv',' _srv');

20TB 4小时

update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1

 alter pluggable database XXX open;------不需要resetlogs
 

create 命令在source 准备，没有并行

下面这个session 是target 库中进行，并行

/* SQL Analyze(10346,0) */ SELECT /*+PARALLEL(32) NO_STATEMENT_QUEUING  */ * FROM X$KXFTASK /*kpdbfParallelCopyOrMove,PDB_FILE_COPY*/

Symptoms

Error reported on Source PDB during any PDB structure management task(Adding new tablespace, Modifying Tablespace size, Resizing datafile size)

ORA-65198: operation not allowed on a PDB when it is being cloned

Changes

None 

Cause

This is found as an expected behavior when PDB clone is in progress and any database maintenance command executed on Source PDB from which clone command executed.

Solution

We are not allowed to perform below activity on Source PDB

1. Creating a new Tablespace/Datafile

2. Dropping Tablespace/Datafile

Execute database maintenance activity only after clone will be completed.

Oracle Database - Enterprise Edition - Version 19.0.0.0 and later
Information in this document applies to any platform.

Symptoms

Create pluggable database PDB_NAME from SOURCE_PDB@clone_pdb
file_name_convert = ('/oracle/u01/app/oracle/oradata/PDN_NAME/', '/oracle/u01/app/oracle/oradata/SOURCE_PDB/')
path_prefix = NONE;

The clone process copies over all the datafiles

On the source production CDB the session connected to CLONE_PDB is inactive.

On the target pre-production CDB the session has 24 processes and is waiting on :

remote db file read

PX Deq: Execution Msg

Changes

Cause

/* SQL Analyze(1787,0) */
SELECT /*+PARALLEL(24) NO_STATEMENT_QUEUING */
* FROM X$KXFTASK /*kpdbfParallelCopyOrMove,PDB_FILE_COPY*/

processes waiting on PX Deq: Execution Msg

Solution

The issue was resolved after running the following on the production CDB and PDB

exec dbms_stats.gather_fixed_objects_stats;

exec dbms_stats.gather_dictionary_stats;

Rerun the clone and it completed successfully. 

You are trying to create PDB clone from remote DB over DB Link.

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

Changes

 No changes, you are trying to create PDB clone from remote DB over DB Link.

Cause

The issue does not have a clear cause determined.

Solution

 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;