XXX数据库迁移,由于数据量比较大,有1T,用导出导入的方式比较花费时间,采用表空间传输方式提高迁移速度。
1、Environment Profile:
SourceDB Environment:
OS: | oracle@QC_P570_728:/home/oracle>uname -a AIX QC_P570_728 1 6 00C4035E4C00 |
DB Version: | SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production |
ENDIAN_FORMAT: | SQL> SELECT d.NAME, i.VERSION, d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d, V$INSTANCE i 3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME 4 and d.DB_UNIQUE_NAME = i.INSTANCE_NAME; NAME VERSION PLATFORM_NAME ENDIAN_FORMAT --------- ----------------- ------------------------------------ -------------- CSZG4 11.2.0.2.0 AIX-Based Systems (64-bit) Big |
DB Instance: | CSZG4 |
DB Characterset: | SQL> select * from nls_database_parameters where parameter in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET'); PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CHARACTERSET ZHS16GBK |
TargetDB Environment:
OS: | oracle@vm-test-133 shzw]$ uname -a Linux vm-test-133 2.6.32-131.0.15.el6.x86_64 #1 SMP Tue May 10 15:42:40 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux |
DB Version: | SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production |
ENDIAN_FORMAT: | SQL> SELECT d.NAME, i.VERSION, d.PLATFORM_NAME, ENDIAN_FORMAT 2 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d, V$INSTANCE i 3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME 4 and d.DB_UNIQUE_NAME = i.INSTANCE_NAME; NAME VERSION PLATFORM_NAME ENDIAN_FORMAT --------- ----------------- --------------------------- ----------------- SHZW 11.2.0.3.0 Linux x86 64-bit Little |
DB Instance: | SHZW |
DB Characterset: | SQL> select * from nls_database_parameters where parameter in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET'); PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CHARACTERSET ZHS16GBK |
从上面两环境的对比表可以看出,操作系统不同,平台间的endian不同,那么在导入操作之前必须首先转换源平台的表空间到目标格式;如果endian相同,即使是不同操作系统,则可以跳过转换的步骤,当然前提是各平台的数据库版本不能低于10g。数据转换可以在源库进行也可以在目标库进行,它们的转换脚本略有差别,具体在后面操作中提到。
表空间传输方式在目标库的准备工作:
1、 在目标库不用创建任何表空间,
2、 创建与要传输的表空间相关用户,如果用户在源库上指定了默认表空间为要传输的表空间,在这儿创建用户时不用指定(因为还没有表空间),
待传输完后再进行修改用户的默认表空间。
2、Operating Steps:
Source DB:
1、 确认要传输的表空间和相关数据文件:如data01, data02
Select * from v$tablesapce;
Col name format a60;
Set linesize 200;
Select ts#,file#,name from v$datafile order by ts#,file#;
2、 Create directory
oracle$mkdir /u10/app【目录随便定义】
SQL>create directory dmpdir as ‘/u01/app’;
SQL>grant read ,write on directory dmpdir to system;
3、 将要传输的表空间置为只读状态
Alter tablespace data read only;
Alter tablespace data01 read only;
4、导出元数据
expdp system/sys@orcl directory=dmp dumpfile=tts.dmp transport_tablespaces=data01,data02logfile=tts.log
5、转换数据(如果是相同平台迁移,此步可以略去)
$rman target=/
RMAN> convert tablespace 'DATA'
to platform="Linux x86 64-bit"
db_file_name_convert='/home/oracle/oradata/data01.dbf','/tmp/data01.dbf';
note:如果在目标库转换,则因为目标库还没有表空间,则进行数据文件转换。采用如下脚本:
RMAN> convert datafile'/home/oracle/oradata/data01.dbf'
to platform =" Linux x86 64-bit "
from platform ="AIX-Based Systems(64-bit) "
db_file_name_convert='/home/oracle/oradata/data01.dbf','/tmp/data01.dbf';
6、ftp转换后的数据文件到目标机的数据文件存放位置
Targe DB(只用创建相关用户默认到users表空间,不用创建表空间):
1、Create directory
oracle$mkdir /u10/app【目录随便定义】
SQL>reate directory dmpdir as ‘/u01/app’;
SQL>grant read ,write on directory dmpdir to system;
2、 将在源头库所在的机器上的导出文件tts.dmp文件拷贝到目标库的dmpdir目录
3、导入元数据
impdp system/sys@dbtest directory=dmpdir dumpfile=tts.dmplogfile=tts2.logtransport_datafiles='E:\oracle\product\10.2.0\oradata\dbtest\X01.DBF',
'E:\oracle\product\10.2.0\oradata\dbtest\Y01.DBF'(数据文件多也可以写到参数文件中去,parfile=db_imp.par)
4、检查表空间,数据
5、执行完后将表空间置为正常状态(包括目标库和源头库)
Alter tablespace data01 read write;
Alter tablespace data02 read write;
6、修改用户默认表空间
Alter user cd default tablespace data01;
Alter usdr ad default tablespace data02;