Tuesday, May 27, 2025

Convert Physical Standby To Snapshot Standby

     No comments
  • Introduction: Snapshot standby is a feature since Oracle 11g that allows doing a read-write operation on the standby database. Suppose application team want to do testing on Fresh Live Data, but DBA cannot allow them to test on Primary. To help on this, DBA can convert physical standby to snapshot standby in read-write mode. Then application team can make their changes in snapshot database. Whatever changes done on snapshot standby will be rollbacked once convert back to physical standby database from snapshot standby. Primary database changes will not be applied to snapshot standby database as there is no MRP process running snapshot database.

  • Prerequisites:
    • Oracle Data Guard should be configured.
    • Disable DG broker if any as it will start MRP automatically.
  • Environment:

    • Server Primary Standby
    Hostname Source Target
    IP 192.168.80.51 192.168.80.111
    OS OEL 9 OEL 9
    SID ORCLDC ORCLDR
    Service Name ORCLDC ORCLDR

  • Now proceed to convert into Snapshot Standby Database activity step by step:

1. Check the sync between Primary and Standby
At Primary:

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDC                         PRIMARY          READ WRITE

SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
 (select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL>  

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC                         PRIMARY                   1        250         250 14-APR-2025 12:27:43          0
At Standby:

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL>  

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDR                         PHYSICAL STANDBY          1        250         250 14-APR-2025 12:27:43          0

SQL>
2. Now on Standby, stop MRP & shutdown the database. 

SQL> alter database recover managed standby database cancel;

Database altered.

SQL>
SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';

no rows selected

SQL> create pfile='/home/oracle/drpfile.ora' from spfile;

File created.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
3. Now Standby in mount & convert into Snapshot Standby. 

SQL> startup mount
ORACLE instance started.

Total System Global Area 1459616616 bytes
Fixed Size                  9177960 bytes
Variable Size            1224736768 bytes
Database Buffers          218103808 bytes
Redo Buffers                7598080 bytes
Database mounted.
SQL>


SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         PHYSICAL STANDBY MOUNTED

SQL> alter database convert to snapshot standby;

Database altered.

SQL>
SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         SNAPSHOT STANDBY MOUNTED

SQL> alter database open;

Database altered.

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         SNAPSHOT STANDBY READ WRITE
Note: For RAC system where there are two or more nodes system. Shutdown the database on both the nodes. Then run the convert command on one node. Once completed then start the Database on other nodes.

4. At Primay you will observe the sync gap, but that is no worry. 

SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL>

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC                         PRIMARY                   1        259         250 14-APR-2025 12:27:43          9
5. Now post application team work, you can do the rollback as below: 

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         SNAPSHOT STANDBY READ WRITE

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

Total System Global Area 1459616616 bytes
Fixed Size                  9177960 bytes
Variable Size            1224736768 bytes
Database Buffers          218103808 bytes
Redo Buffers                7598080 bytes
Database mounted.
SQL>
SQL> alter database convert to physical standby;

Database altered.

SQL>
SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         PHYSICAL STANDBY MOUNTED
6. Now start the MRP & verify the sync:

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,thread#,sequence#,block# from v$managed_standby where process like '%MRP%';

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#
--------- ------------ ---------- ---------- ----------
MRP0      APPLYING_LOG          1        260        634

SQL>
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL> 

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                         PHYSICAL STANDBY          1        259         250          9 14-APR-2025 12:27:43       47.02           2821

SQL> /
DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                         PHYSICAL STANDBY          1        259         252          7 14-APR-2025 12:39:31       37.98           2279

SQL>
SQL> /

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                         PHYSICAL STANDBY          1        260         260          0 14-APR-2025 13:19:47        1.45             87


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 20, 2025

Oracle RU 19.27 Patch on 19c Database

     No comments
  • Introduction: A Release Update (RU) is Oracle's primary method for delivering both bug fixes and new features for Oracle Database versions 12.2 and later. Introduced to replace the older Patch Set Updates (PSUs) and Critical Patch Updates (CPUs), RUs are part of Oracle's shift toward a more predictable and modern patching model. In this article we will apply the Oracle RU 19.27 Patch on Primary & Standby database.

  • Data Guard Standby-First Patch Apply: As we are having Data Guard configured, so we will first apply the patch on Standby Database as per Oracle standard. Although we must go through the Patch ReadMe to ensure that the Patch is Data Guard Standby-First Installable. Refer Oracle Doc 1265700.1 for more details

  • Approach:
    • o Apply patch at binary level on Standby.
    o Apply patch at binary level on Primary.
    o Ensure Primay & Standby are in Sync.
    o Run Datapatch (SQL Patch) on Primary & verify the sync between Primary & Standby.

  • Prerequisites:
    • o Downtime required at Database end for 120 mins.
    o Minium 15GB space should be free in ORACLE_HOME mount point. Also 25GB additional space required for patch & Binary backup.
    o Download the RU Patch & required OPatch from below link.
    Oracle RU 19.27 Patch on 19c Database
    Opatch
  • Environment:

    • Server Primary Standby
    Hostname Source Target
    IP 192.168.80.51 192.168.80.111
    OS OEL 9 OEL 9
    SID ORCLDC ORCLDR
    Current Patch Version 19.24 19.24

  • Now first apply the patch on Standby step by step:
1. Copy the patch & required opatch to stanby server and unzip them. Also apply the opatch: 

[oracle@target patches]$ pwd
/u01/app/oracle/patches
[oracle@target patches]$ ls -lrth
total 2.2G
-rw-r--r--. 1 oracle oinstall 2.2G May  4 15:14 p37642901_190000_Linux-x86-64.zip
-rw-r--r--. 1 oracle oinstall  70M May  4 15:14 p6880880_190000_Linux-x86-64_V46.zip
[oracle@target patches]$
[oracle@target patches]$ unzip 19_27_p37642901_190000_Linux-x86-64.zip
[oracle@target patches]$ ls
p37642901_190000_Linux-x86-64.zip  37642901  p6880880_190000_Linux-x86-64_V46.zip  PatchSearch.xml
[oracle@target patches]$
[oracle@target backup]$ cd $ORACLE_HOME
[oracle@target dbhome_1]$ OPatch/./opatch version
OPatch Version: 12.2.0.1.43

OPatch succeeded.
[oracle@target dbhome_1]$
[oracle@target dbhome_1]$ mv  OPatch  OPatch_bkp

[oracle@target patches]$ cd /u01/app/oracle/patches
[oracle@target patches]$ unzip p6880880_190000_Linux-x86-64_V46.zip -d $ORACLE_HOME
[oracle@target backup]$ cd $ORACLE_HOME
[oracle@target dbhome_1]$ OPatch/./opatch version
OPatch Version: 12.2.0.1.46

OPatch succeeded.
[oracle@target dbhome_1]$
2. Now check the sync between Primary & Standby: 


Primary


SQL>  select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDC                         PRIMARY          READ WRITE

SQL>
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL>

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC                         PRIMARY                   1        267         267 04-MAY-2025 14:24:11          0

SQL>



Standby


SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         PHYSICAL STANDBY MOUNTED

SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL>

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                         PHYSICAL STANDBY          1        267         267          0 04-MAY-2025 14:24:11         .43             26

SQL>
3. Disable log shipment at Primary: 

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE

SQL> alter system set log_archive_dest_state_2=DEFER;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER
4. Stop MRP and shutdown Standby Database: 

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@target ~]$
5. Take the ORACLE_HOME(binary) backup at Standby: 

