一次oracle无法open的解决

kiddwyl

这几天因为公司的复杂查询出现性能的问题（说实话本来就没设计好，数据行数都在1000W左右），所以需要进行调优，其实问题很简单，在系统中都使用了exists来进行表关联，
当然是不行的，我利用了表内联来解决问题
恩，结果还算不错，从跑不出来到了第一次硬解析在10分钟以内，但是公司管理层还是希望请“大牛”来查看下（该说的我都说了，摆明了不相信我吗）
 
“大牛”来的第一天：
       一、调整过程：
              1.) 通过with语句进行调整语句
              2.) 调整分区表结构
           调整结果：
              1.) 返回数据太大，tempspace不够
              2.) 调整分区表结构
 
       二、最终结果：
              不了了之
 
“大牛”来的第二天：（因为有事，我不在，以下是听同事说的）
       一、调整过程：
              1.) 增加redo group
              2.) resetlogs
              3.) 增大SGA(ASSM)、PGA
           调整结果：
              1.) 数据库死机
 
       二、最终结果：
              无法open
 
当然了，事件是出了，事情还要做得，所以接下来由我来进行解决
 
配置说明：
OS: RH Linux AS 4
HD: 1 CPU、2GB MEM
DB: Oracle 10gR1
 
[oracle@crm73 ~]$ sqlplus /nolog
 
SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jan 16 13:27:53 2009
 
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
 
SQL> startup mount;
....
SQL> alter database open;
数据库就死在那里不动了....
 
查看alert_je01.log文件，看到以下问题：
Recovery of Online Redo Log: Thread 1 Group 3 Seq 11894 Reading mem 0
  Mem# 0 errs 0: /opt/oracle/oradata/je01/redo03.log
Thu Jan 15 13:52:06 2009
Warning: scanned uninitialized pool DEFAULT, setid=3, blksz=8192, 0
 
然后通过linux gdb进行进程dump，得到如下等待事件：
PX Deq: Test for msg
PX Deq: Par Recov Execute
free buffer waits
 

在LINUX下可以使用gdb，下面是一个例子：

[oracle@xty ~]$ ps -ef | grep LOCAL
......
[oracle@xty ~]$ gdb $ORACLE_HOME/bin/oracle 3765
......
(gdb) print ksudss(10)
[Switching to Thread -1219938624 (LWP 3765)]
$1 = 213658428
(gdb) detach
Detaching from program: /u01/app/oracle/product/10.1.0/db_1/bin/oracle, process 3765
(gdb) quit

根据“大牛”调整的步骤、trace、经验判断，很有可能问题出在redo和SGA上，根据推测做如下工作：
       一、调整/etc/sysctl.conf
              原本：kernel.shmmax = 4073741824
              调整：
kernel.shmmax = 1073741824
kernel.shmall = 1048576
kernel.shmmni = 2048
kernel.sem = 250 32000 100 128
 
       一、调整SGA：
              原本：sga_max_size=520M
              现在：sga_max_size=800M
              调整：alter system set sga_max_size=520M scope=spfile;
       二、调整PGA：
              原本：pga_aggregate_target=170M
              现在：pga_aggregate_target=400M
              调整：alter system set pga_aggregate_target=170M;
       三、删除redo group 3：
查看V$log，发现是当前日志
SQL> select group#,sequence#,archived,status from v$log;
 
GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
1 1 YES INACTIVE
2 2 YES INACTIVE
3 3 NO CURRENT
 
 
发现clear不成功
SQL> alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of thread 1
ORA-00312: online log 3 thread 1: /op/oracle/oradata/je01/REDO03.LOG
 
把数据库down掉
SQL>shutdown immediate
 
recover database until cancel
先选择auto，尽量恢复可以利用的归档日志，然后重新
recover database until cancel
这次输入cancel，完成不完全恢复，也就是说恢复两次
如：
SQL> recover database until cancel;
Auto
……
SQL> recover database until cancel;
Cancel;
 
在init.ora.4292008151251中加入如下参数
_allow_resetlogs_corruption=TRUE
 
以resetlogs方式打开数据库
SQL>alter database open resetlogs;
 
问题解决！
 
将pfile转换成spfile
先注释_allow_resetlogs_corruption=TRUE
Create spfile from pfile=’ /opt/oracle/admin/je01/pfile/init.ora.4292008151251’
 
SQL> show parameter spfile;
 
NAME          TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile            string      /opt/oracle/product/10g/dbs/spfileje01.ora
 
 
问题总结：
1、在没有充分了解OS的环境配置，就调整了SGA和PGA，导致scanned uninitialized pool DEFAULT
2、在处理过程使用了shutdown abort，导致buffer cache和redo不统一