问题描述
使用PL/SQL Developer登录数据库时显示如下错误:
问题分析
经查阅资料,找到了问题排查方向,可能是archive log 日志已满。
问题排查步骤如下:
(1)用sys用户登录
$ sqlplus / as sysdba
(2)看看archiv log所在位置
SQL> show parameter log_archive_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_11 string enable
log_archive_dest_state_12 string enable
log_archive_dest_state_13 string enable
log_archive_dest_state_14 string enable
log_archive_dest_state_15 string enable
log_archive_dest_state_16 string enable
log_archive_dest_state_17 string enable
log_archive_dest_state_18 string enable
log_archive_dest_state_19 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_20 string enable
log_archive_dest_state_21 string enable
log_archive_dest_state_22 string enable
log_archive_dest_state_23 string enable
log_archive_dest_state_24 string enable
log_archive_dest_state_25 string enable
log_archive_dest_state_26 string enable
log_archive_dest_state_27 string enable
log_archive_dest_state_28 string enable
log_archive_dest_state_29 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_30 string enable
log_archive_dest_state_31 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
(3)上述查询VALUE为空,可以用如下命令查看归档目录和log sequence
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 269
Next log sequence to archive 269
Current log sequence 272
由查询结果得知,归档位置用的是默认值,放在flash_recovery_area下。
(4)可以用如下命令查询recovery目录
SQL> show parameter recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/app/fast_recovery_area
db_recovery_file_dest_size big integer 4560M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
由查询结果得知:
db_recovery_file_dest目录为/oracle/app/fast_recovery_area ;
该目录大小限制为4560M,即4.5G 。
(5)查看该文件当前大小
$ cd /oracle/app/fast_recovery_area
$ du * -sh --time
9.8M 2022-03-28 22:46 study
4.5G 2022-03-28 14:00 STUDY
$ cd STUDY
$ ll
total 4
drwxr-x---. 32 oracle oinstall 4096 Mar 28 05:00 archivelog
drwxr-x---. 2 oracle oinstall 6 Feb 17 22:54 onlinelog
$ du * -sh --time
4.5G 2022-03-28 14:00 archivelog
0 2022-02-17 22:54 onlinelog
由查询结果可知,该文件已大小已达上限。验证了对问题产生原因的猜测。
处理方法
转移或清除对应的归档日志, 删除一些不用的日期目录的文件,注意保留最后几个文件。
注意:
在删除归档日志后,必须用RMAN维护控制文件,否则空间显示仍然不释放。
$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Mon Mar 28 23:17:34 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: STUDY (DBID=3114536018)
检查一些无用的archivelog
RMAN> crosscheck archivelog all;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=190 device type=DISK
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_01/o1_mf_1_86_k1w9qc4l_.arc RECID=1 STAMP=1098223211
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_01/o1_mf_1_87_k1w9qk4w_.arc RECID=2 STAMP=1098223217
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_02/o1_mf_1_88_k1wjrt67_.arc RECID=3 STAMP=1098230426
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_02/o1_mf_1_89_k1x9fc98_.arc RECID=4 STAMP=1098255659
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_02/o1_mf_1_90_k1xft89q_.arc RECID=5 STAMP=1098260168
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_02/o1_mf_1_91_k1y6nfd7_.arc RECID=6 STAMP=1098285581
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_02/o1_mf_1_92_k1yy35f8_.arc RECID=7 STAMP=1098309605
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_02/o1_mf_1_93_k1yy3cfj_.arc RECID=8 STAMP=1098309611
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_02/o1_mf_1_94_k1yy3qfp_.arc RECID=9 STAMP=1098309623
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_03/o1_mf_1_95_k1zprcgo_.arc RECID=10 STAMP=1098334859
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_03/o1_mf_1_96_k202b3hm_.arc RECID=11 STAMP=1098346691
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_03/o1_mf_1_97_k20sx2ml_.arc RECID=12 STAMP=1098370850
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_03/o1_mf_1_98_k21lhcn0_.arc RECID=13 STAMP=1098396011
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_03/o1_mf_1_99_k21lhnot_.arc RECID=14 STAMP=1098396020
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_04/o1_mf_1_100_k227mrq7_.arc RECID=15 STAMP=1098417656
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_04/o1_mf_1_101_k22pt0f0_.arc RECID=16 STAMP=1098433216
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_04/o1_mf_1_102_k233q6rw_.arc RECID=17 STAMP=1098446438
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_04/o1_mf_1_103_k23rsctq_.arc RECID=18 STAMP=1098468011
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_04/o1_mf_1_104_k246v8wo_.arc RECID=19 STAMP=1098482409
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_04/o1_mf_1_105_k246vkws_.arc RECID=20 STAMP=1098482417
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_05/o1_mf_1_106_k24vzz5d_.arc RECID=21 STAMP=1098504063
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_05/o1_mf_1_107_k252zh5v_.arc RECID=22 STAMP=1098511215
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_05/o1_mf_1_108_k25c9wp5_.arc RECID=23 STAMP=1098519740
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_05/o1_mf_1_109_k25k5w73_.arc RECID=24 STAMP=1098525756
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_05/o1_mf_1_110_k25r3b99_.arc RECID=25 STAMP=1098532842
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_05/o1_mf_1_111_k25z8qbb_.arc RECID=26 STAMP=1098540183
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_05/o1_mf_1_112_k26fc6cs_.arc RECID=27 STAMP=1098554598
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_05/o1_mf_1_113_k26vfydv_.arc RECID=28 STAMP=1098569022
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_06/o1_mf_1_114_k27mtwgg_.arc RECID=29 STAMP=1098594012
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_06/o1_mf_1_115_k27tw5hw_.arc RECID=30 STAMP=1098601221
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_06/o1_mf_1_116_k27zss7p_.arc RECID=31 STAMP=1098606265
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_06/o1_mf_1_117_k285oxk9_.arc RECID=32 STAMP=1098612285
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_06/o1_mf_1_118_k28dhjkw_.arc RECID=33 STAMP=1098619248
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_06/o1_mf_1_119_k28mrhlj_.arc RECID=34 STAMP=1098626703
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_06/o1_mf_1_120_k291v7ly_.arc RECID=35 STAMP=1098641127
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_06/o1_mf_1_121_k29hxso7_.arc RECID=36 STAMP=1098655545
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_07/o1_mf_1_122_k2b86zqf_.arc RECID=37 STAMP=1098680415
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_07/o1_mf_1_123_k2bn9oo2_.arc RECID=38 STAMP=1098692789
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_07/o1_mf_1_124_k2c4d9tj_.arc RECID=39 STAMP=1098709257
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_07/o1_mf_1_125_k2cwz1xh_.arc RECID=40 STAMP=1098734433
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_07/o1_mf_1_126_k2d3zdyc_.arc RECID=41 STAMP=1098741613
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_07/o1_mf_1_127_k2d3zoyq_.arc RECID=42 STAMP=1098741622
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_08/o1_mf_1_128_k2ds2j0v_.arc RECID=43 STAMP=1098763216
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_08/o1_mf_1_129_k2f8sl0c_.arc RECID=44 STAMP=1098779314
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_08/o1_mf_1_130_k2flhf2l_.arc RECID=45 STAMP=1098789229
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_08/o1_mf_1_131_k2gd083t_.arc RECID=46 STAMP=1098815368
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_08/o1_mf_1_132_k2grc75p_.arc RECID=47 STAMP=1098828007
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_08/o1_mf_1_133_k2grcj60_.arc RECID=48 STAMP=1098828016
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_09/o1_mf_1_134_k2hfgm91_.arc RECID=49 STAMP=1098849619
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_09/o1_mf_1_135_k2hvk5bb_.arc RECID=50 STAMP=1098864037
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_09/o1_mf_1_136_k2jmsjc9_.arc RECID=51 STAMP=1098888880
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_09/o1_mf_1_137_k2kc22fy_.arc RECID=52 STAMP=1098912706
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_09/o1_mf_1_138_k2kdqfgg_.arc RECID=53 STAMP=1098914413
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_09/o1_mf_1_139_k2kj9bgl_.arc RECID=54 STAMP=1098918058
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_10/o1_mf_1_140_k2l8w2kp_.arc RECID=55 STAMP=1098943234
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_10/o1_mf_1_141_k2lj1102_.arc RECID=56 STAMP=1098950561
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_10/o1_mf_1_142_k2m4zjmz_.arc RECID=57 STAMP=1098972016
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_10/o1_mf_1_143_k2mxn4oj_.arc RECID=58 STAMP=1098997252
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_10/o1_mf_1_144_k2n13boq_.arc RECID=59 STAMP=1099000810
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_10/o1_mf_1_145_k2n13moz_.arc RECID=60 STAMP=1099000819
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_11/o1_mf_1_146_k2np6wp8_.arc RECID=61 STAMP=1099022428
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_11/o1_mf_1_147_k2ogsnr3_.arc RECID=62 STAMP=1099047604
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_11/o1_mf_1_148_k2p7qks5_.arc RECID=63 STAMP=1099073137
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_11/o1_mf_1_149_k2pohfmf_.arc RECID=64 STAMP=1099087213
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_11/o1_mf_1_150_k2pot6d4_.arc RECID=65 STAMP=1099087558
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_11/o1_mf_1_151_k2pot6sh_.arc RECID=66 STAMP=1099087558
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_12/o1_mf_1_152_k2q7fktd_.arc RECID=67 STAMP=1099105585
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_12/o1_mf_1_153_k2qkm7y6_.arc RECID=68 STAMP=1099116008
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_12/o1_mf_1_154_k2qkmfyb_.arc RECID=69 STAMP=1099116014
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_12/o1_mf_1_155_k2r02h08_.arc RECID=70 STAMP=1099130831
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_12/o1_mf_1_156_k2rg4y17_.arc RECID=71 STAMP=1099145246
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_12/o1_mf_1_157_k2rw7s2p_.arc RECID=72 STAMP=1099159673
validation succeeded for archived log
archived log file name=/oracle/app/fast_recovery_area/STUDY/archivelog/2022_03_12/o1_mf_1_158_k2sbb3mo_.arc RECID=73 STAMP=1099174083
validation succeeded for archived l