eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

December 9, 2016

Oracle 12c 多租户:未完成事务的隐式提交(commit)

在前面的测试过程中,偶然发现12.1版本的一个表现,确切的说,应该是在12.1.0.1版本中。当关闭数据库前,当前会话中,未被提交的事务将会隐式被提交。

[oracle@12c01db trace]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Dec 1 16:48:42 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
	 2 PDB$SEED			  READ ONLY
	 3 YHEM 			  MOUNTED

SQL> alter session set container=YHEM;

Session altered.

SQL> startup
Pluggable Database opened.

SQL> alter session set current_schema=EYGLE;

Session altered.

SQL> create table enmotech as select con_id,name,open_mode from v$pdbs;

Table created.

SQL> select * from enmotech;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
	 3 YHEM 			  READ WRITE

SQL> update enmotech set con_id=10000+con_id;

1 row updated.

SQL> select * from enmotech;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
     10003 YHEM 			  READ WRITE

SQL> shutdown immediate;
Pluggable Database closed.

正常情况下,这个未完成的事务应当被回滚。

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database yhem open;

Pluggable database altered.

SQL> alter session set container=YHEM;

Session altered.

SQL> select * from eygle.enmotech;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ ----------
     10003 YHEM 			  READ WRITE

在12.1.0.2中已经改变。注意在以下的测试中,直接关闭PDB,Oracle会提示事务在进行中,需要提交或者回滚。此时abort命令拥有了进一步的实质性含义,不再提示,直接停止了PDB。当然最后事务被成功回退。

