10g升级至11g exp的问题解决

昨天升级数据库，从10.2.0.5.0升级到11.2.0.2.0.

按照预定的步骤很快就操作完了。升级完成后，开始跑一些应用和Job.有一个Job开始报错，Job是一个自动的同步job,中会有exp的动作，而且里面用到了consistent=y的选项，这样exp就大体如下：

exp xxxx/xxxx file=xxx.dmp tables=xxxx consistent=y

报错如下：

Export: Release 11.2.0.2.0 - Production on Mon Sep 23 16:43:12 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Produ

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in TH8TISASCII character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table xxxx 2201 rows exported

EXP-00008: ORACLE error 1466 encountered

ORA-01466: unable to read data - table definition has changed

Export terminated successfully with warnings.

1.初步的感觉是时间的问题，

最先想到的系统时间的问题

> hwclock

dateTue 24 Sep 2013 07:29:54 AM ICT -0.564711 seconds

> date

Tue Sep 24 07:29:54 ICT 2013

但是查询物理时间和系统时间，都没问题。顺便提一句，如果在9i等版本中出现这个问题，很可能是物理时间和系统时间不同步造成的。这在tom大师的帖子中也有印证。

<a href="http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:704225010478">http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:704225010478</a>

2.排除这个问题，可能是object级的时间问题

MOS上看到有可能是creation_date比系统时间还要晚，用如下的sql来排除

select to_char(created,'dd-mm-yyyy hh24:mi:ss')

"CREATION TIME", object_name, object_type, object_id

from dba_objects where created > sysdate;

但是我这个例子没有任何输出，所以这个问题应该不是这个原因。

3.我查询alert日志，发现这么一句，感觉很蹊跷

Mon Sep 23 15:58:26 2013

ORA-1466 (RO Tx began: 09/23/2013 08:58:25, Last DDL: 09/23/2013 11:14:16, Curr Time: 09/23/2013 08:58:26)

Mon Sep 23 15:58:38 2013

4.对于ORA-1466的解决方法，MOS上的一些建议是重建数据库，这也太狠了。

5.最后在TOM的帖子里找到了灵感，他是这么写的。

you mentioned out of sync clocks, that is what caught my eye on that note.

It could even be a TIMEZONE issue. The dedicated server you are running might have a different TZ

than the environment the export is running in. Consider:

[tkyte@xtkyte-pc tkyte]$ echo $TZ

[tkyte@xtkyte-pc tkyte]$ plus

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jun 6 12:53:04 2005

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

ops$tkyte@ORA9IR2> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) from dual;

TO_CHAR(SYSDATE,'DD-

--------------------

06-jun-2005 12:53:15

ops$tkyte@ORA9IR2> !

[tkyte@xtkyte-pc tkyte]$ export TZ=PST

[tkyte@xtkyte-pc tkyte]$ plus

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jun 6 16:53:23 2005

ops$tkyte@ORA9IR2> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' ) from dual;

06-jun-2005 16:53:33

One thing to check would be that the TZ of the export session is consistent with the rest of the

sessions.

排除了系统级的timezone问题，我觉得可能是db级的timezone问题。

最后发现升级timezone的时候没有把步骤做完。

SQL> select *from v$timezone_file;

FILENAME VERSION

-------------------- ----------

timezlrg_4.dat 4

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE

------------------------------ ------------------------------

DST_PRIMARY_TT_VERSION 4

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

最后给出完整的解决方法（升级timezone)

Timezone数据库层面的升级。

注意：该步骤是否执行是和Step 6中的检查结果相关的，只有当Timezone的版本小于14时，才需要执行该步骤。

主要参考：Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]

1）Timezone升级前的准备工作：

先检查一下当前的timezone版本：

conn / as sysdba

SELECT version FROM v$timezone_file;

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

一个典型的输出是：

PROPERTY_NAME VALUE

DST_PRIMARY_TT_VERSION 4

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

然后开始准备工作：

alter session set "_with_subquery"=materialize;

exec DBMS_DST.BEGIN_PREPARE(14);

接着检查准备状态：

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

ORDER BY PROPERTY_NAME;

DST_SECONDARY_TT_VERSION 14

DST_UPGRADE_STATE PREPARE

-- truncate logging tables if they exist.

TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

TRUNCATE TABLE sys.dst$affected_tables;

TRUNCATE TABLE sys.dst$error_table;

-- log affected data

set serveroutput on

BEGIN

DBMS_DST.FIND_AFFECTED_TABLES

(affected_tables => 'sys.dst$affected_tables',

log_errors => TRUE,

log_errors_table => 'sys.dst$error_table');

END;

下面的语句都不能有返回结果：

SELECT * FROM sys.dst$affected_tables;

SELECT * FROM sys.dst$error_table;

SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

-- end prepare window, the rows above will stay in those tables.

EXEC DBMS_DST.END_PREPARE;

-- check if this is ended

2）真正开始升级Timezone

shutdown immediate;

startup upgrade;

purge dba_recyclebin;

EXEC DBMS_DST.BEGIN_UPGRADE(14);

DST_PRIMARY_TT_VERSION 14

DST_SECONDARY_TT_VERSION 4

DST_UPGRADE_STATE UPGRADE

下面这条语句应该没有返回结果：

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

重启数据库：

shutdown immediate

startup

升级相关的table：

VAR numfail number

DBMS_DST.UPGRADE_DATABASE(:numfail,

parallel => TRUE,

log_errors_table => 'SYS.DST$ERROR_TABLE',

log_triggers_table => 'SYS.DST$TRIGGER_TABLE',

error_on_overlap_time => FALSE,

error_on_nonexisting_time => FALSE);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

如果没有错误，则结束升级：

VAR fail number

DBMS_DST.END_UPGRADE(:fail);

DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

最后一次检查：

典型输出是：

SELECT * FROM v$timezone_file;

FILENAME VERSION

timezlrg_14.dat 14

升级完成后，可以用如下的方式来进行验证

> exp prdrefwork/petrefwork file=a.dmp tables=csm_offer consistent=y

Export: Release 11.2.0.2.0 - Production on Tue Sep 24 07:25:24 2013

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

. . exporting table xxxxxx 2201 rows exported

Export terminated successfully without warnings.

这次就不会跑错了。大功告成。

10g升级至11g exp的问题解决

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述

Oracle 批量查询传入List 返回List