翻译自:
Oracle Multitenant Option - 12c : Frequently Asked Questions (Doc ID 1511619.1)
适用于:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.1 [Release 12.1]
Information in this document applies to any platform.
什么是多租户架构中的PDB?
PDB是一个Oracle DB 12.1中的新特性.在一个Oracle数据库中,你可以有多个PDB.
PBD是完全向12.1之前的DB兼容的.(Pluggable Databases are fully backwards compatible with an ordinary pre-12.1 database)
我为啥考虑使用多租户选项?
降低TCO
提升功能
不需要修改应用程序
对性能无损害
在多个application之间提供资源管理和隔离
简化Oracle Database 补丁更新和版本.
我能从多住户选项中得到其他的收益?
Fast provisioning of a new database or of a copy of an existing database.
Fast redeployment,by unplug and plug,of an existing database to a new platform.
Quickly patch or upgrade the Oracle Database version for many databases and for the cost of doing it once.
Patch or upgrade by unplugging a PDB and plugging it into a different container database (CDB) in a later version.
A machine can run more database instances in the form of PDBs than as individual,monolithic databases.
Separate the duties of the application administrator from the duties of the administrator of the Oracle-supplied system.
把一个12.1之前版本的DB迁移到12c多租户架构之下难易程度如何?
迁移到12cPDB数据库很简单并且很容易.你可以评估并采用适合你的如下方法:
Plan A.
先把12.1之前版本的DB升级到12.1
Plug-in the database post upgrade into a CDB
Plan B.
搞一个空的PDB
使用数据泵或者OGG来迁移数据到PDB中.
你可以看如下文章Doc ID 2051130.1 - Database upgrade to 12c Pluggable database (Multitenant),
该文章使用的是TTS方法,通过TTS方法,从11.2.0.4.0的DB直接升级到了12.1.0.2.0的DB,这个方法不需要中间的12c升级过程.
在多租户架构中,哪些Oracle数据库特性是不支持的?
1.Continuous Query Notification
2.Flashback Data Archive
3.Heat Maps
4.Automatic Data Optimization
你如果一定使用上述特性,那么请建立一个non-CDB
问:Does each non-CDB need its own CDB if I do not have a license for Multitenant?
答:YES --------译者注:没明白啥意思....
问:多个CDB可以运行在同一个server上么?
答:YES
问:多个CDB可以运行在不同的ORACLE_HOME下.
答:YES
问:怎么知道我的DB是不是多租户架构:
答:运行如下查询即可:
sql> select NAME,DECODE(CDB,'YES','Multitenant Option enabled','Regular 12c Database: ') "Multitenant Option ?",OPEN_MODE,CON_ID from V$DATABASE;
NAME Multitenant Option ? OPEN_MODE CON_ID
--------- ------------------------------ -------------------- ----------
CDB2 Multitenant Option enabled MOUNTED 0
问:在一个容器数据库中有哪些PDB?
答:
sql> select CON_ID,NAME,OPEN_MODE from V$PDBS;
CON_ID NAME OPEN_MODE
---------- ------------------------ ------------
2 PDB$SEED READ ONLY
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 MOUNTED
6 PDB4 MOUNTED
7 PDB5 MOUNTED
8 PDB6 MOUNTED
9 PDB7 MOUNTED
...
问:我怎么连接一个特定的PDB,比如PDB6?
答:你可以用如下sql从Root Container 或者其他PDB来切换到PDB6
sql> alter session set container = pdb6;
使用sql*Plus 的CONNECT命令可以直接连接到PDB中,例子如下:
A) Database connection using easy connect
举例1: CONNECT username/password@host[:port][/service_name][:server][/instance_name]
举例2:从OS提示符运行sqlplus命令:
$ sqlplus hpal/hpal@//hpal-node1:1521/pdb2
OR
$ sqlplus hpal/hpal@//localhost:1521/pdb2
OR
$ sqlplus hpal/hpal@//localhost/pdb2
sql> show con_name
CON_NAME
------------------------------
PDB2
B)使用网络服务名来建立数据库连接:
TNSNAMES.ora的内容举例如下:
=======
LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
)
)
PDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hpal-node1.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)
=======
从OS提示符运行sqlplus命令:
$ sqlplus hpal/hpal@pdb2
问:我怎么切换到主容器数据库?
答:
sql> ALTER SESSION SET CONTAINER = CDB$ROOT;
问:怎么确定我当前连接的是PDB or CDB?
答:
sql> show con_name
CON_NAME
------------------------------
PDB6
OR
sql> select sys_context ( 'Userenv','Con_Name') "Container DB" from dual;
Container DB
--------------------------------------------------------------------------------
PDB6
问:怎么startup 一个PDB?
答:
当你连接的是PDB时:
sql> alter pluggable database open;
当你连接的root时:
sql> alter pluggable database pdb6 open;
问:我怎么关闭一个PDB?
答:
当你连接的是PDB时:
sql> alter pluggable database close;
当你连接的root时:
sql> alter pluggable database pdb6 close;
问:我怎么关闭/启动 CDB(Container Database )?
答:
使用startup/shutdown名来来启动/关闭 non CDB ----译者注:我觉得此出的non不应该有.
当CDB关闭之后,任何PDB都不能被访问.
在一个CDB中,root和所有PDB均共享一个实例(instance),当使用rac时,多个并发数据库实例.
你启动和关闭这个CDB,而不是单个PDB,但是,当CDB被open之后,你可以使用 ALTER PLUGGABLE DATABASE命令来改变单个PDB的open mode.
问:在PDB级别可以修改哪些参数?
答:
select NAME,ISPDB_MODIFIABLE from V$PARAMETER;
问:在我的CDB中,common users有哪些?
答:
sql> select distinct USERNAME from CDB_USERS where common = 'YES';
问:我怎么建立common user?
答:
create user c##db_dba1 identified by manager1 container=all;
问:为怎么建立local user?
答:
sql> create user pdb6_dba1 identified by manager1 container=current;
多租户架构:
Container ID 零和1的区别是什么?
con_id 等于零意味着data不从属(pertain)于任何一个特定的Container,而从属于作为一个整体的CDB.
举例来说,从v$database中查询返回的一行(row)从属于CDB,不从属于任何一个特定的Container,因此con_id 被设置为0.
一个CONTAINER_DATA object 能令人信服的返回从属于很多Containers(包括 con_id等于的1的Root)的数据,
并且CDB作为一个整体,CDB行中的con_id将会被设置为0
下面的表格描述了Container Data Objects 中con_id列的各种各样的值:
0 是The data pertains to the entire CDB
1 是The data pertains to the root
2 是The data pertains to the seed
3到254是The data pertains to a PDB,每个PDB有自己的Container ID
问:有后台进程(比如pmon,smon等等)与PDB想关联么?
答:没有.后台进程是被root和所有pdb所共享的.
问:每个PDB有单独的控制文件么?
答:没有.整个CDB级别有单个redo log和单个控制文件
问:每个PDB有单独的redo log么?
答:没有.整个CDB级别有单个redo log和单个控制文件
问:我能在PDB上监控sga 使用情况么?
答:sga是被所有PDB共享的,你可以确定被所有Container使用的sga消耗,比如root和PDB
sql> alter session set container=CDB$ROOT;
sql> select POOL,BYTES from V$SGASTAT where CON_ID = '&con_id';
sql> select CON_ID,POOL,sum(bytes) from v$sgastat
group by CON_ID,POOL order by CON_ID,POOL;
问:我能在PDB上监控pga 使用情况么?
select CON_ID,sum(PGA_USED_MEM),sum(PGA_ALLOC_MEM),sum(PGA_MAX_MEM)
from v$process
group by CON_ID order by CON_ID;
alter session set container =CDB$ROOT;
select NAME,value from v$sysstat where NAME like 'workarea%';
alter session set container = <targetPDB>;
select NAME,value from v$sysstat where NAME like 'workarea%';
总结--多租户架构下,undo,redo,控制文件,临时表空间都是全局共享的
问:对每个pdb来说,我需要使用单独的undo表空间么?
答:对一个CDB实例来说,有一个active的undo tablespace.
对一个Oracle RAC CDB来说,每个实例有一个active的undo tablespace.
只有有合适权限并且当前Container是root的Common user 才能建立undo tablespace.
问:对每个pdb来说,我需要使用单独的system表空间么?
答:对root和每个pdb来说,都有各自的system表空间
问:对每个pdb来说,我需要使用单独的sysaux表空间么?
答:对root和每个pdb来说,都有各自的sysaux表空间.
问:对每个pdb来说,我需要使用单独的临时表空间么?
答:对于整个CDB来说,有一个默认的临时表空间.但是,你可以在pdb中建立额外的临时表空间.
一个活动的临时表空间对一个单实例CDB来说是需要的.
或者:一个活动的临时表空间对Oracle RAC CDB中的每个实例来说是需要的.
问:对于root和每个pdb,我需要指定默认表空间么?
答:是的,你可以为root和每个pdb指定默认表空间.
问:对于root和pdb来说,所有的物理文件是分开的么?
答:对于root,seed,pdb来说,都有各自的datafile
问:在CDB中,user data存在于哪里?
答:在一个CDB中,user data存在于PDB中,root Container 不存储user data或者存储最少的user data.
问:PDB支持独立的字符集么?
答:一个CDB使用一个字符集,其下所有的PDB均使用这个字符集.
Oracle推荐如下:
1.对于全新部署并且如果所有pdb被创建为空的,Oracle推荐CDB使用AL32UTF8数据库字符集,推荐CDB使用AL16UTF16国家字符集.
2.在consolidation之前,如果你迁移现有的数据库到AL32UTF8,
Oracle recommends that you do so and consolidate into one or more AL32UTF8 CDBs,依据你的需求.
对于unicode字符集来说,你可以使用 Oracle Database Migration Assistant 把一个non-CDB迁移到AL32UTF8.
You cannot migrate the CDB using Oracle Database Migration Assistant for Unicode,after creation.
当建立CDB之后,你不能使用Oracle Database Migration Assistant来迁移个unicode的CDB.---不太确定这么翻译是否合适.
3.If you cannot migrate your existing databases prior to consolidation,
then you have to partition them into sets with plug-in compatible database character sets
and plug each set into a separate CDB with the appropriate superset character set.
参考:Oracle Database Globalization Support Guide,12c Release 1 (12.1)
问:在pdb环境中,怎么配置Net files
答:对整个cdb来说,存在单个的listener.ora,tnsnames.ora,sqlnet.ora.在该CDB中的所有的PDB都使用这些文件.
高级CDB/PDB操作.
问:怎么安装与建立PDB?
答:使用runInstaller 来安装Oracle数据库软件
使用dbca来建立数据库,你可以在单个操作中,你可以建立多个pdb
dbca让你可以在cdb中指定pdb中的数量.当一个cdb被建立之后,你可以使用dbca来把pdb插入到cdb中,把pdb从cdb中拔出.
These operations act on PDBs as entities:
• create PDB (brand-new,as a clone of an existing PDB,by plugging in an unplugged PDB)
• unplug PDB
• drop PDB
• set the Open_Mode for a PDB
问:怎么创建一个pdb?
答:
create pluggable database x admin user a identified by p;
create pluggable database y admin user a identified by p file_name_convert = ('pdbseed','y');
问:怎么不可撤销地drop 一个pdb?
答:
drop pluggable database x
including datafiles;
问:How easy is it to manage the provisioning of PDBs using PL/sql ?
答:暂时不翻译.
问:怎么从一个现存的pdb中克隆出一个pdb?
答:
clonee(这应该是:克隆的源头数据库)必须出于read only open模式
--使用Oracle-Managed Files方式:
create pluggable database x2 from x;
问:怎么unplug(拔出) 一个pdb?
答:
alter pluggable database x unplug into '/some_directory/x_description.xml' ;
注意:into关键字之后必须是全路径(full path),生成的文件是xml格式的.
可扩展性和RAC
问:怎么add 或者modify 一个用户自定义的service?
答:srvctl add service … –pdb <pdb_name>
Starting a user-managed service using
srvtcl will open the PDB automatically in all the instances in which the service is started.
Specifying the empty string ("") as the <pdb_name> will cause the pluggable database
attribute of a service to be set to null. The service can then be used only to connect to the root.
问:怎么查看一个pdb所关联的service?
答:
sql> column NAME format a30
sql> select PDB,INST_ID,NAME from gv$services order by 1;
PDB INST_ID NAME
-------------------------------- ---------- --------------------------------
CDB$ROOT 1 cdb1XDB
CDB$ROOT 1 SYS$BACKGROUND
CDB$ROOT 1 SYS$USERS
CDB$ROOT 1 cdb1
PDB1 1 pdb1
PDB2 1 pdb2
诊断:
问:一个特定pdb的alert日志在哪里?
答:
A Single copy of Alert log is generated which contains warnings and alert information for all PDBs.
---所有pdb共享同一个alert日志.也就是说,alert日志是实例的日志,不是库的日志.
xml格式的alert可以在Diag Alert中找到,
text格式的alert可以在container database的Diag Trace中找到.
问:一个特定pdb的trace file在哪里?
答:所有pdb生成的所有trace文件都可以在container database的Diag Trace中找到.
你可以从v$diag_info 视图中找到细节.
杂项:
If a user-defined,common user creates schema objects in a PDB,and if later that PDB is unplugged
and plugged into a different CDB in which that common user does not exist,
then what happens to the schema objects?
By which user will they be owned?
Will other users within the PDB,which had been granted privileges on those schema objects,still retain those privileges?
If you plug a PDB that contains a common user into a CDB,then the following actions take place:
The common user accounts in this PDB lose commonly granted privileges that they may have had,
including the SET CONTAINER privilege.
If the target CDB has a common user with the same name as a common user in a newly plugged-in PDB,
then the new common user is merged with the target CDB common user. The password of the target CDB
common user takes precedence. Otherwise,a common user in a newly plugged in PDB becomes a locked account. In this case,you can do one of the following:
Leave the user account locked and use the objects of its schema.
Use Oracle Data Pump to copy these objects to another schema,
and then drop the locked user account.
Close the PDB,connect to the root,and then create a common user with the same name
as the locked account. When you re-open the PDB,Oracle Database resolves the differences
in the roles and privileges that were commonly granted to the locked user. Afterward,
you can unlock this user account in the PDB. Privileges and roles that were
locally granted to the user will remain unchanged.
Is the multitenant option available in Standard Edition?
Yes,but you may only create one PDB,per CDB
Can a transaction spanacross PDBS ? No,though "alter session set container" is allowed after starting a transaction in a PDB,
only select is allowed in the second PDB.Transaction is preserved and you can do
commit or rollback after switch back to original PDB
CDB_* views are container data objects. When a user connected to the root queries a CDB_* view,
the query results will depend on the CONTAINER_DATA attribute for users for the view.
The CONTAINER_DATA clause of the sql ALTER USER statement is used to set and modify
users' CONTAINER_DATA attribute.
In the root of a multitenant container database (CDB),CDB_* views can be used to
obtain information about tables,tablespaces,users,privileges,parameters,and so on
contained in the root and in pluggable databases (PDBs).
The CDB_* views are owned by SYS,regardless of who owns the underlying DBA_* view.
By default,a user connected to the root will only see data pertaining to the root.
Yes.
May NLS currency settings (NLS_CURRENCY et al) be set on a per-PDB basis?Yes.
How to monitor the undo usage of each container /database in CDB/PDB ?
select NAME,MAX(TUNED_UNDORETENTION),MAX(MAXQUERYLEN),MAX(NOSPACEERRCNT),MAX(EXPSTEALCNT)
from V$CONTAINERS c,V$UNDOSTAT u
where c.CON_ID=u.CON_ID
group by NAME;
select NAME,SNAP_ID,UNDOTSN,UNDOBLKS,TXNCOUNT,MAXQUERYLEN,MAXQUERYsqlID
from V$CONTAINERS c,DBA_HIST_UNDOSTAT u
where c.CON_ID=u.CON_ID
and u.CON_DBID=c.DBID
order by NAME;
1. Name collision might prevent schema-based consolidation. 2. Schema-based consolidation brings weak security. 3. Per-application,back-end,point-in-time recovery is prohibitively difficult. 4. Resource management between application back-ends is difficult. 5. Patching the Oracle version for a single application back-end is not possible. 6. Cloning a single application back-end is difficult.