[oracle@enmocoredb ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 1 17:06:53 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> col name for a30
SQL> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME 			  OPEN_MODE
---------- ------------------------------ --------------------
	 2 PDB$SEED			  READ ONLY
	 3 PDBPROD1			  READ WRITE
	 4 PDBPROD2			  MOUNTED

SQL> alter session set container=PDBPROD1;

Session altered.

SQL> create table enmotech as select con_id,name from v$pdbs;

Table created.

SQL> select * from enmotech;

    CON_ID NAME
---------- ------------------------------
	 3 PDBPROD1

SQL> update enmotech set con_id=10000+con_id;

1 row updated.

SQL> select * from enmotech;

    CON_ID NAME
---------- ------------------------------
     10003 PDBPROD1

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction - commit or rollback first


SQL> shutdown abort;
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> select * from enmotech;

    CON_ID NAME
---------- ------------------------------
	 3 PDBPROD1

一个新特性的引入,往往是牵一发动全身的改变,完善就需要从头再来。

Posted by eygle at 9:01 AM | Permalink | Oracle12c/11g (118)

December 8, 2016

Oracle 12.2 新特性:在线PDB数据库克隆(Pluggable Hot Clone)

在Oracle 12c的多租户(Multitenant)特性中,可以通过灵活的克隆复制来创建PDB(Pluggable Database),使得运维的工作大大简化。但是在12.1中,进行数据库Clone时,源数据库需要置于Read Only模式,即影响源数据库的使用,又会带来时间窗口的不便。

在12.2中,Oracle引入了 Hot Clone 技术,可以通过在线的方式,对源库进行复制。大大简化的运维的复杂性。

PDBHotClone.jpg

以下测试向大家展示了这一新特性的用法和功能。首先在PDB1执行一个数据表的连续插入动作,维持活动事务(注意:最后中断是因为克隆已经完成了):

SQL> connect eygle/eygle@pdb1
Connected.
SQL> create table enmotech ( id number, dt timestamp );

Table created.

Elapsed: 00:00:01.17
SQL> begin
  2   for i in 1 .. 600 loop
  3  	insert into enmotech values (i, systimestamp );
  4  	commit;
  5  	dbms_lock.sleep(2);
  6   end loop;
  7  end;
  8  /
^Cbegin
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_LOCK", line 205
ORA-06512: at line 5


Elapsed: 00:04:20.96

选择PDB1作为克隆复制的源数据库,在源数据库保持活动的状态下,进行Clone复制:

[oracle@pg1-enmotech-com ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 1 03:22:50 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select con_id,name from v$datafile where con_id=3;

    CON_ID
----------
NAME
--------------------------------------------------------------------------------
	 3
/u01/app/oracle/oradata/ORCL/3F605C8D8A4206B6E0536838A8C04ACF/datafile/o1_mf_system_d0n37tyk_.dbf

	 3
/u01/app/oracle/oradata/ORCL/3F605C8D8A4206B6E0536838A8C04ACF/datafile/o1_mf_sysaux_d0n37tyx_.dbf

	 3

    CON_ID
----------
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/3F605C8D8A4206B6E0536838A8C04ACF/datafile/o1_mf_undotbs1_d0n37tyy_.dbf

	 3
/u01/app/oracle/oradata/ORCL/3F605C8D8A4206B6E0536838A8C04ACF/datafile/o1_mf_users_d0n38fdr_.dbf

接下来执行数据库克隆的操作,无需对源库执行任何操作:

SQL> ! mkdir /u01/app/oracle/oradata/ORCL/enmotech

SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/ORCL/enmotech';

System altered.

SQL> create pluggable database yhem from pdb1 ;

Pluggable database created.

克隆复制完成之后,可以打开数据库,查看数据,之前的事务数据已经被部分的复制过来:

SQL> alter pluggable database yhem open;

Pluggable database altered.

SQL> alter session set container=YHEM;

Session altered.

SQL> alter session set current_schema=EYGLE;

Session altered.

SQL> select count(*) from enmotech;

  COUNT(*)
----------
	10

SQL> select * from enmotech; 

	ID DT
---------- ------------------------------------------------------
	 1 01-DEC-16 03.37.06.539694 AM
	 2 01-DEC-16 03.37.08.556754 AM
	 3 01-DEC-16 03.37.10.558066 AM
	 4 01-DEC-16 03.37.12.563268 AM
	 5 01-DEC-16 03.37.14.564449 AM
	 6 01-DEC-16 03.37.16.570639 AM
	 7 01-DEC-16 03.37.18.571286 AM
	 8 01-DEC-16 03.37.20.571668 AM
	 9 01-DEC-16 03.37.22.903123 AM
	10 01-DEC-16 03.37.24.902948 AM

10 rows selected.

通过告警日志记录的信息,可以看到整个动作的核心步骤,实际上Oracle做了不完全恢复,最后在指定的SCN打开了数据库:

2016-12-01T03:35:43.207319+08:00
ALTER SYSTEM SET db_create_file_dest='/u01/app/oracle/oradata/ORCL/enmotech' SCOPE=BOTH;
2016-12-01T03:37:08.934023+08:00
create pluggable database yhem from pdb1 
2016-12-01T03:37:08.989913+08:00
PDB1(3): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2016-12-01T03:37:29.640510+08:00
YHEM(5):Endian type of dictionary set to little
****************************************************************
Pluggable Database YHEM with pdb id - 5 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x00000000000000e0
****************************************************************
YHEM(5):Media Recovery Start
2016-12-01T03:37:30.026018+08:00
YHEM(5):Serial Media Recovery started
2016-12-01T03:37:30.154319+08:00
YHEM(5):Recovery of Online Redo Log: Thread 1 Group 2 Seq 68 Reading mem 0
YHEM(5):  Mem# 0: /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_d0n2yzro_.log
YHEM(5):  Mem# 1: /u01/app/oracle/fast_recovery_area/orcl/ORCL/onlinelog/o1_mf_2_d0n2z0vm_.log
2016-12-01T03:37:30.346568+08:00
YHEM(5):Incomplete Recovery applied until change 2198639 time 12/01/2016 03:37:26
2016-12-01T03:37:30.353390+08:00
YHEM(5):Media Recovery Complete (orcl)
YHEM(5):Autotune of undo retention is turned on. 
2016-12-01T03:37:36.701219+08:00
YHEM(5):[15846] Successfully onlined Undo Tablespace 2.
YHEM(5):Undo initialization finished serial:0 start:139692076 end:139692182 diff:106 ms (0.1 seconds)
YHEM(5):Database Characterset for YHEM is AL32UTF8
YHEM(5):JIT: pid 15846 requesting stop
Completed: create pluggable database yhem from pdb1 
2016-12-01T03:38:32.727311+08:00
alter pluggable database yhem open
YHEM(5):Autotune of undo retention is turned on. 
2016-12-01T03:38:32.850162+08:00
YHEM(5):Endian type of dictionary set to little
YHEM(5):[15846] Successfully onlined Undo Tablespace 2.
YHEM(5):Undo initialization finished serial:0 start:139748579 end:139748829 diff:250 ms (0.2 seconds)
YHEM(5):Deleting old file#9 from file$ 
YHEM(5):Deleting old file#10 from file$ 
YHEM(5):Deleting old file#11 from file$ 
YHEM(5):Deleting old file#12 from file$ 
YHEM(5):Deleting old file#13 from file$ 
YHEM(5):Adding new file#18 to file$(old file#9) 
YHEM(5):Adding new file#19 to file$(old file#10) 
YHEM(5):Adding new file#20 to file$(old file#11) 
YHEM(5):Adding new file#21 to file$(old file#12) 
YHEM(5):Successfully created internal service yhem at open
****************************************************************
Post plug operations are now complete.
Pluggable database YHEM with pdb id - 5 is now marked as NEW.
****************************************************************
YHEM(5):Database Characterset for YHEM is AL32UTF8
2016-12-01T03:38:33.998091+08:00
YHEM(5):Opatch validation is skipped for PDB YHEM (con_id=0)
2016-12-01T03:39:01.596335+08:00
YHEM(5):Opening pdb with no Resource Manager plan active
2016-12-01T03:39:12.433441+08:00
Pluggable database YHEM opened read write
Completed: alter pluggable database yhem open
2016-12-01T03:46:45.511090+08:00
Starting control autobackup

Control autobackup written to DISK device

handle '/u01/app/oracle/fast_recovery_area/orcl/PG1/autobackup/2016_12_01/o1_mf_s_929418405_d3yc55s5_.bkp'


这就是Oracle 12.2的重要增强之一,关于PDB的持续改进。

Posted by eygle at 6:43 PM | Permalink | Oracle12c/11g (118)

Oracle 12c 新特性: 多租户 PDB 的克隆与维护

Multitenant12c.jpg

这部分内容节选自《深入解析Oracle-数据库的初始化》一书,本节内容针对Oracle 12.1.0.1版本。

在PDB的创建过程中,除了通过种子PDB创建新的空PDB之外,还可以通过一个现有的用户PDB克隆创建新的PDB数据库。

以下详细记录一个PDB的创建与访问过程。 创建PDB的源需要置于只读模式:

SQL> CREATE PLUGGABLE DATABASE yunh FROM enmo;
CREATE PLUGGABLE DATABASE yunh FROM enmo
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode

SQL> ALTER PLUGGABLE DATABASE enmo CLOSE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE enmo OPEN READ ONLY;

Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE yunh FROM enmo;

Pluggable database created.

SQL> select name, open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
ENMO			       READ ONLY
YUNH			       MOUNTED


随后可以打开这个新创建的PDB:

SQL> ALTER PLUGGABLE DATABASE yunh OPEN;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
ENMO			       READ ONLY
YUNH			       READ WRITE


检查数据库的告警日志文件,可以看到,新创建的数据库,其服务名已经被自动添加到数据库的服务名配置中:

Mon Oct 22 12:53:57 2012
ALTER PLUGGABLE DATABASE yunh OPEN
ALTER SYSTEM SET service_names='enmo','eygle','yunh' SCOPE=MEMORY SID='eygle';
Completed: ALTER PLUGGABLE DATABASE yunh OPEN


检查数据库监听器,可以看到PDB都已经被监听器监听服务:

bash-4.1$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.0.1 - Beta on 22-OCT-2012 13:16:33
Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.0.1 - Beta
Start Date                22-OCT-2012 10:44:02
Uptime                    0 days 2 hr. 32 min. 31 sec
Trace Level               support
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.01/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listener Trace File       /u01/app/oracle/diag/tnslsnr/localhost/listener/trace/ora_2637_140488411366176.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "enmo" has 1 instance(s).
  Instance "eygle", status READY, has 1 handler(s) for this service...
Service "eygle" has 1 instance(s).
  Instance "eygle", status READY, has 1 handler(s) for this service...
Service "yunh" has 1 instance(s).
  Instance "eygle", status READY, has 1 handler(s) for this service...
The command completed successfully


在12c的建库过程中,引入了Perl脚本的调用方式,以下是在创建过程中跟踪到的脚本调用,在数据库创建的日志中也可以观察这种方式:

bash-4.1$ ps -ef|grep perl
oracle    3777  3115  0 17:21 pts/2    00:00:02 /u01/app/oracle/product/12.01.24/dbhome_1/perl/bin/perl /u01/app/oracle/product/12.01.24/dbhome_1/rdbms/admin/catcon.pl -l /u01/app/oracle/cfgtoollogs/dbca/yunh -b catproc /u01/app/oracle/product/12.01.24/dbhome_1/rdbms/admin/catproc.sql


这个过程完成之后,会在告警日志文件中记录如下信息:

SERVER COMPONENT id=CATPROC: timestamp=2012-10-23 17:58:36


在tnsnames.ora文件中,增加相应的配置,就可以通过服务名连接数据库了,以下是两个PDB的本地网络服务名配置:

ENMO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = enmo )
    )
  )
