分类: Oracle

2018-05-16 15:58:30


|导 语


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 Relocate完成后,源CDB和目标CDB会同时存在2个Relocate PDB,此时目标CDB中该PDB处于MOUNT状态。

当在目标CDB中的PDB OPEN时,源PDB会停止且Oracle会自动KILL掉源PDB连接的所有会话,并同步且应用源PDB的日志到目标PDB,同时也会回滚未提交的事务,应用完成后源PDB库的所有数据文件将会自动删除,目标PDB可以对外提供服务。

如果在Relocate过程中使用AVAILABILITY模式进行Relocate,新的连接请求Oracle会将其发送新PDB上,则完全实现PDB迁移的零停机。

其实Relocate的机制就是HotClone+DBlink的增量恢复。

Relocate a PDB

Relocate a PDB Intoan Application Container

在使用RelocatePDB进行数据迁移时需要注意如下事项:

1.如果PDB被Relocate到的CDB的字符集不是AL32UTF8,那么源与目标字符集必须兼容。

2.源端与目标短的字节顺序必须相同。

3.连接的用户在CDB中必须拥有'CREATEPLUGGABLE DATABASE'的权限。

5.源端PDB必须为归档模式。

6.源端PDB必须是localundo模式。

7.当指定AVAILABILITY MAX字句时,要求目标PDB与源PDB名字必须保持一致。

这里演示将源CDB ora12c中的woqupdb使用Relocating的方式迁移到CDB orcl12c中,且命名为QDatapdb。

(1)源库

点击(此处)折叠或打开

  1. sys. ora12c>show pdbs
  2. CON_ID CON_NAME OPEN MODE RESTRICTED
  3. ---------- ------------------------------ ---------- ----------
  4.      2 PDB$SEED READ ONLY NO
  5.      5WOQUPDB READ WRITENO
  6. sys. ora12c>select file_name from cdb_data_files where con_id=5;
  7. FILE_NAME
  8. ----------------------------------------------------------------------
  9. /u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_system_fb2pjxo4_.dbf
  10. /u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_sysaux_fb2pjxo5_.dbf
  11. /u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_undotbs1_fb2pjxo6_.dbf
  12. /u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_users_fb2pjxo6_.dbf
  13. Elapsed: 00:00:00.05
  14. sys. ora12c>

(2)在源库为Relocate的用户system赋权相关权限(createpluggable database)


点击(此处)折叠或打开

  1. sys. ora12c>show pdbs
  2. sys. ora12c>grant connect,sysoper,create pluggable database to systemcontainer=all;
  3. Grant succeeded.
  4. Elapsed: 00:00:00.53
  5. sys. ora12c>

(3)目标库端创建dblink:woqu

点击(此处)折叠或打开

  1. sys. ora12c>show pdbs
  2. CON_ID CON_NAME OPEN MODE RESTRICTED
  3. ---------- ------------------------------ ---------- ----------
  4.      2 PDB$SEED READ ONLY NO
  5.      5 WOQUPDB READ WRITE NO
  6. sys. ora12c>CREATE DATABASE LINK orcl12c CONNECT TO system IDENTIFIEDBY oracle USING 'orcl12c';
  7. Database link created.
  8. Elapsed: 00:00:00.15
  9. sys. ora12c>

(4)源库和目标库的兼容性检查

1>检查shared undo模式

源库:

点击(此处)折叠或打开

  1. COL PROPERTY_NAME FOR A30
  2. COL PROPERTY_VALUE FOR A30
  3. SELECT property_name, property_value
  4. FROM database_properties
  5. WHERE property_name='LOCAL_UNDO_ENABLED';
  6. PROPERTY_NAME PROPERTY_VALUE
  7. ------------------------------------------------------------
  8. LOCAL_UNDO_ENABLED TRUE

目标库:

点击(此处)折叠或打开

  1. COLUMNproperty_name FORMAT A30
  2. COLUMNproperty_value FORMAT A30
  3. SELECTproperty_name, property_value
  4. FROM database_properties
  5. WHERE property_name = 'LOCAL_UNDO_ENABLED';
  6. PROPERTY_NAME PROPERTY_VALUE
  7. ------------------------------------------------------------
  8. LOCAL_UNDO_ENABLED TRUE
  9. Elapsed:00:00:00.02
  10. sys.orcl12c>

2>检查源库是否未archivelog模式

源端:

