XTTS数据迁移 Windows(单机)--Linux(RAC)

已于 2022-10-17 16:33:22 修改
阅读量1.5k 收藏 1
点赞数
文章标签： windows linux oracle 数据库 运维
于 2022-02-08 11:07:01 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/loveLAxin/article/details/122819592
版权

1、确认迁移的对象

迁移用户：T1、T2、T3、TEST
迁移表空间：USERS、BIG1、T2、T3

2、判断表空间是否自包含

execute sys.dbms_tts.transport_set_check('USERS,BIG1,T2,T3',true);
col violations for a100
select * from sys.transport_set_violations;

3、开启块追踪

alter database enable block change tracking using file 'F:\xtts_copy\xtts.trc';

4、源端进行数据文件copy

rman target /
backup as copy datafile 9,10 format 'F:\xtts_copy\T2_%U.dbf';
backup as copy datafile 11,12 format 'F:\xtts_copy\T3_%U.dbf';
backup as copy datafile 13 format 'F:\xtts_copy\BIG1_%U.dbf';
backup as copy datafile 4 format 'F:\xtts_copy\USERS_%U.dbf';

5、目标端数据文件字节序转换

convert from platform 'Microsoft Windows x86 64-bit' datafile '/tmp/xtts/BIG1_DATA_D-CSDB_I-3295990825_TS-BIG1_FNO-13_07VVDIOL.DBF' format '+DATA/orcl/datafile/big01.dbf';
convert from platform 'Microsoft Windows x86 64-bit' datafile '/tmp/xtts/T2_DATA_D-CSDB_I-3295990825_TS-T2_FNO-10_04VVDIJJ.DBF' format '+DATA/orcl/datafile/t202.dbf';
convert from platform 'Microsoft Windows x86 64-bit' datafile '/tmp/xtts/T2_DATA_D-CSDB_I-3295990825_TS-T2_FNO-9_03VVDIJ4.DBF' format '+DATA/orcl/datafile/t201.dbf';
convert from platform 'Microsoft Windows x86 64-bit' datafile '/tmp/xtts/T3_DATA_D-CSDB_I-3295990825_TS-T3_FNO-11_05VVDILD.DBF' format '+DATA/orcl/datafile/t301.dbf';
convert from platform 'Microsoft Windows x86 64-bit' datafile '/tmp/xtts/T3_DATA_D-CSDB_I-3295990825_TS-T3_FNO-12_06VVDILS.DBF' format '+DATA/orcl/datafile/t302.dbf';
convert from platform 'Microsoft Windows x86 64-bit' datafile '/tmp/xtts/USERS_DATA_D-CSDB_I-3295990825_TS-USERS_FNO-4_08VVDITL.DBF' format '+DATA/orcl/datafile/users02.dbf';

6、查询SCN，做为增量备份的起点

alter system checkpoint;
select current_scn from v$database;
current_scn
-----------
1048072

7、进行增量备份

select current_scn from v$database;
current_scn
-----------
1048508

run{
set until scn=1048508;
backup incremental from scn 1048072 datafile 5,6,9,10,11,12,13,4 format 'F:\xtts_copy\xtts_incr1_%U.bak';
}

8、目标端对增量备份进行转换与应用

vi xtts_conv1.sql
  declare
  handle  varchar2(512);
  media  varchar2(80);
  comment  varchar2(80);
  concur boolean;
  recid number;
  stamp number;
  pltfrmfr number;
  devtype varchar2(512);
  begin
   begin
                 sys.dbms_backup_restore.restoreCancel(true);
                 devtype := sys.dbms_backup_restore.deviceAllocate;
                 sys.dbms_backup_restore.backupBackupPiece(bpname  => '/tmp/xtts/incr/XTTS_INCR1_09VVDPF9_1_1.BAK',
                     fname =>  '/tmp/xtts/incr/XTTS_INCR1_09VVDPF9_1_1.BAK_CONV',handle =>  handle,media=> media,
                    comment=>  comment, concur=> concur,recid=> recid,stamp => stamp, check_logical  => FALSE,copyno=> 1,
                    deffmt=> 0,  copy_recid=> 0,copy_stamp => 0,npieces=> 1,dest=> 0,pltfrmfr=> 13);
          end;
 end;
 /
 
start xtts_conv1.sql;

vi apply_incr1.sql
 set serveroutput on;
  declare
  outhandle   varchar2(512);
  outtag   varchar2(512);
  done boolean;
  failover  boolean;
  devtype   varchar2(512);
  begin
    begin
     dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 13,toname => '+DATA/orcl/datafile/big01.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 13');
	 sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR1_09VVDPF9_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
	  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;
    begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 4,toname => '+DATA/orcl/datafile/users02.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 4');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR1_09VVDPF9_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
	begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 9,toname => '+DATA/orcl/datafile/t201.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 9');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR1_09VVDPF9_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
	begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 10,toname => '+DATA/orcl/datafile/t202.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 10');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR1_09VVDPF9_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
	begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 11,toname => '+DATA/orcl/datafile/t301.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 11');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR1_09VVDPF9_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
	begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 12,toname => '+DATA/orcl/datafile/t302.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 12');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR1_09VVDPF9_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
  end;
  /

@apply_incr1.sql;

9、正式割接准备

--停止监听
lsnrctl stop

--清空回收站
purge dba_recyclebin;

--停止job
show parameter job_queue_processes;
NAME                        TYPE       VALUE
------------------------ ----------- ----------
job_queue_processes        integer     1000

alter system set job_queue_processes=0;
select local_tran_id,state from dba_2pc_pending;

--将表空间设为只读模式
SQL> alter tablespace users read only;

表空间已更改。

SQL> alter tablespace big1 read only;

表空间已更改。

SQL> alter tablespace t2 read only;

表空间已更改。

SQL> alter tablespace t3 read only;

表空间已更改。

10、表空间read only后，做最后一次增量备份

select current_scn from v$database;

run{
set until scn=1199060;
backup incremental from scn 1048508 datafile 5,6,9,10,11,12,13,4 format 'F:\xtts_copy\xtts_incr2_%U.bak';
}

---目标端转换、应用
declare
  handle  varchar2(512);
  media  varchar2(80);
  comment  varchar2(80);
  concur boolean;
  recid number;
  stamp number;
  pltfrmfr number;
  devtype varchar2(512);
  begin
   begin
                 sys.dbms_backup_restore.restoreCancel(true);
                 devtype := sys.dbms_backup_restore.deviceAllocate;
                 sys.dbms_backup_restore.backupBackupPiece(bpname  => '/tmp/xtts/incr/XTTS_INCR2_0LVVL4NF_1_1.BAK',
                     fname =>  '/tmp/xtts/incr/XTTS_INCR2_0LVVL4NF_1_1.BAK_CONV',handle =>  handle,media=> media,
                    comment=>  comment, concur=> concur,recid=> recid,stamp => stamp, check_logical  => FALSE,copyno=> 1,
                    deffmt=> 0,  copy_recid=> 0,copy_stamp => 0,npieces=> 1,dest=> 0,pltfrmfr=> 13);
          end;
 end;
 /

set serveroutput on;
  declare
  outhandle   varchar2(512);
  outtag   varchar2(512);
  done boolean;
  failover  boolean;
  devtype   varchar2(512);
  begin
    begin
     dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 13,toname => '+DATA/orcl/datafile/big01.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 13');
	 sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR2_0LVVL4NF_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
	  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;
    begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 4,toname => '+DATA/orcl/datafile/users02.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 4');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR2_0LVVL4NF_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
	begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 9,toname => '+DATA/orcl/datafile/t201.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 9');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR2_0LVVL4NF_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
	begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 10,toname => '+DATA/orcl/datafile/t202.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 10');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR2_0LVVL4NF_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
	begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 11,toname => '+DATA/orcl/datafile/t301.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 11');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR2_0LVVL4NF_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
	begin
	 dbms_output.put_line('begin apply incr1');
     devtype := sys.dbms_backup_restore.deviceAllocate;
     sys.dbms_backup_restore.applySetDatafile(check_logical => false, cleanup => false);
     sys.dbms_backup_restore.applyDatafileTo(
      dfnumber => 12,toname => '+DATA/orcl/datafile/t302.dbf',
      fuzziness_hint => 0,max_corrupt => 0, islevel0 => 0, recid =>0,stamp =>0);
     dbms_output.put_line('done: applyDatafileTo 12');
     sys.dbms_backup_restore.restoreSetPiece(
      handle=>'/tmp/xtts/incr/XTTS_INCR2_0LVVL4NF_1_1.BAK_CONV',
      tag=>null,fromdisk=>true,recid=>0,stamp=>0);
     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;
  end;
  /

