暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / CDB 和 PDB 的维护管理（一）

CDB 和 PDB 的维护管理（一）

原创 心在梦在²º²º 2022-09-05
3001

CDB 和 PDB 的维护管理（一）

[toc]
 
 Oracle 12C引入了CDB与PDB的新特性。CDB全称为Container Database，中文翻译为数据库容器，PDB全称为Pluggable Database，即可插拔数据库。在ORACLE 12C之前，实例与数据库是一对一或多对一关系（RAC）：即一个实例只能与一个数据库相关联，数据库可以被多个实例所加载。而实例与数据库不可能是一对多的关系。当进入ORACLE 12C后，实例与数据库可以是一对多的关系。如，在ORACLE 12C数据库引入的多租用户环境中，允许一个数据库容器（CDB）承载多个可插拔数据库（PDB）。

下面是官方文档关于CDB与PDB的关系图:
图片.png
 
 理解了CDB与PDB的关系后我们就知道，我们才能更好的理解PDB相关操作，从 Oracle Database 21c 开始，多租户容器数据库是唯一受支持的架构，不再支持Non-CDB架构。
 本篇整理了日常工作中常见CDB 和 PDB 的维护管理，包含创建、克隆、删除PDB等操作。
 

一、查看数据库是否为CDB架构

方法1：查询v$database

SYS@ORCLCDB> select name,name,cdb from v$database;

NAME               NAME               CDB
------------------ ------------------ ------
ORCLCDB            ORCLCDB            YES

方法2：show pdbs

SYS@ORCLCDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO

二、关闭和启动CDB&PDB

1. 关闭和启动cdb

– 连接到cdb$root中，然后执行启动和关闭命令。