[oracle@target backup]$ pwd
/u01/app/oracle/backup
[oracle@target backup]$
[oracle@target backup]$ env|grep ORA
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
ORACLE_SID=orcldr
ORA_INVENTORY=/u01/app/oraInventory
[oracle@target backup]$
[oracle@target backup]$
[oracle@target backup]$ tar -cvf /u01/app/oracle/backup/DB_HOME_19_24.tar /u01/app/oracle/product/19.0.0
[oracle@target backup]$ ls -lrth
total 11G
-rw-r--r--. 1 oracle oinstall 11G May  4 14:49 DB_HOME_19_24.tar
[oracle@target backup]$
6. Take precheck of lspatches & lsinventory at Standby: 

[oracle@target ~]$ cd $ORACLE_HOME/OPatch
[oracle@target OPatch]$
[oracle@target OPatch]$ ./opatch lsinventory| grep -i applied

Patch  36582781     : applied on Sun Sep 29 11:18:33 IST 2024
Patch  29585399     : applied on Thu Apr 18 12:51:33 IST 2019
[oracle@target OPatch]$
[oracle@target OPatch]$ ./opatch lspatches
36582781;Database Release Update : 19.24.0.0.240716 (36582781)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
[oracle@target OPatch]$
7. Now do patch Conflict precheck: 

