查看: 1428|回复: 9

oracle 11g 集群异常关闭

[复制链接]
论坛徽章:
4
优秀写手
日期:2014-04-04 06:00:14秀才
日期:2015-09-10 09:29:01秀才
日期:2016-12-21 16:55:07娜美
日期:2018-02-09 12:04:16
发表于 2018-4-16 10:17 | 显示全部楼层 |阅读模式


现象:

oracle 11g  rac (11.2.0.4) 异常关闭,告警日志
节点一二告警日志:

节点一.txt

116.76 KB, 下载次数: 4

节点一

节点二.txt

61.48 KB, 下载次数: 3

节点二

论坛徽章:
4
优秀写手
日期:2014-04-04 06:00:14秀才
日期:2015-09-10 09:29:01秀才
日期:2016-12-21 16:55:07娜美
日期:2018-02-09 12:04:16
 楼主| 发表于 2018-4-16 10:21 | 显示全部楼层
本帖最后由 gh_95533 于 2018-4-16 10:28 编辑

重启机器后,数据库自动启动,但是报了在新的日志中报错信息是
Errors in file /home/u01/app/oracle/diag/rdbms/nbdb/nbdb1/trace/nbdb1_m000_10907.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4621074432 bytes is 93.81% used, and has 286261248 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.
************************************************************************
Sat Apr 14 18:47:04 2018
Starting background process CJQ0
Sat Apr 14 18:47:04 2018
CJQ0 started with pid=119, OS id=10951
Setting Resource Manager plan SCHEDULER[0x32DE]EFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Apr 14 18:47:08 2018
Starting background process VKRM
Sat Apr 14 18:47:08 2018
VKRM started with pid=116, OS id=10972
Sat Apr 14 18:47:10 2018
Starting background process SMCO
Sat Apr 14 18:47:10 2018
SMCO started with pid=157, OS id=11040
Sat Apr 14 18:47:27 2018
Decreasing number of real time LMS from 4 to 0
Reconfiguration started (old inc 2, new inc 4)
List of instances:
1 2 (myinst: 1)
Global Resource Directory frozen
Communication channels reestablished
Sat Apr 14 18:47:37 2018
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Sat Apr 14 18:47:37 2018
LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Sat Apr 14 18:47:37 2018
LMS 3: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Sat Apr 14 18:47:37 2018
LMS 2: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Sat Apr 14 18:47:37 2018
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Submitted all GCS remote-cache requests
Fix write in gcs resources
Reconfiguration complete
Sat Apr 14 18:47:38 2018
minact-scn: Master returning as live inst:2 has inc# mismatch instinc:0 cur:4 errcnt:0
Sat Apr 14 18:48:05 2018
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Apr 14 18:48:55 2018
Increasing number of real time LMS from 0 to 4
Sat Apr 14 18:54:43 2018


***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 11.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 14-APR-2018 18:54:43
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
   
TNS-12535: TNSperation timed out
    ns secondary err code: 12606
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.194)(PORT=7934))
WARNING: inbound connection timed out (ORA-3136)
Sat Apr 14 19:01:30 2018
Errors in file /home/u01/app/oracle/diag/rdbms/nbdb/nbdb1/trace/nbdb1_m000_16306.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4621074432 bytes is 93.81% used, and has 286261248 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.
************************************************************************
Sat Apr 14 19:08:35 2018
alter tablespace TS_ZYK_DATA
add datafile '+DATA/nbdb/datafile/ts_zyk_data_16g_56' size 16g
Sat Apr 14 19:13:01 2018
Completed: alter tablespace TS_ZYK_DATA
add datafile '+DATA/nbdb/datafile/ts_zyk_data_16g_56' size 16g
Sat Apr 14 19:40:50 2018
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 134217728 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
select total_size,awr_flush_emergency_count from v$ash_info;
Sat Apr 14 19:44:39 2018
Thread 1 advanced to log sequence 40285 (LGWR switch)
  Current log# 1 seq# 40285 mem# 0: +DATA/nbdb/onlinelog/group_1.269.900627419
  Current log# 1 seq# 40285 mem# 1: +DATA/nbdb/onlinelog/group_1.268.900627419
Sat Apr 14 19:44:48 2018
Archived Log entry 66834 added for thread 1 sequence 40284 ID 0x155d0c54 dest 1:
Sat Apr 14 20:23:13 2018
opiodr aborting process unknown ospid (17879) as a result of ORA-28
Sat Apr 14 20:23:27 2018
SMON: Restarting fast_start parallel rollback
Sat Apr 14 20:23:49 2018
SMON: Parallel transaction recovery tried
Sat Apr 14 20:37:44 2018
opiodr aborting process unknown ospid (48156) as a result of ORA-28
Sat Apr 14 20:37:58 2018
SMON: Parallel transaction recovery tried
Sat Apr 14 20:38:02 2018
Thread 1 advanced to log sequence 40286 (LGWR switch)
  Current log# 2 seq# 40286 mem# 0: +DATA/nbdb/onlinelog/group_2.265.900627421
  Current log# 2 seq# 40286 mem# 1: +DATA/nbdb/onlinelog/group_2.264.900627421
Sat Apr 14 20:38:08 2018
Archived Log entry 66836 added for thread 1 sequence 40285 ID 0x155d0c54 dest 1:
Sat Apr 14 21:04:14 2018
db_recovery_file_dest_size of 8192 MB is 50.46% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Sat Apr 14 21:04:14 2018

使用道具 举报

回复
论坛徽章:
4
优秀写手
日期:2014-04-04 06:00:14秀才
日期:2015-09-10 09:29:01秀才
日期:2016-12-21 16:55:07娜美
日期:2018-02-09 12:04:16
 楼主| 发表于 2018-4-16 10:22 | 显示全部楼层