YUNH =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = yunh )
    )
  )


对于PDB的一些更改操作不能在CDB级别进行,CDB级操作会提示不能在PDB之外执行,如以下更改GLOBAL_NAME的操作:

SQL> connect / as sysdba
Connected.
SQL> ALTER PLUGGABLE DATABASE yunh RENAME GLOBAL_NAME to yunh;
ALTER PLUGGABLE DATABASE yunh RENAME GLOBAL_NAME to yunh
                                                    *
ERROR at line 1:
ORA-65046: operation not allowed from outside a pluggable database


连接到PDB以RESTRICTED模式可以进行这些修改:

SQL> connect sys/oracle@yunh as sysdba
Connected.
SQL> ALTER PLUGGABLE DATABASE YUNH CLOSE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE OPEN RESTRICTED;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE RENAME GLOBAL_NAME TO YUNHE;

Pluggable database altered.

SQL> SELECT SERVICE_ID,NAME,PDB FROM V$SERVICES;


SERVICE_ID NAME 		PDB
---------- -------------------- ------------------------------
	 3 yunhe		YUNHE
	 3 yunh 		YUNHE


在PDB创建完成之后,可以通过SYSDBA连接到PDB,执行维护操作,这和常规的Non-CDB数据库没有差别,在PDB中,只要具备足够的权限,可以创建表空间、数据文件、用户和数据对象等。

