当前位置:Gxlcms > mysql > 【Oracle】undo损坏,无备份非常规恢复

【Oracle】undo损坏,无备份非常规恢复

时间:2021-07-01 10:21:17 帮助过:52人阅读

客户的一个测试环境,主机异常断电,启动后发现undo文件损坏,无法启动,在open阶段报错如下: Errors in file /u01/app/oracle/diag/rdbms/cdrdb/CDRDB/trace/CDRDB_ora_4109.trc: ORA-01122: database file 3 failed verification check ORA-01110: data f

客户的一个测试环境,主机异常断电,启动后发现undo文件损坏,无法启动,在open阶段报错如下:

Errors in file /u01/app/oracle/diag/rdbms/cdrdb/CDRDB/trace/CDRDB_ora_4109.trc:

ORA-01122: database file 3 failed verification check

ORA-01110: data file 3: '/u01/app/oracle/oradata/CDRDB/undotbs01.dbf'

ORA-01210: data file header is media corrupt

ORA-1122 signalled during: ALTER DATABASE OPEN...

由于是测试环境,没有备份,但是又需要里边的一些数据,所以我尝试使用非常规恢复方法进行了尝试。

先冷备份现有环境!!!!!!!!!!!!!!!!

创建pfile文件:

create pfile from spfile;

在pfile中修改这两个参数

#*.undo_tablespace='UNDOTBS1'

*.undo_management= MANUAL

之后用这个pfile启动:

SYS@CDRDB>startup force pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initCDRDB.ora';

ORACLE instance started.

Total System Global Area 523108352 bytes

Fixed Size 1337632 bytes

Variable Size 364906208 bytes

Database Buffers 150994944 bytes

Redo Buffers 5869568 bytes

Database mounted.

ORA-01122: database file 3 failed verification check

ORA-01110: data file 3: '/u01/app/oracle/oradata/CDRDB/undotbs01.dbf'

ORA-01210: data file header is media corrupt

仍然报错

之后尝试drop掉这个undo

SYS@CDRDB>alter database datafile 3 offline drop;

Database altered.

之后重新开库

SYS@CDRDB>alter database open;

Database altered.

之后创建新的undo表空间undotbs2

SYS@CDRDB>create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/CDRDB/undotbs02_01.dbf' size 100M;

create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/CDRDB/undotbs02_01.dbf' size 100M

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace

'DATA_OL'

ORA-06512: at line 999

ORA-01552: cannot use system rollback segment for non-system tablespace

'DATA_OL'

产生报错如上

比较疑惑创建undo为什么会影响到DATA_OL表空间,所以做了个10046

SYS@CDRDB>oradebug event 10046 trace name context off

Statement processed.

SYS@CDRDB>oradebug tracefile_name

/u01/app/oracle/diag/rdbms/cdrdb/CDRDB/trace/CDRDB_ora_4279.trc

[oracle@centos-1 ~]$ tkprof /u01/app/oracle/diag/rdbms/cdrdb/CDRDB/trace/CDRDB_ora_4279.trc

output = 1.trm

查看1.trm,发现了原因:

"OGG".DDLReplication.dbQueried IS NULL THEN

SELECT database_role,

open_mode

INTO dbRole, dbOpenMode

FROM v$database;

"OGG".DDLReplication.dbQueried := TRUE;

END IF;

IF NOT (

^@ (dbRole = 'PRIMARY' OR dbRole = 'LOGICAL STANDBY')

AND dbOpenMode =

'READ WRITE'

)

THEN

-- do not write any trace even though it

should work as this is standby

"OGG"

.DDLReplication.setCtxInfo(-1,-1,-1,-1,-1);

RETURN; -- do not use

trigger if not read/write and primary/logical_standby

END IF;

EXCEPTION

......略

原来是因为这个库配置过OGG的DDL同步,有DDL产生时会产生insert操作,使用DATA_OL表空间。

