[20170622]傳輸表空間與dblink.txt--//測試看看使用dblink+傳輸表空間的情況.寫的情況相對複雜一點,具體看測試:1.環境:--//2個資料庫版本一致.實際上在1台主機上.SCOTT@book> @ &r/ver1PORT_STRING VERSION BANNER x86_ ...
[20170622]傳輸表空間與dblink.txt
--//測試看看使用dblink+傳輸表空間的情況.寫的情況相對複雜一點,具體看測試:
1.環境:
--//2個資料庫版本一致.實際上在1台主機上.
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------- ---------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@dbcn1> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@dbcn1> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header where tablespace_name='USERS';
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------------------------- ----------------
4 12499575795 2017-06-22 09:04:40 16143 12499554377 ONLINE 1286 YES /u01/app/oracle/oradata/dbcn1/datafile/users.307.8 USERS
62160493
--//傳輸表空間users到實例book.
2.檢查表空間相容性:
SYS@dbcn1> Execute sys.dbms_tts.transport_set_check('users',TRUE,TRUE);
PL/SQL procedure successfully completed.
SYS@dbcn1> SELECT * FROM sys.transport_set_violations;
VIOLATIONS
--------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index JSTJ.SYS_C0012915 in tablespace USERS enforces primary constraints of table JSTJ.XTLAB_RESULT in tablespace TSP_JSTJ.
--//移動索引 JSTJ.SYS_C0012915 到TSP_JSTJ表空間.
SYS@dbcn1> Execute sys.dbms_tts.transport_set_check('users',TRUE,TRUE);
PL/SQL procedure successfully completed.
SYS@dbcn1> SELECT * FROM sys.transport_set_violations;
no rows selected
3.開始:
--//設置表空間只讀:
SYS@dbcn1> alter tablespace users read only;
Tablespace altered.
--//拷貝相關數據文件到本機,我這裡在同一臺電腦,使用cp命令.(註我該了名)
$ cp /u01/app/oracle/oradata/dbcn1/datafile/users.307.862160493 /home/oracle/backup/sugar01.dbf
4.建立dblink:
create public database link "l_dbcn1"
connect to SYSTEM
identified by "xxxx"
using '192.168.100.78:1521/DBCN1';
--//測試是否ok.
SCOTT@book> select * from scott.dept@l_dbcn1 where rownum=1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
--//由於表空間users在實例book已經存在,必須映射一個新的空間,另外我本機也存在scott.dept.必須建立新的用戶.
SCOTT@book> grant dba to sss IDENTIFIED BY sss;
Grant succeeded.
--//補充說明1點,這樣直接grant 可以直接建立用戶,並且賦予dba許可權.測試ok.
5.開始導入操作:
impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf
REMAP_TABLESPACE=USERS:SUAGR REMAP_SCHEMA=scott:sss logfile=impdp.log
--//命令有點長.^_^,意思很容易理解.
$ impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf REMAP_TABLESPACE=USERS:SUAGR REMAP_SCHEMA=scott:sss logfile=impdp.log
Import: Release 11.2.0.4.0 - Production on Thu Jun 22 09:35: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 "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01": scott/a** network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.dbf REMAP_TABLESPACE=USERS:SUAGR REMAP_SCHEMA=scott:sss logfile=impdp.log
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/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SCOTT"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu Jun 22 09:35:45 2017 elapsed 0 00:00:22
$ echo 'select * from dept where rownum=1;' | sqlplus -s sss/sss
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ---------------
1 13277598620 2017-06-22 03:00:48 7 925702 ONLINE 912 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13277598620 2017-06-22 03:00:48 1834 925702 ONLINE 901 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13277598620 2017-06-22 03:00:48 923328 925702 ONLINE 822 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13277598620 2017-06-22 03:00:48 16143 925702 ONLINE 931 YES /mnt/ramdisk/book/users01.dbf USERS
5 13277598620 2017-06-22 03:00:48 952916 925702 ONLINE 818 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13277598620 2017-06-22 03:00:48 13276257767 925702 ONLINE 309 YES /mnt/ramdisk/book/tea01.dbf TEA
7 12499578739 2017-06-22 09:21:14 16143 12499554377 ONLINE 1287 NO /home/oracle/backup/sugar01.dbf USERS
7 rows selected.
--//昏,前面不小心把單詞sugar寫成SUAGR了(REMAP_TABLESPACE=USERS:SUAGR),不影響測試結論.^_^.等一會在修改表空間名字.
--//不過這裡註意這個時候表空間還是users,因為這個時候數據文件還是只讀的.
SCOTT@book> select tablespace_name,status from dba_tablespaces where tablespace_name='SUAGR';
TABLESPACE_NAME STATUS
--------------- ---------
SUAGR READ ONLY
--//先改名看看是否可以.
SCOTT@book> alter tablespace SUAGR rename to SUGAR;
Tablespace altered.
6.接著看看一些現象:
SCOTT@book> select rowid,dept.* from scott.dept ;
ROWID DEPTNO DNAME LOC
------------------ ------ -------------- -------------
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAAVRCAAEAAAACHAAB 20 RESEARCH DALLAS
AAAVRCAAEAAAACHAAC 30 SALES CHICAGO
AAAVRCAAEAAAACHAAD 40 OPERATIONS BOSTON
SCOTT@book> select rowid,dept.* from sss.dept ;
ROWID DEPTNO DNAME LOC
------------------ ------ -------------- -------------
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAAVRCAAEAAAACHAAB 20 RESEARCH DALLAS
AAAVRCAAEAAAACHAAC 30 SALES CHICAGO
AAAVRCAAEAAAACHAAD 40 OPERATIONS BOSTON
--//你可以發現rowid兩個表是一樣的.
SCOTT@book> column EDITION_NAME noprint
SCOTT@book> select * from dba_objects where object_name='DEPT';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE
------ ----------- ---------- ------------ -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ------------
SCOTT DEPT 87106 87106 TABLE 2013-08-24 12:04:21 2013-08-24 12:04:21 2013-08-24:12:04:21 VALID N N N 1
SSS DEPT 104210 87106 TABLE 2017-06-22 09:35:32 2017-06-22 09:35:36 2017-06-22:09:35:32 VALID N N N 1
--//你可以發現DATA_OBJECT_ID是一樣的.打開讀寫看看.
SCOTT@book> alter tablespace sugar read write ;
Tablespace altered.
SCOTT@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE# , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME CREATION_CHANGE# RESETLOGS_CHANGE# STATUS CHECKPOINT_COUNT FUZ NAME TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ---------------
1 13277598620 2017-06-22 03:00:48 7 925702 ONLINE 912 YES /mnt/ramdisk/book/system01.dbf SYSTEM
2 13277598620 2017-06-22 03:00:48 1834 925702 ONLINE 901 YES /mnt/ramdisk/book/sysaux01.dbf SYSAUX
3 13277598620 2017-06-22 03:00:48 923328 925702 ONLINE 822 YES /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
4 13277598620 2017-06-22 03:00:48 16143 925702 ONLINE 931 YES /mnt/ramdisk/book/users01.dbf USERS
5 13277598620 2017-06-22 03:00:48 952916 925702 ONLINE 818 YES /mnt/ramdisk/book/example01.dbf EXAMPLE
6 13277598620 2017-06-22 03:00:48 13276257767 925702 ONLINE 309 YES /mnt/ramdisk/book/tea01.dbf TEA
7 13277642074 2017-06-22 09:54:55 13277642064 925702 ONLINE 3 YES /home/oracle/backup/sugar01.dbf SUGAR
7 rows selected.
--//註意file#=7這行.你可以發現CHECKPOINT_COUNT=3(重新記數了),CHECKPOINT_CHANGE#從12499578739=>13277642074.
--//你可以反過來想像一下,加入傳輸的表空間記錄的scn大於當前資料庫的scn,這樣可以大幅提升資料庫的scn.
SCOTT@book> select rowid,a.* from scott.dept a where rownum=1 union all select rowid,b.* from sss.dept b where rownum=1;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAAVRCAAEAAAACHAAA 10 ACCOUNTING NEW YORK
--//你可以發現兩個表的rowid一樣,因為什麼呢?對象的DATA_OBJECT_ID,file號,block號,行號都一樣.
SCOTT@book> @ &r/rowid AAAVRCAAEAAAACHAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------ ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
87106 4 135 0 0x1000087 4,135 alter system dump datafile 4 block 135 ;
--//註意看這裡file=4,而不是前面查看FILE#=7.這也是oralce引入的絕對文件號與相對文件號的概念.換一句話講rowid看到的相對文件
--//號.
SCOTT@book> select * FROM dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
------------------------------- ------- --------------- ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- ----------- -------
/mnt/ramdisk/book/users01.dbf 4 USERS 157810688 19264 AVAILABLE 4 YES 3.4360E+10 4194302 160 156762112 19136 ONLINE
/mnt/ramdisk/book/undotbs01.dbf 3 UNDOTBS1 907018240 110720 AVAILABLE 3 YES 3.4360E+10 4194302 640 905969664 110592 ONLINE
/mnt/ramdisk/book/sysaux01.dbf 2 SYSAUX 985661440 120320 AVAILABLE 2 YES 3.4360E+10 4194302 1280 984612864 120192 ONLINE
/mnt/ramdisk/book/system01.dbf 1 SYSTEM 796917760 97280 AVAILABLE 1 YES 3.4360E+10 4194302 1280 795869184 97152 SYSTEM
/mnt/ramdisk/book/example01.dbf 5 EXAMPLE 363069440 44320 AVAILABLE 5 YES 3.4360E+10 4194302 80 362020864 44192 ONLINE
/mnt/ramdisk/book/tea01.dbf 6 TEA 41943040 5120 AVAILABLE 6 YES 3.4360E+10 4194302 128 40894464 4992 ONLINE
/home/oracle/backup/sugar01.dbf 7 SUGAR 5242880 640 AVAILABLE 4 YES 3.4360E+10 4194302 160 4194304 512 ONLINE
7 rows selected.
--//註意看file_id=7行,RELATIVE_FNO=4.
總結:
1.通過dblink的方式實際上就是減少一步,使用expdp生成元數據的過程.直接通過dblink從源資料庫取.
2.另外實際上做這個測試我心裡有一個想法,比如不小心刪除不該刪除的數據,一般我們選擇在另外的機器恢復到出問題前的scn,然後再想
辦法處理,是否可以藉助傳輸表空間來恢複數據,就導入本庫來處理呢?還是沒想好,留待以後測試.
3.另外註意的問題,傳輸表空間建立的數據文件要手工傳輸數據文件到dg資料庫中.
4.還有一個問題要註意就是該方法僅僅導出了元數據，其它像FUNCTION,PACKAGE,PROCEDURE,SEQUENCE,SYNONYM,VIEW,USER,ROLE_GRANT,
SYSTEM_GRANT,trigger等都沒有導出.必須採用別的方式導入.以及臨時表(自己有1次這樣的經歷)^_^.
--//需要單獨在導入:
$ cat stage2.par
schemas=SCOTT
include=TABLE:"IN (SELECT table_name from dba_tables where owner='DAILY' and temporary='Y')"
include=FUNCTION,PACKAGE,PROCEDURE,SEQUENCE,SYNONYM,VIEW,USER,ROLE_GRANT,SYSTEM_GRANT,TRIGGER
dumpfile=stage2.dmp
