前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >案例：数据库open时报错ORA-1172，ORA-1151 处理

案例：数据库open时报错ORA-1172，ORA-1151 处理

作者头像
Alfred Zhao
发布于 2019-05-24 20:16:26
5100
发布于 2019-05-24 20:16:26
举报
文章被收录于专栏：从ORACLE起航，领略精彩的IT技术。从ORACLE起航，领略精彩的IT技术。

环境：OEL 5.7 + Oracle 10.2.0.5 Clusterware + Oracle 10.2.0.5 RAC 故障：数据库open时报错ORA-1172，ORA-1151

  • 1.故障详细
  • 2.原因定位
  • 3.尝试修复

1.故障详细

几年前的10gRAC实验环境，整体冷备迁移到新环境时，无法正常启动数据库，手工尝试也无法正常启动，报错如下：

代码语言：javascript
复制
Mon Aug 14 10:04:13 EDT 2017
ALTER DATABASE OPEN
This instance was first to open
Block change tracking file is current.
Mon Aug 14 10:04:14 EDT 2017
Beginning crash recovery of 2 threads
Mon Aug 14 10:04:14 EDT 2017
Started redo scan
Mon Aug 14 10:04:14 EDT 2017
Completed redo scan
 337 redo blocks read, 85 data blocks need recovery
Mon Aug 14 10:04:14 EDT 2017
Started redo application at
 Thread 1: logseq 21, block 71672
 Thread 2: logseq 17, block 33379
Mon Aug 14 10:04:14 EDT 2017
Recovery of Online Redo Log: Thread 1 Group 1 Seq 21 Reading mem 0
  Mem# 0: +ZHAOJINGYU/jy/onlinelog/group_1.262.839673937
  Mem# 1: +ZHAOJINGYU/jy/onlinelog/group_1.263.839673939
Mon Aug 14 10:04:14 EDT 2017
Recovery of Online Redo Log: Thread 2 Group 3 Seq 17 Reading mem 0
  Mem# 0: +ZHAOJINGYU/jy/onlinelog/group_3.269.839674171
  Mem# 1: +ZHAOJINGYU/jy/onlinelog/group_3.270.839674173
RECOVERY OF THREAD 1 STUCK AT BLOCK 41 OF FILE 2
Mon Aug 14 10:04:27 EDT 2017
Abort recovery for domain 0
Mon Aug 14 10:04:27 EDT 2017
Aborting crash recovery due to error 1172
Mon Aug 14 10:04:27 EDT 2017
Errors in file /s01/oracle/admin/jy/udump/jy1_ora_18982.trc:
ORA-01172: recovery of thread 1 stuck at block 41 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...
Mon Aug 14 10:04:30 EDT 2017
Shutting down instance (abort)
License high water mark = 1
Instance terminated by USER, pid = 19144

2.原因定位

根据MOS文档 Error ORA-01219 , ORA-01172, ORA-01151, ORA-01033 (文档 ID 1605148.1) 结合自己这里的实际情况，怀疑是之前数据库没有正常关闭导致。 试图在mount下进行recover database操作。

代码语言：javascript
复制
startup mount 
recover database;
ALTER DATABASE OPEN;

3.尝试修复

我这里实际解决过程如下：

3.1 查看状态发现数据库实例资源始终没有启动：

代码语言：javascript
复制
[oracle@rac1-server crsd]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.jy.db      application    ONLINE    OFFLINE               
ora....y1.inst application    ONLINE    OFFLINE               
ora....y2.inst application    ONLINE    OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    rac1-server 
ora....ER.lsnr application    ONLINE    ONLINE    rac1-server 
ora....ver.gsd application    ONLINE    ONLINE    rac1-server 
ora....ver.ons application    ONLINE    ONLINE    rac1-server 
ora....ver.vip application    ONLINE    ONLINE    rac1-server 
ora....SM2.asm application    ONLINE    ONLINE    rac2-server 
ora....ER.lsnr application    ONLINE    ONLINE    rac2-server 
ora....ver.gsd application    ONLINE    ONLINE    rac2-server 
ora....ver.ons application    ONLINE    ONLINE    rac2-server 
ora....ver.vip application    ONLINE    ONLINE    rac2-server

3.2 尝试手工启动数据库

代码语言：javascript
复制
[oracle@rac1-server crsd]$ srvctl start database -d jy
PRKP-1001 : Error starting instance jy1 on node rac1-server
CRS-0215: Could not start resource 'ora.jy.jy1.inst'.
PRKP-1001 : Error starting instance jy2 on node rac2-server
CRS-0215: Could not start resource 'ora.jy.jy2.inst'.

尝试手工启动数据库失败，再次查询状态：

代码语言：javascript
复制
[oracle@rac1-server crsd]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.jy.db      application    ONLINE    OFFLINE               
ora....y1.inst application    ONLINE    OFFLINE               
ora....y2.inst application    ONLINE    OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    rac1-server 
ora....ER.lsnr application    ONLINE    ONLINE    rac1-server 
ora....ver.gsd application    ONLINE    ONLINE    rac1-server 
ora....ver.ons application    ONLINE    ONLINE    rac1-server 
ora....ver.vip application    ONLINE    ONLINE    rac1-server 
ora....SM2.asm application    ONLINE    ONLINE    rac2-server 
ora....ER.lsnr application    ONLINE    ONLINE    rac2-server 
ora....ver.gsd application    ONLINE    ONLINE    rac2-server 
ora....ver.ons application    ONLINE    ONLINE    rac2-server 
ora....ver.vip application    ONLINE    ONLINE    rac2-server

3.3 根据MOS文档思路，将数据库启动到mount

代码语言：javascript
复制
[oracle@rac1-server crsd]$ srvctl start database -d jy -o mount;
[oracle@rac1-server crsd]$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.jy.db      application    ONLINE    ONLINE    rac1-server 
ora....y1.inst application    ONLINE    ONLINE    rac1-server 
ora....y2.inst application    ONLINE    ONLINE    rac2-server 
ora....SM1.asm application    ONLINE    ONLINE    rac1-server 
ora....ER.lsnr application    ONLINE    ONLINE    rac1-server 
ora....ver.gsd application    ONLINE    ONLINE    rac1-server 
ora....ver.ons application    ONLINE    ONLINE    rac1-server 
ora....ver.vip application    ONLINE    ONLINE    rac1-server 
ora....SM2.asm application    ONLINE    ONLINE    rac2-server 
ora....ER.lsnr application    ONLINE    ONLINE    rac2-server 
ora....ver.gsd application    ONLINE    ONLINE    rac2-server 
ora....ver.ons application    ONLINE    ONLINE    rac2-server 
ora....ver.vip application    ONLINE    ONLINE    rac2-server

3.4 尝试在mount下recover database

代码语言：javascript
复制
[oracle@rac1-server crsd]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 14 22:43:03 2017

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

最终成功recover并打开数据库。

3.5 节点2也将数据库open 节点2也将数据库open：

代码语言：javascript
复制
[oracle@rac2-server ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 14 22:45:17 2017

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.

SQL>

至此，这套10g RAC的数据库实验环境恢复正常。

本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2017-08-15 ，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
oracle
sql

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ，欢迎热爱写作的你一起参与！

数据库
oracle
sql
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 1.故障详细
  • 2.原因定位
  • 3.尝试修复
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建，轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量，更专注于业务发展，让企业一站式享受数据上云及分布式架构的技术红利！
产品介绍
精选特惠 用云无忧
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论