V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
zerone0086
V2EX  ›  Oracle

关于 Oracle 启动卡住的问题

  •  
  •   zerone0086 · 2021-12-05 17:26:45 +08:00 · 906 次点击
    这是一个创建于 844 天前的主题,其中的信息可能已经有所发展或是发生改变。

    操作步骤

    今天发现数据库查询返回有点慢,就想重启下 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
    

    尝试解决方法

    1. 增加日志文件
    	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.为什么正常运行的数据库会出现这种情况,突然变慢,是开归档的问题吗?

    望各位大佬不吝赐教 谢谢

    zerone0086
        1
    zerone0086  
    OP
       2021-12-05 17:57:20 +08:00
    再经过了漫长的等待后,数据库终于 open
    1.我趁机又重复了 ALTER SYSTEM SWITCH LOGFILE 命令 ,
    2.然后原来不能删除的的 我使用了 ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1; 并 drop
    3.删除原有的日志组,并重建
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   4034 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 10:18 · PVG 18:18 · LAX 03:18 · JFK 06:18
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.