以下通过SYS用户连接到一个名为ENMO的 PDB数据库:

SQL> connect sys/oracle@enmo as sysdba
Connected.
SQL> select name from v$datafile;

NAME
-------------------------------------------------------------------------------------
/u01/app/oracle/oradata/julia/undotbs01.dbf
/u01/app/oracle/oradata/julia/JULIA/datafile/o1_mf_system_88f89nmp_.dbf
/u01/app/oracle/oradata/julia/JULIA/datafile/o1_mf_sysaux_88f89nko_.dbf


在PDB中执行用户及表空间创建命令:

SQL> create user eygle identified by eygle ;

User created.

SQL> create tablespace enmo datafile size 20M; 

Tablespace created.

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------
/u01/app/oracle/oradata/julia/undotbs01.dbf
/u01/app/oracle/oradata/julia/JULIA/datafile/o1_mf_system_88f89nmp_.dbf
/u01/app/oracle/oradata/julia/JULIA/datafile/o1_mf_sysaux_88f89nko_.dbf
/u01/app/oracle/oradata/julia/JULIA/datafile/o1_mf_enmo_88h0l71p_.dbf


执行用户管理,分配空间、更改默认表空间等:

SQL> alter user eygle default tablespace enmo;

User altered.

SQL> alter user eygle quota unlimited on enmo;

User altered.

SQL> grant connect,resource to eygle;

Grant succeeded.


通过指定用户连接,可以创建数据对象,以下测试以SCOTT用户脚本为例创建:

SQL> connect eygle/eygle@enmo
Connected.

SQL> CREATE TABLE DEPT
  2  	    (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  3  	     DNAME VARCHAR2(14) ,
  4  	     LOC VARCHAR2(13) ) ;

Table created.

