Steps to Clone A Pluggable Database From Existing PDB

 

Steps to Clone A Pluggable Database From Existing PDB

 

So, I had a requirement for a customer where I had to clone a PDB from existing PDB on same CDB . I followed the following steps to do this.

 

Step 1.

Put the PDB in read only mode

SQL> select name,open_mode from v$pdbs;   NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY DEV READ WRITE     SQL> alter session set container=DEV;   Session altered.   SQL> select file_name from dba_data_files;   FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/nonprod/dev/system01.dbf /u01/app/oracle/oradata/nonprod/dev/sysaux01.dbf /u01/app/oracle/oradata/nonprod/dev/users01.dbf     SQL> show con_name   CON_NAME ------------------------------ DEV   SQL> shu immediate; Pluggable Database closed.   SQL> startup open read only Pluggable Database opened.

 

 

Step 2.

Connect to the CDB and initiate PDB clone:

 

Set Oracle SID as CDB SID [oracle@server1]$export ORACLE_SID=NONPROD [oracle@server1]$sqlplus / as sysdba SQL> show con_name   CON_NAME ------------------------------ CDB$ROOT   -- Clone pluggable     SQL> create pluggable database DEVCLONE from DEV FILE_NAME_CONVERT=('/u01/app/oracle/oradata/nonprod/dev','/u01/app/oracle/oradata/nonprod/devclone'); Pluggable database created.

 

 

Step 3:

Start the new cloned PDB In read write mode

SQL> select name,open_mode from v$pdbs;   NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY DEV READ ONLY DEVCLONE MOUNTED   SQL> alter session set CONTAINER=DEV;   Session altered.   SQL> shutdown immediate; Pluggable Database closed. SQL> startup Pluggable Database opened. SQL> show con_name DEV

 

 

 

Step4:-

start the new cloned PDB( DEVCLONE)

SQL> alter session set container=DEVCLONE;   Session altered. SQL> SHU IMMEDIATE   SQL> startup Pluggable Database opened.     SQL> conn sys/syspassword@NONPROD as sysdba Connected. SQL> select name,open_mode from v$pdbs;   NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY DEV READ WRITE DEVCLONE READ WRITE

 

 

Oracle datapump error ORA-39077: unable to subscribe agent KUPC$A_1_083325450472000

Oracle datapump error ORA-39077: unable to subscribe agent KUPC$A_1_083325450472000

 

During import we faced following error.

Error Details:-

D:\oracle\product\19.3.0\dbhome_1\bin>impdp HOSPITAL/HOSPITAL@ORCL dumpfile=EXP_HOSPITAL_20231113140110_A.DMP logfile=IMP_HOSPITAL_20231113140110_A.LOG directory=dumps   Import: Release 19.0.0.0.0 - Production on Mon Nov 13 16:59:16 2023 Version 19.13.0.0.0   Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.   Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-31626: job does not exist ORA-31638: cannot attach to job SYS_IMPORT_FULL_01 for user HOSPITAL ORA-06512: at "SYS.KUPV$FT", line 1142 ORA-06512: at "SYS.KUPV$FT", line 1744 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT_INT", line 498 ORA-39077: unable to subscribe agent KUPC$A_1_165917326000000 to queue "KUPC$C_1_20231113165917_0" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPC$QUE_INT", line 294 ORA-00972: identifier is too long ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9306 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1873 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 3802 ORA-06512: at "SYS.DBMS_RULE_ADM", line 296 ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 106 ORA-24000: invalid value "SYS"."KUPC$C_1_20231113165917_0$10471", RULE SET should be of the form [SCHEMA.]NAME ORA-00972: identifier is too long ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 109 ORA-06512: at "SYS.DBMS_RULEADM_INTERNAL", line 97 ORA-06512: at "SYS.DBMS_RULE_ADM", line 290 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 3757 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 3709 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1756 ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1516 ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9900 ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9269 ORA-06512: at "SYS.DBMS_AQADM", line 881 ORA-06512: at "SYS.KUPC$QUE_INT", line 267 ORA-06512: at "SYS.KUPC$QUE_INT", line 1360 ORA-06512: at line 1 ORA-06512: at "SYS.KUPC$QUEUE_INT", line 65 ORA-06512: at "SYS.KUPV$FT_INT", line 465 ORA-06512: at "SYS.KUPV$FT", line 1664

Cause:-

Once the sequence SYS.AQ$_KUPC$DATAPUMP_QUETAB_1_N exceeds 10000, the name of a rule set then exceeds 30 characters, which should not be a problem any more with 19c version.

The max identifier length with DB compatibility set to < 12.2  is  30
The max identifier length with DB compatibility set to >= 12.2  is  128

The issue in this case was caused due to compatible setting 11.2.0.4.0, in which case the name of a rule set then cannot exceed 30 characters.

 

Solution:-

 

Since our database was of version 19c which was upgraded from 11.2.0.4 but the compatible was still set to “11.2.0.4.0”. Because of this the issue was present.

After changing the compatible parameter to “19.0.0” it was resolved

 

SQL> alter system set compatible=’19.0.0’ scope=spfile;