ORACLE 10.2.0.4 table统计信息异常丢失

最新推荐文章于 2022-07-27 12:49:00 发布

cuilingchai0891

最新推荐文章于 2022-07-27 12:49:00 发布

阅读量465

点赞数

文章标签：数据库操作系统系统架构

以下是一个RAC数据库Oracle 10.2.0.4因为 Bug 8318020,导致一个拥有近6千多万笔数据的表T1的统计信息异常丢失—查看数据字典表视图如下:

SQL>select num_rows,sample_size,last_analyzed from user_tables

2 where table_name='R_DETAIL_T'；

no rows selected

SQL>select num_rows,sample_size,last_analyzed from user_indexes

2 where table_name='R_DETAIL_T'；

no rows selected

进而导致此系统资源耗尽，使得数据库发生ORA-29740: evicted by member , group incarnation错误，一个节点重启；因为此RAC数据库和其它3个数据库通过DB LINK实现分布式事务处理，它发生异常使得其余三个数据库出现in-doubt distributed transaction,查看dba_2pc_pending和dba_2pc_neighbors视图发现存在很多悬而未决分布式事务且在dba_2pc_pending中state栏位为Collecting状态(在官方文档中解释This category normally applies only to the global coordinator or local coordinators. The node is currently collecting information from other database servers before it can decide whether it can prepare.), 严重的是悬而未决分布式事务导致其余三个数据库无法正常提供服务，客户端应用程式连接报ORA-12514错误。

在这里有必要简略讲一讲分布式事务，也就是在一个事务中可以连接更新多个数据库的数据，同时为了保证全局数据的一致性，通过2pc协议采用两阶段提交(Two-Phase Commit)，两阶段提交保证分布事物全部提交或全部回滚；对处于in-doubt distributed transaction的事务，通常数据库通过视图经由 RECO 进程对异常分布式事务做出相应的处理；如果事务处于悬而未决状态，就需要DBA及时介入来处理，根据事务在dba_2pc_pending视图中state栏位的状态信息(Prepared,Collecting, Committed, Forced Commit, Forced termination (rollback))手动采用以下SQL来处理:

SQL>Commit force ‘LOCAL_TRAN_ID’;

SQL>Commit force ‘GLOBAL_TRAN_ID’;

清除本地数据库中IN_DOUBT状态分布事物的数据字典信息，采用如下数据库系统包：

SQL>EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(' LOCAL_TRAN_ID ');

处理2pc问题官方文档讲很详细，网络上也讨论很多，这里不再赘述。

具体案例处理分析

2010年10月15号早晨6:40左右接到应用开发人员电话,说是程式连接数据库报ORA-12514错误，此数据库dba是双节点RAC架构OS为Linux 4.6，Oracle版本为10.2.0.4，我的第一反应是网络有问题或是数据库本身监听出现了问题，于是立刻让应用系统维护人员切换连接节点，但是切换完成后维护人员反应还是报ORA-12514错误；于是，我怀疑难道是RAC发生了脑裂(split-brain)一个节点实例重启了，正在我思考的时候，维护人员又打来电话说是OK了，初步断定应该是节点重启了！当我准备坐车去公司时,又有维护人员打来电话说是程式连接数据库报ORA-12514错误，此数据库dbb是双节点RAC架构OS为Linux 4.4，Oracle版本为10.2.0.1，数据库dba和dbb通过DB LINK实现分布式事务处理，难道是db2也发生了实例重启了，于是我立刻坐车赶去公司。

7:20我到达公司，连接dbb的业务还是没有OK，问题复杂了！没过几分钟又有一个维护人员打来电话，和上边两位反应的问题一样，只不过是应用系统连接的是dbc, 双节点RAC架构OS为Linux 4.4，Oracle版本为10.2.0.1;第四位维护人员带来电话，反应同样问题，dbd系统架构OS为Linux 4.6，Oracle版本为10.2.0.4，看来问题更复杂了… …

具体处理步骤

因为是生产系统，业务非常紧急；先找到问题点和解决方法，然后再找导致问题的根本原因。

一，dbb RAC数据库系统异常处理

首先，登陆到dbb RAC系统的一个实例节点，查看告警日志：

Fri Oct 15 06:28:36 2010

Error 18 trapped in 2PC on transaction 85.38.656940. Cleaning up.