SQL> CREATE TABLE EMP
  2  	    (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  3  	     ENAME VARCHAR2(10),
  4  	     JOB VARCHAR2(9),
  5  	     MGR NUMBER(4),
  6  	     HIREDATE DATE,
  7  	     SAL NUMBER(7,2),
  8  	     COMM NUMBER(7,2),
  9  	     DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

Table created.

SQL> INSERT INTO DEPT VALUES
  2  	     (10,'ACCOUNTING','NEW YORK');
1 row created.
SQL> INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
1 row created.
SQL> INSERT INTO DEPT VALUES
  2  	     (30,'SALES','CHICAGO');
1 row created.
SQL> INSERT INTO DEPT VALUES
  2  	     (40,'OPERATIONS','BOSTON');
1 row created.
SQL> INSERT INTO EMP VALUES
  2  (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
1 row created.

...........

SQL> CREATE TABLE BONUS
  2  	     (
  3  	     ENAME VARCHAR2(10)      ,
  4  	     JOB VARCHAR2(9)  ,
  5  	     SAL NUMBER,
  6  	     COMM NUMBER
  7  	     ) ;

Table created.

SQL> CREATE TABLE SALGRADE
  2  	   ( GRADE NUMBER,
  3  	     LOSAL NUMBER,
  4  	     HISAL NUMBER );

Table created.

SQL> INSERT INTO SALGRADE VALUES (1,700,1200);
1 row created.

。。。。。。。

SQL> COMMIT;

Commit complete.


查看这些信息:

SQL> connect sys/oracle@enmo as sysdba
Connected.
SQL> select table_name from dba_tables where owner='EYGLE';

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE


这些信息在CDB级别的数据库中是不可见的:

SQL> connect / as sysdba
Connected.
SQL> select table_name from dba_tables where owner='EYGLE';

no rows selected


通常情况下,跨数据库的数据访问,需要通过DB Link进行,如以下测试范例:

SQL> create database link enmo connect to eygle identified by eygle using 'enmo';

Database link created.

SQL> select * from dept@enmo;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	40 OPERATIONS	  BOSTON

这就是基于Pluggable Database的创建与克隆过程,这个过程在12.2中已经被改进,有所不同。

Posted by eygle at 3:38 PM | Permalink | Oracle12c/11g (118)

December 6, 2016

Oracle 12c:多租户数据库克隆 ORA-01276 错误处理一例

在使用12c多租户模式,克隆创建PDB时遇到如下 ORA-01276 错误:

SQL> 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
SQL> create pluggable database yhem from pdb1
  2  file_name_convert=('/u01/app/oracle/oradata/ORCL/3F605C8D8A4206B6E0536838A8C04ACF','/u01/app/oracle/oradata/ORCL/enmotech');
create pluggable database yhem from pdb1
*
ERROR at line 1:
ORA-01276: Cannot add file
/u01/app/oracle/oradata/ORCL/enmotech/datafile/o1_mf_system_d0n37tyk_.dbf.
File has an Oracle Managed Files file name.

这个错误是由于使用了OMF管理模式后,不允许显示的指定目录转换的映射关系。
可以通过设置DB_CREATE_FILE_DEST参数指向新的PDB目录,执行PDB Clone创建即可。
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/ORCL/enmotech';

System altered.

SQL> create pluggable database yhem from pdb1 ;

Pluggable database created.

SQL> select name from v$datafile where con_id=5;

NAME
------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/enmotech/PG1/428AB277A7FC3DE6E0530381250AFAB1/datafile/o1_mf_system_d3ybm4z6_.dbf
/u01/app/oracle/oradata/ORCL/enmotech/PG1/428AB277A7FC3DE6E0530381250AFAB1/datafile/o1_mf_sysaux_d3ybm50s_.dbf
/u01/app/oracle/oradata/ORCL/enmotech/PG1/428AB277A7FC3DE6E0530381250AFAB1/datafile/o1_mf_undotbs1_d3ybm50w_.dbf
/u01/app/oracle/oradata/ORCL/enmotech/PG1/428AB277A7FC3DE6E0530381250AFAB1/datafile/o1_mf_users_d3ybm50y_.dbf

SQL> alter pluggable database yhem open;

Pluggable database altered.

SQL> alter session set container=YHEM;

Session altered.

SQL> alter session set current_schema=EYGLE;

Session altered.

SQL> select count(*) from enmotech;

  COUNT(*)
----------
	10

在引入了多租户特性之后,数据库的维护发生了很多改变。记录一下。


Posted by eygle at 12:12 PM | Permalink | Oracle12c/11g (118)

December 2, 2016

Oracle 12c多租户新特性:Common用户和Local用户的管理

编辑手记:这一节我们将介绍多租户架构中用户及权限的变化,全局用户和本地用户,管理方式和内部实现,这篇文章来自<深入解析Oracle>一书的摘录。云和恩墨 的很多客户已经采用多租户特性进行生产部署,这一技术早已经走入实践

无论在CDB和Non-CDB数据库中,用户都拥有一个Schema,拥有一系列的Schema对象,在CDB中由于PDB的引入,用户范畴有所不同。

在CDB模式下,公用用户(Common User)和本地用户(Local User)两个概念被引入进来,公用用户可以在CDB和PDB中同时存在,能够连接ROOT和PDB进行操作;而本地用户则只在特定的PDB中存在,也只能在特定的PDB中执行操作;在PDB中不能创建公用用户,而在CDB中(CDB$ROOT中)同样不能创建本地用户。


在CDB中创建的公用用户要求以c##或C## 开头,以下测试以常规方式命名的用户将会创建失败,符合规则的用户可以被创建:

SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> create user eygle identified by eygle;
create user eygle identified by eygle
            *
第 1 行出现错误:
ORA-65096: 公用用户名或角色名无效

SQL> create user c##eygle identified by eygle;

用户已创建。

当创建公用用户时,Oracle会向每个PDB中同时创建该用户,如果PDB未打开,则创建工作会以任务的方式延后。

以下查询显示数据库中只在容器1中存在新创建的用户:

SQL> select username,common,con_id from cdb_users where username like '%EYGLE%';

USERNAME                       COM     CON_ID
------------------------------ --- ----------
C##EYGLE                       YES          1

此时打开PDB,则数据库会自动完成之前挂起的内部创建工作:

SQL> alter pluggable database enmo open;

插接式数据库已变更。

SQL> select username,common,con_id from cdb_users where username like '%EYGLE%';

USERNAME                       COM     CON_ID
------------------------------ --- ----------
C##EYGLE                       YES          1
C##EYGLE                       YES          3


SQL> drop user c##eygle;

用户已删除。

下图描述了公用用户和本地用户的区别:

cdbpdb.png

在拥有了CREATE SESSION权限后,公用用户能够登陆包括Root在内的任何Container。公用用户一般在每个PDB中都存在对应的用户信息,在PDB中不能存在与公用用户重名的用户,如初始的SYS和SYSTEM用户都属于公用用户。也只有公用用户能够授权或被授权相应的公用角色和权限。
公用权限是指对所有Container都有效的系统或者对象权限,例如一个公用用户被授予了公用权限 CREATE ANY TABLE WITH ADMIN OPTION 可以将这个权限转授给其他公用用户.公用用户之外的权限被称为本地权限(Local Privilege).
公用角色是指在所有Container中都可见的角色,这些角色可能包含全局和本地权限。本地角色只能包含本地权限。授予公用角色的公用权限,对于具有该角色的用户在任何可以连接的Container中都将具有该权限。
以下是一些相关的常识性介绍:

  • 一个公用用户在不同Container中的Schema可以不同;
  • 本地用户只能在各自的PDB中进行操作,在不同PDB中可以存在同名的本地用户;
  • PDB中的本地用户不能登陆其他PDB或ROOT;
  • PDB的本地用户只需要在本PDB内保持用户名唯一;
  • 本地用户能否访问一个公用Schema中的对象取决于其拥有的具体权限;
  • PDB能够通过DB Link访问其他的PDB;

在CDB、PDB模式下,一个权限在被授权的Container中存在。因此,在PDB中授予的本地权限和角色和在Non-CDB中没有不同,例如,在PDB HRPDB中授予本地用户HR的 SELECT ANY TABLE权限,仅在该PDB中生效。
相对而言,公用权限和角色可以跨越Container生效,当需要跨Container进行操作时,需要公用权限或角色,并且这些权限需要在现有和将来创建的Container中生效。
在CDB中,每个权限或者是在某个Container中的本地权限,或者是在所有Container中生效的公用权限。公用权限确保公用用户无需在不同PDB中重复授权。
类似CREATE ANY TABLE的权限,其自身既不是公用权限也不是本地权限,如果一个用户通过CONTAINER=CURRENT方式授权,则被授权用户拥有的是本地权限;如果一个权限通过CONTAINER=ALL方式授权,则用户获得的是公用权限。因此,一个权限称其为本地或公用权限,完全依赖于其授权方式。

在CDB中,每个角色或者是基于PDB的本地角色,或者是对全体PDB生效的公用角色,所有系统提供的角色(如DBA)都属于公用角色。

在视图DBA_USERS和CDB_USERS中都包含了一个字段COMMON,用于标识公用用户和本地用户。

SQL> select username,common from dba_users;

USERNAME		       COM
------------------------------ ---
AUDSYS			       YES
SYSKM			       YES
XS$NULL 		       YES
XDB			       YES
WMSYS			       YES
DBSNMP			       YES
SYSDG			       YES
SYSBACKUP		       YES
SYS			       YES
SYSTEM			       YES

以下查询显示SYSTEM作为公用用户在四个容器中存在:

SQL> select username,common,con_id from cdb_users where username='SYSTEM';

USERNAME		       COM     CON_ID
------------------------------ --- ----------
SYSTEM			       YES	    1
SYSTEM			       YES	    2
SYSTEM			       YES	    3
SYSTEM			       YES	    4

数据库中存在17个公用用户:

SQL> select count(distinct username) from cdb_users where common='YES';

COUNT(DISTINCTUSERNAME)
-----------------------
		     17

以下查询列出了数据库中的本地用户:

SQL> select username,con_id from cdb_users where common='NO';

USERNAME			   CON_ID
------------------------------ ----------
ENMO					3
EYGLE					3
JULIA					3
ENMO					4
EYGLE					4
JULIA					4

通过指定CONTAINER可以限定创建用户的类型,当使用ALL选项时,以下命令就创建了一个名为APPADMIN的公用用户:

SQL> create user c##admin identified by appadmin container=ALL;

User created.

SQL> select username,common,con_id from cdb_users where username='C##ADMIN';

USERNAME		       COM     CON_ID
------------------------------ --- ----------
C##ADMIN		       YES	    1
C##ADMIN		       YES	    3
C##ADMIN		       YES	    4

查询dba_users视图,可以看到APPADMIN的相关用户属性:

SQL> select username,common from dba_users where username='C##ADMIN';

USERNAME		       COM
------------------------------ ---
C##ADMIN		       YES

注意,在CDB$ROOT中不能创建本地用户或角色:

SQL> create user appdba identified by appdba container=CURRENT;
create user appdba identified by appdba container=CURRENT
                                 *
第 1 行出现错误:
RA-65049: 不允许在 CDB$ROOT 中创建本地用户或角色

在PDB中才可以创建本地用户,以下测试首先连接到PDB(名称为ENMO)中,连接用户具备DBA权限可以创建用户:

SQL> connect eygle/eygle@enmo
Connected.
SQL> show con_id con_name

CON_ID
------------------------------
3

CON_NAME
------------------------------
ENMO

当然在PDB中也不允许创建公用用户:

SQL> create user appdba identified by appdba container=ALL;
create user appdba identified by appdba container=ALL
                                 *
第 1 行出现错误:
ORA-65050: 只允许在 CDB$ROOT 中执行公用 DDL
同样在PDB中也不能删除公用用户:
SQL> drop user c##eygle;
drop user c##eygle
*
第 1 行出现错误:
ORA-65050: 只允许在 CDB$ROOT 中执行公用 DDL
以下SQL成功在PDB下创建了本地用户:
SQL> create user appdba identified by appdba container=CURRENT;

User created.


SQL> select username,common,con_id from cdb_users where username='APPDBA';

USERNAME		       COM     CON_ID
------------------------------ --- ----------
APPDBA			       NO	    3
类似的,本地用户不能被授予公用权限或角色,以下尝试在全局授权的命令会返回明确的错误:
SQL> select username,common from dba_users where username='APPDBA';

USERNAME		       COM
------------------------------ ---
APPDBA			       NO

SQL> grant create session to appdba container=ALL;
grant create session to appdba container=ALL
*
第 1 行出现错误:
ORA-65030: 用户无法向本地用户或角色授予公用权限
在PDB内授予本地权限之后,新创建的用户可以登陆本地PDB数据库:
SQL> grant create session to appdba ;

Grant succeeded.

SQL> connect appdba/appdba@enmo
Connected.
SQL> show con_id con_name

CON_ID
------------------------------
3

CON_NAME
------------------------------
ENMO
下面来研究一下角色。在CDB_ROLES视图可以查询CDB的角色信息,以下查询可以看到由于PDB的引入,角色记录大大增加:
SQL> select count(distinct role),count(*) from cdb_roles;

COUNT(DISTINCTROLE)   COUNT(*)
------------------- ----------
		 44	   174
对于DBA公用角色来说,在每个Container中都存在相应的信息记录:
SQL> select role,common,con_id from cdb_roles where role='DBA';

ROLE			       COM     CON_ID
------------------------------ --- ----------
DBA			       YES	    1
DBA			       YES	    2
DBA			       YES	    3
DBA			       YES	    4
而对于PDB_DBA角色,仅在PDB中存在:
SQL> select role,common,con_id from cdb_roles where role='PDB_DBA';

ROLE			       COM     CON_ID
------------------------------ --- ----------
PDB_DBA 		       NO	    3
PDB_DBA 		       NO	    4
SQL> select con_id,name from v$pdbs;

    CON_ID NAME
---------- ------------------------------
	 2 PDB$SEED
	 3 ENMO
	 4 YUNH
同用户管理类似,在CDB$ROOT中可以建立公用角色,但是不能创建本地角色,公用角色在每个PDB中都存在,同样需要以c##为前缀开头:
SQL> show con_id con_name

CON_ID
------------------------------
1

CON_NAME
------------------------------
CDB$ROOT
SQL> create role APPROLE container=ALL;
create role APPROLE container=ALL
            *
第 1 行出现错误:
ORA-65096: 公用用户名或角色名无效

SQL> create role C##ROLE container=ALL;

角色已创建。

SQL> select role,common,con_id from cdb_roles where role='C##ROLE';

ROLE			       COM     CON_ID
------------------------------ --- ----------
C##ROLE 		       YES	    1
C##ROLE 		       YES	    3
C##ROLE 		       YES	    4

SQL> create role APPADMIN container=CURRENT;
create role APPADMIN container=CURRENT
*
第 1 行出现错误:
ORA-65049: 不允许在 CDB$ROOT 中创建本地用户或角色
在PDB中,同样不能创建公用角色,仅能创建本地角色:
SQL> connect sys/oracle@enmo as sysdba
Connected.
SQL> select count(role) from dba_roles;

COUNT(ROLE)
-----------
	 45

SQL> create role COMMON_ROLE container=ALL;
create role COMMON_ROLE container=ALL
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT


SQL> create role APPENMO container=CURRENT;

Role created.

SQL> select role,common from dba_roles where role='APPENMO';

ROLE			       COM
------------------------------ ---
APPENMO 		       NO

SQL> select count(role) from dba_roles;

COUNT(ROLE)
-----------
	 46
对于系统权限和对象权限,CDB相应的增加了对应视图用于存储这些信息:
SQL> connect / as sysdba
Connected.
SQL> desc system_privilege_map
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRIVILEGE				   NOT NULL NUMBER
 NAME					   NOT NULL VARCHAR2(40)
 PROPERTY				   NOT NULL NUMBER

SQL> desc table_privilege_map
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRIVILEGE				   NOT NULL NUMBER
 NAME					   NOT NULL VARCHAR2(40)

SQL> desc cdb_sys_privs
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE					    VARCHAR2(128)
 PRIVILEGE					    VARCHAR2(40)
 ADMIN_OPTION					    VARCHAR2(3)
 COMMON 					    VARCHAR2(3)
 CON_ID 					    NUMBER

SQL> desc cdb_tab_privs
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRANTEE					    VARCHAR2(128)
 OWNER						    VARCHAR2(128)
 TABLE_NAME					    VARCHAR2(128)
 GRANTOR					    VARCHAR2(128)
 PRIVILEGE					    VARCHAR2(40)
 GRANTABLE					    VARCHAR2(3)
 HIERARCHY					    VARCHAR2(3)
 COMMON 					    VARCHAR2(3)
 TYPE						    VARCHAR2(24)
 CON_ID 					    NUMBER
在CDB中可以像在NON-CDB的数据库中一样进行权限授予与回收:
SQL> connect sys/oracle@enmo as sysdba
Connected.
SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee='APPDBA';

GRANTEE 	     PRIVILEGE				      COM     CON_ID
-------------------- ---------------------------------------- --- ----------
APPDBA		     CREATE SESSION			      NO	   3

SQL> grant create table to appdba;

Grant succeeded.

SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee='APPDBA';     

GRANTEE 	     PRIVILEGE				      COM     CON_ID
-------------------- ---------------------------------------- --- ----------
APPDBA		     CREATE TABLE			      NO	   3
APPDBA		     CREATE SESSION			      NO	   3
SQL> revoke create table from appdba;

Revoke succeeded.

SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee='APPDBA';   

GRANTEE 	     PRIVILEGE				      COM     CON_ID
-------------------- ---------------------------------------- --- ----------
APPDBA		     CREATE SESSION			      NO	   3

欢迎加入『云和恩墨大讲堂』,参与我们的学习和讨论。
YhemTalk.jpeg

Posted by eygle at 6:23 PM | Permalink | Oracle12c/11g (118)

近期发表

  • Oracle 12c 多租户:PDB 支持 abort 关闭么? - December 1, 2016
  • 关于ORA-20315数据库攻击事件的核心 AfterConnect.sql - November 16, 2016
  • 【红色警报】近期Oracle数据库遭受比特币勒索攻击原因揭秘和预防 - November 16, 2016
  • 2016.11.4日-第六届Oracle技术嘉年华与你相约 - October 10, 2016
  • ORA-600 kghstack_underflow_internal_2 错误及BUG提示 - June 28, 2016
  • 2016 ACOUG Asia Tour 西安站 - 西北长安 软件园区 - May 29, 2016
  • 2016 ACOUG Asia Tour 兰州站 - 黄河之滨 数据为王 - May 28, 2016
  • 2016 DTCC 回顾:DevOps时代SQL审核潮流渐成 - May 20, 2016
  • 2016 ACOUG Asia Tour 济南站 - 花好月圆 饮水泉城 - April 24, 2016
  • 2016 ACOUG Asia Tour 南京站 - 六朝古都 金陵王气 - April 23, 2016


  • CopyRight © 2004 ~ 2012 eygle.com, All rights reserved.