[oracle@target patches]$ ls
p37642901_190000_Linux-x86-64.zip  37642901  p6880880_190000_Linux-x86-64_V46.zip  PatchSearch.xml
[oracle@target patches]$
[oracle@target patches]$ cd 37642901
[oracle@target 37642901]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.46
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.46
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2025-05-04_15-34-10PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oracle@target 37642901]$
8. Now apply the patch: 

[oracle@target 37642901]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.46
Copyright (c) 2025, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.46
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2025-05-04_15-39-19PM_1.log

Verifying environment and performing prerequisite checks...

--------------------------------------------------------------------------------
Start OOP by Prereq process.
Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.46
Copyright (c) 2025, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.46
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2025-05-04_15-41-51PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   37642901

Do you want to proceed? [y|n]

y

User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')


Is the local system ready for patching? [y|n]

y

User Responded with: Y
Backing up files...
Applying interim patch '37642901' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.crypto.rsf, 19.0.0.0.0 ] , [ oracle.pg4appc, 19.0.0.0.0 ] , [ oracle.pg4mq, 19.0.0.0.0 ] , [ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.precomp.companion, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.ldap.ztk, 19.0.0.0.0 ] , [ oracle.java.sqlj.sqljruntime, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] , [ oracle.jdk, 1.8.0.391.11 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms, 19.0.0.0.0...


Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.blaslapack, 19.0.0.0.0...

Patching component oracle.buildtools.rsf, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.marvel, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.odbc.ic, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.ons.ic, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.perlint, 5.28.1.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.crs, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.rhp.db, 19.0.0.0.0...

Patching component oracle.rsf, 19.0.0.0.0...

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.sqlj.sqljruntime, 19.0.0.0.0...

Patching component oracle.tfa.db, 19.0.0.0.0...

Patching component oracle.wwg.plsql, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.ldap.client, 19.0.0.0.0...

Patching component oracle.ldap.ssl, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.mgw.common, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.rdbms.locator, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.oraolap.api, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.lbuilder, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.ctx.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.install.common, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.xdk.xquery, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.rdbms.hs_common, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.network.aso, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.ic, 19.0.0.0.0...

Patching component oracle.dbtoolslistener, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.rdbms.drdaas, 19.0.0.0.0...

Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.rdbms.dm, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.rdbms.rat, 19.0.0.0.0...

Patching component oracle.install.deinstalltool, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.jdk, 1.8.0.201.0...
Patch 37642901 successfully applied.
Sub-set patch [36582781] has become inactive due to the application of a super-set patch [37642901].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2025-05-04_15-41-51PM_1.log

OPatch succeeded.
[oracle@target 37642901]$
9. Now check the lspatches & lsinventory: 

[oracle@target 37642901]$ cd $ORACLE_HOME/OPatch
[oracle@target OPatch]$
[oracle@target OPatch]$  ./opatch lsinventory| grep -i applied
Patch  37642901     : applied on Sun May 04 15:54:47 IST 2025
Patch  29585399     : applied on Thu Apr 18 12:51:33 IST 2019
[oracle@target OPatch]$
[oracle@target OPatch]$ ./opatch lspatches
37642901;Database Release Update : 19.27.0.0.250415 (37642901)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
[oracle@target OPatch]$
10. Now start Standby Database & start MRP: 

[oracle@target ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 4 17:21:14 2025
Version 19.27.0.0.0

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

Connected to an idle instance.

SQL>
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1459616616 bytes
Fixed Size                  9177960 bytes
Variable Size            1224736768 bytes
Database Buffers          218103808 bytes
Redo Buffers                7598080 bytes
Database mounted.
SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>
11. Enable log shipment at Primary : 

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER

SQL> alter system set log_archive_dest_state_2=ENABLE;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE
12. Verify the sync between Primary & Standby: 


Primary



SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDC                         PRIMARY          READ WRITE

SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
SQL> (select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL> 

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC                         PRIMARY                   1        268         268 04-MAY-2025 17:24:17          0

SQL>


Standby



SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDR                         PHYSICAL STANDBY MOUNTED

SQL>
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by SQL> thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;

SQL>

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                         PHYSICAL STANDBY          1        268         268          0 04-MAY-2025 17:24:17       12.28            737
Now we have successfully applied the 19.27 Patch on Standby, so let's proceed to apply the Patch on Primary as well.

  • Now apply the patch on Primary step by step:
1. Copy the patch & required opatch to Primary server and unzip them. Also apply the opatch: 

[oracle@source patches]$ pwd
/u01/app/oracle/patches
[oracle@source patches]$ ls -lrth
total 2.2G
-rw-r--r--. 1 oracle oinstall 2.2G May  4 18:29 p37642901_190000_Linux-x86-64.zip
-rw-r--r--. 1 oracle oinstall  70M May  4 18:29 p6880880_190000_Linux-x86-64_V46.zip
[oracle@source patches]$
[oracle@source patches]$ unzip 19_27_p37642901_190000_Linux-x86-64.zip
[oracle@source patches]$ ls
p37642901_190000_Linux-x86-64.zip  37642901  p6880880_190000_Linux-x86-64_V46.zip  PatchSearch.xml
[oracle@source patches]$
[oracle@source backup]$ cd $ORACLE_HOME
[oracle@source dbhome_1]$ OPatch/./opatch version
OPatch Version: 12.2.0.1.43

OPatch succeeded.
[oracle@source dbhome_1]$
[oracle@source dbhome_1]$ mv  OPatch  OPatch_bkp

[oracle@source patches]$ cd /u01/app/oracle/patches
[oracle@source patches]$ unzip p6880880_190000_Linux-x86-64_V46.zip -d $ORACLE_HOME
[oracle@source backup]$ cd $ORACLE_HOME
[oracle@source dbhome_1]$ OPatch/./opatch version
OPatch Version: 12.2.0.1.46

OPatch succeeded.
[oracle@source dbhome_1]
2. Now take Precheck at Primary: 

SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDC                         PRIMARY          READ WRITE

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


SQL> 

 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    22-JAN-25 12.15.07.4 Database Release Update : 19.24.0.0.240716 (36582781)
                                                                            85121 AM


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

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> show pdbs

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

Session altered.

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
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@source ~]$ cd $ORACLE_HOME/OPatch
[oracle@source OPatch]$
[oracle@source OPatch]$ ./opatch lsinventory| grep -i applied
Patch  36582781     : applied on Sun Sep 29 11:18:33 IST 2024
Patch  29585399     : applied on Thu Apr 18 12:51:33 IST 2019
[oracle@source OPatch]$
[oracle@source OPatch]$ ./opatch lspatches
36582781;Database Release Update : 19.24.0.0.240716 (36582781)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
[oracle@source OPatch]
3. Disable log shipment at Primary: 

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE

SQL> alter system set log_archive_dest_state_2=DEFER;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER
4. Stop MRP at Standby Database: 

SQL> alter database recover managed standby database cancel;

Database altered.
5. Now do patch Conflict precheck:: 

[oracle@source patch]$ ls
p37642901_190000_Linux-x86-64.zip  37642901  p6880880_190000_Linux-x86-64_V46.zip  PatchSearch.xml
[oracle@source patch]$ cd 37642901/
[oracle@source 37642901]$
[oracle@source 37642901]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.46
Copyright (c) 2025, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.46
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2025-05-04_18-44-36PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
[oracle@source 37642901]$
6. Stop Database & Listener at Primary: 

[oracle@source ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 4 17:53:42 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>
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.24.0.0.0
[oracle@source ~]$
[oracle@source ~]$
[oracle@source ~]$ lsnrctl stop

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-MAY-2025 17:54:34

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=source.localdomain)(PORT=1521)))
The command completed successfully
[oracle@source ~]$
7. Take the ORACLE_HOME(binary) backup: 