点击(此处)折叠或打开

  1. sys. ora12c>ARCHIVE LOG LIST
  2. Database log mode Archive Mode
  3. Automatic archival Enabled
  4. Archive destination USE_DB_RECOVERY_FILE_DEST
  5. Oldest online log sequence 9
  6. Next log sequence to archive 11
  7. Current log sequence 11
  8. sys. ora12c>

目标端:

点击(此处)折叠或打开

  1. sys. orcl12c>archive log list
  2. Database log mode Archive Mode
  3. Automatic archival Enabled
  4. Archive destination USE_DB_RECOVERY_FILE_DEST
  5. Oldest online log sequence 1
  6. Next log sequence to archive 2
  7. Current log sequence 2
  8. sys. orcl12c>

3>检查源与目标字节顺序

源库:

点击(此处)折叠或打开

  1. sys. ora12c>select a.platform_id, a.platform_name, b.endian_format fromv$database a, v$transportable_platform b where a.platform_id=b.platform_id;
  2. PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
  3. ----------- -------------------- --------------
  4.      13 Linux x86 64-bit Little
  5. Elapsed: 00:00:00.01
  6. sys. ora12c>

目标库:

点击(此处)折叠或打开

  1. sys. orcl12c>select a.platform_id, a.platform_name, b.endian_formatfrom v$database a, v$transportable_platform b wherea.platform_id=b.platform_id;
  2. PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
  3. ----------- ------------------------------ --------------
  4.      13 Linux x86 64-bit Little
  5. Elapsed: 00:00:00.00
  6. sys. orcl12c>

4>检查字符集

源库:

点击(此处)折叠或打开

  1. sys. ora12c>SELECT a.value || '_' || b.value || '.' || c.value NLS_LANG
  2. FROM nls_database_parameters a, nls_database_parameters b,nls_database_parameters c
  3. WHERE a.parameter = 'NLS_LANGUAGE' AND b.parameter = 'NLS_TERRITORY' ANDc.parameter = 'NLS_CHARACTERSET';
  4. NLS_LANG
  5. ----------------------------------------
  6. AMERICAN_AMERICA.AL32UTF8
  7. Elapsed: 00:00:00.00
  8. sys. ora12c>

目标库:

点击(此处)折叠或打开

  1. sys. orcl12c>SELECT a.value || '_' || b.value || '.' || c.valueNLS_LANG
  2. FROM nls_database_parameters a, nls_database_parameters b,nls_database_parameters c
  3. WHERE a.parameter = 'NLS_LANGUAGE' AND b.parameter = 'NLS_TERRITORY' ANDc.parameter = 'NLS_CHARACTERSET';
  4. NLS_LANG
  5. ----------------------------------------
  6. AMERICAN_AMERICA.AL32UTF8
  7. Elapsed: 00:00:00.00
  8. sys. orcl12c>

(5)设置目标端db_create_file_dest

点击(此处)折叠或打开

  1. sys. orcl12c>alter system set
  2. db_create_file_dest='/u01/app/oracle/oradata';
  3. System altered.
  4. Elapsed: 00:00:00.01
  5. sys. orcl12c>

(6)在目标端使用RELOCATE进行PDB的迁移

点击(此处)折叠或打开

  1. sys. orcl12c>CREATE PLUGGABLE DATABASE qdatapdb FROM woqupdb@ora12cRELOCATE PATH_PREFIX = '/u01/app/oracle/oradata';
  2. Pluggable database created.
  3. Elapsed: 00:00:19.08
  4. sys. orcl12c>

目标日志信息:

点击(此处)折叠或打开

  1. 2018-03-08T12:29:09.340050-05:00
  2. CREATE PLUGGABLE DATABASE qdatapdb FROM woqupdb@ora12c RELOCATEPATH_PREFIX = '/u01/app/oracle/oradata'
  3. 2018-03-08T12:29:09.700432-05:00
  4. Opatch validation is skipped for PDB QDATAPDB (con_id=3)
  5. 2018-03-08T12:29:26.987023-05:00
  6. QDATAPDB(3):Endian type of dictionary set to little
  7. ****************************************************************
  8. Pluggable Database QDATAPDB with pdb id - 3 is created as UNUSABLE.
  9. If any errors are encountered before the pdb is marked as NEW,
  10. then the pdb must be dropped
  11. local undo-1, localundoscn-0x00000000000000e1
  12. ****************************************************************
  13. Applying media recovery for pdb-4099 from SCN 2571683 to SCN 2571702
  14. Remote log information: count-1
  15. thr-1, seq-11,logfile-/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc,los-2550003, nxs-18446744073709551615
  16. QDATAPDB(3):Media Recovery Start
  17. 2018-03-08T12:29:27.618887-05:00
  18. QDATAPDB(3):Serial Media Recovery started
  19. 2018-03-08T12:29:27.696115-05:00
  20. QDATAPDB(3):Media Recovery Log/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc
  21. 2018-03-08T12:29:28.212326-05:00
  22. QDATAPDB(3):Incomplete Recovery applied until change 2571702 time03/08/2018 12:29:27
  23. 2018-03-08T12:29:28.218004-05:00
  24. QDATAPDB(3):Media Recovery Complete (orcl12c)
  25. Completed:CREATE PLUGGABLE DATABASE qdatapdb FROM woqupdb@ora12c RELOCATE PATH_PREFIX ='/u01/app/oracle/oradata '

