ORA-01012: not logged on

问题描述

明明已经shutdown immediate一致性关库，但是查进程时发现oracle进程依然存在。sqlplus / as sysdba登录提示ORA-01012: not logged on报错，着重强调，服务器上就一个实例。

分析过程

查看进程

我通常会在shutdown immediate之后查下进程是否还在，头一回碰到进程还在的情况。

查看数据库日志

日志中已显示Completed: ALTER DATABASE CLOSE NORMAL

......
SMON: disabling tx recovery
SMON: disabling cache recovery
Fri May 17 17:48:45 2024
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 5879
Successful close of redo thread 1
Fri May 17 18:00:27 2024
Completed: ALTER DATABASE CLOSE NORMAL

尝试再次启动

[oracle@host-10-35-76-7 ~]$ export ORACLE_SID=topeocps
[oracle@host-10-35-76-7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri May 17 18:12:01 2024

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

Connected.
SQL> startup
ORA-01012: not logged on

查看资源

IPC资源未释放

--查看所有的IPC资源，包括共享内存，共享队列
[oracle@host-10-35-76-7 dbs]$ ipcs -a 

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch     status      
0x00000000 131075     oracle     640        134217728  14                      
0x00000000 163844     oracle     640        20132659200 14                      
0x6b2b0e34 196613     oracle     640        2097152    14                      

------ Semaphore Arrays --------
key        semid      owner      perms      nsems     
0x17d6a1bc 163842     oracle     640        250       
0x17d6a1bd 196611     oracle     640        250       
0x17d6a1be 229380     oracle     640        250       
0x17d6a1bf 262149     oracle     640        250       
0x17d6a1c0 294918     oracle     640        250       
0x17d6a1c1 327687     oracle     640        250       
0x17d6a1c2 360456     oracle     640        250       
0x17d6a1c3 393225     oracle     640        250       
0x17d6a1c4 425994     oracle     640        250       

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages   

解决办法

shutdown abort停止库

群里有大佬引导可用shutdown abort停止库，起初有些担心毕竟是生产库，还未开归档，没有备份。万一库启动不起来咋弄，但是也没更好的办法了，只能大胆的尝试了shutdown abort停止库

SQL> shutdown abort
ORACLE instance shut down.

启动数据库

万幸启动数据库成功。

SQL> startup      
ORACLE instance started.

Total System Global Area 2.0176E+10 bytes
Fixed Size		    2261928 bytes
Variable Size		 3422555224 bytes
Database Buffers	 1.6710E+10 bytes
Redo Buffers		   41463808 bytes
Database mounted.
Database opened.

总结

停库前最好查下是否有会话未断开，如果有进行断开。

--查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;

--查询用户会话
select username,serial#, sid from v$session where username is not null;
select username,status,schemaname,osuser,process,machine,port,program,serial#, sid from v$session where username is not null;

--删除相关用户会话
alter system kill session 'sid，serial#';

补充：
--源库杀用户连接 不建议 该步骤慎重 该文档未进行该操作  该方法迫不得已不用
ps -ef|grep LOCAL=NO|awk '{print $2}'|xargs kill -9