« Oracle 12c:多租户数据库克隆 ORA-01276 错误处理一例 | Blog首页 | Oracle 12.2 新特性:在线PDB数据库克隆(Pluggable Hot Clone) »
Oracle 12c 新特性: 多租户 PDB 的克隆与维护
链接:https://www.eygle.com/archives/2016/12/oracle_12c_pdb_clone.html
这部分内容节选自《深入解析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中已经被改进,有所不同。
历史上的今天...
>> 2019-12-08文章:
>> 2010-12-08文章:
>> 2008-12-08文章:
>> 2006-12-08文章:
By eygle on 2016-12-08 15:38 | Comments (0) | Oracle12c/11g | 3223 |