(7)目标端查看Relocating的PDB状态

点击(此处)折叠或打开

  1. sys. orcl12c>show pdbs
  2. CON_ID CON_NAME OPEN MODE RESTRICTED
  3. ---------- ------------------------------ ---------- ----------
  4.      2 PDB$SEED READ ONLY NO
  5.      3 QDATAPDB MOUNTED
  6. sys. orcl12c>select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;
  7. PDB_ID PDB_NAME STATUS REFRES REFRESH_INTERVAL
  8. ---------- ------------------------------ ---------- ----------------------
  9.      2 PDB$SEED NORMAL NONE
  10.      3 QDATAPDB RELOCATING NONE
  11. Elapsed: 00:00:00.03
  12. sys. orcl12c>

(8)检查源库PDB的状态

点击(此处)折叠或打开

  1. sys. ora12c>select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;
  2. PDB_ID PDB_NAME STATUS REFRES REFRESH_INTERVAL
  3. ---------- ------------------------------ ---------- ----------------------
  4.      2 PDB$SEED NORMAL NONE
  5.      5 WOQUPDB NORMAL NONE
  6. Elapsed: 00:00:00.03
  7. sys. ora12c>show pdbs
  8. CON_ID CON_NAME OPEN MODE RESTRICTED
  9. ---------- ------------------------------ ---------- ----------
  10.      2 PDB$SEED READ ONLY NO
  11.      5 WOQUPDB READ WRITE NO
  12. sys. ora12c>

(9)源库woqupdb中创建表空间

点击(此处)折叠或打开

  1. sys. woqupdb>create tablespace tbs1 datafile size 20M;
  2. Tablespace created.
  3. Elapsed: 00:00:00.30
  4. sys. woqupdb>

(10)源库woqupdb中创建测试表t

点击(此处)折叠或打开

  1. sys. woqupdb>create table t tablespace tbs1 as select * fromdba_objects;
  2. Table created.
  3. Elapsed: 00:00:00.87
  4. sys. woqupdb>select count(*) from t;
  5. COUNT(*)
  6. ----------
  7.  72668
  8. Elapsed: 00:00:00.03
  9. sys. woqupdb>

(11)目标库将QDatapdb打开

点击(此处)折叠或打开

  1. sys. orcl12c>show pdbs
  2. CON_ID CON_NAME OPEN MODE RESTRICTED
  3. ---------- ------------------------------ ---------- ----------
  4.      2 PDB$SEED READ ONLY NO
  5.      3 QDATAPDB MOUNTED
  6. sys. orcl12c>alter pluggable database qdatapdb open;
  7. Pluggable database altered.
  8. Elapsed: 00:00:13.54
  9. sys. orcl12c>select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;
  10. PDB_ID PDB_NAME STATUS REFRES REFRESH_INTERVAL
  11. ---------- ------------------------------ ---------- ----------------------
  12.      2 PDB$SEED NORMAL NONE
  13.      3 QDATAPDB NORMAL NONE
  14. Elapsed: 00:00:00.02
  15. sys. orcl12c>

目标端日志:

