掉电引起的ORA-1172错误解决过程(三)

简介: 由于UPS故障,导致机房连续多次掉电,问题解决后,发现一台本地测试数据库打开时报错,ORA-1172、ORA-1151错误。 掉电引起的ORA-1172错误解决过程(一):http://yangtingkun.

由于UPS故障,导致机房连续多次掉电,问题解决后,发现一台本地测试数据库打开时报错,ORA-1172ORA-1151错误。

掉电引起的ORA-1172错误解决过程(一):http://yangtingkun.itpub.net/post/468/465223

掉电引起的ORA-1172错误解决过程(二):http://yangtingkun.itpub.net/post/468/465868

打开数据库后的处理:

 

 

在前一篇文章中已经成功打开数据库,其实这时从目标上已经基本完成了,只需通过EXP或者EXPDP工具将数据库中的用户导出,重建数据库,然后导入数据即可。

不过对于恢复来说,还有很多可以做的,检查数据库的回滚段状态:

SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS
  2  FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME    OWNER  TABLESPACE_NAME      STATUS
--------------- ------ -------------------- ----------------
SYSTEM          SYS    SYSTEM               ONLINE
_SYSSMU1$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU2$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU3$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU4$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU5$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU6$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU7$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU8$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU9$       PUBLIC UNDOTBS1             OFFLINE
_SYSSMU10$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU11$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU12$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU13$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU14$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU15$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU16$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU17$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU18$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU19$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU20$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU21$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU22$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU23$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU24$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU25$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU26$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU27$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU28$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU29$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU30$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU31$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU32$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU33$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU34$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU35$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU36$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU37$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU38$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU39$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU40$      PUBLIC UNDOTBS1             OFFLINE
_SYSSMU41$      PUBLIC UNDOTBS1             OFFLINE

42 rows selected.

可以发现除了SYSTEM回滚段,其他回滚段均为OFFLINE状态,这时所有的DML操作均回报错:

SQL> DELETE TEST.T;
DELETE TEST.T
            *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'GPO'

下面创建一个新的UNDO表空间,使得ORACLE有可用的UNDO表空间:

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/data/oradata/test08/undotbs21.dbf'           
  2  SIZE 4096M;

Tablespace created.

下面修改初始化参数文件,改变UNDO表空间为UNDOTBS2,并将UNDO管理设置为AUTO模式,注释掉隐含参数_corrupted_rollback_segments

*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS2’

在关闭数据库时出现了异常:

SQL> SHUTDOWN IMMEDIATE

等待了几个小时,SHUTDOWN IMMEDIATE方式仍然无法关闭数据库,检查alert文件发现信息如下:

Tue Jun 10 17:02:41 2008
Starting background process EMN0
EMN0 started with pid=16, OS id=15734
Tue Jun 10 17:02:41 2008
Shutting down instance: further logons disabled
Tue Jun 10 17:02:41 2008
Stopping background process CJQ0
Tue Jun 10 17:02:41 2008
Stopping background process MMNL
Tue Jun 10 17:02:42 2008
Stopping background process MMON
Tue Jun 10 17:02:43 2008
Shutting down instance (immediate)
License high water mark = 44
Tue Jun 10 17:02:43 2008
Stopping Job queue slave processes
Tue Jun 10 17:02:43 2008
Job queue slave processes stopped
All dispatchers and shared servers shutdown
Tue Jun 10 17:02:50 2008
Process OS id : 15693 alive after kill
Errors in file /opt/ora10g/admin/test08/udump/test08_ora_15629.trc

在另外的会话以SYSDBA登陆,利用SHUTDOWN ABORT关闭数据库,SHUTDOWN IMMEDIATE的会话信息如下:

ORA-03113: end-of-file on communication channel
SQL> STARTUP PFILE=/home/oracle/inittest08.ora
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> CONN / AS SYSDBA
Connected to an idle instance.
SQL> STARTUP PFILE=/home/oracle/inittest08.ora
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size                  2074760 bytes
Variable Size            1090520952 bytes
Database Buffers         1375731712 bytes
Redo Buffers               14700544 bytes
Database mounted.
Database opened.

数据库可以正常启动。下面删除UNDOTBS1表空间即可:

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DELETE TEST.T;

4051072 rows deleted.

SQL> COMMIT;

Commit complete.

不过由于数据库本身已经处于异常状态,后台仍然可以经常发现大量坏块:

Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_19485.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 32529)
ORA-01110: data file 1: '/data/oradata/test08/system01.dbf'
Wed Jun 11 08:58:29 2008
WARNING: inbound connection timed out (ORA-3136)
Wed Jun 11 09:02:29 2008
Hex dump of (file 3, block 37871) in trace file /opt/ora10g/admin/test08/bdump/test08_m000_19556.trc
Corrupt block relative dba: 0x00c093ef (file 3, block 37871)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00c093ef
 last change scn: 0x0001.81e5b9f5 seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb56b0601
 check value in block header: 0x9fc
 computed block checksum: 0xdd4
