ORA-03113, ORA-19815

APPLIES TO

Operating System - Oracle Linux Server 7.9
Oracle Database - Enterprise Edition - version 11.2.0.4.0

SYMPTOMS

数据库打开时，报 ORA-03113 错误：

SQL> startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 373295128 bytes
Database Buffers 138412032 bytes
Redo Buffers 7974912 bytes
Database mounted.

ORA-03113: end-of-file on communication channel
Process ID: 10579
Session ID: 63 Serial number: 5

CAUSE

查看数据库 alert 日志，提示闪回区空间已满 100%。

Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_10708.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2097152000 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.

ARCH: Error 19809 Creating archive log file to '/u01/app/oracle/archive_dest/PROD1/archivelog/2018_08_12/o1_mf_1_19_%u_.arc'
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_10708.trc:
ORA-16038: log 1 sequence# 19 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/PROD1/redo01.log'
USER (ospid: 10708): terminating the instance due to error 16038

SOLUTION

  • 增加闪回区空间 db_recovery_file_dest_size。

    启动数据库到 mount 状态

    startup mount

    增加闪回区空间限制

    alter system set db_recovery_file_dest_sise=200G sid='*' scope=both;

    打开数据库

    alter database open;

  • 使用 RMAN 删除归档，释放闪回区空间。

    启动数据库到 mount 状态

    startup mount

    删除归档，保留 30 天

    rman target /
    RMAN> crosscheck archivelog all;
    RMAN> delete noprompt expired archivelog all;
    RMAN> delete noprompt archivelog all completed before 'sysdate - 30';

    打开数据库

    alter database open;