oracle多租户环境之CDB与PDB操作小结

前言：

众所周知，从oracle12c版本开始，oracle就支持多租户环境（Multitenant Environment），它允许一个数据库容器（CDB：Container Database）承载多个可插拔数据库（PDB：Pluggable Database）。CDB相当于操作系统，调用并管理各个PDB。PDB相当于真正提供业务需求的数据库实例。CDB容器化部署，能够更加灵活地共享服务器资源，快速地创建、克隆和迁移数据库实例。

1. 创建容器数据库CDB

Creating a CDB with DBCA：

Oracle strongly recommends using the Database Configuration Assistant (DBCA) to create a CDB, because it is a more automated approach, and your CDB is ready to use when DBCA completes.
Oracle强烈建议使用数据库配置助手(DBCA)来创建CDB，因为这是一种自动化程度更高的方法，并且CDB在DBCA创建完成后即可开始使用。

(方式1)图形化方式创建CDB：
cd $ORACLE_HOME/bin
./dbca

(方式2)非图形化(静默)安装：
dbca -silent -createDatabase -createAsContainerDatabase true \
-templateName $ORACLE_HOME/assistants/dbca/templates/General_Purpose.dbc -gdbname ORCL \
-storageType FS -datafileDestination /oradata/orcl -redoLogFileSize 200 \
-emConfiguration none -totalMemory 5120

注：totalMemory参数表示oracle的SGA+PGA的总大小，单位为MB。

1.2 查询当前数据库是否为容器数据库(CDB)

SELECT CDB FROM V$DATABASE;

CDB
—
YES

2. 创建容器(PDB)

2.1 使用DBCA创建容器(PDB)

在支持图形化界面的环境中，使用DBCA创建容器是值得推荐的一种方法。
具体操作过程略。

2.2 黑屏方式(命令行下)创建PDB

create pluggable database:
通过create pluggable database命令，我们可以做以下工作：

1）通过模板创建一个pdb
2）通过已存在的pdb或no-cdb克隆一个新的pdb
3）通过插入一个卸载的pdb或者将一个no-cdb 插入到一个 cdb中
4）可以创建一个应用pdb、应用种子或应用容器

以下链接到官方文档：

“Creating a PDB from Scratch”

Create a PDB in a CDB using the files of the PDB seed or application seed. This technique copies the files associated with the seed to a new location and associates the copied files with the new PDB. This is the default creation mechanism. The other techniques require either a source PDB or XML.

“Cloning a PDB”

Create a PDB by cloning a source PDB. A source can be a PDB in the local CDB, a PDB in a remote CDB, or a PDB in a local or remote application container. This technique copies the files associated with the source to a new location and associates the copied files with the new PDB.

“Relocating a PDB”

Create a PDB by relocating it from one CDB to another. This technique moves the files associated with the PDB to a new location.

“Plugging In an Unplugged PDB”

Create a PDB by using the XML metadata file that describes the PDB and the files associated with the PDB to plug it into the CDB.

“Creating a PDB as a Proxy PDB”

Create a PDB as a proxy PDB by referencing a different PDB with a database link. The referenced PDB can be in the same CDB as the proxy PDB, or it can be in a different CDB.

2.2.1 直接通过种子容器创建新的PDB：

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb2admin IDENTIFIED BY Pdb2admin;
该方式需要开启OMF：
ALTER SYSTEM SET DB_CREATE_FILE_DEST='+PDB_DATA';

2.2.2 创建PDB时指定默认表空间

--以下命令将创建一个默认表空间USERS，roles默认为pdb_dba
create pluggable database pdb1 admin user pdbadmin identified by pdbadmin roles=(dba) default tablespace users datafile '+PDBT_DATA' size 250M autoextend on;

--如果没有指定默认表空间，此时数据库将分配SYSTEM表空间为pdb默认表空间
create pluggable database pdb1 admin user pdbadmin identified by pdbadmin;

