oracle xtts数据库迁移方法测试之一

测试结论
由于步骤过多,具体过程见下

测试思路
oracle xtts数据库迁移方法测试之一

测试明细

1,源与目标数据库的概况

编号      数据库类型      数据库名称   数据库版本    数据库IP地址     操作系统平台
1         oracle单实例    mygirl     11.2.0.4      10.0.0.5        redhat 6.5

2         oracle单实例    esbdb      11.2.0.4      10.0.0.39       suse 11

2,创建源端数据库的测试表空间及测试用户并创建测试表和插入数据 
SQL> set linesize 300
SQL> col name for a50
SQL> select file#,name from v$datafile
  2  ;

     FILE# NAME
———- ————————————————–
         1 /oracle/mygirl/system01.dbf
         2 /oracle/mygirl/sysaux01.dbf
         3 /oracle/mygirl/undotbs01.dbf
         4 /oracle/mygirl/users01.dbf
         5 /oracle/admin/mygirl/dpdump/tbs_father01.dbf
         6 /oracle/admin/mygirl/dpdump/tbs_mother01.dbf
         7 /oracle/admin/mygirl/dpdump/tbs_sun01.dbf
         8 /oracle/mygirl/tbs_zxy01.dbf
         9 /oracle/mygirl/tbs_obj01.dbf
        10 /oracle/mygirl/tbs_wife01.dbf

10 rows selected.

SQL> create tablespace tbs_dead datafile ‘/oracle/mygirl/tbs_dead01.dbf’ size 10m;

Tablespace created.

SQL> create user user_dead identified by system account unlock default tablespace tbs_dead;

User created.

SQL> grant resource,connect to user_dead;

Grant succeeded.

SQL> conn user_dead/system
Connected.

SQL> create table t_test(a int);

Table created.

SQL> insert into t_test values(1);

1 row created.

SQL> commit;

Commit complete.

3,在源端数据库对data file 11进行data file copy
SQL> conn /as sysdba
Connected.
SQL> select file#,name from v$datafile where file#=11;

     FILE# NAME
———- ————————————————–
        11 /oracle/mygirl/tbs_dead01.dbf

[oracle@mygirl ~]$  rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Thu Jun 15 19:21:58 2017

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

connected to target database: MYGIRL (DBID=2527996082)

RMAN> backup as copy datafile 11 tag ‘datafile 11 copy note’ format ‘/home/oracle/tbs_dead01.copy';  

Starting backup at 15-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/oracle/mygirl/tbs_dead01.dbf
output file name=/home/oracle/tbs_dead01.copy tag=DATAFILE 11 COPY NOTE RECID=16 STAMP=946754806
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 15-JUN-17

4,传输源端数据库测试表空间DATA FILE COPY到目标端数据库对应目录
[oracle@mygirl ~]$ scp /home/oracle/tbs_dead01.copy oracle@10.0.0.39:/home/oracle
Password: 
tbs_dead01.copy                                                                                                                                   100%   10MB  10.0MB/s   00:01    
[oracle@mygirl ~]$ 

5,在目标端数据库对自源端数据库传输过来的测试表空间DATA FILE COPY进行字节序转换
—源端
SQL> set linesize 300
SQL> r
  1* select platform_id,platform_name,endian_format 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 x86 64-bit                                                                                      Little
         16 Apple Mac OS                                                                                          Big
         12 Microsoft Windows x86 64-bit                                                                          Little
         17 Solaris Operating System (x86)                                                                        Little
         18 IBM Power Based Linux                                                                                 Big
         19 HP IA Open VMS                                                                                        Little
         20 Solaris Operating System (x86-64)                                                                     Little
         21 Apple Mac OS (x86-64)                                                                                 Little

20 rows selected.

—目标端

oracle@suse11:~> pwd
/home/oracle
oracle@suse11:~> ll tbs_dead01.copy
-rw-r—– 1 oracle oinstall 10493952 2017-06-15 19:27 tbs_dead01.copy
oracle@suse11:~> rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Thu Jun 15 19:29:46 2017

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

