一. 12cR2中PDB内存资源管理
12cR2中PDB内存资源管理
在Oracle 12.2中，你可以控制某单个PDB能使用的内存总量。
PDB内存参数
下列参数都可以在PDB级别进行设置：
DB_CACHE_SIZE : The minimum buffer cache size for the PDB.
SHARED_POOL_SIZE : The minimum shared pool size for the PDB.
PGA_AGGREGATE_LIMIT : The maximum PGA size for the PDB.
PGA_AGGREGATE_TARGET : The target PGA size for the PDB.
SGA_MIN_SIZE : The minimum SGA size for the PDB.
SGA_TARGET : The maximum SGA size for the PDB.
设置PDB内存参数
设置PDB内存参数的过程和设置一个普通实例的存储参数并没有什么区别。下面的例子是修改SGA_TARGET参数。
检查CDB的当前设置：
CONN AS SYSDBA
S
SHOW PARAMETER sga_target;
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
s
sga_target big integer 2544M
S
SQL>
检查PDB的当前设置：
CONN AS SYSDBA
A
ALTER SESSION SET CONTAINER=pdb1;
SHOW PARAMETER sga_target;
N
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
s
sga_target big integer 0
S
SQL>
设置PDB的SGA_TARGET参数：
SQL> ALTER SYSTEM SET sga_target=1G SCOPE=BOTH;
SQL> SHOW PARAMETER sga_target;
N
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
s
sga_target big integer 1G
监控PDB的内存使用
Oracle提供了一些视图来监控PDB的资源使用（例如CPU、I/O、内存）。每个视图包含相同的信息，只是保留时间不同。
V$RSRCPDBMETRIC：一个PDB对应一条记录，只保留最近一分钟的数据
V$RSRCPDBMETRIC_HISTORY：一个PDB对应61行记录，保留最近60分钟的数据
DBA_HIST_RSRC_PDB_METRIC：AWR快照，保留时间基于AWR的保留时间
例子如下：
CONN / AS SYSDBA
SET LINESIZE 150
COLUMN pdb_name FORMAT A10
COLUMN begin_time FORMAT A26
COLUMN end_time FORMAT A26
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';
-- Last sample per PDB.
SELECT r.con_id,
p.pdb_name,
r.begin_time,
r.end_time,
r.sga_bytes,
r.pga_bytes,
r.buffer_cache_bytes,
r.shared_pool_bytes FROM v$rsrcpdbmetric r,cdb_pdbs p WHERE r.con_id = p.con_idORDER BY p.pdb_name;
-- Last hours samples for PDB1
SELECT r.con_id,
p.pdb_name,
r.begin_time,
r.end_time,
r.sga_bytes,
r.pga_bytes,
r.buffer_cache_bytes,
r.shared_pool_bytes FROM v$rsrcpdbmetric_history r, cdb_pdbs p
WHERE r.con_id = p.con_id AND p.pdb_name = 'PDB1' ORDER BY r.begin_time;
-- All AWR snapshot information for PDB1.
SELECT r.snap_id,
r.con_id,
p.pdb_name,
r.begin_time,
r.end_time,
r.sga_bytes,
r.pga_bytes,
r.buffer_cache_bytes,
r.shared_pool_bytes FROM dba_hist_rsrc_pdb_metric r,
cdb_pdbs p WHERE r.con_id = p.con_id AND p.pdb_name = 'PDB1'ORDER BY r.begin_time;
二. Non-CDB转换为PDB
当我们需要将Non-CDB数据库类型更改为PDB数据库类型时，可以使用Cloning的方式将其复制到现有的CDB中，但是该方法需要将Non-CDB中的数据文件复制到新的目录中，除了Cloning的方式外我们还可以使用DBMS_PDB包来生成Non-CDB数据库的XML元数据文件，该XML元数据文件中描述了Non-CDB中的数据文件信息，可以使用XML文件将Non-CDB数据库附加为CDB中的PDB，通过该方式将Non-CDB数据库转换成CDB中的PDB，它的优点在于省去了复制Non-CDB数据文件的过程，但要求Non-CDB必须为12.1.0之上的版本，如果Non-CDB为12c之前的版本，需要将其升级到12c，另外需要我们提前创建一个CDB容器数据库，或者现有环境中已存在CDB容器数据库(将Non-CDB插入已存在的CDB中)。
利用non-cdb 创建pdb数据库：
使用DBMS_PDB将Non-CDB转换为PDB的流程如下：
1. 确保Non-CDB处于只读状态。
2. 使用DBMS_PDB.DESCRIBE创建描述Non-CDB的XML元数据文件。
3. 使用DBMS_PDB.CHECK_PLUG_COMPATIBILITY检查Non-CDB与目标CDB是否兼容。
4. 关闭源Non-CDB。
5. 使用描述Non-CDB的XML文件将Non-CDB插入CDB中。
6. 执行$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql。
7. 以读写模式打开新的PDB。
如下示例将Non-CDB:orcl转换成CDB:ora12c中的pdb:orclpdb，详细过程如下：
原数据库：
sys. orcl>select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
Elapsed: 00:00:00.00
sys. orcl>select name, dbid, cdb from v$database;
NAME DBID CDB
------------------------------ ---------- ---
ORCL 1503851221 NO
Elapsed: 00:00:00.01
sys. orcl>
目标数据库：
sys. ora12c>select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
Elapsed: 00:00:00.01
sys. ora12c>select name, dbid, cdb from v$database;
NAME DBID CDB
------------------------------ ---------- ---
ORA12C 345654762 YES
Elapsed: 00:00:00.01
sys. ora12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 QDATAPDB MOUNTED
sys. ora12c>
1.将Non-CDB:orcl置于只读模式。
03:09:33 sys. orcl>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
03:09:57 sys. orcl>startup mount
ORACLE instance started.
Total System Global Area 947912704 bytes
Fixed Size 8627488 bytes
Variable Size 348130016 bytes
Database Buffers 587202560 bytes
Redo Buffers 3952640 bytes
Database mounted.
03:10:08 sys. orcl>alter database open read only;
Database altered.
Elapsed: 00:00:00.90
03:12:58 sys. orcl>
2.连接到Non-CDB并使用存储过程dbms_pdb.describe来创建描述Non-CDB的XML文件。
sys. orcl>exec dbms_pdb.describe(pdb_descr_file=>'/home/oracle/tmp/orcl.xml');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47
sys. orcl>
3.使用dbms_pdb.check_plug_compatibility检查Non-CDB与目标CDB是否兼容：
#pdb_name:指定CDB中新的PDB的名字。
set serverout on
declare
compatible constant varchar2(3) := case
dbms_pdb.check_plug_compatibility(
pdb_descr_file => '/home/oracle/tmp/orcl.xml',
pdb_name => 'orclpdb')
when true then 'yes' else 'no' end;
begin
dbms_output.put_line(compatible);
end;
/
03:14:53 sys. ora12c>set serverout on
03:15:02 sys. ora12c>declare
03:15:03 2 compatible constant varchar2(3) := case
03:15:03 3 dbms_pdb.check_plug_compatibility(
03:15:03 4 pdb_descr_file => '/home/oracle/tmp/orcl.xml',
03:15:03 5 pdb_name => 'orclpdb')
03:15:03 6 when true then 'yes' else 'no' end;
03:15:03 7 begin
03:15:03 8 dbms_output.put_line(compatible);
03:15:03 9 end;
03:15:03 10 /
yes
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05
03:15:03 sys. ora12c>
4.关闭源库Non-CDB:orcl
03:15:40 sys. orcl>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
03:15:59 sys. orcl>
5.将Non-CDB:orcl插入到CDB:ora12c中，且命名为orclpdb。
03:16:29 sys. ora12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 QDATAPDB MOUNTED
sys. ora12c>create pluggable database orclpdb using '/home/oracle/tmp/orcl.xml' nocopy tempfile reuse;
Pluggable database created.
Elapsed: 00:00:01.36
03:19:16 sys. ora12c>
03:19:33 sys. ora12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 QDATAPDB MOUNTED
5 ORCLPDB MOUNTED
03:20:09 sys. ora12c>select pdb_id, pdb_name, dbid, status, con_id from cdb_pdbs;
PDB_ID PDB_NAME DBID STATUS CON_ID
---------- -------------------- ---------- ---------- ----------
2 PDB$SEED 1587318379 NORMAL 2
4 QDATAPDB 2726866135 NORMAL 4
5 ORCLPDB 1503851221 NEW 5
Elapsed: 00:00:00.00
03:20:10 sys. ora12c>
6.执行
$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql脚本，这个脚本需要在打开新的PDB:orclpdb之前执行。
03:21:28 sys. ora12c>alter session set container=orclpdb;
Session altered.
Elapsed: 00:00:00.00
03:21:36 sys. ora12c>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
…
…
7.以读写模式打开新PDB
03:50:20 sys. ora12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 QDATAPDB MOUNTED
5 ORCLPDB MOUNTED
03:50:21 sys. ora12c>alter pluggable database orclpdb open;
Pluggable database altered.
Elapsed: 00:00:11.04
03:51:48 sys. ora12c>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 QDATAPDB MOUNTED
5 ORCLPDB READ WRITE NO
03:55:17 sys. ora12c>alter session set container=orclpdb;
Session altered.
Elapsed: 00:00:00.03
03:55:24 sys. ora12c>create table t as select * from dba_objects;
Table created.
Elapsed: 00:00:01.89
03:55:34 sys. ora12c>
Non-CDB:ORCL成功转换为容器数据库ORA12C中的PDB，且转换过程中未经过数据copy的过程。
三. 创建配置管理PDB
1、手工创建PDB：
[code]sqlplus / as sysdba
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY oracle
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');
SELECT pdb_name, status FROM dba_pdbs ORDER BY pdb_name;
SELECT name, open_mode FROM v$pdbs ORDER BY name;
ALTER PLUGGABLE DATABASE pdb2 OPEN[/code]
2、手工卸载PDB：
[code]ALTER PLUGGABLE DATABASE pdb2 CLOSE;
ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml';
SELECT name, open_mode FROM v$pdbs ORDER BY name;
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;
SELECT name, open_mode FROM v$pdbs ORDER BY name;[/code]
3、手工装载PDB：
[code]SET SERVEROUTPUT ON
DECLARE
l_result BOOLEAN;
BEGIN
l_result := DBMS_PDB.check_plug_compatibility(
pdb_descr_file => '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml',
pdb_name => 'pdb2');
IF l_result THEN
DBMS_OUTPUT.PUT_LINE('compatible');
ELSE
DBMS_OUTPUT.PUT_LINE('incompatible');
END IF;
END;
/
compatible
PL/SQL procedure successfully completed.
SQL>
CREATE PLUGGABLE DATABASE pdb2 USING '/u01/app/oracle/oradata/cdb1/pdb2/pdb2.xml' NOCOPY TEMPFILE REUSE;
ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
SELECT name, open_mode FROM v$pdbs ORDER BY name;
4、手工克隆PDB：
[code]ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ ONLY;
CREATE PLUGGABLE DATABASE pdb4 FROM pdb3
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb3/','/u01/app/oracle/oradata/cdb1/pdb4/');
ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;
将源PDB还原到可读写状态
ALTER PLUGGABLE DATABASE pdb3 CLOSE;
ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;
5、手工删除PDB：
[code]ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;
ALTER PLUGGABLE DATABASE lxpdb2 CLOSE;
DROP PLUGGABLE DATABASE lxpdb2 INCLUDING DATAFILES;
SELECT name, open_mode FROM v$pdbs ORDER BY name;