-- 关闭：shutdown immediate
[oracle@ora19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 3 23:32:05 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 启动：startup
SQL> startup
ORACLE instance started.
Total System Global Area 1073739168 bytes
Fixed Size                  9143712 bytes
Variable Size             381681664 bytes
Database Buffers          679477248 bytes
Redo Buffers                3436544 bytes
Database mounted.
Database opened.

2. 关闭和启动pdb

-- 2.1 在cdb中执行启动和关闭pdb
## 启动
SQL> alter pluggable database pdb1 open; 
SQL> alter pluggable database pdb1 open read only; 
SQL> alter pluggable database all open;    -- 打开所有PDB

## 关闭
SQL> alter pluggable database pdb1 close;  -- 建议加上immediate，否则如果pdb中有用户连接的话，那么该语句会夯住 
SQL> alter pluggable database pdb1 close immediate;  
SQL> alter pluggable database all close immediate;  -- 关闭所有PDB

-- 2.2 在pdb里边执行启动和关闭pdb
SQL> alter session set container=pdb1; 
SQL> shutdown immediate; 
SQL> shutdown abort;
SQL> startup ; 

或者： 
SQL> alter pluggable database close immediate; 
SQL> alter pluggable database close abort;
SQL> alter pluggable database open;

-- 注意PDB关闭之后，状态为mount
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           MOUNTED

3. 让pdb随着cdb的启动而启动

1、让所有的pdb处于open write状态 
2、保存现有的状态：alter pluggable database all save state;
3、启动cdb后，所有的pdb都会自动启动到open write状态
4、如果要指定某一个PDB随着cdb的启动而启动，执行如下命令：
  alter pluggable database pdb1 save state;

-- pdb的启动状态保存在视图cdb_pdb_saved_states中，可以直接查询：
SQL> col con_name for a10
SQL> col instance_name for a20 
SQL> col state for a10
SQL> select con_name,instance_name,state from cdb_pdb_saved_states;
CON_NAME   INSTANCE_NAME        STATE
---------- -------------------- ----------
PDB1       ORCLCDB              OPEN

-- 如果不想pdb随着cdb的启动而启动，那么可以执行discard state，如下：
SQL> alter pluggable database all discard state;
SQL> alter pluggable database pdb1 discard state; 
SQL> select con_name,instance_name,state from cdb_pdb_saved_states;
no rows selected

三、连接CDB和PDB方式

1. 连接CDB

– 和存储非CDB架构一样，可以通过export ORACLE_SID 和 tns方式连接

方式1：export ORACLE_SID

[oracle@ora19c ~]$ export ORACLE_SID=ORCLCDB
[oracle@ora19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 23 18:42:50 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@ORCLCDB>

方式2：通过监听

[oracle@ora19c ~]$ sqlplus sys/oracle@ORCLCDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 23 18:37:50 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@ORCLCDB>

2. 连接PDB

– 每个PDB都有一个默认的服务名，且要保证唯一性，否则无法保证自己连接数据库的唯一性。

[oracle@ora19c ~]$ lsnrctl  status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 23-AUG-2022 18:48:09
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                23-AUG-2022 18:36:43
Uptime                    0 days 0 hr. 11 min. 25 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=ora19c)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCLCDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDBXDB" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "a8be8cc09f902cd2e0530d0011ac912e" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb1" has 1 instance(s).
  Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully

方式1：alter session set cintainer

SYS@ORCLCDB> alter session set container=ORCLPDB1;
Session altered.

SYS@ORCLCDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB1                       READ WRITE NO

方式2：通过监听

[oracle@ora19c ~]$ sqlplus sys/oracle@orclpdb1 as sysdba       
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 23 18:54:33 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SYS@orclpdb1> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB1                       READ WRITE NO

连接PDB中普通用户

[oracle@ora19c ~]$ sqlplus sxc/sxc@orclpdb1       
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 23 19:00:39 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Fri Jul 22 2022 20:32:18 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SXC@orclpdb1> 

-- 不支持在PDB中使用conn sxc/sxc方式连接到普通用户
SYS@ORCLCDB> alter session set container=ORCLPDB1;
Session altered.

SYS@ORCLCDB> conn sxc/sxc
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
@>

注意：不可以使用export ORACLE_SID=orclpdb1的方式连接pdb实例，因为不存在orclpdb1的SID, 对应的SID是ORCLCDB。

3. 从pdb切回cdb

方式1： alter session set cintainer

SYS@orclpdb1>  alter session set container=cdb$root;
Session altered.

SYS@orclpdb1> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO

方式2： 直接conn / as sysdba

SYS@ORCLCDB> alter session set container=ORCLPDB1;
Session altered.

SYS@ORCLCDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 ORCLPDB1                       READ WRITE NO
SYS@ORCLCDB> conn / as sysdba
Connected.
SYS@ORCLCDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO

注意：在PDB中执行conn / as sysdba后，会直接切换到CDB中，而不是PDB在的sys用户。

4. 18c、19c以上，设置ORACLE_PDB_SID

[oracle@rac1 ~]$ export ORACLE_PDB_SID=PDB1
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 14 15:09:15 2020
Version 19.8.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.8.0.0.0
SQL> show pdbs;
CON_ID   CON_NAME            OPEN MODE    RESTRICTED
---------- -------------------------------------------------
3         PDB1               READ WRITE   NO

四、创建和克隆PDB

– 我们可以通过DBCA图形界面创建PDB，这里我们就不演示了。下面我们演示用命令行的几种方法：

方法1： 从pdb$seed 创建PDB

– 1）创建pdb时使用CREATE_FILE_DEST这个参数，在创建pdb的时候即指定omf管理文件的位置。

SYS@ORCLCDB> create pluggable database PDB2 admin user root identified by root CREATE_FILE_DEST = '/opt/oracle/oradata/ORCLCDB';

Pluggable database created.

SYS@ORCLCDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
         4 PDB2                           MOUNTED
SYS@ORCLCDB> alter pluggable database PDB2 open;
Pluggable database altered.

SYS@ORCLCDB> alter session set container=PDB2;
Session altered.

SYS@ORCLCDB> select name from v$datafile;

NAME
-----------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/ORCLCDB/E6E8A47EF3EE177DE053020011AC3FE0/datafile/o1_mf_system_kj9kqhwg_.dbf
/opt/oracle/oradata/ORCLCDB/ORCLCDB/E6E8A47EF3EE177DE053020011AC3FE0/datafile/o1_mf_sysaux_kj9kqhxm_.dbf
/opt/oracle/oradata/ORCLCDB/ORCLCDB/E6E8A47EF3EE177DE053020011AC3FE0/datafile/o1_mf_undotbs1_kj9kqhxo_.dbf

– 2）创建pdb时使用file_name_convert 参数，转换路径，使数据文件名称整齐

SYS@ORCLCDB> create pluggable database pdb3 admin user root identified by root file_name_convert=('/opt/oracle/oradata/ORCLCDB/pdbseed/','/opt/oracle/oradata/ORCLCDB/pdb3/') ;

Pluggable database created.

SYS@ORCLCDB> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           MOUNTED
         
SYS@ORCLCDB> alter pluggable database PDB3 open;
Pluggable database altered.

SYS@ORCLCDB> alter session set container=PDB3;
Session altered.

SYS@ORCLCDB> select name from v$datafile;

NAME
-----------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/pdb3/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdb3/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/pdb3/undotbs01.dbf

-- 如果是ASM环境，直接写+NEWDATA转化就可以,但是数据文件是OMF格式
create pluggable database PDBLHR4 admin user lhr identified by lhr  ROLES = (dba) file_name_convert=('+DATA','+NEWDATA') ;

方法2：克隆本地PDB

– 在12c r2版本中引入了local undo mode, 源PDB在read/write 读写模式也可以 clone。local undo mode 也是oracle推崇的模式。

-- 1）使用FILE_NAME_CONVERT参数
SYS@ORCLCDB> create pluggable database pdb4  from pdb3;
create pluggable database pdb4  from pdb3
                                       *
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified

SYS@ORCLCDB> create pluggable database pdb4  from pdb3 file_name_convert=('/opt/oracle/oradata/ORCLCDB/pdb3/','/opt/oracle/oradata/ORCLCDB/pdb4/') ;

Pluggable database created.


SYS@ORCLCDB> show pdbs;

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        2 PDB$SEED                       READ ONLY  NO
        3 ORCLPDB1                       READ WRITE NO
        4 PDB2                           READ WRITE NO
        5 PDB3                           READ WRITE NO
        6 PDB4                           MOUNTED

SYS@ORCLCDB> alter session set container=PDB4;
Session altered.

SYS@ORCLCDB> alter pluggable database open;
Pluggable database altered.


-- 2）或者使用CREATE_FILE_DEST
SYS@ORCLCDB> !mkdir /opt/oracle/oradata/ORCLCDB/pdb5

SYS@ORCLCDB> alter system set db_create_file_dest='/opt/oracle/oradata/ORCLCDB/pdb5';
System altered.

SYS@ORCLCDB> create pluggable database pdb5  from pdb3;
Pluggable database created.

注意：

12C R1版本中 12.1.0.1 clone 一份PDB，用来克隆的pdb必须是以read only模式打开 , 在12.1.0.2 版本中，源PDB没有活动事务，那么可以在read write状态下clone，否则会报错:

ORA-65081: database or pluggable database is not open in read only mode

– 用来克隆的pdb必须是以read only模式打开，步骤如下：

SQL> alter session set container=pdb3;
SQL> alter pluggable database close immediate;
SQL> startup open read only;
SQL> conn / as sysdba
SQL> create pluggable database pdb6  from pdb3 file_name_convert=('/opt/oracle/oradata/ORCLCDB/pdb3/','/opt/oracle/oradata/ORCLCDB/pdb6/') ;

方法3：远程克隆PDB

使用dblink克隆远程Clone PDB时需要注意以下几点：

  • 如果PDB被Clone到的CDB的字符集不是AL32UTF8，那么源与目标字符集必须兼容。

  • 源端与目标端的字节顺序必须相同。

  • 连接的用户在CDB中必须拥有’CREATE PLUGGABLE DATABASE’的权限。

  • 如果源端CDB为shared undo，源PDB必须为READ-ONLY状态。在12C R1版本中undo只支持Global Shared Undo模式，12C R2引入了PDB Local UNDO模式，且是默认选项。

  • 源端可以是非归档模式。

  • 源PDB不可以是关闭状态。

    SYS@ORCLCDB> create pluggable database pdb19c from pdb12c@DB_PDB12C file_name_convert=('/u01/app/oracle/oradata/cdb1/pdb12c/','/opt/oracle/oradata/ORCLCDB/pdb19c/') ;
create pluggable database pdb19c from pdb12c@DB_PDB12C file_name_convert=('/u01/app/oracle/oradata/cdb1/pdb12c/','/opt/oracle/oradata/ORCLCDB/pdb19c/')
*
ERROR at line 1:
ORA-17627: ORA-01033: ORACLE initialization or shutdown in progress
ORA-17629: Cannot connect to the remote database server
实验1： 跨版本克隆 - 将 12c 中 pdb 克隆至19c 环境中
IP PDB NAME 字符集 国家字符集
源端 172.17.0.3 PDB12C AL32UTF8 AL16UTF16
目标端 172.17.0.2 PDB19C (预克隆名称) AL32UTF8 AL16UTF16

1.源端PDB创建用户

SQL> alter session set container=PDB12C;
Session altered.

SQL> create user clone identified by oracle;
User created.

SQL> grant create session,create pluggable database to clone;
Grant succeeded.

2.目标端的CDB中创建dblink

-- 连接到源端的pdb中
SQL> create public database link DB_PDB12C connect to clone identified by oracle using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB12C)))';