connected to target database: ESBDB (DBID=3277468929)

RMAN> convert datafile ‘/home/oracle/tbs_dead01.copy’  format ‘/home/oracle/tbs_dead01.dbf’  from platform ‘Linux x86 64-bit';

Starting conversion at target at 15-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/tbs_dead01.copy
converted datafile=/home/oracle/tbs_dead01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 15-JUN-17

RMAN> 

6,在源端数据库查询测试表空间data file copy的SCN
SQL> conn /as sysdba
Connected.
SQL> set linesize 300
SQL> col name for a50
SQL> select recid,name,file#,incremental_level,checkpoint_time,checkpoint_change# from v$datafile_copy where file#=11;

     RECID NAME                                                    FILE# INCREMENTAL_LEVEL CHECKPOIN CHECKPOINT_CHANGE#
———- ————————————————– ———- —————– ——— ——————
        16 /home/oracle/tbs_dead01.copy                               11                   15-JUN-17            6065041

7,在源端数据库对测试表空间进行数据库事务变化
SQL> conn user_dead/system
Connected.
SQL> insert into t_test values(2);

1 row created.

SQL> commit;

Commit complete.

8,在源端数据库基于 上述的DATA FILE COPY的SCN进行增量RMAN备份

RMAN> backup incremental from scn 6065041 datafile 11 format ‘/home/oracle/incr_datafile11_first_bak_6065041_%u_%d.bak’ tag=’datafile11_first_incr_bak';

Starting backup at 15-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/oracle/mygirl/tbs_dead01.dbf
channel ORA_DISK_1: starting piece 1 at 15-JUN-17
channel ORA_DISK_1: finished piece 1 at 15-JUN-17
piece handle=/home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak tag=DATAFILE11_FIRST_INCR_BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 15-JUN-17
channel ORA_DISK_1: finished piece 1 at 15-JUN-17
piece handle=/home/oracle/incr_datafile11_first_bak_6065041_21s6slnj_MYGIRL.bak tag=DATAFILE11_FIRST_INCR_BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-JUN-17

9,在源端数据库获取最新的SCN用于下次增量RMAN备份的基础
SQL> select file#,checkpoint_change# from v$datafile where file#=11;

     FILE# CHECKPOINT_CHANGE#
———- ——————
        11            6065307

10,传输源端上述的增量RMAN备份到目标端数据库对应目录
[oracle@mygirl ~]$ scp /home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak oracle@10.0.0.39:/home/oracle
Password: 
incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak                                                                                             100%   48KB  48.0KB/s   00:00    
[oracle@mygirl ~]$ 

11,在目标端数据库对自源端数据库传输过来的增量RMAN备份集进行字节序转换

oracle@suse11:~> ls -l /home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak 
-rw-r—– 1 oracle oinstall 49152 2017-06-15 21:52 /home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak
oracle@suse11:~> 

oracle@suse11:~> sqlplus ‘/as sysdba’

DECLARE
  handle    varchar2(512);
  comment   varchar2(80);
  media     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 => ‘/home/oracle/incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak’,fname => ‘/home/oracle/first_incr_bak.bak’,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;
/

PL/SQL procedure successfully completed.

oracle@suse11:~> pwd
/home/oracle
oracle@suse11:~> ll first_incr_bak.bak 
-rw-r—– 1 oracle oinstall 49152 2017-06-15 21:56 first_incr_bak.bak

12,在目标端数据库对上述的DATA FILE COPY以及初次的增量RMAN备份集进行前滚应用

