[20121128]传输表空间与scn.txt

[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) 存在大的跳跃.