[oracle@source backup]$
[oracle@source backup]$ env|grep ORA
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
ORACLE_SID=orcldc
ORA_INVENTORY=/u01/app/oraInventory
[oracle@source backup]$
[oracle@source backup]$ tar -cvf /u01/app/oracle/backup/DB_HOME_19_24.tar /u01/app/oracle/product/19.0.0

[oracle@source backup]$ ls -lrth
total 11G
-rw-r--r--. 1 oracle oinstall 11G May  4 18:06 DB_HOME_19_24.tar
[oracle@source backup]$
8. Now apply the patch: 

[oracle@source 37642901]$ $ORACLE_HOME/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.46
Copyright (c) 2025, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.46
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2025-05-04_18-59-01PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   37642901

Do you want to proceed? [y|n]

y

User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/19.0.0/dbhome_1')


Is the local system ready for patching? [y|n]

y

User Responded with: Y
Backing up files...
Applying interim patch '37642901' to OH '/u01/app/oracle/product/19.0.0/dbhome_1'
ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.crypto.rsf, 19.0.0.0.0 ] , [ oracle.pg4appc, 19.0.0.0.0 ] , [ oracle.pg4mq, 19.0.0.0.0 ] , [ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.precomp.companion, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.ldap.ztk, 19.0.0.0.0 ] , [ oracle.java.sqlj.sqljruntime, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] , [ oracle.jdk, 1.8.0.391.11 ]  not present in the Oracle Home or a higher version is found.