Database link created.

-- 检查dblink连通性
SQL> select * from dual@DB_PDB12C;

DU
--
X

3.在目标端的CDB中远程克隆PDB

SQL> create pluggable database pdb19c from pdb12c@DB_PDB12C file_name_convert=('/u01/app/oracle/oradata/cdb1/pdb12c/','/opt/oracle/oradata/ORCLCDB/pdb19c/') ;

Pluggable database created.

SQL> alter pluggable database pdb19c open;         --打开出现Warning

Warning: PDB altered with errors.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         5 PDB19C                         MIGRATE    YES    --PDB19C状态不对

4.查看创建过程中报错信息

-- 目标端通过 pdb_plug_in_violations 视图查看创建过程中报错信息
SQL> select name, MESSAGE,ACTION from pdb_plug_in_violations where status='PENDING' order by time;
------------------------------------------------------------------------------------------------------------
PDB$SEED                                                                                                             '19.3.0.0.0 Release_Update 1904101227' is installed in the CDB but no release updates are installed in the PDB
Call datapatch to install in the PDB or the CDB

PDB19C                                                                                                               Database option APS mismatch: PDB installed version 12.2.0.1.0. CDB installed version 19.0.0.0.0.
Fix the database option in the PDB or the CDB
.......
.......

PDB19C                                                                                                               PDB's version does not match CDB's version: PDB's version 12.2.0.1.0. CDB's version 19.0.0.0.0.
Either upgrade the PDB or reload the components in the PDB.

