oracle xtts資料庫遷移方法測試之一

煙花丶易冷發表於2017-06-16
測試結論
由於步驟過多,具體過程見下


測試思路




測試明細


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:~> 


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29618264/viewspace-2140855/,如需轉載,請註明出處,否則將追究法律責任。

相關文章