xtts遷移實踐
XTTS遷移環境調研
--源庫、目標庫硬體及軟體清理
源庫： 192.168.56.20 hostname:slient rdbms:11.2.0.4 os:rhel 6.7
目標庫: 192.168.56.12 hostname:wang rdbms:11.2.0.4 os:rhel 7.2
--源庫建立需要遷移的使用者、表空間等
SQL> create tablespace xtts datafile '/u01/app/oracle/oradata/test/xtts.dbf' size 1g autoextend off;
Tablespace created.
SQL> create user test identified by test default tablespace xtts temporary tablespace temp02 quota unlimited on xtts profile default account unlock;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> conn test/test;
Connected.
--建立測試表test_xtts
create table test_xtts(id number(10),create_date date,name varchar2(100),up_date date);
--使用批次繫結技術插入400萬資料初始測試資料
declare
type t_mid is table of test_xtts%rowtype index by binary_integer;
l_tab_mid t_mid;
begin
for i in 1 .. 4000000 loop
l_tab_mid(i).id := i;
l_tab_mid(i).create_date := sysdate;
l_tab_mid(i).name := lpad('a', 100, 'a');
l_tab_mid(i).up_date := sysdate;
end loop;
forall i in 1 .. l_tab_mid.count
insert into test_xtts values l_tab_mid (i);
commit;
end;
/
--給表test_xtts增加主鍵約束及建索引
alter table test_xtts add (constraint test_xtts_pk primary key (id));
create index idx01_test_xtts on test_xtts(create_date);
--收集統計資訊
exec dbms_stats.gather_table_stats(ownname => 'TEST',TABNAME => 'TEST_XTTS' ,cascade => true,estimate_percent => 100);
--查詢驗證
SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------- --------------- ---------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP02 Name of default temporary tablespace
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TEST';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
TEST XTTS TEMP02
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,NUM_ROWS,BLOCKS from dba_tables where table_name='TEST_XTTS';
OWNER TABLE_NAME TABLESPACE_NAME STATUS NUM_ROWS BLOCKS
--------------- --------------- --------------- -------- ---------- ----------
TEST TEST_XTTS XTTS VALID 4000000 71297
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='TEST_XTTS';
OWNER SEGMENT_NAME TABLESPACE_NAME SIZE_M
--------------- --------------- --------------- ----------
TEST TEST_XTTS XTTS 560
一、前期準備，先決條件檢查
1.1. 檢查資料庫時區，保持兩邊一致
SQL> select dbtimezone from dual;
DBTIME
------
+08:00
一致。
1.2. 檢查資料庫字符集，保持兩邊一致
SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET%';
PARAMETER VALUE
------------------------- -----------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
一致。
1.3. 檢查目標端補丁情況
目標端psu無特殊需求，可以隨情況安裝最新即可，測試環境省略。
如果準備階段使用dbms_file_transfer方法，目標端建議安裝的補丁如下：
Patch 19023822，修復目標端使用dbms_file_transfer.get_file包獲取源端資料檔案出現ORA-03106的情況。
Patch 22171097: MERGE REQUEST ON TOP OF DATABASE PSU 11.2.0.4.6 FOR BUGS 17534365 19023822
如果準備階段使用rman方法，目標端沒有小補丁安裝需求
1.4. 檢查元件安裝情況，對於不一樣的元件，需要有所標記
--源庫
SQL> Select comp_name from dba_registry;
COMP_NAME
-------------------------------------------------------
Oracle Enterprise Manager
Spatial
Oracle interMedia
OLAP Catalog
Oracle XML Database
Oracle Text
Oracle Expression Filter
Oracle Rule Manager
Oracle Workspace Manager
Oracle Data Mining
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
OLAP Analytic Workspace
Oracle OLAP API
17 rows selected.
--目標庫
SQL> Select comp_name from dba_registry;
COMP_NAME
-------------------------------------------------------
OWB
Oracle Application Express
Oracle Enterprise Manager
OLAP Catalog
Spatial
Oracle Multimedia
Oracle XML Database
Oracle Text
Oracle Expression Filter
Oracle Rules Manager
Oracle Workspace Manager
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle XDK
Oracle Database Java Packages
OLAP Analytic Workspace
Oracle OLAP API
18 rows selected.
備註：元件不同，可能導致源端的部分物件無法匯入到目標端
1.5. 檢查是否使用了key compression的索引組織表
--檢查是否使用索引壓縮
Select index_name,table_name from dba_indexes where compression='ENABLED' and owner='TEST';
--檢查是否有索引組織表
Select owner,table_name from dba_tables where iot_type is not null and owner='TEST';
如果存在，目標端需要安裝patch 14835322，否則索引組織表的物件無法匯入到目標端
目前該補丁只在11.2.0.3版本下提供，針對其他版本，如果iot表或者鍵值壓縮索引無法匯入時，建議在後設資料匯入完成後，將該類物件傳輸過去。
1.6. 檢查源端是否有cluster 物件
select OWNER,CLUSTER_NAME,TABLESPACE_NAME from dba_clusters where owner='TEST';
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where owner='TEST' and object_type='CLUSTER';
=======================================================================
If the source contains cluster objects, then run "analyze cluster &cluster_name validate structure cascade" after XTTS has been completed in the target database and if it reports an ORA-1499 open the trace file and review if it has entries like:
kdcchk: index points to block 0x01c034f2 slot 0x1 chain length is 256
kdcchk: chain count wrong 0x01c034f2.1 chain is 1 index says 256
last entry 0x01c034f2.1 blockcount = 1
kdavls: kdcchk returns 3 when checking cluster dba 0x01c034a1 objn 90376
Then to repair this inconsistency either:
1. rebuild the cluster index.
or
2. Install fix bug 17866999 and run dbms_repair.repair_cluster_index_keycount
If after repairing the inconsistency the "analyze cluster &cluster_name validate structure cascade" still reports issues then recreate the affected cluster which involves recreating its tables.
=========================================================================
1.7. 檢查RMAN DEVICE TYPE DISK 是否配置 COMPRESSED
ORA-19837: invalid blocksize 0 in backup piece header
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 2338
ORA-06512: at line 40
配置壓縮會導致以上報錯，透過rman > show all; 檢查
1.8. SYS、SYSTEM使用者是否有物件存在於業務表空間
如資料庫使用者SYS、SYSTEM在業務表空間上建立有物件，則這些物件不能透過XTTS遷移，需要在目標庫手工建立。
SQL> select table_name , owner, tablespace_name from dba_tables where tablespace_name not in ('SYSTEM','SYSAUX') and owner in ('SYS','SYSTEM');
no rows selected
1.9. 檢查表空間自包含
--檢查表空間時，只檢查業務表空間的自包含情況；系統表空間，臨時表空間，undo表空間不在檢查列
--在表空間傳輸的中，要求表空間集為自包含的，自包含表示用於傳輸的內部表空間集沒有引用指向外部表空間集。
常見的以下情況是違反自包含原則的：
- 索引在內部表空間集，而表在外部表空間集（相反地，如果表在內部表空間集，而索引在外部表空間集，則不違反自包含原則）。
- 分割槽表一部分割槽在內部表空間集，一部分在外部表空間集（對於分割槽表，要麼全部包含在內部表空間集中，要麼全不包含）。
- 如果在傳輸表空間時同時傳輸約束，則對於引用完整性約束，約束指向的表在外部表空間集，則違反自包含約束；如果不傳輸約束，則與約束指向無關。
- 表在內部表空間集，而lob列在外部表空間集，則違反自包含約束。
exec dbms_tts.transport_set_check('將所有需要傳輸的表空間名寫在這裡用逗號隔開' ,TRUE,TRUE);
select * from transport_set_violations;
SQL> exec dbms_tts.transport_set_check('XTTS',TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
1.10. 檢查源端compatible引數
source端不可以是windows P7,source端的compatible.rdbms必須大於10.2.0，且不大於目標端compatible.rdbms.
如果目標端資料庫版本是11.2.0.3或更低。那麼需要在目標端裝11.2.0.4 並建立例項，然後用來進行備份集轉換。如果11.2.0.4中轉例項使用ASM。那麼ASM版本也必須是11.2.0.4，否則報錯ORA-15295
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
1.11. 如果啟用block change tracking功能
如果源庫是11g，延時段特性需要先禁用alter system set deferred_segment_creation=false sid='*' scope=spfile;
不然xtts不會將空表匯入目標庫
SQL> select status from v$block_change_tracking;
STATUS
----------
DISABLED
SQL> alter database enable block change tracking using file'/home/oracle/xtts/track.log';
Database altered.
SQL> select status from v$block_change_tracking;
STATUS
----------
ENABLED
1.12. 檢查目標端的db_files引數
在後設資料匯入階段，如果目標端的db_files引數小於源端的db_files引數，會導致後設資料匯入出錯，所以要確保目標端引數比源端大於或者等於。
Show parameter db_files
1.13. 確認生產庫的recyclebin功能是關閉，並手工清空回收站
show parameter recyclebin;
NAME TYPE VALUE
----------- ---------------------- --------
recyclebin string on(這裡應該為off)
關閉回收站
alter system set recyclebin=off scope=spfile;
1.14. 資料檔案狀態檢查
--正常返回應為：ONLINE、SYSTEM
Select distinct status from v$datafile;
1.15 源資料庫必須處於ARCHIVELOG模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 73
Next log sequence to archive 82
Current log sequence 82
1.16 RMAN的預設裝置型別應配置為DISK
show default device type;
1.17 當前版本不支援Windows
1.18 源資料庫必須執行10.2.0.3或更高版本。
1.19 源端保留使用者資訊和許可權
--源端保留使用者資訊和許可權
spool create_user_LUOKLE.sql
select 'create user '||username||' identified by values '||''''||password||''''||';' from dba_users where default_tablespace in('XTTS');
spool off
--角色許可權的語句：
spool grant_role_priv_LUOKLE.sql
select 'grant '||GRANTED_ROLE||' to '||grantee||';' from dba_role_privs where grantee in(select username from dba_users where default_tablespace in('XTTS'));
spool off
--sys許可權的賦權語句：
spool grant_sys_priv_LUOKLE.sql
select 'grant '||privilege||' to '||grantee||';' from dba_sys_privs where grantee in(select username from dba_users where default_tablespace in('XTTS'));
spool off
--對錶空間的配額許可權語句：
spool unlimited_tablespace_LUOKLE.sql
select 'alter user '||username||' quota unlimited on XTTS;' from dba_users where default_tablespace in('XTTS');
spool off
--若後期存在使用者與其他非本使用者的物件許可權問題，如Schema A對Schema B上表的訪問和操作等許可權，可以使用以下語句在源庫檢索出許可權，並在目標端資料庫進行賦權即可：
set line 200
set pages 0
spool grant_tab_priv.sql
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
' to ' || grantee || ';'
from dba_tab_privs
where owner in
(select username from dba_users where default_tablespace in ('XTTS'))
or grantee in
(select username from dba_users where default_tablespace in ('XTTS'))
and privilege in ('SELECT', 'DELETE', 'UPDATE', 'INSERT')
and grantable = 'NO'
union
select 'grant ' || privilege || ' on ' || owner || '.' || table_name ||
' to ' || grantee || ' with grant option;'
from dba_tab_privs
where owner in
(select username from dba_users where default_tablespace in ('XTTS'))
or grantee in
(select username from dba_users where default_tablespace in ('XTTS'))
and privilege in ('SELECT', 'DELETE', 'UPDATE', 'INSERT')
and grantable = 'YES';
spool off
二. XTTS操作步驟
遷移步驟大致如下：
1. 初始化階段，配置引數檔案
2. 開啟塊追蹤特性
3. 準備階段，進行資料全量同步
4. 增量階段，進行資料檔案的增量同步
5. 在停機同步前，在目標端新建owner使用者，臨時表
6. 傳輸階段，最後一次增量同步，並匯入後設資料
7. 將原庫中的其餘使用者匯入到目標端中
8. 校驗資料檔案，檢查有無壞塊
三. XTTS之RMAN方式遷移
注意：
(1). 使用rman方式有一個嚴重的限制：同一批次的表空間傳輸到目標端後，都在同一個目錄下。即目標端的最終存放資料檔案的目錄大小要足夠存放一個批次中所有的表空間資料檔案。
(2). 源端，目標端需要掛載儲存用於存放所有資料檔案的映象檔案，建議使用ntp的方式將儲存遠端從源端掛載到目標端，減少備份傳送時間。
3.1. 初始化階段，配置引數檔案
3.1.1. 建立dest端到source端的dblink
--該步驟創立的dblink用於在傳輸階段的後設資料匯入時使用（目標庫操作）
--tnsnames.ora
xttslink=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.20)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)
--測試
tnsping xttslink
--建立公共link
create public database link testlink connect to system identified by oracle using 'xttslink';
select count(*) from test.TEST_XTTS@testlink;
3.1.2. source端和dest端都建立目錄用於存放xtts指令碼
[oracle@slient xtt]$ pwd
/home/oracle/xtt
[oracle@slient xtt]$ ls
rman_xttconvert_v3.zip
[oracle@slient xtt]$ unzip rman_xttconvert_v3.zip
Archive: rman_xttconvert_v3.zip
inflating: xtt.properties
inflating: xttcnvrtbkupdest.sql
inflating: xttdbopen.sql
inflating: xttdriver.pl
inflating: xttprep.tmpl
extracting: xttstartupnomount.sql
[oracle@slient xtt]$
3.1.3. 開啟塊追蹤特性
前面已經開啟
3.2. 準備階段，進行資料全量同步
3.2.1. 修改引數
--源端平臺id查詢
select * from v$transportable_platform;
SQL> select * 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.
--修改xtts配置檔案屬性引數
[oracle@slient xtt]$ cat xtt.properties
tablespaces=XTTS
platformid=13
dfcopydir=/home/oracle/xtt2/dfcopydir
backupformat=/home/oracle/xtt2/backup
stageondest=/home/oracle/xtt2/stageondest
storageondest=/home/oracle/xtt2/storageondest
backupondest=/home/oracle/xtt2/backupondest
cnvinst_sid=DBdb --dest:ORACLE_SID
parallel=2
rollparallel=2
getfileparallel=2
[oracle@slient xtt]$
--根據配置檔案建立目錄
--源庫
mkdir -p /home/oracle/xtt2/dfcopydir
mkdir -p /home/oracle/xtt2/backup
--目標庫
mkdir -p /home/oracle/xtt2/stageondest
mkdir -p /home/oracle/xtt2/storageondest
mkdir -p /home/oracle/xtt2/backupondest
3.2.2. 將xtt目錄(即xtts配置資訊）copy傳送到目標端
scp -r /home/oracle/xtt 192.168.56.12:/home/oracle
[oracle@slient xtt]$ scp -r /home/oracle/xtt 192.168.56.12:/home/oracle/
oracle@192.168.56.12's password:
xttcnvrtbkupdest.sql 100% 1390 1.4KB/s 00:00
rman_xttconvert_v3.zip 100% 33KB 33.2KB/s 00:00
xttstartupnomount.sql 100% 52 0.1KB/s 00:00
xttdriver.pl 100% 136KB 136.1KB/s 00:00
xttprep.tmpl 100% 11KB 11.4KB/s 00:00
xttdbopen.sql 100% 71 0.1KB/s 00:00
xtt.properties 100% 320 0.3KB/s 00:00
[oracle@slient xtt]$
3.2.3. 設定TMPDIR
In the shell environment on both source and destination systems, set environment variable TMPDIR to the location where the supporting scripts exist. Use this shell to run the Perl script xttdriver.pl as shown in the steps below. If TMPDIR is not set, output files are created in and input files are expected to be in /tmp.
[oracle@slient xtt]$ export TMPDIR=/home/oracle/xtt
[oracle@wang xtt]$ export TMPDIR=/home/oracle/xtt
3.2.4. 源端prepare
perl /home/oracle/xtt/xttdriver.pl -p
初始化之後產生xttplan.txt rmanconvert.cmd,xttplan.txt 記錄了當前SCN,也就是下次需要增量的開始SCN; rmanconvert.cmd 記錄了檔案轉換的名字.
[oracle@slient xtt]$ perl /home/oracle/xtt/xttdriver.pl -p
============================================================
trace file is /home/oracle/xtt/prepare_Oct18_Thu_10_37_19_11//Oct18_Thu_10_37_19_11_.log
=============================================================
.....................................................
[oracle@slient xtt]$
[oracle@slient xtt]$ pwd
/home/oracle/xtt
[oracle@slient xtt]$ cat xttplan.txt
XTTS::::21769193
8
[oracle@slient xtt]$
[oracle@slient xtt]$ cat rmanconvert.cmd
host 'echo ts::XTTS';
convert from platform 'Linux x86 64-bit'
datafile
'/home/oracle/xtt2/stageondest/XTTS_8.tf'
format '/home/oracle/xtt2/storageondest/%N_%f.dbf'
parallelism 2;
[oracle@slient xtt]$
3.2.5. 源端生成的資料檔案copy到目標端
scp /home/oracle/xtt2/dfcopydir/XTTS_8.tf 192.168.56.12:/home/oracle/xtt2/stageondest
[oracle@slient xtt]$ cd /home/oracle/xtt2/dfcopydir
[oracle@slient dfcopydir]$ ls -lrt
total 1048588
-rw-r----- 1 oracle oinstall 1073750016 Oct 18 10:38 XTTS_8.tf
[oracle@slient dfcopydir]$
[oracle@slient dfcopydir]$ scp /home/oracle/xtt2/dfcopydir/XTTS_8.tf 192.168.56.12:/home/oracle/xtt2/stageondest
oracle@192.168.56.12's password:
XTTS_8.tf 100% 1024MB 29.3MB/s 00:35
[oracle@slient dfcopydir]$
3.2.6. 轉換資料檔案copy的endian模式
--將源庫的rmanconvert.cmd傳到目標庫
scp rmanconvert.cmd 192.168.56.12:/home/oracle/xtt
[oracle@slient xtt]$ scp rmanconvert.cmd 192.168.56.12:/home/oracle/xtt
oracle@192.168.56.12's password:
rmanconvert.cmd 100% 196 0.2KB/s 00:00
[oracle@slient xtt]$
--在目標庫執行轉換操作
export TMPDIR=/home/oracle/xtt
perl xttdriver.pl -c
[oracle@wang xtt]$ ls -lrt
total 208
-rw-r--r-- 1 oracle oinstall 1390 Jul 29 18:57 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 33949 Jul 29 18:57 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 52 Jul 29 18:57 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 139331 Jul 29 18:57 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 11710 Jul 29 18:57 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 71 Jul 29 18:57 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 320 Jul 29 18:57 xtt.properties
-rw-r--r-- 1 oracle oinstall 196 Jul 29 19:00 rmanconvert.cmd
[oracle@wang xtt]$
[oracle@wang xtt]$ export TMPDIR=/home/oracle/xtt
[oracle@wang xtt]$ perl xttdriver.pl -c
============================================================
trace file is /home/oracle/xtt/convert_Jul29_Sun_19_01_13_48//Jul29_Sun_19_01_13_48_.log
=============================================================
........................................
--------------------------------------------------------------------
Performing convert
--------------------------------------------------------------------
[oracle@wang xtt]$
--轉換成功之後會生成xttnewdatafiles.txt
整個過程，存放在stageondest的copy，將會轉換寫入到storageondest引數設定的最終存放點。轉換完成後，copy就可以刪除了。
[oracle@wang xtt]$ ls -lrt
total 212
-rw-r--r-- 1 oracle oinstall 1390 Jul 29 18:57 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 33949 Jul 29 18:57 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 52 Jul 29 18:57 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 139331 Jul 29 18:57 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 11710 Jul 29 18:57 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 71 Jul 29 18:57 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 320 Jul 29 18:57 xtt.properties
-rw-r--r-- 1 oracle oinstall 196 Jul 29 19:00 rmanconvert.cmd
-rw-r--r-- 1 oracle oinstall 52 Jul 29 19:02 xttnewdatafiles.txt
drwxr-xr-x 2 oracle oinstall 65 Jul 29 19:02 convert_Jul29_Sun_19_01_13_48
[oracle@wang storageondest]$ cd /home/oracle/xtt2/stageondest/
[oracle@wang stageondest]$ ls
XTTS_8.tf
[oracle@wang stageondest]$ cd /home/oracle/xtt2/storageondest/
[oracle@wang storageondest]$ ls
XTTS_8.dbf
[oracle@wang storageondest]$
3.3. 增量階段，進行資料檔案的增量同步
3.3.1. 源端建立增量備份
--第一次增量備份之後產生的配置檔案為tsbkupmap.txt/incrbackups.txt，這兩個為增量與資料檔案對應關係配置,在做增量恢復時候需要用到。增量備份檔案放在了backupformat指定位置.
perl xttdriver.pl -i
[oracle@slient xtt]$ perl xttdriver.pl -i
============================================================
trace file is /home/oracle/xtt/incremental_Oct18_Thu_11_34_55_502//Oct18_Thu_11_34_55_502_.log
=============================================================
............................................
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------
[oracle@slient xtt]$
[oracle@slient xtt]$ ls -lrt
total 236
-rw-r--r-- 1 oracle oinstall 1390 May 24 2017 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 52 May 24 2017 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 11710 May 24 2017 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 139331 May 24 2017 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 71 May 24 2017 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 33949 Oct 16 21:12 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 320 Oct 18 11:13 xtt.properties
-rw-r--r-- 1 oracle oinstall 19 Oct 18 11:16 xttplan.txt
-rw-r--r-- 1 oracle oinstall 196 Oct 18 11:16 rmanconvert.cmd
-rw-r--r-- 1 oracle oinstall 52 Oct 18 11:17 xttnewdatafiles.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:17 prepare_Oct18_Thu_11_16_54_355
-rw-r--r-- 1 oracle oinstall 21 Oct 18 11:34 xttplan.txt.new
-rw-r--r-- 1 oracle oinstall 25 Oct 18 11:34 tsbkupmap.txt
-rw-r--r-- 1 oracle oinstall 38 Oct 18 11:34 incrbackups.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:34 incremental_Oct18_Thu_11_34_55_502
[oracle@slient xtt]$ cat tsbkupmap.txt
XTTS::8:::1=03tfvgn1_1_1
[oracle@slient xtt]$
[oracle@slient xtt]$ cat incrbackups.txt
/home/oracle/xtt2/backup/03tfvgn1_1_1
[oracle@slient xtt]$
[oracle@slient xtt]$ cd /home/oracle/xtt2/backup/
[oracle@slient backup]$ ls
03tfvgn1_1_1
[oracle@slient backup]$
3.3.2. 傳輸增量備份到目標端
scp `cat incrbackups.txt` 192.168.56.12:/home/oracle/xtt2/stageondest
--源庫
[oracle@slient xtt]$ scp `cat incrbackups.txt` 192.168.56.12:/home/oracle/xtt2/stageondest
oracle@192.168.56.12's password:
03tfvgn1_1_1 100% 40KB 40.0KB/s 00:00
[oracle@slient xtt]$
--目標庫
[oracle@wang stageondest]$ cd /home/oracle/xtt2/stageondest
[oracle@wang stageondest]$ ls
03tfvgn1_1_1 XTTS_8.tf
[oracle@wang stageondest]$
3.3.3. 目標端應用增量備份
--源庫操作：xttplan.txt,在做完增量之後會更新，xttplan.txt檔案記錄了增量SCN起始位置，tsbkupmap.txt和incrbackups.txt，這兩個為增量與資料檔案對應關係配置，在做增量恢復時候需要用到。
scp xttplan.txt tsbkupmap.txt 192.168.56.12:/home/oracle/xtt
[oracle@slient xtt]$ scp xttplan.txt tsbkupmap.txt 192.168.56.12:/home/oracle/xtt
oracle@192.168.56.12's password:
xttplan.txt 100% 19 0.0KB/s 00:00
tsbkupmap.txt 100% 25 0.0KB/s 00:00
[oracle@slient xtt]$
--目標庫操作：增量恢復前需要檢查xttnewdatafiles.txt（資料檔案在ASM中MAP關係表）、tsbkupmap.txt和incrbackups.txt（增量與資料檔案對應關係配置）、xttplan.txt（下次需要增量的開始SCN）這些配置檔案是否存在,如不存在會出現報錯。
perl xttdriver.pl -r
[oracle@wang xtt]$ ls -lrt
total 220
-rw-r--r-- 1 oracle oinstall 1390 Jul 29 18:57 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 33949 Jul 29 18:57 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 52 Jul 29 18:57 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 139331 Jul 29 18:57 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 11710 Jul 29 18:57 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 71 Jul 29 18:57 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 320 Jul 29 18:57 xtt.properties
-rw-r--r-- 1 oracle oinstall 196 Jul 29 19:00 rmanconvert.cmd
-rw-r--r-- 1 oracle oinstall 52 Jul 29 19:02 xttnewdatafiles.txt
drwxr-xr-x 2 oracle oinstall 65 Jul 29 19:02 convert_Jul29_Sun_19_01_13_48
-rw-r--r-- 1 oracle oinstall 19 Jul 29 19:19 xttplan.txt
-rw-r--r-- 1 oracle oinstall 25 Jul 29 19:19 tsbkupmap.txt
[oracle@wang xtt]$
[oracle@wang xtt]$ cat xttplan.txt
XTTS::::21769193
8
[oracle@wang xtt]$ cat xttnewdatafiles.txt
::XTTS
8,/home/oracle/xtt2/storageondest/XTTS_8.dbf
[oracle@wang xtt]$
[oracle@wang xtt]$ cat tsbkupmap.txt
XTTS::8:::1=03tfvgn1_1_1
[oracle@wang xtt]$
[oracle@wang xtt]$ perl xttdriver.pl -r
============================================================
trace file is /home/oracle/xtt/rollforward_Jul29_Sun_19_21_37_238//Jul29_Sun_19_21_37_238_.log
=============================================================
........................................................
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------
[oracle@wang xtt]$
3.3.4. 源端為之後的增量備份確定新的scn
--該步驟會將-i時生成的xttplan.txt.new改名為xttplan.txt,並將原來的xttplan.txt備份
perl xttdriver.pl -s
--源庫對錶做插入操作
SQL> conn test/test;
Connected.
SQL> select * from tab;
SQL> insert into test_xtts values(4000001,sysdate,'wang',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from TEST_XTTS;
COUNT(*)
----------
4000001
--源庫執行確定新的scn
[oracle@slient xtt]$ perl xttdriver.pl -s
============================================================
trace file is /home/oracle/xtt/determinescn_Oct18_Thu_11_58_13_235//Oct18_Thu_11_58_13_235_.log
=============================================================
..................................................
Prepare newscn for Tablespaces: 'XTTS'
New /home/oracle/xtt/xttplan.txt with FROM SCN's generated
[oracle@slient xtt]$
[oracle@slient xtt]$ cat xttplan.txt
XTTS::::21771098
8
[oracle@slient xtt]$
3.4. 停機同步前，在目標端新建owner使用者
3.4.1. 建立臨時表空間
--依照原庫上的臨時表空間大小，在目標端建立同名臨時表空間
SQL> create temporary tablespace temp02 tempfile '/u01/app/oracle/oradata/DBdb/temp02' size 512m;
Tablespace created.
3.4.2. 建立owner使用者
--在源端執行下列語句(user$)
select 'create user '||name||' identified by values '''||password||''' default tablespace users temporary tablespace temp;' from user$ where name in ('TEST');
--將生成的語句，在目標端執行，建立使用者
SQL> create user TEST identified by values '7A0F2B316C212D67' default tablespace users temporary tablespace temp;
User created.
3.5. 傳輸階段，最後一次增量同步，並匯入後設資料
3.5.1. 將原庫表空間置為只讀狀態
alter tablespace xtts read only;
SQL> alter tablespace xtts read only;
Tablespace altered.
3.5.2. 開始最後做增量備份
重複步驟3.3.1-3.3.3，做最後的增量恢復
--源端建立增量備份
export TMPDIR=/home/oracle/xtt
perl xttdriver.pl -i
[oracle@slient xtt]$ export TMPDIR=/home/oracle/xtt
[oracle@slient xtt]$ perl xttdriver.pl -i
============================================================
trace file is /home/oracle/xtt/incremental_Oct18_Thu_14_43_31_140//Oct18_Thu_14_43_31_140_.log
=============================================================
..........................
--------------------------------------------------------------------
Done backing up incrementals
--------------------------------------------------------------------
[oracle@slient xtt]$ ls -lrt
total 248
-rw-r--r-- 1 oracle oinstall 1390 May 24 2017 xttcnvrtbkupdest.sql
-rw-r--r-- 1 oracle oinstall 52 May 24 2017 xttstartupnomount.sql
-rw-r--r-- 1 oracle oinstall 11710 May 24 2017 xttprep.tmpl
-rw-r--r-- 1 oracle oinstall 139331 May 24 2017 xttdriver.pl
-rw-r--r-- 1 oracle oinstall 71 May 24 2017 xttdbopen.sql
-rw-r--r-- 1 oracle oinstall 33949 Oct 16 21:12 rman_xttconvert_v3.zip
-rw-r--r-- 1 oracle oinstall 320 Oct 18 11:13 xtt.properties
-rw-r--r-- 1 oracle oinstall 196 Oct 18 11:16 rmanconvert.cmd
-rw-r--r-- 1 oracle oinstall 52 Oct 18 11:17 xttnewdatafiles.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:17 prepare_Oct18_Thu_11_16_54_355
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:34 incremental_Oct18_Thu_11_34_55_502
-rw-r--r-- 1 oracle oinstall 21 Oct 18 11:58 xttplan.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 11:58 determinescn_Oct18_Thu_11_58_13_235
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 14:42 Oct18_Thu_14_42_37_778
-rw-r--r-- 1 oracle oinstall 21 Oct 18 14:43 xttplan.txt.new
-rw-r--r-- 1 oracle oinstall 25 Oct 18 14:43 tsbkupmap.txt
-rw-r--r-- 1 oracle oinstall 38 Oct 18 14:43 incrbackups.txt
drwxr-xr-x 2 oracle oinstall 4096 Oct 18 14:43 incremental_Oct18_Thu_14_43_31_140
[oracle@slient xtt]$ cat xttplan.txt
XTTS::::21771098
8
[oracle@slient xtt]$ cat xttplan.txt.new
XTTS::::21780327
8
[oracle@slient xtt]$
[oracle@slient xtt]$ cat tsbkupmap.txt
XTTS::8:::1=04tfvrok_1_1
[oracle@slient xtt]$ cat incrbackups.txt
/home/oracle/xtt2/backup/04tfvrok_1_1
[oracle@slient xtt]$
--傳輸增量備份到目標端
scp `cat incrbackups.txt` 192.168.56.12:/home/oracle/xtt2/stageondest
[oracle@slient xtt]$ scp `cat incrbackups.txt` 192.168.56.12:/home/oracle/xtt2/stageondest
oracle@192.168.56.12's password:
05tfvshp_1_1 100% 64KB 64.0KB/s 00:00
[oracle@slient xtt]$
--目標端應用增量備份
--源庫操作：xttplan.txt,在做完增量之後會更新，xttplan.txt檔案記錄了增量SCN起始位置，tsbkupmap.txt和incrbackups.txt，這兩個為增量與資料檔案對應關係配置，在做增量恢復時候需要用到
scp xttplan.txt tsbkupmap.txt 192.168.56.12:/home/oracle/xtt
[oracle@slient xtt]$ scp xttplan.txt tsbkupmap.txt 192.168.56.12:/home/oracle/xtt
oracle@192.168.56.12's password:
xttplan.txt 100% 21 0.0KB/s 00:00
tsbkupmap.txt 100% 25 0.0KB/s 00:00
[oracle@slient xtt]$
--目標庫操作：增量恢復前需要檢查xttnewdatafiles.txt（資料檔案在ASM中MAP關係表）、tsbkupmap.txt和incrbackups.txt（增量與資料檔案對應關係配置）、xttplan.txt（下次需要增量的開始SCN）這些配置檔案是否存在,如不存在會出現報錯
--scp xttplan.txt tsbkupmap.txt之前檢視目標庫
[oracle@wang xtt]$ cat xttplan.txt
XTTS::::21769193
8
[oracle@wang xtt]$
--scp xttplan.txt tsbkupmap.txt之後檢視目標庫
[oracle@wang xtt]$ cat xttplan.txt
XTTS::::21771098
8
[oracle@wang xtt]$ cat tsbkupmap.txt
XTTS::8:::1=05tfvshp_1_1
[oracle@wang xtt]$
--目標庫實施最後的增量應用
export TMPDIR=/home/oracle/xtt
perl xttdriver.pl -r -d
[oracle@wang xtt]$ export TMPDIR=/home/oracle/xtt
[oracle@wang xtt]$ perl xttdriver.pl -r -d
============================================================
trace file is /home/oracle/xtt/rollforward_Jul29_Sun_22_36_34_381//Jul29_Sun_22_36_34_381_.log
=============================================================
--------------------------------------------------------------------
Parsing properties
--------------------------------------------------------------------
Key: backupondest
Values: /home/oracle/xtt2/backupondest
Key: platformid
Values: 13
Key: backupformat
Values: /home/oracle/xtt2/backup
Key: parallel
Values: 2
Key: storageondest
Values: /home/oracle/xtt2/storageondest
Key: dfcopydir
Values: /home/oracle/xtt2/dfcopydir
Key: cnvinst_sid
Values: DBdb --dest:ORACLE_SID
Key: rollparallel
Values: 2
Key: stageondest
Values: /home/oracle/xtt2/stageondest
Key: tablespaces
Values: XTTS
Key: getfileparallel
Values: 2
--------------------------------------------------------------------
Done parsing properties
--------------------------------------------------------------------
--------------------------------------------------------------------
Checking properties
--------------------------------------------------------------------
ARGUMENT tablespaces
ARGUMENT platformid
ARGUMENT backupformat
ARGUMENT stageondest
ARGUMENT backupondest
--------------------------------------------------------------------
Done checking properties
--------------------------------------------------------------------
ORACLE_SID : DBdb
ORACLE_HOME : /u01/app/oracle/product/11.2.0/db_1
--------------------------------------------------------------------
Start rollforward
--------------------------------------------------------------------
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
rdfno 8
BEFORE ROLLPLAN
datafile number : 8
datafile name : /home/oracle/xtt2/storageondest/XTTS_8.dbf
AFTER ROLLPLAN
CONVERTED BACKUP PIECE/home/oracle/xtt2/backupondest/xib_05tfvshp_1_1_8
PL/SQL procedure successfully completed.
Entering RollForward
After applySetDataFile
Done: applyDataFileTo
Done: applyDataFileTo
Done: RestoreSetPiece
Done: RestoreBackupPiece
PL/SQL procedure successfully completed.
--------------------------------------------------------------------
End of rollforward phase
--------------------------------------------------------------------
[oracle@wang xtt]$
3.5.3. 自定義profile處理
--執行源端保留的profile資訊
set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
set echo on
set timing off
set wrap On
SET LONGCHUNKSIZE 400
spool create_profile.sql
with profile as(
select distinct profile from dba_profiles where profile in(select PROFILE from dba_users where username='TEST'))
select dbms_metadata.get_ddl('PROFILE','DEFAULT') from profile;
不用做此步驟
3.5.6. 目標端匯入後設資料
3.5.6.1. 一次後設資料匯入
perl xttdriver.pl -e
這個步驟會產生一個匯入樣本指令碼xttplugin.txt，需要修改如下：
在一次後設資料匯入階段，不能開啟並行，統計資訊匯入階段，可以將會話殺除終止
------------------
cat xttplugin.txt
impdp directory=DATA_PUMP_DIR logfile=tts_imp.log \
network_link=testlink transport_full_check=no \
transport_tablespaces=XTTS \
transport_datafiles='/home/oracle/xtt2/storageondest/XTTS_8.dbf' exclude=statistics
將統計資訊排除，後面開啟並行收集統計資訊
------------------
[oracle@wang xtt]$ perl xttdriver.pl -e
============================================================
trace file is /home/oracle/xtt/generate_Jul29_Sun_22_58_19_528//Jul29_Sun_22_58_19_528_.log
=============================================================
...........................
--------------------------------------------------------------------
Done generating plugin file /home/oracle/xtt/xttplugin.txt
--------------------------------------------------------------------
[oracle@wang xtt]$
[oracle@wang xtt]$ cat xttplugin.txt
impdp directory=DATA_PUMP_DIR logfile=tts_imp.log \
network_link=testlink transport_full_check=no \
transport_tablespaces=XTTS \
transport_datafiles='/home/oracle/xtt2/storageondest/XTTS_8.dbf' exclude=statistics
[oracle@wang xtt]$
--經過查詢目錄物件DATA_PUMP_DIR已經存在，預設值。
SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ---------------------------------------------------------------------------
SYS DATA_PUMP_DIR /u01/app/oracle/admin/DBdb/dpdump/
--執行
[oracle@wang xtt]$ sh xttplugin.txt
Import: Release 11.2.0.4.0 - Production on Sun Jul 29 23:25:26 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: system
Password:
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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=DATA_PUMP_DIR logfile=tts_imp.log network_link=testlink transport_full_check=no transport_tablespaces=XTTS transport_datafiles=/home/oracle/xtt2/storageondest/XTTS_8.dbf exclude=statistics
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sun Jul 29 23:26:14 2018 elapsed 0 00:00:39
[oracle@wang xtt]$
3.5.6.2. 二次後設資料匯入
將第一次沒有匯入的過程，檢視，包，觸發器匯入
impdp directory=DATA_PUMP_DIR logfile=tts_imp_2.log network_link=testlink schemas= 'TEST' content=metadata_only exclude=index,table,constraint,statistics
[oracle@wang xtt]$ impdp directory=DATA_PUMP_DIR logfile=tts_imp_2.log network_link=testlink schemas= 'TEST' content=metadata_only exclude=index,table,constraint,statistics
Import: Release 11.2.0.4.0 - Production on Sun Jul 29 23:29:36 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: SYSTEM
Password:
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
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": SYSTEM/******** directory=DATA_PUMP_DIR logfile=tts_imp_2.log network_link=testlink schemas= content=metadata_only exclude=index,table,constraint,statistics
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sun Jul 29 23:29:53 2018 elapsed 0 00:00:09
[oracle@wang xtt]$
3.5.6.3. 重新在目標端蒐集物件統計資訊
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TEST',ESTIMATE_PERCENT=>100,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>2);
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'TEST',ESTIMATE_PERCENT=>100,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>2);
PL/SQL procedure successfully completed.
3.6. 將原庫中的其餘使用者匯入到目標端中，許可權處理
使用如下指令碼重新賦權
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET TRIMSPOOL ON
set long 999999
SET PAGESIZE 1000
spool grant_role_priv.sql
select 'grant '||GRANTED_ROLE||' to '||grantee||';' from dba_role_privs where grantee in('TEST') and admin_option='NO'
union
select 'grant '||GRANTED_ROLE||' to '||grantee||' with admin option;' from dba_role_privs where grantee in('TEST') and admin_option='YES';
spool off
===============
--目標庫執行
SQL> grant CONNECT to TEST;
grant RESOURCE to TEST;
Grant succeeded.
===============
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
set long 999999
SET TRIMSPOOL ON
SET PAGESIZE 1000
spool grant_sys_priv.sql
select 'grant '||privilege||' to '||grantee||';' from dba_sys_privs where grantee in('TEST') and admin_option='NO'
union
select 'grant '||privilege||' to '||grantee||' with admin option;' from dba_sys_privs where grantee in('TEST')and admin_option='YES';
spool off
===============
--目標庫執行
SQL> grant UNLIMITED TABLESPACE to TEST;
Grant succeeded.
===============
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET TRIMSPOOL ON
set long 999999
SET PAGESIZE 1000
spool grant_tab_privs.sql
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';' from dba_tab_privs where grantee in('TEST') and grantable='NO'
union
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ' with grant option;' from dba_tab_privs where grantee in('TEST') and grantable='YES';
spool off
3.7. 更改使用者（test)預設的永久表空間、臨時表空間，比對源庫
--源庫執行
select 'alter user test default tablespace '||DEFAULT_TABLESPACE||' temporary tablespace '||TEMPORARY_TABLESPACE||';' from dba_users where USERNAME='TEST';
--根據上述查詢出來的sql在目標庫執行
SQL> alter user test default tablespace XTTS temporary tablespace TEMP02;
User altered.
3.8. 目標庫校驗資料檔案，檢查有無壞塊
RMAN> validate tablespace xtts check logical;
編譯無效物件，進行資料對比
[oracle@wang xtt]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jul 29 23:42:05 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBDB (DBID=3282897732)
RMAN> validate tablespace xtts check logical;
Starting validate at 29-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/home/oracle/xtt2/storageondest/XTTS_8.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:08
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 39556 131072 21772837
File Name: /home/oracle/xtt2/storageondest/XTTS_8.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 71297
Index 0 19423
Other 0 796
Finished validate at 29-JUL-18
3.9. 將目標庫表空間xtts 置為讀寫狀態
alter tablespace xtts read write;
SQL> alter tablespace xtts read write;
Tablespace altered.
3.10 目標庫查詢驗證
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='TEST';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------------- ------------------------------ ------------------------------
TEST XTTS TEMP02
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,NUM_ROWS,BLOCKS from dba_tables where table_name='TEST_XTTS';
OWNER TABLE_NAME TABLESPACE_NAME STATUS NUM_ROWS BLOCKS
------------ ---------------- -------------------- -------- ---------- ----------
TEST TEST_XTTS XTTS VALID 4000001 71297
SQL> select OWNER,SEGMENT_NAME,TABLESPACE_NAME,BYTES/1024/1024 size_m from dba_segments where SEGMENT_NAME='TEST_XTTS';
OWNER SEGMENT_NAME TABLESPACE_NAME SIZE_M
-------------- ------------------------- -------------------- ----------
TEST TEST_XTTS XTTS 560
SQL> select * from test.TEST_XTTS where ID=4000001;
ID CREATE_DATE NAME UP_DATE
---------- ------------ --------------------- ------------
4000001 18-OCT-18 wang 18-OCT-18
SQL> col TABLESPACE_NAME for a20
SQL> col file_name for a60
SQL> select s.file_id,
2 s.file_name,
3 s.tablespace_name,
4 s.bytes / 1024 / 1024 size_m,
5 s.ONLINE_STATUS,
6 d.status,
7 d.contents
8 from dba_data_files s, dba_tablespaces d
9 where s.TABLESPACE_NAME = d.TABLESPACE_NAME and s.TABLESPACE_NAME ='XTTS';
FILE_ID FILE_NAME TABLESPACE_NAME SIZE_M ONLINE_ STATUS CONTENTS
---------- ------------------------------------------------------------ -------------------- ---------- ------- --------- ---------
6 /home/oracle/xtt2/storageondest/XTTS_8.dbf XTTS 1024 ONLINE ONLINE PERMANENT
至此，XTTS遷移完成！！！！！！！！！！！！！！！！！！！！！！！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2216817/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle xtts遷移 AIX to LinuxOracleTTSAILinux
- 【XTTS】Oracle XTTS V4--Oracle11.2.0.4+ 遷移升級TTSOracle
- xtts遷移時ORA-353處理TTS
- 超大資料量遷移方案，XTTS vs OGG大資料TTS
- kafka資料遷移實踐Kafka
- 記錄一次XTTS遷移碰到的問題TTS
- oracle xtts資料庫遷移方法測試之一OracleTTS資料庫
- Swift Static Libraries遷移實踐Swift
- VPGAME的Kubernetes遷移實踐GAM
- VPGAME 的 Kubernetes 遷移實踐GAM
- 小米Kylin平滑遷移HBase實踐
- 【kingsql分享】Oracle跨版本遷移之XTTS_V4版本的實施SQLOracleTTS
- cassandra百億級資料庫遷移實踐資料庫
- 360 數科實踐：JanusGraph 到 NebulaGraph 遷移
- 大型系統儲存層遷移實踐
- 達達埋點遷移京東子午線實踐
- Jenkins搭建與資料遷移實踐Jenkins
- Datapump資料遷移的實踐總結
- Hadoop資料遷移MaxCompute最佳實踐Hadoop
- 資料庫平滑遷移方案與實踐分享資料庫
- 攜程MySQL遷移OceanBase最佳實踐｜分享MySql
- PgSQL·最佳實踐·雲上的資料遷移SQL
- 【資料遷移】XTTS跨平臺傳輸表空間v4TTS
- 線上資料遷移，數字化時代的必修課 —— 京東雲資料遷移實踐
- 阿里雲NAS檔案遷移專案實踐阿里
- XTTS系列之一：U2L遷移解決方案之XTTS的使用TTS
- 【資料遷移】XTTS跨平臺傳輸表空間（1.傳統方式）TTS
- 高途資料平臺遷移與成本治理實踐
- 內容遷移或域名切換的期望與實踐
- 最少停機時間遷移資料庫: XTTS + Cross Platform Incremental Backup(增量備份) - 1資料庫TTSROSPlatformREM
- 最少停機時間遷移資料庫: XTTS + Cross Platform Incremental Backup(增量備份) - 2資料庫TTSROSPlatformREM
- .net core遷移實踐：專案檔案csproj的轉換
- 從 Oracle 到 TiDB，全鏈路資料遷移平臺核心能力和杭州銀行遷移實踐OracleTiDB
- 【資料遷移】XTTS跨平臺傳輸表空間v3（3.DFT方式）TTS
- 【資料遷移】XTTS跨平臺傳輸表空間v3（2.RMAN增量）TTS
- Oracle行遷移實驗Oracle
- 實踐案例：平安健康的 Dubbo3 遷移歷程總結
- 全量、增量資料在HBase遷移的多種技巧實踐