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

原创 eygle 2016-12-08

837

这部分内容节选自《深入解析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中已经被改进，有所不同。

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

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

评论