ITPub博客

首页 > 数据库 > Oracle > oracle10G 异构方式迁移表空间之二

oracle10G 异构方式迁移表空间之二

原创 Oracle 作者:西门吹牛 时间:2011-02-16 15:11:29 0 删除 编辑

西门说明:本次试验成功,
实验一是把源库表空间设置为只读状态进行的,实际上在生产库,一般是不允许这样操作的。
本次是实验二,采用从备份中产生传输文件,好处是生产库表空间不需要置为只读状态

源库:WinXP + Oracle10.2.0.1
目的库:Linux + ASM + RAC + Oracle10.2.0.1


1.准备工作:
查询源数据库平台信息

SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little

查询目标数据库平台信息

SQL> col platform_name for a40
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Linux IA (32-bit) Little

查询Oracle10g支持的平台转换

SQL> select * from v$transportable_platform;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big

已选择17行。

SQL>
SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

DIRECTORY_NAME
------------------------------
DIRECTORY_PATH
-----------------------------------------------------------------------------
ADMIN_DIR
C:ADEaime_10.2_nt_pushoracle/md/admin

DATA_PUMP_DIR
C:oracleproduct10.2.0adminorcldpdump

WORK_DIR
C:ADEaime_10.2_nt_pushoracle/work


已选择3行。

SQL>

2.源库创建一个独立的自包含表空间,用于测试

SQL> select name from v$datafile;

NAME
------------------------------------------------------
C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF
C:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF
C:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF
C:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF

create tablespace trans datafile 'C:ORACLEPRODUCT10.2.0ORADATAORCLtrans01.dbf' size 2M;

create user trans identified by trans default tablespace trans;

grant connect,resource to trans;

connect trans/trans

create table test as select * from all_objects where rownum < 1000;

commit;

实际上该步骤在26节已经完成,26节已经迁移过trans表空间,我们这里从目标数据库删除trans表空间即可,后面继续

确认该表空间是可以传输的
SQL> conn / as sysdba
已连接。
SQL> EXEC dbms_tts.transport_set_check('trans',true);

PL/SQL 过程已成功完成。

SQL> select * from transport_set_violations;

未选定行


3.对数据文件做一个备份
注意:传输表空间不必置为只读状态

SQL> col file_name format a60
SQL> select file_id,file_name from dba_data_files;

FILE_ID FILE_NAME
---------- ---------------------------------------------------------
4 C:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF
3 C:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF
2 C:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF
1 C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF
5 C:ORACLEPRODUCT10.2.0ORADATAORCLTRANS01.DBF

数据库必须是归档模式才可以用下面的方法
不过一般生产库都是归档模式

RMAN> backup as copy datafile 5 format 'C:oracleproduct10.2.0expdptrans01.dbf';

这个备份产生的文件大小和真实的文件是几乎一样的大小,毕竟是copy的方式备份。


4:利用备份产生的文件生成转换格式的文件
RMAN> transport tablespace trans tablespace destination 'C:oracleproduct10.2.0td' auxiliary destination 'C:oracleproduct10.2.0ad' ;

上面的两个目录都是空目录C:oracleproduct10.2.0ad 和 C:oracleproduct10.2.0td


注意第一次启动数据库的归档模式后,最好执行 alter system switch logfile;

否则上面执行的时候报错
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: tranport tablespace 命令 (在 02/16/2011 12:28:32 上) 失败
ORA-01405: 提取的列值为 NULL

后来又报告下面的错误

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: tranport tablespace 命令 (在 02/16/2011 12:31:32 上) 失败
RMAN-03015: 在存储的脚本Memory Script中出现错误
RMAN-06026: 有些目标没有找到 - 终止恢复
RMAN-06024: 没有找到控制文件的备份或副本来复原

必须备份整个数据库才可以进行下去

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup
tag mjs_test_standby
format 'C:oracleproduct10.2.0rmanorcl%t_s%s_p%p'
database include current controlfile ;
sql 'alter system archive log current';
backup
archivelog all delete input
format 'C:oracleproduct10.2.0rmanorcl_archlog_%d.%s';
release channel t1;
release channel t2;
release channel t3;
allocate channel node1 type disk format 'C:oracleproduct10.2.0rmanorcl_control_%d_%s_%p_%c';
Backup current controlfile;
release channel node1;
}

备份过程很长,过程略