set serveroutput on;
   DECLARE
   outhandle varchar2(512) ;
   outtag varchar2(30) ;
   done boolean ;
   failover boolean ;
   devtype VARCHAR2(512);
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 => 11,
     toname => ‘/home/oracle/tbs_dead01.dbf’,
     fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,
     recid => 0, stamp => 0);

  DBMS_OUTPUT.put_line(‘Done: applyDataFileTo’);

  DBMS_OUTPUT.put_line(‘Done: applyDataFileTo’);

  — Restore Set Piece
  sys.dbms_backup_restore.restoreSetPiece(
    handle => ‘/home/oracle/first_incr_bak.bak’,
    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;

/

Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.

13,继续在源端数据库对测试表空间进行数据库事务变化
[oracle@mygirl ~]$ sqlplus user_dead/system

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:00:39 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into t_test values(3);

1 row created.

SQL> commit;

Commit complete.

14,在源端数据库配置测试表空间为READ ONLY 
(注:现在就是真正开始停机的时间)
SQL> conn /as sysdba
Connected.
SQL> alter tablespace tbs_dead read only;

Tablespace altered.

15,在源端数据库基于上述的SCN进行增量RMAN备份
[oracle@mygirl ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Thu Jun 15 22:02:44 2017

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

connected to target database: MYGIRL (DBID=2527996082)

RMAN> backup incremental from scn 6065307 datafile 11 format ‘/home/oracle/incr_datafile11_end_bak_6065041_%u_%d.bak’ tag=’datafile11_end_incr_bak';

Starting backup at 15-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/oracle/mygirl/tbs_dead01.dbf
channel ORA_DISK_1: starting piece 1 at 15-JUN-17
channel ORA_DISK_1: finished piece 1 at 15-JUN-17
piece handle=/home/oracle/incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak tag=DATAFILE11_END_INCR_BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 15-JUN-17
channel ORA_DISK_1: finished piece 1 at 15-JUN-17
piece handle=/home/oracle/incr_datafile11_end_bak_6065041_23s6suc8_MYGIRL.bak tag=DATAFILE11_END_INCR_BAK comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-JUN-17

16,传输源端上述的RMAN备份到目标端数据库对应目录
[oracle@mygirl ~]$ scp /home/oracle/incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak  oracle@10.0.0.39:/home/oracle
Password: 
incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak                                                                                               100%   48KB  48.0KB/s   00:00    
[oracle@mygirl ~]$ 

17,在目标端数据库对自源端数据库传输过来的RMAN增量备份集进行字节序转换
DECLARE
  handle    varchar2(512);
  comment   varchar2(80);
  media     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 => ‘/home/oracle/incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak’,fname => ‘/home/oracle/end_incr_bak.bak’,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;
/

PL/SQL procedure successfully completed.

18,在目标端数据库对上述的增量RMAN备份进行前滚应用
set serveroutput on;
   DECLARE
   outhandle varchar2(512) ;
   outtag varchar2(30) ;
   done boolean ;
   failover boolean ;
   devtype VARCHAR2(512);
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 => 11,
     toname => ‘/home/oracle/tbs_dead01.dbf’,
     fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,
     recid => 0, stamp => 0);

  DBMS_OUTPUT.put_line(‘Done: applyDataFileTo’);

  DBMS_OUTPUT.put_line(‘Done: applyDataFileTo’);

  — Restore Set Piece
  sys.dbms_backup_restore.restoreSetPiece(
    handle => ‘/home/oracle/end_incr_bak.bak’,
    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;

Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece

PL/SQL procedure successfully completed.

19,在源端数据库导出测试表空间的元数据
[oracle@mygirl ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:14:09 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col directory_name for a50 
SQL> col directory_path for a50
SQL> set linesize 300
SQL> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME                                     DIRECTORY_PATH
————————————————– ————————————————–
XMLDIR                                             /oracle/product/11.2.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR                              /oracle/product/11.2.0/db_1/ccr/hosts/mygirl/state
DATA_PUMP_DIR                                      /oracle/admin/mygirl/dpdump/
ORACLE_OCM_CONFIG_DIR2                             /oracle/product/11.2.0/db_1/ccr/state

[oracle@mygirl ~]$ expdp /’sys/system as sysdba/’  dumpfile=expdp_tbs_dead.dmp directory=data_pump_dir transport_tablespaces=tbs_dead logfile=expdp_tbs_dead.log 

Export: Release 11.2.0.4.0 – Production on Thu Jun 15 22:16:21 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″:  “sys/******** AS SYSDBA” dumpfile=expdp_tbs_dead.dmp directory=data_pump_dir transport_tablespaces=tbs_dead logfile=expdp_tbs_dead.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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:
  /oracle/admin/mygirl/dpdump/expdp_tbs_dead.dmp
******************************************************************************
Datafiles required for transportable tablespace TBS_DEAD:
  /oracle/mygirl/tbs_dead01.dbf
Job “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at Thu Jun 15 22:17:06 2017 elapsed 0 00:00:44

[oracle@mygirl ~]$ 

20,传输源端导出的测试表空间元数据到目标端数据库对应目录

—目标端
oracle@suse11:~> sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:18:25 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col directory_name for a50
SQL> col directory_path for a50
SQL> set linesize 300
SQL> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME                                     DIRECTORY_PATH
————————————————– ————————————————–
XMLDIR                                             /oracle/product/11.2.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR                              /oracle/product/11.2.0/db_1/ccr/hosts/suse11/state
DATA_PUMP_DIR                                      /oracle/admin/esbdb/dpdump/
ORACLE_OCM_CONFIG_DIR2                             /oracle/product/11.2.0/db_1/ccr/state

—源端
[oracle@mygirl ~]$ scp /oracle/admin/mygirl/dpdump/expdp_tbs_dead.dmp oracle@10.0.0.39:/oracle/admin/esbdb/dpdump
Password: 
expdp_tbs_dead.dmp                                                                                                                                100%   88KB  88.0KB/s   00:00    
[oracle@mygirl ~]$ 

21,在目标端数据库基于源端测试用户及角色构建测试用户
oracle@suse11:~> sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:20:54 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user user_dead identified by system account unlock;

User created.

SQL> grant resource,connect to user_dead;

Grant succeeded.

22,在目标端数据库导入测试表空间

oracle@suse11:~> impdp /’/as sysdba/’ directory=DATA_PUMP_DIR  transport_datafiles=’/home/oracle/tbs_dead01.dbf’ dumpfile=expdp_tbs_dead.dmp logfile=impdp_tbs_dead.log

Import: Release 11.2.0.4.0 – Production on Thu Jun 15 22:23:28 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_TRANSPORTABLE_01″:  “/******** AS SYSDBA” directory=DATA_PUMP_DIR transport_datafiles=/home/oracle/tbs_dead01.dbf dumpfile=expdp_tbs_dead.dmp logfile=impdp_tbs_dead.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job “SYS”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at Thu Jun 15 22:23:39 2017 elapsed 0 00:00:07

oracle@suse11:~> 

23,在目标端数据库验证迁移表空间是否导入成功
oracle@suse11:~> sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:24:07 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$tablespace;

NAME
——————————
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TBS_DEAD

6 rows selected.

SQL> col name for a50

SQL> r
  1* select file#,name from v$datafile

     FILE# NAME
———- ————————————————–
         1 /oracle/esbdb/system01.dbf
         2 /oracle/esbdb/sysaux01.dbf
         3 /oracle/esbdb/undotbs01.dbf
         4 /oracle/esbdb/users01.dbf
         5 /home/oracle/tbs_dead01.dbf

24,在目标端数据库变更迁移表空间为read write以及变更测试用户的默认表空间为迁移表空间
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
—————————— ———
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TBS_DEAD                       READ ONLY

6 rows selected.

SQL> alter tablespace tbs_dead  read write;

Tablespace altered.

SQL> select username,default_tablespace from dba_users where username=’USER_DEAD';

USERNAME                       DEFAULT_TABLESPACE
—————————— ——————————
USER_DEAD                      USERS

SQL> alter user user_dead default tablespace tbs_dead;

User altered.

25,在目标端数据库验证迁移表空间的数据一致性
SQL> conn user_dead/system
Connected.
SQL> select *  from t_test;

         A
———-
         2
         1
         3

26,在源端和目标端导出导入其它的数据库特殊对象类型比如:trigger,sequence
请参考旧文如下:

20170609星期五之如何基于oracle 11.2.0.4数据库版本在数据库间迁移非表特殊对象类比如序列及存储过程测试明细

27,在目标端数据库启动数据库监听器
oracle@suse11:~> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 15-JUN-2017 22:31:33

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused
oracle@suse11:~> lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 15-JUN-2017 22:31:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Starting /oracle/product/11.2.0/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 11.2.0.4.0 – Production
Log messages written to /oracle/diag/tnslsnr/suse11/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse11)(PORT=1521)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 – Production
Start Date                15-JUN-2017 22:31:35
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /oracle/diag/tnslsnr/suse11/listener/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse11)(PORT=1521)))
The listener supports no services
The command completed successfully
oracle@suse11:~> sqlplus ‘/as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 15 22:31:43 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system register;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@suse11:~> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 – Production on 15-JUN-2017 22:31:50

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 – Production
Start Date                15-JUN-2017 22:31:35
Uptime                    0 days 0 hr. 0 min. 14 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /oracle/diag/tnslsnr/suse11/listener/alert/log.xml
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse11)(PORT=1521)))
Services Summary…
Service “esbdb” has 1 instance(s).
  Instance “esbdb”, status READY, has 1 handler(s) for this service…
Service “esbdbXDB” has 1 instance(s).
  Instance “esbdb”, status READY, has 1 handler(s) for this service…
The command completed successfully
oracle@suse11:~> 

28,通知应用厂商连接数据库进行业务验证

29,确认业务运行正常后,清除目标端XTTS相关的临时文件
oracle@suse11:~> pwd
/home/oracle
oracle@suse11:~> ll
total 20772
drwxr-xr-x 3 oracle oinstall     4096 2015-12-28 19:02 11204software
drwxr-xr-x 2 oracle oinstall     4096 2017-06-08 19:40 after_diff_dir
drwxr-xr-x 2 oracle oinstall     4096 2017-06-07 18:33 db_full_bak
drwxr-xr-x 2 oracle oinstall     4096 2017-05-29 14:06 Desktop
drwxr-xr-x 2 oracle oinstall     4096 2017-06-09 00:51 dest_convert_dir
drwx—— 2 oracle oinstall     4096 2017-05-29 14:07 Documents
-rw-r—– 1 oracle oinstall    49152 2017-06-15 22:05 end_incr_bak.bak
drwxr-xr-x 2 oracle oinstall     4096 2017-06-08 19:47 ending_dir
drwxr-xr-x 2 oracle oinstall     4096 2017-06-08 19:34 every_dir
-rw-r—– 1 oracle oinstall    49152 2017-06-15 21:56 first_incr_bak.bak
drwxr-xr-x 2 oracle oinstall     4096 2017-06-12 02:04 from_incr_dir
drwxr-xr-x 2 oracle oinstall     4096 2017-06-11 22:30 from_source_datafilecopy_dir
-rw-r—– 1 oracle oinstall    49152 2017-06-15 22:04 incr_datafile11_end_bak_6065041_22s6suc7_MYGIRL.bak
-rw-r—– 1 oracle oinstall    49152 2017-06-15 21:52 incr_datafile11_first_bak_6065041_20s6slni_MYGIRL.bak
-rw-r—– 1 oracle oinstall 10493952 2017-06-15 19:27 tbs_dead01.copy
-rw-r—– 1 oracle oinstall 10493952 2017-06-15 22:27 tbs_dead01.dbf
drwxr-xr-x 2 oracle oinstall     4096 2017-06-09 00:03 temp_dir
-rw-r–r– 1 oracle oinstall      687 2017-06-07 18:42 use_des.ora
drwxr-xr-x 2 oracle oinstall     4096 2017-06-09 00:27 xtts_convert_dir
oracle@suse11:~> rm -Rf first_incr_bak.bak end_incr_bak.bak  incr_datafile11_* tbs_dead01.copy 
oracle@suse11:~> 

PS:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处:Harries Blog™ » oracle xtts数据库迁移方法测试之一

赞 (0)

分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址