sqlplus登录卡死无响应异常处理

夜光小兔纸

已于 2024-03-15 20:48:45 修改

阅读量1.4k

点赞数 10

分类专栏： Oracle 数据库文章标签： oracle 数据库 linux

于 2024-03-15 20:48:11 首次发布

本文链接：https://blog.csdn.net/Ruishine/article/details/136730446

版权

数据库同时被 2 个专栏收录

214 篇文章 28 订阅

订阅专栏

Oracle

184 篇文章 18 订阅

订阅专栏

一、问题描述
通过Sqlplus 访问数据hang死在登录界面，且不能通过Ctrl+C取消，如下所示：

[oracle@test01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 13 10:41:36 2024
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> conn trui/rui

或：

[oracle@test01 ~]$ sqlplus trui/rui@192.168.1.10:1521/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 13 10:42:14 2024
Version Version 19.3.0.0.0

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

二、原因分析
查看alert日志，看看是否有相关提示信息。

说明：alert日志路径一般为 $ORACLE_BASE/diag/rdbms/[SID]/[SID]/trace/
其中[SID]需替换成数据库实例 $ORACLE_SID的名字，比如当前的实例是orcl，则alert日志路径为 $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/

执行如下命令查看alert日志：

$ cd $ORACLE_BASE/
$ cd diag/rdbms/orcl/orcl/trace/
$ tail -f alert_orcl.log  或  $ tail -n100 alert_orcl.log

发现有如下字样：

2024-03-13T00:10:26.192121+08:00
************************************************************* Unable to allocate flashback log of 32768 blocks from current recovery area
of size 32212254720 bytes. Recovery Writer (RVWR) is stuck until more
space is available in the recovery area. Unable to write Flashback
database log data because the recovery area is full, presence of a
guaranteed restore point and no reusable flashback logs.

继续向上翻看log，还有如下字样：

2024-03-12T19:11:32.919339+08:00
Errors in file /oracle/app/oracle/diag/rdbms/wind2/wind2/trace/wind2_m002_23422.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 32212254720 bytes is 100.00% used, and has 0 remaining bytes available.

所以，初步分析应该是fast_recovery_area为文件夹空间不足。

三、处理方法
1.查询fast_recovery_area路径

SQL> show parameter recover

NAME                                                  TYPE        VALUE
------------------------------------                 -----------       ------------------------------
db_recovery_file_dest                       string              /oracle/app/oracle/fast_recovery_area
db_recovery_file_dest_size              big integer    4560M
db_unrecoverable_scn_tracking     boolean        TRUE
recovery_parallelism                         integer           0
remote_recovery_file_dest              string

2.查看fast_recovery_area文件夹当前大小

[oracle@test01 ~]$ cd  /oracle/app/oracle/
[oracle@test01 ~]$ du * -sh --time
6.3G    2024-03-13 10:45        admin
4.0K    2024-01-23 11:10        audit
18M     2023-12-01 17:52        cfgtoollogs
0       2023-12-01 17:53        checkpoints
717M    2024-03-13 10:45        diag
4.5G     2024-03-13 10:45        fast_recovery_area
2.0G    2024-03-13 10:45        oradata
5.7G    2024-03-08 13:26        product

发现确实是文件夹写满了。

3.修改db_recovery_file_dest_size参数
本例中，将db_recovery_file_dest_size参数值修改为10G。

SQL> alter system set db_recovery_file_dest_size=10G scope=both;

System altered.

SQL> show parameter recover

NAME                                                  TYPE        VALUE
------------------------------------                 -----------       ------------------------------
db_recovery_file_dest                       string              /oracle/app/oracle/fast_recovery_area
db_recovery_file_dest_size              big integer    10G
db_unrecoverable_scn_tracking     boolean        TRUE
recovery_parallelism                         integer           0
remote_recovery_file_dest              string

四、效果验证
再次尝试登录，发现问题已解决，可正常登录。

[oracle@test01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Mar 13 10:45:26 2024
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> conn wcinstall/wcinstall
Connected.
SQL>

或：

[oracle@test01 ~]$ sqlplus wcinstall/wcinstall@192.168.1.10:1521/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 13 10:47:00 2024
Version 19.3.0.0.0

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

Last Successful login time: Fri Mar 15 2024 10:14:04 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>