2.2.3 创建时指定pdb数据文件保存位置

``` 

--先查询当前CDB数据文件保存路径
set lin 200
select name from v$datafile
union
select name from v$tempfile;

--关闭OMF
ALTER SYSTEM SET DB_CREATE_FILE_DEST='';

CREATE PLUGGABLE DATABASE pdb1
ADMIN USER pdbadmin IDENTIFIED BY pdbadmin
DEFAULT TABLESPACE users
DATAFILE '+PDB_DATA/PDB1/users01.dbf' SIZE 250M
AUTOEXTEND ON
PATH_PREFIX = '+PDB_DATA/PDB1/' --注意：CREATE PLUGGABLE DATABASE语句的PATH_PREFIX子句确保和限制了与PDB关联的所有目录对象路径都必须在该指定的目录或其子目录中。
创建PDB后，不能再修改其PATH_PREFIX设置。该限制包括但不限于数据文件、spfile、directory等，因此不推荐使用该参数。
FILE_NAME_CONVERT = (
'+PDB_DATA/IWENDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.265.1133953331','+PDB_DATA/PDB1/system.dbf',
'+PDB_DATA/IWENDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.266.1133953331','+PDB_DATA/PDB1/sysaux.dbf',
'+PDB_DATA/IWENDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.267.1133953331','+PDB_DATA/PDB1/undotbs1.dbf',
'+PDB_DATA/IWENDB/F91BD56E240F4F8FE05317CA19ACCEBA/TEMPFILE/temp.268.1133953349','+PDB_DATA/PDB1/temp01.dbf'
);
```

参考：
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-PLUGGABLE-DATABASE.html#GUID-F2DBA8DD-EEA8-4BB7-A07F-78DC04DB1FFC

假如指定了PATH_PREFIX,则后续在pdb中创建目录时会报错：
SQL> create or replace directory DATA_PUMP as '/backup';
create or replace directory DATA_PUMP as '/backup'
*
ERROR at line 1:
ORA-65254: invalid path specified for the directory

查看当前pdb中设置的'PATH_PREFIX'的路径：
--pdb中执行
SQL> select * from cdb_properties where property_name = 'PATH_PREFIX';

2.2.4 直接克隆已有PDB

--克隆CDB中已经存在的某个PDB

CREATE PLUGGABLE DATABASE pdb2 FROM pdb1
FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdb1/','/disk1/oracle/dbs/pdb2/')
PATH_PREFIX = NONE;

注意：如果当前数据库使用的是OMF方式，则FILE_NAME_CONVERT参数中需要列出所有具体的数据文件地址以及转换后的路径和文件名。

--远程克隆,需要先在本地添加好tns别名相关信息，然后执行克隆
create pluggable database pdb8 from
pdb1@remotePdb FILE_NAME_CONVERT=('/oradata/pdb1','/oradata/pdb8');

3. 连接到容器

sqlplus / AS SYSDBA
show pdbs
ALTER PLUGGABLE DATABASE PDB1 OPEN;
注：除了使用dbca创建pdb以外，其它方式创建pdb后，都需要初次使用时对其进行open，以使容器初始化。

--切换到指定pdb
ALTER SESSION SET CONTAINER = PDB1;

--关闭指定数据pdb
ALTER PLUGGABLE DATABASE PDB1 CLOSE;

--打开所有pdb
ALTER PLUGGABLE DATABASE all OPEN;

--切换到cdb根容器
ALTER SESSION SET CONTAINER = cdb$root;

--保存当前容器数据库状态
如果容器为open，则重新启动cdb后，对应容器也会自动open:
alter pluggable database pdb1 save state;

4. 使用rman备份和恢复某个容器

--备份容器
rman target /
backup database;
or
backup pluggable database pdb1;

--恢复某个容器
restore pluggable database pdb2;
recover pluggable database pdb2;
ALTER PLUGGABLE DATABASE PDB2 open;

