首页 » ORACLE [C]系列 » How To fix Oracle 19c PDB clone cross differente RU CDB, Then PDB in restricted
How To fix Oracle 19c PDB clone cross differente RU CDB, Then PDB in restricted
几年前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 恢复正常模式。
对不起,这篇文章暂时关闭评论。