问题描述
使用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.
参考链接：