原因找到~ 跑脚本关闭该库OGG的DDL配置即可

SYS@CDRDB>@ddl_disable.sql

Trigger altered.

之后再重新创建undotbs2表空间

SYS@CDRDB>create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/CDRDB/undotbs02_01.dbf' size 1000M;

Tablespace created.

这次很顺利的添加成功。

undotbs2添加完毕,接下来关闭数据库,修改pfile参数

*.undo_tablespace='UNDOTBS2'

*.undo_management=AUTO

重启数据库用pfile启动

SYS@CDRDB>startup pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initCDRDB.ora';

ORACLE instance started.

Total System Global Area 523108352 bytes

Fixed Size 1337632 bytes

Variable Size 364906208 bytes

Database Buffers 150994944 bytes

Redo Buffers 5869568 bytes

Database mounted.

Database opened.

数据库成功启动,然而在很多业务表查询时依然会报错:

ERROR at line 1:

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/u01/app/oracle/oradata/CDRDB/undotbs01.dbf'

依然需要原来的undotbs01.dbf回滚数据库崩溃时未提交的事务

之后我先尝试了设置event 10513,来屏蔽smon的回滚

SYS@CDRDB>alter system set events '10513 trace name context forever, level 2';

System altered.

发现还是会报之前的错误,看来需要自己手工去屏蔽回滚段了。

SYS@CDRDB>SELECT SEGMENT_NAME,STATUS FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME STATUS

------------------------------ ----------------

SYSTEM ONLINE

_SYSSMU10_4131489474$ NEEDS RECOVERY

_SYSSMU9_1735643689$ NEEDS RECOVERY

_SYSSMU8_3901294357$ NEEDS RECOVERY

_SYSSMU7_3517345427$ NEEDS RECOVERY

_SYSSMU6_2897970769$ NEEDS RECOVERY

_SYSSMU5_538557934$ NEEDS RECOVERY

_SYSSMU4_1003442803$ NEEDS RECOVERY

_SYSSMU3_1204390606$ NEEDS RECOVERY

_SYSSMU2_967517682$ NEEDS RECOVERY

_SYSSMU1_592353410$ NEEDS RECOVERY

SEGMENT_NAME STATUS

------------------------------ ----------------

_SYSSMU30_244658789$ ONLINE

_SYSSMU29_1020880693$ ONLINE

_SYSSMU28_2912622077$ ONLINE

_SYSSMU27_747253598$ ONLINE

_SYSSMU26_560868814$ ONLINE

_SYSSMU25_1357066082$ ONLINE

_SYSSMU24_103440716$ ONLINE

_SYSSMU23_1006903361$ ONLINE

_SYSSMU22_2808190508$ ONLINE

_SYSSMU21_39626587$ ONLINE

21 rows selected.

之后把查询到的NEEDS RECOVERY的表加到以下两个参数中,屏蔽这些回滚段

_offline_rollback_segments/_corrupted_rollback_segments参数

*._offline_rollback_segments=(_SYSSMU10_4131489474$,_SYSSMU9_1735643689$,_SYSSMU8_3901294357$,_SYSSMU7_3517345427$,_SYSSMU6_2897970769$,_SYSSMU5_538557934$,_SYSSMU4_1003442803$,_SYSSMU3_1204390606$,_SYSSMU2_967517682$,_SYSSMU1_592353410$)

*._corrupted_rollback_segments=(_SYSSMU10_4131489474$,_SYSSMU9_1735643689$,_SYSSMU8_3901294357$,_SYSSMU7_3517345427$,_SYSSMU6_2897970769$,_SYSSMU5_538557934$,_SYSSMU4_1003442803$,_SYSSMU3_1204390606$,_SYSSMU2_967517682$,_SYSSMU1_592353410$)

使用这个pfile启动数据库

startup force pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initCDRDB.ora';

之后对那些测试表都查询了一下,确认可以查询,之后导出帮他们导出数据,一切OK~

人气教程排行