-- 提示部分option不一样，PDB版本和CDB不一致，需要升级PDB

5.升级PDB脚本 oracle用户下执行

--5.oracle用户下执行升级PDB脚本 (升级数据字典）
[oracle@ora19c ~]$ $ORACLE_HOME/bin/dbupgrade -c PDB19C
Argument list for [/opt/oracle/product/19c/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in                       c = PDB19C
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0

catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]


/opt/oracle/product/19c/dbhome_1/rdbms/admin/orahome = [/opt/oracle/product/19c/dbhome_1]
/opt/oracle/product/19c/dbhome_1/bin/orabasehome = [/opt/oracle/product/19c/dbhome_1]
catctlGetOraBaseLogDir = [/opt/oracle/product/19c/dbhome_1]

Analyzing file /opt/oracle/product/19c/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20220904220638]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20220904220638/catupgrd_catcon_2091.lst]
......
......
......
****************   Summary report   ****************
Serial   Phase #:104  [PDB19C] Files:1    Time: 7s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:105  [PDB19C] Files:1    Time: 6s
Serial   Phase #:106  [PDB19C] Files:1    Time: 5s
Phases [0-107]         End Time:[2022_09_04 22:43:20]
Container Lists Inclusion:[PDB19C] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 2124s [PDB19C]

 LOG FILES: (/opt/oracle/product/19c/dbhome_1/cfgtoollogs/ORCLCDB/upgrade20220904220654/catupgrdpdb19c*.log)