Error stack returned to user:

ORA-00018: 超過階段作業數目上限

ORA-02063: 在 line 之前, 自 dba

ORA-02063: 在 2 lines 之前, 自 dba

Fri Oct 15 06:28:36 2010

Errors in file /opt/oracle/admin/dbb/bdump/dbb1_j000_17242.trc:

ORA-12012: 自動執行工作 402 時發生錯誤

ORA-00018: 超過階段作業數目上限

ORA-02063: 在 line 之前, 自 dba

ORA-06512: 在 "BWB02.SMTSUBMIT_TRANSNBAIO", line 29

ORA-02063: 在 2 lines 之前, 自 dba

ORA-06512: 在 line 1

看来是因为无法连接dba RAC数据库，进而导致出现2PC问题，登陆dba rac系统的一个节点实例，查看警告日志：

Fri Oct 15 06:22:45 2010

IPC Send timeout detected. Receiver ospid 30663

Fri Oct 15 06:23:04 2010

Errors in file /opt/app/oracle/admin/dba/udump/dl1_ora_30663.trc:

Fri Oct 15 06:23:06 2010

ksvcreate: Process(q002) creation failed

Fri Oct 15 06:23:06 2010

IPC Send timeout detected. Receiver ospid 30206

Fri Oct 15 06:23:06 2010

Errors in file /opt/app/oracle/admin/dba/bdump/dl1_lmd0_30206.trc:

Fri Oct 15 06:23:06 2010

Trace dumping is performing id=[cdmp_20101015061627]

Fri Oct 15 06:24:56 2010

Errors in file /opt/app/oracle/admin/dba/bdump/dl1_lmon_30204.trc:

ORA-29740: evicted by member 1, group incarnation 6

确实如怀疑，dba RAC发生了重启，业务紧急先处理dbb RAC无法连接问题，在回过头来找重启原因。

登陆到dbb中，查看gv$locked_object视图，发现存在很多阻塞，然后查看等待事件：

SQL>select inst_id,event,count(*) from gv$session_wait

2 group by inst_id,event

3 order by count(*) desc;

发现free global transaction table entry（分布式数据库中会话等待一个全局事务槽）等待事件居首,做出AWR报告，查看Top 5 Timed Events如下：

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
free global transaction table entry	11,538	11,273	977	72.5	Other
CPU time		7,911		50.9
latch: shared pool	127,906	4,919	38	31.7	Concurrency
latch: library cache	216,902	2,520	12	16.2	Concurrency
latch free	3,232	294	91	1.9	Other

看来是分布式事务出现了问题，查看dba_2pc_pending和dba_2pc_neighbors视图，发现存在很多悬而未决事件，且state栏位为collecting状态；接下来在gv$locked_object视图中找到找到对应session_id，根据inst_id列登陆对应节点实例以session_id信息查询v$session视图，找到对应的进程id,在OS下kill掉。

按一下方式查找,例如

SQL>select inst_id,object_id,session_id,oracle_username,os_user_name

2 from gv$locked_object;

INST_ID OBJECT_ID SESSION_ID ORACLE_USERNAME

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

1 73396 569 BWB02

2 68228 197 BWB01

2 74327 197 BWB01

2 68140 197 BWB01

登陆第一个节点实例

$sqlplus / as sysdate

SQL> select sid,serial#,username,process,program from v$session

2 Where sid=’ 569’;

SID SERIAL# USERNAME PROCESS PROGRAM

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

569 27210 BWB02 9665 oracle@bwb/1 (J001)

看来如警告日志中显示，JOB 402 ,(BWB02.SMTSUBMIT_TRANSNBAIO)出现2pc错误后处于僵死状态，首先将JOB 402至于BROKEN状态以避免JOB再次运行：

SQL> EXEC DBMS_JOB.BROKEN(402, TRUE)

PL/SQL procedure successfully completed.

SQL> COMMIT;

在OS下杀掉对应的进程

$kill -9 9665

并检查连接dba的DB LINK是否OK,确定无问题后开启job 402,

SQL> EXEC DBMS_JOB.BROKEN(402, FALSE)

PL/SQL procedure successfully completed.

SQL> COMMIT;

