Oracle 12 . 2 新特性 : Online PDB relocate (PDB hot move)
Relocating a PDB 是 Oracle 在 12C 中推出的一种新的数据迁移方式，在采用 Relocate 时可以使用最短的停机时间在不同的 CDB 之间直接迁移 PDB 。
Oracle 12.1 中 Relocate 迁移数据时，需要源库处于 read only 状态，但由于 12.2 中 local undo 的推出，可以实现完全在线迁移，源库的 PDB 在 read-write 模式下就可以 Relocate 到远端 CDB 中， 源 PDB 中的 DML 事务不会受到任何影响 ，整个迁移过程中不需要导出导入元数据，其迁移方式比 XTTS 更加简单快捷。
在目标 PDB 执行“ create pluggable database xxx relocate ”完成后，源 CDB 和目标 CDB 会同时存在 2 个 Relocate PDB ，此时目标 CDB 中该 PDB 处于 MOUNT 状态，而源库的 PDB 仍然处于 READ WRITE 状态。当在目标 CDB 中的 PDB 执行 OPEN 时，源 PDB 会停止且 Oracle 会自动 KILL 掉源 PDB 连接的所有会话，并同步且应用源 PDB 的日志到目标 PDB ，同时也会回滚未提交的事务，应用完成后 源 PDB 库的所有数据文件将会自动删除，源库会被删除 ，目标 PDB 可以对外提供服务。
如果在 Relocate 过程中使用 AVAILABILITY 模式进行 Relocate ，新的连接请求 Oracle 会将其发送新 PDB 上，则完全实现 PDB 迁移的零停机。
其实 Relocate 的机制就是 Hot Clone+DBlink 的增量恢复。
PDB relocate 的基本实现方式 hot clone 和通过 dblink 的增量 redo apply 。在线 Pdb Relocate 需要在目标 CDB 中创建一个 database link 指向源库的 CDB ，需要 DBLINK 使用的 common 用户有 create pluggable database 的权限， relocate 的 AVAILABILITY （高用选项）有 normal|max|high ，当目标库使用 create pluggable database relocate 选项时，源库会一直在 read-write open 状态，甚至到 create pdb 的命令完成，源 PDB （ READ-WRITE OPEN ）上的用户 DML 事务都不会有任何影响。当目标库的 CREATE PDB RELOCATE 完成时，会在源 CDB 和目标 CDB 存在 2 个 relocate 的 PDB ，只不过在目标 CDB 中该 PDB 是 mount 状态，此时源库的 DML 为生成更多的 redo 日志为后期的 PDB 切换， PDB 的切换操作是在目标 CDB 中的 PDB open read-write 时，此时源 PDB 会暂停，并且 KILL 掉源 PDB 库连接的会话，同步并应用源库 PDB 的 redo 到目标 PDB ，并且应用 undo 数据回滚未提交的事务，当应用完成后源 pdb 库的所有数据文件将会自动删除，目标 PDB 事务继续，在这短暂的操作期间如果使用 AVAILABILITY 如果有新的连接请求， Oracle 可以跳过连接到新 PDB 上，实现了移动 PDB 的零停机。
有一些基本的条件如源库和目标库是 archivelog mode, 并且 local undo ，和相同的字节码（ endianness ），相同的 options 和字符集或者目标库是源库的子集，如目标库是字符集是 AL32UTF8 ，源库可以是任何字符集，当然这也是 12.2 的另一新特性 ( 同一 CDB 中，不同的 PDB 可以使用不同的字符集，但是其限制是 CDB 必须是 AL32UTF8) 。
Relocate a PDB
Relocate a PDB Into an Application Container
在使用 RelocatePDB 进行数据迁移时需要注意如下事项：
1. 如果 PDB 被 Relocate 到的 CDB 的字符集不是 AL32UTF8 ，那么源与目标字符集必须兼容。
2. 源端与目标端的字节顺序必须相同。
3. 连接的用户在 CDB 中必须拥有 'CREATE PLUGGABLE DATABASE' 的权限。
5. 源端 PDB 必须为归档模式。
6. 源端 PDB 必须是 local undo 模式。
7. 当指定 AVAILABILITY MAX 子句时，要求目标 PDB 与源 PDB 名字必须保持一致。
实验时使用 12.2.0.2 测试，或同版本测试：
alter pluggable database PDBLHR1 close;
drop pluggable database PDBLHR1 including datafiles;
--源库
grant connect, sysoper, create pluggable database to SYSTEM container=all;
--这里的DBLINK是连接到源库的CDB，而非PDB
create public database link dbl_CDBLHR1 connect to system identified by lhr using 'CDBLHR1';
create public database link dbl_CDBLHR1 connect to system identified by lhr using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.59.52)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lhr18cdb)))';
create pluggable database PDBRELOCATE from PDBLHR2@dbl_CDBLHR1 relocate CREATE_FILE_DEST = '/u01/app/oracle/oradata';
alter pluggable database PDBRELOCATE open;
show pdbs
SELECT to_char(time, 'YYYY-MM-DD HH24:MI:SS') time,
NAME,
CAUSE,
TYPE,
LINE,
MESSAGE,
STATUS,
ACTION,
CON_ID
FROM PDB_PLUG_IN_VIOLATIONS
order by time desc;
源库的告警日志：
2018-12-04T14:05:32.210947+08:00
PDBLHR2(4):JIT: pid 5065 requesting stop
Pluggable database PDBLHR2 closed
PDBLHR2(4):JIT: pid 5065 requesting stop
PDBLHR2(4):Buffer Cache flush started: 4
PDBLHR2(4):Buffer Cache flush finished: 4
2018-12-04T14:05:32.768297+08:00
PDBLHR2(4):While transitioning the pdb 4 to clean state, clearing all its abort bits in the control file.
Pluggable database PDBLHR2 closed
2018-12-04T14:05:40.727737+08:00
Deleted Oracle managed file +DATA/LHR18CDB/78CAF704CB8846B6E0550250563FC469/TEMPFILE/temp.281.990191125
Deleted Oracle managed file +DATA/LHR18CDB/78CAF704CB8846B6E0550250563FC469/DATAFILE/undotbs1.278.990190699
Deleted Oracle managed file +DATA/LHR18CDB/78CAF704CB8846B6E0550250563FC469/DATAFILE/sysaux.280.990190707
Deleted Oracle managed file +DATA/LHR18CDB/78CAF704CB8846B6E0550250563FC469/DATAFILE/system.279.990190701
目标库的告警日志：
2018-12-04T13:59:54.065377+08:00
create pluggable database PDBRELOCATE from PDBLHR2@dbl_CDBLHR1 relocate CREATE_FILE_DEST = '/u01/app/oracle/oradata'
2018-12-04T13:59:56.696932+08:00
**************************************************************
Undo Create of Pluggable Database PDBRELOCATE with pdb id - 4.
**************************************************************
ORA-17628 signalled during: create pluggable database PDBRELOCATE from PDBLHR2@dbl_CDBLHR1 relocate CREATE_FILE_DEST = '/u01/app/oracle/oradata'...
2018-12-04T14:00:17.018600+08:00
Resize operation completed for file# 3, old size 532480K, new size 542720K
2018-12-04T14:02:27.106990+08:00
create pluggable database PDBRELOCATE from PDBLHR2@dbl_CDBLHR1 relocate CREATE_FILE_DEST = '/u01/app/oracle/oradata'
2018-12-04T14:02:44.901996+08:00
PDBRELOCATE(5):Endian type of dictionary set to little
****************************************************************
Pluggable Database PDBRELOCATE 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-0x0000000000000103
****************************************************************
2018-12-04T14:02:49.576748+08:00
Applying media recovery for pdb-4099 from SCN 1872606 to SCN 1872624
Remote log information: count-1
thr-1, seq-8, logfile-+FRA/LHR18CDB/partial_archivelog/2018_12_04/thread_1_seq_8.260.993996167, los-1864077, nxs-18446744073709551615
PDBRELOCATE(5):Media Recovery Start
2018-12-04T14:02:49.583591+08:00
PDBRELOCATE(5):Serial Media Recovery started
PDBRELOCATE(5):max_pdb is 5
2018-12-04T14:02:49.654294+08:00
PDBRELOCATE(5):Media Recovery Log +FRA/LHR18CDB/partial_archivelog/2018_12_04/thread_1_seq_8.260.993996167
2018-12-04T14:02:51.341131+08:00
PDBRELOCATE(5):Incomplete Recovery applied until change 1872624 time 12/04/2018 14:02:45
2018-12-04T14:02:51.345366+08:00
PDBRELOCATE(5):Media Recovery Complete (CDBLHR18c)
Completed: create pluggable database PDBRELOCATE from PDBLHR2@dbl_CDBLHR1 relocate CREATE_FILE_DEST = '/u01/app/oracle/oradata'
2018-12-04T14:05:23.994157+08:00
alter pluggable database PDBRELOCATE open
2018-12-04T14:05:28.606877+08:00
Applying media recovery for pdb-4099 from SCN 1872624 to SCN 1873690
Remote log information: count-1
thr-1, seq-8, logfile-+FRA/LHR18CDB/partial_archivelog/2018_12_04/thread_1_seq_8.261.993996327, los-1864077, nxs-18446744073709551615
PDBRELOCATE(5):Media Recovery Start
2018-12-04T14:05:28.607442+08:00
PDBRELOCATE(5):Serial Media Recovery started
PDBRELOCATE(5):max_pdb is 5
2018-12-04T14:05:28.655007+08:00
PDBRELOCATE(5):Media Recovery Log +FRA/LHR18CDB/partial_archivelog/2018_12_04/thread_1_seq_8.261.993996327
2018-12-04T14:05:31.068791+08:00
PDBRELOCATE(5):Incomplete Recovery applied until change 1873690 time 12/04/2018 14:05:24
2018-12-04T14:05:31.071574+08:00
PDBRELOCATE(5):Media Recovery Complete (CDBLHR18c)
PDBRELOCATE(5):Autotune of undo retention is turned on.
PDBRELOCATE(5):Undo initialization finished serial:0 start:20396712 end:20396712 diff:0 ms (0.0 seconds)
PDBRELOCATE(5):Database Characterset for PDBRELOCATE is AL32UTF8
PDBRELOCATE(5):Opening pdb with no Resource Manager plan active
2018-12-04T14:05:32.213689+08:00
PDBRELOCATE(5):JIT: pid 62503 requesting stop
PDBRELOCATE(5):Buffer Cache flush started: 5
PDBRELOCATE(5):Buffer Cache flush finished: 5
2018-12-04T14:05:37.321967+08:00
Applying media recovery for pdb-4099 from SCN 1873690 to SCN 1873744
Remote log information: count-1
thr-1, seq-8, logfile-+FRA/LHR18CDB/partial_archivelog/2018_12_04/thread_1_seq_8.262.993996335, los-1864077, nxs-18446744073709551615
PDBRELOCATE(5):Media Recovery Start
2018-12-04T14:05:37.322339+08:00
PDBRELOCATE(5):Serial Media Recovery started
PDBRELOCATE(5):max_pdb is 5
2018-12-04T14:05:37.356870+08:00
PDBRELOCATE(5):Media Recovery Log +FRA/LHR18CDB/partial_archivelog/2018_12_04/thread_1_seq_8.262.993996335
2018-12-04T14:05:38.978984+08:00
PDBRELOCATE(5):Incomplete Recovery applied until change 1873744 time 12/04/2018 14:05:32
2018-12-04T14:05:38.981093+08:00
PDBRELOCATE(5):Media Recovery Complete (CDBLHR18c)
PDBRELOCATE(5):Undo initialization recovery: err:0 start: 20404280 end: 20404284 diff: 4 ms (0.0 seconds)
PDBRELOCATE(5):[62503] Successfully onlined Undo Tablespace 2.
PDBRELOCATE(5):Undo initialization online undo segments: err:0 start: 20404284 end: 20404289 diff: 5 ms (0.0 seconds)
PDBRELOCATE(5):Undo initialization finished serial:0 start:20404280 end:20404290 diff:10 ms (0.0 seconds)
PDBRELOCATE(5):Database Characterset for PDBRELOCATE is AL32UTF8
PDBRELOCATE(5):Buffer Cache flush started: 5
PDBRELOCATE(5):Buffer Cache flush finished: 5
2018-12-04T14:05:39.056929+08:00
PDBRELOCATE(5):While transitioning the pdb 5 to clean state, clearing all its abort bits in the control file.
2018-12-04T14:05:41.205276+08:00
PDBRELOCATE(5):Undo initialization recovery: err:0 start: 20406479 end: 20406480 diff: 1 ms (0.0 seconds)
PDBRELOCATE(5):[62503] Successfully onlined Undo Tablespace 2.
PDBRELOCATE(5):Undo initialization online undo segments: err:0 start: 20406480 end: 20406518 diff: 38 ms (0.0 seconds)
PDBRELOCATE(5):Undo initialization finished serial:0 start:20406479 end:20406519 diff:40 ms (0.0 seconds)
PDBRELOCATE(5):Deleting old file#12 from file$
PDBRELOCATE(5):Deleting old file#13 from file$
PDBRELOCATE(5):Deleting old file#14 from file$
PDBRELOCATE(5):Adding new file#12 to file$(old file#12). fopr-1, newblks-34560, oldblks-19200
PDBRELOCATE(5):Adding new file#13 to file$(old file#13). fopr-1, newblks-47360, oldblks-15360
PDBRELOCATE(5):Adding new file#14 to file$(old file#14). fopr-1, newblks-12800, oldblks-12800
PDBRELOCATE(5):Successfully created internal service PDBRELOCATE at open
****************************************************************
Post plug operations are now complete.
Pluggable database PDBRELOCATE with pdb id - 5 is now marked as NEW.
****************************************************************
PDBRELOCATE(5):Pluggable database PDBRELOCATE dictionary check beginning
PDBRELOCATE(5):Pluggable Database PDBRELOCATE Dictionary check complete
PDBRELOCATE(5):Database Characterset for PDBRELOCATE is AL32UTF8
Violations: Type: 2, Count: 3
PDBRELOCATE(5):***************************************************************
PDBRELOCATE(5):WARNING: Pluggable Database PDBRELOCATE with pdb id - 5 is
PDBRELOCATE(5): altered with errors or warnings. Please look into
PDBRELOCATE(5): PDB_PLUG_IN_VIOLATIONS view for more details.
PDBRELOCATE(5):***************************************************************
2018-12-04T14:05:42.403987+08:00
PDBRELOCATE(5):Opening pdb with no Resource Manager plan active
Pluggable database PDBRELOCATE opened read write
Completed: alter pluggable database PDBRELOCATE open
下面开始演示, 因为测试12c 的环境成本增高，测试环境是我和朋友的两台笔记本之间不同CDB之间的relocate, oracle 12.2 ee on OL6, 迁移源库ORCL122的PDB：test到目标库ANBOB中
源库CDB中创建COMMON用户用于dblink
SQL> create user C##CLONE_ADMIN identified by oracle container=all; User created. SQL> grant connect, sysoper, create pluggable databaseto C##CLONE_ADMIN container=all; Grant succeeded.
目标库配置tnsnames.ora并创建DBLINK
# tnsnames.ora append cdb210 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.210)(PORT = 1521)) (LOAD_BALANCE = NO) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL122) ) ) [oracle@anbob ~]$ tnsping cdb210 TNS Ping Utility for Linux: Vesion 12.2.0.1.0 - Production on 12-MAR-2017 12:07:24 Copyright (c) 1997, 2016, Oracle. All rights reserved. Used parameter files: /u02/app/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.210)(PORT = 1521)) (LOAD_BALANCE = NO) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL122))) OK (0 msec) SQL> create database link link_cdb210 connect to C##CLONE_ADMIN identified by oracle using 'cdb210'; Database link created. SQL> select sysdate from dual@link_cdb210; SYSDATE ------------------- 2017-03-12 12:20:38
源库和目标库的兼容性检查
# target db
[oracle@anbob admin]$ ora
SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 12 11:48:05 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
USERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR
-------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYS CDB$ROOT-anbob anbob 53 20573 12.2.0.1.0 20170312 5029 33 5028 000000006B23A690 000000006CC27FC8
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
col PROPERTY_NAME for a30
col PROPERTY_VALUE for a40
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME in('LOCAL_UNDO_ENABLED','DICTIONARY_ENDIAN_TYPE','GLOBAL_DB_NAME','NLS_CHARACTERSET');
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------------------
DICTIONARY_ENDIAN_TYPE LITTLE
LOCAL_UNDO_ENABLED TRUE
GLOBAL_DB_NAME ANBOB.COM
NLS_CHARACTERSET AL32UTF8
SQL> select
db.name,
db.platform_id,
db.platform_name ,
os.ENDIAN_FORMAT
from
v$database db ,v$transportable_platform os
where db.platform_id=os.platform_id;
NAME PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
--------- ----------- ------------------------------ --------------
ANBOB 13 Linux x86 64-bit Little
SQL> select * from v$option where value='TRUE';
PARAMETER VALUE CON_ID
---------------------------------------- ---------- ----------
Partitioning TRUE 0
Objects TRUE 0
Advanced replication TRUE 0
Bit-mapped indexes TRUE 0
Connection multiplexing TRUE 0
Connection pooling TRUE 0
Database queuing TRUE 0
Incremental backup and recovery TRUE 0
Instead-of triggers TRUE 0
Parallel backup and recovery TRUE 0
Parallel execution TRUE 0
Parallel load TRUE 0
Point-in-time tablespace recovery TRUE 0
Fine-grained access control TRUE 0
Proxy authentication/authorization TRUE 0
Change Data Capture TRUE 0
Plan Stability TRUE 0
Online Index Build TRUE 0
Coalesce Index TRUE 0
Managed Standby TRUE 0
Materialized view rewrite TRUE 0
Database resource manager TRUE 0
Spatial TRUE 0
Export transportable tablespaces TRUE 0
Transparent Application Failover TRUE 0
Fast-Start Fault Recovery TRUE 0
Sample Scan TRUE 0
Duplexed backups TRUE 0
Java TRUE 0
OLAP Window Functions TRUE 0
Block Media Recovery TRUE 0
Fine-grained Auditing TRUE 0
Application Role TRUE 0
Enterprise User Security TRUE 0
Oracle Data Guard TRUE 0
OLAP TRUE 0
Basic Compression TRUE 0
Join index TRUE 0
Trial Recovery TRUE 0
Advanced Analytics TRUE 0
Online Redefinition TRUE 0
Streams Capture TRUE 0
File Mapping TRUE 0
Block Change Tracking TRUE 0
Flashback Table TRUE 0
Flashback Database TRUE 0
Transparent Data Encryption TRUE 0
Backup Encryption TRUE 0
Unused Block Compression TRUE 0
Result Cache TRUE 0
SQL Plan Management TRUE 0
SecureFiles Encryption TRUE 0
Real Application Testing TRUE 0
Flashback Data Archive TRUE 0
DICOM TRUE 0
Active Data Guard TRUE 0
Server Flash Cache TRUE 0
Advanced Compression TRUE 0
XStream TRUE 0
Deferred Segment Creation TRUE 0
Exadata Discovery TRUE 0
Data Mining TRUE 0
Global Data Services TRUE 0
Adaptive Execution Plans TRUE 0
Table Clustering TRUE 0
Zone Maps TRUE 0
Real Application Security TRUE 0
Privilege Analysis TRUE 0
Data Redaction TRUE 0
Cross Transportable Backups TRUE 0
Cache Fusion Lock Accelerator TRUE 0
Snapshot time recovery TRUE 0
Heat Map TRUE 0
Automatic Data Optimization TRUE 0
Transparent Sensitive Data Protection TRUE 0
In-Memory Column Store TRUE 0
Advanced Index Compression TRUE 0
In-Memory Aggregation TRUE 0
78 rows selected.
# source db
[oracle@db1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sun Mar 12 12:13:27 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 156
Next log sequence to archive 158
Current log sequence 158
SQL> col PROPERTY_NAME for a30
SQL> col PROPERTY_VALUE for a40
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME in('LOCAL_UNDO_ENABLED','DICTIONARY_ENDIAN_TYPE','GLOBAL_DB_NAME','NLS_CHARACTERSET');
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------------------
DICTIONARY_ENDIAN_TYPE LITTLE
LOCAL_UNDO_ENABLED TRUE
GLOBAL_DB_NAME ORCL122
NLS_CHARACTERSET ZHS16GBK
SQL> select
2 db.name,
3 db.platform_id,
4 db.platform_name ,
5 os.ENDIAN_FORMAT
6 from
7 v$database db ,v$transportable_platform os
8 where db.platform_id=os.platform_id;
NAME PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
--------- ----------- -------------------------------- --------------
ORCL122 13 Linux x86 64-bit Little
SQL> select * from v$option where value='TRUE';
PARAMETER VALUE CON_ID
---------------------------------------------------------------- --------------- ----------
Partitioning TRUE 0
Objects TRUE 0
Advanced replication TRUE 0
Bit-mapped indexes TRUE 0
Connection multiplexing TRUE 0
Connection pooling TRUE 0
Database queuing TRUE 0
Incremental backup and recovery TRUE 0
Instead-of triggers TRUE 0
Parallel backup and recovery TRUE 0
Parallel execution TRUE 0
Parallel load TRUE 0
Point-in-time tablespace recovery TRUE 0
Fine-grained access control TRUE 0
Proxy authentication/authorization TRUE 0
Change Data Capture TRUE 0
Plan Stability TRUE 0
Online Index Build TRUE 0
Coalesce Index TRUE 0
Managed Standby TRUE 0
Materialized view rewrite TRUE 0
Database resource manager TRUE 0
Spatial TRUE 0
Export transportable tablespaces TRUE 0
Transparent Application Failover TRUE 0
Fast-Start Fault Recovery TRUE 0
Sample Scan TRUE 0
Duplexed backups TRUE 0
Java TRUE 0
OLAP Window Functions TRUE 0
Block Media Recovery TRUE 0
Fine-grained Auditing TRUE 0
Application Role TRUE 0
Enterprise User Security TRUE 0
Oracle Data Guard TRUE 0
OLAP TRUE 0
Basic Compression TRUE 0
Join index TRUE 0
Trial Recovery TRUE 0
Advanced Analytics TRUE 0
Online Redefinition TRUE 0
Streams Capture TRUE 0
File Mapping TRUE 0
Block Change Tracking TRUE 0
Flashback Table TRUE 0
Flashback Database TRUE 0
Transparent Data Encryption TRUE 0
Backup Encryption TRUE 0
Unused Block Compression TRUE 0
Result Cache TRUE 0
SQL Plan Management TRUE 0
SecureFiles Encryption TRUE 0
Real Application Testing TRUE 0
Flashback Data Archive TRUE 0
DICOM TRUE 0
Active Data Guard TRUE 0
Server Flash Cache TRUE 0
Advanced Compression TRUE 0
XStream TRUE 0
Deferred Segment Creation TRUE 0
Exadata Discovery TRUE 0
Data Mining TRUE 0
Global Data Services TRUE 0
Adaptive Execution Plans TRUE 0
Table Clustering TRUE 0
Zone Maps TRUE 0
Real Application Security TRUE 0
Privilege Analysis TRUE 0
Data Redaction TRUE 0
Cross Transportable Backups TRUE 0
Cache Fusion Lock Accelerator TRUE 0
Snapshot time recovery TRUE 0
Heat Map TRUE 0
Automatic Data Optimization TRUE 0
Transparent Sensitive Data Protection TRUE 0
In-Memory Column Store TRUE 0
Advanced Index Compression TRUE 0
In-Memory Aggregation TRUE 0
78 rows selected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FAN MOUNTED
4 TEST2 MOUNTED
6 TEST READ WRITE NO
SQL> alter session set container=test;
Session altered.
SQL> select name from v$datafile;
NAME
-------------------------------------------------------------------------------------------------------
/oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_system_dbz88noo_.dbf
/oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_sysaux_dbz88noz_.dbf
/oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_users_dbz88np1_.dbf
/oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_undo_dbz88np1_.dbf
目标库中开始relocate pdb :test, 注意这段期间我们在用java应用不停在向源库PDB中做INSERT，确认对事务并无影响。
SQL> create pluggable database pdbtest from test@link_cdb210 relocate;
create pluggable database pdbtest from test@link_cdb210 relocate
*
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
SQL> alter session set pdb_file_name_convert='/oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile','pdbtest';
Session altered.
SQL> show parameter create
PARAMETER_NAME TYPE VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> create pluggable database pdbtest from test@link_cdb210 relocate;
create pluggable database pdbtest from test@link_cdb210 relocate
*
ERROR at line 1:
ORA-65005: missing or invalid file name pattern for file - /oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_system_dbz88noo_.dbf
SQL> @ls users
TABLESPACE_NAME FILE_ID FILE_NAME EXT MB MAXSZ
------------------------------ ---------- -------------------------------------------------------------------------------- --- ---------- ----------
USERS 7 /u02/app/oracle/oradata/anbob/users01.dbf YES 5 32767.98
SQL> host
[oracle@anbob scripts]$ mkdir -p /u02/app/oracle/oradata/pdbtest
[oracle@anbob scripts]$ exit
exit
SQL> create pluggable database pdbtest from test@link_cdb210 relocate FILE_NAME_CONVERT=('/oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile','/u02/app/oracle/oradata/pdbtest');
create pluggable database pdbtest from test@link_cdb210 relocate FILE_NAME_CONVERT=('/oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile','/u02/app/oracle/oradata/pdbtest')
*
ERROR at line 1:
ORA-01276: Cannot add file /u02/app/oracle/oradata/pdbtest/o1_mf_system_dbz88noo_.dbf. File has an Oracle Managed Files file name.
SQL> alter session set pdb_file_name_convert='/oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile','/u02/app/oracle/oradata/pdbtest';
Session altered.
SQL> create pluggable database pdbtest from test@link_cdb210 relocate;
create pluggable database pdbtest from test@link_cdb210 relocate
*
ERROR at line 1:
ORA-01276: Cannot add file /u02/app/oracle/oradata/pdbtest/o1_mf_system_dbz88noo_.dbf. File has an Oracle Managed Files file name.
SQL> create pluggable database pdbtest from test@link_cdb210 relocate PATH_PREFIX ='/u02/app/oracle/oradata/pdbtest';
create pluggable database pdbtest from test@link_cdb210 relocate PATH_PREFIX ='/u02/app/oracle/oradata/pdbtest'
*
ERROR at line 1:
ORA-01276: Cannot add file /u02/app/oracle/oradata/pdbtest/o1_mf_system_dbz88noo_.dbf. File has an Oracle Managed Files file name.
SQL> ho oerr ora 1276
01276, 00000, "Cannot add file %s. File has an Oracle Managed Files file name."
// *Cause: An attempt was made to add to the database a datafile, log file,
// control file, snapshot control file, backup control file,
// datafile copy, control file copy or backuppiece with an Oracle
// Managed Files file name.
// *Action: Retry the operation with a new file name.
SQL> alter session set db_create_file_dest='/u02/app/oracle/oradata';
Session altered.
SQL> create pluggable database pdbtest from test@link_cdb210 relocate;
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBANBOB MOUNTED
4 PDBTEST MOUNTED ######
6 PDBWEEJAR MOUNTED
SQL> select pdb_name, status from cdb_pdbs;
PDB_NAME STATUS
------------------------------ ----------
PDBANBOB NORMAL
PDB$SEED NORMAL
PDBWEEJAR NORMAL
PDBTEST RELOCATING #######
[oracle@anbob oradata]$ ls -lrt
total 20
drwxr-x--- 3 oracle oinstall 4096 Mar 6 17:14 weejar
drwxr-x--- 3 oracle oinstall 4096 Mar 8 17:16 weejar_img
drwxr-x--- 5 oracle oinstall 4096 Mar 9 20:51 anbob
drwxr-x--- 3 oracle oinstall 4096 Mar 12 12:40 ANBOB
[oracle@anbob ANBOB]$ ls
492D54D080AD4990E053D238A8C08E62
[oracle@anbob ANBOB]$ cd 492D54D080AD4990E053D238A8C08E62/
[oracle@anbob 492D54D080AD4990E053D238A8C08E62]$ ls
datafile
[oracle@anbob 492D54D080AD4990E053D238A8C08E62]$ cd datafile/
[oracle@anbob datafile]$ ls -lrt
total 556064
-rw-r----- 1 oracle oinstall 52436992 Mar 12 12:41 o1_mf_users_dd9n9byf_.dbf
-rw-r----- 1 oracle oinstall 52436992 Mar 12 12:41 o1_mf_undo_dd9n9byg_.dbf
-rw-r----- 1 oracle oinstall 241180672 Mar 12 12:46 o1_mf_sysaux_dd9n9byd_.dbf
-rw-r----- 1 oracle oinstall 225452032 Mar 12 12:47 o1_mf_system_dd9n9by2_.dbf
Note:
如果源库使用了OMF格式的文件名，目标库无法使用convert转换，只能配置db_create_file_dest同样对于该PDB使用OMF。PDB创建成功后是MOUNT格式，此时源库的DML事务并没有影响。同时查看PDBTEST的状态为 RELOCATING
， 同时监听上已注册了pdbanbob的service。
# source db
SQL> select pdb_name, status from cdb_pdbs; PDB_NAME STATUS ----------------------------- ---------- TEST NORMAL SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 6 TEST READ WRITE NO
以上操作目标库ALERT LOG日志
2017-03-12 12:40:08.012000 +08:00 create pluggable database pdbtest from test@link_cdb210 relocate 2017-03-12 12:40:10.952000 +08:00 Opatch validation is skipped for PDB PDBTEST (con_id=4) 2017-03-12 12:47:08.169000 +08:00 Endian type of dictionary set to little **************************************************************** Pluggable Database PDBTEST with pdb id - 4 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-0x0000000000164856 **************************************************************** 2017-03-12 12:47:11.075000 +08:00 Applying media recovery for pdb-4099 from SCN 3296978 to SCN 3298748 Remote log information: count-1 thr-1, seq-158, logfile-/arch/parlog_1_158_ea6c4617_936609332.arc, los-2479672, nxs-18446744073709551615 Media Recovery Start Serial Media Recovery started Media Recovery Log /arch/parlog_1_158_ea6c4617_936609332.arc ##### 路径是源库 2017-03-12 12:47:40.825000 +08:00 Incomplete Recovery applied until change 3298748 time 03/12/2017 12:47:06 Media Recovery Complete (anbob) Completed: create pluggable database pdbtest from test@link_cdb210 relocate
在目标CDB中打开PDB，实际的PDB切换操作
SQL> alter pluggable database pdbtest open; Pluggable database altered. SQL> select pdb_name, status from cdb_pdbs; PDB_NAME STATUS -------------------------------------------- ---------- PDBANBOB NORMAL PDB$SEED NORMAL PDBWEEJAR NORMAL PDBTEST NORMAL SQL> alter session set container=pdbtest; Session altered. SQL> @tab test.t Show tables matching condition "%test.t%" (if schema is not specified then current user s tables only are shown)... OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS -------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ------------------- -------------------- -------- TEST T TAB 4238 20 0 0 19 2017-03-12 12:58:04 1 DISABLED SQL> @desc test.t Name Null? Type ------------------------------- -------- ---------------------------- 1 STR VARCHAR2(100) 2 INTIME DATE SQL> select max(intime) from test.t; MAX(INTIME) ------------------- 2017-03-12 12:58:57 SQL> select count(*) from test.t where str='jdbc'; COUNT(*) ---------- 2146 # 测试向源库insert 的java 进程日志 ... 2145:2017-03-12 12:58:59 2146:2017-03-12 12:59:00 insert end java.sql.SQLRecoverableException: 无法从套接字读取更多的数据 at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1157) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:350) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227) at oracle.jdbc.driver.T4C7Ocommoncall.doOCOMMIT(T4C7Ocommoncall.java:75) at oracle.jdbc.driver.T4CConnection.doCommit(T4CConnection.java:641)
Note:
目标CDB中的relocate pdb打开时，源库之前的INSERT会话补KILL, 因为测试的应用只配置了到源库的连接，所以insert 被中断，同时验证了数据在源库的记录条数和目标端一致。 且源库的PDB TEST已自动删除。 目标库的PDBtest可以正常操作。
目标库对应的ALERT LOG
2017-03-12 12:58:36.324000 +08:00 alter pluggable database pdbtest open 2017-03-12 12:58:37.901000 +08:00 Applying media recovery for pdb-4099 from SCN 3298748 to SCN 3303755 Remote log information: count-2 thr-1, seq-158, logfile-/arch/9b87b4_1_158_936609332.dbf, los-2479672, nxs-3301888 thr-1, seq-159, logfile-/arch/parlog_1_159_ea6c4617_936609332.arc, los-3301888, nxs-18446744073709551615 Media Recovery Start Serial Media Recovery started Media Recovery Log /arch/9b87b4_1_158_936609332.dbf 2017-03-12 12:58:52.959000 +08:00 Media Recovery Log /arch/parlog_1_159_ea6c4617_936609332.arc 2017-03-12 12:58:59.102000 +08:00 Incomplete Recovery applied until change 3303755 time 03/12/2017 12:58:34 Media Recovery Complete (anbob) Autotune of undo retention is turned on. Undo initialization finished serial:0 start:6848016 end:6848016 diff:0 ms (0.0 seconds) Opatch validation is skipped for PDB PDBTEST (con_id=0) *************************************************************** WARNING: Pluggable Database PDBTEST with pdb id - 4 is altered with errors or warnings. Please look into PDB_PLUG_IN_VIOLATIONS view for more details. *************************************************************** 2017-03-12 12:59:00.330000 +08:00 Opening pdb with no Resource Manager plan active 2017-03-12 12:59:04.586000 +08:00 JIT: pid 5236 requesting stop 2017-03-12 12:59:10.993000 +08:00 Applying media recovery for pdb-4099 from SCN 3303755 to SCN 3304167 Remote log information: count-1 thr-1, seq-159, logfile-/arch/parlog_1_159_ea6c4617_936609332.arc, los-3301888, nxs-18446744073709551615 Media Recovery Start Serial Media Recovery started Media Recovery Log /arch/parlog_1_159_ea6c4617_936609332.arc 2017-03-12 12:59:14.011000 +08:00 Incomplete Recovery applied until change 3304167 time 03/12/2017 12:59:05 Media Recovery Complete (anbob) [5236] Successfully onlined Undo Tablespace 5. Undo initialization finished serial:0 start:6862572 end:6862668 diff:96 ms (0.1 seconds) Database Characterset for PDBTEST is ZHS16GBK 2017-03-12 12:59:15.870000 +08:00 [5236] Successfully onlined Undo Tablespace 5. Undo initialization finished serial:0 start:6864208 end:6864295 diff:87 ms (0.1 seconds) Opatch validation is skipped for PDB PDBTEST (con_id=4) Deleting old file#29 from file$ Deleting old file#30 from file$ Deleting old file#31 from file$ Deleting old file#32 from file$ Adding new file#44 to file$(old file#29) Adding new file#45 to file$(old file#30) Adding new file#46 to file$(old file#31) Adding new file#47 to file$(old file#32) Successfully created internal service pdbtest at open **************************************************************** Post plug operations are now complete. Pluggable database PDBTEST with pdb id - 4 is now marked as NEW. **************************************************************** Pluggable database PDBTEST dictionary check beginning Pluggable Database PDBTEST Dictionary check complete Database Characterset for PDBTEST is ZHS16GBK Opatch validation is skipped for PDB PDBTEST (con_id=0) *************************************************************** WARNING: Pluggable Database PDBTEST with pdb id - 4 is altered with errors or warnings. Please look into PDB_PLUG_IN_VIOLATIONS view for more details. *************************************************************** 2017-03-12 12:59:16.971000 +08:00 JIT: pid 5236 requesting full stop 2017-03-12 12:59:18.966000 +08:00 Opening pdb with no Resource Manager plan active Pluggable database PDBTEST opened read write Completed: alter pluggable database pdbtest open
源库对应的ALERT 日志
# ALERT LOG 2017-03-12T12:54:40.070341+08:00 Thread 1 advanced to log sequence 159 (LGWR switch) Current log# 3 seq# 159 mem# 0: /oracle/app/oracle/oradata/ORCL122/onlinelog/redo03.log 2017-03-12T12:54:40.611341+08:00 Archived Log entry 98 added for T-1.S-158 ID 0x9b6eb4 LAD:1 2017-03-12T12:58:57.765334+08:00 TEST(6):JIT: pid 20670 requesting stop 2017-03-12T12:58:58.312698+08:00 TEST(6):opiodr aborting process unknown ospid (7259) as a result of ORA-1089 2017-03-12T12:58:58.406081+08:00 TEST(6):KILL SESSION for sid=(58, 61986): TEST(6): Reason = PDB close immediate TEST(6): Mode = KILL HARD FORCE -/-/- TEST(6): Requestor = USER (orapid = 27, ospid = 20670, inst = 1) TEST(6): Owner = Process: USER (orapid = 56, ospid = 7259) TEST(6): Result = ORA-0 TEST(6):KILL SESSION for sid=(64, 20274): TEST(6): Reason = PDB close immediate TEST(6): Mode = KILL HARD FORCE -/-/- TEST(6): Requestor = USER (orapid = 27, ospid = 20670, inst = 1) TEST(6): Owner = Process: USER (orapid = 57, ospid = 13885) TEST(6): Result = ORA-0 TEST(6):KILL SESSION for sid=(69, 18219): TEST(6): Reason = PDB close immediate TEST(6): Mode = KILL HARD FORCE -/-/- TEST(6): Requestor = USER (orapid = 27, ospid = 20670, inst = 1) TEST(6): Owner = Process: USER (orapid = 52, ospid = 8682) TEST(6): Result = ORA-0 TEST(6):KILL SESSION for sid=(70, 22999): TEST(6): Reason = PDB close immediate TEST(6): Mode = KILL HARD FORCE -/-/- TEST(6): Requestor = USER (orapid = 27, ospid = 20670, inst = 1) TEST(6): Owner = Process: USER (orapid = 59, ospid = 15135) TEST(6): Result = ORA-0 2017-03-12T12:59:01.770159+08:00 Pluggable database TEST closed TEST(6):JIT: pid 20670 requesting stop 2017-03-12T12:59:05.132239+08:00 Pluggable database TEST closed 2017-03-12T12:59:12.712096+08:00 Deleted Oracle managed file /oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_undo_dbz88np1_.dbf Deleted Oracle managed file /oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_users_dbz88np1_.dbf Deleted Oracle managed file /oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_temp_dbz88np0_.dbf Deleted Oracle managed file /oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_sysaux_dbz88noz_.dbf Deleted Oracle managed file /oracle/app/oracle/oradata/ORCL122/492D54D080AD4990E053D238A8C08E62/datafile/o1_mf_system_dbz88noo_.dbf
Summary:
12.2的online pdb relocate 实现了PDB 在线几乎零停机时间在不同CDB之间的迁移，且在relocate过程中源库一直是open read-write状态，使用了增量日志的方式追加减少了最源库和网络资源的影响。降低了PDB操作和移动如上云、下云的代价。
About Me
| ........................................................................................................................ ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（ http://blog.itpub.net/26736162 ）、博客园（ http://www.cnblogs.com/lhrbest ）和个人weixin公众号（ xiaomaimiaolhr ）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文博客园地址： http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号： 230161599 （满） 、618766405 ● weixin群：可加我weixin，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友 （ 646634621 ） ，注明添加缘由 ● 于 2018-12-01 06:00 ~ 2018-12-31 24:00 在魔都完成 ● 最新修改时间：2018-12-01 06:00 ~ 2018-12-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店 ： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书 ： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班 ： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页 ： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 weixin客户端 扫描下面的二维码来关注小麦苗的weixin公众号（ xiaomaimiaolhr ）及QQ群（DBA宝典）、添加小麦苗weixin， 学习最实用的数据库技术。 ........................................................................................................................ |
 | |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2284043/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2284043/
102
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
