Introduction
Since Oracle 12c, the CDB (Container Database) and PDB (Pluggable Database) model has been the recommended architecture, and the non-CDB architecture is deprecated and unsupported starting from Oracle 21c. In an earlier article, we covered migrating a non-CDB into a CDB using the DBMS_PDB plugging method, which requires taking an RMAN backup, restoring it as a standalone instance, and then plugging that in. This article covers a second approach, creating the PDB directly over a database link, which skips the RMAN restore step entirely and pulls the non-CDB's data straight across the network in one CREATE PLUGGABLE DATABASE command.
Patch Consideration
If the source and destination aren't at the same patch level, you'll see PDB violations in the PDB_PLUG_IN_VIOLATIONS view, exactly as with the plugging method. The resolution depends on direction:
- If the destination is at a higher patch level than the source, run
datapatch -verboseon the destination as normal. - If the destination is at a lower patch level than the source, run a
datapatch -rollbackoperation instead.
Methods of Migration
- DBMS_PDB Package (Plugging method), covered in the earlier article
- Creating the PDB via DB Link (this article)
- Export/Import Method
Creating the PDB via DB Link
Prerequisites
- Network connectivity must be established between the non-CDB and CDB database servers, since this method pulls data live over a database link rather than via files.
Environment Used in This Guide
| Server | Non-CDB | CDB |
|---|---|---|
| Hostname | MISN | REPORT |
| IP | 192.168.80.51 | 192.168.80.52 |
| OS | OEL 9 | OEL 9 |
| SID | MISN | REPORT |
| Patch Level | 19.24 | 19.27 |
| PDB | NA | MISD, MISN |
Step 1: Grant PDB Creation Privilege on the Source
The database link needs to connect as a user with privileges to create a pluggable database from this source, we'll use SYSTEM. First create a test table so we can validate data survived the migration, then check the source's health and grant the required privilege.
SQL> show user
USER is "TEST1"
SQL>
SQL> def
DEFINE _DATE = "27-APR-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "misn" (CHAR)
DEFINE _USER = "TEST1" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1924000000" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0" (CHAR)
DEFINE _O_RELEASE = "1924000000" (CHAR)
SQL>
SQL>
SQL> CREATE TABLE COMPANY (EMP_ID INT,NAME VARCHAR(255), COMPANY VARCHAR(255));
Table created.
SQL> INSERT INTO COMPANY VALUES (101,'Yash','WIPRO');
1 row created.
SQL> INSERT INTO COMPANY VALUES (102,'Vijay','AIRTEL');
1 row created.
SQL> INSERT INTO COMPANY VALUES (103,'Riya','TCS');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from COMPANY;
COUNT(*)
----------
3
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@misn ]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 14:04:47 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL> set lines 200 pages 1000
col open_mode for a15
col HOST_NAME for a15
select NAME,DATABASE_ROLE,OPEN_MODE,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$database,gv$instance;SQL> SQL> SQL>
NAME DATABASE_ROLE OPEN_MODE INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS
--------- ---------------- --------------- --------------- ---------------- --------------- ----------------- --------- ------------
MISN PRIMARY READ WRITE 1 MISN misn 19.0.0.0.0 27-APR-25 OPEN
SQL> select BANNER_FULL from v$version;
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
SQL> col ACTION_TIME for a20
SQL> col ACTION for a10
SQL> col STATUS for a10
SQL> col DESCRIPTION for a60
SQL> select patch_uid,patch_id,SOURCE_VERSION, TARGET_VERSION, action, status,action_time,description from dba_registry_sqlpatch;
PATCH_UID PATCH_ID SOURCE_VERSION TARGET_VERSION ACTION STATUS ACTION_TIME DESCRIPTION
---------- ---------- --------------- --------------- ---------- ---------- -------------------- ------------------------------------------------------------
25751445 36582781 19.1.0.0.0 19.24.0.0.0 APPLY SUCCESS 26-APR-25 09.55.24.9 Database Release Update : 19.24.0.0.240716 (36582781)
95644 PM
SQL> col comp_id for a10
SQL> col version for a11
SQL> col status for a10
SQL> col comp_name for a37
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------------- ----------- ----------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/MISN/system01.dbf
/u01/app/oracle/oradata/MISN/sysaux01.dbf
/u01/app/oracle/oradata/MISN/undotbs01.dbf
/u01/app/oracle/oradata/MISN/users01.dbf
SQL> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/MISN/temp01.dbf
Provide the grant:
SQL> grant create pluggable database to system;
Grant succeeded.
Step 2: Add a TNS Entry on the CDB and Test Connectivity
Unlike the plugging method, here the CDB needs to be able to reach the non-CDB live over the network, so add a TNS entry for it and confirm connectivity with tnsping before doing anything else.
[oracle@report ~]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
REPORT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = report.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = REPORT)
)
)
LISTENER_REPORT =
(ADDRESS = (PROTOCOL = TCP)(HOST = report.localdomain)(PORT = 1521))
MISN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = misn.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = misn)
)
)
[oracle@report ~]$ tnsping misn
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-APR-2025 18:52:55
Copyright (c) 1997, 2025, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = misn.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = misn)))
OK (0 msec)
[oracle@report ~]$
Step 3: Create and Test the Database Link
[oracle@report ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 28 18:53:23 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
SQL>
SQL> CREATE DATABASE LINK "MIGLINK" CONNECT TO "SYSTEM" IDENTIFIED BY sys using 'MISN';
Database link created.
SQL> select sysdate from dual@MIGLINK;
SYSDATE
---------
28-APR-25
SQL>
Step 4: Create the PDB Using the DB Link
As with the plugging method, there are three options for handling datafiles, COPY, NOCOPY, and MOVE, with the same tradeoffs (COPY keeps the source usable, the other two don't). We'll use COPY again here. The key difference in this step is the FROM non$cdb@MIGLINK clause, which tells Oracle to pull the non-CDB's structure and data live through the database link, rather than from an XML description file and restored datafiles.
[oracle@report ~]$ mkdir -p /u01/app/oracle/oradata/PDB/PRODN
[oracle@report ~]$
[oracle@report ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 28 18:58:17 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
SQL>
SQL> CREATE PLUGGABLE DATABASE PROD from non$cdb@MIGLINK file_name_convert=('/u01/app/oracle/oradata/MISN','/u01/app/oracle/oradata/PDB/PROD');
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
5 PROD MOUNTED
SQL> set lines 400 pages 4000
SQL> col PDB_NAME for a30
SQL> select name, open_mode, cdb, pdb_name, status from v$database,dba_pdbs;
NAME OPEN_MODE CDB PDB_NAME STATUS
--------- -------------------- --- ------------------------------ ----------
REPORT READ WRITE YES MISD NORMAL
REPORT READ WRITE YES PDB$SEED NORMAL
REPORT READ WRITE YES MISN NORMAL
REPORT READ WRITE YES PROD NEW
SQL> alter session set container=PROD;
Session altered.
SQL> select name from v$datafile;
NAME
---------------------------------------------------
/u01/app/oracle/oradata/PDB/PROD/system01.dbf
/u01/app/oracle/oradata/PDB/PROD/sysaux01.dbf
/u01/app/oracle/oradata/PDB/PROD/undotbs01.dbf
/u01/app/oracle/oradata/PDB/PROD/users01.dbf
SQL> select name from v$tempfile;
NAME
---------------------------------------------------
/u01/app/oracle/oradata/PDB/PROD/temp01.dbf
Step 5: Run noncdb_to_pdb.sql on the New PDB
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
5 PROD MOUNTED
SQL>
SQL> alter session set container=PROD;
Session altered.
SQL>
SQL> @?/rdbms/admin/noncdb_to_pdb.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> SET VERIFY OFF
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01403: no data found"
DOC> error if we're not in a PDB.
DOC> This script is intended to be run right after plugin of a PDB,
DOC> while inside the PDB.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
...(remaining internal script output omitted for brevity)...
SQL> -- leave the PDB in the same state it was when we started
SQL> BEGIN
2 execute immediate '&open_sql &restricted_state';
3 EXCEPTION
4 WHEN OTHERS THEN
5 BEGIN
6 IF (sqlcode <> -900) THEN
7 RAISE;
8 END IF;
9 END;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> WHENEVER SQLERROR CONTINUE;
SQL>
SQL>
Step 6: Open the New PDB and Investigate Restricted Mode
This is where the DB Link method diverges from the plugging method in a way worth calling out specifically. Opening the PDB here comes back with a warning, and the PDB ends up open but in restricted mode.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
5 PROD MOUNTED
SQL> alter pluggable database PROD open;
Warning: PDB altered with errors.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
5 PROD READ WRITE YES
The non-CDB has migrated into PDB PROD, but the RESTRICTED flag tells us something still needs resolving before it's fully usable.
Step 7: Check PDB_PLUG_IN_VIOLATIONS and Apply the Patch
SQL> set lines 333 pages 333
SQL> col NAME for a20
SQL> col CAUSE for a30
SQL> col MESSAGE for a70
SQL> select name,cause,type,message,status,action from PDB_PLUG_IN_VIOLATIONS where name='PROD';
NAME CAUSE TYPE MESSAGE STATUS
-------------------- ------------------------------ --------- ---------------------------------------------------------------------- ---------
PROD Non-CDB to PDB ERROR PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. RESOLVED
PROD SQL Patch ERROR '19.27.0.0.0 Release_Update 2504061311' is installed in the CDB but PENDING
'19.24.0.0.0 Release_Update 2406272351' is installed in the PDB
The noncdb_to_pdb.sql violation is already RESOLVED, but the patch mismatch is still PENDING. Run datapatch to resolve it.
[oracle@report ~]$ cd $ORACLE_HOME/OPatch
[oracle@report OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.27.0.0.0 Production on Mon Apr 28 20:33:40 2025
Copyright (c) 2012, 2025, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_16512_2025_04_28_20_33_40/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...
done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.27.0.0.0 Release_Update 250406131139: Installed
PDB CDB$ROOT:
Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 27-APR-25 11.51.35.558320 AM
PDB MISD:
Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 27-APR-25 11.58.48.003402 AM
PDB MISN:
Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 27-APR-25 03.12.59.773550 PM
PDB PDB$SEED:
Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 27-APR-25 11.58.58.243642 AM
PDB PROD:
Applied 19.24.0.0.0 Release_Update 240627235157 successfully on 26-APR-25 09.55.24.995644 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED MISD MISN
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied
For the following PDBs: PROD
No interim patches need to be rolled back
Patch 37642901 (Database Release Update : 19.27.0.0.250415 (37642901)):
Apply from 19.24.0.0.0 Release_Update 240627235157 to 19.27.0.0.0 Release_Update 250406131139
No interim patches need to be applied
WARNING: Following components are NOT in a valid state.
This could cause patching failure. If it does, consider
running utlrp.sql to bring components to VALID state.
Then, re-run datapatch.
PROD : CATPROC [INVALID]
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 37642901 apply (pdb PROD): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/37642901/27123174/37642901_apply_REPORT_PROD_2025Apr28_20_36_03.log (no errors)
SQL Patching tool complete on Mon Apr 28 20:43:14 2025
Note the warning about CATPROC being invalid. This is exactly the kind of state you can hit during a live DB-Link-based plug-in, since components weren't necessarily recompiled cleanly during the transfer. The fix is to run utlrp.sql to recompile invalid objects, then re-run datapatch.
Step 8: Recompile Invalid Components With utlrp.sql
[oracle@report OPatch]$
[oracle@report OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 28 20:45:55 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
5 PROD READ WRITE YES
SQL>
SQL> alter session set container=PROD;
Session altered.
SQL> col comp_id for a10
SQL> col version for a11
SQL> col status for a10
SQL> col comp_name for a37
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------------- ----------- ----------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 INVALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
SQL>
SQL> @?/rdbms/admin/utlrp
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2025-04-28 20:49:15
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
...(remaining internal script output omitted for brevity)...
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2025-04-28 20:49:42
OBJECTS WITH ERRORS
-------------------
0
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------------- ----------- ----------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
With CATPROC now VALID, re-run datapatch, this time it should complete cleanly against PROD along with the other PDBs.
[oracle@report OPatch]$
[oracle@report OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.27.0.0.0 Production on Mon Apr 28 20:50:23 2025
Copyright (c) 2012, 2025, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_17077_2025_04_28_20_50_23/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
19.27.0.0.0 Release_Update 250406131139: Installed
PDB CDB$ROOT:
Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 27-APR-25 11.51.35.558320 AM
PDB MISD:
Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 27-APR-25 11.58.48.003402 AM
PDB MISN:
Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 27-APR-25 03.12.59.773550 PM
PDB PDB$SEED:
Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 27-APR-25 11.58.58.243642 AM
PDB PROD:
Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 28-APR-25 08.41.56.203836 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED MISD MISN PROD
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied
SQL Patching tool complete on Mon Apr 28 20:53:40 2025
[oracle@report OPatch]$
Step 9: Restart the PDB and Confirm the Violations Are Resolved
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
5 PROD READ WRITE YES
SQL>
SQL> alter pluggable database PROD close immediate;
Pluggable database altered.
SQL> alter pluggable database PROD open;
Pluggable database altered.
SQL> SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
5 PROD READ WRITE NO
SQL>
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='PROD';
NAME CAUSE TYPE MESSAGE STATUS
-------------------- ------------------------------ --------- ---------------------------------------------------------------------- ---------
PROD Non-CDB to PDB ERROR PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. RESOLVED
PROD SQL Patch ERROR '19.27.0.0.0 Release_Update 2504061311' is installed in the CDB but '1 RESOLVED
9.24.0.0.0 Release_Update 2406272351' is installed in the PDB
The PDB now shows READ WRITE NO, restricted mode is off, and both violations are marked RESOLVED.
Step 10: Final Verification
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 MISD READ WRITE NO
4 MISN READ WRITE NO
5 PROD READ WRITE NO
SQL> select BANNER_FULL from v$version;
BANNER_FULL
---------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
SQL> alter session set container=PROD;
Session altered.
SQL> col comp_id for a10
SQL> col version for a11
SQL> col status for a10
SQL> col comp_name for a37
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------------- ----------- ----------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
SQL> exit
[oracle@report ~]$ sqlplus test1@prod
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 28 21:13:31 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Enter password:
Last Successful login time: Sun Apr 28 2025 19:45:15 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
SQL> show user
USER is "TEST1"
SQL>
SQL> select count(*) from company;
COUNT(*)
----------
3
Zero invalid objects and the original 3 rows confirm the non-CDB database MISN has been successfully migrated into the CDB REPORT via database link, no data loss, no lingering compatibility issues. Compared to the plugging method, this approach skipped the RMAN backup and restore entirely, at the cost of one extra troubleshooting step (the CATPROC/restricted mode issue) that didn't come up with the file-based approach.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment