首页 » 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 恢复正常模式。

打赏

目前这篇文章还没有评论(Rss)

我要评论