Upgrade Summary Report Located in:
/opt/oracle/product/19c/dbhome_1/cfgtoollogs/ORCLCDB/upgrade20220904220654/upg_summary.log

     Time: 2206s For PDB(s)

Grand Total Time: 2206s 

 LOG FILES: (/opt/oracle/product/19c/dbhome_1/cfgtoollogs/ORCLCDB/upgrade20220904220654/catupgrd*.log)


Grand Total Upgrade Time:    [0d:0h:36m:46s]

注意：

  1. 如果是相同版本迁移，则不需要执行dbupgrade升级命令。
  2. dbupgrade可以加上-n表示并行数
  3. 升级多个PDB时候，dbupgrade不能开多个窗口同时跑 ，可以一个一个执行升级，或者用引号同时升级多个PDB，例如同时升级pdb1和pdb2，执行如下脚本：
    $ORACLE_HOME/bin/dbupgrade -c ‘pdb1 pdb2’

6.再次打开PDB

SQL> alter pluggable database PDB19C open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         5 PDB19C                         READ WRITE NO     

--结论：PDB19C状态正常，成功将12c环境的pdb克隆至19c环境中。
实验2： 同版本，19c 中 pdb 克隆至19c 环境中，但是字符集设置不一样

​ 在oracle12.1版本中，同一CDB中的所有PDB使用的都是相同的字符集，并且Plug-in时PDB也要和目标CDB相同字符集或者是子集，否则plug-in时会失败在PDB_PLUG_IN_VIOLATIONS视图提示，这样影响了PDB的迁移灵活性。

​ 从 12.2 开始，同一个 cdb 中的各个 PDB 字符集可以不相同。但是我们发现在新创建PDB时并没有办法指定PDB 的字符集，新建的PDB都会继承CDB的字符集。更多信息参考MOS Note 1968706.1。

IP PDB NAME 字符集 国家字符集
源端 172.17.0.3 GBKPDB1 ZHS16GBK UTF8
目标端 172.17.0.2 clone_gbkpdb1 (预克隆名称) AL32UTF8 AL16UTF16
  1. 源端单独创建用户
SQL> alter session set container=GBKPDB1;
Session altered.

SQL> create user clone identified by oracle;
User created.

SQL> grant create session,create pluggable database to clone;
Grant succeeded.
  1. 目标端的CDB创建dblink
SQL> create public database link DB_GBKPDB1 connect to clone identified by oracle using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.0.2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = GBKPDB1)))';

Database link created.

SQL> select * from dual@DB_GBKPDB1;

D
-
X
  1. 在目标端的CDB中远程克隆PDB
SQL> create pluggable database clone_gbkpdb1 from gbkpdb1@DB_GBKPDB1 file_name_convert=('/opt/oracle/oradata/GBKDB/gbkpdb1/','/opt/oracle/oradata/ORCLCDB/clone_gbkpdb1/') ;

Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 clone_gbkpdb1                  MOUNTED
  1. 打开PDB
