Oracle启动报错ORA-03113解决

环境:RHEL6.4 + Oracle 11.2.0.4

步骤摘要: 1.启动报错ORA-03113 2.查看alert日志查找原因 3.根据实际情况采取合理的措施,这里我们先增加闪回区大小,把库启动起来 4.制定对应的归档日志删除策略

1.启动报错ORA-03113

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期六 2月 28 13:56:44 2015

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size            2365590928 bytes
Database Buffers          822083584 bytes
Redo Buffers               16904192 bytes
Database mounted.
SQL> alter database open; 
alter database open
*
ERROR at line 1:
ORA-03113: 通信通道的文件结尾
进程 ID: 13501
会话 ID: 853 序列号: 5


SQL> 

2.查看alert日志查找原因

Sat Feb 28 13:56:52 2015
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 8
CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
    NUMA status: non-NUMA system
    cellaffinity.ora status: N/A
CELL communication will use 1 IP group(s):
    Grp 0: 
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =249
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
ORACLE_HOME = /opt/app/oracle/product/11.2.0.4/dbhome_1
System name:    Linux
Node name:      JJFAB01
Release:        2.6.32-358.el6.x86_64
Version:        #1 SMP Tue Jan 29 11:47:41 EST 2013
Machine:        x86_64
VM name:        Xen Version: 4.1 (PVM)
Using parameter settings in server-side spfile /opt/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfileJJFAB.ora
System parameters with non-default values:
  processes                = 1500
  sessions                 = 2272
  memory_target            = 3G
  control_files            = "/opt/oradata/gxwj/control01.ctl"
  control_files            = "/opt/app/oracle/fast_recovery_area/gxwj/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  db_recovery_file_dest    = "/opt/app/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4122M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=gxwjXDB)"
  audit_file_dest          = "/opt/app/oracle/admin/gxwj/adump"
  audit_trail              = "DB"
  db_name                  = "JJFAB"
  open_cursors             = 300
  diagnostic_dest          = "/opt/app/oracle"
Sat Feb 28 13:56:54 2015
PMON started with pid=2, OS id=13461 
Sat Feb 28 13:56:54 2015
PSP0 started with pid=3, OS id=13463 
Sat Feb 28 13:56:55 2015
VKTM started with pid=4, OS id=13465 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Sat Feb 28 13:56:55 2015
GEN0 started with pid=5, OS id=13469 
Sat Feb 28 13:56:55 2015
DIAG started with pid=6, OS id=13471 
Sat Feb 28 13:56:55 2015
DBRM started with pid=7, OS id=13473 
Sat Feb 28 13:56:55 2015
DIA0 started with pid=8, OS id=13475 
Sat Feb 28 13:56:55 2015
MMAN started with pid=9, OS id=13477 
Sat Feb 28 13:56:55 2015
DBW0 started with pid=10, OS id=13479 
Sat Feb 28 13:56:55 2015
LGWR started with pid=11, OS id=13481 
Sat Feb 28 13:56:55 2015
CKPT started with pid=12, OS id=13483 
Sat Feb 28 13:56:55 2015
SMON started with pid=13, OS id=13485 
Sat Feb 28 13:56:55 2015
RECO started with pid=14, OS id=13487 
Sat Feb 28 13:56:55 2015
MMON started with pid=15, OS id=13489 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Sat Feb 28 13:56:55 2015
MMNL started with pid=16, OS id=13491 
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/app/oracle
Sat Feb 28 13:56:56 2015
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 3498004520
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Sat Feb 28 13:57:48 2015
alter database open
Sat Feb 28 13:57:48 2015
LGWR: STARTING ARCH PROCESSES
Sat Feb 28 13:57:48 2015
ARC0 started with pid=20, OS id=13553 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Sat Feb 28 13:57:49 2015
ARC1 started with pid=21, OS id=13555 
Sat Feb 28 13:57:49 2015
ARC2 started with pid=22, OS id=13557 
Errors in file /opt/app/oracle/diag/rdbms/jjfab/JJFAB/trace/JJFAB_ora_13501.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 4322230272 字节) 已使用 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.
************************************************************************
ARCH: Error 19809 Creating archive log file to '/opt/app/oracle/fast_recovery_area/JJFAB/archivelog/2015_02_28/o1_mf_1_130_%u_.arc'
Errors in file /opt/app/oracle/diag/rdbms/jjfab/JJFAB/trace/JJFAB_ora_13501.trc:
ORA-16038: 日志 1 sequence# 130 无法归档
ORA-19809: 超出了恢复文件数的限制
ORA-00312: 联机日志 1 线程 1: '/opt/oradata/gxwj/redo01.log'
Sat Feb 28 13:57:49 2015
ARC3 started with pid=23, OS id=13559 
USER (ospid: 13501): terminating the instance due to error 16038
Sat Feb 28 13:57:50 2015
System state dump requested by (instance=1, osid=13501), summary=[abnormal instance termination].
System State dumped to trace file /opt/app/oracle/diag/rdbms/jjfab/JJFAB/trace/JJFAB_diag_13471_20150228135750.trc
Dumping diagnostic data in directory=[cdmp_20150228135750], requested by (instance=1, osid=13501), summary=[abnormal instance termination].
Instance terminated by USER, pid = 13501