begin apply incr1
done: applyDatafileTo 13
done: restoreBackupPiece
begin apply incr1
done: applyDatafileTo 4
done: restoreBackupPiece
begin apply incr1
done: applyDatafileTo 5
done: restoreBackupPiece
begin apply incr1
done: applyDatafileTo 6
done: restoreBackupPiece
begin apply incr1
done: applyDatafileTo 9
done: restoreBackupPiece
begin apply incr1
done: applyDatafileTo 10
done: restoreBackupPiece
begin apply incr1
done: applyDatafileTo 11
done: restoreBackupPiece
begin apply incr1
done: applyDatafileTo 12
done: restoreBackupPiece

PL/SQL procedure successfully completed.

11、源端导出表空间的元数据

--检查表空间是否自包含
exec dbms_tts.transport_set_check('USERS,BIG1,T2,T3',TRUE);
select * from sys.transport_set_violations;
未选定行

--开始导出
expdp sys/1 directory=dir dumpfile=xtts.dmp TRANSPORT_TABLESPACES=USERS,BIG1,T2,T3 transport_full_check=y exclude=STATISTICS logfile=xtts.log
Export: Release 11.2.0.1.0 - Production on 星期一 5月 24 13:06:36 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

UDE-01017: 操作产生了 ORACLE 错误 1017
ORA-01017: 用户名/口令无效; 登录被拒绝

用户名: / as sysdba

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=dir dumpfile=xtts.dmp TRANSPORT_TABLESPACES=USERS,BIG1,T2,T3 transport_full_check=y exclude=STATISTICS logfile=xtts.log
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYS.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
  F:\EXPDP\XTTS.DMP
******************************************************************************
可传输表空间 BIG1 所需的数据文件:
  F:\ORADATA\CSDB\BIG01.DBF
可传输表空间 T2 所需的数据文件:
  F:\ORADATA\CSDB\T201.DBF
  F:\ORADATA\CSDB\T202.DBF
可传输表空间 T3 所需的数据文件:
  F:\ORADATA\CSDB\T301.DBF
  F:\ORADATA\CSDB\T302.DBF
可传输表空间 USERS 所需的数据文件:
  F:\ORADATA\CSDB\USERS01.DBF
作业 "SYS"."SYS_EXPORT_TRANSPORTABLE_01" 已于 13:07:50 成功完成。

12、目标端导入表空间

--开始导入
impdp sys/1 directory=dir dumpfile=xtts.dmp transport_datafiles='+DATA/orcl/datafile/big01.dbf','+DATA/orcl/datafile/t202.dbf','+DATA/orcl/datafile/t201.dbf','+DATA/orcl/datafile/t301.dbf','+DATA/orcl/datafile/t302.dbf','+DATA/orcl/datafile/users02.dbf' logfile=xtts.log

--导入时，报错USERS表空间已经存在，需要删除后再次导入
Users表空间为默认永久表空间，删除时先修改默认永久表空间，然后再删除。
查看默认永久表空间：
SQL> set lines 200
SQL> col property_name for a30
SQL> col property_value for a30
SQL> col description for a40
SQL> select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                   PROPERTY_VALUE    DESCRIPTION
-----------------------------  ----------------  -------------------
DEFAULT_PERMANENT_TABLESPACE        USERS        Name of default permanent tablespace

--需创建新的表空间做中转表空间
Create tablespace mew datafile ‘+DATA/orcl/datafile/new.dbf’size 100M;
alter database default tablespace new;
SQL> select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE   DESCRIPTION
----------------------------- ---------------- ---------------------------
DEFAULT_PERMANENT_TABLESPACE        NEW         Name of default permanent tablespace

--进行删除
drop tablespace USERS including contents and datafiles;

--导入完成后，修改回USERS
alter database default tablespace users;
SQL> select * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE   DESCRIPTION
-----------------------------  --------------  ------------------------
DEFAULT_PERMANENT_TABLESPACE       USERS        Name of default permanent tablespace

--删除中转表空间
drop tablespace NEW including contents and datafiles;

--再次导入成功
impdp sys/1 directory=dir dumpfile=XTTS.DMP transport_datafiles='+DATA/orcl/datafile/big01.dbf','+DATA/orcl/datafile/t202.dbf','+DATA/orcl/datafile/t201.dbf','+DATA/orcl/datafile/t301.dbf','+DATA/orcl/datafile/t302.dbf','+DATA/orcl/datafile/users02.dbf' logfile=xtts.log

Import: Release 11.2.0.4.0 - Production on Mon May 24 13:52:16 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

UDI-01017: operation generated ORACLE error 1017
ORA-01017: invalid username/password; logon denied

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone version is 11 and target time zone version is 14.
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=dir dumpfile=XTTS.DMP transport_datafiles=+DATA/orcl/datafile/big01.dbf,+DATA/orcl/datafile/t202.dbf,+DATA/orcl/datafile/t201.dbf,+DATA/orcl/datafile/t301.dbf,+DATA/orcl/datafile/t302.dbf,+DATA/orcl/datafile/users02.dbf logfile=xtts.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
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 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Mon May 24 13:52:47 2021 elapsed 0 00:00:24

13、数据校验，收尾阶段

--按用户收集统计信息：
exec dbms_stats.gather_schema_stats(ownname => 'T1',degree => 3);
exec dbms_stats.gather_schema_stats(ownname => 'T2',degree => 3);
exec dbms_stats.gather_schema_stats(ownname => 'T3',degree => 3);
exec dbms_stats.gather_schema_stats(ownname => 'TEST',degree => 3);

--收集数据字典信息：
exec DBMS_STATS.GATHER_DICTIONARY_STATS(degree=>3);

--源端和目标端相关对象对比
select owner,count(object_name) from dba_objects where owner in('T1','T2',T3','TEST');

经对比源端与目标端的查询结果一致。

Floating warm sun
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Oracle 11.2.0.4到Oracle 19.3 PDB XTTS迁移方案测试.doc
05-17
Oracle 11.2.0.4到Oracle 19.3 PDB XTTS迁移方案测试.doc
XTTS-solaris-Linux 跨版本数据迁移
09-23
从solairs 11.2.0.4迁移到Linux 19.3.0 CDB下的PDB中，通过XTTS减少停机时间对数据进行迁移工作
linux下的Oracle迁移新人,记一次oracle 通过手动xtts的方法从WINDOW迁移到LINUX（一）...
weixin_35925298的博客
04-10 262
基本信息*IP操作系统数据库版本数据库信息源端10.10.41.85windowsoracle11.2.04test(这里用TEST代替真正的SID)目标端10.10.41.50centos7.6oracle11.2.04test(这里用TEST代替真正的SID)测试目的:检查确认TEST(这里TEST代替数据库名)从WINDOWS迁移到LINUX是否具有可行性。这里通过手动XTTS的方法进行迁移...
通过xtts迁移单实例文件系统表空间到RAC ASM存储表空间
congse3359的博客
01-18 500
xtts迁移文件系统表空间到文件系统表空间可参考,oracle小知识点14--xtts传输表空间 http://blog.itpub.net/28539951/viewspace-1978401/ 测试: os:...
使用XTTS增量进行HP Unix到Soalris Sparc的数据库迁移
Roger's oracle blog
05-07 1482
自从2015年初进行了xtts增量的U2L迁移测试之后，国内很多人都开始利用这种方案进行数据库跨平台迁移了，基本上都是利用Oracle 封装的perl脚本。
windows下的oracle迁移到linux上
DreamDB的专栏
09-24 6288
<br /><br />最近学习了一下oracle数据库启动原理，于是，就把在Windows创建起来做测试的数据库。<br /> <br />　　移植到linux下使用,前几天把Linux移植到Windows成功，但Windows移植到Linux碰到问题会多，<br /> <br />　　Windows下不区分大小写，但在Linux是区分的，这点务必请大家注意，下面让我们一起去这过程吧!<br /> <br />　　还是和上面讲的一样，我直接通过文件复制，把原来在Windows下使用的数据库移植到linux
Oracle+10g-11g+XTTS迁移方案.pdf
09-28
Oracle 10g 到 11g 的迁移是一个复杂的过程，涉及到多个步骤和技术，其中XTTS（Extreme Transaction Transport Services）是一种高级的数据迁移工具。XTTS是Oracle提供的一种用于在线数据迁移的技术，它允许在不中断...
实战xtts迁移12C到19c
最新发布
07-26
XTTS迁移 Oracle 数据库从 12C 到 19C XTTS（Cross Platform Transportable Tablespaces）是一种 Oracle 数据库迁移方法，它可以将数据库从一个平台迁移到另一个平台，包括跨操作系统、架构和版本的迁移。XTTS 的...
xtts-aix-linux测试20200128.pdf
01-28
利用xtts跨平台增量传输表空间技术，轻松快速将aix环境下的oracle11g在1小时内迁移到linux环境下。 本次采用手动xtts的方式，该方法适用任意平台之间，源端>=oracle10.2.0.3，目标端>=11.2.0.4,包括windows<->linux...
oracle12c xtts迁移,记录一次XTTS迁移碰到的问题
weixin_27027459的博客
04-08 497
最近在做一次迁移，原库 AIX-11203RAC ，目标端linux 11204 RAC，数据量3t左右，期间也是出现了点问题，现在记录一下，以供大家参考：1 在打开BCT卡住alter database enable block change tracking using file '/home/oracle/trace.log';再打开快跟踪的时候，一直hang在那边，几分钟后对业务造成了影响...
oracle中aix至Linux导出,oracle xtts迁移 AIX to Linux
weixin_42392425的博客
04-03 466
以下是XTTS迁移的全过程，需要根据实际情况更改，主要包括以下几个流程：检查数据库用户自包含准备xtts前期环境进行全量备份并传输备份集到目标端目标端进行全量恢复源端目标端多次增量恢复表空间readonly进行最后一次增量元数据导入导出检查对象源端目标端Oracle版本10.2.0.311.2.0.4操作系统AIX 5.3RHEL 6.7是否RAC否是数据库名称oraoldoranew本次只迁移T...
【迁移】使用XTTS-V3（DBMS_FILE_TRANSFER）数据迁移
数据库学习笔记
05-01 1067
生产上遇到一个迁移场景，大概1T数据量左右，由于没用XTTS做过迁移，所以准备尝试一下，本次迁移采用XTTS(增强传输表空间) V3版本的DBMS_FILE_TRANSFER方式。 一、源库及目标库情况 从目标库及源库情况来看，选用XTTS并不是个好选择。XTTS主要还是用在跨平台、跨版本且数据量大的场景下，当前的场景：服务器间网络传输速率高用DataPump的netlink方式最简单，用DG停机时间可以做到最短。 源库 目标库 IP地址 10.0.11.XXX 10.0.14.XXX
XTTS测试：aix->linux
sqlora的专栏
01-27 645
基本信息 主机 IP 操作系统 数据库版本 数据库信息 备份目录 源端 10.1.1.61 aix6.1 11.2.0.4 数据库名erp 用户名:test ...
续：跨平台版本迁移之 XTTS 方案操作指南
weixin_34095889的博客
04-23 173
XTTS 迁移后检查 7.1 更改用户默认表空间 更改用户默认表空间，将用户默认表空间设置与源数据库保持一致： @default_tablespace.sql 源端执行： spool default_tablespace.sql select 'alter user '||username||' default tablespace '||def...
手动XTTS跨平台跨版本增量传输表空间
sqlora的专栏
01-16 693
基本信息 主机 IP 操作系统 数据库版本 数据库信息 备份目录 源端 192.168.52.145 windows2008 10.2.0.5 数据库名erp ...
XTTS+Rman迁移
Du.的博客
04-06 415
Phase 1 - Initial Setup phase 1. Install the desired oracle database software (oracle version:11.2.0.4 and later) on the destination system 2. From the source database, check its characterset, sga, processes, hidden parameters .etc for destination instan
数据库迁移windows->linux ORACLE 10G
weixin_30711917的博客
09-09 110
1、首先从windows中将数据库以dmp文件的形式倒出来。 2、以书写参数文件的形式将倒出的数据库导入linux下的oracle 10g。 3、注意字符集否则导入数据库之后会发现数据库中的中文都是乱码。 4、过程如下： 1）确认字符集 到处数据库使用字符集内容：NLS_CHARACTERSET：ZHS16GBK 　　　　　　　　　　　　　NLS_NCHAR_CHARACTERSET：...
Oracle 使用xtts升级11g-to-19c
sinat_36757755的博客
07-25 3806
本文主要描述使用xtts升级Oracle11g 到19c的操作步骤，基于dbms_file_transfer全备+rman增量备份的xtts数据迁移，通常用于短时间(
xtts 迁移的时候 ORA-12545: Connect failed because target host or object does not exist
05-22
ORA-12545 错误通常是由于网络问题导致的。这个错误可能有多种原因，比如： 1. 目标主机的 IP 地址不正确或者主机名无法解析。 2. 目标主机上的 Oracle 服务未启动或者未监听到指定的端口。 3. 防火墙或者网络设备...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值