问题描述

使用shutdown immediate方式关闭数据库，由于关闭时间久就在数据库未关闭时CTRL+Z停止执行，退出用SQLPLUS重登陆，出现报错：ORA-01012: not logged on

[oracle@top132:/u01/app/oracle/diag/rdbms/topdh/topdh/trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 21 15:40:23 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected.
ERROR:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

分析过程

--查进程  进程还在
[root@top132:/root]$ ps -ef | grep ora_pmon
oracle    1730     1  0 Dec11 ?        00:20:11 ora_pmon_topdh

--登录数据库
[oracle@top132:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 21 16:20:29 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected.
ERROR:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0


idle 21-DEC-23> startup
ORA-01012: not logged on
idle 21-DEC-23> conn / as sysdba
Connected to an idle instance.
ERROR:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0


idle 21-DEC-23> conn topicis/oracle
ERROR:
ORA-01089: immediate shutdown in progress - no operations are permitted
Process ID: 0
Session ID: 0 Serial number: 0


Warning: You are no longer connected to ORACLE.

问题原因

关闭数据库是shutdown 后面可跟的参数有：
nomal ：所有连接都断开时才能关闭；
transactional ：等待事务结束后，主动断开连接；
immediate ：主动断开事务和连接
abort ：立刻关闭数据库，这个操作是危险的，不会同步数据，不触发检查点，回滚段直接清 空，相当于掉电，每次启动都要实例恢复。
数据库关闭很慢，直接Ctrl +c退出了sqlplus，造成oracle文件被lock，当再次登录时，操作失败，因为文件依然被锁定状态，导致报错ORA-01012: not logged on。

解决办法

找到dbw0进程并kill掉。

[oracle@top132:/home/oracle]$ ps -ef |grep ora_dbw0_
oracle    1750     1  0 Dec11 ?        00:07:50 ora_dbw0_topdh
oracle   29341 25300  0 16:33 pts/2    00:00:00 grep --color=auto ora_dbw0_
[oracle@top132:/home/oracle]$ kill -9 1750
[oracle@top132:/home/oracle]$ ps -ef |grep ora_dbw0_
oracle   29358 25300  0 16:33 pts/2    00:00:00 grep --color=auto ora_dbw0_
[oracle@top132:/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 21 16:33:35 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

idle 21-DEC-23> startup
ORACLE instance started.

Total System Global Area 1.0623E+10 bytes
Fixed Size		    2262568 bytes
Variable Size		 2348812760 bytes
Database Buffers	 8254390272 bytes
Redo Buffers		   17199104 bytes
Database mounted.
Database opened.

参考链接：