Oracle 12c 新特性: 多租户 PDB 的克隆与维护
这部分内容节选自《深入解析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 yunheYUNHE
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中已经被改进，有所不同。
By eygle on 2016-12-08 15:38 |
Comments (0) |
Oracle12c/11g | 3223 |
扫一扫
202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
