使用dblink导致scn发现改变

1.dblink目标端

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Jan 21 15:25:59 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER scn from dual;

       SCN
----------
  17454270

SQL> select file#,CHECKPOINT_CHANGE# scn from v$datafile;

     FILE#        SCN
---------- ----------
         1   17454265
         2   17454265
         3   17454265
         4   17454265
         5   17454265
         6   17454265
         7   17454265
         8   17454265
         9   17454265
        10   17454265
        11   17454265

     FILE#        SCN
---------- ----------
        13   17454265
        14   17454265

13 rows selected.

通过这里可以知道,该数据库目前的scn是小于18000000。

2.dblink操作

[oracle@node1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sat Jan 21 15:49:39 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER scn from dual;

       SCN
----------
  20359364

SQL> select dbms_flashback.GET_SYSTEM_CHANGE_NUMBER scn from dual@chf;

       SCN
----------
  20359367

通过这步操作发现目标端的scn发生改变,和原端库scn一致(因为先后关系,有小出入)

3.再次查看dblink目标端

SQL>  alter system checkpoint;

System altered.

SQL> select file#,CHECKPOINT_CHANGE# scn from v$datafile;

     FILE#        SCN
---------- ----------
         1   20359409
         2   20359409
         3   20359409
         4   20359409
         5   20359409
         6   20359409
         7   20359409
         8   20359409
         9   20359409
        10   20359409
        11   20359409

     FILE#        SCN
---------- ----------
        13   20359409
        14   20359409

13 rows selected.

通过checkpoint之后,发现目标库的scn已经发现改变(向前推荐),从而证明使用dblink,确实使得scn小的库和scn大的库一致。

人生中的十个不要等

一、不要等到想要得到爱时才学会付出

  人生就像一场戏,在等待中错过了美丽。正如歌中所唱:该出手时就出手。因为幸福就在你身边。当你付出时,爱以从你身边轻轻划过,留下的是悔恨、遗憾。
二、不要等到孤单时才想念起你的朋友

  什么是朋友?真正的朋友永远不会离弃你。人生得一知己足矣,孤独时、彷徨时,朋友是你最忠实的听众,他们没有怨言,他们有的只是一颗包容的心。
三、不要等到有了职位时才去努力工作

  有的人一生都在等自己如意的工作,戴到白发之时方诲自己执著地等待。因为世界真的很精彩,只要你肯努力,处处都有你满意的工作。
四、不要等到失败时才记起他人的忠告

  忠言逆耳利于行,良药苦口利于病。世人往往善于听信谗言,因为谗言总是美丽的,而忘了这句古话。待到自己失败时,一切如过眼云烟,烟消云散。
五、不要等到生病时才意识到生命脆弱

  生命真的很脆弱,一只蚂蚁可能死在你的脚下,只不过你没有觉察。可能你在生命的边缘徘徊,为什么不珍惜自己的生命?
六、不要等到分离时后悔没有珍惜感情

  为什么总是离别之后才懂得珍惜,因为人无完人,金无足赤。拥有一颗宽容的心,善待别人就等于善待自己。
七、不要等到有人赞赏你时才相信自己

  每个人都有自己的优点和长处,自信有时也是成功的钥匙。要等到别人的赞赏,恐怕已经太迟了,因为生命属于你只有一次,没有循环,没有往复。
八、不要等到别人指出才知道自己错了

  其实,勇于承认错误并没有人嘲笑你,反而得到别人的尊重。因为每个人都有错误,只不过有的人善于掩饰自己的错误,有的人勇于承认罢了。
九、不要等到腰缠万贯才准备帮助穷人

  助人为乐永远是一种美德,待到腰缠万贯之时,你不一定会快乐,因为你的施舍别人不一定接受。
十、不要等到临死时才发现要热爱生活

  生活真的很精彩,为什么要游戏人生。人生就像一条长河,永远没有尽头,没有止境。并不因为你的生老病死而改变。热爱生活就等于热爱

验证imp show参数

1.exp导出数据

[oracle@node1 ~]$ exp "'/ as sysdba'" owner=ECPUUM file=/tmp/ECPUUM.dmp log=/tmp/E.log

Export: Release 10.2.0.5.0 - Production on Wed Jan 18 16:32:34 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.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

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ECPUUM 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ECPUUM 
About to export ECPUUM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ECPUUM's tables via Conventional Path ...
. . exporting table         TAB_UUM_COMPANY_DETAIL        263 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

2.创建新用户

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 18 16:39:17 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> drop user xifenfei cascade;

User dropped.

SQL> create user xifenfei identified by xff; 

User created.

SQL> grant dba to xifenfei;

Grant succeeded.

SQL> exit

3.imp+show=y导入

[oracle@node1 ~]$ imp "'/ as sysdba'" fromuser=ECPUUM touser=xifenfei \
>file=/tmp/ECPUUM.dmp log=/tmp/ECPUUM.log show=y

Import: Release 11.2.0.3.0 - Production on Wed Jan 18 16:41:52 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing ECPUUM's objects into XIFENFEI
 "BEGIN  "
 "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
 "CURRENT_SCHEMA'), export_db_name=>'ECP', inst_scn=>'20152169');"
 "COMMIT; END;"
 "ALTER SESSION SET CURRENT_SCHEMA= "XIFENFEI""
 "CREATE SEQUENCE "SEQ_UUM_COMPANY_ID" MINVALUE 1 MAXVALUE 999999999999999999"
 "9999999 INCREMENT BY 1 START WITH 462 CACHE 20 NOORDER NOCYCLE"
 "CREATE GLOBAL TEMPORARY TABLE "HT_TAB_UUM_USERS" ("USER_ID" NUMBER(19, 0) N"
 "OT NULL ENABLE) ON COMMIT DELETE ROWS "           
 "CREATE TABLE "TAB_UUM_COMPANY_DETAIL" ("COMPANY_ID" NUMBER(7, 0) NOT NULL E"
 "NABLE, "COMPANY_NAMEALIAS" VARCHAR2(64) NOT NULL ENABLE, "COMPANY_TYPE" NUM"
 "BER, "COMPANY_COUNTRY" NUMBER, "COMPANY_PROVICE" NUMBER, "COMPANY_CITY" NUM"
 "BER, "COMPANY_AREA" NUMBER, "COMPANY_ADDRESS" VARCHAR2(256), "COMPANY_POSTC"
 "ODE" NUMBER(6, 0), "COMPANY_PHONE" VARCHAR2(13), "COMPANY_WEBSITE" VARCHAR2"
 "(128), "COMPANY_TRADEBIG" NUMBER, "COMPANY_SIZE" NUMBER, "COMPANY_PAYMENT" "
 "NUMBER, "COMPANY_EXTEND1" VARCHAR2(32), "COMPANY_EXTEND2" VARCHAR2(32), "CO"
 "MPANY_EXTEND3" VARCHAR2(32), "COMPANY_TRADESMALL" NUMBER, "COMPANY_REG_CAPI"
 "TAL" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL"
 " 65536 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABL"
 "ESPACE "TS_PUB_BASE" LOGGING NOCOMPRESS"
. . skipping table "TAB_UUM_COMPANY_DETAIL"           
 "CREATE UNIQUE INDEX "PK_TAB_UUM_COMPANY_DETAIL" ON "TAB_UUM_COMPANY_DETAIL""
 " ("COMPANY_ID" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 "
 "NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "
 ""TS_PUB_BASE" LOGGING"
 "ALTER SESSION SET CURRENT_SCHEMA= "XIFENFEI""
 "ALTER TABLE "TAB_UUM_COMPANY_DETAIL" ADD SUPPLEMENTAL LOG GROUP "GGS_TAB_UU"
 "M_COMPANY_51784" ("COMPANY_ID") ALWAYS"
 "ALTER TABLE "TAB_UUM_COMPANY_DETAIL" ADD SUPPLEMENTAL LOG GROUP "GGS_TAB_UU"
 "M_COMPANY_52725" ("COMPANY_ID") ALWAYS"
 "ALTER TABLE "TAB_UUM_COMPANY_DETAIL" ADD SUPPLEMENTAL LOG GROUP "GGS_TAB_UU"
 "M_COMPANY_52946" ("COMPANY_ID") ALWAYS"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_ID" IS  '企业ID'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_NAMEALIAS" IS  '公司名称"
 "简称'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_TYPE" IS  '公司类型:单选"
 ",选择:1 有限责任公司、2 股份有限公司、3 国有独资公司、4个人独资企业、5个体"
 "工商户、6合伙企业、7外商投资企业、8私营企业、9其他;'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_COUNTRY" IS  '公司所在地"
 ":从列表选择,分四项:国家、省份、地市、区县,其中国家一项默认值为:中国;'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_PROVICE" IS  '省'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_CITY" IS  '市'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_AREA" IS  '地区'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_ADDRESS" IS  '公司详细地"
 "址: 手工录入,100个汉字;'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_POSTCODE" IS  '邮编'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_PHONE" IS  '公司电话'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_WEBSITE" IS  '公司网址'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_SIZE" IS  '公司规模'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_PAYMENT" IS  '付款方式'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_EXTEND1" IS  '扩展字段1"
 "'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_EXTEND2" IS  '扩展字段1"
 "'"
 "COMMENT ON COLUMN "TAB_UUM_COMPANY_DETAIL"."COMPANY_EXTEND3" IS  '扩展字段3"
 "'"
. . skipping table "TAB_UUM_COMPANY_DETAIL"     
Import terminated successfully without warnings.

4.查询imp结果

[oracle@node1 ~]$ sqlplus xifenfei/xff

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 18 16:45:10 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select count(*) from TAB_UUM_COMPANY;
select count(*) from TAB_UUM_COMPANY
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from tab;

no rows selected

通过实验知道,show=y表示展示imp导入的时候,会执行的相关语句(不包括insert语句),而不会真正的在数据库中执行

EXP-00091: Exporting questionable statistics.

一.出现EXP-00091错误

[oracle@localhost tmp]$ exp "'/ as sysdba'" tables=testxuchao.TAB_XN_PY_DICT \
> file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log

Export: Release 9.2.0.4.0 - Production on Mon Jan 16 16:02:37 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TESTXUCHAO
. . exporting table                 TAB_XN_PY_DICT      25433 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

当环境变量和数据库的不一致,在导出统计信息时候,会报EXP-00091错误。

二.查看NLS相关参数

[oracle@localhost tmp]$ echo $NLS_LANG
AMERICAN

SQL>  col value for a30
SQL>  select parameter,value from nls_database_parameters 
   2  where parameter like '%CHARACTERSET%'; 

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               ZHS16GBK
NLS_NCHAR_CHARACTERSET         AL16UTF16

三.解决方法
1.设置NLS_LANG为NLS_CHARACTERSET

[oracle@localhost tmp]export NLS_LANG=american_america.ZHS16GBK
[oracle@localhost tmp]$ exp "'/ as sysdba'" tables=testxuchao.TAB_XN_PY_DICT \
> file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log

Export: Release 9.2.0.4.0 - Production on Mon Jan 16 16:08:10 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to TESTXUCHAO
. . exporting table                 TAB_XN_PY_DICT      25433 rows exported
Export terminated successfully without warnings.

2.使用statistics=none

[oracle@localhost tmp]$ exp "'/ as sysdba'" tables=testxuchao.TAB_XN_PY_DICT \
> file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log  statistics=none

Export: Release 9.2.0.4.0 - Production on Mon Jan 16 16:08:53 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to TESTXUCHAO
. . exporting table                 TAB_XN_PY_DICT      25433 rows exported
Export terminated successfully without warnings.

PLS-00302: component ‘SET_NO_OUTLINES’ must be declared

今天接到测试报告,他的客户端不能导出数据库
1.报错如下

Export started on 2012-1-16 15:30:05
D:\oracle\product\10.2.0\client_3\BIN\exp.exe parfile=C:\WINDOWS\Tmp\plsexp.par
EXP-00056: 遇到 ORACLE 错误 6550
ORA-06550: line 1, column 41:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: 导出终止失败
Export finished on 2012-1-16 15:30:07

2.服务器上导出

[oracle@localhost tmp]$ exp "'/ as sysdba'" owner=testxuchao \
> file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log

Export: Release 9.2.0.4.0 - Production on Mon Jan 16 15:36:38 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TESTXUCHAO 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TESTXUCHAO 
About to export TESTXUCHAO's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TESTXUCHAO's tables via Conventional Path ...
. . exporting table                    COMPANY_CON          0 rows exported
. . exporting table       SMP_VDD_OPERATIONS_TABLE          0 rows exported
. . exporting table        TAB_XN_NXT_CONSULTATION          0 rows exported
EXP-00091: Exporting questionable statistics.
……………………
EXP-00091: Exporting questionable statistics.
. . exporting table                      TAB_XN_PY          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                 TAB_XN_PY_DICT      25433 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

服务器上执行exp成功了,我很自然的说了句,是客户端的问题,然后和测试说,可以端重新装下再试试看(因为他装了两个客户端,我是怀疑变量等原因导致)。当他装好后,然后和我说,还是不行,我开始怀疑是版本的问题,然后对比了下今天他的报错,和数据库版本,发现果真是两边版本的问题(数据库服务器版本:9.2.0.4,客户端版本:10.2.0.1),解决问题,让他装一个低版本的客户端,再装个高版本的客户端,然后在根据导出数据的版本,选择不同版本的exp。

3.失误总结
自己的自以为是让他忙碌了几个小时,深表歉意。人家把你当做专家,你就应该对人家负责任。用认真的心态去帮别人解决问题,不要以为自己就要离开,用种不负责任的态度去处理事情,更不要以为这些东西,自己已经很懂了,不认真对待。