重庆思庄Oracle、Redhat认证学习论坛

标题: 数据库open时报错ORA-00205 [打印本页]

作者: denglj 时间: 2022-8-30 17:54
标题: 数据库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

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

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

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.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)