几年前12c刚release时测试过一系列pdb的功能，其中<Oracle 12c R2 New Feature: Hot Clone A PDB (12.2热克隆)>有小测试一下12.2的pdb hot clone,最近看到一个案例是19c 在不同的RU版本的CDB之间通过PDB clone迁移的数据库，从19.6 clone到19.11后PDB 变为restricted受限模式，查询PDB_PLUG_IN_VIOLATIONS看到如下错误：
‘19.11.0.0.0 Release_Update 2104130040’ is installed in the CDB but ‘19.6.0.0.0 Release_Update 1912171550’ is installed in the PDB
那测试2个场景：
1，19.6 RU to 19.11 RU
2, 19.6 RU带一个oneoff patch（19.11 fixed） to 19.11 RU
19.6 RU to 19.11 RU
[oracle@anbob OPatch]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 8 01:01:51 2022 Version 19.6.0.0.0 Copyright (c) 1998, 2019, Oracle. All rights reserved. SQL> create pluggable database pdbtt admin user pdb1admin identified by pdb1admin file_name_convert=('/u01/app/oracle/oradata/ORCLTT/pdbseed','/u01/app/oracle/oradata/ORCLTT/pdbtt'); SQL> alter pluggable database all open; SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB READ WRITE NO 4 PDBTT READ WRITE NO [oracle@anbob OPatch]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 8 01:02:04 2022 Version 19.11.0.0.0 Copyright (c) 1982, 2020, Oracle. All rights reserved. SQL> create public database link dblink_clone_pdb connect to system identified by "oracle" using '192.168.0.51:1521/orcltt'; SQL> create pluggable database clone_pdb from pdbtt@dblink_clone_pdb file_name_convert=('/u01/app/oracle/oradata/ORCLTT/pdbtt','/u01/app/oracle/oradata/ORCLTT/clone_pdb'); Pluggable database created. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB READ WRITE NO 4 CLONE_PDB MOUNTED SQL> alter pluggable database CLONE_PDB open; Warning: PDB altered with errors. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB READ WRITE NO 4 CLONE_PDB READ WRITE YES SQL> select name, cause, type, message, status, action from PDB_PLUG_IN_VIOLATIONS where type like 'ERROR%' and status like 'PENDING'; NAME CAUSE TYPE MESSAGE -------- --------- ----- -------------------------------------------------------------------- STATUS ACTION CLONE_PDB SQL Patch ERROR '19.11.0.0.0 Release_Update 2104130040' is installed in the CDB but '19.6.0.0.0 Release_Update 1912171550' is installed in the PDB PENDING Call datapatch to install in the PDB or the CDB
解决
[oracle@anbob OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.11.0.0.0 Production on Thu Dec 8 01:31:47 2022
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_36866_2022_12_08_01_31_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.11.0.0.0 Release_Update 210413004009: Installed
PDB CDB$ROOT:
Applied 19.11.0.0.0 Release_Update 210413004009 successfully on 08-DEC-22 01.09.42.503697 AM
PDB CLONE_PDB:
Applied 19.6.0.0.0 Release_Update 191217155004 successfully on 08-DEC-22 01.05.58.491994 AM
PDB PDB:
Applied 19.11.0.0.0 Release_Update 210413004009 successfully on 08-DEC-22 01.09.48.660777 AM
PDB PDB$SEED:
Applied 19.11.0.0.0 Release_Update 210413004009 successfully on 08-DEC-22 01.09.45.566126 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB
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: CLONE_PDB
No interim patches need to be rolled back
Patch 32545013 (Database Release Update : 19.11.0.0.210420 (32545013)):
Apply from 19.6.0.0.0 Release_Update 191217155004 to 19.11.0.0.0 Release_Update 210413004009
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 32545013 apply (pdb CLONE_PDB): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32545013/24175065/32545013_apply_ORCLTT_CLONE_PDB_2022Dec08_01_32_04.log (no errors)
SQL Patching tool complete on Thu Dec 8 01:35:15 2022
[oracle@anbob OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 8 01:35:24 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 CLONE_PDB READ WRITE YES
SQL> alter pluggable database CLONE_PDB close immediate;
Pluggable database altered.
SQL> alter pluggable database CLONE_PDB open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 CLONE_PDB READ WRITE NO
SQL>
19.6 RU带一个oneoff patch（19.11 fixed） to 19.11 RU
# 19.6 找一个小oneoff patch 31142749,该patch在19.11是修复的测试是否影响 [oracle@anbob 31142749]$ ll total 8 drwxr-x--- 3 oracle oinstall 20 Aug 13 2021 etc drwxr-x--- 4 oracle oinstall 30 Aug 13 2021 files -rw-rw-r-- 1 oracle oinstall 6213 Aug 13 2021 README.txt [oracle@anbob 31142749]$ /u01/app/oracle/product/19.3.0/db_1/OPatch/opatch apply ... Is the local system ready for patching? [y|n] Y (auto-answered by -silent) User Responded with: Y Backing up files... Applying interim patch '31142749' to OH '/u01/app/oracle/product/19.3.0/db_1' Patching component oracle.rdbms.rsf, 19.0.0.0.0... Patching component oracle.rdbms.rman, 19.0.0.0.0... Patching component oracle.rdbms, 19.0.0.0.0... Patch 31142749 successfully applied. OPatch succeeded. [oracle@anbob trace]$ opatch lspatches 31142749;QUERY ON ALL_ARGUMENTS SLOW IN 19.6 PDB 30557433;Database Release Update : 19.6.0.0.200114 (30557433) 29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399) OPatch succeeded. # 19.11 RU SQL> create pluggable database clone_pdb2 from pdbtt@dblink_clone_pdb file_name_convert=('/u01/app/oracle/oradata/ORCLTT/pdbtt','/u01/app/oracle/oradata/ORCLTT/clone_pdb2'); Pluggable database created. SQL> alter pluggable database CLONE_PDB2 open; Warning: PDB altered with errors. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB READ WRITE NO 4 CLONE_PDB READ WRITE NO 6 CLONE_PDB2 READ WRITE YES SQL> select name, cause, type, message, status, action from PDB_PLUG_IN_VIOLATIONS where type like 'ERROR%' and status like 'PENDING'; NAME CAUSE TYPE MESSAGE STATUS ACTION CLONE_PDB2 SQL Patch ERROR '19.11.0.0.0 Release_Update 2104130040' is installed in the CDB but '19.6.0.0.0 Release_Update 1912171550' is installed in the PDB PENDING Call datapatch to install in the PDB or the CDB
解决
[oracle@anbob OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.11.0.0.0 Production on Thu Dec 8 01:56:36 2022
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_43830_2022_12_08_01_56_36/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.11.0.0.0 Release_Update 210413004009: Installed
PDB CDB$ROOT:
Applied 19.11.0.0.0 Release_Update 210413004009 successfully on 08-DEC-22 01.09.42.503697 AM
PDB CLONE_PDB:
Applied 19.11.0.0.0 Release_Update 210413004009 successfully on 08-DEC-22 01.35.11.641369 AM
PDB CLONE_PDB2:
Applied 19.6.0.0.0 Release_Update 191217155004 successfully on 08-DEC-22 01.05.58.491994 AM
PDB PDB:
Applied 19.11.0.0.0 Release_Update 210413004009 successfully on 08-DEC-22 01.09.48.660777 AM
PDB PDB$SEED:
Applied 19.11.0.0.0 Release_Update 210413004009 successfully on 08-DEC-22 01.09.45.566126 AM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB CLONE_PDB
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: CLONE_PDB2
No interim patches need to be rolled back
Patch 32545013 (Database Release Update : 19.11.0.0.210420 (32545013)):
Apply from 19.6.0.0.0 Release_Update 191217155004 to 19.11.0.0.0 Release_Update 210413004009
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 32545013 apply (pdb CLONE_PDB2): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32545013/24175065/32545013_apply_ORCLTT_CLONE_PDB2_2022Dec08_01_56_53.log (no errors)
SQL Patching tool complete on Thu Dec 8 02:00:04 2022
[oracle@anbob OPatch]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 8 02:00:29 2022
Version 19.11.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 CLONE_PDB READ WRITE NO
6 CLONE_PDB2 READ WRITE YES
SQL> alter pluggable database CLONE_PDB2 close immediate;
Pluggable database altered.
SQL> alter pluggable database CLONE_PDB2 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 CLONE_PDB READ WRITE NO
6 CLONE_PDB2 READ WRITE NO
Summary:
当从低RU向高版本RU clone PDB时，PDB open后会因为数据字典的版本不一致，启动到受限模式，无论是否有不兼容oneoff patch, 直接在目标执行datapatch -verbose时会自动更新RU不一致的PDB, 同时也会检查是否有需要回滚的patch. 应用完opatch后，需要再重启一下PDB 恢复正常模式。