分类目录归档:ORA-xxxxx

Getting ORA-01476 during execution of DBMS_STATS.GATHER_SCHEMA_STATS

alert日志

Fri Jan 27 22:00:09 2012
GATHER_STATS_JOB encountered errors.  Check the trace file.
Fri Jan 27 22:00:09 2012
Errors in file /oracle10/admin/ocs/bdump/ocs1_j001_29138.trc:
ORA-01476: divisor is equal to zero

trace内容

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle10/app/product/db/10.2.0
System name:    HP-UX
Node name:      ocsdb1
Release:        B.11.23
Version:        U
Machine:        ia64
Instance name: ocs1
Redo thread mounted by this instance: 1
Oracle process number: 60
Unix process pid: 29138, image: oracle@ocsdb1 (J001)

*** ACTION NAME:(GATHER_STATS_JOB) 2012-01-27 22:00:09.308
*** MODULE NAME:(DBMS_SCHEDULER) 2012-01-27 22:00:09.308
*** SERVICE NAME:(SYS$USERS) 2012-01-27 22:00:09.308
*** SESSION ID:(988.31342) 2012-01-27 22:00:09.307
ORA-01476: divisor is equal to zero
*** 2012-01-27 22:00:09.417
GATHER_STATS_JOB: GATHER_TABLE_STATS('"OCS_SM"','"HLP_SMS_SEND"','""', ...)
ORA-01476: divisor is equal to zero

错误原因
oracle unpublished Bug 5645718

解决方法
1.Setting event 38041 at level 16

sql> connect / as sysdba
sql> alter system set events '38041 trace name context forever, level 16';

2.Patch 6319761

发表在 ORA-xxxxx | 标签为 | 2 条评论

手动提交分布式事务一例

一.alert文件中出现了很多类此记录

Fri Feb 10 05:25:01 2012
Errors in file /tmp/recover/ahcx216_reco_7956.trc:
ORA-12154: TNS:could not resolve service name
Fri Feb 10 05:25:01 2012
Errors in file /tmp/recover/ahcx216_reco_7956.trc:
ORA-12154: TNS:could not resolve service name

这里可以看出来两个信息:
1.出错的进程是rec0进程,而该进程的作用是解决分布式事务失败后遗留问题(事务提交或者回滚)
2.错误信息是tns不能被解析

二.查看trace文件

/tmp/recover/ahcx216_reco_7956.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0/db_1
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.9-89.0.0.0.1.ELhugemem
Version:        #1 SMP Tue May 19 04:38:38 EDT 2009
Machine:        i686
Instance name: ahcx216
Redo thread mounted by this instance: 1
Oracle process number: 7
Unix process pid: 7956, image: oracle@localhost.localdomain (RECO)


*** SESSION ID:(6.1) 2012-02-10 04:58:24.886
*** 2012-02-10 04:58:24.886
ERROR, tran=6.1.712757, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name
………………
*** 2012-02-10 05:25:01.580
ERROR, tran=6.1.712757, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name
*** 2012-02-10 05:25:01.627
ERROR, tran=12.19.99059, session#=1, ose=0:
ORA-12154: TNS:could not resolve service name

通过这里我们可以看出事务id分别为12.19.99059和6.1.712757有问题

三.查看dba_2pc_pending视图

SQL> select local_tran_id,state,fail_time,retry_time from dba_2pc_pending;

LOCAL_TRAN_ID     STATE            FAIL_TIME           RETRY_TIME
-------------- ---------------- --------------       --------------
6.1.712757	collecting	2010/12/1 13:39:03   2012/2/10 5:38:52
12.19.99059	collecting	2010/12/1 15:56:26   2012/2/10 5:38:52

可以看出,果然有两个分布式事务在2010年12月1日出了问题(本库是一个问题库,在把库拉起来后发现该问题)
因为是异机恢复,而且间隔时间较长,很多tns的信息都已经不存在,所以需要手工提交分布式事务

四.手动提交事务

SQL> BEGIN
  2  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
  3  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
  4  END;
  5  /
BEGIN
*
第 1 行出现错误:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 2

SQL> alter session set "_smu_debug_mode"=4;

Session altered.

-------------------------------------------------------------
--设置UNDO_SUPPRESS_ERRORS=true也可以解决此问题
alter system set UNDO_SUPPRESS_ERRORS = TRUE;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('<事务ID>');
commit;
alter system set UNDO_SUPPRESS_ERRORS = false;
--------------------------------------------------------------

SQL> commit;

SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');

PL/SQL procedure successfully completed.


SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059'); END;

*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1
--第一个分布式事务处理后,未提交导致

SQL> commit;

Commit complete.

SQL> exec DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

五.补充说明
开始在另一个会话中,执行失败原因

SQL> alter session set "_smu_debug_mode"=4;

会话已更改。

SQL> commit;

提交完成。

SQL> BEGIN
  2  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('6.1.712757');
  3  DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('12.19.99059');
  4  END;
  5  /
BEGIN
*
第 1 行出现错误:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 3

因为这里的begin end中包含了两个事务的清理,在清理完第一个事务之后,需要提交才能够清理第二个,这里因为没有提交,导致ORA-01453错误。

发表在 ORA-xxxxx | 标签为 | 评论关闭

sqlplus版本不正确,导致ORA-12560

sqlplus登录数据库报ORA-12560

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Feb 14 23:33:31 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Users\XIFENFEI>sqlplus -v

SQL*Plus: Release 11.2.0.2.0 Production

在这里发现sqlplus的版本信息不正确,我的数据库安装的11.2.0.1,这么怎么显示sqlplus是11.2.0.2,然后进入%ORACLE_HOME%/bin目录下面执行sqlplus,登录成功

C:\Users\XIFENFEI>cd e:\oracle\11_2_0\BIN

e:\oracle\11_2_0\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 2月 14 23:44:38 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL>

出现了这样的问题,考虑应该是环境变量设置不当导致,查看环境变量发现下面一部分

E:\oracle\timesten\bin;E:\oracle\timesten\ttoracle_home\instantclient_11_2;e:\oracle\11_2_0\bin;

问题的答案就是在这里,因为我的电脑是先安装Oracle,前几天安装了TimesTen,导致环境变量发生了改变,使得我在默认情况下使用sqlplus的时候,自动调用了TimesTen中的sqlplus导致

C:\Users\XIFENFEI>cd E:\oracle\timesten\ttoracle_home\instantclient_11_2

E:\oracle\timesten\ttoracle_home\instantclient_11_2>dir sqlplus*
 驱动器 E 中的卷没有标签。
 卷的序列号是 38D0-2A35

 E:\oracle\timesten\ttoracle_home\instantclient_11_2 的目录

2010/11/19  00:50           655,360 sqlplus.exe
2011/11/08  04:12            84,524 sqlplus.sym
2011/11/08  04:12               554 SQLPLUS_README
               3 个文件        740,438 字节
               0 个目录  9,825,832,960 可用字节
发表在 ORA-xxxxx | 标签为 | 评论关闭