« Oracle 12c 新特性: 多租户 PDB 的克隆与维护 | Blog首页 | Oracle 12c 多租户:未完成事务的隐式提交(commit) »
Oracle 12.2 新特性:在线PDB数据库克隆(Pluggable Hot Clone)
链接:https://www.eygle.com/archives/2016/12/oracle_122_pdb_hotclone.html
在Oracle 12c的多租户(Multitenant)特性中,可以通过灵活的克隆复制来创建PDB(Pluggable Database),使得运维的工作大大简化。但是在12.1中,进行数据库Clone时,源数据库需要置于Read Only模式,即影响源数据库的使用,又会带来时间窗口的不便。
在12.2中,Oracle引入了 Hot Clone 技术,可以通过在线的方式,对源库进行复制。大大简化的运维的复杂性。
以下测试向大家展示了这一新特性的用法和功能。首先在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的持续改进。
历史上的今天...
>> 2019-12-08文章:
>> 2010-12-08文章:
>> 2008-12-08文章:
>> 2006-12-08文章:
By eygle on 2016-12-08 18:43 | Comments (0) | Oracle12c/11g | 3222 |