测试同学有一个需求,需要将应用某一个版本,对应的数据库信息复制一份,通过了解,整理需求如下:
原始数据库IP:10.1.1.1
目标数据库IP:10.1.1.2
将原始数据库的用户USER_A,所有对象(表、索引、序列),复制至目标数据库一份,用户名为同名的USER_A,复制此库的目的,用于下一阶段,不同应用版本的并行测试。
环境:
原始目标数据库,数据库版本相同(11.2.0.4),操作系统相同(Linux 2.6),相应目录结构基本一致。
原始库用户USER_A数据容量约为60G,有数据表空间、索引表空间如下:
数据表空间TB_DAT,含有tb_dat00.dat、tb_dat01.dat、tb_dat02.dat、tb_dat03.dat、tb_dat04.dat数据文件。
索引表空间TB_IDX,含有tb_idx00.dat、tb_idx01.dat、tb_idx02.dat数据文件。
对于数据迁移,可能有不止一种方法,例如:
1. 使用exp/imp;
2. 使用expdp/impdp;
3. 使用OGG;
4. XTTS;
5. TTS;
基于以上需求,
1. 原始库和目标库平台相同、数据库版本相同。
2. 允许停机时间,但尽量不要太久,避免影响正常的测试工作。
上述5种方法,其实可以归类为3种,停机用时比较:
方案1和2 > 方案4和5 > 方案3
对于方案3,没有这种测试的环境,因此无法考虑(何况自己不熟:))。
对于方案1和2,导出和导入操作,时间会久一些。
方案4和5比较合适,由于平台信息基本一致,连字节序也一致,不太需要增量的数据迁移,因此,方案5是最简单的。
之前整理过一篇《传输表空间TTS操作》,操作基本一致。
步骤1. 准备工作
为了使用数据泵,原始库和目标库,需要创建目录,路径和名称相同,
create directory tts as '/oracle/dump/tts';
步骤2. 自包含检查,确认导出的表空间中,不会引用其他表空间的内容
此处需要检查两个表空间,
exec dbms_tts.transport_set_check('tb_dat,tb_idx',true);
步骤3. 将原始库表空间置为只读,避免有数据更新,此时原始库只允许读,不允许写入了,
alter tablespace tb_dat read only;
alter tablespace tb_idx read only;
步骤4. 导出元数据
expdp system/oracle dumpfile=tb.dmp directory=tts transport_tablespaces=tb_dat,tb_idx logfile=tb.log statistics=none
Export: Release 11.2.0.4.0 - Production on Wed Mar 7 11:38:01 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "statistics=none" Location: Command Line, ignored.
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=tb.dmp directory=tts transport_tablespaces=tb_dat,tb_idx logfile=departure.log reuse_dumpfiles=true
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/oracle/dump/tts/departure.dmp
******************************************************************************
Datafiles required for transportable tablespace TB_DAT:
/oracle/oradata_ORA50T/ORA50T/tb_dat_00.dbf
/oracle/oradata_ORA50T/ORA50T/tb_dat_01.dbf
/oracle/oradata_ORA50T/ORA50T/tb_dat_02.dbf
/oracle/oradata_ORA50T/ORA50T/tb_dat_03.dbf
/oracle/oradata_ORA50T/ORA50T/tb_04.dbf
Datafiles required for transportable tablespace TB_IDX:
/oracle/oradata_ORA50T/ORA50T/tb_idx_00.dbf
/oracle/oradata_ORA50T/ORA50T/tb_idx_01.dbf
/oracle/oradata_ORA50T/ORA50T/tb_idx_02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Mar 7 11:39:03 2018 elapsed 0 00:01:02
步骤5. 复制数据文件
将上述元数据dmp的文件,以及原始库的数据文件,tb_dat00.dat、tb_dat01.dat、tb_dat02.dat、tb_dat03.dat、tb_dat04.dat、tb_idx00.dat、tb_idx01.dat、tb_idx02.dat,拷贝目标库中。
步骤6. 将原始库表空间置为读写,可正常使用了,
alter tablespace tb_dat read write;
alter tablespace tb_idx read write;
步骤7. 导入目标库
impdp system/oracle dumpfile=tb.dmp directory=tts transport_datafiles=/oracle/oradata_ORA30T/ORA30T/tb_dat_00.dbf,/oracle/oradata_ORA30T/ORA30T/tb_dat_01.dbf,/oracle/oradata_ORA30T/ORA30T/tb_dat_02.dbf,/oracle/oradata_ORA30T/ORA30T/tb_dat_03.dbf,/oracle/oradata_ORA30T/ORA30T/tb_dat_04.dbf,/oracle/oradata_ORA30T/ORA30T/tb_idx_00.dbf,/oracle/oradata_ORA30T/ORA30T/tb_idx_01.dbf,/oracle/oradata_ORA30T/ORA30T/tb_idx_02.dbf logfile=tb.log
Import: Release 11.2.0.4.0 - Production on Wed Mar 7 16:09:58 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=tb.dmp directory=tts transport_datafiles=/oracle/oradata_ORA30T/ORA30T/tb_dat_00.dbf,/oracle/oradata_ORA30T/ORA30T/tb_dat_01.dbf,/oracle/oradata_ORA30T/ORA30T/tb_dat_02.dbf,/oracle/oradata_ORA30T/ORA30T/tb_dat_03.dbf,/oracle/oradata_ORA30T/ORA30T/tb_dat_04.dbf,/oracle/oradata_ORA30T/ORA30T/tb_idx_00.dbf,/oracle/oradata_ORA30T/ORA30T/tb_idx_01.dbf,/oracle/oradata_ORA30T/ORA30T/tb_idx_02.dbf logfile=tb.log logfile=tb.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Wed Mar 7 16:11:12 2018 elapsed 0 00:01:13
步骤8. 检查表空间状态
select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TB_DAT READ ONLY
TB_IDX READ ONLY
步骤9. 将表空间置为可读写
alter tablespace tb_dat read write;
alter tablespace tb_idx read write;
select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TB_DAT ONLINE
TB_IDX ONLINE
步骤10. 操作序列
由于TTS完成导入的对象,未包含序列对象,因此可以从源库,拼接出创建序列的语句,
select 'create sequence user_a.' || SEQUENCE_NAME || ' minvalue ' || MIN_VALUE || ' maxvalue ' || MAX_VALUE || ' start with ' || LAST_NUMBER || ' increment by ' || INCREMENT_BY || ' cache ' || CACHE_SIZE || ';'
from dba_sequences
where SEQUENCE_OWNER = 'USER_A';
得到如下语句,其实最重要的,是可以知道当前序列start with,这样可以在目标库继续使用,
create sequence user_a.s_seq minvalue 1 maxvalue 9999999999999999999999999999 start with 1001 increment by 1 cache 1000;
...
至此,完成原始库的复制操作。
其实,在操作过程中,会碰见一些问题,例如,
1. 目标库必须有和源库同名的数据库用户名才可以,否则执行impdp提示,
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user USER_A does not exist in the database
2. 导入的时候发现tb_dat和tb_idx表空间,除了USER_A用户用了,USER_B用户也有涉及,因为提示,
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user USER_B does not exist in the database
此时可以“将错就错”了,目标库创建USER_B用户,导入完成了,删除用户USER_B,及其所有对象,
drop user dep cascade;
要记住原始库、目标库表空间的状态,需要置为read,write,否则只能只读。
如果您觉得本文有帮助,欢迎关注转发:bisal的个人杂货铺,