月归档：一月 2012

使用dblink导致scn发现改变

发表于 2012 年 1 月 21 日 由 惜分飞

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大的库一致。

发表在 Oracle | 评论关闭

人生中的十个不要等

发表于 2012 年 1 月 20 日 由 惜分飞

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

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

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

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

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

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

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

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

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

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

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

发表在 至理名言 | 一条评论

验证imp show参数

发表于 2012 年 1 月 18 日 由 惜分飞

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语句)，而不会真正的在数据库中执行

发表在 逻辑备份/恢复 | 评论关闭