本文共 7925 字,大约阅读时间需要 26 分钟。
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
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的时候没有把步骤做完。
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数据库层面的升级。
注意:该步骤是否执行是和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 FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
一个典型的输出是:
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; / 下面的语句都不能有返回结果: 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
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;
一个典型的输出是:
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-773275/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-773275/