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操作和移动如上云、下云的代价。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。