首页 » ORACLE [C]系列, ORACLE 9i-23c » Oracle 12c R2 新特性: Online PDB relocate (PDB hot move)

Oracle 12c R2 新特性: Online PDB relocate (PDB hot move)

继续我的PDB系列测试, 前面测试了PDB HOT Clone( copy), PDB refresh(copy and sync), 这篇测试PDB relocate(move) , 在前两年的OOW上Larry Ellison就演示了迁移一个PDB 从on-premise到 Oracle Public Cloud,不过当时还是12.1 原库需要打开在read only, 现在12C R2版本实现在完全在线,源库在read-write的模式下,就可以把本地中的PDB relocate到远程CDB中, 如果配置上了connect的可用性跳转,完全实现了zero  downtime的迁移, 比过去的TTS还要灵活,不需要在导出导入元数据。

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)

下面开始演示, 因为测试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操作和移动如上云、下云的代价。

打赏

,

对不起,这篇文章暂时关闭评论。