oracle文件夹已满,oracle 归档空间满的解决办法

问题现象：

通过命令提示符登陆数据库，一般提示“ora-03113：通信通道的文件结尾”错误，查看trace日志，可以看到详细信息。部分摘录如下(橙色部分给出了建议方案):

Errors infile g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1368.trc:

ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 4102029312 字节) 已使用 100.00%, 尚有 0字节可用。************************************************************************You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

system command was used to delete files, then use RMAN CROSSCHECK and

DELETE EXPIRED commands.************************************************************************Errorsinfile g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1368.trc:

ORA-19809: 超出了恢复文件数的限制

ORA-19804: 无法回收 17847808 字节磁盘空间 (从 4102029312限制中)

ARCH: Error19809 Creating archive log file to ‘G:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_08_26\O1_MF_1_561_%U_.ARC‘Errorsinfile g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1368.trc:

ORA-16038: 日志 3 sequence# 561无法归档

ORA-19809: 超出了恢复文件数的限制

ORA-00312: 联机日志 3 线程 1: ‘G:\ORADATA\ORCL\REDO03.LOG‘USER (ospid:1368): terminating the instance due to error 16038ARC1: Archival started

Errorsinfile g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_arc0_4836.trc:

ORA-01092: ORACLE instance terminated. Disconnection forced

解决方法(两种)：

增加归档空间，设置db_recovery_file_dest参数

sqlplus / as sysdba

shutdown abort --关闭进程

startup mount -- 启动数据库到mount模式

alter system set db_recovery_file_dest=10G; -- 设置归档空间大小

shutdown immediate --关闭数据库

startup --启动数据库

删除多余归档，只保留需要的(根据业务保留恢复数据库所需要的最近归档)，这种办法同样有两种方式，一种直接rman方式删除归档，一种在操作系统层次删除，但是因为每个归档在数据库中都有记录，仍需再进入rman删除失效归档文件，否则报错。

第一种方式：

rman target / --进入rman模式

list archivelog all; --列出全部归档文件

delete archivelog all complited before ‘SYSDATA-7‘; --删除7天前的所有归档

ps：SYSDATA-7，表明当前的系统时间7天前，before关键字表示在7天前的归档日志

第二种方式：

如果不小心在操作系统层次提前删除了归档日志，需进入rman清楚过期归档日志信息。执行如下操作：

rman target/crosscheck archivelogall; --检查归档信息

delete expired archivelog all; --删除过期归档list archivelog all;

执行完毕后，退出rman，重启数据库。

附(部分rman命令)：

backup database    对数据库做全备

delete obsolete    根据备份策略删除陈旧备份

delete expired backup     删除失效备份

delete expired copy     删除失效备份副本

delete backup    删除所有备份，慎用。如需使用，建议使用后立即备份一次

问题现象

通过命令提示符登陆数据库，一般提示“ora-03113：通信通道的文件结尾 ”错误，查看trace日志，可以看到详细信息。部分摘录如下(粗体给出了建议方案):

Errors in file g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1368.trc:

ORA-19815:警告: db_recovery_file_dest_size字节(共4102029312字节)已使用100.00%,尚有0字节可用。

************************************************************************

You have following choices to free up space from recovery area:

1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,

then consider changing RMAN ARCHIVELOG DELETION POLICY.

2. Back up files to tertiary device such as tape using RMAN

BACKUP RECOVERY AREA command.

3. Add disk space and increase db_recovery_file_dest_size parameter to

reflect the new space.

4. Delete unnecessary files using RMAN DELETE command. If an operating

system command was used to delete files, then use RMAN CROSSCHECK and

DELETE EXPIRED commands.

************************************************************************

Errors in file g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1368.trc:

ORA-19809:超出了恢复文件数的限制

ORA-19804:无法回收17847808字节磁盘空间(从4102029312限制中)

ARCH: Error 19809 Creating archive log file to ‘G:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2017_08_26\O1_MF_1_561_%U_.ARC‘

Errors in file g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1368.trc:

ORA-16038:日志3 sequence# 561无法归档

ORA-19809:超出了恢复文件数的限制

ORA-00312:联机日志3线程1: ‘G:\ORADATA\ORCL\REDO03.LOG‘

USER (ospid: 1368): terminating the instance due to error 16038

ARC1: Archival started

Errors in file g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_arc0_4836.trc:

ORA-01092: ORACLE instance terminated. Disconnection forced

解决方法

l增加归档空间，设置db_recovery_file_dest参数。

1.Sqlplus / as sysdba

删除多余归档，只保留需要的(根据业务保留恢复数据库所需要的最近归档)。

原文：http://www.cnblogs.com/benbenduo/p/7437333.html