transport tablespace 过程很长,过程略

从上面过程看,oracle是先建立了一个辅助数据库,(用到了auxiliary参数,然后把辅助数据库打开,把表空间read only,然后导出,然后删除辅助数据库)

过程结束后,

auxiliary destination 'C:oracleproduct10.2.0ad' 目录下只有一些空目录,没有数据文件
tablespace destination 'C:oracleproduct10.2.0td' 目录下有四个文件,把这四个文件复制到目标数据库的某个目录下


5: 复制这几个文件目标数据库的/home/oracle目录下

6: 因为源库和目标库的Endian格式不一致,所以需要进行转换
在目的库进行转换。

连接到目标数据库:
rman target /

执行转换语句如下,看来不用from platform语句是对的
CONVERT DATAFILE '/home/oracle/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD'
to platform 'Linux IA (32-bit)'
DB_FILE_NAME_CONVERT='/home/oracle','/home/oracle/linux';

过程如下

RMAN> CONVERT DATAFILE '/home/oracle/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD'
2> to platform 'Linux IA (32-bit)'
from platform 'Microsoft Windows IA (32-bit)'
3> 4> DB_FILE_NAME_CONVERT='/home/oracle','/home/oracle/linux';


Starting backup at 16-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=133 instance=RACDB1 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/16/2011 22:42:03
RMAN-06576: platform 'Linux IA (32-bit)' (10) found in header of datafile /home/oracle/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD does not match specified platform name 'Microsoft Windows IA (32-bit)' (7)

RMAN>
RMAN> CONVERT DATAFILE '/home/oracle/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD'
2> to platform 'Linux IA (32-bit)'
3> DB_FILE_NAME_CONVERT='/home/oracle','/home/oracle/linux';

Starting backup at 16-FEB-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD
converted datafile=/home/oracle/linux/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 16-FEB-11

RMAN>

转换后的结果
[oracle@node1 linux]$ ls
DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD
[oracle@node1 linux]$ pwd
/home/oracle/linux
[oracle@node1 linux]$

7:文件转换完成后,把转换后的文件复制到ASM中
RMAN> convert datafile '/home/oracle/linux/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD' format '+RAC_DISK/racdb/datafile/trans01.dbf';

RMAN> convert datafile '/home/oracle/linux/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD' format '+RAC_DISK/racdb/datafile/trans01.dbf';

Starting backup at 16-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=124 instance=RACDB1 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/linux/DATA_D-ORCL_I-1266117835_TS-TRANS_FNO-5_02M4O9QD
converted datafile=+RAC_DISK/racdb/datafile/trans01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 16-FEB-11

RMAN>

8:在目的库创建directory对象
我们利用现成的directory对象
directory是数据库建立的目录,如果没有,需要通过 create directory DD as '/home/oracle' ; 建立,这个目录我们我们之前的实验已经建立了。

SQL>col DIRECTORY_PATH format a60
SQL>col DIRECTORY_NAME format a20
SQL>select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;


DIRECTORY_NAME DIRECTORY_PATH
-------------------- ------------------------------------------------------------
DD /home/oracle
WORK_DIR /opt/ora10g/product/10.2.0/db_1/work
SD +RAC_DISK/racdb/datafile
ADMIN_DIR /opt/ora10g/product/10.2.0/db_1/md/admin
DATA_PUMP_DIR /opt/ora10g/product/10.2.0/db_1/rdbms/log/

SQL>

9:导入数据库表空间
dumpfile 参数是该目录下的导出文件
transport_datafiles 是绝对路径名称,是指ASM中需要关联的数据文件。

[oracle@node1 ~]$ impdp system/tagal dumpfile=TRANS.DMP directory=DD transport_datafiles='+RAC_DISK/racdb/datafile/trans01.dbf'

成功了

10.检查数据

sqlplus trans/trans


SQL> select count(*) from test;

COUNT(*)
----------
999

SQL> exit


成功了

注意,上面第八步的时候,可以指定impdp的参数remap_schema=trans:other
把源库上面trans用户的所有对象转移到目标库other用户下面

[@more@]oracle10G 异构方式迁移表空间之一

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10771/viewspace-1046071/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 丢包处理过程
请登录后发表评论 登录
全部评论

注册时间:2013-06-13

  • 博文量
    18
  • 访问量
    122936