linux下的Oracle迁移新人,记一次oracle 通过手动xtts的方法从WINDOW迁移到LINUX（一）...

基本信息*

IP

操作系统

数据库版本

数据库信息

源端

10.10.41.85

windows

oracle11.2.04

test(这里用TEST代替真正的SID)

目标端

10.10.41.50

centos7.6

oracle11.2.04

test(这里用TEST代替真正的SID)

测试目的:

检查确认TEST(这里TEST代替数据库名)从WINDOWS迁移到LINUX是否具有可行性。

这里通过手动XTTS的方法进行迁移，由于XTTS的PERL脚本不支持源服务端为WINDOWS，所以这里用手动XTTS(RMAN备份集方式)。

迁移前检查

1.检查平台

SELECT PLATFORM_id,PLATFORM_NAME, ENDIAN_FORMAT

FROM V$TRANSPORTABLE_PLATFORM

WHERE PLATFORM_ID =

( SELECT PLATFORM_ID

FROM V$DATABASE );

2.检查字符集

select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

3检查时区

select dbtimezone from dual;

4.检查命令进行自包含检查(重点)

exec sys.dbms_tts.transport_set_check('TEST_INDEX,TEST1_INDEX,TEST2_DATA,TEST3_DATA,TEST4_DATA,TEST3_INDEX',TRUE);

select select * from TRANSPORT_SET_VIOLATIONS;

开始迁移

1.在源端 (进行全备份)

RMAN TARGET /

run{

allocate channel t1 type disk;

backup incremental level=0 tablespace TEST_INDEX,TEST1_INDEX,TEST2_DATA,TEST3_DATA,TEST4_DATA,TEST3_INDEX

format 'I:\pglng-backup\rman\TESTtbs0_%U'tag=TESTtbs0;

release channel c1;

}

2.在目的端进行全量恢复

DECLARE

devtype varchar2(256);

done Boolean;

BEGIN

Devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'t1');

sys.dbms_backup_restore.restoreSetDatafile;

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'/data/test/test1_INDEX.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'/data/test/test2_INDEX.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'/data/test/test3_DATA.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'/data/test/test1_DATA.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'/data/test/test2_DATA.DBF');

sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>10,toname=>'/data/test/test3_INDEX.DBF');

sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/u01/rman/leve0/testTBS0_0HV7GF2Q_1_1',params=>null);

sys.dbms_backup_restore.deviceDeallocate;

END;

/

第一次增量备份

rman target /

run{

backup incremental level=1 tablespace TEST_INDEX,TEST1_INDEX,TEST2_DATA,TEST3_DATA,TEST4_DATA,TEST3_INDEX

format 'I:\test-backup\test\testtbs1_%U'tag=testtbs1;

}

第一次增量恢复

DECLARE

outhandle varchar2(512);

outtag varchar2(30);

done boolean;

failover boolean;

devtype varchar2(256);

BEGIN

DBMS_OUTPUT.put_line('Entering Rollforward');

--Now the rolling forward

Devtype:=sys.dbms_backup_restore.deviceAllocate;

sys.dbms_backup_restore.applySetDatafile(check_logical=>FALSE,cleanup=>FALSE);

DBMS_OUTPUT.put_line('After applySetDataFile');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>05,toname=>'/data/test/test1_INDEX.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>06,toname=>'/data/test/test2_INDEX.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>07,toname=>'/data/test/test2_DATA.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>08,toname=>'/data/test/test1_DATA.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>09,toname=>'/data/test/test3_DATA.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'/data/test/test3_INDEX.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

--Restore set Piece

sys.dbms_backup_restore.restoreSetPiece(handle=>'/u01/rman/inc/first/testTBS1_0IV7GL23_1_1',tag=>null,fromdisk=>true,recid=>0,stamp=>0);

DBMS_OUTPUT.put_line('Done:RestoreSetPiece');

--Restore Backup Piece

sys.dbms_backup_restore.restoreBackupPiece(done=>done,params=>null,outhandle=>outhandle,outtag=>outtag,failover=>failover);

DBMS_OUTPUT.put_line('Done:RestoreBackupPiece');

sys.dbms_backup_restore.restoreCancel(TRUE);

sys.dbms_backup_restore.deviceDeallocate;

END;

/

第二次增量备份

rman target /

run{

backup incremental level=1 tablespace TEST_INDEX,TEST1_INDEX,TEST2_DATA,TEST3_DATA,TEST4_DATA,TEST3_INDEX

format 'I:\test-backup\test\testtbs1_%U'tag=testtbs1;

}

第二次增量恢复

DECLARE

outhandle varchar2(512);

outtag varchar2(30);

done boolean;

failover boolean;

devtype varchar2(256);

BEGIN

DBMS_OUTPUT.put_line('Entering Rollforward');

--Now the rolling forward

Devtype:=sys.dbms_backup_restore.deviceAllocate;

sys.dbms_backup_restore.applySetDatafile(check_logical=>FALSE,cleanup=>FALSE);

