重庆思庄oracle技术分享-数据库open时报错ORA-00205

系统：CentOS7.9

环境：单实例

问题描述：Oracle11.2.0.4升级19.3后，open数据库报错ORA-00205，如下所示：

SQL>startup

ORACLEinstance started.

TotalSystem Global Area 2455228416 bytes

FixedSize                  2255712 bytes

VariableSize             620758176 bytes

DatabaseBuffers         1811939328 bytes

RedoBuffers               20275200 bytes

ORA-00205:error in identifying control file, check alert log for more info

以下trace文件报错信息：

Wed Aug17 20:55:07 2022

ALTERDATABASE   MOUNT

ORA-00210:cannot open the specified control file

ORA-00202:control file: '/u01/app/oracle/fast_recovery_area/orcl150/control02.ctl'

ORA-27086:unable to lock file - already in use

Linux-x86_64Error: 11: Resource temporarily unavailable

Additionalinformation: 8

Additionalinformation: 34443

ORA-00210:cannot open the specified control file

ORA-00202:control file: '/u01/app/oracle/oradata/orcl150/control01.ctl'

ORA-27086:unable to lock file - already in use

Linux-x86_64Error: 11: Resource temporarily unavailable

Additionalinformation: 8

Additionalinformation: 34443

ORA-205signalled during: ALTER DATABASE  MOUNT...

核心报错ORA-27086，通过ps-ef确认到存在数据库orcl150正在运行.

[oracle@oeldb]$ ps -ef|grep ora_smon

oracle    34447     1  0 20:53 ?        00:00:00 ora_smon_orcl150

oracle    43919  6876  0 23:20 pts/1    00:00:00 grep --color=auto ora_smon

监听状态也显示orcl150数据库正在运行.

[oracle@oeldb]$ lsnrctl status

LSNRCTLfor Linux: Version 11.2.0.4.0 - Production on 17-AUG-2022 23:20:20

Copyright(c) 1991, 2013, Oracle.  All rightsreserved.

Connectingto (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS ofthe LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version19.0.0.0.0 - Production

StartDate                17-AUG-2022 20:46:07

Uptime                    0 days 2 hr. 34 min. 13 sec

TraceLevel               off

Security                  ON: Local OS Authentication

SNMP                      OFF

ListenerParameter File  /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora

ListenerLog File        /u01/app/oracle/diag/tnslsnr/oel/listener/alert/log.xml

ListeningEndpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oel)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

ServicesSummary...

Service "ORCL150"has 1 instance(s).

  Instance "orcl150", status READY,has 1 handler(s) for this service...

Service"orcl150XDB" has 1 instance(s).

  Instance "orcl150", status READY,has 1 handler(s) for this service...

Thecommand completed successfully

sql plus连接显示却为idleinstance.

[oracle@oeldb]$ sqlplus / as sysdba

SQL*Plus:Release 11.2.0.4.0 Production on Wed Aug 17 23:20:28 2022

Copyright(c) 1982, 2013, Oracle.  All rightsreserved.

Connectedto an idle instance.

通过控制文件查看数据库后台进程运行情况.

[root@oeldbs]# lsof //u01/app/oracle/oradata/orcl150/control0*

COMMAND     PID  USER   FD   TYPE DEVICE SIZE/OFF      NODE NAME

ora_dbw0_34439 oracle  256u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_lgwr_34441 oracle  256u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_ckpt_34443 oracle  256uW  REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_w001_34459 oracle  258u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_mmon_34465 oracle  257u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_m000_34478 oracle  256u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_m001_34480 oracle  260u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_tt00_34487 oracle  256u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_arc0_34489 oracle  256u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_arc1_34493 oracle  256u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_arc2_34495 oracle  256u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_arc3_34497 oracle  256u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_cjq0_34503 oracle  257u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_w002_34530 oracle  257u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_m002_34704 oracle  258u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_m003_34706 oracle  259u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_w003_34729 oracle  258u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

ora_w005_35415 oracle  258u   REG 253,0 10436608 208809672 //u01/app/oracle/oradata/orcl150/control01.ctl

通过系统命令杀掉后台进程.

[root@oeldbs]# kill -9 34439

[root@oeldbs]# kill -9 34441

[root@oeldbs]# kill -9 34443

[root@oeldbs]# kill -9 34459

[root@oeldbs]# kill -9 34465

[root@oeldbs]# kill -9 34478

[root@oeldbs]# kill -9 34480

[root@oeldbs]# kill -9 34487

[root@oeldbs]# kill -9 34489

[root@oeldbs]# kill -9 34493

[root@oeldbs]# kill -9 34495

[root@oeldbs]# kill -9 34497

[root@oeldbs]# kill -9 34503

[root@oeldbs]# kill -9 34530

[root@oeldbs]# kill -9 34704

[root@oeldbs]# kill -9 34706

[root@oeldbs]# kill -9 34729

[root@oeldbs]# kill -9 35415

[root@oeldbs]# lsof /u01/app/oracle/fast_recovery_area/orcl150/control0*

此后数据库正常开启.

[oracle@oel~]$ sqlplus / as sysdba

SQL*Plus:Release 19.0.0.0.0 - Production on Wed Aug 17 23:32:36 2022

Version19.3.0.0.0

Copyright(c) 1982, 2019, Oracle.  All rightsreserved.

Connectedto an idle instance.

SQL>startup nomount;

ORACLEinstance started.

TotalSystem Global Area 2466249672 bytes

FixedSize                  8899528 bytes

VariableSize             536870912 bytes

DatabaseBuffers         1912602624 bytes

RedoBuffers                7876608 bytes

SQL>alter database mount;

Databasealtered.

SQL>alter database open;

Databasealtered.