前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >10g升级至11g exp的问题解决(23天)

10g升级至11g exp的问题解决(23天)

作者头像
jeanron100
发布2018-03-13 17:46:43
1.2K0
发布2018-03-13 17:46:43
举报
文章被收录于专栏:杨建荣的学习笔记

昨天升级数据库,从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报错如下:

代码语言:javascript
复制
Export: Release 11.2.0.2.0 - Production on Mon Sep 23 16:43:12 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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.初步的感觉是时间的问题,最先想到的系统时间的问题> hwclockdateTue 24 Sep 2013 07:29:54 AM ICT -0.564711 seconds> dateTue Sep 24 07:29:54 ICT 2013但是查询物理时间和系统时间,都没问题。顺便提一句,如果在9i等版本中出现这个问题,很可能是物理时间和系统时间不同步造成的。这在tom大师的帖子中也有印证。http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:704225010478

2.排除这个问题,可能是object级的时间问题MOS上看到有可能是creation_date比系统时间还要晚,用如下的sql来排除select to_char(created,'dd-mm-yyyy hh24:mi:ss')"CREATION TIME", object_name, object_type, object_idfrom dba_objects where created > sysdate; 但是我这个例子没有任何输出,所以这个问题应该不是这个原因。3.我查询alert日志,发现这么一句,感觉很蹊跷

代码语言:javascript
复制
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的帖子里找到了灵感,他是这么写的。

代码语言:javascript
复制
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
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
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
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
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 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的时候没有把步骤做完。

代码语言:javascript
复制
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版本:

代码语言:javascript
复制

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;

一个典型的输出是:

代码语言:javascript
复制

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 FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; 一个典型的输出是:

代码语言:javascript
复制

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         4
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;
/

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

代码语言:javascript
复制

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
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

2)真正开始升级Timezone

代码语言:javascript
复制

conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on
purge dba_recyclebin;
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
alter session set "_with_subquery"=materialize;
EXEC DBMS_DST.BEGIN_UPGRADE(14);
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

一个典型的输出是:

代码语言:javascript
复制

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
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: alter session set "_with_subquery"=materialize; set serveroutput on VAR numfail number BEGIN DBMS_DST.UPGRADE_DATABASE(:numfail, parallel => TRUE, log_errors => 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); END; / 如果没有错误,则结束升级: VAR fail number BEGIN DBMS_DST.END_UPGRADE(:fail); DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail); END; / 最后一次检查: 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 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE SELECT * FROM v$timezone_file; FILENAME VERSION -------------------- ---------- timezlrg_14.dat 14

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

> exp prdrefwork/petrefwork file=a.dmp tables=csm_offer consistent=yExport: Release 11.2.0.2.0 - Production on Tue Sep 24 07:25:24 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in TH8TISASCII character set and UTF8 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table xxxxxx 2201 rows exportedExport terminated successfully without warnings.

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

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2014-03-26,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档