Oracle 12c系列(六)｜Relocate a PDB

原创

沃趣科技

发布于 2018-05-16 16:37:20

1.5K3

发布于 2018-05-16 16:37:20

文章被收录于专栏：沃趣科技沃趣科技

｜导语

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)源库

sys. ora12c>show pdbs
CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED                       READ ONLY  NO
     5WOQUPDB                        READ WRITENO
sys. ora12c>select file_name from cdb_data_files where con_id=5;
FILE_NAME
----------------------------------------------------------------------
/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_system_fb2pjxo4_.dbf
/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_sysaux_fb2pjxo5_.dbf
/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_undotbs1_fb2pjxo6_.dbf
/u01/app/oracle/oradata/ora12c/ora12cpdbo1_mf_users_fb2pjxo6_.dbf
Elapsed: 00:00:00.05
sys. ora12c>

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

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

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

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

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

1>检查shared undo模式

源库：

COL PROPERTY_NAME FOR A30
COL PROPERTY_VALUE FOR A30
SELECT property_name, property_value
FROM database_properties
WHERE property_name='LOCAL_UNDO_ENABLED';
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------------------------------------
LOCAL_UNDO_ENABLED             TRUE

目标库：

COLUMNproperty_name FORMAT A30
COLUMNproperty_value FORMAT A30
SELECTproperty_name, property_value
FROM   database_properties
WHERE  property_name = 'LOCAL_UNDO_ENABLED';
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------------------------------------
LOCAL_UNDO_ENABLED             TRUE
Elapsed:00:00:00.02
sys.orcl12c>

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

源端：

sys. ora12c>ARCHIVE LOG LIST
Database log mode             Archive Mode
Automatic archival            Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     9
Next log sequence to archive   11
Current log sequence           11
sys. ora12c>

目标端：

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

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

源库：

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;
PLATFORM_ID PLATFORM_NAME       ENDIAN_FORMAT
----------- -------------------- --------------
     13 Linux x86 64-bit     Little
Elapsed: 00:00:00.01
sys. ora12c>

目标库：

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;
PLATFORM_ID PLATFORM_NAME                 ENDIAN_FORMAT
----------- ------------------------------ --------------
     13 Linux x86 64-bit               Little
Elapsed: 00:00:00.00
sys. orcl12c>

4>检查字符集

源库：

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

目标库：

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

(5)设置目标端db_create_file_dest

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

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

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

目标日志信息：

2018-03-08T12:29:09.340050-05:00
CREATE PLUGGABLE DATABASE qdatapdb FROM woqupdb@ora12c RELOCATEPATH_PREFIX = '/u01/app/oracle/oradata'
2018-03-08T12:29:09.700432-05:00
Opatch validation is skipped for PDB QDATAPDB (con_id=3)
2018-03-08T12:29:26.987023-05:00
QDATAPDB(3):Endian type of dictionary set to little
****************************************************************
Pluggable Database QDATAPDB with pdb id - 3 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-0x00000000000000e1
****************************************************************
Applying media recovery for pdb-4099 from SCN 2571683 to SCN 2571702
Remote log information: count-1
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
QDATAPDB(3):Media Recovery Start
2018-03-08T12:29:27.618887-05:00
QDATAPDB(3):Serial Media Recovery started
2018-03-08T12:29:27.696115-05:00
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
2018-03-08T12:29:28.212326-05:00
QDATAPDB(3):Incomplete Recovery applied until change 2571702 time03/08/2018 12:29:27
2018-03-08T12:29:28.218004-05:00
QDATAPDB(3):Media Recovery Complete (orcl12c)
Completed:CREATE PLUGGABLE DATABASE qdatapdb FROM woqupdb@ora12c RELOCATE PATH_PREFIX ='/u01/app/oracle/oradata '

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

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

(8)检查源库PDB的状态

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

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

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

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

sys. woqupdb>create table t tablespace tbs1 as select * fromdba_objects;
Table created.
Elapsed: 00:00:00.87
sys. woqupdb>select count(*) from t;
COUNT(*)
----------
 72668
Elapsed: 00:00:00.03
sys. woqupdb>

(11)目标库将QDatapdb打开

sys. orcl12c>show pdbs
CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
     2 PDB$SEED                       READ ONLY  NO
     3 QDATAPDB                       MOUNTED
sys. orcl12c>alter pluggable database qdatapdb open;
Pluggable database altered.
Elapsed: 00:00:13.54
sys. orcl12c>select pdb_id, pdb_name, status, refresh_mode,refresh_interval from cdb_pdbs;
PDB_ID PDB_NAME                       STATUS     REFRES REFRESH_INTERVAL
---------- ------------------------------ ---------- ----------------------
     2 PDB$SEED                       NORMAL     NONE
     3 QDATAPDB                       NORMAL     NONE
Elapsed: 00:00:00.02
sys. orcl12c>

目标端日志:

