1、Oracle Multitenant多租户数据库
Oracle Multitenant 是 Oracle Database 12c的一个新选件,它提供的新架构允许多租户容器数据库(CDB)容纳多个可插拔数据库(PDB)。现有数据库可以直接采用,无需更改应用程序。Oracle Multitenant 是对 Oracle Real Application Clusters和 Oracle Active Data Guard等选件的全面补充。
一个多租户容器数据库中的多个可插拔数据库共享内存和后台进程。相比于旧架构,这样可以整合更多的可插拔数据库,提供与基于模式的整合类似的优点,但避免了该方法所需的重大应用程序更改。
使用新的 SQL 命令,创建可插拔数据库、在容器间移动可插拔数据库和克隆可插拔数据库只需几秒钟的时间。当底层文件系统支持瘦供应时,只需在 SQL命令中使用关键字“snapshot copy”,几乎瞬间即可克隆 TB级数据。
只需投入修补一个多租户容器数据库的时间和精力,即可修补所有多个可插拔数据库。要修补一个可插拔数据库,只需将其拔/插到不同的 Oracle Database软件版本中的多租户容器数据库。
通过将现有数据库整合为可插拔数据库,管理员可以将多个数据库作为一个管理。例如,在多租户容器数据库级别执行备份和灾难恢复等任务。
Oracle Database 12c 中对资源管理器进行了扩展,加入了特定功能来控制多租户容器数据库中可插拔数据库之间的资源竞争。
2、CDB的组件
Root:即CDB$ROOT,保存了元数据及通用用户。如PL/SQL包的源码就是元数据。通用用户是指每一个容器都知道的数据库用户。
The root, namedCDB$ROOT, stores Oracle-supplied metadata andcommon users. An example of metadata is the source code for Oracle-suppliedPL/SQL packages. A common user is a database user known in every container. ACDB has exactly one root.
Seed:即PDB$SEED,它是创建PDB的模板,我们不能添加或修改Seed中的对象,并且一个CDB只能有一个Seed。
The seed, named PDB$SEED, is a templatethat you can use to create new PDBs. You cannot add objects to or modifyobjects in the seed. A CDB has exactly one seed.
PDBs:一个PDB对于用户或应用程序来说就像一个普通的数据库。
A PDB appears to users and applications asif it were a non-CDB. For example, a PDB can contain the data and code requiredto support a specific application. A PDB is fully backward compatible withOracle Database releases before Oracle Database 12c.
3、common user与local user
一个CDB支持多个common user。Common user 在root与PDB中具有相同的标识,并且可以登录root与任何其拥有权限的PDB。Common user能够执行的操作取决于对common user的授权。另外,一些管理操作,如创建PDB,拔出PDB等需要由common user来执行。
一个local user只存在于一个PDB中。
A CDB supports common users. A common useris a user that has the same identity in the root and in every existing andfuture PDB. A common user can log in to the root and any PDB in which it hasprivileges. The operations that a common user can perform depend on theprivileges granted to the common user. Some administrative tasks, such ascreating a PDB or unplugging a PDB, must be performed by a common user. A CDBalso supports local users. A local user is a user that exists in exactly one PDB.
4、CDB的规划
创建CDB之前,需要了解下面的内容:
一个CDB可以包含253个PDB(去掉一个是seed还能创建252个PDB)。
Root,seed以及每一个PDB的数据文件是独立的。
一个单实例的CDB有一个redo log;RAC环境中每个实例有一个redo log。
只有一组后台进程,它们被ROOT和所有PDB共享。
Root的全局数据库名就是CDB的全局数据库名;PDB的全局数据库名是由PDB name和DB_DOMAIN参数共同定义的。
一个CDB使用一个SPFILE或者一个PFILE,其中为ROOT设置的参数的值可以被PDB继承。另外,也可以使用ALTER SYSTEM语句来设置PDB的参数。对参数文件执行操作时,必须使用common user并且使用AS SYSDBA, ASSYSOPER, AS SYSBACKUP来连接数据库。要创建CDB,必须要将初始化参数ENABLE_PLUGGABLE_DATABASE的值设置为TRUE。
所有PDB的字符集都使用CDB的字符集。
可以设置所有PDB的时区与CDB相同,也可以单独为PDB设置时区。
块大小被应用于整个CDB。
一个CDB也仅有一个控制文件(可以多路复用)。
Root与每一个PDB都有自己的SYSAUX表空间和SYSTEM表空间。可以为root和每一个PDB设置默认表空间。而默认的临时表空间对于整个CDB只有一个,但是可以为每个PDB创建临时表空间。
撤销表空间对于CDB是唯一的。在CDB中,初始化参数UNDO_MANAGEMENT必须被设置为AUTO。当前容器为PDB时,无法通过数据字典视图查看撤销表空间,只能通过动态性能视图。
可以为root或每个PDB创建服务。
5、创建CDB
在使用dbca创建数据库时,就可以指定数据库为CDB,并可以创建PDB。
6、连接到CDB$ROOT与PDB
6.1 连接到ROOT$CDB
可以使用操作系统认证方式连接到ROOT$CDB:
[oracle@oracle12c~]$ sqlplus "/as sysdba"
SQL*Plus:Release 12.1.0.2.0 Production on Sun Feb 22 16:36:17 2015
Copyright (c)1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With thePartitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> showcon_name
CON_NAME
------------------------------
CDB$ROOT
也可以在客户端使用tns连接到ROOT$CDB:
6.2 查看CDB与PDB
操作系统认证方式连接数据库(下面可以看到此时容器为ROOT):
[oracle@oracle12c~]$ sqlplus "/as sysdba"
SQL*Plus: Release12.1.0.2.0 Production on Sun Feb 22 16:36:17 2015
Copyright (c)1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With thePartitioning, OLAP, Advanced Analytics and Real Application Testing options
查看当前容器:
SQL> showcon_name
CON_NAME
------------------------------
CDB$ROOT
查看包含的PDB:
SQL> select pdb_name,status,logging from cdb_pdbs;
PDB_NAME
--------------------------------------------------------------------------------
STATUS LOGGING
------------------
PDB_1
NORMAL LOGGING
PDB$SEED
NORMAL LOGGING
看到此时CDB包含两个PDB,一个是自动创建的PDB$SEED,另一个是我们创建的PDB_1。
查看PDB状态信息:
SQL> selectname,open_mode,block_size from v$pdbs;
NAME OPEN_MODE BLOCK_SIZE
---------------------------------------- ----------
PDB$SEED READ ONLY 8192
PDB_1 READ WRITE 8192
关闭PDB_1,再查看状态:
SQL> alterpluggable database pdb_1 close immediate;
Pluggabledatabase altered.
SQL> selectname,open_mode from v$pdbs;
NAME OPEN_MODE
----------------------------------------
PDB$SEED READ ONLY
PDB_1 MOUNTED
看到close后,PDB_1状态为MOUNTED。
再次打开PDB_1:
SQL> alterpluggable database pdb_1 open;
Pluggabledatabase altered.
6.3 连接PDB
服务器上添加tns:
PDB_1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PDB_1)
)
)
[oracle@oracle12c admin]$ sqlplussys/oracle@pdb_1 as sysdba;
SQL*Plus: Release 12.1.0.2.0 Production onSat Feb 28 14:32:44 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise EditionRelease 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL> show con_name;
CON_NAME
------------------------------
PDB_1
6.4 查看PDB
数据文件:
对于PDB_1,数据文件包含:
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/pdb_1/system01.dbf
/u01/app/oracle/oradata/orcl/pdb_1/sysaux01.dbf
/u01/app/oracle/oradata/orcl/pdb_1/pdb_1_users01.dbf
对于CDB,数据文件包含:
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/orcl/pdb_1/system01.dbf
/u01/app/oracle/oradata/orcl/pdb_1/sysaux01.dbf
/u01/app/oracle/oradata/orcl/pdb_1/pdb_1_users01.dbf
9 rows selected.
联机日志文件:
对于PDB_1和CDB,它们使用相同的联机日志文件:
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
控制文件:
PDB_1与CDB都是用相同的控制文件:
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
7、关于用户
在ROOT中修改system口令:
SQL> alter user system identified bysystem;
User altered.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
尝试使用system的旧口令登录PDB_1:
[oracle@oracle12c admin]$ sqlplus system/oracle@pdb_1
SQL*Plus: Release 12.1.0.2.0 Production onSat Feb 28 15:01:16 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password;logon denied
使用system的新口令登录:
[oracle@oracle12c admin]$ sqlplussystem/system@pdb_1
SQL*Plus: Release 12.1.0.2.0 Production onSat Feb 28 15:02:56 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat Feb 282015 14:59:02 +08:00
Connected to:
Oracle Database 12c Enterprise EditionRelease 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, AdvancedAnalytics and Real Application Testing options
SQL> show con_name;
CON_NAME
------------------------------
PDB_1
可以看出默认情况下ROOT与PDB使用相同的用户,因此应该为每个PDB创建管理用户:
SQL> show con_name;
CON_NAME
------------------------------
PDB_1
SQL> create user pdb_1 identified bypdb_1;
User created.
SQL> grant create session to pdb_1;
Grant succeeded.
SQL> grant dba to pdb_1;
Grant succeeded.
再确认一下在ROOT中能否看见pdb_1用户:
SQL> select username from dba_userswhere username='PDB_1';
no rows selected
可以看出用户pdb_1专属于pdb_1数据库。
8、创建PDB
8.1 使用seed创建pdb
SQL>CREATE PLUGGABLE DATABASE pdb_2 ADMIN USER pdb_2 IDENTIFIED BY pdb_2
2 STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
3 DEFAULT TABLESPACE pdb_2
4 DATAFILE '/u01/app/oracle/oradata/orcl/pdb_2/pdb_201.dbf' SIZE 250MAUTOEXTEND ON
5 PATH_PREFIX = '/u01/app/oracle/oradata/orcl/pdb_2/'
6 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/pdbseed/',
7 '/u01/app/oracle/oradata/orcl/pdb_2/');
Pluggabledatabase created.
SQL>alter pluggable database pdb_2 open;
Pluggabledatabase altered.
创建完PDB需要手动将其open。
上面的语句定义PDB_2的所有表空间之和最大为2G,且对于共享的临时表空间最大可使用100M;
默认表空间为pdb_2,数据文件为pdb_201.dbf,大小为250M,自动增长;
FILE_NAME_CONVERT表示从pdbseed拷贝文件到pdb_2。
8.2 使用PDB_1创建PDB
SQL> CREATEPLUGGABLE DATABASE pdb_3 FROM pdb_1
2 PATH_PREFIX = '/u01/app/oracle/oradata/orcl/pdb_3/'
3 FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/pdb_1/','/u01/app/oracle/oradata/orcl/pdb_3/');
Pluggable databasecreated.
SQL> alterpluggable database pdb_3 open;
Pluggable databasealtered.
[oracle@oracle12cadmin]$ sqlplus pdb_1/pdb_1@pdb_3
SQL*Plus: Release12.1.0.2.0 Production on Sat Feb 28 15:25:20 2015
Copyright (c) 1982,2014, Oracle. All rights reserved.
Last Successful logintime: Sat Feb 28 2015 15:08:34 +08:00
Connected to:
Oracle Database 12cEnterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning,OLAP, Advanced Analytics and Real Application Testing options
SQL>
可以看出先前创建的用户也被复制了过来,如果不想复制数据则使用NO DATA子句:
在PDB_1中创建表:
SQL> showcon_name;
CON_NAME
------------------------------
PDB_1
SQL> create tabletest (c1 number);
Table created.
SQL> insert intotest values (1);
1 row created.
SQL> commit;
Commit complete.
使用PDB_1复制PDB_4:
SQL> CREATEPLUGGABLE DATABASE pdb_4 FROM pdb_1
2 PATH_PREFIX = '/u01/app/oracle/oradata/orcl/pdb_4/'
3 FILE_NAME_CONVERT =('/u01/app/oracle/oradata/orcl/pdb_1/','/u01/app/oracle/oradata/orcl/pdb_4/')
4 NODATA;
Pluggable databasecreated.
SQL> alterpluggable database open;
alter pluggabledatabase open
连接PDB_4并查询测试表:
[oracle@oracle12cadmin]$ sqlplus pdb_1/pdb_1@pdb_4
SQL*Plus: Release12.1.0.2.0 Production on Sat Feb 28 15:40:27 2015
Copyright (c) 1982,2014, Oracle. All rights reserved.
Last Successful logintime: Sat Feb 28 2015 15:35:34 +08:00
Connected to:
Oracle Database 12cEnterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning,OLAP, Advanced Analytics and Real Application Testing options
SQL> select * fromtest;
no rows selected
可以看出使用NO DATA子句是拷贝除了表数据之外的内容,包括表结构。
9、关于PDB的自动启动:
可以通过添加Trigger的形式来客制化startup时自动将PDB OPEN:
CREATE TRIGGERopen_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable databaseall open';
END open_all_pdbs;
/