SQL> alter pluggable database clone_gbkpdb1 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 clone_gbkpdb1                  READ WRITE NO
  1. 分别查看CBD和PDB字符集
SQL> select value,parameter from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';

PARAMETER                 VALUE
------------------------- --------------------
NLS_NCHAR_CHARACTERSET    AL16UTF16
NLS_CHARACTERSET          AL32UTF8

SQL> alter session set container=clone_gbkpdb1;

Session altered.

SQL> select parameter,value from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';

PARAMETER                 VALUE
------------------------- --------------------
NLS_NCHAR_CHARACTERSET    UTF8
NLS_CHARACTERSET          ZHS16GBK
--结论：可以看到CDB的字符集是AL32UTF8，pdb:clone_gbkpdb1 的字符集是ZHS16GBK，且PDB状态正常。

五、删除PDB

-- 需要先关闭PDB，否则会抛出如下报错
SYS@ORCLCDB> drop pluggable database PDB2 including datafiles;
drop pluggable database PDB2 including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDB2 is not closed on all instances.


SYS@ORCLCDB> alter pluggable database pdb2 close immediate;
Pluggable database altered.

SYS@ORCLCDB> drop pluggable database PDB2 including datafiles;
Pluggable database dropped.

– 也可以通过dbca命令删除，注意：这里即使pdb是open状态，也会被删除。

[oracle@ora19c ~]$   dbca -silent   -deletePluggableDatabase  -sourceDB ORCLCDB    -pdbName RELOCATEDPDB1
Prepare for db operation
25% complete
Deleting Pluggable Database
40% complete
85% complete
92% complete
100% complete
Pluggable database "RELOCATEDPDB1" deleted successfully.
Look at the log file "/opt/oracle/cfgtoollogs/dbca/ORCLCDB/RELOCATEDPDB1/ORCLCDB.log" for further details.


## 六、重命名PDB

  -- 修改PDB名字，从orclpdb1改为pdb1

```sql
-- 直接改名，会抛出如下错误：
SQL> alter session set container=ORCLPDB1;
Session altered.

SQL> alter pluggable database rename global_name to pdb1;
alter pluggable database rename global_name to orclpdb1
*
ERROR at line 1:
ORA-65045: pluggable database not in a restricted mode

-- 1)启动restricted mode
SQL> alter pluggable database orclpdb1 close immediate;
Pluggable database altered.

SQL> alter pluggable database orclpdb1 open restricted;
Pluggable database altered.

SQL> col name for a20
SQL> select name, open_mode, restricted from v$pdbs;
NAME                 OPEN_MODE            RESTRI
-------------------- -------------------- ------
ORCLPDB1             READ WRITE           YES

-- 2)rename pdb name
SQL> alter session set container=orclpdb1;
Session altered.

SQL> alter pluggable database rename global_name to pdb1;
Pluggable database altered.

SQL> alter pluggable database close immediate;
Pluggable database altered.

SQL> alter pluggable database open;
Pluggable database altered.

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB1                           READ WRITE NO

七、修改PDB字符集

   可以通过下面的方法修改字符集，但是不建议这么操作，最好还是通过数据泵的形式转换字符集，尽量不要去更改数据库的字符集。

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> col parameter for a25
SQL> col value for a20
SQL> select parameter,value from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';

PARAMETER                 VALUE
------------------------- --------------------
NLS_NCHAR_CHARACTERSET    AL16UTF16
NLS_CHARACTERSET          AL32UTF8

SQL> alter system enable restricted session;

System altered.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;

Database altered.

SQL> select parameter,value from NLS_DATABASE_PARAMETERS where PARAMETER like '%CHARACTERSET';

PARAMETER                 VALUE
------------------------- --------------------
NLS_NCHAR_CHARACTERSET    AL16UTF16
NLS_CHARACTERSET          ZHS16GBK

SQL> alter system DISABLE restricted session;

System altered.
cdbpbd墨力计划
最后修改时间：2023-01-06 16:05:00
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论