5. 为容器创建新的service(服务)

默认情况下，容器创建好后，会自动注册1个与容器名同名的service，供外部连接使用。
set lines 200
col name for a20
col pdb for a20
select con_id,name,enabled,pdb from cdb_services order by 1;
select con_id, pdb, network_name from cdb_services where pdb is not null and con_id > 2 order by pdb;

column name format a30;
column network_name format a30;
select name, network_name from v$active_services;

show con_name

--向当前PDB添加新的service:
dbms_service.create_service(service_name IN VARCHAR2,network_name IN VARCHAR2,parameter_array IN svc_parameter_array);
例如：
--当前容器/实例中创建1个新服务
exec dbms_service.create_service('pdb1.com','pdb1.com');

在rac环境中，使用如下命令添加服务：
srvctl add service -d ORCLCDB -s pdb1.com -pdb PDB1 -preferred ORCLCDB1,ORCLCDB2

--启动服务
exec dbms_service.start_service('pdb1');
exec dbms_service.stop_service('pdb1',DBMS_SERVICE.ALL_INSTANCES);

--删除服务
exec dbms_service.delete_service('pdb1');

--如果连接容器时提示ORA-12518: TNS: 监听程序无法分发客户机连接
处理方法：
1) 在tnsnames.ora中添加容器服务名
如：
pdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.171)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)

2) 重启监听
lsnrctl reload

3) 进入容器，设置local_listener=<目录名>
如：alter system set local_listener=pdb1;

6. 删除PDB

drop pluggable database pdb1 including datafiles;

7. 容器相关查询

7.1 查看 pdb_dba权限
col granted_role for a20
select granted_role,ADMIN_OPTION from dba_role_privs where grantee='PDBADMIN';
select * from DBA_SYS_PRIVS where grantee='PDB_DBA';

7.2 查询 CDB 中的容器信息
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

7.3 查询 PDB 信息
SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

7.4 查询 PDB 的 open mode
SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS ;
或
show pdbs

7.5 查询容器下的表
--查询指定多租户容器用户下的表
SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
FROM DBA_PDBS p, CDB_TABLES t
WHERE p.PDB_ID > 2
--AND t.OWNER IN('HR')
AND p.PDB_ID = t.CON_ID
ORDER BY p.PDB_ID;

7.6 查询多租户容器中的用户
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;

7.7 查询每个PDB的数据文件
SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;

7.8 查询CDB的temp文件
SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES ORDER BY CON_ID;

7.9 查询PDBs的服务名
SELECT PDB, NETWORK_NAME, CON_ID
FROM CDB_SERVICES
WHERE PDB IS NOT NULL AND CON_ID > 2
ORDER BY PDB;

7.10 查询pdb用户创建的表和视图
--Querying a Table Owned by a Common User Across All PDBs
SELECT * FROM CONTAINERS(employees);

CREATE OR REPLACE VIEW employees AS SELECT * FROM hr.employees;
SELECT * FROM CONTAINERS(employees);
SELECT * FROM CONTAINERS(employees) WHERE CON_ID IN(3,4);

7.11 查询当前 container 的 ID 和 name
SHOW CON_ID
SHOW CON_NAME
SELECT CON_NAME_TO_ID('HRPDB') FROM DUAL;
SELECT CON_DBID_TO_ID(2226957846) FROM DUAL;

7.12 查询 PDB 中可以修改的参数
SELECT NAME FROM V$SYSTEM_PARAMETER
WHERE ISPDB_MODIFIABLE = 'TRUE'
ORDER BY NAME;

7.13 查询 PDB 历史记录
COLUMN DB_NAME FORMAT A10
COLUMN CON_ID FORMAT 999
COLUMN PDB_NAME FORMAT A15
COLUMN OPERATION FORMAT A16
COLUMN OP_TIMESTAMP FORMAT A10
COLUMN CLONED_FROM_PDB_NAME FORMAT A15

SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;