真实环境下创建pdb、在两个cdb之间迁移pdb和创建proxy pdb的操作汇总系列4
Author: Bo Tang
1. 跨根容器操作之——热克隆PDB，之后源头数据库和目的地数据库之间也没有任何同步关系，各自独立运行：
在本实验中，将cdb1中的pdb1_1热克隆到cdb2中生成pdb2_1： 1）在cdb2中，创建指向cdb1中的pdb1_1的system用户的公共数据库链接： [oracle@classroom ~]$ tnsping pdb1_1
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 = pdb1_1)))
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 public database link link_pdb1_1 connect to system identified by cloud_4U using 'pdb1_1';
Database link created.
SQL> select count(*) from dba_source@link_pdb1_1;
COUNT(*)
----------
292098
SQL> |
2）源端pdb1_1所在的根容器cdb1上，用于链接的公共用户system需要授权：
在源端根容器上需要一些特殊的权限（光光有dba不够），需要给create pluggable database的权限。请在cdb1根容器上操作： grant create pluggable database to system container=all; select * from cdb_sys_privs where grantee='SYSTEM' order by con_id; -------------------------------------------------------------------- SYSTEM CREATE TABLE NO YES NO 1
SYSTEM MANAGE ANY QUEUE YES YES NO 1
SYSTEM CREATE PLUGGABLE DATABASE NO YES NO 1
SYSTEM CREATE MATERIALIZED VIEW NO YES NO 1
SYSTEM UNLIMITED TABLESPACE NO YES NO 1
SYSTEM GLOBAL QUERY REWRITE NO YES NO 1
SYSTEM ENQUEUE ANY QUEUE YES YES NO 1
SYSTEM DEQUEUE ANY QUEUE YES YES NO 1
SYSTEM SELECT ANY TABLE NO YES NO 1
SYSTEM CREATE TABLE NO YES YES 3
SYSTEM MANAGE ANY QUEUE YES YES YES 3
SYSTEM UNLIMITED TABLESPACE NO YES YES 3
SYSTEM SELECT ANY TABLE NO YES YES 3
SYSTEM CREATE MATERIALIZED VIEW NO YES YES 3
SYSTEM DEQUEUE ANY QUEUE YES YES YES 3
SYSTEM ENQUEUE ANY QUEUE YES YES YES 3
SYSTEM GLOBAL QUERY REWRITE NO YES YES 3
SYSTEM CREATE PLUGGABLE DATABASE NO YES YES 3
SYSTEM CREATE TABLE NO YES YES 4
SYSTEM GLOBAL QUERY REWRITE NO YES YES 4
SYSTEM ENQUEUE ANY QUEUE YES YES YES 4
SYSTEM DEQUEUE ANY QUEUE YES YES YES 4
SYSTEM CREATE MATERIALIZED VIEW NO YES YES 4
SYSTEM SELECT ANY TABLE NO YES YES 4
SYSTEM UNLIMITED TABLESPACE NO YES YES 4
SYSTEM MANAGE ANY QUEUE YES YES YES 4
SYSTEM CREATE PLUGGABLE DATABASE NO YES YES 4
SYSTEM CREATE PLUGGABLE DATABASE NO NO NO 7
SYSTEM CREATE TABLE NO YES YES 7
SYSTEM GLOBAL QUERY REWRITE NO YES YES 7
SYSTEM ENQUEUE ANY QUEUE YES YES YES 7
SYSTEM CREATE MATERIALIZED VIEW NO YES YES 7
SYSTEM CREATE PLUGGABLE DATABASE NO YES YES 7
SYSTEM SELECT ANY TABLE NO YES YES 7
SYSTEM UNLIMITED TABLESPACE NO YES YES 7
SYSTEM MANAGE ANY QUEUE YES YES YES 7
SYSTEM DEQUEUE ANY QUEUE YES YES YES 7
|
3）让pdb1_1处于高负载插入状态下，同时进行pdb2_1的热克隆：SQL> select count(*) from hr.tbig;
COUNT(*)
--------------- 1644384
注：在执行下面操作时（commit前），同时在cdb2上操作（见下个代码框提示）！
SQL> conn hr/cloud_4U@pdb1_1
Connected.
SQL> insert into tbig select * from tbig;
1644384 rows created.
SQL> insert into tbig select * from tbig;
3288768 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 pdb2_1 from pdb1_1@link_pdb1_1 create pluggable database pdb2_1 from pdb1_1@link_pdb1_1
*
ERROR at line 1:
ORA-65345: cannot refresh pluggable database
SQL> alter pluggable database pdb2_1 open;
ORA-65104: operation not allowed on an inactive pluggable database
SQL>drop pluggable database pdb2_1 including datafiles;
pluggable DATABASE dropped.
SQL> create pluggable database pdb2_1 from pdb1_1@link_pdb1_1 refresh mode none;
Pluggable database created.
SQL> select pdb_id,pdb_name,status, refresh_mode,refresh_interval from cdb_pdbs;
3 PDB_ORCL2 NORMAL NONE
2 PDB$SEED NORMAL NONE
5 PDB2_1 NORMAL NONE
|
pdb2_1已经创建出来。由于如《汇总操作1》中描述的一样，cdb1/cdb2都处于非归档模式，所以pdb2_1无法被两个根容器之间传递的归档日志刷新同步（pdb级别的dataguard）。
[oracle@classroom admin]$ tnsping pdb2_1
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 = pdb2_1)))
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@pdb2_1
ERROR:
ORA-01109: database not open
SQL> conn sys/cloud_4U@pdb2_1 as sysdba
Connected.
SQL> alter database open;
Database altered.
SQL> conn hr/cloud_4U@pdb2_1
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
要处理《汇总操作2》的JAVA和JVM的ERROR：注意@/u01/app/oracle/admin/test3/scripts/JServer.sql脚本在pdb2_1上执行，pdb2_1不需要执行@?/javavm/install/update_javavm_db.sql 要处理《汇总操作3》的著名的SQL Patch1904101227的ERROR： [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=pdb2_1 之后在cdb2上alter pluggable database pdb2_1 close immediate; 再alter pluggable database pdb2_1 open;
......
请参考对应的网页，此处略。
SQL> conn hr/cloud_4U@pdb2_1
Connected.
SQL> select count(*) from hr.tbig;
COUNT(*)
---------------
1644384 |
说明热克隆操作成功完成，并且维持了读一致性，而且之后源头数据库和目的地数据库之间也没有任何同步关系，各自独立运行。
2. 跨根容器操作之——热克隆PDB，之后源头数据库和目的地数据库之间也没有任何同步关系，各自独立运行（使用dbca方法）： 在本实验中，将cdb1中的pdb1_2热克隆到cdb2中生成pdb2_2：
1）在cdb1中，创建c##remote这个common user，并授予create session权限和create pluggable database权限： [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 Fri Mar 11 16:08:03 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> create user c##remote identified by cloud_4U;
User created.
SQL> grant create session to c##remote container=all;
Grant succeeded.
SQL> grant create pluggable database to c##remote container=all;
Grant succeeded.
|
2）需要将cdb1运行于归档模式： [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 Fri Mar 11 15:52:27 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4294963960 bytes
Fixed Size 9143032 bytes
Variable Size 2348810240 bytes
Database Buffers 1929379840 bytes
Redo Buffers 7630848 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter pluggable database all open;
Pluggable database altered.
|
3）需要将cdb2运行于归档模式： [oracle@classroom ~]$ . oraenv
ORACLE_SID = [cdb2] ? 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 Fri Mar 11 15:52:27 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4294963960 bytes
Fixed Size 9143032 bytes
Variable Size 2348810240 bytes
Database Buffers 1929379840 bytes
Redo Buffers 7630848 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter pluggable database all open;
Pluggable database altered.
|
4）让pdb1_2处于高负载插入状态下，同时进行pdb2_2的热克隆：SQL> select count(*) from hr.tbig;
COUNT(*)
--------------- 205548
注：在执行下面操作时（commit前），同时在cdb2上操作（见下个代码框提示）！
SQL> conn hr/cloud_4U@pdb1_2
Connected.
SQL> insert into tbig select * from tbig;
205548 rows created.
SQL> insert into tbig select * from tbig;
411096 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from tbig;
COUNT(*)
----------
822192
|
在上面代码框标注红字处，同时在cdb2上操作：
先查看dbca -createPluggableDatabase的帮助：[oracle@classroom ~]$ . oraenv
ORACLE_SID = [cdb2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@classroom ~]$ dbca -silent -createPluggableDatabase -createFromRemotePDB -remotePDBName pdb1_2 -remoteDBConnString cdb1 -sysDBAUserName sys -sysDBAPassword cloud_4U -remoteDBSYSDBAUserName sys -remoteDBSYSDBAUserPassword cloud_4U -dbLinkUsername c##remote -dbLinkUserPassword cloud_4U -sourceDB cdb2 -pdbName pdb2_2
Prepare for db operation
50% complete
Create pluggable database using remote clone operation
100% complete
Pluggable database "PDB2_2" plugged successfully.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb2/PDB2_2/cdb2.log" for further details.
SQL> select pdb_id,pdb_name,status, refresh_mode,refresh_interval from cdb_pdbs;
3 PDB_ORCL2 NORMAL NONE
2 PDB$SEED NORMAL NONE
4 PDB2_2 NORMAL NONE
5 PDB2_1 NORMAL NONE
|
以上的dbca命令中请特别注意“ -remoteDBConnString cdb1”的tns串大小写，这是一个bug：tns串是大写就在这里大写；小写就在这里小写。pdb2_2已经创建出来。 在cdb2上查pdb_plug_in_violations：
select * from pdb_plug_in_violations
where name='PDB2_2';
-------------------------------------------------------------------------------
11-MAR-22 04.29.28.161471000 PM PDB2_2 Parameter WARNING 0 1 CDB parameter processes mismatch: Previous 300 Current 640 PENDING Please check the parameter in the current CDB 1
11-MAR-22 04.29.28.161958000 PM PDB2_2 Parameter WARNING 0 2 CDB parameter memory_target mismatch: Previous 4G Current 0 PENDING Please check the parameter in the current CDB 1
11-MAR-22 04.29.28.813406000 PM PDB2_2 OPTION WARNING 0 1 Database option APS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.28.815014000 PM PDB2_2 OPTION WARNING 0 2 Database option CATJAVA mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.28.816341000 PM PDB2_2 OPTION WARNING 0 3 Database option CONTEXT mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.28.817628000 PM PDB2_2 OPTION WARNING 0 4 Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.28.818822000 PM PDB2_2 OPTION WARNING 0 5 Database option JAVAVM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.28.820060000 PM PDB2_2 OPTION WARNING 0 6 Database option OLS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.28.821256000 PM PDB2_2 OPTION WARNING 0 7 Database option ORDIM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.28.822477000 PM PDB2_2 OPTION WARNING 0 8 Database option SDO mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.28.823673000 PM PDB2_2 OPTION WARNING 0 9 Database option XML mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.28.824851000 PM PDB2_2 OPTION WARNING 0 10 Database option XOQ mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING Fix the database option in the PDB or the CDB 4
11-MAR-22 04.29.29.163081000 PM PDB2_2 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 4
|
上面代码框中出现ERROR的都要解决，否则pdb2_2永远处于restricted mode。在cdb2上查：
select con_id, name, open_mode, restricted from v$pdbs;
-----------------------------------------------------------------------------
2 PDB$SEED READ ONLY NO
3 PDB_ORCL2 READ WRITE NO
4 PDB2_2 READ WRITE YES
5 PDB2_1 READ WRITE NO
|
要处理《汇总操作2》的JAVA和JVM的ERROR：注意@/u01/app/oracle/admin/test3/scripts/JServer.sql脚本在pdb2_2上执行，pdb2_2不需要执行@?/javavm/install/update_javavm_db.sql 要处理《汇总操作3》的著名的SQL Patch1904101227的ERROR： [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=pdb2_2 之后在cdb2上alter pluggable database pdb2_2 close immediate; 再alter pluggable database pdb2_2 open;
......
请参考对应的网页，此处略。
SQL> conn hr/cloud_4U@pdb2_2
Connected.
SQL> select count(*) from hr.tbig;
COUNT(*)
---------------
205548 |
说明热克隆操作成功完成，并且维持了读一致性，而且之后源头数据库和目的地数据库之间也没有任何同步关系，各自独立运行。
3. 跨根容器操作之——热克隆PDB，而且之后源头数据库和目的地数据库保持PDB级别Dataguard同步关系：
在本实验中，将cdb1中的pdb1_1热克隆到cdb2中生成pdb2_3，并且保持PDB级别Dataguard同步关系： 1）为了保证同步，要对源头cdb1执行datapatch，cdb1的pdb1_1和pdb1_2上著名的SQL Patch 1904101227（前面的实验可以看出）要升级；如果不升级，目的地的pdb2_3等为了同步只能只读打开，就没有机会打这个补丁了： [oracle@classroom OPatch]$ ./datapatch -db=cdb1
SQL Patching tool version 19.3.0.0.0 Production on Fri Mar 11 22:22:04 2022
Copyright (c) 2012, 2019, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_4109257_2022_03_11_22_22_04/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 CDB$ROOT:
No release update patches installed
PDB PDB$SEED:
No release update patches installed
PDB PDB1_1:
No release update patches installed
PDB PDB1_2:
No release update patches installed
PDB PDB_CDB1_ORCL2:
No release update patches installed
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED PDB1_1 PDB1_2
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
For the following PDBs: PDB_CDB1_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: 5
Validating logfiles...done
Patch 29517242 apply (pdb CDB$ROOT): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB1_CDBROOT_2022Mar11_22_23_17.log (no errors)
Patch 29517242 apply (pdb PDB$SEED): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB1_PDBSEED_2022Mar11_22_24_58.log (no errors)
Patch 29517242 apply (pdb PDB1_1): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB1_PDB1_1_2022Mar11_22_24_57.log (no errors)
Patch 29517242 apply (pdb PDB1_2): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB1_PDB1_2_2022Mar11_22_24_57.log (no errors)
Patch 29517242 apply (pdb PDB_CDB1_ORCL2): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29517242/22862832/29517242_apply_CDB1_PDB_CDB1_ORCL2_2022Mar11_22_26_11.log (no errors)
SQL Patching tool complete on Fri Mar 11 22:33:09 2022
|
2）在cdb2中，创建指向cdb1中的pdb1_1的system用户的公共数据库链接（本文前面已经做过了）： [oracle@classroom ~]$ tnsping pdb1_1
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 = pdb1_1)))
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 public database link link_pdb1_1 connect to system identified by cloud_4U using 'pdb1_1';
Database link created.
SQL> select count(*) from dba_source@link_pdb1_1;
COUNT(*)
----------
292098
SQL> |
3）源端pdb1_1所在的根容器cdb1上，用于链接的公共用户system需要授权（本文前面已经做过了）： 在源端根容器上需要一些特殊的权限（光光有dba不够），需要给create pluggable database的权限。请在cdb1根容器上操作： grant create pluggable database to system container=all; select * from cdb_sys_privs where grantee='SYSTEM' order by con_id; -------------------------------------------------------------------- SYSTEM CREATE TABLE NO YES NO 1
SYSTEM MANAGE ANY QUEUE YES YES NO 1
SYSTEM CREATE PLUGGABLE DATABASE NO YES NO 1
SYSTEM CREATE MATERIALIZED VIEW NO YES NO 1
SYSTEM UNLIMITED TABLESPACE NO YES NO 1
SYSTEM GLOBAL QUERY REWRITE NO YES NO 1
SYSTEM ENQUEUE ANY QUEUE YES YES NO 1
SYSTEM DEQUEUE ANY QUEUE YES YES NO 1
SYSTEM SELECT ANY TABLE NO YES NO 1
SYSTEM CREATE TABLE NO YES YES 3
SYSTEM MANAGE ANY QUEUE YES YES YES 3
SYSTEM UNLIMITED TABLESPACE NO YES YES 3
SYSTEM SELECT ANY TABLE NO YES YES 3
SYSTEM CREATE MATERIALIZED VIEW NO YES YES 3
SYSTEM DEQUEUE ANY QUEUE YES YES YES 3
SYSTEM ENQUEUE ANY QUEUE YES YES YES 3
SYSTEM GLOBAL QUERY REWRITE NO YES YES 3
SYSTEM CREATE PLUGGABLE DATABASE NO YES YES 3
SYSTEM CREATE TABLE NO YES YES 4
SYSTEM GLOBAL QUERY REWRITE NO YES YES 4
SYSTEM ENQUEUE ANY QUEUE YES YES YES 4
SYSTEM DEQUEUE ANY QUEUE YES YES YES 4
SYSTEM CREATE MATERIALIZED VIEW NO YES YES 4
SYSTEM SELECT ANY TABLE NO YES YES 4
SYSTEM UNLIMITED TABLESPACE NO YES YES 4
SYSTEM MANAGE ANY QUEUE YES YES YES 4
SYSTEM CREATE PLUGGABLE DATABASE NO YES YES 4
SYSTEM CREATE PLUGGABLE DATABASE NO NO NO 7
SYSTEM CREATE TABLE NO YES YES 7
SYSTEM GLOBAL QUERY REWRITE NO YES YES 7
SYSTEM ENQUEUE ANY QUEUE YES YES YES 7
SYSTEM CREATE MATERIALIZED VIEW NO YES YES 7
SYSTEM CREATE PLUGGABLE DATABASE NO YES YES 7
SYSTEM SELECT ANY TABLE NO YES YES 7
SYSTEM UNLIMITED TABLESPACE NO YES YES 7
SYSTEM MANAGE ANY QUEUE YES YES YES 7
SYSTEM DEQUEUE ANY QUEUE YES YES YES 7
|
4） 让pdb1_1处于高负载插入状态下，同时进行pdb2_3的热克隆：SQL> select count(*) from hr.tbig;
COUNT(*)
--------------- 6577536
注：在执行下面操作时（commit前），同时在cdb2上操作（见下个代码框提示）！
SQL> conn hr/cloud_4U@pdb1_1
Connected.
SQL> insert into tbig select * from tbig;
6577536 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from tbig;
COUNT(*)
----------
13155072 |
在上面代码框标注红字处，同时在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 pdb2_3 from pdb1_1@link_pdb1_1 refresh mode manual;
Pluggable database created.
|
pdb2_3已经创建出来。由于本文前面的操作，cdb1/cdb2都处于归档模式，所以pdb2_3可以被两个根容器之间传递的归档日志刷新同步（pdb级别的dataguard）。
[oracle@classroom admin]$ tnsping pdb2_3
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 = pdb2_1)))
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@pdb2_3
ERROR:
ORA-01109: database not open
SQL> conn sys/cloud_4U@pdb2_3 as sysdba
Connected.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode
之后在cdb2上alter pluggable database pdb2_3 open read only;
SQL> conn hr/cloud_4U@pdb2_3
Connected.
SQL> select count(*) from hr.tbig;
COUNT(*)
---------------
6577536 |
5）可刷新性： 创建的pdb2_3由于处于刷新模式，所以只能打开成read only状态。但是处于read only状态时不能执行刷新，只有关闭pdb2_3才能进行刷新：
SQL> conn hr/cloud_4U@pdb2_3
ERROR:
ORA-01109: database not open
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb2_3 open read only;
Pluggable database altered.
SQL> conn hr/cloud_4U@pdb2_3
Connected.
SQL> select count(*) from tbig;
COUNT(*)
----------
6577536 |
在cdb2上操作：
SQL> conn / as sysdba
Connected.
SQL> select pdb_id,pdb_name,status, refresh_mode,refresh_interval from cdb_pdbs; ------------------------------------------------------------------ 4 PDB2_2 NORMAL NONE
2 PDB$SEED NORMAL NONE
3 PDB_ORCL2 NORMAL NONE
5 PDB2_1 NORMAL NONE
7 PDB2_3 REFRESHING MANUAL
SQL> alter pluggable database pdb2_3 refresh;
alter pluggable database pdb2_3 refresh
*
ERROR at line 1:
ORA-65025: Pluggable database PDB2_3 is not closed on all instances.
SQL> alter pluggable database pdb2_3 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb2_3 refresh;
Pluggable database altered.
SQL> alter pluggable database pdb2_3 open read only;
Pluggable database altered.
|
在pdb2_3上验证：
SQL> conn hr/cloud_4U@pdb2_3
Connected.
SQL> select count(*) from tbig;
COUNT(*)
----------
13155072
|
说明刷新成功。 6）将其改成每分钟自动刷新，查看可刷新性： 在cdb2上操作：
SQL> alter pluggable database pdb2_3 refresh mode every 1 minutes; pluggable DATABASE altered.
SQL> select pdb_id,pdb_name,status, refresh_mode,refresh_interval from cdb_pdbs; ------------------------------------------------------------------ 4 PDB2_2 NORMAL NONE
2 PDB$SEED NORMAL NONE
3 PDB_ORCL2 NORMAL NONE
5 PDB2_1 NORMAL NONE
7 PDB2_3 REFRESHING AUTO 1 |
在源头pdb1_1上做变化：
SQL> conn hr/cloud_4U@pdb1_1
Connected.
SQL> truncate table tbig;
Table truncated.
SQL> select count(*) from tbig;
COUNT(*)
----------
0 |
在pdb2_3上操作：
SQL> conn hr/cloud_4U@pdb2_3 Connected. SQL> ! sleep 60
SQL> select count(*) from tbig;
COUNT(*)
----------
13155072 |
等了1分钟未刷新是因为插件数据库pdb2_3处于只读状态，插件数据库pdb2_3必需处于关闭状态才能执行手动和自动刷新。
在cdb2上操作：
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb2_3 close immediate;
Pluggable database altered.
SQL> ! sleep 60
SQL> alter pluggable database pdb2_3 open read only;
Pluggable database altered.
|
在pdb2_3上验证：
SQL> conn hr/cloud_4U@pdb2_3
Connected.
SQL> select count(*) from tbig;
COUNT(*)
----------
0 |
说明刷新成功。 7）不可逆地转成不可刷新，读写打开， 查看可刷新性： 在cdb2上操作：
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb2_3 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb2_3 refresh mode none;
Pluggable database altered.
SQL> alter pluggable database pdb2_3 open;
Pluggable database altered.
select pdb_id,pdb_name,status, refresh_mode,refresh_interval from cdb_pdbs; ------------------------------------------------------------------------------------------------ 4 PDB2_2 NORMAL NONE
2 PDB$SEED NORMAL NONE
3 PDB_ORCL2 NORMAL NONE
5 PDB2_1 NORMAL NONE
7 PDB2_3 NORMAL NONE |
在cdb2上操作，无法再改回manual或automatic刷新：
SQL> alter pluggable database pdb2_3 refresh mode manual;
alter pluggable database pdb2_3 refresh mode manual
*
ERROR at line 1:
ORA-65261: pluggable database PDB2_3 not enabled for refresh
SQL> alter pluggable database pdb2_3 refresh mode every 1 minutes;
alter pluggable database pdb2_3 refresh mode every 1 minutes
*
ERROR at line 1:
ORA-65261: pluggable database PDB2_3 not enabled for refresh |
|