Patching component oracle.rdbms, 19.0.0.0.0...

Patching component oracle.rdbms.util, 19.0.0.0.0...

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.assistants.acf, 19.0.0.0.0...

Patching component oracle.assistants.deconfig, 19.0.0.0.0...

Patching component oracle.assistants.server, 19.0.0.0.0...

Patching component oracle.blaslapack, 19.0.0.0.0...

Patching component oracle.buildtools.rsf, 19.0.0.0.0...

Patching component oracle.ctx, 19.0.0.0.0...

Patching component oracle.dbdev, 19.0.0.0.0...

Patching component oracle.dbjava.ic, 19.0.0.0.0...

Patching component oracle.dbjava.jdbc, 19.0.0.0.0...

Patching component oracle.dbjava.ucp, 19.0.0.0.0...

Patching component oracle.duma, 19.0.0.0.0...

Patching component oracle.javavm.client, 19.0.0.0.0...

Patching component oracle.ldap.owm, 19.0.0.0.0...

Patching component oracle.ldap.rsf, 19.0.0.0.0...

Patching component oracle.ldap.security.osdt, 19.0.0.0.0...

Patching component oracle.marvel, 19.0.0.0.0...

Patching component oracle.network.rsf, 19.0.0.0.0...

Patching component oracle.odbc.ic, 19.0.0.0.0...

Patching component oracle.ons, 19.0.0.0.0...

Patching component oracle.ons.ic, 19.0.0.0.0...

Patching component oracle.oracore.rsf, 19.0.0.0.0...

Patching component oracle.perlint, 5.28.1.0.0...

Patching component oracle.precomp.common.core, 19.0.0.0.0...

Patching component oracle.precomp.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.crs, 19.0.0.0.0...

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...