DBMS_OUTPUT.put_line('After applySetDataFile');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>05,toname=>'/data/test/test1_INDEX.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>06,toname=>'/data/test/test2_INDEX.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>07,toname=>'/data/test/test2_DATA.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>08,toname=>'/data/test/test1_DATA.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>09,toname=>'/data/test/test3_DATA.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'/data/test/test3_INDEX.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

--Restore set Piece

sys.dbms_backup_restore.restoreSetPiece(handle=>'/u01/rman/inc/first/testTBS1_0IV7GL23_1_1',tag=>null,fromdisk=>true,recid=>0,stamp=>0);

DBMS_OUTPUT.put_line('Done:RestoreSetPiece');

--Restore Backup Piece

sys.dbms_backup_restore.restoreBackupPiece(done=>done,params=>null,outhandle=>outhandle,outtag=>outtag,failover=>failover);

DBMS_OUTPUT.put_line('Done:RestoreBackupPiece');

sys.dbms_backup_restore.restoreCancel(TRUE);

sys.dbms_backup_restore.deviceDeallocate;

END;

/

进行数据库割接

把要迁移表空间设为只读状态

alter tablespace exessa_data readonly;

alter tablespace exessa_index readonly;

alter tablespace appquery_data readonly;

alter tablespace appquery_index readonly;

alter tablespace fullquery_data readonly;

alter tablespace fullquery_index readonly;

最后增量备份(注最后增量备份前必须把表空间置为只读，不然最后导入元数据会报错)

rman target /

run{

backup incremental level=1 tablespace test1_INDEX,test2_INDEX,test2_DATA,test1_DATA,test3_DATA,test3_INDEX

format 'I:\pglng-backup\rman\testtbs1_%U'tag=testtbs1;

}

最后增量恢复

DECLARE

outhandle varchar2(512);

outtag varchar2(30);

done boolean;

failover boolean;

devtype varchar2(256);

BEGIN

DBMS_OUTPUT.put_line('Entering Rollforward');

--Now the rolling forward

Devtype:=sys.dbms_backup_restore.deviceAllocate;

sys.dbms_backup_restore.applySetDatafile(check_logical=>FALSE,cleanup=>FALSE);

DBMS_OUTPUT.put_line('After applySetDataFile');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>05,toname=>'/data/test/test1_INDEX.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>06,toname=>'/data/test/test2_INDEX.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>07,toname=>'/data/test/test2_DATA.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>08,toname=>'/data/test/test1_DATA.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>09,toname=>'/data/test/test3_DATA.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

sys.dbms_backup_restore.applyDatafileTo(dfnumber=>10,toname=>'/data/test/test3_INDEX.DBF');

DBMS_OUTPUT.put_line('Done:applyDataFileTo');

--Restore set Piece

sys.dbms_backup_restore.restoreSetPiece(handle=>'/u01/rman/inc/first/testTBS1_0IV7GL23_1_1',tag=>null,fromdisk=>true,recid=>0,stamp=>0);

DBMS_OUTPUT.put_line('Done:RestoreSetPiece');

--Restore Backup Piece

sys.dbms_backup_restore.restoreBackupPiece(done=>done,params=>null,outhandle=>outhandle,outtag=>outtag,failover=>failover);

DBMS_OUTPUT.put_line('Done:RestoreBackupPiece');

sys.dbms_backup_restore.restoreCancel(TRUE);

sys.dbms_backup_restore.deviceDeallocate;

END;

/

在目标库创建相关用户

create user test1 identified by "a1p2p3" default tablespace system temporary tablespace temp;

create user test2 identified by "a1p2p3" default tablespace system temporary tablespace temp;

create user test3 identified by "a1p2p3" default tablespace system temporary tablespace temp;

create user test13 identified by "a1p2p3" default tablespace system temporary tablespace temp;

grant unlimited tablespace to test1;

grant dba to test1;

grant create session to test1;

grant unlimited tablespace to test13;

grant dba to test13;

grant create session to test13;

grant unlimited tablespace to test2;

grant dba to test2;

grant create session to test2;

grant unlimited tablespace to test3;

grant dba to test3;

grant create session to test3;

从源库导出元数据

exp "'sys/admin2295 as sysdba'" transport_tablespace=y tablespaces='test1_INDEX','test1_DATA','test2_DATA','test2_INDEX','test3_INDEX','test3_DATA' STATISTICS=none file=I:\dump\testtablespace.dmp

把元数据导入目标库

imp "'sys/admin2295 as sysdba'" transport_tablespace=y TABLESPACES='test1_INDEX','test1_DATA','test2_DATA','test2_INDEX','test3_INDEX','test3_DATA' FILE=/u01/exp/testtablespace.dmp log=/u01/exp/testtablespace.log datafiles='/data/test/test2_DATA.DBF','/data/test/test2_INDEX.DBF','/data/test/test3_INDEX.DBF','/data/test/test3_DATA.DBF','/data/test/test1_DATA.DBF','/data/test/test1_INDEX.DBF'

修改用户权限 (略)