ORA-03113: end-of-file on communication channel解决步骤

通常这个异常都是空间满导致,查询alter_orcl.log 这个文件一般都能看到错误信息,其中orcl为数据库的sid。
一般都会有类似的日志:
ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 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.

解决方案一、清理文件(如果不想清理文件可以查看解决方案二)

切换到oracle用户执行:

su - oracle

[oracle@db~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 23 17:44:00 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.
sql> conn / as sysdba

Connected to an idle instance.

sql> startup nomount

ORACLE instance started.
再执行
sql> alter database mount;

Database altered.

接下来就要清理空间了
另外起一个终端,找到文件存放路径:
[oracle@db~]$ cd /data2/oracle/flash_recovery_area/ORCL/archivelog/
[oracle@db archivelog]$ pwd
/data2/oracle/flash_recovery_area/ORCL/archivelog
[oracle@db archivelog]$ ls
2016_06_01 2016_06_02 2016_06_03 2016_06_04 2016_06_05 2016_06_06 2016_06_07 2016_06_08 2016_06_21 2016_06_22 2016_06_23
[oracle@db archivelog]$ rm -rf 2016_05*
删除掉不要的文件。

接下来需要启动rman了
[oracle@db archivelog]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 23 17:49:55 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1412518558)

RMAN> crosscheck archivelog all;

一堆的提示不管它,再执行:
RMAN> delete expired archivelog all;
删除掉过期的归档文件。

接下来回到刚才的sqlplus终端,执行如下:
SQL> alter database open;

Database altered.

SQL>

好了,数据库起来了。
然后启动监听端口就万事大吉了。

解决方案二、增加数据库空间

如果磁盘空间没有满,想增加闪回恢复区。
在mount数据库后,执行:
alter system set db_recovery_file_dest_size=8G;
修改的过程如下:
<pre>
[oracle@db~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 24 10:56:40 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> conn /as sydba
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL> exit
[oracle@firebox42 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 24 10:57:02 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORA-01012: not logged on
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size 2216344 bytes
Variable Size 6845107816 bytes
Database Buffers 3825205248 bytes
Redo Buffers 16945152 bytes
SQL> alter database mount;

Database altered.

SQL> show parameter db_recover;

NAME TYPE VALUE


db_recovery_file_dest string /opt/oracle/flash_recovery_are
a
db_recovery_file_dest_size big integer 3882M
SQL> alter system set db_recovery_file_dest_size=80G;

System altered.

SQL> alter database open;

Database altered.

参考文章:https://www.jianshu.com/p/97b86d95894b

Comments Closed.