标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-600 kcratr_nab_less_than_odr ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle碎片 OSD-04016 YOUR FILES ARE ENCRYPTED 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,885)
- DB2 (22)
- MySQL (82)
- Oracle (1,710)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (73)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (663)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 恢复案例分享:win丢失数据文件恢复
- 记录19c ASM磁盘掉线触发ORA-59048故障处理
- aix平台Oracle数据库由于断电无法启动故障处理
- 断电引起ORA-600 3005故障处理
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁(RUs+OJVM)-19.33
- 由于con$.con#值达到极限,触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
分类目录归档: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
手动提交分布式事务一例
一.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错误。
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 可用字节

加我微信(17813235971)
加我QQ(107644445)

