代码改变世界

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

2022-08-04 10:49  AlfredZhao  阅读(52)  评论(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的数据文件,在其子目录 pdbseedORCLPDB1 下的则分别是种子库和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做过滤,这就留着等下一次我们再来具体看下吧_