分类目录归档：Oracle备份恢复

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

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

EXP-00091: Exporting questionable statistics.

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

一.出现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

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

今天接到测试报告，他的客户端不能导出数据库
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.失误总结
自己的自以为是让他忙碌了几个小时，深表歉意。人家把你当做专家，你就应该对人家负责任。用认真的心态去帮别人解决问题，不要以为自己就要离开，用种不负责任的态度去处理事情，更不要以为这些东西，自己已经很懂了，不认真对待。

发表在 逻辑备份/恢复 | 2 条评论