一条sql语句“导致”的数据库宕机问题及分析 (38天)

最近测试环境需要做一些变更,把测试环境切分成两套环境,存储空间也需要压缩压缩和整理。 unix组的人已经开始做空间划分了,然后我们需要在此基础上重建一套环境。 有些数据文件使用空间不大,所以准备压缩一下。 用了下面的sql语句,结果跑了十几秒中就抛了下面的错误。

SQL> set linesize 200
SQL> col name for a40
SQL> col resizecmd for a80
SQL> select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
  2         ceil(HWM * a.block_size)/1024/1024 ResizeTo,
  3         (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
  4         'alter database datafile '''||a.name||''' resize '||
  5         ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
  6  from v$datafile a,
  7       (select file_id,max(block_id+blocks-1) HWM
  8         from dba_extents
  9         group by file_id) b
 1	0  where a.file# = b.file_id(+)
 11  and (a.bytes - HWM *block_size)>0
order by 5     12  ;
order by 5
             *
ERROR at line 12:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/testPT1/oracle/TEST01/oratmp01/temp/ggs_data01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

再一查看数据库进程,都没有了,看来数据库是宕了。 我还想这条sql语句真是厉害,看看日志里面怎么说。 Tue Mar 25 22:04:19 2014 End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" Wed Mar 26 02:00:00 2014 Closing scheduler window Closing Resource Manager plan via scheduler window Clearing Resource Manager plan via parameter Mon Mar 31 10:14:55 2014 USER (ospid: 21846): terminating the instance due to error 472 Instance terminated by USER, pid = 21846 先把库重启了,看有没有什么问题。

SQL> startup nomount ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1.2827E+10 bytes Fixed Size 2253880 bytes Variable Size 4211084232 bytes Database Buffers 8589934592 bytes Redo Buffers 24096768 bytes SQL> alter database mount; Database altered. 当准备Open的时候,报了下面的错误。说有个数据文件丢失了。 SQL> alter database open; alter database open * ERROR at line 1: ORA-01157: cannot identify/lock data file 4 - see DBWR trace file ORA-01110: data file 4: '/testPT1/oracle/TEST01/oratmp01/temp/ggs_data01.dbf' 查看备份情况,是否有热备份之类的操作遗留。可以看到这个文件确实是损坏了或者被认为删除了。 SQL> select *from v$backup; FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 1.0583E+13 22-JAN-13 2 NOT ACTIVE 1.0583E+13 22-JAN-13 3 NOT ACTIVE 1.0583E+13 22-JAN-13 4 FILE NOT FOUND 0 ... 40 NOT ACTIVE 1.0583E+13 22-JAN-13 查看文件的路径。 1* select file#,name from v$datafile 4 /testPT1/oracle/TEST01/oratmp01/temp/ggs_data01.dbf 确认了下这个数据文件goldengate用,现在测试环境上还没有goldengate,可以删除,于是头脑一发热,就准备马上删了,结果报了错。 SQL> drop tablespace GGS_DATA including contents and datafiles cascade constraint; drop tablespace GGS_DATA including contents and datafiles cascade constraint * ERROR at line 1: ORA-01109: database not open 才反应过来数据库还在mount状态 先把数据文件offline了 SQL> alter database datafile '/testPT1/oracle/TEST01/oratmp01/temp/ggs_data01.dbf' offline; Database altered. 这时候再查看v$backup,那条记录就不复存在了。 FILE# STATUS CHANGE# TIME ---------- ------------------ ---------- --------- 1 NOT ACTIVE 1.0583E+13 22-JAN-13 2 NOT ACTIVE 1.0583E+13 22-JAN-13 3 NOT ACTIVE 1.0583E+13 22-JAN-13 5 NOT ACTIVE 1.0583E+13 22-JAN-13 6 NOT ACTIVE 1.0583E+13 22-JAN-13 7 NOT ACTIVE 1.0583E+13 22-JAN-13 8 NOT ACTIVE 1.0583E+13 22-JAN-13 9 NOT ACTIVE 1.0583E+13 22-JAN-13 10 NOT ACTIVE 1.0583E+13 22-JAN-13 11 NOT ACTIVE 1.0583E+13 22-JAN-13 12 NOT ACTIVE 1.0583E+13 22-JAN-13 .... 39 rows selected. 把数据库open起来。 SQL> alter database open; Database altered. 然后再删除,其实这个阶段也只是释放了句柄和更新了数据字典。 SQL> drop tablespace ggs_data including contents and datafiles cascade constraint; Tablespace dropped. 再次查看数据库进程是否正常。可以看到数据库已经正常了。 test01@ccbdbpt4:/opt/app/oracle/TEST01> ps -ef|grep smon test018420 5954 0 10:39 pts/2 00:00:00 grep smon test0110295 1 0 10:18 ? 00:00:01 ora_smon_TEST01 test01@ccbdbpt4:/opt/app/oracle/TEST01> sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 31 10:40:08 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "SYS" SQL> select status from v$instance; STATUS ------------ OPEN 我在另一个环境上碰到了类似的问题。 test01@ccbdbpt4:/opt/app/oracle/TEST01/test> sqlplus / as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 3 17:26:43 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SQL> select dbms_metadata.get_ddl('USER',u.username) from dba_users u WHERE USER NAME in('TEST'); ERROR: ORA-03113: end-of-file on communication channel Process ID: 4056 Session ID: 4159 Serial number: 15 no rows selected 查看日志,错误还是类似。 Mon Mar 31 11:47:47 2014 USER (ospid: 26945): terminating the instance due to error 472 Instance terminated by USER, pid = 26945 这个问题的总结如下 导致数据库宕机的原因不是因为sql语句,而是因为unix组做文件的操作中,导致数据库进程问题,数据库其实已经停了,但是我们仍然可以进行简单的操作。因为操作系统中句柄还在。 那个数据文件的丢失和unix组的人确认是认为的失误,他们在做空间切分的时候,没有停库。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-04-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

返璞归真:如何判断一个初始化参数是否来自默认设置

? 杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主 Oracle初...

2767
来自专栏Jerry的SAP技术分享

ABAP OPEN SQL里OPEN CURSOR和SELECT的比较

After the OPEN CURSOR statement, the database cursor is positioned in front of t...

3749
来自专栏杨建荣的学习笔记

关于dual表的破坏性测试(r3笔记第60天)

关于dual表的破坏性测试,既然是破坏性测试,就需要确定这个测试仅限于测试或者个人学习所用,可能有些sql看似极为简单,但是一旦运行就会导致整个业务系统崩溃。 ...

38113
来自专栏杨建荣的学习笔记

关于db link权限分配的苦旅(一) (r7笔记第42天)

昨天接到一个开发的需求,内容看起来非常简单。 申请数据库192.168.1.118:1522:TEST下用户APP_TE_FLOW_128赋予对表testore...

3366
来自专栏ASP.NET MVC5 后台权限管理系统

ASP.NET MVC5+EF6+EasyUI 后台管理系统(31)-MVC使用RDL报表

这次我们来演示MVC3怎么显示RDL报表,坑爹的微软把MVC升级到5都木有良好的支持报表,让MVC在某些领域趋于短板 我们只能通过一些方式来使用rdl报表。 R...

2675
来自专栏乐沙弥的世界

使用带dblink方式的datapump迁移Oracle 10g到11g

      对于从Oracle 10g下迁移数据库到Oracle 11g,除了使用RMAN方式之外,我们可以使用带dblink的datapump方式来实现基于逻...

591
来自专栏乐沙弥的世界

Heap size 80869K exceeds notification threshold (51200K)

      前阵子的alert日志获得了所需堆尺寸的大小超出指定阙值的提示,即Heap size 80869K exceeds notification thr...

943
来自专栏杨建荣的学习笔记

insert中启用错误日志的问题及分析(r2第10天)

在平时的工作中,有时候需要insert一批数据,这些数据可能是临时表,外部表,普通表,子查询等形式,类似下面的格式 insert into xxxx (sele...

2859
来自专栏杨建荣的学习笔记

执行计划变化导致CPU负载高的问题分析 (r8笔记第20天)

前几天碰到一个CPU负载较高的问题。从系统层面来看,情况不是很严重,但是从应用的角度来说,已经感觉到很慢了。因为前端的调用频率还是比较高。所以会把这个问题放大。...

2567
来自专栏数据和云

追本溯源:Oracle 只读表空间的探索实践

作者简介 ? 胡中豪 云和恩墨西区交付工程师,多年一线 DBA 经验,曾服务于运营商、电网、政府行业、银行等行业客户;擅长数据库故障处理、性能优化、实施升级 本...

2783

扫码关注云+社区