点击(此处)折叠或打开

  1. 2018-03-08T12:41:19.958204-05:00
  2. alter pluggable database qdatapdb open
  3. 2018-03-08T12:41:23.173900-05:00
  4. Applying media recovery for pdb-4099 from SCN 2571702 to SCN 2573455
  5. Remote log information: count-1
  6. thr-1, seq-11, logfile-/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc,los-2550003, nxs-18446744073709551615
  7. QDATAPDB(3):Media Recovery Start
  8. 2018-03-08T12:41:23.190073-05:00
  9. QDATAPDB(3):Serial Media Recovery started
  10. 2018-03-08T12:41:23.287360-05:00
  11. QDATAPDB(3):Media Recovery Log/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc
  12. QDATAPDB(3):Successfully added datafile 71 to media recovery
  13. QDATAPDB(3):Datafile #71: '/u01/app/oracle/oradata/ORCL12C/64FE807590395CF2E0535138A8C01D7F/datafile/o1_mf_tbs1_fb2xg3nw_.dbf'
  14. QDATAPDB(3):Resize operation completed for file# 68, old size 368640K, newsize 378880K
  15. 2018-03-08T12:41:24.402663-05:00
  16. QDATAPDB(3):Incomplete Recovery applied until change 2573455 time03/08/2018 12:41:20
  17. 2018-03-08T12:41:24.409434-05:00
  18. QDATAPDB(3):Media Recovery Complete (orcl12c)
  19. QDATAPDB(3):Autotune of undo retention is turned on.
  20. QDATAPDB(3):Undo initialization finished serial:0 start:38039070end:38039070 diff:0 ms (0.0 seconds)
  21. QDATAPDB(3):Database Characterset for QDATAPDB is AL32UTF8
  22. QDATAPDB(3):Opatch validation is skipped for PDB QDATAPDB (con_id=0)
  23. 2018-03-08T12:41:25.956271-05:00
  24. QDATAPDB(3):Opening pdb with no Resource Manager plan active
  25. QDATAPDB(3):JIT: pid 17154 requesting stop
  26. 2018-03-08T12:41:28.851631-05:00
  27. Applying media recovery for pdb-4099 from SCN 2573455 to SCN 2573475
  28. Remote log information: count-1
  29. thr-1, seq-11, logfile-/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc,los-2550003, nxs-18446744073709551615
  30. QDATAPDB(3):Media Recovery Start
  31. 2018-03-08T12:41:28.852244-05:00
  32. QDATAPDB(3):Serial Media Recovery started
  33. 2018-03-08T12:41:28.904519-05:00
  34. QDATAPDB(3):Media Recovery Log/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/foreign_archivelog/WOQUPDB/2018_03_08/o1_mf_1_11_2331664873_.arc
  35. 2018-03-08T12:41:29.547567-05:00
  36. QDATAPDB(3):Incomplete Recovery applied until change 2573475 time03/08/2018 12:41:26
  37. 2018-03-08T12:41:29.553124-05:00
  38. QDATAPDB(3):Media Recovery Complete (orcl12c)
  39. QDATAPDB(3):[17154] Successfully onlined Undo Tablespace 2.
  40. QDATAPDB(3):Undo initialization finished serial:0 start:38043849end:38043883 diff:34 ms (0.0 seconds)
  41. QDATAPDB(3):Database Characterset for QDATAPDB is AL32UTF8
  42. 2018-03-08T12:41:30.747145-05:00
  43. QDATAPDB(3):[17154] Successfully onlined Undo Tablespace 2.
  44. QDATAPDB(3):Undo initialization finished serial:0 start:38044944end:38045008 diff:64 ms (0.1 seconds)
  45. Opatch validation is skipped for PDB QDATAPDB (con_id=3)
  46. QDATAPDB(3):Deleting old file#15 from file$
  47. QDATAPDB(3):Deleting old file#16 from file$
  48. QDATAPDB(3):Deleting old file#17 from file$
  49. QDATAPDB(3):Deleting old file#18 from file$
  50. QDATAPDB(3):Deleting old file#19 from file$
  51. QDATAPDB(3):Deleting old file#20 from file$
  52. QDATAPDB(3):Deleting old file#21 from file$
  53. QDATAPDB(3):Deleting old file#22 from file$
  54. QDATAPDB(3):Adding new file#67 to file$(old file#15)
  55. QDATAPDB(3):Adding new file#68 to file$(old file#16)
  56. QDATAPDB(3):Adding new file#69 to file$(old file#17)
  57. QDATAPDB(3):Adding new file#70 to file$(old file#18)
  58. QDATAPDB(3):Adding new file#71 to file$(old file#22)
  59. QDATAPDB(3):Successfully created internal service qdatapdb.example.com atopen
  60. ****************************************************************
  61. Post plug operations are now complete.
  62. Pluggable database QDATAPDB with pdb id - 3 is now marked as NEW.
  63. ****************************************************************
  64. QDATAPDB(3):Pluggable database QDATAPDB dictionary check beginning
  65. QDATAPDB(3):Pluggable Database QDATAPDB Dictionary check complete
  66. QDATAPDB(3):Database Characterset for QDATAPDB is AL32UTF8
  67. QDATAPDB(3):Opatch validation is skipped for PDB QDATAPDB (con_id=0)
  68. 2018-03-08T12:41:33.183843-05:00
  69. QDATAPDB(3):Opening pdb with no Resource Manager plan active
  70. Pluggable database QDATAPDB opened read write
  71. Completed: alter pluggable database qdatapdb open