按此步骤处理如上其余阻塞事务。处理完后开发人员反应连接OK, 但是观察dba_2pc_pending视图，发现还存在悬而未决事务，且state栏位状态为collecting,采取如下动作：

SQL>EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(' LOCAL_TRAN_ID ');

至此，dbb处理完成

二，dbc RAC数据库系统异常处理

首先，登陆到dbc RAC系统的一个实例节点，查看告警日志：

Fri Oct 15 06:29:04 2010

Error 18 trapped in 2PC on transaction 19.40.1502227. Cleaning up.

Error stack returned to user:

ORA-00018: maximum number of sessions exceeded

ORA-02063: preceding line from dba

Fri Oct 15 06:30:50 2010

Error 18 trapped in 2PC on transaction 99.5.207398. Cleaning up.

Error stack returned to user:

ORA-00018: 超過階段作業數目上限

ORA-02063: 在 line 之前, 自 dba

Fri Oct 15 06:31:21 2010

Error 18 trapped in 2PC on transaction 63.8.931355. Cleaning up.

Error stack returned to user:

ORA-02050: 交易 63.8.931355 被倒回, 有些遠端 DB 可能有問題

ORA-00018: 超過階段作業數目上限

ORA-02063: 在 line 之前, 自 dba

Fri Oct 15 06:31:21 2010

DISTRIB TRAN DBC.REGRESS.RDBMS.DEV.US.ORACLE.COM.b59aaf3c.63.8.931355

is local tran 63.8.931355 (hex=3f.08.e361b)

insert pending collecting tran, scn=9292277044984 (hex=873.86df56f8)

看来和dbb RAC发生的异常状况类似,查看gv$locked_object，dba_2pc_pending等视图，同样出现很多阻塞与悬而未决事务；此RAC数据库业务非常紧急重要，查看dba_2pc_pending视图存在481个悬而未决事务，决定重启数据库。

$ srvctl stop database –d dbc

$ srvctl start database –d dbc

$ srvctl start service –d dbc

15分钟后数据库重启OK，业务连接正常；查看视图，异常事务消失。

三，dbd 单实例数据库系统异常处理

首先，登陆到dbd数据库的实例，查看告警日志

Fri Oct 15 06:39:24 2010

DISTRIB TRAN DBB.REGRESS.RDBMS.DEV.US.ORACLE.COM.e144a0ad.59.21.742902

is local tran 33.17.40030 (hex=21.11.9c5e)

insert pending prepared tran, scn=9292281233495 (hex=873.871f4057)

看来是同样的原因，因为分布式事务发生了异常,处理动作如上。

四，问题反复，重启dbb RAC数据库

以上异常处理完成已接近8:30，突然又接到电话反映，连接dba RAC数据库的业务程式反映很慢，于是查看登陆一个节点实例查看相关视图，发现gv$locked_object中存在很多阻塞，且dba_2pc_pending中存在4个悬而未决事务，根据session_id查看v$session发现是远端dbb数据库调用.

登陆dbb查看异常，发现又出现了很多悬而未决事务，看来刚才未能彻底解决问题，决定立即重启数据库。

重启dbb后异常消失，此时已经9点钟了。

五，可怕的问题有出现了

突然又接到电话，反映连接dba RAC数据库的业务程式反映很慢，客户以无法忍受。

查看dba操作系统资源，发现CPU闲置资源不到10%，使用OEM查看检视ADDM执行，如下：

查看等待事件，发现如下SQL消耗大量资源：

SELECT "PRODUCT_SN","DATA_TIME" FROM "BWB02"."R_DETAIL_T" "SN"

查看执行计划：

从执行计划中可以看到，返回行Rows 68M而Cost已经达到97；此SQL已独占了CPU资源，其它查询将排队等待，在$vmstat 1 5查询发现r列排队已经达到21；在AWR报表中得到以下信息：

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
PX Deq Credit: send blkd	12,326,536	108,805	9	38.3	Other
direct path write temp	458,934	47,604	104	16.7	User I/O
enq: TS - contention	50,507	22,460	445	7.9	Other
enq: SS - contention	43,731	19,193	439	6.7	Configuration
direct path read temp	150,849	17,966	119	6.3	User I/O

