创建容器数据库
最方便方法是DBCA。
在创建时必须设定实例参数ENABLE_PLUGGABLE_DATABASE=TRUE
使用DBCA创建CDB
当然DBCA也可以创建non-CDB。
GUI不说了,下面是命令行示例,参见这里:
## CDB with 1 PDB
$ dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname ORCLCDB1 -sid ORCLCDB1 -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword Welcome1 \
-systemPassword Welcome1\
-createAsContainerDatabase true \
-numberOfPDBs 1 \
-pdbName orclpdb \
-pdbAdminPassword Welcome1 \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-totalMemory 1536 \
-storageType FS \
-datafileDestination "/opt/oracle/oradata" \
-redoLogFileSize 50 \
-emConfiguration NONE \
-ignorePreReqs
## non-CDB
$ dbca -silent -createDatabase \
-templateName General_Purpose.dbc \
-gdbname ORCLCDB1 -sid ORCLCDB1 -responseFile NO_VALUE \
-characterSet AL32UTF8 \
-sysPassword Welcome1 \
-systemPassword Welcome1\
-createAsContainerDatabase false \
-databaseType MULTIPURPOSE \
-automaticMemoryManagement false \
-totalMemory 1536 \
-storageType FS \
-datafileDestination "/opt/oracle/oradata" \
-redoLogFileSize 50 \
-emConfiguration NONE \
-ignorePreReqs
此命令在我笔记本上耗时20m2.815s, non-CDB耗时10m17.134s
删除使用以下,删除时数据库必须打开:
dbca -silent -deleteDatabase -sourceDB ORCLCDB1 -sysDBAUserName sys -sysDBAPassword Welcome1
dbca的帮助:
$ dbca -createDatabase -h
使用SQL*Plus创建CDB
CDB与non-CDB内存与物理结构均不同。设置实例参数ENABLE_PLUGGABLE_DATABASE为TRUE设置了内存结构,CREATE DATABASE命令中包括ENABLE PLUGGABLE DATABASE 设置了物理结构。
DB_CREATE_FILE_DEST可指定数据文件的位置。PDB_FILE_NAME_CONVERT可设定文件名转换规则。
示例:
# $ORACLE_HOME/dbs下创建参数文件initORCLCDB2.ora
control_files='/opt/oracle/oradata/ORCLCDB2/control.ctl'
db_name=ORCLCDB2
memory_target=1g
enable_pluggable_database=true
$ export ORACLE_SID=ORCLCDB2
$ sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> startup nomount
$ mkdir /opt/oracle/oradata/ORCLCDB2
$ mkdir /opt/oracle/oradata/seed
# 下面这个命令执行很快结束
SQL>
create database ORCLCDB2
logfile
group 1 ('/opt/oracle/oradata/ORCLCDB2/redo1a.log') size 50m,
group 2 ('/opt/oracle/oradata/ORCLCDB2/redo2a.log') size 50m
datafile '/opt/oracle/oradata/ORCLCDB2/system01.dbf' size 500m
sysaux datafile '/opt/oracle/oradata/ORCLCDB2/sysaux01.dbf' size 500m
default temporary tablespace temp
tempfile '/opt/oracle/oradata/ORCLCDB2/temp01.dbf' size 100m
undo tablespace undotbs datafile '/opt/oracle/oradata/ORCLCDB2/undotbs01.dbf' size 100m
enable pluggable database
seed file_name_convert = ('ORCLCDB2', 'seed');
# 创建数据字典和PL/SQL Package,执行时间较长, 3m41.736s和45m38.082s
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql
$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql
# 验证
select name, cdb from v$database;
select name, con_id, open_mode from v$containers;
select con_id, name from v$datafile;
select con_id, name from v$tempfile;
select * from v$controlfile;
select con_id, member from v$logfile;
select sys_context('userenv', 'con_name') from dual;
show con_name;
## 删除
shutdown immediate;
startup mount exclusive restrict;
drop database;
创建PDB
有4中方法。
使用PDB$SEED创建新PDB
主要需考虑数据文件路径,可以使用OMF或指定PDB_FILE_NAME_CONVERT。
alter system set db_create_file_dest='/opt/oracle/oradata';
create pluggable database orclpdb2 admin user pdbadmin identified by Welcome1;
select con_id, name, open_mode from v$pdbs; -- open_mode=mounted
select con_id, name, open_mode from v$containers;
select con_id, pdb_id, pdb_name, status from cdb_pdbs; -- status=new
select con_id, file_name from cdb_data_files;
select con_id, name from v$datafile;
alter pluggable database orclpdb2 open;
select con_id, name, open_mode from v$containers; -- open_mode=READ WRITE
select con_id, pdb_id, pdb_name, status from cdb_pdbs; -- status=normal
新的PDB会创建默认的服务名并在监听中注册。
$ lsnrctl status
...
Service "orclpdb1" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb2" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
删除数据库:
alter pluggable database orclpdb2 close;
drop pluggable database orclpdb2 including datafiles;
克隆已有PDB以创建新PDB
alter system set db_create_file_dest='/opt/oracle/oradata';
alter pluggable database orclpdb1 close;
alter pluggable database orclpdb1 open read only;
create pluggable database orclpdb1_clone from orclpdb1 storage unlimited file_name_convert=NONE;
alter pluggable database orclpdb1 close;
alter pluggable database orclpdb1 open read write;
alter pluggable database orclpdb1_clone open read write;
将Non-CDB插入到CDB
Non-CDB必须是12c。假设non-CDB为ORCLCDB1,插入到ORCLCDB中的orclpdb2:
export ORACLE_SID=ORCLCDB1
sqlplus / as sysdba
startup mount
alter database open read only;
exec dbms_pdb.describe('/tmp/ORCL.xml');
export ORACLE_SID=ORCLCDB
sqlplus / as sysdba
create pluggable database orclpdb2 using '/tmp/ORCL.xml';
alter session set container=orclpdb2;
alter database open;
-- 删除在多租户环境中不必要的元数据,并重新编译,执行时间很长
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
alter database open;
此方法无需额外空间,仍使用之前的数据文件。
将拔出的PDB插入到CDB
是宕机时间最少的升级方法。
create pluggable database orclpdb1 using '/tmp/orclpdb1.xml' nocopy;
alter pluggable database orclpdb1 open read write;
拔出并删除PDB
拔出:
alter pluggable database orclpdb1 close;
alter pluggable database oraclpdb1 unplug into '/tmp/orclpdb1.xml';
删除:
alter pluggable database orclpdb2 close;
drop pluggable database orclpdb1 including datafiles;
迁移12.1版本之前的NON-CDB到CDB
指10g或11g数据库。或者升级,或者使用Data Pump。前者无需额外空间,后者更加灵活,可跨平台,可以实现文件系统到ASM转换,可保留两份环境,确认兼容后再删除老的环境,但迁移时间长。
建立到CDB和PDB的连接
可使用OS认证或easy connect或通过服务,每一个新建、克隆或插入的PDB都会建立一个服务名,或者你可以用DBMS_SERVICE包和srvctl实用程序创建服务。
理解CDB和PDB服务名
CDB中的每一个容器(除PDB$SEED)都有自己的服务名,CDB默认的服务名是CDB名字加上域名。
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-JAN-2020 20:29:46
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 02-JAN-2020 20:01:41
Uptime 0 days 0 hr. 28 min. 4 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/oracle-19c-vagrant/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle-19c-vagrant)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/19c/dbhome_1/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "9b23d6499799073be0530100007f6a6f" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "9b24e21f078450cde0530100007fb288" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "ORCLCDB1" has 1 instance(s).
Instance "ORCLCDB1", status READY, has 1 handler(s) for this service...
Service "ORCLCDB1XDB" has 1 instance(s).
Instance "ORCLCDB1", 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 "orclpdb1" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
Service "orclpdb2" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
The command completed successfully
为CDB和PDB创建服务
如果安装并配置了GI,启用Oracle Restart或Oracle RAC:
srvctl add service -db orclcdb -service orclpdb1_extra -pdb orclpdb1
srvctl start service -db orclcdb -service orclpdb1_extra
lsnrctl status
如果没有安装GI,则必须先连接到PDB:
alter session set container=orclpdb1;
begin
dbms_service.create_service(
service_name => 'orclpdb1_int',
network_name => 'orclpdb1_ext'
);
dbms_service.start_service(service_name => 'orclpdb1_int');
end;
/
查看服务,注意输出中显示的是orclpdb1_ext
:
$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-JAN-2020 20:39:09
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 02-JAN-2020 20:01:41
Uptime 0 days 0 hr. 37 min. 27 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/oracle-19c-vagrant/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle-19c-vagrant)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/19c/dbhome_1/admin/ORCLCDB/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
...
Service "orclpdb1_ext" has 1 instance(s).
Instance "ORCLCDB", status READY, has 1 handler(s) for this service...
...
使用easy connect连接:
$ sqlplus sys/Welcome1@localhost:1521/orclpdb1_ext as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 2 20:42:26 2020
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
SQL> show con_name;
CON_NAME
------------------------------
ORCLPDB1
在CDB中切换连接
必须有SET CONTAINER权限。
alter session set container=orclpdb1;
alter session set container=cdb$root;
show con_name;
如果有未提交事务,切换连接时并不会自动提交。
启停CDB以及开闭PDB
在RAC环境下,CDB在每节点只有一个实例,PDB可以在这些实例间切换。
CDB的启停会带动其中PDB的启停。
启动CDB 实例
CDB的5状态:
- shutdown (shutdown immediate)
- nomount (实例启动,startup nomount)
- mount (CDB controlfile打开,cdb$root和pdb mount, alter database mount)
- open (cdb$root open, pdb的状态取决于save state, alter database open)
- pdb open (alter pluggable database orclpdb1 open)
开闭PDB
可以打开所有或部分PDB,或排除某些PDB。
使用 ALTER PLUGGABLE DATABASE命令
alter pluggable database all open;
alter pluggable database orclpdb2 close;
alter pluggable database orclpdb2 open read only;
alter pluggable database orclpdb2 close;
alter pluggable database orclpdb2 open read write;
alter session set container=orclpdb2;
alter pluggable database close;
alter pluggable database open read write;
选择性开闭PDB
alter pluggable database all except orclpdb1 close;
alter pluggable database all except orclpdb1 open;
alter pluggable database all close;
alter pluggable database all open;
在PDB中,也可以使用shutdown命令,等同于close。
CDB实例shutdown
shutdown immediate
PDB自动启动
保存其状态(12.1.0.2后支持):
alter pluggable database orclpdb2 open read write;
alter pluggable database orclpdb2 save state;
老的,不建议的方法是使用触发器:
create trigger open_dev
after startup on database
begin
execute immediate 'alter pluggable database orclpdb1 open';
end;
/
改变PDB状态
-- 进入维护模式
alter pluggable database orclpdb1 close;
alter pluggable database orclpdb1 open restricted;
-- 退出维护模式
alter pluggable database orclpdb1 close;
alter pluggable database orclpdb1 open;
维护模式下,可修改PDB名字,修改默认表空间,将数据文件离线等。
评估参数改变的影响
通常情况下,PDB会继承CDB的参数,不过也有少数参数可在PDB一级修改。
理解参数改变的范围
V$PARAMETER中的ISPDB_MODIFIABLE决定了参数是否可在PDB一级修改。
select name from V$PARAMETER where ISPDB_MODIFIABLE='TRUE';
PDB_SPFILE$
显示PDB一级参数的非默认值。
SQL> select db_uniq_name,pdb_uid, name, value$ from PDB_SPFILE$;
DB_UNIQ_NAME PDB_UID NAME VALUE$
------------ ---------- ------------------------------ ------------------------------
* 991076965 sga_target 1207959552
* 991076965 open_cursors 300
* 991076965 nls_language 'AMERICAN'
* 991076965 nls_territory 'AMERICA'
* 991076965 pga_aggregate_target 402653184
多租户环境下使用ALTER SYSTEM
根据上下文,alter system可能影响整个CDB或只影响当前PDB。或有时只能在cdb$root中执行。
以下命令只影响当前PDB:
ALTER SYSTEM FLUSH SHARED_POOL
ALTER SYSTEM FLUSH BUFFER_CACHE
ALTER SYSTEM ENABLE RESTRICTED SESSION
ALTER SYSTEM KILL SESSION
ALTER SYSTEM SET <parameter>
ALTER SYSTEM CHECKPOINT
可在PDB中运行,但影响整个CDB。ALTER SYSTEM SWITCH LOGFILE
只能在根容器中执行。