2018-03-08T12:41:19.958204-05:00
alter pluggable database qdatapdb open
2018-03-08T12:41:23.173900-05:00
Applying media recovery for pdb-4099 from SCN 2571702 to SCN 2573455
Remote log information: count-1
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
QDATAPDB(3):Media Recovery Start
2018-03-08T12:41:23.190073-05:00
QDATAPDB(3):Serial Media Recovery started
2018-03-08T12:41:23.287360-05:00
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
QDATAPDB(3):Successfully added datafile 71 to media recovery
QDATAPDB(3):Datafile #71: '/u01/app/oracle/oradata/ORCL12C/64FE807590395CF2E0535138A8C01D7F/datafile/o1_mf_tbs1_fb2xg3nw_.dbf'
QDATAPDB(3):Resize operation completed for file# 68, old size 368640K, newsize 378880K
2018-03-08T12:41:24.402663-05:00
QDATAPDB(3):Incomplete Recovery applied until change 2573455 time03/08/2018 12:41:20
2018-03-08T12:41:24.409434-05:00
QDATAPDB(3):Media Recovery Complete (orcl12c)
QDATAPDB(3):Autotune of undo retention is turned on.
QDATAPDB(3):Undo initialization finished serial:0 start:38039070end:38039070 diff:0 ms (0.0 seconds)
QDATAPDB(3):Database Characterset for QDATAPDB is AL32UTF8
QDATAPDB(3):Opatch validation is skipped for PDB QDATAPDB (con_id=0)
2018-03-08T12:41:25.956271-05:00
QDATAPDB(3):Opening pdb with no Resource Manager plan active
QDATAPDB(3):JIT: pid 17154 requesting stop
2018-03-08T12:41:28.851631-05:00
Applying media recovery for pdb-4099 from SCN 2573455 to SCN 2573475
Remote log information: count-1
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
QDATAPDB(3):Media Recovery Start
2018-03-08T12:41:28.852244-05:00
QDATAPDB(3):Serial Media Recovery started
2018-03-08T12:41:28.904519-05:00
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
2018-03-08T12:41:29.547567-05:00
QDATAPDB(3):Incomplete Recovery applied until change 2573475 time03/08/2018 12:41:26
2018-03-08T12:41:29.553124-05:00
QDATAPDB(3):Media Recovery Complete (orcl12c)
QDATAPDB(3):[17154] Successfully onlined Undo Tablespace 2.
QDATAPDB(3):Undo initialization finished serial:0 start:38043849end:38043883 diff:34 ms (0.0 seconds)
QDATAPDB(3):Database Characterset for QDATAPDB is AL32UTF8
2018-03-08T12:41:30.747145-05:00
QDATAPDB(3):[17154] Successfully onlined Undo Tablespace 2.
QDATAPDB(3):Undo initialization finished serial:0 start:38044944end:38045008 diff:64 ms (0.1 seconds)
Opatch validation is skipped for PDB QDATAPDB (con_id=3)
QDATAPDB(3):Deleting old file#15 from file$
QDATAPDB(3):Deleting old file#16 from file$
QDATAPDB(3):Deleting old file#17 from file$
QDATAPDB(3):Deleting old file#18 from file$
QDATAPDB(3):Deleting old file#19 from file$
QDATAPDB(3):Deleting old file#20 from file$
QDATAPDB(3):Deleting old file#21 from file$
QDATAPDB(3):Deleting old file#22 from file$
QDATAPDB(3):Adding new file#67 to file$(old file#15)
QDATAPDB(3):Adding new file#68 to file$(old file#16)
QDATAPDB(3):Adding new file#69 to file$(old file#17)
QDATAPDB(3):Adding new file#70 to file$(old file#18)
QDATAPDB(3):Adding new file#71 to file$(old file#22)
QDATAPDB(3):Successfully created internal service qdatapdb.example.com atopen
****************************************************************
Post plug operations are now complete.
Pluggable database QDATAPDB with pdb id - 3 is now marked as NEW.
****************************************************************
QDATAPDB(3):Pluggable database QDATAPDB dictionary check beginning
QDATAPDB(3):Pluggable Database QDATAPDB Dictionary check complete
QDATAPDB(3):Database Characterset for QDATAPDB is AL32UTF8
QDATAPDB(3):Opatch validation is skipped for PDB QDATAPDB (con_id=0)
2018-03-08T12:41:33.183843-05:00
QDATAPDB(3):Opening pdb with no Resource Manager plan active
Pluggable database QDATAPDB opened read write
Completed: alter pluggable database qdatapdb open

源端日志：

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

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

(12)检查源库状态

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

源库已经被删除。

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

sys. qdatapdb>select count(*) from t;
COUNT(*)
----------
 72668
Elapsed: 00:00:00.04
sys. qdatapdb>select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME      FILE_NAME
--------------------------------------------------------------------------------
SYSTEM              /u01/app/oracle/oradata/ORCL12C/64FE8075903
                95CF2E0535138A8C01D7F/datafile/o1_mf_system_fb2wq5p4_.dbf
SYSAUX               /u01/app/oracle/oradata/ORCL12C/64FE8075903
                95CF2E0535138A8C01D7F/datafile/o1_mf_sysaux_fb2wq5pn_.dbf
UNDOTBS1            /u01/app/oracle/oradata/ORCL12C/64FE8075903
                95CF2E0535138A8C01D7F/datafile/o1_mf_undotbs1_fb2wq5po_.dbf
USERS               /u01/app/oracle/oradata/ORCL12C/64FE8075903
                95CF2E0535138A8C01D7F/datafile/o1_mf_users_fb2wq5pp_.dbf
TBS1                 /u01/app/oracle/oradata/ORCL12C/64FE8075903
                95CF2E0535138A8C01D7F/datafile/o1_mf_tbs1_fb2xg3nw_.dbf
Elapsed: 00:00:00.07
sys. qdatapdb>

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

(14)检查源库状态

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

｜作者简介

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

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

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

oracle

数据迁移

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

oracle

数据迁移

登录后参与评论

0 条评论

热度