同时数据库DB Time/Elapsed=4,740.43/119.14=39.79,系统中出现了严重消耗

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	4765	15-Oct-10 08:01:01	490	5.0
End Snap:	4767	15-Oct-10 10:00:09	406	6.9
Elapsed:		119.14 (mins)
DB Time:		4,740.43 (mins)

其中，PX Deq Credit: send blkd位并行查询等待事件，根据SQL_ID 73vyug8t39064，查询v$session视图发现开启了284个并行session连接,找到对应得OS系统进程，$ kill -9 spid;之后没过多久又出现等待和251个并行session连接；此SQL是经过截取的且通过DB LINK远端访问本地表(查看v$session program栏位显示oracleDBC2@dbc2 (TNS V1-V3)), 登陆dbc RAC第二个节点实例，查看v$sql视图:

SQL> select service,module from v$sql

2 where sql_text like ‘%r_detail_t@dbb sd%’;

SQL_TEXT SERVICE MODULE

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

tsrac2 JDBC Thin Client

… …

发现都是通过JDBC连接查询的，同时WEB开发人员打来紧急电话说是他们的信息报表跑不出来，立刻让WEB开发人员将应用暂时停止，随之dbb RAC生产系统恢复正常。接下来找寻根本原因，咨询WEB开发人员得知未对应用进行过改动，难道是SQL解析器出现问题了或是统计失效了？

查看user_tables和user_indexes视图，发现统计信息丢失了！！！

SQL>select num_rows,sample_size,last_analyzed from user_tables

2 where table_name='R_DETAIL_T'；

no rows selected

SQL>select num_rows,sample_size,last_analyzed from user_indexes

2 where table_name='R_DETAIL_T'；

no rows selected

表R_DETIAL_T是一个分区表，且INDEX_DETAIL_TIME是分区索引，利用DBMS_STATS包进行统计：

declare

begin

dbms_stats.gather_table_stats('BWB02','R_DETAIL_T',NULL,NULL,FALSE,'FOR ALL COLUMNS SIZE 1',5,'DEFAULT',FALSE,NULL,NULL,NULL);

dbms_stats.gather_index_stats( 'BWB02', 'INDEX_DETAIL_TIME', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

dbms_stats.gather_index_stats( 'BWB02', 'INDEX_DETAIL_SN', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

exception

when others then

dbms_output.put_line('error when gathering table statistics');

end;

等待统计脚本执行完成后，现在已是2010/10/15 上午 11:48:37再次查看视图：

SQL>select num_rows,sample_size,last_analyzed from user_tables

2 where table_name='R_DETAIL_T'；

NUM_ROWS SAMPLE_SIZE LAST_ANALYZE

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

55183437 55183437 15-OCT-10

SQL>select num_rows,sample_size,last_analyzed from user_indexes

2 where table_name='R_DETAIL_T'；

NUM_ROWS SAMPLE_SIZE LAST_ANALYZE

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

55834544 180139 15-OCT-10

55321159 161967 15-OCT-10

现在统计信息回来了，打开OEM进行监控并告知WEB工程师打开应用，大约十分钟后，WEB工程师反馈，报表生成了观察OEM一切正常，OS下执行vmstat 1 5：

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----

r b swpd free buff cache si so bi bo in cs us sy id wa

1 0 507528 1719376 307344 10809412 0 0 357 45 1 1 5 1 93 1

2 0 507528 1719504 307352 10809404 0 0 206 669 3280 6111 6 1 93 0

0 0 507528 1719696 307352 10809404 0 0 123 267 3001 5325 5 0 94 0

1 0 507528 1719696 307352 10809404 0 0 153 64 2617 4699 5 1 94 0

0 0 507528 1719440 307352 10809404 0 0 109 781 3409 6429 5 1 94 0

怎么统计信息就丢失了？是不是数据库版本(10.2.0.4)存在BUG，在MOS上查找发现匹配的信息ID 8318020.8，此BUG在10.2.0.5或是11.2.0.1上解决；至此，此次异常处理完成。

总结

Oracle发布的PSR在处理了前个版本bug，同时也会带来新的bug;真对新的bug，紧急情况下需静下心理清思路避免走了弯路找错方向；接下来，不断地揣测与尝试，找到解决方法。然后，搜寻Oracle官方是否有发布相对应的临时补丁或PSU, 进行升级！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13478741/viewspace-701697/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13478741/viewspace-701697/

cuilingchai0891

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