发现告警日志中已经明确给出了原因和建议。

3.根据实际情况采取合理的措施,这里我们先增加闪回区大小,把库启动起来。

SQL> startup mount
ORA-24324: 未初始化服务句柄
ORA-01041: 内部错误, hostdef 扩展名不存在
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期六 2月 28 14:05:59 2015

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2257520 bytes
Variable Size            2365590928 bytes
Database Buffers          822083584 bytes
Redo Buffers               16904192 bytes
Database mounted.
SQL> show parameter db_recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/app/oracle/fast_recovery_
                                                 area
db_recovery_file_dest_size           big integer 4122M
SQL> !
$ df -h /opt/
文件系统              容量  已用  可用 已用%% 挂载点
/dev/mapper/vg_extend-lv_opt
                      493G  423G   45G  91% /opt
$ exit
exit

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> 

4.制定对应的归档日志删除策略

SQL> set linesize 120
SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                      41.54                         0             133
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.
可以rman直接删除不再需要的归档,比如删除1天之前的归档
RMAN> delete noprompt archivelog until time 'sysdate-1';
此处省略删除归档的输出..
RMAN> exit

Recovery Manager complete.

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                        .35                         0               1
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

当然还可以设置crontab定时每天删除1天前的归档:

$ crontab -l
00 04 * * * /opt/shell/del_arch.sh
$ more /opt/shell/del_arch.sh
#!/bin/bash
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/11.2.0.4/dbhome_1 
export ORACLE_SID=JJFAB
export NLS_LANG="simplified chinese_china.ZHS16GBK"
export NLS_DATE_FORMAT="YYYY -MM-DD HH24:Mi:SS"
export PATH=.:/opt/app/oracle/product/11.2.0.4/dbhome_1:/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

rman target / log=/opt/backup/del_arch.log <<EOF
list archivelog all;
delete noprompt archivelog until time 'sysdate-1';
show all;
list archivelog all;
EOF

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • RHEL7 配置iSCSI模拟环境

    在RHEL7.3中,对于lvm操作部分与之前RHEL6版本没有区别,还是创建pv,vg,lv的方法,命令参考如下:

    Alfred Zhao
  • Oracle基础维护01-常用管理命令总结

    此时,sga和pga自动调整,sga_target最小为4M,pga_aggregate_target最小为1M;

    Alfred Zhao
  • 记录一则ORACLE MOVE操作后重建索引过程被强制中断导致的ORA-8104案例

    对部分表进行Move操作之后,未重建对应的索引,会导致ORA-1502 索引不可用。

    Alfred Zhao
  • H5实现本地预览图片

    最后注意一点,H5实现图片预览只能使用原生JS进行DOM元素的操作,若使用jQuery则无法实现该效果。

    雪飞鸿
  • 腾讯云部署项目步骤

    最近,自己用Java WEB完成了一个网站,然而,不知道如何部署到腾讯服务器上。百度了很多,也没有完整的,后来自己摸索,完成了部署。本文,将介绍如果将web项目...

    用户6248011
  • CMU机器学习系主任Manuela Veloso加入摩根大通,华尔街金融巨头的AI投资

    机器之心
  • mysqldump与innobackupex备份过程你知多少(二)

    1.2.3. 使用WITH CONSISTENT SNAPSHOT子句的作用 START TRANSACTION语句使用WITH CONSISTENT SNA...

    沃趣科技
  • 互联网寒冬大裁员,AI跻身2019年最赚钱职业TOP10(附薪酬表)

    最近,美国薪酬统计网站PayScale发布了一份报告,盘点了2018年最热门的职业,并预测2019年需求量最大的领域——云计算、AI。

    新智元
  • 一名在斯坦福教授TensorFlow教师的“忏悔”:我觉得自己像个骗子

    【新智元导读】 来自斯坦福教授“深度学习研究中的 TensorFlow”课程的一名教师的“忏悔”,细数她在AI热潮中的一些经历,或多或少也反映了当下AI 行业中...

    新智元
  • 我觉得自己像个骗子-来自斯坦福教授的自述

    有些事我必须坦白,我感觉自己像个江湖骗子。 我每隔几天都会收到来自朋友,朋友介绍的朋友,或者某家公司的什么人的邮件,请我谈谈有关人工智能的看法。这些人中有刚卖掉...

    IT派

扫码关注云+社区

领取腾讯云代金券