Problem statement:Lets assume you want to migrate your existing non-cdb database and plug into a cdb running in same database server and storage.At the same time you want to minimize downtime of this migration as much as possible. This can be achieved using a feature called refreshable pdb introduced in Oracle 12.2
Test environment:
Database version :Oracle Enterprise edition 19.26
On source
grant create session,create pluggable database,select_catalog_role to dblinkuser;
grant read on sys.enc$ to dblinkuser;
On target
CREATE PLUGGABLE DATABASE pdb1 FROM pdb1@clonepdb REFRESH MODE EVERY 10 MINUTES;
SQL> CREATE PLUGGABLE DATABASE pdb1 FROM pdb1@clonepdb REFRESH MODE EVERY 10 MINUTES;
Pluggable database created.
Let me try to summarize the steps here
1.It remotely connects to source non-cdb database using dblink created above and copy datafiles.
2.It creates a scheduler job in target cdb database
2.Once pdb creation is completed it remains in mount mode and tries to sync data periodically based on your statement "REFRESH MODE EVERY N MINUTES".During refresh it tries to reads online redo from last applied SCN using some scheduler jobs.If that is found in redo log then it creates partial archivelog from that redo and changes are pushed back to target pdbs.Once it is synced partial archivelogs are deleted.
If you check source non-cdb database alert log you can find similar alerts like below
Deleted Oracle managed file +RECO/pdb1/partial_archivelog/2026_03_08/thread_1_seq_184.451.1227321403
Since refresh is based on dblink which again based on tnsname used on the definition of dblink,so jst comment out tns entry of the source database and you can see following error in last log of target cdb.
ORA-12012: error on auto execute of job "SYS"."pdb1_2438444115_REFRESH"
ORA-17627: ORA-01109: database not open
ORA-17629: Cannot connect to the remote database server
ORA-06512: at "SYS.DBMS_SQL", line 2995
so,now you came to know about the scheduler job which is created in target pdb.
On the target pdb run a final manual refresh to apply any last-minute changes
SQL>ALTER PLUGGABLE DATABASE pdb1 REFRESH;
Convert the target PDB from a refreshable clone to a regular, standalone PDB by setting its refresh mode to NONE using below command
SQL>ALTER PLUGGABLE DATABASE pdb1 REFRESH MODE NONE;
Now open the target pdb database
SQL>ALTER PLUGGABLE DATABASE pdb1 open;
Check the status of target pdb
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBTEST READ WRITE
pdb1 READ WRITE
For me it was opened with below warning
Warning: PDB altered with errors.
Lets see alert log what is this warning about.Same can also be found using below query
select type, cause, message from PDB_PLUG_IN_VIOLATIONS where name='pdb1' and status != 'RESOLVED';
pdb1(4) Error Violation: Non-CDB to PDB, Cause: PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run., Action: Run noncdb_to_pdb.sql.
pdb1(4) Error Violation: SQL Patch, Cause: Interim patch 29213893/25940729 (DBMS_STATS FAILING WITH ERROR ORA-01422 WHEN GATHERING STATS FOR USER$ TABLE): Installed in the CDB but not in the PDB, Action: Call datapatch to install in the PDB or the CDB
So I have 2 action items here
I need to run run noncdb_to_pdb.sql and apply datapatch
set ORACLE environment
SQL>ALTER SESSION SET CONTAINER=pdb1;
SQL>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
$ORACLE_HOME/OPatch/datapatch -verbose
Make sure all components are valid for target pdb.In my case below component was invalid
COMP_NAME VERSION_FULL STATUS
---------------------------------------- ------------------------------ ---------------------------------
Oracle Database Catalog Views 19.26.0.0.0 VALID
Oracle Database Packages and Types 19.26.0.0.0 INVALID
Oracle XML Database 19.26.0.0.0 VALID
Oracle Workspace Manager 19.26.0.0.0 VALID
JServer JAVA Virtual Machine 19.26.0.0.0 VALID
Oracle XDK 19.26.0.0.0 VALID
Oracle Database Java Packages 19.26.0.0.0 VALID
Oracle Text 19.26.0.0.0 VALID
Oracle Multimedia 19.26.0.0.0 VALID
OLAP Analytic Workspace 19.26.0.0.0 VALID
Oracle OLAP API 19.26.0.0.0 VALID
Spatial 19.26.0.0.0 VALID
Oracle Label Security 19.26.0.0.0 VALID
Oracle Database Vault 19.26.0.0.0 VALID
Oracle Real Application Clusters 19.26.0.0.0 VALID
I just executed utlrp.sql under $ORACLE_HOME/rdbms/admin to compile invalid components and after that it became valid.There are some limitations of this feature which has been described in below articles.
I hope you have learned something useful.
Reference:-
https://dohdatabase.com/2024/08/28/a-few-details-about-using-refreshable-clone-pdb-for-non-cdb-to-pdb-migration/
https://dohdatabase.com/2023/09/27/how-to-upgrade-to-oracle-database-19c-and-migrate-to-a-pdb-using-refreshable-clone-pdbs/
