Oracle归档日志导致磁盘满

报错现象

ORA-00257: archiver error. Connect internal only, until freed.Username: kmdata
java.sql.SQLException: ORA-00257: 归档程序错误。在释放之前仅限于内部连接

解决

• 登录数据库服务器，查看归档日志使用状态

SQL> select FILE_TYPE,  PERCENT_SPACE_USED from v$flash_recovery_area_usage;
FILE_TYPE             PERCENT_SPACE_USED
--------------------- ------------------
CONTROL FILE                           0
REDO LOG                               0
ARCHIVED LOG                       99.97
BACKUP PIECE                           0
IMAGE COPY                             0
FLASHBACK LOG                          0
FOREIGN ARCHIVED LOG                   0

• 查看数据库归档日志位置，以及最大容量

SQL> show parameter db_recover
NAME                        TYPE             VALUE
--------------------------  ---------------  ------------------------------
db_recovery_file_dest       string           /u01/app/oracle/rdbms/archivelog/
db_recovery_file_dest_size  big integer      100G

• 使用rman用户删除归档日志
  ◇： 先使用rman用户删除，再进入/u01/app/oracle/rdbms/archivelog/删除7前天的归档日志
  ◇： find ./ -mtime +7 | xargs rm -rf

su - oracle
rman target/
crosscheck archivelogall;       #显示日志，内容大，可以不打印出来。
delete expired archivelog all;           #删除全部的归档日志
DELETE ARCHIVELOG ALL COMPLETED BEFORE'SYSDATE-7';       #指定删除7天前的归档日志

关闭archivelog

• 关闭arcihvelog，开关档都必须在mount 状态下执行
  ◇： shutdown immediate;
  ◇： startup mount
  ◇： alter database noarchivelog;
  ◇： alter database open;

SQL> show parameter user
NAME                             TYPE         VALUE
-------------------------------- -----------  ------------------------------
license_max_users i              nteger       0
parallel_adaptive_multi_user     boolean      TRUE
redo_transport_user              string
user_dump_dest                   string       /u01/app/oracle/rdbms/diag/rdbms/kmb2b/kmb2b/trace

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

自动删除归档日志脚本

[oracle@kmb2b-db01 init]$ cat oracle_del_archivelog.sh
#!/bin/bash
export ORACLE_SID=kmb2b
export TERM=vt100
export ORACLE_BASE=/u01/app/oracle/rdbms
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export ORACLE_DOC=$ORACLE_BASE/doc
export TMP=/tmp
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'
source /home/oracle/.bash_profile 
$ORACLE_HOME/bin/rman target sys/sys_password  <<EOF
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate-30';
exit;
EOF