Showing posts with label Migration. Show all posts
Showing posts with label Migration. Show all posts

Tuesday, May 13, 2025

Migrating Non-CDB to CDB (Multitenant) using DB Link

     No comments
  • Introduction: Since Oracle 12c, the CDB (Container Database) and PDB (Pluggable Database) model has been the recommended architecture. Oracle has announced that non-CDB architecture is deprecated (and not supported starting from Oracle 21c). So migrating is necessary if you are upgrading to a newer Oracle version.

  • Patch consideration: If the instances are not at the same patch level, you will get PDB violations visible in the PDB_PLUG_IN_VIOLATIONS view.
    1. If the destination is at a higher patch level than the source, then run the datapatch -verbose utility on the destination instance in the normal way. It may throw any error and also suggest what action needs to be taken.
    2. If the destination is at a lower patch level than the source, you will need to run a datapatch -rollback operation.
  • Methods of migration:
    1. DBMS_PDB Package (Plugging method)
      2.
    2. Creating the PDB via DB Link
      3.
    3. Export/Import Method
    In previous article Migrating Non-CDB to CDB ((Plugging method) we have used the 1st method. Now here we will use the 2nd Method "Creating the PDB via DB Link"

    Creating the PDB via DB Link

  • Prerequisites:
    • Connectivity should be established between Non-CDB & CDB database servers.
  • Environment:
    • 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

  • Now proceed for migration activity step by step:

1. On source Non-CDB, grant privilege of creating PDB to user which will be used in DB link. Here we are using System user:
First create a table with test user so that data can be validated post migration:

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
Now take Precheck & provide grant:

[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         



==> Now provide grant.

SQL> grant create pluggable database to system;

Grant succeeded.
2. Now in target, add Non-CDB TNS entry in CDB tnsanames.ora file and test connectivity: 

[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 ~]$
3. Now create the public DB link in CDB and test it: 

[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>
4. Now create the PDB using DB Link. We have below options for maintaing the database files:
  1. COPY - With this option, all data files of Non-CDB will remain untouched. For PDB files will be copied to the new location, provided with the parameter FILE_NAME_CONVERT.
  2. NOCOPY - Existing files will be used and after completion of the operation, Non-CDB will not remain usable. As new PDB is using the same data files.
  3. MOVE - using parameter FILE_NAME_CONVERT, existing datafiles will be moved to the new location, hence after the operation completion, Non-CDB will not be usable.
Here we will be using the COPY option.

[oracle@report ~]$ mkdir -p /u01/app/oracle/oradata/PDB/PRODN   --> Create Directory for datafiles & tempfiles.
[oracle@report ~]$
[oracle@report ~]$ !sq
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
8. Now we need to run the noncdb_to_pdb.sql script on new PDB PROD. 

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>
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>#




....................
....................


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>
8. Now open the new PDB PROD and do the post check. 


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
As per the above output, Non-CDB has been migrated into PDB PROD, but it is in Restricted mode. So let's resolve this.
8. Now query the PDB_PLUG_IN_VIOLATIONS view to find the cause. 


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
Now as per the above output, we can see that in ACTION column which suggests to run the datapatch. So let's proceed for the same.
 
[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


==> As per above error need to check database components.

[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.


==> As per above output CATPROC is in INVALID state so need to run utlrp.sql .

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.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END              2025-04-28 20:49:42

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC>       logged into this table: they go into DBA_ERRORS instead.
DOC>#

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


==> As per above output CATPROC is now in the VALID state so run the datapatch again.

[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]$
8. Now take restart of the PDB PROD and check the status. 

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
8. Now do the post check. 

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
Now the Non-CDB database MISN has been successfully plugged in the CDB database REPORT.

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

📧 Email: oraeasyy@gmail.com
🌐 Website: www.oraeasy.com

Tuesday, May 6, 2025

Migrating Non-CDB to CDB (Multitenant)

     No comments
  • Introduction: Since Oracle 12c, the CDB (Container Database) and PDB (Pluggable Database) model has been the recommended architecture. Oracle has announced that non-CDB architecture is deprecated (and not supported starting from Oracle 21c). So migrating is necessary if you are upgrading to a newer Oracle version.

  • Patch consideration: If the instances are not at the same patch level, you will get PDB violations visible in the PDB_PLUG_IN_VIOLATIONS view.
    1. If the destination is at a higher patch level than the source, then run the datapatch -verbose utility on the destination instance in the normal way. It may throw any error and also suggest what action needs to be taken.
    2. If the destination is at a lower patch level than the source, you will need to run a datapatch -rollback operation.
  • Methods of migration:
    1. DBMS_PDB Package (Plugging method)
      2.
    2. Creating the PDB via DB Link
      3.
    3. Export/Import Method
    Here we will use the 1st & 2nd method as the 3rd method is the traditional method for migration using datapump.

    DBMS_PDB Package (Plugging method)

  • Prerequisites:
    • On the target, there one must be a multitenant (CDB) database, if not then create the same.
  • Environment:
    • 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

    As per above details we have Non-CDB & CDB Databases on different servers. So we will create Non-CDB instance on target and then do the restoration with the backup of Source Non-CDB dataabase. We will use RMAN utility for the same.

  • Now proceed for migration activity step by step:

1. Take precheck & RMAN backup at source:
First create a table with test user so that data can be validated post migration:

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
Now take Precheck & required backup:

[oracle@misn rmanmisn]$ 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
         


==> Stop Listener 


[oracle@misn ~]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 27-APR-2025 13:35:56

Copyright (c) 1991, 2024, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=misn.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                27-APR-2025 13:08:01
Uptime                    0 days 0 hr. 27 min. 56 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/misn/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=misn.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "MISN" has 1 instance(s).
  Instance "MISN", status READY, has 1 handler(s) for this service...
Service "MISNXDB" has 1 instance(s).
  Instance "MISN", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@misn ~]$
[oracle@misn ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 27-APR-2025 13:36:01

Copyright (c) 1991, 2024, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=misn.localdomain)(PORT=1521)))
The command completed successfully
[oracle@misn ~]$

[oracle@misn ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 13:50:44 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> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> create pfile='/u01/app/oracle/rmanmisn/initMISN.ora' from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0



==> Take Backup 

[oracle@misn rmanmisn]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Apr 27 13:50:16 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MISN (DBID=1539538097)


RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name MISN

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1110     SYSTEM               YES     /u01/app/oracle/oradata/MISN/system01.dbf
3    860      SYSAUX               NO      /u01/app/oracle/oradata/MISN/sysaux01.dbf
4    750      UNDOTBS1             YES     /u01/app/oracle/oradata/MISN/undotbs01.dbf
7    5        USERS                NO      /u01/app/oracle/oradata/MISN/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    184      TEMP                 32767       /u01/app/oracle/oradata/MISN/temp01.dbf

RMAN>

RMAN> run
{
allocate channel ch1 device type disk;
backup as compressed backupset database format '/u01/app/oracle/rmanmisn/Fullback_%T_%U';
backup as compressed backupset archivelog all format '/u01/app/oracle/rmanmisn/Archive_%T_%U';
backup current controlfile format '/u01/app/oracle/rmanmisn/Controlback_%T_%U';
release channel ch1;
}

allocated channel: ch1
channel ch1: SID=269 device type=DISK

Starting backup at 27-APR-25
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/MISN/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/MISN/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/MISN/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/MISN/users01.dbf
channel ch1: starting piece 1 at 27-APR-25
channel ch1: finished piece 1 at 27-APR-25
piece handle=/u01/app/oracle/rmanmisn/Fullback_20250427_053nv2qj_5_1_1 tag=TAG20250427T135114 comment=NONE
channel ch1: backup set complete, elapsed time: 00:03:15
Finished backup at 27-APR-25

Starting backup at 27-APR-25
current log archived
channel ch1: starting compressed archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=1 STAMP=1199484381
input archived log thread=1 sequence=11 RECID=2 STAMP=1199540312
input archived log thread=1 sequence=12 RECID=3 STAMP=1199540572
input archived log thread=1 sequence=13 RECID=4 STAMP=1199541053
input archived log thread=1 sequence=14 RECID=5 STAMP=1199541056
input archived log thread=1 sequence=15 RECID=6 STAMP=1199541271
channel ch1: starting piece 1 at 27-APR-25
channel ch1: finished piece 1 at 27-APR-25
piece handle=/u01/app/oracle/rmanmisn/Archive_20250427_063nv30o_6_1_1 tag=TAG20250427T135432 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:45
Finished backup at 27-APR-25

Starting backup at 27-APR-25
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 27-APR-25
channel ch1: finished piece 1 at 27-APR-25
piece handle=/u01/app/oracle/rmanmisn/Controlback_20250427_073nv326_7_1_1 tag=TAG20250427T135518 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 27-APR-25

Starting Control File and SPFILE Autobackup at 27-APR-25
piece handle=/u01/app/oracle/fast_recovery_area/MISN/autobackup/2025_04_27/o1_mf_s_1199541322_n0vthm91_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 27-APR-25

released channel: ch1

RMAN>

RMAN> exit


Recovery Manager complete.
[oracle@misn rmanmisn]$ pwd
/u01/app/oracle/rmanmisn
[oracle@misn rmanmisn]$ 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


==> Transfer Backup 

[oracle@misn rmanmisn]$ ls -lrth
total 747M
-rw-r-----. 1 oracle oinstall 617M Apr 27 13:54 Fullback_20250427_053nv2qj_5_1_1
-rw-r-----. 1 oracle oinstall 120M Apr 27 13:55 Archive_20250427_063nv30o_6_1_1
-rw-r-----. 1 oracle oinstall  11M Apr 27 13:55 Controlback_20250427_073nv326_7_1_1
-rw-r--r--. 1 oracle oinstall 1.2K Apr 27 14:05 initMISN.ora
[oracle@misn rmanmisn]$
[oracle@misn rmanmisn]$ scp * oracle@report:/u01/app/oracle/rman
The authenticity of host 'report (report)' can't be established.
ED25519 key fingerprint is SHA256:u1O6svnS8kY1i6Mv88TDIlYsvqDyISi2Uz3ZBc8kKfY.
This host key is known by the following other names/addresses:
    ~/.ssh/known_hosts:1: target
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added 'report' (ED25519) to the list of known hosts.
oracle@report's password:
Archive_20250427_063nv30o_6_1_1             100%  119MB  43.2MB/s   00:02
Controlback_20250427_073nv326_7_1_1         100%   10MB  41.3MB/s   00:00
Fullback_20250427_053nv2qj_5_1_1            100%  616MB  22.1MB/s   00:27
initMISN.ora                                100% 1148   352.0KB/s   00:00
2. Now we will restore the Non-CDB backup at target. Here will keep the instance name as MISNT: 

[oracle@report rman]$ pwd
/u01/app/oracle/rman
[oracle@report rman]$ cat initMISNT.ora
MISNT.__data_transfer_cache_size=0
MISNT.__db_cache_size=1409286144
MISNT.__inmemory_ext_roarea=0
MISNT.__inmemory_ext_rwarea=0
MISNT.__java_pool_size=0
MISNT.__large_pool_size=16777216
MISNT.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
MISNT.__pga_aggregate_target=671088640
MISNT.__sga_target=1996488704
MISNT.__shared_io_pool_size=100663296
MISNT.__shared_pool_size=452984832
MISNT.__streams_pool_size=0
MISNT.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/MISNT/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/MISNT/control01.ctl'
*.db_block_size=8192
*.db_name='MISN'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MISNTXDB)'
*.local_listener='LISTENER_MISNT'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=631m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1892m
*.undo_tablespace='UNDOTBS1'
[oracle@report rman]$
[oracle@report rman]$ mkdir -p /u01/app/oracle/admin/MISNT/adump
[oracle@report rman]$
[oracle@report rman]$ mkdir -p /u01/app/oracle/oradata/MISNT/
[oracle@report rman]$ . oraenv
ORACLE_SID = [REPORT] ? MISNT
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@report rman]$
[oracle@report rman]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 14:33:29 2025
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>  startup nomount pfile='/u01/app/oracle/rman/initMISNT.ora';
ORACLE instance started.

Total System Global Area 1996488376 bytes
Fixed Size                  8941240 bytes
Variable Size             469762048 bytes
Database Buffers         1509949440 bytes
Redo Buffers                7835648 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[oracle@report rman]$ ls
Archive_20250427_063nv30o_6_1_1  Controlback_20250427_073nv326_7_1_1  Fullback_20250427_053nv2qj_5_1_1  initMISNT.ora

[oracle@report rman]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Apr 27 14:38:04 2025
Version 19.27.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MISN (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/rman/Controlback_20250427_073nv326_7_1_1';
restore controlfile from '/u01/app/oracle/rman/Controlback_20250427_073nv326_7_1_1';
Starting restore at 27-APR-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/MISNT/control01.ctl
Finished restore at 27-APR-25


RMAN> alter database mount;
alter database mount;
released channel: ORA_DISK_1
Statement processed

RMAN> report schema;
report schema;
Starting implicit crosscheck backup at 27-APR-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 27-APR-25

Starting implicit crosscheck copy at 27-APR-25
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-APR-25

searching for all files in the recovery area
cataloging files...
no files cataloged

RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name MISN

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     /u01/app/oracle/oradata/MISN/system01.dbf
3    0        SYSAUX               ***     /u01/app/oracle/oradata/MISN/sysaux01.dbf
4    0        UNDOTBS1             ***     /u01/app/oracle/oradata/MISN/undotbs01.dbf
7    0        USERS                ***     /u01/app/oracle/oradata/MISN/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/MISN/temp01.dbf


RMAN>

RMAN> catalog start with '/u01/app/oracle/rman';
catalog start with '/u01/app/oracle/rman';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/rman

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/rman/Archive_20250427_063nv30o_6_1_1
File Name: /u01/app/oracle/rman/Controlback_20250427_073nv326_7_1_1
File Name: /u01/app/oracle/rman/Fullback_20250427_053nv2qj_5_1_1
File Name: /u01/app/oracle/rman/initMISNT.ora

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/rman/Archive_20250427_063nv30o_6_1_1
File Name: /u01/app/oracle/rman/Controlback_20250427_073nv326_7_1_1
File Name: /u01/app/oracle/rman/Fullback_20250427_053nv2qj_5_1_1

List of Files Which Were Not Cataloged
=======================================
File Name: /u01/app/oracle/rman/initMISNT.ora
  RMAN-07517: Reason: The file header is corrupted


RMAN> RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
set newname for database to '/u01/app/oracle/oradata/MISNT/%b';
restore database;
switch datafile all;
switch tempfile all;
recover database;
release channel c1;
release channel c2;
}RUN
2> {
3> ALLOCATE CHANNEL c1 DEVICE TYPE disk;
4> ALLOCATE CHANNEL c2 DEVICE TYPE disk;
5> set newname for database to '/u01/app/oracle/oradata/MISNT/%b';
6> restore database;
7> switch datafile all;
8> switch tempfile all;
9> recover database;
10> release channel c1;
11> release channel c2;
12>
}
allocated channel: c1
channel c1: SID=17 device type=DISK

allocated channel: c2
channel c2: SID=259 device type=DISK

executing command: SET NEWNAME

Starting restore at 27-APR-25

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/app/oracle/oradata/MISNT/system01.dbf
channel c1: restoring datafile 00003 to /u01/app/oracle/oradata/MISNT/sysaux01.dbf
channel c1: restoring datafile 00004 to /u01/app/oracle/oradata/MISNT/undotbs01.dbf
channel c1: restoring datafile 00007 to /u01/app/oracle/oradata/MISNT/users01.dbf
channel c1: reading from backup piece /u01/app/oracle/rman/Fullback_20250427_053nv2qj_5_1_1
channel c1: piece handle=/u01/app/oracle/rman/Fullback_20250427_053nv2qj_5_1_1 tag=TAG20250427T135114
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:03:28
Finished restore at 27-APR-25

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1199544353 file name=/u01/app/oracle/oradata/MISNT/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1199544354 file name=/u01/app/oracle/oradata/MISNT/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1199544354 file name=/u01/app/oracle/oradata/MISNT/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1199544354 file name=/u01/app/oracle/oradata/MISNT/users01.dbf

renamed tempfile 1 to /u01/app/oracle/oradata/MISNT/temp01.dbf in control file

Starting recover at 27-APR-25

starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=15
channel c1: reading from backup piece /u01/app/oracle/rman/Archive_20250427_063nv30o_6_1_1
channel c1: piece handle=/u01/app/oracle/rman/Archive_20250427_063nv30o_6_1_1 tag=TAG20250427T135432
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/MISN/archivelog/2025_04_27/o1_mf_1_15_n0vxgfm5_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/MISN/archivelog/2025_04_27/o1_mf_1_15_n0vxgfm5_.arc RECID=7 STAMP=1199544357
unable to find archived log
archived log thread=1 sequence=16
RMAN Command Id : 2025-04-27T14:41:25
RMAN Command Id : 2025-04-27T14:41:25
RMAN Command Id : 2025-04-27T14:41:25
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/27/2025 14:46:01
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 16 and starting SCN of 2211545
RMAN Client Diagnostic Trace file : /u01/app/oracle/diag/clients/user_oracle/RMAN_3959702396_110/trace/ora_rman_6436_0.trc
RMAN Server Diagnostic Trace file : /u01/app/oracle/diag/rdbms/misn/MISNT/trace/MISNT_ora_6442.trc

RMAN>exit;


[oracle@report ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 15:00:59 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> 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          MOUNTED                       1 MISNT            report          19.0.0.0.0        27-APR-25 MOUNTED

SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open RESETLOGS;

Database altered.

SQL> create spfile from pfile='/u01/app/oracle/rman/initMISNT.ora';

File created.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1996488376 bytes
Fixed Size                  8941240 bytes
Variable Size             469762048 bytes
Database Buffers         1509949440 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.
SQL>
3. Now open the Non-CDB Database in read-only and describe the non-DBC using the DBMS_PDB.DESCRIBE procedure. It will generate an XML file. At last stop the database: 

[oracle@report ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 15:00:59 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> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1996488376 bytes
Fixed Size                  8941240 bytes
Variable Size             469762048 bytes
Database Buffers         1509949440 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.
SQL>
SQL> set lines 200 pages 1000
SQL> col open_mode for a15
SQL> col HOST_NAME for a15
SQL> select NAME,DATABASE_ROLE,OPEN_MODE,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$database,gv$instance;

NAME      DATABASE_ROLE    OPEN_MODE       INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME       VERSION           STARTUP_T STATUS
--------- ---------------- --------------- --------------- ---------------- --------------- ----------------- --------- ------------
MISN      PRIMARY          READ ONLY                     1 MISNT            report          19.0.0.0.0        27-APR-25 OPEN

SQL> select name from v$datafile;

NAME
----------------------------------------------------
/u01/app/oracle/oradata/MISNT/system01.dbf
/u01/app/oracle/oradata/MISNT/sysaux01.dbf
/u01/app/oracle/oradata/MISNT/undotbs01.dbf
/u01/app/oracle/oradata/MISNT/users01.dbf

SQL> select name from v$tempfile;

NAME
-------------------------------------------------
/u01/app/oracle/oradata/MISNT/temp01.dbf

SQL> exec dbms_pdb.describe('/home/oracle/MISN_noncdb.xml');

PL/SQL procedure successfully completed.

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
4. Now login into the CDB database and check the compatibility: 

[oracle@report ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 15:00:59 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> set lines 200 pages 1000
SQL> col open_mode for a15
SQL> col HOST_NAME for a15
SQL> select NAME,DATABASE_ROLE,OPEN_MODE,INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$database,gv$instance;

NAME      DATABASE_ROLE    OPEN_MODE       INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME       VERSION           STARTUP_T STATUS
--------- ---------------- --------------- --------------- ---------------- --------------- ----------------- --------- ------------
REPORT    PRIMARY          READ WRITE                    1 REPORT           report          19.0.0.0.0        27-APR-25 OPEN

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MISD                           READ WRITE NO
SQL>

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
     compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/home/oracle/MISN_noncdb.xml')
     WHEN TRUE THEN 'YES'
     ELSE 'NO'
     END;
     BEGIN
     DBMS_OUTPUT.PUT_LINE(compatible);
     END;
     /  
NO

PL/SQL procedure successfully completed.


==> The ideal output is YES, but in our case its NO. So we will query the PDB_PLUG_IN_VIOLATIONS view to find the cause.

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='MISN';

NAME  CAUSE           TYPE      MESSAGE                                                               	STATUS    ACTION
----- --------------- --------- ---------------------------------------------------------------------- 	--------- ----------------------------------------
MISN  Non-CDB to PDB  WARNING     PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.        PENDING  Run noncdb_to_pdb.sql.
MISN  SQL Patch       ERROR     '19.27.0.0.0 Release_Update 2504061311' is installed in the CDB but '1 	 PENDING  Call datapatch to install in the PDB or
                                9.24.0.0.0 Release_Update 2406272351' is installed in the PDB                    		the CDB
Now as per the above output, we can see that in ACTION column which suggests to run the datapatch. So let's proceed for the same.
5. Now start the Non-CDB database and invoke datapatch:

[oracle@report ~]$ . oraenv
ORACLE_SID = [REPORT] ? MISNT
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/19.0.0/dbhome_1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@report ~]$
[oracle@report ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 15:03:56 2025
Version 19.27.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to an idle instance.

SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1996488376 bytes
Fixed Size                  8941240 bytes
Variable Size             469762048 bytes
Database Buffers         1509949440 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[oracle@report ~]$
[oracle@report ~]$ cd $ORACLE_HOME/OPatch
[oracle@report OPatch]$
[oracle@report OPatch]$ ./datapatch -db MISNT -verbose
SQL Patching tool version 19.27.0.0.0 Production on Sun Apr 27 15:05:02 2025
Copyright (c) 2012, 2025, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_8569_2025_04_27_15_05_02/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
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
  SQL registry:
    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:
  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

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...done
Patch 37642901 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/37642901/27123174/37642901_apply_MISN_2025Apr27_15_06_38.log (no errors)
SQL Patching tool complete on Sun Apr 27 15:14:21 2025
[oracle@report OPatch]$
[oracle@report OPatch]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 15:21:11 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>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> def
DEFINE _DATE           = "27-APR-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "MISNT" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1927000000" (CHAR)
SQL>
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1996488376 bytes
Fixed Size                  8941240 bytes
Variable Size             536870912 bytes
Database Buffers         1442840576 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.
SQL>
SQL> exec dbms_pdb.describe('/home/oracle/MISN_noncdb.xml');

PL/SQL procedure successfully completed.

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[oracle@report OPatch]$
6. Now again login into CDB and check the compatibility: 

[oracle@report ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 15:22:50 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> def
DEFINE _DATE           = "27-APR-25" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "REPORT" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1927000000" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0" (CHAR)
DEFINE _O_RELEASE      = "1927000000" (CHAR)
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
     compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/home/oracle/MISN_noncdb.xml')
     WHEN TRUE THEN 'YES'
     ELSE 'NO'
     END;
     BEGIN
     DBMS_OUTPUT.PUT_LINE(compatible);
     END;
     /  
YES

PL/SQL procedure successfully completed.

SQL>
Now we get the desired value, so proceeding further.
7. Now plug the Non-CDB database MISN into CDB REPORT database. We have below options for maintaing the database files:
  1. COPY - With this option, all data files of Non-CDB will remain untouched. For PDB files will be copied to the new location, provided with the parameter FILE_NAME_CONVERT.
  2. NOCOPY - Existing files will be used and after completion of the operation, Non-CDB will not remain usable. As new PDB is using the same data files.
  3. MOVE - using parameter FILE_NAME_CONVERT, existing datafiles will be moved to the new location, hence after the operation completion, Non-CDB will not be usable.
Here we will be using the COPY option.


==> Create Directory for datafiles & tempfiles. 

[oracle@report ~]$ mkdir -p /u01/app/oracle/oradata/PDB/MISN
[oracle@report ~]$
[oracle@report ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 15:27:38 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>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MISD                           READ WRITE NO
SQL>
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

SQL>
SQL> create pluggable database MISN using '/home/oracle/MISN_noncdb.xml' copy file_name_convert=('/u01/app/oracle/oradata/MISNT','/u01/app/oracle/oradata/PDB/MISN');


Pluggable database created.

SQL> 
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                           NEW

SQL> alter session set container=MISN;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------
/u01/app/oracle/oradata/PDB/MISN/system01.dbf
/u01/app/oracle/oradata/PDB/MISN/sysaux01.dbf
/u01/app/oracle/oradata/PDB/MISN/undotbs01.dbf
/u01/app/oracle/oradata/PDB/MISN/users01.dbf

SQL>
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------
/u01/app/oracle/oradata/PDB/MISN/temp01.dbf

SQL>
8. Now we need to run the noncdb_to_pdb.sql script on new PDB MISN. 

[oracle@report ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 28 19:07:21 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>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MISD                           READ WRITE NO
         4 MISN                           MOUNTED 
         
SQL> alter session  set container=MISN;

Session altered.

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>#



....................
....................


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>
8. Now open the new PDB MISN and do the post check. 

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> 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=MISN;

Session altered.
SQL>
SQL> alter pluggable database open;

Pluggable database altered.

SQL> alter pluggable database save state;

Pluggable database 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@misn

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Apr 27 16: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 27 2025 13: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
Now the Non-CDB database MISN has been successfully plugged in the CDB database REPORT.
We will see the 2nd Method Creating the PDB via DB Link in the next article.


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

📧 Email: oraeasyy@gmail.com
🌐 Website: www.oraeasy.com