代码改变世界

Oracle多租户架构之如何快速创建一个PDB

2022-08-04 10:49  AlfredZhao  阅读(592)  评论(0)  编辑  收藏  举报

Oracle自从12c版本开始引入多租户的架构，整个管理理念也发生了很大的变化。
比如之前再小的业务只要选择了Oracle，DBA都会选择新建一套独立的数据库，因为传统的架构只能在schema级别作区分，而schema级别有很多问题，隔离不彻底，且最常见的就是出现同名的情况，而如今有了多租户架构之后，一切都已经变得简单起来。
假设企业已经有一套多租户的环境，资源充足，专门提供给小业务使用，那PDB就是绝佳的选择。下面具体感受下Oracle多租户架构下，如何快速创建一个PDB？

先查看下已有的一套CDB环境：

SQL> show pdbs;

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

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/system01.dbf
/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/users01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf

可以看到这套环境除了种子（PDB$SEED）外，已经存在了一套PDB环境：ORCLPDB1。从底层的数据文件可以看到存储目录结构。
直接存放在 /opt/oracle/oradata/ORCLCDB/ 下的是CDB的数据文件，在其子目录 pdbseed 和 ORCLPDB1 下的则分别是种子库和ORCLPDB1库，很清晰的目录结构。

如果现在按照这样的规范，想为某一个新业务创建一个新的PDB呢？
只需要一条命令，而且不需要自己从头敲，直接从官方手册中复制如下：

CREATE PLUGGABLE DATABASE salespdb 
  ADMIN USER salesadm IDENTIFIED BY password
  STORAGE (MAXSIZE 2G)
  DEFAULT TABLESPACE sales 
    DATAFILE '/disk1/oracle/dbs/salespdb/sales01.dbf' SIZE 250M 
    AUTOEXTEND ON
    PATH_PREFIX = '/disk1/oracle/dbs/salespdb/'
    FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', 
                         '/disk1/oracle/dbs/salespdb/');

按照目前的情况稍微改下名称和路径，指定下用户和密码，这里就以创建一个salespdb为例：

CREATE PLUGGABLE DATABASE salespdb
  ADMIN USER salesadm IDENTIFIED BY 123456
  STORAGE (MAXSIZE 2G)
  DEFAULT TABLESPACE sales
    DATAFILE '/opt/oracle/oradata/ORCLCDB/SALESPDB/sales01.dbf' SIZE 250M
    AUTOEXTEND ON
    PATH_PREFIX = '/opt/oracle/oradata/ORCLCDB/SALESPDB/'
    FILE_NAME_CONVERT = ('/opt/oracle/oradata/ORCLCDB/pdbseed/',
                         '/opt/oracle/oradata/ORCLCDB/SALESPDB/');

这里也可以将上面内容保存为salespdb.sql文件，方便执行和后续创建其他pdb快速修改。

执行创建成功后，再次查询下pdb和底层数据文件信息:

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 ORCLPDB1			  READ WRITE NO
	 4 SALESPDB			  MOUNTED
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/system01.dbf
/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/users01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf

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

14 rows selected.

可以看到已经成功的按我们预期增加了SALESPDB这个新的PDB以及对应的数据文件，只不过这个PDB还处于mount状态，直接open即可：

SQL> alter pluggable database SALESPDB open;

Pluggable database altered.

SQL> show pdbs;

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

另外细心的朋友可能会发现，PDB那个创建命令中是有明确指定新的PDB的默认表空间和数据文件的，但是之前我们查的信息并没有这个sales表空间和对应数据库文件，但此时open后再次查询就会发现已经有了：

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/system01.dbf
/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/users01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/SALESPDB/system01.dbf
/opt/oracle/oradata/ORCLCDB/SALESPDB/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/SALESPDB/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/SALESPDB/sales01.dbf        <-- 就是这个数据文件

15 rows selected.

这里我们再观察下， DBA熟悉的system、sysaux、undo以及用户自己的业务表空间都是各自独立的（实验环境为19c版本，12c早期版本中undo并不独立），
然后我们再看下临时表空间和redo吧～

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/temp01.dbf
/opt/oracle/oradata/ORCLCDB/pdbseed/temp012021-11-11_15-24-28-021-PM.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/temp01.dbf
/opt/oracle/oradata/ORCLCDB/SALESPDB/temp012021-11-11_15-24-28-021-PM.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/redo03.log
/opt/oracle/oradata/ORCLCDB/redo02.log
/opt/oracle/oradata/ORCLCDB/redo01.log

可以看到临时文件也是独立的，但是redo依然是CDB这一层面的，所以我们做灾备建设ADG时，还是以CDB的粒度来做的，但是最近官方宣称DGPDB已经在21.7中GA了，具体是怎么实现的呢？是对redo改了啥还是只是在接收端根据pdb做过滤，这就留着等下一次我们再来具体看下吧_