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:~>
由於步驟過多，具體過程見下
測試思路
測試明細
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/9240380/viewspace-2140828/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料庫遷移之一：RMANOracle資料庫
- oracle xtts遷移 AIX to LinuxOracleTTSAILinux
- 【XTTS】Oracle XTTS V4--Oracle11.2.0.4+ 遷移升級TTSOracle
- ORACLE資料庫遷移Oracle資料庫
- 基於redhat 6.5 oracle 11.2.0.4測試tts傳輸表空間之資料庫遷移之一RedhatOracleTTS資料庫
- 資料庫-oracle-資料庫遷移資料庫Oracle
- solaris10_oracle10g_asm_non_asm遷移資料庫測試OracleASM資料庫
- SQL 遷移資料庫至ORACLE簡易方法SQL資料庫Oracle
- Oracle資料庫資料遷移流程Oracle資料庫
- xtts遷移實踐TTS
- Oracle rman duplicate遷移測試Oracle
- oracle 遷移資料庫到asmOracle資料庫ASM
- Oracle資料庫升級或資料遷移的方法探討Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（下）Oracle資料庫
- 無外網Oracle資料庫遷移Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案(上)Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（中）Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（下）Oracle資料庫
- 【DATAPUMP】使用DataPump遷移Oracle資料庫Oracle資料庫
- Oracle資料庫遷移方案比較Oracle資料庫
- 超大資料量遷移方案，XTTS vs OGG大資料TTS
- 【遷移】使用rman遷移資料庫資料庫
- 資料庫遷移資料庫
- Oracle資料庫——xTTS技術的使用Oracle資料庫TTS
- ORACLE 資料遷移Oracle
- 檢測資料庫遷移準確性資料庫
- Core Data資料遷移及單元測試
- 1.1資料庫物件結構遷移方法資料庫物件
- 使用RMAN進行Oracle資料庫遷移Oracle資料庫
- ORACLE資料庫切換和遷移方案Oracle資料庫
- Oracle遷移資料庫過程記錄Oracle資料庫
- 跨平臺遷移oracle資料庫指南Oracle資料庫
- 最少停機時間遷移資料庫: XTTS + Cross Platform Incremental Backup(增量備份) - 1資料庫TTSROSPlatformREM
- 最少停機時間遷移資料庫: XTTS + Cross Platform Incremental Backup(增量備份) - 2資料庫TTSROSPlatformREM
- 【XTTS】使用XTTS傳輸表空間將Oracle11.2.0.4資料遷移至Oracle19CTTSOracle
- 用最簡單的方法複製或遷移Oracle資料庫Oracle資料庫
- Mysql資料遷移方法MySql