{"id":1097,"date":"2026-01-14T19:19:42","date_gmt":"2026-01-14T18:19:42","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=1097"},"modified":"2026-03-24T16:22:18","modified_gmt":"2026-03-24T15:22:18","slug":"follow-the-progress-of-a-pdb-remote-cloning","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2026\/01\/14\/follow-the-progress-of-a-pdb-remote-cloning\/","title":{"rendered":"Follow the progress of a PDB remote cloning"},"content":{"rendered":"\n<p>For a PDB migration, I&#8217;ve configured a Refresh PDB clone.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; CREATE PLUGGABLE DATABASE P1QXPTO from P1QXPTO@C1Q_OLDCDB REFRESH MODE EVERY 2 HOURS;\n<\/pre><\/div>\n\n\n<p>The PDB is 16TB and copying will take a few hours. To get the progress, it is not so straight forward:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>there is no size in V$PDBs until the end<\/li>\n\n\n\n<li>the ASM space is reserved from the beginning<\/li>\n\n\n\n<li>there are no files in V$DATAFILE or V$ASM_FILE during the copy<\/li>\n<\/ul>\n\n\n\n<p>The only place to follow the PDB clone is using V$SQL_LONGOPS, as explained at <a href=\"https:\/\/support.oracle.com\/support\/?kmContentId=2866302&amp;page=sptemplate&amp;sptemplate=km-article\" target=\"_blank\" rel=\"noreferrer noopener\">KB135098 &#8211; How to Monitor PDB Clone \/ Move On &#8216;Create Pluggable Database&#8217; with &#8216;COPY&#8217; Clause Statement Execution<\/a>.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>There is explains, we should filter for <code>opname =&nbsp;kpdbfCopyTaskCbk<\/code>. The output of the query suggested is in my case:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; select opname, sid, serial#, sofar, totalwork, time_remaining, message from v$session_longops where time_remaining &gt; 0;\n\n             OPNAME     SID    SERIAL#      SOFAR    TOTALWORK    TIME_REMAINING                                                                                     MESSAGE\n___________________ _______ __________ __________ ____________ _________________ ___________________________________________________________________________________________\nkpdbfCopyTaskCbk        404      10446    2432639      4129408                93 kpdbfCopyTaskCbk: +U01\/C1QXPTO\/48571DAB220517 3: 2432639 out of 4129408 Blocks done\nkpdbfCopyTaskCbk       1166      58068    2680703      4127360                75 kpdbfCopyTaskCbk: +U01\/C1QXPTO\/48571DAB220517 3: 2680703 out of 4127360 Blocks done\nkpdbfCopyTaskCbk         26       6056    1191807      4128896               165 kpdbfCopyTaskCbk: +U01\/C1QXPTO\/48571DAB220517 3: 1191807 out of 4128896 Blocks done\nkpdbfCopyTaskCbk        786      41609     563967      4137600               203 kpdbfCopyTaskCbk: +U01\/C1QXPTO\/48571DAB220517 3: 563967 out of 4137600 Blocks done\n<\/pre><\/div>\n\n\n<p>This shows there are 4 parallel processes copying the PDB, and each copies &#8211; I presume &#8211; 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.<\/p>\n\n\n\n<p>When one operation it is finished, the session picks another file and the SOFAR starts again from 0.<\/p>\n\n\n\n<p>The string that appears in the Message (<code>+U01\/C1QXPTO\/48571DAB220517<\/code>) corresponds to the beginning of the GUID of the PDB being created:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; select name, guid from v$pdbs where con_id=3;\n\n       NAME                                GUID\n___________ ___________________________________\nP1QXPTO      48571DAB2205170AE063205FE40A503B\n<\/pre><\/div>\n\n\n<p>Now, using the SID of the parallel coordinator &#8211; the QCSID column -, we can see the &#8220;history&#8221; in V$SESSION_LONGOPS for all files. Based on that, I come up with the query:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSELECT p.name AS pdb_name,\nto_char(MIN(start_time),&#039;DD.MM.YY HH24:MI&#039;) AS start_time,\ndbms_xplan.format_time_s((SYSDATE - MIN(start_time)) * 24 * 60 * 60) AS elapsed_time,\ndbms_xplan.format_size(p.total_size) AS pdb_size,\ndbms_xplan.format_size(SUM(sofar) * p.block_size) AS so_far,\ndbms_xplan.format_size(total_size - (SUM(sofar) * p.block_size)) AS remaining,\nROUND(100 * (SUM(sofar) * p.block_size \/ p.total_size), 1)||&#039;%&#039; AS pct_complete,\nCOUNT(*) AS file_count\nFROM v$session_longops l\nCROSS JOIN v$pdbs@OLD_CDB p\nWHERE l.OPNAME = &#039;kpdbfCopyTaskCbk&#039;\n  AND p.con_id&gt;2\nGROUP BY l.QCSID, p.block_size, p.name, p.total_size, l.sql_exec_start;\n\n<\/pre><\/div>\n\n\n<p>This allowed to follow the copy progress of the PDB, with this output:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n   PDB_NAME       START_TIME    ELAPSED_TIME   PDB_SIZE  SO_FAR   REMAINING   PCT_COMPLETE    FILE_COUNT\n___________ ________________ ______________ _________ _________ ____________ _______________ ______\nSOURCE_PDB      14-01-26 10:57:26    03:24:23        16T         5598G     11T        33%           202\n<\/pre><\/div>\n\n\n<p>The query uses <a href=\"https:\/\/franckpachot.medium.com\/oracle-numbers-in-k-m-g-t-p-e-81e3175eae7e\">DBMS_XPLAN functions to automatically format the time and size<\/a>, as my ex-colleague Frank Pachot once blogged about.<\/p>\n\n\n\n<p>And using the SQLcl REPEAT command allows to have the query to repeat 1000 times automatically every x seconds:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; repeat 1000 60\n...\nRunning 291 of 1,000  @ 7:12:27.997 with a delay of 60s\n\n   PDB_NAME       START_TIME    ELAPSED_TIME   PDB_SIZE  SO_FAR   REMAINING   PCT_COMPLETE    FILE_COUNT\n___________ ________________ ______________ _________ _________ ____________ _______________ ______\nSOURCE_PDB      14-01-26 11:43:42    07:28:45        16T         7724G     9257G        45.5%           250\n<\/pre><\/div>\n\n\n<p>However, after (long) while, I noticed the started time changed. This when the &#8220;number of files&#8221; were around 250. I stumbled upon the <a href=\"https:\/\/support.oracle.com\/support\/?anchorId=&amp;kmContentId=783721&amp;page=sptemplate&amp;sptemplate=km-article\">KB832317 <\/a>in MOS which says what is in its title &#8220;V$Session_longops Have Limited Records Of 500&#8221;.<\/p>\n\n\n\n<p>In my case the limit was lower, but for the single parallel coordinator was 250 records:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSQL&gt; select count(*) from v$session_longops where opname=&#039;kpdbfCopyTaskCbk&#039;;\n\n   COUNT(*)\n___________\n        250\n\nSQL&gt; select count(*) from v$session_longops;\n\n   COUNT(*)\n___________\n        311\n<\/pre><\/div>\n\n\n<p>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?<\/p>\n\n\n\n<p>But to up to 250 datafiles, this solution should be good enough.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>For a PDB migration, I&#8217;ve configured a Refresh PDB clone. The PDB is 16TB and copying will take a few hours. To get the progress, it is not so straight forward: The only place to follow the PDB clone is using V$SQL_LONGOPS, as explained at KB135098 &#8211; How to Monitor PDB Clone \/ Move On [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36,65,6,39],"tags":[],"class_list":{"0":"post-1097","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-cloning","7":"category-multitenant","8":"category-oracle","9":"category-oracle-19c","10":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1097","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=1097"}],"version-history":[{"count":5,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1097\/revisions"}],"predecessor-version":[{"id":1114,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1097\/revisions\/1114"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=1097"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=1097"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=1097"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}