我将闪回区更改大小:
ALTER SYSTEM SET db_recovery_file_dest_size='8192M' SCOPE=BOTH;
Sat Apr 14 21:07:21 2018
Thread 1 advanced to log sequence 40287 (LGWR switch)
  Current log# 1 seq# 40287 mem# 0: +DATA/nbdb/onlinelog/group_1.269.900627419
  Current log# 1 seq# 40287 mem# 1: +DATA/nbdb/onlinelog/group_1.268.900627419
Sat Apr 14 21:07:28 2018
Archived Log entry 66837 added for thread 1 sequence 40286 ID 0x155d0c54 dest 1:
Sat Apr 14 21:14:05 2018
alter tablespace SYSAUX
add datafile '+DATA/nbdb/datafile/sysaux_8g_2' size 8g
Sat Apr 14 21:16:36 2018
Completed: alter tablespace SYSAUX
add datafile '+DATA/nbdb/datafile/sysaux_8g_2' size 8g
Sat Apr 14 21:28:11 2018
Thread 1 advanced to log sequence 40288 (LGWR switch)
  Current log# 2 seq# 40288 mem# 0: +DATA/nbdb/onlinelog/group_2.265.900627421
  Current log# 2 seq# 40288 mem# 1: +DATA/nbdb/onlinelog/group_2.264.900627421
Sat Apr 14 21:28:23 2018
Archived Log entry 66839 added for thread 1 sequence 40287 ID 0x155d0c54 dest 1:

使用道具 举报

回复
论坛徽章:
4
优秀写手
日期:2014-04-04 06:00:14秀才
日期:2015-09-10 09:29:01秀才
日期:2016-12-21 16:55:07娜美
日期:2018-02-09 12:04:16
 楼主| 发表于 2018-4-16 10:22 | 显示全部楼层
后面数据库开始正常入库数据了

使用道具 举报

回复
论坛徽章:
4
优秀写手
日期:2014-04-04 06:00:14秀才
日期:2015-09-10 09:29:01秀才
日期:2016-12-21 16:55:07娜美
日期:2018-02-09 12:04:16
 楼主| 发表于 2018-4-16 10:26 | 显示全部楼层
是因为闪回区空间导致数据库异常关闭吗?

使用道具 举报

回复
论坛徽章:
1
秀才
日期:2017-02-22 15:16:26
发表于 2018-4-16 10:46 | 显示全部楼层
Errors in file /home/u01/app/oracle/diag/rdbms/nbdb/nbdb2/trace/nbdb2_ckpt_24227.trc:
ORA-00206: error in writing (block 856, # blocks 1) of control file
ORA-00202: control file: '+DATA/nbdb/controlfile/current.270.900627417'
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk
Errors in file /home/u01/app/oracle/diag/rdbms/nbdb/nbdb2/trace/nbdb2_ckpt_24227.trc:
ORA-00221: error on write to control file
ORA-00206: error in writing (block 856, # blocks 1) of control file
ORA-00202: control file: '+DATA/nbdb/controlfile/current.270.900627417'
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk
Errors in file /home/u01/app/oracle/diag/rdbms/nbdb/nbdb2/trace/nbdb2_lgwr_24225.trc:
ORA-00345: redo log write error block 1076629 count 3
ORA-00312: online log 3 thread 2: '+DATA/nbdb/onlinelog/group_3.259.900627521'
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk
CKPT (ospid: 24227): terminating the instance due to error 221

磁盘IO异常??

使用道具 举报

回复
论坛徽章:
4
优秀写手
日期:2014-04-04 06:00:14秀才
日期:2015-09-10 09:29:01秀才
日期:2016-12-21 16:55:07娜美
日期:2018-02-09 12:04:16
 楼主| 发表于 2018-4-16 11:04 | 显示全部楼层
本帖最后由 gh_95533 于 2018-4-16 11:05 编辑

其实当时不是很清楚,当然现在也是不清楚,就是只知道数据库异常关闭后,我重启操作数据库机器,存储没有重启,数据库能自动起来后,报新的问题是闪回区快满了。然后有点紧张,但是修改该参数又要重启数据库,没想到该参数及时生效,数据库后面就变得正常了

使用道具 举报

回复
论坛徽章:
4
优秀写手
日期:2014-04-04 06:00:14秀才
日期:2015-09-10 09:29:01秀才
日期:2016-12-21 16:55:07娜美
日期:2018-02-09 12:04:16
 楼主| 发表于 2018-4-17 11:47 | 显示全部楼层
嗯,自己顶一下

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2018-4-17 14:37 | 显示全部楼层
这个问题是由于 数据库闪回区的空间满了 数据库会挂起 这个问题在RAC环境下很容易出现,只是单纯的设置
db_recovery_file_dest_size 的大小 治标不治本,这种情况需要通过rman 下去删除归档日志,因为rac归档日志\
通常是放在
USE_DB_RECOVERY_FILE_DEST下面
通过如下命令解决 最好是建成时间任务

rman target /

crosscheck archivelog all;
delete noprompt until time 'sysdate-1';

就可以解决这个问题

使用道具 举报

回复
论坛徽章:
4
优秀写手
日期:2014-04-04 06:00:14秀才
日期:2015-09-10 09:29:01秀才
日期:2016-12-21 16:55:07娜美
日期:2018-02-09 12:04:16
 楼主| 发表于 2018-4-17 14:47 | 显示全部楼层
我执行的命令是

rman target /

crosscheck archivelog all;

delete archivelog all completed before 'sysdate-7';

当时缓冲区并没有释放空间大小

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表