真实环境下创建pdb、在两个cdb之间迁移pdb和创建proxy pdb的操作汇总系列3
Author: Bo Tang
1. 把non$cdb通过数据库链接“热”插入cdb2中，之后源头数据库和目的地数据库之间也没有任何同步关系，各自独立运行：
1） 准备好一个non$cdb，其数据库名叫做orcl2，实际上它是一个从windows上跨平台迁移得到的19.3.0版本的数据库，其基本情况如下（orcl2的sys和system的密码都是javamysql_4U，与cdb2的sys和system的密码不同，cdb2的sys和system的密码是cloud_4U，注意：插入non$cdb后，它的sys和system的密码都得与cdb保持一致）：
select property_name,property_value
from database_properties;
------------------------------------------------------------------------------------------------------
MAX_PDB_SNAPSHOTS 8
CON_VSN 2
DICTIONARY_ENDIAN_TYPE LITTLE
LOCAL_UNDO_ENABLED TRUE
OLS_OID_STATUS 0
GLOBAL_DB_NAME ORCL2
MAX_STRING_SIZE STANDARD
NO_USERID_VERIFIER_SALT E4FA6F27C073C7D5C89D3E2FBCA3D0C7
WORKLOAD_REPLAY_MODE
WORKLOAD_CAPTURE_MODE
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
DEFAULT_TBS_TYPE SMALLFILE
DST_SECONDARY_TT_VERSION 0
DST_PRIMARY_TT_VERSION 32
DST_UPGRADE_STATE NONE
DBTIMEZONE 00:00
TDE_MASTER_KEY_ID
EXPORT_VIEWS_VERSION 8
Flashback Timestamp TimeZone GMT
DEFAULT_EDITION ORA$BASE
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_TEMP_TABLESPACE TEMP
DICT.BASE 2 |
2）在cdb2中，创建指向orcl2的system用户的数据库链接： [oracle@classroom ~]$ tnsping orcl2
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 06-MAR-2022 11:24:46
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting
to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
classroom.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME = orcl2)))
OK (0 msec)
[oracle@classroom admin]$ . oraenv
ORACLE_SID = [cdb2] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@classroom admin]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 6 11:23:29 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create database link link_orcl2 connect to system identified by javamysql_4U using 'orcl2';
Database link created.
SQL> select count(*) from dba_source@link_orcl2;
COUNT(*)
----------
290407
SQL> |
3）源端orcl2上的数据库上用于链接的用户system需要授权：
在源端需要一些特殊的权限（光光有dba不够），需要给create pluggable database的权限。请在orcl2数据库上操作： grant create pluggable database to system; select * from dba_sys_privs where grantee='SYSTEM'; -------------------------------------------------------------------- SYSTEM CREATE PLUGGABLE DATABASE NO NO NO SYSTEM GLOBAL QUERY REWRITE NO YES YES SYSTEM CREATE TABLE NO YES YES SYSTEM DEQUEUE ANY QUEUE YES YES YES SYSTEM ENQUEUE ANY QUEUE YES YES YES SYSTEM SELECT ANY TABLE NO YES YES SYSTEM MANAGE ANY QUEUE YES YES YES SYSTEM UNLIMITED TABLESPACE NO YES YES SYSTEM CREATE MATERIALIZED VIEW NO YES YES |
4）通过数据库链在线“热”插入non$cdb为插件数据库pdb_orcl2：
让orcl2处于高负载插入状态下，同时进行pdb_orcl2的在线“热”插入：
在orcl2：
SQL> create table hr.tbig as select * from dba_source;
Table created.
SQL> select count(*) from hr.tbig;
COUNT(*)
-------------
290407
注：在执行下面操作时（commit前），同时在cdb2上操作（见下个代码框提示）！
SQL> conn hr/javamysql_4U@orcl2
Connected.
SQL> insert into tbig select * from tbig;
290407 rows created.
SQL> insert into tbig select * from tbig;
580814 rows created.
SQL> insert into tbig select * from tbig;
1161628 rows created.
SQL> commit;
Commit complete.
|
在上面代码框标注红字处，同时在cdb2上操作：
[oracle@classroom ~]$ . oraenv
ORACLE_SID = [cdb2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 1 16:21:06 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL>create pluggable database pdb_orcl2 from non$cdb@link_orcl2;
Pluggable database created.
|
[oracle@classroom admin]$ tnsping pdb_orcl2
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-MAR-2022 17:13:28
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = classroom.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb_orcl2)))
OK (0 msec)
[oracle@classroom admin]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 1 17:13:39 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn hr/cloud_4U@pdb_orcl2
ERROR:
ORA-01109: database not open
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb_orcl2 open;
Warning: PDB altered with errors.
SQL> conn sys/cloud_4U@pdb_orcl2 as sysdba
????
SQL> select count(*) from hr.tbig;
COUNT(*)
----------
290407
|
说明热克隆操作成功完成，并且维持了读一致，之后源头数据库和目的地数据库之间也没有任何同步关系，各自独立运行。但是pdb_orcl2打开时已经报错。
2. 真实环境下，处理pdb_orcl2的Warning：
在cdb2上查pdb_plug_in_violations：
select * from pdb_plug_in_violations
where name='PDB_ORCL2';
-------------------------------------------------------------------------------
06-MAR-22 06.41.05.833905000 PM PDB_ORCL2 Parameter WARNING 0 1 CDB parameter processes mismatch: Previous 300 Current 640 RESOLVED Please check the parameter in the current CDB 1
06-MAR-22 06.41.05.833905000 PM PDB_ORCL2 Parameter WARNING 0 2 CDB parameter nls_language mismatch: Previous 'SIMPLIFIED CHINESE' Current 'AMERICAN' RESOLVED Please check the parameter in the current CDB 1
06-MAR-22 06.41.05.833905000 PM PDB_ORCL2 Parameter WARNING 0 3 CDB parameter nls_territory mismatch: Previous 'CHINA' Current 'AMERICA' RESOLVED Please check the parameter in the current CDB 1
06-MAR-22 06.41.05.833905000 PM PDB_ORCL2 Parameter WARNING 0 4 CDB parameter sga_target mismatch: Previous 2448M Current 3136M RESOLVED Please check the parameter in the current CDB 1
06-MAR-22 06.41.05.833905000 PM PDB_ORCL2 Parameter WARNING 0 5 CDB parameter pga_aggregate_target mismatch: Previous 816M Current 1044M RESOLVED Please check the parameter in the current CDB 1
06-MAR-22 06.41.07.742976000 PM PDB_ORCL2 Non-CDB to PDB ERROR 0 1 PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING Run noncdb_to_pdb.sql. 3
06-MAR-22 06.41.08.334394000 PM PDB_ORCL2 SQL Patch ERROR 0 1 '19.3.0.0.0 Release_Update 1904101227' is installed in the CDB but no release updates are installed in the PDB PENDING Call datapatch to install in the PDB or the CDB 3
|
上面代码框中出现ERROR的都要解决，否则pdb_orcl2永远处于restricted mode。在cdb2上查：
select con_id, name, open_mode, restricted from v$pdbs;
-----------------------------------------------------------------------------
2 PDB$SEED READ ONLY NO
3 PDB_ORCL2 READ WRITE YES
|
1）处理Non-CDB to PDB的ERROR：
| 06-MAR-22 06.41.07.742976000 PM PDB_ORCL2 Non-CDB to PDB ERROR 0 1 PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. PENDING Run noncdb_to_pdb.sql. 3 |
1.1）查看pdb_orcl2有没有临时文件：
select * from v$tempfile;
----------------------------
找不到
|
select tablespace_name, initial_extent, next_extent, status from dba_tablespaces;
------------------------------------------------------------------------
SYSTEM 65536 ONLINE
SYSAUX 65536 ONLINE
UNDOTBS1 65536 ONLINE
TEMP 1048576 1048576 ONLINE
USERS 65536 ONLINE
|
1.2）在pdb_orcl2中，向其temp表空间添加临时文件：
alter tablespace temp add tempfile size 30M autoextend on;
|
1.3）在pdb_orcl2中，执行：
[oracle@classroom ~]$ tnsping pdb_orcl2
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 03-MAR-2022 10:44:39
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = classroom.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb_ORCL2)))
OK (0 msec)
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 10:44:44 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn sys/cloud_4U@pdb_orcl2 as sysdba
????
SQL> alter session set nls_language=english;
Session altered.
SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/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> -- save settings
SQL> STORE SET ncdb2pdb.settings.sql REPLACE
Wrote file ncdb2pdb.settings.sql
SQL>
SQL> SET TIME ON
10:48:23 SQL> SET TIMING ON
10:48:23 SQL>
10:48:23 SQL> WHENEVER SQLERROR EXIT;
10:48:23 SQL>
10:48:23 SQL> DOC
10:48:23 DOC>#######################################################################
10:48:23 DOC>#######################################################################
10:48:23 DOC> The following statement will cause an "ORA-01403: no data found"
10:48:23 DOC> error if we're not in a PDB.
10:48:23 DOC> This script is intended to be run right after plugin of a PDB,
10:48:23 DOC> while inside the PDB.
10:48:23 DOC>#######################################################################
10:48:23 DOC>#######################################################################
10:48:23 DOC>#
10:48:23 SQL>
10:48:23 SQL> VARIABLE cdbname VARCHAR2(128)
10:48:23 SQL> VARIABLE pdbname VARCHAR2(128)
10:48:23 SQL> BEGIN
10:48:23 2 SELECT sys_context('USERENV', 'CDB_NAME')
10:48:23 3 INTO :cdbname
10:48:23 4 FROM dual
10:48:23 5 WHERE sys_context('USERENV', 'CDB_NAME') is not null;
10:48:23 6 SELECT sys_context('USERENV', 'CON_NAME')
10:48:23 7 INTO :pdbname
10:48:23 8 FROM dual
10:48:23 9 WHERE sys_context('USERENV', 'CON_NAME') <> 'CDB$ROOT';
10:48:23 10 END;
10:48:23 11 /
PL/SQL procedure successfully completed.
......
|
上述脚本执行完毕即证明该ERROR处理完成，完成了pdb_orcl2字典格式从non$cdb形式向pdb形式的转换。 2）处理著名的SQL Patch1904101227的ERROR：
| 06-MAR-22
06.41.08.334394000 PM PDB_ORCL2 SQL Patch ERROR 0 1
'19.3.0.0.0 Release_Update 1904101227' is installed in the CDB but no
release updates are installed in the PDB PENDING Call datapatch to
install in the PDB or the CDB 3 |
出现上面的原因是因为cdb2上打完了SQL Patch 1904101227补丁，而pdb_orcl2上没有打，需要补打这个补丁。打这个补丁时，需要pdb_orcl2上有特定版本的JVM。 2.1）在pdb_orcl2应用SQL Patch 1904101227补丁： [oracle@classroom dbhome_1]$ . oraenv
ORACLE_SID = [cdb2] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@classroom dbhome_1]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@classroom dbhome_1]$ cd OPatch/
[oracle@classroom OPatch]$ ./datapatch -pdbs=pdb_orcl2
SQL Patching tool version 19.3.0.0.0 Production on Sun Mar 6 22:25:37 2022
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_189182_2022_03_06_22_25_37/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.3.0.0.0 Release_Update 190410122720: Installed
PDB PDB_ORCL2:
No release update patches installed
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: PDB_ORCL2
No interim patches need to be rolled back
Patch 29517242 (Database Release Update : 19.3.0.0.190416 (29517242)):
Apply from 19.1.0.0.0 Feature Release to 19.3.0.0.0 Release_Update 190410122720
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 29517242 apply (pdb PDB_ORCL2): WITH ERRORS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB2_PDB_ORCL2_2022Mar06_22_26_13.log (errors)
-> Error at line 75185: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75195: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75205: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75219: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75229: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75242: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75255: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75265: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75275: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75285: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75295: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75309: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75327: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75342: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75352: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75364: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75377: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75389: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
-> Error at line 75431: script md/admin/catmdloc.sql
- ORA-29548: Java system class reported: release of Java system classes in the
- database (19.3.0.0.0 1.8) does not match that of the oracle executable
- (19.0.0.0.0 1.8)
- ORA-06512: at line 24
- ORA-06512: at "SYS.DBMS_JAVA", line 705
- ORA-06512: at line 2
- ORA-06512: at line 4
Please refer to MOS Note 1609718.1 and/or the invocation log
/u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_189182_2022_03_06_22_25_37/sqlpatch_invocation.log
for information on how to resolve the above errors.
SQL Patching tool complete on Sun Mar 6 22:28:30 2022
|
以上的报错信息说明插入的数据库pdb_orcl2上的jvm（从non$cdb中带来的）与当前ORACLE_HOME中的jvm版本有差异，前者是19.0.0.0.0.1.8，后者是19.3.0.0.0.1.8。 2.2）在 pdb_orcl2中查询其java选项：
select dbms_java.get_jdk_version() from dual;
------------------------------------------------------------------------------
ORA-29548:
Java system class reported: release of Java system classes in the
database (19.3.0.0.0 1.8) does not match that of the oracle executable
(19.0.0.0.0 1.8)
29548. 00000 - "Java system class reported: %s"
*Cause: A command that uses a Java system class was aborted due to an
error reported by the Java system class.
*Action: Correct the error that was reported. |
2.3）在cdb2中查询其java选项：
select dbms_java.get_jdk_version() from dual;
------------------------------------------------------------------------------
1.8.0_201
|
2.4）解决办法： 以上的两个查询说明在pdb_orcl2上jvm的版本不对。处理办法是在pdb_orcl2升级java虚拟机：
[oracle@classroom ~]$ tnsping pdb_orcl2
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 03-MAR-2022 16:11:49
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.3.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting
to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
classroom.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME = pdb_ORCL2)))
OK (10 msec)
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 16:11:53 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn sys/cloud_4U@pdb_orcl2 as sysdba
????
SQL> alter session set nls_language=english;
Session altered.
SQL> @?/javavm/install/update_javavm_db.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>
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL>
SQL> -- If Java is installed, do CJS.
SQL>
SQL> -- If CJS can deal with the SROs inconsistent with the new JDK,
SQL> -- the drop_sros() call here can be removed.
SQL> call initjvmaux.drop_sros();
Call completed.
SQL>
SQL> create or replace java system;
2 /
Java created.
SQL>
SQL> update dependency$
2 set p_timestamp=(select stime from obj$ where obj#=p_obj#)
3 where (select stime from obj$ where obj#=p_obj#)!=p_timestamp and
4 (select type# from obj$ where obj#=p_obj#)=29 and
5 (select owner# from obj$ where obj#=p_obj#)=0;
0 rows updated.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> alter session set "_ORACLE_SCRIPT"=false;
Session altered.
|
再次在pdb_orcl2中查询其java选项：
select dbms_java.get_jdk_version() from dual;
------------------------------------------------------------------------------
1.8.0_201
|
2.5）在pdb_orcl2再次应用SQL Patch 1904101227补丁：[oracle@classroom dbhome_1]$ . oraenv
ORACLE_SID = [cdb2] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@classroom dbhome_1]$ pwd
/u01/app/oracle/product/19.3.0/dbhome_1
[oracle@classroom dbhome_1]$ cd OPatch/
[oracle@classroom OPatch]$ ./datapatch -pdbs=pdb_orcl2
SQL Patching tool version 19.3.0.0.0 Production on Mon Mar 7 16:33:11 2022
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_779795_2022_03_07_16_33_11/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.3.0.0.0 Release_Update 190410122720: Installed
PDB PDB_ORCL2:
Applied 19.3.0.0.0 Release_Update 190410122720 with errors on 06-MAR-22 10.28.24.023776 PM
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: PDB_ORCL2
No interim patches need to be rolled back
Patch 29517242 (Database Release Update : 19.3.0.0.190416 (29517242)):
Apply from 19.1.0.0.0 Feature Release to 19.3.0.0.0 Release_Update 190410122720
No interim patches need to be applied
Installing patches...
Patch installation complete. Total patches installed: 1
Validating logfiles...done
Patch 29517242 apply (pdb PDB_ORCL2): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB2_PDB_ORCL2_2022Mar07_16_33_44.log (no errors)
SQL Patching tool complete on Mon Mar 7 16:36:45 2022
|
SQL Patch成功应用。
3）通过结束pdb_orcl2的restricted mode，证明pdb_orcl2的所有问题都得到解决（pdb_orcl2可以与cdb2拥有不同的字符集）： 首先在cdb2上操作：
delete from pdb_plug_in_violations where name='PDB_ORCL2';
commit;
select * from pdb_plug_in_violations where name='PDB_ORCL2';
-----------------------------------------------------------------------------------------
找不到
|
然后在pdb_orcl2上操作：
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 4 11:19:15 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn sys/cloud_4U@pdb_orcl2 as sysdba
????
SQL> alter session set nls_language=english;
Session altered.
SQL> alter system disable restricted session;
System altered.
|
最后在cdb2上查看：
select * from pdb_plug_in_violations where name='PDB_ORCL2';
-----------------------------------------------------------------------------------------
找不到
select con_id, name, open_mode, restricted from v$pdbs;
-----------------------------------------------------------------------------
2 PDB$SEED READ ONLY NO
3 PDB_ORCL2 READ WRITE NO
|
至此，pdb_orcl2成功插入cdb2。
|
