单实例数据库迁移到rac环境(二)
来源:互联网 发布:php序列化和反序列化 编辑:程序博客网 时间:2024/05/02 07:54
在上一节中,介绍了使用expdp/impdp方式迁移单实例数据库至rac环境,本节中将简要的介绍下,使用传输表空间方式将单实例数据库的表空间迁移至rac环境下,同样需要借助expdp/impdp数据泵实现,但同上一节所演示的结果相比,在大数据量情况下,使用传输表空间方式迁移,可以节约许多时间,但前途是数据库的字符集要一致,且需要将准备迁移的表空间进行只读操作!
环境介绍:
数据库的版本均为10.2.0.5,数据库字符集编码为utf8
操作系统的版本单实例数据库(源库)为rhel5.4 64 bit
rac(目标数据库)为ceontos4.8 64bit
一:在源库上创建表空间,用户,建表,插入数据,建索引以及目录对象等
SQL> create tablespace exp_rac1 datafile
2 '/u01/app/oracle/oradata/orcl/exp_rac1_01.dbf' size 300M
3 autoextend on next 10M maxsize unlimited
4* extent management local;
Tablespace created.
SQL> create tablespace exp_rac1_index datafile
2 '/u01/app/oracle/oradata/orcl/exp_rac1_index_01.dbf' size 300M
3 autoextend on next 10M maxsize unlimited
4* extent management local;
Tablespace created.
SQL> create user test2 identified by oracle
2 default tablespace exp_rac1
3 temporary tablespace temp
4 quota unlimited on exp_rac1
5* account unlock;
User created.
SQL> grant connect,resource to test2;
Grant succeeded.
SQL> create table test2.source2 as select * from dba_source;
Table created.
SQL> insert into test2.source2 select * from test2.source2;
295491 rows created.
SQL> /
590982 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('TEST2','SOURCE2');
PL/SQL procedure successfully completed.
SQL> select count(*) from test2.source2;
COUNT(*)
----------
1181964
SQL> select sum(bytes/(1024*1024)) MB from dba_extents
2 where segment_name='SOURCE2'
3* and owner='TEST2';
MB
----------
208
SQL> create index test2.i_source2
2 on test2.source2(type)
3* tablespace exp_rac1_index;
Index created.
SQL> select table_name,tablespace_name from dba_indexes
2* where owner='TEST2' and index_name='I_SOURCE2'
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SOURCE2 EXP_RAC1_INDEX
SQL> select sum(bytes/(1024*1024)) MB from dba_extents
2 where segment_name='I_SOURCE2'
3* and owner='TEST2'
MB
----------
28
SQL> create directory expdp_dir2 as '/home/oracle/expdp_dir2';
Directory created.
SQL> grant read,write on directory expdp_dir2 to test2;
Grant succeeded.
SQL> !mkdir -p /home/oracle/expdp_dir2
二:将需要迁移的表空间只读,并拷贝表空间数据文件,如果在asm环境下,可以使用rman操作
SQL> alter tablespace exp_rac1 read only;
Tablespace altered.
SQL> alter tablespace exp_rac1_index read only;
Tablespace altered.
[oracle@server49 ~]$ cd /home/oracle/expdp_dir2/
[oracle@server49 expdp_dir2]$ cp /u01/app/oracle/oradata/orcl/exp_rac1_01.dbf ./
[oracle@server49 expdp_dir2]$ cp /u01/app/oracle/oradata/orcl/exp_rac1_index_01.dbf ./
三:使用expdp命令导出传输表空间的元数据并复制到目标数据库上(rac环境)
[oracle@server49 ~]$ expdp \'sys/123456 as sysdba\' directory=expdp_dir2 dumpfile=source2.dmp
logfile=source2.log transport_tablespaces=exp_rac1,exp_rac1_index
Export: Release 10.2.0.5.0 - 64bit Production on Sunday, 01 January, 2012 22:44:51
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" directory=expdp_dir2
dumpfile=source2.dmp logfile=source2.log transport_tablespaces=exp_rac1,exp_rac1_index
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/expdp_dir2/source2.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 22:45:12
[oracle@server49 ~]$ ls /home/oracle/expdp_dir2/
exp_rac1_01.dbf exp_rac1_index_01.dbf source2.dmp source2.log
四:在目标数据库上创建用户和目录对象,并利用rman将表空间的数据文件导入到asm实例上
[oracle@rac1 ~]$ sqlplus sys/123456@racdb1 as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Jan 1 23:13:17 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> create user test2 identified by oracle account unlock;
User created.
SQL> grant connect,resource to test2;
Grant succeeded.
SQL> create directory expdp_dir2 as '/home/oracle/expdp_dir2';
Directory created.
SQL> grant read,write on directory expdp_dir2 to test2;
Grant succeeded.
RMAN> convert datafile '/home/oracle/expdp_dir2/exp_rac1_01.dbf' format '+DATA/racdb/datafile/exp_rac1_01.dbf';
Starting backup at 2012-01-01 23:41:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=126 instance=racdb1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/expdp_dir2/exp_rac1_01.dbf
converted datafile=+DATA/racdb/datafile/exp_rac1_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:24
Finished backup at 2012-01-01 23:42:14
RMAN> convert datafile '/home/oracle/expdp_dir2/exp_rac1_index_01.dbf' format
'+DATA/racdb/datafile/exp_rac1_index_01.dbf';
Starting backup at 2012-01-01 23:44:02
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/expdp_dir2/exp_rac1_index_01.dbf
converted datafile=+DATA/racdb/datafile/exp_rac1_index_01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:27
Finished backup at 2012-01-01 23:44:29
ASMCMD> pwd
+DATA/racdb/datafile
ASMCMD> ls
EXAMPLE.264.769205649
EXP_RAC1.301.771464511
EXP_RAC1_INDEX.297.771464643
LOCAL_ARCH_TEST.312.770588847
RAC_EXP.309.771454375
RAC_EXP_INDEX.305.771454413
SYSAUX.257.770222575
SYSTEM.256.770588849
UNDOTBS1.258.769205541
UNDOTBS2.265.769205765
USERS.259.769960507
exp_rac1_01.dbf
exp_rac1_index_01.dbf
五:利用impdp导入传输表空间的元数据
[oracle@rac1 ~]$ impdp \'sys/123456 as sysdba\' directory=expdp_dir2 dumpfile=source2.dmp
transport_datafiles='+DATA/racdb/datafile/exp_rac1_01.dbf','+DATA/racdb/datafile/exp_rac1_index_01.dbf'
Import: Release 10.2.0.5.0 - 64bit Production on Monday, 02 January, 2012 0:08:33
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" directory=expdp_dir2
dumpfile=source2.dmp transport_datafiles=+DATA/racdb/datafile/exp_rac1_01.dbf,
+DATA/racdb/datafile/exp_rac1_index_01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 00:08:52
六:测试
[oracle@rac1 ~]$ sqlplus sys/123456@racdb as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 2 00:09:20 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select count(*) from test2.source2;
COUNT(*)
----------
1181964
SQL> col table_name format a20
SQL> col tablespace_name format a20
SQL> select table_name,tablespace_name from dba_indexes
2 where owner='TEST2' and index_name='I_SOURCE2';
TABLE_NAME TABLESPACE_NAME
-------------------- --------------------
SOURCE2 EXP_RAC1_INDEX
SQL> select sum(bytes/(1024*1024)) MB from dba_extents
2 where segment_name='I_SOURCE2'
3* and owner='TEST2'
MB
----------
28
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- --------------------
+DATA/racdb/datafile/users.259.769960507 USERS
+DATA/racdb/datafile/sysaux.257.77022257 SYSAUX
+DATA/racdb/datafile/undotbs1.258.769205 UNDOTBS1
+DATA/racdb/datafile/system.256.77058884 SYSTEM
+DATA/racdb/datafile/example.264.7692056 EXAMPLE
+DATA/racdb/datafile/undotbs2.265.769205 UNDOTBS2
+DATA/racdb/datafile/local_arch_test.312 LOCAL_ARCH_TEST
+DATA/racdb/datafile/rac_exp.309.7714543 EXP_RAC
+DATA/racdb/datafile/rac_exp_index.305.7 EXP_RAC_INDEX
+DATA/racdb/datafile/exp_rac1_index_01.d EXP_RAC1_INDEX
+DATA/racdb/datafile/exp_rac1_01.dbf EXP_RAC1
- 单实例数据库迁移到rac环境(二)
- 单实例迁移到rac
- Oracle单实例数据库迁移到Oracle RAC 环境之(1)--系统架构篇
- Oracle单实例数据库迁移到Oracle RAC 环境之(1)--系统架构篇
- Oracle单实例数据库迁移到Oracle RAC 环境之(2)--实施篇
- Oracle单实例数据库迁移到Oracle RAC 环境之(3)--主备库Switchover
- Oracle单实例数据库迁移到Oracle RAC环境之--数据导出导入
- oracle从单实例数据库迁移到RAC数据库
- 单实例文件管理的数据库迁移到RAC+ASM
- 单实例到RAC数据迁移
- rac迁移到单实例实验
- RAC 实例 迁移到 单实例 -- 使用导出导入
- RAC 实例 迁移到 单实例 -- 使用RMAN Duplicate
- RAC 实例 迁移到 单实例 -- 使用RMAN Duplicate
- RAC 实例 迁移到 单实例 -- 使用导出导入
- RAC 实例 迁移到 单实例 -- 使用RMAN Duplicate
- RAC 实例 迁移到 单实例 -- 使用RMAN Duplicate
- RAC 实例 迁移到 单实例 -- 使用导出导入
- 关于性能测试中使用物理机和虚拟机的区别
- Android 配置 OpenCV2.3.1
- 从细节处剖析 教你登顶新浪微博
- APUE读书笔记-第13章-守护进程
- pcDuino安装vnc进行远程控制
- 单实例数据库迁移到rac环境(二)
- iOS应用内支付(IAP)的那些坑
- 九度笔记之 1349:数字在排序数组中出现的次数
- glib wpa_supplicant Unix上库编译错误解决与总结
- 如何使用TPTP中的IDatapool
- MFC为什么要安装_AfxCbtFilterHook钩子函数?
- android定制化开关机动画、铃音
- Sysfs_linux设备底层模型
- 利用百度贴吧进行网站优化的六个妙招