8.数据库无法登陆，提示archiver error

数据库无法登陆，提示archiver error

1、查看alert日志，归档日志进程阻塞，归档日志无法写入flash_recovery_area空间：

Fri Mar 09 11:03:33 2018
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 40879616 bytes disk space from 4070572032 limit
ARC1: Error 19809 Creating archive log file to '/home/orcl/flash_recovery_area/ORCL/archivelog/2018_03_09/o1_mf_1_4293_%u_.arc'

2、查看分区空间适用情况，空间并没用完

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_microplatdb-lv_root
                       50G  5.1G   42G  11% /
tmpfs                  16G  7.3G  8.4G  47% /dev/shm
/dev/sda1             477M   64M  384M  15% /boot
/dev/mapper/vg_microplatdb-lv_home
                      132G   30G   95G  24% /home

3、查看flash_recovery_area参数和archive参数配置，FLA空间为4G，查看该目录确已用完，archive归档日志写入到FLA

SQL> show parameter recover

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_recovery_file_dest                string                 /home/orcl/flash_recovery_area
db_recovery_file_dest_size           big integer            3882M
recovery_parallelism                 integer                0


SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4294
Next log sequence to archive   4296
Current log sequence           4296


SQL> show parameter archive
NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
archive_lag_target                   integer                0
log_archive_config                   string
log_archive_dest                     string                 /home/orcl/arch01
log_archive_dest_1                   string

解决：
4、先删除FLA中的归档日志（通过RMAN删除才会立即释放空间） ,数据库归档继续，可以正常登录

RMAN> crosscheck archivelog all;

RMAN> delete archivelog all;
<<<
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3407 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
3       1    4199    A 07-FEB-18
        Name: /home/orcl/flash_recovery_area/ORCL/archivelog/2018_02_06/o1_mf_1_4199_f7llvlr3_.arc

5、查看alert历史日志，分析为什么archive归档要写到flash_recovery_area，发现由于LOG_ARCHIVE_DEST_1配置格式不正确

alert_orcl.log
<<<
ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

6、修改log_archive_dest参数设置，切换日志，archivelog恢复到归档目录，然后添加归档日志清理脚本

SQL> alter system set log_archive_dest_1='location=/home/orcl/arch01';

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/orcl/arch01
Oldest online log sequence     4294
Next log sequence to archive   4296
Current log sequence           4296


SQL> alter system switch logfile;

System altered.

SQL> !ls -al /home/orcl/arch01/*.arc
-rw-r-----   1 sieorcl dba       16962560 Mar  9 11:26 1_4296_886178759.arc
	
$ crontab -l	
01 00 * * * /home/orcl/scripts/delete_archivelog.sh >/dev/null 2>&1