Patching component oracle.rdbms.deconfig, 19.0.0.0.0...

Patching component oracle.rdbms.oci, 19.0.0.0.0...

Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0...

Patching component oracle.rdbms.scheduler, 19.0.0.0.0...

Patching component oracle.rhp.db, 19.0.0.0.0...

Patching component oracle.rsf, 19.0.0.0.0...

Patching component oracle.sdo, 19.0.0.0.0...

Patching component oracle.sdo.locator.jrf, 19.0.0.0.0...

Patching component oracle.sqlplus, 19.0.0.0.0...

Patching component oracle.sqlplus.ic, 19.0.0.0.0...

Patching component oracle.sqlj.sqljruntime, 19.0.0.0.0...

Patching component oracle.tfa.db, 19.0.0.0.0...

Patching component oracle.wwg.plsql, 19.0.0.0.0...

Patching component oracle.xdk.rsf, 19.0.0.0.0...

Patching component oracle.ldap.client, 19.0.0.0.0...

Patching component oracle.ldap.ssl, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0...

Patching component oracle.oraolap.dbscripts, 19.0.0.0.0...

Patching component oracle.mgw.common, 19.0.0.0.0...

Patching component oracle.rdbms.install.plugins, 19.0.0.0.0...

Patching component oracle.ldap.rsf.ic, 19.0.0.0.0...

Patching component oracle.javavm.server, 19.0.0.0.0...

Patching component oracle.ovm, 19.0.0.0.0...

Patching component oracle.rdbms.locator, 19.0.0.0.0...

Patching component oracle.xdk.parser.java, 19.0.0.0.0...

Patching component oracle.oraolap.api, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.lbuilder, 19.0.0.0.0...

Patching component oracle.rdbms.lbac, 19.0.0.0.0...

Patching component oracle.ctx.rsf, 19.0.0.0.0...

Patching component oracle.rdbms.install.common, 19.0.0.0.0...

Patching component oracle.network.listener, 19.0.0.0.0...

Patching component oracle.xdk.xquery, 19.0.0.0.0...

Patching component oracle.odbc, 19.0.0.0.0...

Patching component oracle.ctx.atg, 19.0.0.0.0...

Patching component oracle.rdbms.hs_common, 19.0.0.0.0...

Patching component oracle.oraolap, 19.0.0.0.0...

Patching component oracle.network.aso, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0...

Patching component oracle.nlsrtl.rsf.ic, 19.0.0.0.0...

Patching component oracle.dbtoolslistener, 19.0.0.0.0...

Patching component oracle.rdbms.rman, 19.0.0.0.0...

Patching component oracle.xdk, 19.0.0.0.0...

Patching component oracle.rdbms.drdaas, 19.0.0.0.0...

Patching component oracle.rdbms.hsodbc, 19.0.0.0.0...

Patching component oracle.network.client, 19.0.0.0.0...

Patching component oracle.rdbms.dm, 19.0.0.0.0...

Patching component oracle.sdo.locator, 19.0.0.0.0...

Patching component oracle.rdbms.dv, 19.0.0.0.0...

Patching component oracle.rdbms.rat, 19.0.0.0.0...

Patching component oracle.install.deinstalltool, 19.0.0.0.0...

Patching component oracle.precomp.lang, 19.0.0.0.0...

Patching component oracle.precomp.common, 19.0.0.0.0...

Patching component oracle.jdk, 1.8.0.201.0...

Patch 37642901 successfully applied.
Sub-set patch [36582781] has become inactive due to the application of a super-set patch [37642901].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2025-05-04_18-59-01PM_1.log

OPatch succeeded.
[oracle@source 37642901]$
[oracle@source 37642901]$
9. Now check the lspatches & lsinventory: 

