Oracle 12.2 新特性：在线PDB数据库克隆(Pluggable Hot Clone)
在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的持续改进。
Oracle 12c R2 New Feature: Hot Clone A PDB (12.2热克隆)
在12.1版本中Clone一份PDB源库需要打开在read only只读模式, 在12.2版本中引入了local undo mode, 源PDB在read/write 读写模式也可以Clone. 这样可以在复制一份测试环境时对源库影响降到最低, 同时local undo mode也是ORACLE推荐的.
12.1 源PDB在read/write模式clone是不允许的.以前的 小例子12.1 clone pdb
ORA-65081: database or pluggable database is not open in read only mode
下面会演示一下12.2 的PDB HOT Clone, 开始前先补充一点local undo的知识.
UNDO模式
undo 在12C R1版本中只支持Global Shared Undo模式, 所有container共享一个UNDO表空间, 目前保留这种模式只是为了升级过渡, 在12C R2引入了PDB Local UNDO模式,每个container都有自己的UNDO 表空间, 对于RAC是每个实例每个container都有自己的UNDO表空间, 这也正是推荐的. 无论是Shared undo还是Local undo模式,都是CDB的属性,所以在修改时当前的container都要在cdb$root下. 12.2只要配置了LOCAL undo才支持下面的新特性:Hot Clone, Refresh PDB, PDB Relocate, Flashback PDB
如何检查当前UNDO模式?
SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPE DESCRIPTION -------------------- ----- ----------------------------------- LOCAL_UNDO_ENABLED TRUE true if local undo is enabled
UNDO模式转换
How to convert CDB from Local to Shared Undo Mode
When a CDB is in local undo mode, you can change it to use shared undo mode by issuing an ALTER DATABASE LOCAL UNDO OFF statement and restarting the database. When it is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces. startup upgrade ALTER DATABASE LOCAL UNDO off; shutdown immediate startup ALTER PLUGGABLE DATABASE xxx OPEN READ WRITE; ALTER SESSION SET CONTAINER=xxx; select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; drop tablespace UNDOTBS1 including contents and datafiles;
How to convert CDB from Shared to Local Undo Mode
startup upgrade container set cdb$root ,if not :alter session set container=cdb$root; ALTER DATABASE LOCAL UNDO ON; shutdown immediate; startup -- When a CDB is changed from shared undo mode to local undo mode, -- Oracle Database creates the required undo tablespaces automatically.alter pluggable database xxx open;
Hot Clone Local PDB
因为我当前的测试环境12.2只有一套,先测试Clone LOCAL Pdb, 环境oracle 12.2.0.1 ee on OEL 6, HOT Clone的内部原理:
Source PDB remains open for read and write
Read and copy in parallel
On-going operations imply a “dirty read”
Some data changes not included in initial file copy
Ship and apply redo to catch up with source
Apply undo to rollback uncommitted transactions
所以在较长或较大PDB Clone期间要保证REDO 日志存在, 最好源库开起archivelog mode.
[oracle@anbob ~]$ ora SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 6 17:11:54 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBANBOB MOUNTED SQL> alter pluggable database pdbanbob open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBANBOB READ WRITE NO SQL> create pluggable database pdbweejar from pdbanbpb; create pluggable database pdbweejar from pdbanbpb ERROR at line 1: ORA-65016: FILE_NAME_CONVERT must be specified SQL> alter session set pdb_file_name_convert='anbob','weejar'; Session altered. -- Multi-threaded based on cpu count by default -- File copy progress can be monitored in v$session_longops:SQL> create pluggable database pdbweejar from pdbanbob; Pluggable database created. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBANBOB READ WRITE NO 6 PDBWEEJAR MOUNTED SQL> alter pluggable database pdbweejar open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDBANBOB READ WRITE NO 6 PDBWEEJAR READ WRITE NO SQL> alter session set container=pdbweejar; Session altered. SQL> select * from v$dbfile FILE# NAME CON_ID ---------- -------------------------------------------------------------------- ---------- 14 /u02/app/oracle/oradata/weejar/pdbweejar/system01.dbf 6 15 /u02/app/oracle/oradata/weejar/pdbweejar/sysaux01.dbf 6 16 /u02/app/oracle/oradata/weejar/pdbweejar/undotbs01.dbf 6 17 /u02/app/oracle/oradata/weejar/pdbweejar/users01.dbf 6 18 /u02/app/oracle/oradata/weejar/pdbweejar/lower01.dbf 6 SQL> alter session set container=pdbanbob; Session altered. SQL> set lines 300 SQL> select * from v$dbfile; FILE# NAME CON_ID ---------- -------------------------------------------------------------------------------- ---------- 9 /u02/app/oracle/oradata/anbob/pdbanbob/system01.dbf 3 10 /u02/app/oracle/oradata/anbob/pdbanbob/sysaux01.dbf 3 11 /u02/app/oracle/oradata/anbob/pdbanbob/undotbs01.dbf 3 12 /u02/app/oracle/oradata/anbob/pdbanbob/users01.dbf 3 13 /u02/app/oracle/oradata/anbob/pdbanbob/lower01.dbf 3 SQL> select * from database_properties; PROPERTY_NAME PROPERTY_VALUE DESCRIPTION --------------------------- -------------------- ------------------------------------------------------------ CON_VSN 5 version number for the CDB or the Application Container DICTIONARY_ENDIAN_TYPE LITTLE Endian type of the data dictionary LOCAL_UNDO_ENABLED TRUE true if local undo is enabled OLS_OID_STATUS 0 OLS OID Status used for Label Security GLOBAL_DB_NAME ANBOB.COM Global database name
Hot Clone REMOTE PDB
ENABLE ARCHIVELOG MODE IN CDB$ROOT CDB$ROOT-SQL> startup mount CDB$ROOT-SQL> alter database archivelog; CDB$ROOT-SQL> alter database open; ENABLE LOCAL UNDO IN CDB$ROOT CDB$ROOT-SQL> startup mount CDB$ROOT-SQL> alter database open upgrade; CDB$ROOT-SQL> alter database local undo on; CREATE COMMON USER IN CDB$ROOT CDB$ROOT-SQL> create user c##admin identified by <password> container=all; CDB$ROOT-SQL> grant sysoper to c##admin container=all; CREATE PUBLIC DATABASE LINK IN CDB$ROOT CDB$ROOT-SQL> create public database link cdb1_link connect to c##admin identified by <password> using ‘<tns alias>’; CREATE PLUGGABLE DATABASE PDB_DEV FROM PDB@DBLINK;
Summary:
12.2引入了local undo解决了12.1时clone pdb时源PDB需要READ-ONLY的前提. 减少了clone pdb时对源库的影响. 12.2 可以在shared undo和local undo之前来回的转换, 事务可以透明的转移, 从LOCAL 转到 SHARED里,第一次打开PDB时会应用local undo接着使用shared undo, 后就不再需要local undo, 但是local undo mode是ORACLE建议的配置. 这样才可以使用HOT CLONE和Refresh PDB及快速的闪回PDB时使用.
References:
Undo Modes in 12.2 Multitenant Databases – Local and Shared Modes (文档 ID 2169828.1)
