环境
oracle rac 11.2.0.4 + CentOS Linux release 7.9.2009 ORA-01012
现象
登录时候报错
关闭数据的时候报错
[oracle@rac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 14 14:40:38 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected.
SQL> startup;
ORA-01012: not logged on
SQL> startup;
ORA-01012: not logged on
SQL> exit
Disconnected
[oracle@rac1 ~]$ sqlplus / as sysdba@racdb
SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 14 14:40:53 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
查看数据库监听有BLOCK
[oracle@rac1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-OCT-2021 14:40:32
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 14-OCT-2021 14:38:56
Uptime 0 days 0 hr. 1 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "racdb" has 1 instance(s).
Instance "racdb1", status BLOCKED, has 2 handler(s) for this service...
The command completed successfully
处理方法如下
[oracle@rac1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 14 14:47:55 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn system as sysdba
Enter password:
ERROR:
ORA-12532: TNS:invalid argument
SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 3273641984 bytes
Fixed Size 2257680 bytes
Variable Size 1929383152 bytes
Database Buffers 1325400064 bytes
Redo Buffers 16601088 bytes
Database mounted.
Database opened.
分析原因
shutdown immediate 报错如下
SQL> shut immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-27140: attach to post/wait facility failed
ORA-27300: OS system dependent operation:invalid_egid failed with status: 1
ORA-27301: OS failure message: Operation not permitted
ORA-27302: failure occurred at: skgpwinit6
ORA-27303: additional information: startup egid = 501 (oinstall), current egid = 504 (asmadmin)
metelink解释 Background processes are hanging/not started correctly during the previous startup of this database. Hence the semaphores and shared memory segments are not getting detached properly now during shutdown.
官方处理建议
1. Verify that there are no background processes owned by "oracle" , if there are kill them
$ ps -ef | grep ora_ | grep $ORACLE_SID
2. Remove shared memory and semaphores:
A) Check for shared memory and semaphores
$ ipcs -mt (if there is anything owned by oracle remove it)
$ ipcrm -m [ID] (to remove it)
B) Check and remove semaphores
$ ipcs -sbt (if there is anything owned by oracle remove it)
$ ipcrm -s [ID] (to remove it)
C) Remove sga and lk file
$ cd $ORACLE_HOME/dbs
$ rm sgadef<SID>.dbf (removing sga file)
$ORACLE_HOME/dbs/lk<sid> (removing lk... flies)
D) If database is down, try to bring up oracle one step at a time:
$ sqlplus /nolog
SQL> startup nomount pfile = ...[path]
SQL> alter database mount;
SQL> alter database open;\
Otherwise, exit current SQL*Plus session and verify the following environment variables are set.
echo $ORACLE_HOME
echo $ORACLE_SID (echo %ORACLE_SID% on Windows)
Then, Execute the following:
sqlplus / as sysdba
shutdown abort
exit
sqlplus / as sysdba
startup
上篇连接数问题