ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-19815 归档空间不足

ORA-19815 归档空间不足

原创 Linux操作系统 作者:hjianping 时间:2011-05-01 17:42:01 0 删除 编辑

alert日志时出现以下警告:

Wed Apr 20 15:14:24 2011
Errors in file /u01/oracle/app/admin/ora10/bdump/ora10_arc0_2452.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes avai
lable.
Wed Apr 20 15:14:24 2011
************************************************************************
You have following choices to free up space from flash 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.
************************************************************************

Oracle给了4中解决办法:

1. 修改RMAN的备份保持策略,如果使用了Data Guard 改变archivelog 删除策略。
2. 使用Rman 把文件备份到磁带上(这里好像只能把recovery area 备份到磁带上,备份到磁盘上
   会报错,有待进一步确认)
3. 修改db_recovery_file_dest_size 参数增加更多的空间。
4. 使用操作系统或RMAN DELETE 命令删除过期的命令。如果使用操作系统命令删除文件,要用rman执行
   crosscheck archivelog all;


解决方法:

一、删除过期归档日志

SQL> conn / as sysdba
SQL> show parameter db_recovery_file_dest_size

NAME                        TYPE        VALUE
--------------------------- ----------- ------
db_recovery_file_dest_size  big integer 2G

SQL> col name for a40
SQL> select name,space_limit,space_used,space_reclaimable,number_of_files
from v$recovery_file_dest;

NAME                                QUOTA       USED RECLAIMABLE      FILES
------------------------------ ---------- ---------- ----------- ----------
/oracle/flash_recovery_area    2147483648 2146841600           0         52


RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

SQL> select name,space_limit,space_used,space_reclaimable,number_of_files
from v$recovery_file_dest;

NAME                                QUOTA       USED RECLAIMABLE      FILES
------------------------------ ---------- ---------- ----------- ----------
/oracle/flash_recovery_area    2147483648   15521792           0          3

问题解决


二、修改过db_recovery_file_dest_size,把其值改大些

SQL> alter system set db_recovery_file_dest_size=4G scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              71305460 bytes
Database Buffers           92274688 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.

SQL> show parameter db_recovery_file_dest_size

NAME                        TYPE        VALUE
--------------------------- ----------- ------
db_recovery_file_dest_size  big integer 4G
 

三、重新设置归档日志位置

SQL> alter system set log_archive_dest_1='location=/u02/oracle/app/ora10/arch/';

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17012874/viewspace-694207/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 批处理中执行SQL
下一篇: Database Link
请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    80
  • 访问量
    72864