[20121128]传输表空间与scn.txt
今天测试如果使用传输表空间，数据文件的scn的变化。如果传输表空间的数据文件scn很大，传过来后数据库的scn是否同步到最大的情况。
测试机器A(10g)
SQL> select * from v$version where rownum
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SQL> select current_scn from v$database ;
CURRENT_SCN
-----------
3180115893
测试机器B(11g)
SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> select current_scn from v$database ;
CURRENT_SCN
-----------
3179998749
两者相差3180115893 - 3179998749 = 117144. 相差有点小，不过这两个数据都是测试库，基本没人使用,应该还是可以测试出来的。
1.首先测试是否可以传输表空间(10g)
SQL> exec dbms_tts.transport_set_check('LIS_BAK',TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations ;
no rows selected
SQL> alter tablespace lis_bak read only;
$ exp \"/ as sysdba\" tablespaces=LIS_BAK transport_tablespace=y file=lis_bak.dmp
Export: Release 10.2.0.4.0 - Production on Wed Nov 28 09:31:36 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace LIS_BAK ...
. exporting cluster definitions
. exporting table definitions
......
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
2.在11g上建立用户:
CREATE USER DBO
IDENTIFIED BY VALUES 'XXXXXXXX'
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for DBO
GRANT CONNECT TO DBO;
GRANT DBA TO DBO;
ALTER USER DBO DEFAULT ROLE ALL;
3.拷贝文件到B机器,并导入数据:
$ scp /data/orcl/lis_bak.dbf oracle11g@192.168.105.xx:/u01/app/oracle11g/oradata/test/
$ scp lis_bak.dmp oracle11g@192.168.105.xx:/home/oracle11g/test3
$ imp \'\/ as sysdba\' tablespaces=LIS_BAK transport_tablespace=y file=lis_bak.dmp datafiles=/u01/app/oracle11g/oradata/test/lis_bak.dbf
Import: Release 11.2.0.1.0 - Production on Wed Nov 28 11:00:14 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing DBO's objects into DBO
.....
. importing SYS's objects into SYS
Import terminated successfully without warnings.
SQL> select current_scn from v$database ;
CURRENT_SCN
-----------
3180117422
--可以发现现在scn同步到最大的数据文件，像dblink一样。
在11G上执行：
SQL> alter system checkpoint;
SQL> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change# FROM v$database
UNION
SELECT 'file in controlfile', NAME, checkpoint_change# FROM v$datafile WHERE NAME LIKE '%lis_bak%'
UNION
SELECT 'file header', NAME, checkpoint_change# FROM v$datafile_header WHERE NAME LIKE '%lis_bak%';
SCN location NAME CHECKPOINT_CHANGE#
------------------- ---------------------------------------- ------------------
controlfile SYSTEM checkpoint 3180123887
file header /data/orcl/lis_bak.dbf 3180116692
file in controlfile /data/orcl/lis_bak.dbf 3180116692
在测试机器A(10G)上执行：
SQL> alter system checkpoint;
SQL> SELECT 'controlfile' "SCN location", 'SYSTEM checkpoint' NAME, checkpoint_change# FROM v$database
UNION
SELECT 'file in controlfile', NAME, checkpoint_change# FROM v$datafile WHERE NAME LIKE '%lis_bak%'
UNION
SELECT 'file header', NAME, checkpoint_change# FROM v$datafile_header WHERE NAME LIKE '%lis_bak%';
SCN location NAME CHECKPOINT_CHANGE#
------------------- -------------------------------------------------- ------------------
controlfile SYSTEM checkpoint 3180117582
file header /u01/app/oracle11g/oradata/test/lis_bak.dbf 3180116692
file in controlfile /u01/app/oracle11g/oradata/test/lis_bak.dbf 3180116692
使用logminer看也能发现存在大的跳跃：
SQL> SELECT SCN, TIMESTAMP, sql_redo FROM v$logmnr_contents WHERE SCN BETWEEN 3179999383 AND 3180116696 ORDER BY 1;
SCN TIMESTAMP SQL_REDO
---------- ------------------- -----------------------------------------
3179999383 2012-11-28 11:00:16 ALTER SEQUENCE sys.idgen1$ INCREMENT BY 50;
3179999385 2012-11-28 11:00:16 commit;
3180116693 2012-11-28 11:00:16 set transaction read write;
3180116693 2012-11-28 11:00:16 update "SYS"."OBJ$" set "OBJ#" = '1', "DATAOBJ#" = '267606', "TYPE#" = '0', ....
3180116694 2012-11-28 11:00:16 commit;
3180116696 2012-11-28 11:00:16 set transaction read write;
3180116696 2012-11-28 11:00:16 Unsupported
--从3179999385 到 3180116693(正好是数据文件记录的scn+1) 存在大的跳跃.