源端日志:

点击(此处)折叠或打开

  1. 2018-03-08T12:41:26.144319-05:00
  2. WOQUPDB(5):JIT: pid 3741 requesting stop
  3. WOQUPDB(5):KILL SESSION for sid=(77, 18753):
  4. WOQUPDB(5): Reason = PDB closeimmediate
  5. WOQUPDB(5): Mode = KILL HARD FORCE-/-/-
  6. WOQUPDB(5): Requestor = USER(orapid = 33, ospid = 3741, inst = 1)
  7. WOQUPDB(5): Owner = Process: USER(orapid = 57, ospid = 2560)
  8. WOQUPDB(5): Result = ORA-0
  9. Pluggable database WOQUPDB closed
  10. WOQUPDB(5):JIT: pid 3741 requesting stop
  11. Pluggable database WOQUPDB closed
  12. 2018-03-08T12:41:30.236316-05:00
  13. Deleted Oracle managed file /u01/app/oracle/oradata/ora12c/woqupdb/ORA12C/64FE807590395CF2E0535138A8C01D7F/datafile/o1_mf_tbs1_fb2x5o8w_.dbf
  14. Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_users_fb2pjxo6_.dbf
  15. Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_temp_fb2pjxo6_.dbf
  16. Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_undotbs1_fb2pjxo6_.dbf
  17. Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_sysaux_fb2pjxo5_.dbf
  18. Deleted file/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_system_fb2pjxo4_.dbf

通过日志中我们可以看到连接woqupdb的session(77, 18753)被Oracle在后台kill掉了,并且woqupdb中的数据文件被自动删除。

(12)检查源库状态

点击(此处)折叠或打开

  1. sys. ora12c>show pdbs
  2. CON_ID CON_NAME OPEN MODE RESTRICTED
  3. ---------- ------------------------------ ---------- ----------
  4.      2 PDB$SEED READ ONLY NO
  5. sys. ora12c>

源库已经被删除。

(13)检查目标QDatapdb中的测试数据


点击(此处)折叠或打开

  1. sys. qdatapdb>select count(*) from t;
  2. COUNT(*)
  3. ----------
  4.  72668
  5. Elapsed: 00:00:00.04
  6. sys. qdatapdb>select tablespace_name, file_name from dba_data_files;
  7. TABLESPACE_NAME FILE_NAME
  8. --------------------------------------------------------------------------------
  9. SYSTEM /u01/app/oracle/oradata/ORCL12C/64FE8075903
  10.                 95CF2E0535138A8C01D7F/datafile/o1_mf_system_fb2wq5p4_.dbf
  11. SYSAUX /u01/app/oracle/oradata/ORCL12C/64FE8075903
  12.                 95CF2E0535138A8C01D7F/datafile/o1_mf_sysaux_fb2wq5pn_.dbf
  13. UNDOTBS1 /u01/app/oracle/oradata/ORCL12C/64FE8075903
  14.                 95CF2E0535138A8C01D7F/datafile/o1_mf_undotbs1_fb2wq5po_.dbf
  15. USERS /u01/app/oracle/oradata/ORCL12C/64FE8075903
  16.                 95CF2E0535138A8C01D7F/datafile/o1_mf_users_fb2wq5pp_.dbf
  17. TBS1 /u01/app/oracle/oradata/ORCL12C/64FE8075903
  18.                 95CF2E0535138A8C01D7F/datafile/o1_mf_tbs1_fb2xg3nw_.dbf
  19. Elapsed: 00:00:00.07
  20. sys. qdatapdb>

目标端数据与源库一致,表空间同样同步到QDATAPDB。

(14)检查源库状态

点击(此处)折叠或打开

  1. sys. woqu>show pdbs
  2. CON_ID CON_NAME OPEN MODE RESTRICTED
  3. ---------- --------------- ---------- ----------
  4.      2 PDB$SEED READ ONLY NO
  5.      5 PDBTEST READ WRITE NO
  6. sys. woqu>

|作者简介


杨禹航·沃趣科技高级数据库技术专家

熟悉Oracle数据库内部机制,丰富的数据库及RAC集群层故障诊断、性能调优、OWI、数据库备份恢复及迁移经验。

阅读(624) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册