Reread of rdba: 0x00c093ef (file 3, block 37871) found same corrupted data
Hex dump of (file 3, block 35683) in trace file /opt/ora10g/admin/test08/bdump/test08_m000_19556.trc
Corrupt block relative dba: 0x00c08b63 (file 3, block 35683)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00c08b63
 last change scn: 0x0001.856e48fc seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x7ead0601
 check value in block header: 0x1214
 computed block checksum: 0x3404
Reread of rdba: 0x00c08b63 (file 3, block 35683) found same corrupted data
Hex dump of (file 3, block 40608) in trace file /opt/ora10g/admin/test08/bdump/test08_m000_19556.trc
Corrupt block relative dba: 0x00c09ea0 (file 3, block 40608)
Fractured block found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x00c09ea0
 last change scn: 0x0001.856e48fc seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x65c20601
 check value in block header: 0x9ef1
 computed block checksum: 0x7a6b
Reread of rdba: 0x00c09ea0 (file 3, block 40608) found same corrupted data
Wed Jun 11 09:02:30 2008
Corrupt Block Found
         TSN = 2, TSNAME = SYSAUX
         RFN = 3, BLK = 37871, RDBA = 12620783
         BJN = 8933, BJD = 8933, BJECT = WRH$_SQLTEXT, SUBOBJECT =
         SEGMENT WNER = SYS, SEGMENT TYPE = Table Segment
Corrupt Block Found
         TSN = 2, TSNAME = SYSAUX
         RFN = 3, BLK = 35683, RDBA = 12618595
         BJN = 8943, BJD = 8943, BJECT = WRH$_SQL_BIND_METADATA, SUBOBJECT =
         SEGMENT WNER = SYS, SEGMENT TYPE = Table Segment
Wed Jun 11 09:07:30 2008
Errors in file /opt/ora10g/admin/test08/bdump/test08_smon_19485.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 32529)
ORA-01110: data file 1: '/data/oradata/test08/system01.dbf'
Wed Jun 11 09:07:32 2008
Corrupt Block Found
         TSN = 2, TSNAME = SYSAUX
         RFN = 3, BLK = 40608, RDBA = 12623520
         BJN = 8939, BJD = 8939, BJECT = WRH$_SQL_PLAN, SUBOBJECT =
         SEGMENT WNER = SYS, SEGMENT TYPE = Table Segment

因此,虽然数据库已经可以使用,但是为了防止数据库的进一步损坏,还是通过导出、重建、导入的方式比较稳妥。

 

 

相关文章
|
C# Windows
2000条你应知的WPF小姿势 基础篇<74-77 WPF 多窗口Tips>
2000条你应知的WPF小姿势 基础篇<74-77 WPF 多窗口Tips>
191 0
|
8月前
|
运维 安全 Linux
阿里云操作系统迁移最佳实践
本次分享的主题是社区操作系统迁移和阿里云操作系统迁移。由龙溪社区王喆分享。 1. 操作系统生命周期 2. 操作系统迁移方案 3. 操作系统迁移流程 4. 操作系统迁移评估 5. 一站式迁移运维平台SysOM 6. SMC操作系统迁移实践
275 2
|
11月前
|
存储 关系型数据库 MySQL
mysql 8.0 时间维度表生成(可运行)
mysql 8.0 时间维度表生成(可运行)
343 0
|
数据采集
LabVIEW Actor架构特点与适用范围
LabVIEW Actor架构特点与适用范围
179 1
|
IDE 开发工具
使用STM32F103标准库实现自定义键盘
通过本文,我们学习了如何使用STM32F103标准库实现一个简单的自定义键盘。我们首先初始化了GPIO引脚,然后实现了一个扫描函数来检测按键状态。这个项目不仅能够帮助我们理解STM32的GPIO配置和按键扫描原理,还可以作为进一步学习中断处理和低功耗设计的基础。希望本文对你有所帮助,祝你在嵌入式开发的道路上不断进步!
947 4
|
编解码 Oracle iOS开发
VirtualBox虚拟机安装Mac OS X Lion系统详解
VirtualBox虚拟机安装Mac OS X Lion系统详解
689 1
|
缓存 Shell 索引
LabVIEW文件和文件夹资源管理器Tree Control
LabVIEW文件和文件夹资源管理器Tree Control
169 0
|
存储 算法 程序员
内存管理(二)——连续分配管理方式
内存管理(二)——连续分配管理方式
657 0
|
SQL Oracle 关系型数据库
掉电引起的ORA-1172错误解决过程(二)
由于UPS故障,导致机房连续多次掉电,问题解决后,发现一台本地测试数据库打开时报错,ORA-1172、ORA-1151错误。 掉电引起的ORA-1172错误解决过程(一):http://yangtingkun.itpub.net/post/468/465223 尝试打开数据库。
1535 0
|
传感器 vr&ar 开发工具
Baumer工业相机VCX系列相机硬件触发流程和设置
Baumer工业相机VCX系列相机硬件触发流程和设置
3502 0