[oracle@source OPatch]$ cd $ORACLE_HOME/OPatch
[oracle@source OPatch]$ ./opatch lspatches
37642901;Database Release Update : 19.27.0.0.250415 (37642901)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.
[oracle@source OPatch]$
[oracle@source OPatch]$ ./opatch lsinventory| grep -i applied
Patch  37642901     : applied on Sun May 04 19:10:36 IST 2025
Patch  29585399     : applied on Thu Apr 18 12:51:33 IST 2019
[oracle@source OPatch]$
10. Now start Database & Listener: 

[oracle@source ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 4 19:42:21 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 1459616616 bytes
Fixed Size                  9177960 bytes
Variable Size            1207959552 bytes
Database Buffers          234881024 bytes
Redo Buffers                7598080 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0
[oracle@source ~]$ lsnrctl start

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 04-MAY-2025 19:50:18

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

Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/source/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source.localdomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=source.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                04-MAY-2025 19:50:18
Uptime                    0 days 0 hr. 0 min. 0 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/source/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=source.localdomain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@source ~]$
11. Enable log shipment at Primary : 

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER

SQL> alter system set log_archive_dest_state_2=ENABLE;

System altered.

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      ENABLE
12. Start MRP at Standby : 

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>
13. Verify the sync between Primary & Standby: 


Primary


SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDC                         PRIMARY          READ WRITE

SQL>
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp, b.last_seq - a.applied_seq ARC_DIFF
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;


SQL>

DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP     ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC                         PRIMARY                   1        271         271 04-MAY-2025 19:57:10          0

SQL>


Standby


SQL> set lines 200 pages 300
SQL> alter session set nls_date_format= 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name,d.database_role, a.thread#, b.last_seq, a.applied_seq, b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins, round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp from gv$archived_log where REGISTRAR='RFS' and applied='YES' group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name,database_role from v$database) d where a.thread#=b.thread#;
DB_UNIQUE_NAME                 DATABASE_ROLE       THREAD#   LAST_SEQ APPLIED_SEQ   ARC_DIFF LAST_APP_TIMESTAMP   GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR                         PHYSICAL STANDBY          1        271         271          0 04-MAY-2025 19:57:10        1.62             97

SQL>
14. Now run the datapatch: 

[oracle@source ~]$ cd $ORACLE_HOME/OPatch
[oracle@source OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.27.0.0.0 Production on Sun May  4 20:59:47 2025
Copyright (c) 2012, 2025, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_19458_2025_05_04_20_59_47/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.24.0.0.0 Release_Update 240627235157 successfully on 22-JAN-25 12.15.07.485121 AM
  PDB ORCLPDB:
    Applied 19.24.0.0.0 Release_Update 240627235157 successfully on 22-JAN-25 12.53.22.669280 AM
  PDB PDB$SEED:
    Applied 19.24.0.0.0 Release_Update 240627235157 successfully on 22-JAN-25 12.53.22.669280 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  For the following PDBs: CDB$ROOT PDB$SEED ORCLPDB
    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: 3

Validating logfiles...done
Patch 37642901 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/37642901/27123174/37642901_apply_ORCL_CDBROOT_2025May04_21_01_06.log (no errors)
Patch 37642901 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/37642901/27123174/37642901_apply_ORCL_PDBSEED_2025May04_21_07_51.log (no errors)
Patch 37642901 apply (pdb ORCLPDB): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/37642901/27123174/37642901_apply_ORCL_ORCLPDB_2025May04_21_07_52.log (no errors)
SQL Patching tool complete on Sun May  4 21:16:43 2025
15. Now take the Postcheck: 

[oracle@source ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun May 4 21:23:37 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>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB                        READ WRITE NO
SQL>
SQL> select name,db_unique_name,database_role,open_mode from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL      ORCLDC                         PRIMARY          READ WRITE

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> set lines 333 pages 333
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    22-JAN-25 12.15.07.4 Database Release Update : 19.24.0.0.240716 (36582781)
                                                                            85121 AM

  27123174   37642901 19.24.0.0.0     19.27.0.0.0     APPLY      SUCCESS    04-MAY-25 09.06.41.8 Database Release Update : 19.27.0.0.250415 (37642901)
                                                                            92191 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> show pdbs

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

Session altered.

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


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