关于 Oracle 启动卡住的问题

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1084 天前的主题，其中的信息可能已经有所发展或是发生改变。

操作步骤

今天发现数据库查询返回有点慢，就想重启下 Oracle 数据库。操作如下：
1.shutdown immediate ；

但是报错：ORA-24324: service handle not initialized

2.使用了 shutdown abort ，然后 startup ，就卡住了，数据库并没有打开

Total System Global Area 3.4206E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            1.5771E+10 bytes
Database Buffers         1.8321E+10 bytes
Redo Buffers              112783360 bytes
Database mounted.

日志内容如下：

TNS-12535: TNS:operation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.XXX.XXX)(PORT=53268))
WARNING: inbound connection timed out (ORA-3136)
Sun Dec 05 12:51:52 2021
Completed redo scan
 read 9215214 KB redo, 232373 data blocks need recovery
Sun Dec 05 12:52:15 2021
Started redo application at
 Thread 1: logseq 209283, block 1662614
Recovery of Online Redo Log: Thread 1 Group 1 Seq 209283 Reading mem 0
  Mem# 0: /opt/oracle/oradata/orcl/onlinelog/redo01_01_log.dbf
Recovery of Online Redo Log: Thread 1 Group 3 Seq 209284 Reading mem 0
  Mem# 0: /opt/oracle/oradata/orcl/onlinelog/redo03_01_log.dbf
Recovery of Online Redo Log: Thread 1 Group 4 Seq 209285 Reading mem 0
  Mem# 0: /opt/oracle/oradata/orcl/onlinelog/redo04_01_log.dbf
Recovery of Online Redo Log: Thread 1 Group 5 Seq 209286 Reading mem 0
  Mem# 0: /opt/oracle/oradata/orcl/onlinelog/redo05_01_log.dbf
Recovery of Online Redo Log: Thread 1 Group 6 Seq 209287 Reading mem 0
  Mem# 0: /opt/oracle/oradata/orcl/onlinelog/redo06_01_log.dbf
Recovery of Online Redo Log: Thread 1 Group 7 Seq 209288 Reading mem 0
  Mem# 0: /opt/oracle/oradata/orcl/onlinelog/redo07_01_log.dbf
Recovery of Online Redo Log: Thread 1 Group 8 Seq 209289 Reading mem 0
  Mem# 0: /opt/oracle/oradata/orcl/onlinelog/redo08_01_log.dbf
Recovery of Online Redo Log: Thread 1 Group 9 Seq 209290 Reading mem 0
  Mem# 0: /opt/oracle/oradata/orcl/onlinelog/redo09_01_log.dbf
Sun Dec 05 12:52:28 2021
Completed redo application of 1796.76MB

尝试解决方法

增加日志文件

	ALTER DATABASE ADD LOGFILE GROUP 7 ('/opt/oracle/oradata/orcl/onlinelog/redo07_01_log.dbf') SIZE 2000M;
	ALTER DATABASE ADD LOGFILE GROUP 8 ('/opt/oracle/oradata/orcl/onlinelog/redo08_01_log.dbf') SIZE 2000M;
	ALTER DATABASE ADD LOGFILE GROUP 9 ('/opt/oracle/oradata/orcl/onlinelog/redo09_01_log.dbf') SIZE 2000M;

2.使用三次 ALTER SYSTEM SWITCH LOGFILE;命令，并 SELECT GROUP#,MEMBERS,BYTES/1024/1024,STATUS FROM V$LOG;查询：

1	1	2048	ACTIVE  
2	1	2048	ACTIVE  
3	1	2048	ACTIVE  
4	1	2048	ACTIVE  
5	1	2048	ACTIVE  
6	1	2048	ACTIVE  
7	1	2048	ACTIVE  
8	1	2048	ACTIVE  
9	1	2048	CURRENT

同时做了删除日志组操作

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;

只有 group 2 drop 成功 group1 和 group 3 未执行成功，错误如下：

log 1 needed for crash recovery of instance

然后重复执行 操作步骤 2 ，结果相同

so 问题没解决，解决办法都是遇见问题查找 Google 百度

请教各位:

1.这种情况该如何正常启动呢，我不熟悉数据库，只会简单的 select+根据错误百度 Google ，有时候还操作不对，根本原因不了解逻辑
2.为什么正常运行的数据库会出现这种情况，突然变慢，是开归档的问题吗？

望各位大佬不吝赐教谢谢

1 条回复

zerone0086

2021-12-05 17:57:20 +08:00

再经过了漫长的等待后，数据库终于 open
1.我趁机又重复了 ALTER SYSTEM SWITCH LOGFILE 命令，
2.然后原来不能删除的的我使用了 ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1; 并 drop
3.删除原有的日志组，并重建