真实环境下创建pdb、在两个cdb之间迁移pdb和创建proxy pdb的操作汇总系列2
Author: Bo Tang
1. 在cdb1中克隆pdb1_1创建pdb1_2，之后源头数据库和目的地数据库之间也没有任何同步关系，各自独立运行：
在cdb1的pdb1_1中： [oracle@classroom ~]$ . oraenv
ORACLE_SID = [cdb1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@classroom ~]$ tnsping pdb1_1
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-MAR-2022 16:02:56
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 = pdb1_1)))
OK (10 msec)
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 1 16:08:24 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn sys/cloud_4U@pdb1_1 as sysdba
Connected.
SQL> create user hr identified by cloud_4U account unlock;
User created.
SQL> alter user hr default tablespace users quota unlimited on users;
User altered.
SQL> grant resource to hr;
Grant succeeded.
SQL> grant create session to hr;
Grant succeeded.
|
让pdb1_1处于高负载插入状态下，同时进行pdb1_2的热克隆： 在 pdb1_1：
SQL> create table hr.tbig as select * from dba_source;
Table created.
SQL> select count(*) from hr.tbig;
COUNT(*)
----------
205548
注：在执行下面操作时（commit前），同时在cdb1上操作（见下个代码框提示）！
SQL> conn hr/cloud_4U@pdb1_1
Connected.
SQL> insert into tbig select * from tbig;
205548 rows created.
SQL> insert into tbig select * from tbig;
411096 rows created.
SQL> insert into tbig select * from tbig;
822192 rows created.
SQL> commit;
Commit complete.
|
在上面代码框标注红字处，同时在cdb1上操作：
[oracle@classroom ~]$ . oraenv
ORACLE_SID = [cdb1] ?
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 pdb1_2 from pdb1_1;
Pluggable database created.
|
[oracle@classroom admin]$ tnsping pdb1_2
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 = pdb1_2)))
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@pdb1_2
ERROR:
ORA-01109: database not open
SQL> conn sys/cloud_4U@pdb1_2 as sysdba
Connected.
SQL> alter database open;
Database altered.
SQL> conn hr/cloud_4U@pdb1_2
Connected.
SQL> select count(*) from hr.tbig;
COUNT(*)
----------
205548
|
说明热克隆操作成功完成，并且维持了读一致性，之后源头数据库和目的地数据库之间也没有任何同步关系，各自独立运行。
2. 把non$cdb以xml方式插入cdb1中，之后源头数据库和目的地数据库之间也没有任何同步关系，各自独立运行：
1） 准备好一个non$cdb，其数据库名叫做orcl2，实际上它是一个从windows上跨平台迁移得到的19.3.0版本的数据库，其基本情况如下（orcl2的sys和system的密码都是javamysql_4U，与cdb1的sys和system的密码不同，cdb1的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）在orcl2处于read only的状态下，描述其dbms_pdb的xml文件： [oracle@classroom admin]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 2 15:14:02 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
????
SQL> alter session set nls_language=english;
Session altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> exec dbms_pdb.describe('/home/oracle/orcl2.xml');
PL/SQL procedure successfully completed.
|
注：如果orcl2处于read write状态，下面代码框的create pluggable database语句会报告如下错误：
[oracle@classroom admin]$ . oraenv
ORACLE_SID = [orcl2] ? cdb1
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 Wed Mar 2 16:16:22 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 using '/home/oracle/orcl2.xml';
create pluggable database pdb_orcl2 using '/home/oracle/orcl2.xml'
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [ktcpoptx:!cmt top lvl], [], [], [],
[], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 1053038
Session ID: 3 Serial number: 1087
|
3）在cdb1中，使用上面代码框中生成的xml文件创建插件数据库pdb_orcl2： [oracle@classroom admin]$ . oraenv
ORACLE_SID = [orcl2] ? cdb1
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 Wed Mar 2 16:16:22 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 using '/home/oracle/orcl2.xml';
Pluggable database created. SQL> alter pluggable database pdb_orcl2 open;
Warning: PDB altered with errors.
|
3. 真实环境下，处理pdb_orcl2的Warning：
在cdb1上查pdb_plug_in_violations：
select * from pdb_plug_in_violations
where name='PDB_ORCL2';
-------------------------------------------------------------------------------
02-MAR-22 04.45.10.765693000 PM PDB_ORCL2 Parameter WARNING 0 1 CDB parameter nls_language mismatch: Previous 'SIMPLIFIED CHINESE' Current 'AMERICAN' PENDING Please check the parameter in the current CDB 1
02-MAR-22 04.45.10.766159000 PM PDB_ORCL2 Parameter WARNING 0 2 CDB parameter nls_territory mismatch: Previous 'CHINA' Current 'AMERICA' PENDING Please check the parameter in the current CDB 1
02-MAR-22 04.45.10.766631000 PM PDB_ORCL2 Parameter WARNING 0 3 CDB parameter sga_target mismatch: Previous 2448M Current 0 PENDING Please check the parameter in the current CDB 1
02-MAR-22 04.45.10.767330000 PM PDB_ORCL2 Parameter WARNING 0 4 CDB parameter pga_aggregate_target mismatch: Previous 816M Current 0 PENDING Please check the parameter in the current CDB 1
02-MAR-22 04.45.11.362560000 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. 7
02-MAR-22 04.45.11.435493000 PM PDB_ORCL2 OPTION ERROR 0 1 Database option APS mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
02-MAR-22 04.45.11.436857000 PM PDB_ORCL2 OPTION ERROR 0 2 Database option CATJAVA mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
02-MAR-22 04.45.11.438033000 PM PDB_ORCL2 OPTION ERROR 0 3 Database option CONTEXT mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
02-MAR-22 04.45.11.439155000 PM PDB_ORCL2 OPTION ERROR 0 4 Database option DV mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
02-MAR-22 04.45.11.440307000 PM PDB_ORCL2 OPTION ERROR 0 5 Database option JAVAVM mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
02-MAR-22 04.45.11.441448000 PM PDB_ORCL2 OPTION ERROR 0 6 Database option OLS mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
02-MAR-22 04.45.11.442527000 PM PDB_ORCL2 OPTION ERROR 0 7 Database option ORDIM mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
02-MAR-22 04.45.11.443621000 PM PDB_ORCL2 OPTION ERROR 0 8 Database option SDO mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
02-MAR-22 04.45.11.444695000 PM PDB_ORCL2 OPTION ERROR 0 9 Database option XML mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
02-MAR-22 04.45.11.445778000 PM PDB_ORCL2 OPTION ERROR 0 10 Database option XOQ mismatch: PDB installed version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the database option in the PDB or the CDB 7
|
上面代码框中出现ERROR的都要解决，否则pdb_orcl2永远处于restricted mode。在cdb1上查：
select con_id, name, open_mode, restricted from v$pdbs;
-----------------------------------------------------------------------------
2 PDB$SEED READ ONLY NO
3 PDB1_1 READ WRITE NO
4 PDB1_2 READ WRITE NO
7 PDB_ORCL2 READ WRITE YES
|
1）处理Non-CDB
to PDB的ERROR：
| 02-MAR-22 04.45.11.362560000 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. 7 |
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）处理JAVA和JVM的ERROR：
02-MAR-22 04.45.11.436857000 PM PDB_ORCL2 OPTION
ERROR 0 2 Database option CATJAVA mismatch: PDB installed
version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the
database option in the PDB or the CDB 7
02-MAR-22 04.45.11.440307000 PM PDB_ORCL2 OPTION
ERROR 0 5 Database option JAVAVM mismatch: PDB installed
version 19.0.0.0.0. CDB installed version NULL. PENDING Fix the
database option in the PDB or the CDB 7
|
以上的报错信息说明插入的数据库pdb_orcl2上有java选项（从non$cdb中带来的），而cdb1中没有java选项（cdb1是用sql命令创建的，只是执行了catcdb.sql）。 2.1）在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.2）在cdb1中查询其java选项：
select dbms_java.get_jdk_version() from dual;
------------------------------------------------------------------------------
ORA-00904: "DBMS_JAVA"."GET_JDK_VERSION": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 15 Column: 8
|
2.3）解决办法：
以上的两个查询说明两个问题：a）在cdb1中根本找不到dbms_java这个包；b）在pdb_orcl2上jvm的版本不对。处理办法是在cdb1上执行： 先用dbca录制定制建库（比如test3）的脚本，选所有定制建多租户库的选项，脚本默认存放在/u01/app/oracle/admin/test3/scripts。此步骤会生成下面所有实验所需要的脚本，一定要注意。
[oracle@classroom ~]$ . oraenv
ORACLE_SID = [cdb1] ? cdb1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 11:32:53 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL>@/u01/app/oracle/admin/test3/scripts/JServer.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Enter value for syspassword: cloud_4U
Connected.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/test3/scripts/JServer.log append
SQL> host /u01/app/oracle/product/19.3.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/test3/scripts -v -b initjvm -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" /u01/app/oracle/product/19.3.0/dbhome_1/javavm/install/initjvm.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/test3/scripts/initjvm_catcon_1811223.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/initjvm*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/initjvm_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
catcon::catconInit2: finished constructing connect strings
catcon::catconInit2: start CDB-specific processing
catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.
catcon::catconInit2: DBMS version: 19.0.0.0.0.
catcon::catconInit2: started SQL*Plus processes.
catcon::catconInit2: initialization completed successfully (2022-03-03 15:34:56)
catcon::catconExec: start executing scripts/SQL statements
catcon::catconExec_int: finished examining scripts/SQL statements to be executed.
catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB
catcon::log_script_execution: executing "@/u01/app/oracle/product/19.3.0/dbhome_1/javavm/install/initjvm.sql" in container CDB$ROOT using process 0
......
|
在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
|
再次在cdb1中查询其java选项： select dbms_java.get_jdk_version() from dual;
------------------------------------------------------------------------------
1.8.0_201
|
cdb和pdb的jdk版本一致，问题得到解决。
3）处理APS等OPTION类的ERROR： 原因同上，插入的数据库pdb_orcl2上有各种选项（从non$cdb中带来的），而cdb1中没有这些选项（cdb1是用sql命令创建的，只是执行了catcdb.sql）。 3.1）在pdb_orcl2中查询这些选项：
select * from dba_registry order by comp_id;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
APS OLAP Analytic Workspace 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:23 SERVER SYS SYS APS_VALIDATE
CATALOG Oracle Database Catalog Views 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:18 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATJAVA Oracle Database Java Packages 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:23 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
CATPROC Oracle Database Packages and Types 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:18 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC APPQOSSYS,AUDSYS,DBSFWUSER,DBSNMP,DIP,GGSYS,GSMADMIN_INTERNAL,GSMCATUSER,GSMROOTUSER,GSMUSER,ORACLE_OCM,OUTLN,REMOTE_SCHEDULER_AGENT,SYS$UMF,SYSBACKUP,SYSDG,SYSKM...
CONTEXT Oracle Text 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:23 SERVER SYS CTXSYS VALIDATE_CONTEXT
DV Oracle Database Vault 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:25 SERVER SYS DVSYS VALIDATE_DV DVF
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 19.3.0.0.0 INVALID 03-MAR-2022 11:12:22 SERVER SYS SYS INITJVMAUX.VALIDATE_JAVAVM OJVMSYS
OLS Oracle Label Security 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:25 SERVER SYS LBACSYS VALIDATE_OLS
ORDIM Oracle Multimedia 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:23 SERVER SYS ORDSYS VALIDATE_ORDIM MDSYS,ORDDATA,ORDPLUGINS,SI_INFORMTN_SCHEMA
OWM Oracle Workspace Manager 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:22 SERVER SYS WMSYS VALIDATE_OWM
RAC Oracle Real Application Clusters 19.0.0.0.0 19.3.0.0.0 OPTION OFF 30-MAY-2019 05:19:54 SERVER SYS SYS DBMS_CLUSTDB.VALIDATE
SDO Spatial 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:25 SERVER SYS MDSYS VALIDATE_SDO MDDATA,MDSYS
XDB Oracle XML Database 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:20 SERVER SYS XDB DBMS_REGXDB.VALIDATEXDB ANONYMOUS,XS$NULL
XML Oracle XDK 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:23 SERVER SYS SYS XMLVALIDATE
XOQ Oracle OLAP API 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 11:12:25 SERVER SYS OLAPSYS XOQ_VALIDATE
|
3.2）在cdb1中查询这些选项： select * from dba_registry order by comp_id;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 19.3.0.0.0 VALID 28-FEB-2022 16:50:15 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATJAVA Oracle Database Java Packages 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 15:43:33 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
CATPROC Oracle Database Packages and Types 19.0.0.0.0 19.3.0.0.0 VALID 28-FEB-2022 16:50:15 SERVER SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC APPQOSSYS,AUDSYS,DBSFWUSER,DBSNMP,DIP,GGSYS,GSMADMIN_INTERNAL,GSMCATUSER,GSMROOTUSER,GSMUSER,ORACLE_OCM,OUTLN,REMOTE_SCHEDULER_AGENT,SYS$UMF,SYSBACKUP,SYSDG,SYSKM...
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 15:38:27 SERVER SYS SYS INITJVMAUX.VALIDATE_JAVAVM OJVMSYS
OWM Oracle Workspace Manager 19.0.0.0.0 19.3.0.0.0 VALID 28-FEB-2022 16:50:19 SERVER SYS WMSYS VALIDATE_OWM
RAC Oracle Real Application Clusters 19.0.0.0.0 19.3.0.0.0 OPTION OFF 28-FEB-2022 16:48:51 SERVER SYS SYS DBMS_CLUSTDB.VALIDATE
XDB Oracle XML Database 19.0.0.0.0 19.3.0.0.0 VALID 28-FEB-2022 16:50:16 SERVER SYS XDB DBMS_REGXDB.VALIDATEXDB ANONYMOUS,XS$NULL
XML Oracle XDK 19.0.0.0.0 19.3.0.0.0 VALID 03-MAR-2022 15:41:19 SERVER SYS SYS XMLVALIDATE
|
3.3）在cdb1上执行context脚本，处理CONTEXT相关的ERROR： [oracle@classroom ~]$ . oraenv
ORACLE_SID = [cdb1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 16:32:07 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/app/oracle/admin/test3/scripts/context.sql
Enter value for syspassword: clould_4U
Connected.
SQL> spool /u01/app/oracle/admin/test3/scripts/context.log append
SQL> host /u01/app/oracle/product/19.3.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/test3/scripts -v -b catctx -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" -a 1 /u01/app/oracle/product/19.3.0/dbhome_1/ctx/admin/catctx.sql 1Xbkfsdcdf1ggh_123 1SYSAUX 1TEMP 1LOCK;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/test3/scripts/catctx_catcon_1844266.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/catctx*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/catctx_*.lst] files for spool files, if any
......
|
3.4）在cdb1上执行ordinst和interMedia脚本，处理ORDIM相关的ERROR： [oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 16:50:30 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/app/oracle/admin/test3/scripts/ordinst.sql
Enter value for syspassword: cloud_4U
Connected.
SQL> spool /u01/app/oracle/admin/test3/scripts/ordinst.log append
SQL> host /u01/app/oracle/product/19.3.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/test3/scripts -v -b ordinst -U "SYS"/"&&sysPassword" -a 1 /u01/app/oracle/product/19.3.0/dbhome_1/ord/admin/ordinst.sql 1SYSAUX 1SYSAUX;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/test3/scripts/ordinst_catcon_1857516.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/ordinst*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/ordinst_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
......
|
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 16:59:27 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/app/oracle/admin/test3/scripts/interMedia.sql
Enter value for syspassword: clould_4U
Connected.
SQL> spool /u01/app/oracle/admin/test3/scripts/interMedia.log append
SQL> host /u01/app/oracle/product/19.3.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/test3/scripts -v -b iminst -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" /u01/app/oracle/product/19.3.0/dbhome_1/ord/im/admin/iminst.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/test3/scripts/iminst_catcon_1860141.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/iminst*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/iminst_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
catcon::catconInit2: finished constructing connect strings
catcon::catconInit2: start CDB-specific processing
......
|
3.5）在cdb1上执行cwmlite脚本，处理APS和XOQ这些在线分析类相关的ERROR： [oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 17:09:50 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/app/oracle/admin/test3/scripts/cwmlite.sql
SQL> spool /u01/app/oracle/admin/test3/scripts/cwmlite.log append
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Enter value for syspassword: clould_4U
Connected.
SQL> host /u01/app/oracle/product/19.3.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/test3/scripts -v -b olap -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" -a 1 /u01/app/oracle/product/19.3.0/dbhome_1/olap/admin/olap.sql 1SYSAUX 1TEMP;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/test3/scripts/olap_catcon_1867191.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/olap*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/olap_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
catcon::catconInit2: finished constructing connect strings
catcon::catconInit2: start CDB-specific processing
catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.
catcon::catconInit2: DBMS version: 19.0.0.0.0.
catcon::catconInit2: started SQL*Plus processes.
catcon::catconInit2: initialization completed successfully (2022-03-03 17:10:27)
......
|
[oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 17:13:55 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/app/oracle/admin/test3/scripts/spatial.sql
Enter value for syspassword: clould_4U
Connected.
SQL> spool /u01/app/oracle/admin/test3/scripts/spatial.log append
SQL> host /u01/app/oracle/product/19.3.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/test3/scripts -v -b mdinst -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" /u01/app/oracle/product/19.3.0/dbhome_1/md/admin/mdinst.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/test3/scripts/mdinst_catcon_1869004.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/mdinst*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/mdinst_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
catcon::catconInit2: finished constructing connect strings
catcon::catconInit2: start CDB-specific processing
catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.
......
|
3.7）在cdb1上执行labelSecurity脚本，处理OLS相关的ERROR： [oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 4 10:41:52 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/app/oracle/admin/test3/scripts/labelSecurity.sql
Enter value for syspassword: cloud_4U
Connected.
SQL> spool /u01/app/oracle/admin/test3/scripts/labelSecurity.log append
SQL> host /u01/app/oracle/product/19.3.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/test3/scripts -v -b catols -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catols.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/test3/scripts/catols_catcon_2436176.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/catols*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/catols_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
catcon::catconInit2: finished constructing connect strings
catcon::catconInit2: start CDB-specific processing
......
|
3.8）在cdb1上执行datavault脚本，处理DV相关的ERROR： [oracle@classroom ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 4 11:13:10 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @/u01/app/oracle/admin/test3/scripts/datavault.sql
Enter value for syspassword: cloud_4U
Connected.
SQL> spool /u01/app/oracle/admin/test3/scripts/datavault.log append
SQL> host /u01/app/oracle/product/19.3.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/test3/scripts -v -b catmac -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" -a 1 /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catmac.sql 1SYSAUX 1TEMP;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/test3/scripts/catmac_catcon_2453579.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/catmac*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/test3/scripts/catmac_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
catcon::catconInit2: finished constructing connect strings
catcon::catconInit2: start CDB-specific processing
catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.
......
|
4）通过结束pdb_orcl2的restricted mode，证明pdb_orcl2的所有问题都得到解决（pdb_orcl2可以与cdb1拥有不同的字符集）： 首先在cdb1上操作：
delete from pdb_plug_in_violations where name='PDB_ORCL2';
commit;
select * from pdb_plug_in_violations where name='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.
|
最后在cdb1上查看：
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 PDB1_1 READ WRITE NO
4 PDB1_2 READ WRITE NO
7 PDB_ORCL2 READ WRITE NO
|
至此，pdb_orcl2成功插入cdb1。
4. 更改插入的pdb_orcl2的名字：
pdb_orcl2必须处于restricted mode，在pdb_orcl2上操作：
SQL> alter database rename global_name to pdb_cdb1_orcl2;
alter database rename global_name to pdb_cdb1_orcl2
*
ERROR at line 1:
ORA-65045: pluggable database not in a restricted mode
SQL> alter system enable restricted session;
System altered.
SQL> alter database rename global_name to pdb_cdb1_orcl2;
Database altered.
SQL> alter system disable restricted session;
System altered.
|
在cdb1上查看：
select * from pdb_plug_in_violations where name='PDB_CDB1_ORCL2';
-----------------------------------------------------------------------------------------
找不到
select con_id, name, open_mode, restricted from v$pdbs;
-----------------------------------------------------------------------------
2 PDB$SEED READ ONLY NO
3 PDB1_1 READ WRITE NO
4 PDB1_2 READ WRITE NO
7 PDB_CDB1_ORCL2 READ WRITE NO
|
完成了插件数据库的名字更改工作。
|
