For a PDB migration, I’ve configured a Refresh PDB clone.
SQL> CREATE PLUGGABLE DATABASE P1QXPTO from P1QXPTO@C1Q_OLDCDB REFRESH MODE EVERY 2 HOURS;
The PDB is 16TB and copying will take a few hours. To get the progress, it is not so straight forward:
- there is no size in V$PDBs until the end
- the ASM space is reserved from the beginning
- there are no files in V$DATAFILE or V$ASM_FILE during the copy
The only place to follow the PDB clone is using V$SQL_LONGOPS, as explained at KB135098 – How to Monitor PDB Clone / Move On ‘Create Pluggable Database’ with ‘COPY’ Clause Statement Execution.
There is explains, we should filter for opname = kpdbfCopyTaskCbk. The output of the query suggested is in my case:
SQL> select opname, sid, serial#, sofar, totalwork, time_remaining, message from v$session_longops where time_remaining > 0;
OPNAME SID SERIAL# SOFAR TOTALWORK TIME_REMAINING MESSAGE
___________________ _______ __________ __________ ____________ _________________ ___________________________________________________________________________________________
kpdbfCopyTaskCbk 404 10446 2432639 4129408 93 kpdbfCopyTaskCbk: +U01/C1QXPTO/48571DAB220517 3: 2432639 out of 4129408 Blocks done
kpdbfCopyTaskCbk 1166 58068 2680703 4127360 75 kpdbfCopyTaskCbk: +U01/C1QXPTO/48571DAB220517 3: 2680703 out of 4127360 Blocks done
kpdbfCopyTaskCbk 26 6056 1191807 4128896 165 kpdbfCopyTaskCbk: +U01/C1QXPTO/48571DAB220517 3: 1191807 out of 4128896 Blocks done
kpdbfCopyTaskCbk 786 41609 563967 4137600 203 kpdbfCopyTaskCbk: +U01/C1QXPTO/48571DAB220517 3: 563967 out of 4137600 Blocks done
This shows there are 4 parallel processes copying the PDB, and each copies – I presume – a datafile. This because the TOTALWORK is mostly around 41.3M blocks, which is about the 32GB small tablespace datafile size for 8K block size.
When one operation it is finished, the session picks another file and the SOFAR starts again from 0.
The string that appears in the Message (+U01/C1QXPTO/48571DAB220517) corresponds to the beginning of the GUID of the PDB being created:
SQL> select name, guid from v$pdbs where con_id=3;
NAME GUID
___________ ___________________________________
P1QXPTO 48571DAB2205170AE063205FE40A503B
Now, using the SID of the parallel coordinator – the QCSID column -, we can see the “history” in V$SESSION_LONGOPS for all files. Based on that, I come up with the query:
SELECT p.name AS pdb_name,
to_char(MIN(start_time),'DD.MM.YY HH24:MI') AS start_time,
dbms_xplan.format_time_s((SYSDATE - MIN(start_time)) * 24 * 60 * 60) AS elapsed_time,
dbms_xplan.format_size(p.total_size) AS pdb_size,
dbms_xplan.format_size(SUM(sofar) * p.block_size) AS so_far,
dbms_xplan.format_size(total_size - (SUM(sofar) * p.block_size)) AS remaining,
ROUND(100 * (SUM(sofar) * p.block_size / p.total_size), 1)||'%' AS pct_complete,
COUNT(*) AS file_count
FROM v$session_longops l
CROSS JOIN v$pdbs@OLD_CDB p
WHERE l.OPNAME = 'kpdbfCopyTaskCbk'
AND p.con_id>2
GROUP BY l.QCSID, p.block_size, p.name, p.total_size, l.sql_exec_start;
This allowed to follow the copy progress of the PDB, with this output:
PDB_NAME START_TIME ELAPSED_TIME PDB_SIZE SO_FAR REMAINING PCT_COMPLETE FILE_COUNT
___________ ________________ ______________ _________ _________ ____________ _______________ ______
SOURCE_PDB 14-01-26 10:57:26 03:24:23 16T 5598G 11T 33% 202
The query uses DBMS_XPLAN functions to automatically format the time and size, as my ex-colleague Frank Pachot once blogged about.
And using the SQLcl REPEAT command allows to have the query to repeat 1000 times automatically every x seconds:
SQL> repeat 1000 60
...
Running 291 of 1,000 @ 7:12:27.997 with a delay of 60s
PDB_NAME START_TIME ELAPSED_TIME PDB_SIZE SO_FAR REMAINING PCT_COMPLETE FILE_COUNT
___________ ________________ ______________ _________ _________ ____________ _______________ ______
SOURCE_PDB 14-01-26 11:43:42 07:28:45 16T 7724G 9257G 45.5% 250
However, after (long) while, I noticed the started time changed. This when the “number of files” were around 250. I stumbled upon the KB832317 in MOS which says what is in its title “V$Session_longops Have Limited Records Of 500”.
In my case the limit was lower, but for the single parallel coordinator was 250 records:
SQL> select count(*) from v$session_longops where opname='kpdbfCopyTaskCbk';
COUNT(*)
___________
250
SQL> select count(*) from v$session_longops;
COUNT(*)
___________
311
So I could not use my query to follow the progression of the PDB clone correctly until the end. Maybe there are other ways to do it?
But to up to 250 datafiles, this solution should be good enough.