Soumya's Database Blog : November 2023

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

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

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;