ORA-03113

首先查看日志文件信息

 

G:\app\Administrator\diag\rdbms\orcl\orcl\trace

 

*******************************************************************
Errors in file g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_arc2_15548.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 33586688 bytes disk space from 10737418240 limit
ARC2: Error 19809 Creating archive log file to ‘G:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2021_06_25\O1_MF_1_1682_%U_.ARC‘
Fri Jun 25 18:16:14 2021
Errors in file g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5728.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 10737418240 字节) 已使用 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_5728.trc:
ORA-19809: 超出了恢复文件数的限制
ORA-19804: 无法回收 32588800 字节磁盘空间 (从 10737418240 限制中)
ARCH: Error 19809 Creating archive log file to ‘G:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2021_06_25\O1_MF_1_1681_%U_.ARC‘
Errors in file g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5728.trc:
ORA-16038: 日志 4 sequence# 1681 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 4 线程 1: ‘G:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO04.LOG‘
USER (ospid: 5728): terminating the instance due to error 16038
Errors in file g:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_arc0_15568.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
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.
************************************************************************
Fri Jun 25 18:16:29 2021
Instance terminated by USER, pid = 5728

 

从这里我们发现了问题的根源:“

ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 4102029312 字节) 已使用100.00%, 尚有 0 字节可用。 db_recovery_file_dest_size也叫归档日志空间不足导致的,既然找到问题的根源,那解决起来也就容易了。

 

解决途径

 

空间小,那摆在我们面前办法就是,一个是将空间设置大点,另一个就是将多余的文件删除掉即可,那么我们就将这两个办法都使用一下。

 

通过命令窗口:

--------设置归档日志空间的大小

  1. sqlplus / as sysdba  
  2. shutdown abort     ----关闭进程  
  3. startup mount       ---- 装载数据库  
  4. select * from v$recovery_file_dest; ---查询归档日志  
  5. > alter system set  db_recovery_file_dest_size=10737418240  scope=spfile;; --设置归档日志空间为10G      // alter system set    db_recovery_file_dest_size=30720M  scope=spfile;; 
  6. ---到这里空间大小已经设置完成  重启数据库
  7. shutdown immediate
  8. startup
  9. ORACLE 例程已经启动。

    Total System Global Area 3390558208 bytes
    Fixed Size 2180464 bytes
    Variable Size 2231372432 bytes
    Database Buffers 1140850688 bytes
    Redo Buffers 16154624 bytes
    数据库装载完毕。
    数据库已经打开。
    SQL>

 

删除归档文件

export Oracle_SID=testdb

delete archivelog until time "sysdate - 7";

YES

exit

EOF

ORA-03113

上一篇:1277. 统计全为 1 的正方形子矩阵


下一篇:自定义异常、基于类和对象、多态、继承、抽象、接口、异常的汽车选择查询