undo丢失且存在未提交事务的恢复
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: undo丢失且存在未提交事务的恢复
|
1 2 3 |
这篇文章其实很早之前就写过了，在旧博客中，今天刚刚有同事提到这个问题，所以就转载到www.killdb.com上。 对于非归档模式，无备份，abort方式关掉数据库且存在活动事务，使用常规方法进行恢复是行不通的， 而且mos文档也说是无法进行恢复的，其实不然，不过本文这种方法oracle并不推荐，仅限于大家研究玩玩！ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 |
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) blk# 3 from undo$; FILE# BLK# ---------- ---------- 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 1 106 21 rows selected. SQL> desc undo$ Name Null? Type -------------------------- -------- -------------------------------------------- US# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) USER# NOT NULL NUMBER FILE# NOT NULL NUMBER BLOCK# NOT NULL NUMBER SCNBAS NUMBER SCNWRP NUMBER XACTSQN NUMBER UNDOSQN NUMBER INST# NUMBER STATUS$ NOT NULL NUMBER <== 回滚段状态 TS# NUMBER UGRP# NUMBER KEEP NUMBER OPTIMAL NUMBER FLAGS NUMBER SPARE1 NUMBER SPARE2 NUMBER SPARE3 NUMBER SPARE4 VARCHAR2(1000) SPARE5 VARCHAR2(1000) SPARE6 DATE SQL> conn /as sysdba Connected. SQL> select owner, segment_name, TABLESPACE_NAME, status 2 from dba_rollback_segs; OWNER SEGMENT_NAME TABLESPACE_NAME STATUS ------ ------------------------------ ------------------------------ ---------------- SYS SYSTEM SYSTEM ONLINE PUBLIC _SYSSMU1$ UNDOTBS1 ONLINE PUBLIC _SYSSMU2$ UNDOTBS1 ONLINE PUBLIC _SYSSMU3$ UNDOTBS1 ONLINE PUBLIC _SYSSMU4$ UNDOTBS1 ONLINE PUBLIC _SYSSMU5$ UNDOTBS1 ONLINE PUBLIC _SYSSMU6$ UNDOTBS1 ONLINE PUBLIC _SYSSMU7$ UNDOTBS1 ONLINE PUBLIC _SYSSMU8$ UNDOTBS1 ONLINE PUBLIC _SYSSMU9$ UNDOTBS1 ONLINE PUBLIC _SYSSMU10$ UNDOTBS1 ONLINE 11 rows selected. SQL> select NAME, STATUS$ 2 from undo$; NAME STATUS$ ------------------------------ ---------- SYSTEM 3 _SYSSMU1$ 3 _SYSSMU2$ 3 _SYSSMU3$ 3 _SYSSMU4$ 3 _SYSSMU5$ 3 _SYSSMU6$ 3 _SYSSMU7$ 3 _SYSSMU8$ 3 _SYSSMU9$ 3 <== 从这里 我们可以猜测出 3 表是online _SYSSMU10$ 3 _SYSSMU11$ 1 _SYSSMU12$ 1 _SYSSMU13$ 1 _SYSSMU14$ 1 _SYSSMU15$ 1 _SYSSMU16$ 1 _SYSSMU17$ 1 _SYSSMU18$ 1 _SYSSMU19$ 1 _SYSSMU20$ 1 21 rows selected. |
|
1 |
下面我们将undotbs offline，看看status$会发生什么变化 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
SQL> select owner, segment_name, TABLESPACE_NAME, status 2 from dba_rollback_segs; OWNER SEGMENT_NAME TABLESPACE_NAME STATUS ------ ------------------------------ ------------------------------ ---------------- SYS SYSTEM SYSTEM ONLINE PUBLIC _SYSSMU1$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU3$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU4$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU5$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU6$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU7$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU8$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU9$ UNDOTBS1 OFFLINE PUBLIC _SYSSMU10$ UNDOTBS1 OFFLINE 11 rows selected. SQL> select NAME, STATUS$ from undo$; NAME STATUS$ ------------------------------ ---------- SYSTEM 3 _SYSSMU1$ 2 _SYSSMU2$ 2 _SYSSMU3$ 2 _SYSSMU4$ 2 _SYSSMU5$ 2 _SYSSMU6$ 2 _SYSSMU7$ 2 _SYSSMU8$ 2 <== 从这里我们可以知道2表示offline _SYSSMU9$ 2 _SYSSMU10$ 2 _SYSSMU11$ 1 _SYSSMU12$ 1 _SYSSMU13$ 1 _SYSSMU14$ 1 _SYSSMU15$ 1 _SYSSMU16$ 1 _SYSSMU17$ 1 <== 结合下面，我们可以看出1表示已经删除或不存在 _SYSSMU18$ 1 _SYSSMU19$ 1 _SYSSMU20$ 1 21 rows selected. SQL> alter system dump undo header '_SYSSMU11$'; alter system dump undo header '_SYSSMU11$' * ERROR at line 1: ORA-01534: rollback segment '_SYSSMU11$' doesn't exist |
|
1 |
准备工作已经做完了，下面我们回到以前的问题上来，在恢复的时候报错： |
|
1 2 3 4 5 |
Thread 1: Sequence reset to 1. ORA-00704: bootstrap process failure ORA-00604: error occurred at recursive SQL level 2 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/oracle/product/oradata/roger/undotbs.dbf' |
|
1 |
不管怎么样，都无法open数据库，做10046 trace后，发现其实是在bootstrap$初始化就失败了，失败的sql如下： |
|
1 2 3 |
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
BBED> set file 1 block 106 FILE# 1 BLOCK# 106 BBED> p kdbr sb2 kdbr[0] @86 8079 sb2 kdbr[1] @88 5277 sb2 kdbr[2] @90 5221 sb2 kdbr[3] @92 5165 sb2 kdbr[4] @94 5109 sb2 kdbr[5] @96 4429 sb2 kdbr[6] @98 4997 sb2 kdbr[7] @100 4940 sb2 kdbr[8] @102 4598 sb2 kdbr[9] @104 4826 sb2 kdbr[10] @106 4540 sb2 kdbr[11] @108 7471 sb2 kdbr[12] @110 7417 sb2 kdbr[13] @112 7363 sb2 kdbr[14] @114 7309 sb2 kdbr[15] @116 7255 sb2 kdbr[16] @118 7201 sb2 kdbr[17] @120 7146 sb2 kdbr[18] @122 7091 sb2 kdbr[19] @124 7036 sb2 kdbr[20] @126 6981 BBED> p *kdbr[1] rowdata[848] ------------ ub1 rowdata[848] @5345 0x2c BBED> x /1rnnnnnnnn rowdata[848] @5345 ------------ flag@5345: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@5346: 0x00 cols@5347: 17 col 0[2] @5348: 1 col 1[9] @5351: -0 col 2[2] @5361: 1 col 3[2] @5364: 2 col 4[2] @5367: 9 col 5[5] @5370: 1263443 col 6[1] @5376: 0 col 7[3] @5378: 335 col 8[3] @5382: 474 col 9[1] @5386: 0 col 10[2] @5388: 3 <== 对照前面undo$的结构, 我们可以发现这里应该是对应的 status$ col 11[2] @5391: 1 col 12[0] @5394: *NULL* col 13[0] @5395: *NULL* col 14[0] @5396: *NULL* col 15[0] @5397: *NULL* col 16[2] @5398: 1 |
|
1 |
下面再次尝试做该实验 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
SQL> startup ORACLE instance started. Total System Global Area 234881024 bytes Fixed Size 1266776 bytes Variable Size 100666280 bytes Database Buffers 130023424 bytes Redo Buffers 2924544 bytes Database mounted. Database opened. SQL> conn roger1/roger1 Connected. SQL> select count(*) from t1; COUNT(*) ---------- 907 SQL> delete from t1 where rownum <100; 99 rows deleted. SQL> conn /as sysdba Connected. SQL> select file_name, status 2 from dba_data_files; FILE_NAME STATUS ------------------------------------------------------------ --------- /oracle/product/oradata/roger/users01.dbf AVAILABLE /oracle/product/oradata/roger/sysaux01.dbf AVAILABLE /oracle/product/oradata/roger/undotbs01.dbf AVAILABLE /oracle/product/oradata/roger/system01.dbf AVAILABLE /oracle/product/oradata/roger/roger01.dbf AVAILABLE SQL> shutdown abort; ORACLE instance shut down. SQL> ! rm /oracle/product/oradata/roger/undotbs01.dbf SQL> !ls -ltr /oracle/product/oradata/roger total 1483044 -rw-r----- 1 oracle dba 20979712 Mar 27 14:55 temp01.dbf -rw-r----- 1 oracle dba 7872512 Mar 27 15:32 users01.dbf -rw-r----- 1 oracle dba 524296192 Mar 27 15:32 system01.dbf -rw-r----- 1 oracle dba 272637952 Mar 27 15:32 sysaux01.dbf -rw-r----- 1 oracle dba 524296192 Mar 27 15:32 roger01.dbf -rw-r----- 1 oracle dba 52429312 Mar 27 15:32 redo03.log -rw-r----- 1 oracle dba 52429312 Mar 27 15:32 redo02.log -rw-r----- 1 oracle dba 52429312 Mar 27 15:33 redo01.log -rw-r----- 1 oracle dba 7061504 Mar 27 15:33 control03.ctl -rw-r----- 1 oracle dba 7061504 Mar 27 15:33 control02.ctl -rw-r----- 1 oracle dba 7061504 Mar 27 15:33 control01.ctl '++++++ 存在未提交事务，直接rm undodatafile，且是abort方式。++++++' |
|
1 |
下面我们用bbed来尝试 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
BBED> p kdbr sb2 kdbr[0] @86 8079 sb2 kdbr[1] @88 5277 sb2 kdbr[2] @90 5221 sb2 kdbr[3] @92 5165 sb2 kdbr[4] @94 4319 sb2 kdbr[5] @96 4374 sb2 kdbr[6] @98 4997 sb2 kdbr[7] @100 4940 sb2 kdbr[8] @102 4598 sb2 kdbr[9] @104 4826 sb2 kdbr[10] @106 4540 sb2 kdbr[11] @108 7471 sb2 kdbr[12] @110 7417 sb2 kdbr[13] @112 7363 sb2 kdbr[14] @114 7309 sb2 kdbr[15] @116 7255 sb2 kdbr[16] @118 7201 sb2 kdbr[17] @120 7146 sb2 kdbr[18] @122 7091 sb2 kdbr[19] @124 7036 sb2 kdbr[20] @126 6981 |
|
1 2 3 4 |
我们知道当前可用的回滚段是11个（虽然这里显示为21个，其中有10已经被删除了），kdbr[0]是对应的 system 回滚段 那也就是说, 从kdbr[1] 到 kdbr[10]就是我们的undotbs1 如下所示： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 |
BBED> p *kdbr[1] rowdata[958] ------------ ub1 rowdata[958] @5345 0x2c BBED> x /1rncnnnnnnnnnnn rowdata[958] @5345 ------------ flag@5345: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@5346: 0x01 cols@5347: 17 col 0[2] @5348: 1 col 1[9] @5351: _SYSSMU1$ col 2[2] @5361: 1 col 3[2] @5364: 2 col 4[2] @5367: 9 col 5[5] @5370: 1286346 col 6[1] @5376: 0 col 7[3] @5378: 337 col 8[3] @5382: 474 col 9[1] @5386: 0 col 10[2] @5388: 2 col 11[2] @5391: 1 col 12[0] @5394: *NULL* col 13[0] @5395: *NULL* col 14[0] @5396: *NULL* col 15[0] @5397: *NULL* col 16[2] @5398: 1 BBED> p *kdbr[2] rowdata[902] ------------ ub1 rowdata[902] @5289 0x2c BBED> x /1rncnnnnnnnnnnnnnn rowdata[902] @5289 ------------ flag@5289: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@5290: 0x01 cols@5291: 17 col 0[2] @5292: 2 col 1[9] @5295: _SYSSMU2$ col 2[2] @5305: 1 col 3[2] @5308: 2 col 4[2] @5311: 25 col 5[5] @5314: 1286342 col 6[1] @5320: 0 col 7[3] @5322: 366 col 8[3] @5326: 273 col 9[1] @5330: 0 col 10[2] @5332: 2 col 11[2] @5335: 1 col 12[0] @5338: *NULL* col 13[0] @5339: *NULL* col 14[0] @5340: *NULL* col 15[0] @5341: *NULL* col 16[2] @5342: 1 下面用bbed进行修改 BBED> p *kdbr[1] rowdata[958] ------------ ub1 rowdata[958] @5345 0x2c BBED> x /1rncnnnnnnnnnnn rowdata[958] @5345 ------------ flag@5345: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@5346: 0x01 cols@5347: 17 col 0[2] @5348: 1 col 1[9] @5351: _SYSSMU1$ col 2[2] @5361: 1 col 3[2] @5364: 2 col 4[2] @5367: 9 col 5[5] @5370: 1286346 col 6[1] @5376: 0 col 7[3] @5378: 337 col 8[3] @5382: 474 col 9[1] @5386: 0 col 10[2] @5388: 2 col 11[2] @5391: 1 col 12[0] @5394: *NULL* col 13[0] @5395: *NULL* col 14[0] @5396: *NULL* col 15[0] @5397: *NULL* col 16[2] @5398: 1 BBED> modify /x 02 offset 5390 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /oracle/product/oradata/roger/system01.dbf (1) Block: 106 Offsets: 5390 to 5901 Dba:0x0040006a ------------------------------------------------------------------------ 0202c102 ffffffff 02c1022c 001102c1 0a095f53 5953534d 55392402 c10202c1 0303c202 2604c363 14580180 03c2041d 02c20401 8002c104 02c102ff ffffff02 c1022c00 1102c106 095f5359 53534d55 352402c1 0202c103 02c14a04 c3631534 018003c2 041c03c2 04200180 02c10402 c102ffff ffff02c1 022c0011 02c10b0a 5f535953 534d5531 302402c1 0202c103 03c20236 04c36315 0b018003 c2032d03 c2036301 8002c104 02c102ff ffffff02 c1022c00 1102c106 095f5359 53534d55 352402c1 0202c103 02c14a03 c35b2201 8003c204 1103c204 13018002 c10402c1 02ffffff ff02c102 2c001102 c10b0a5f 53595353 4d553130 2402c102 02c10303 c2023603 c3591001 8003c203 2103c203 54018002 c10402c1 02ffffff ff02c102 2c001102 c109095f 53595353 4d553824 02c10202 c10303c2 021604c3 63152801 8003c203 6203c204 2d018002 c10402c1 02ffffff ff02c102 2c001102 c10a095f 53595353 4d553924 02c10202 c10303c2 022604c3 60512a01 8003c204 1a03c203 62018002 c10402c1 02ffffff ff02c102 2c001102 c109095f 53595353 4d553824 02c10202 c10303c2 021604c3 50345301 8003c203 4802c204 018002c1 0402c102 ffffffff 02c1022c 001102c1 05095f53 5953534d 55342402 c10202c1 0302c13a 04c36315 24018003 c2033103 c2031601 8002c104 02c102ff ffffff02 c1022c00 <32 bytes per line> ###### 中间的 kdbr[2] ~kdbr[9] 省略 ###### BBED> p *kdbr[10] rowdata[221] ------------ ub1 rowdata[221] @4608 0x2c BBED> x /1rncnnnnnnnnnnn rowdata[221] @4608 ------------ flag@4608: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@4609: 0x01 cols@4610: 17 col 0[2] @4611: 10 col 1[10] @4614: _SYSSMU10$ col 2[2] @4625: 1 col 3[2] @4628: 2 col 4[3] @4631: 153 col 5[5] @4635: 1286340 col 6[1] @4641: 0 col 7[3] @4643: 284 col 8[3] @4647: 340 col 9[1] @4651: 0 col 10[2] @4653: 2 col 11[2] @4656: 1 col 12[0] @4659: *NULL* col 13[0] @4660: *NULL* col 14[0] @4661: *NULL* col 15[0] @4662: *NULL* col 16[2] @4663: 1 BBED> modify /x 02 offset 4655 File: /oracle/product/oradata/roger/system01.dbf (1) Block: 106 Offsets: 4655 to 5166 Dba:0x0040006a ------------------------------------------------------------------------ 0202c102 ffffffff 02c1022c 011102c1 09095f53 5953534d 55382402 c10202c1 0303c202 1605c402 1d403201 8003c204 2803c204 59018002 c10202c1 02ffffff ff02c102 2c001102 c109095f 53595353 4d553824 02c10202 c10303c2 021605c4 0203552c 018002c2 0403c204 2f018002 c10402c1 02ffffff ff02c102 2c001102 c10b0a5f 53595353 4d553130 2402c102 02c10303 c2023605 c4020355 2a018003 c2033102 c2040180 02c10402 c102ffff ffff02c1 022c0011 02c10b0a 5f535953 534d5531 302402c1 0202c103 03c20236 05c40201 211c0180 03c2032e 03c20364 018002c1 0402c102 ffffffff 02c1022c 011102c1 0a095f53 5953534d 55392402 c10202c1 0303c202 2605c402 1d402d01 8003c204 4c03c204 35018002 c10202c1 02ffffff ff02c102 2c001102 c109095f 53595353 4d553824 02c10202 c10303c2 021605c4 02012213 018003c2 036203c2 042d0180 02c10402 c102ffff ffff02c1 022c0111 02c10809 5f535953 534d5537 2402c102 02c10303 c2020605 c4021d40 34018003 c2035c03 c2040801 8002c102 02c102ff ffffff02 c1022c01 1102c107 095f5359 53534d55 362402c1 0202c103 02c15a05 c4021d40 31018003 c2046203 c2042c01 8002c102 02c102ff ffffff02 c1022c00 1102c106 095f5359 53534d55 352402c1 0202c103 02c14a05 c4020d25 2b018003 c2043103 c2043f01 8002c104 <32 bytes per line> BBED> sum apply Check value for File 1, Block 106: current = 0xbb06, required = 0xbb06 BBED> exit |
|
1 |
下面来看下成果 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 |
SQL> startup mount ORACLE instance started. Total System Global Area 234881024 bytes Fixed Size 1266776 bytes Variable Size 100666280 bytes Database Buffers 130023424 bytes Redo Buffers 2924544 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/oracle/product/oradata/roger/undotbs01.dbf' SQL> alter database datafile '/oracle/product/oradata/roger/undotbs01.dbf' offline drop; Database altered. SQL> startup mount pfile='/oracle/a.ora'; ORACLE instance started. Total System Global Area 234881024 bytes Fixed Size 1266776 bytes Variable Size 100666280 bytes Database Buffers 130023424 bytes Redo Buffers 2924544 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-01172: recovery of thread 1 stuck at block 106 of file 1 ORA-01151: use media recovery to recover block, restore backup if needed SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-00600: internal error code, arguments: [3020], [1], [106], [4194410], [], [], [], [] ORA-10567: Redo is inconsistent with data block (file# 1, block# 106) ORA-10564: tablespace SYSTEM ORA-01110: data file 1: '/oracle/product/oradata/roger/system01.dbf' ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 15 SQL> recover database using backup controlfile until cancel; ORA-00279: change 1307218 generated at 03/27/2011 16:00:26 needed for thread 1 ORA-00289: suggestion : /oracle/archroger/1_101_740404448.dbf ORA-00280: change 1307218 for thread 1 is in sequence #101 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/oracle/archroger/1_101_740404448.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00308: cannot open archived log '/oracle/archroger/1_101_740404448.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/product/oradata/roger/system01.dbf' SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/oracle/product/oradata/roger/system01.dbf' SQL> startup mount pfile='/oracle/a.ora'; -- 其中我使用了隐含参数 ORACLE instance started. Total System Global Area 234881024 bytes Fixed Size 1266776 bytes Variable Size 100666280 bytes Database Buffers 130023424 bytes Redo Buffers 2924544 bytes Database mounted. SQL> alter database open resetlogs; Database altered. SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1 SQL> startup ORACLE instance started. Total System Global Area 234881024 bytes Fixed Size 1266776 bytes Variable Size 100666280 bytes Database Buffers 130023424 bytes Redo Buffers 2924544 bytes Database mounted. Database opened. SQL> select owner, segment_name, TABLESPACE_NAME, status 2 from dba_rollback_segs; OWNER SEGMENT_NAME TABLESPACE_NAME STATUS ------ ------------------------------ ------------------------------ ---------------- SYS SYSTEM SYSTEM ONLINE SQL> select NAME, STATUS$ 2 from undo$; NAME STATUS$ --------------- ---------- SYSTEM 3 _SYSSMU1$ 1 _SYSSMU2$ 1 _SYSSMU3$ 1 _SYSSMU4$ 1 _SYSSMU5$ 1 _SYSSMU6$ 1 _SYSSMU7$ 1 _SYSSMU8$ 1 _SYSSMU9$ 1 _SYSSMU10$ 1 _SYSSMU11$ 1 _SYSSMU12$ 1 _SYSSMU13$ 1 _SYSSMU14$ 1 _SYSSMU15$ 1 _SYSSMU16$ 1 _SYSSMU17$ 1 _SYSSMU18$ 1 _SYSSMU19$ 1 _SYSSMU20$ 1 21 rows selected. SQL> conn roger1/roger1 Connected. SQL> select count(*) from t1; COUNT(*) ---------- 808 -- 丢失了部分未提交事务 |
|
1 |
既然open成功了，那剩下来的时间就是重建undo了。 |
6 Responses to “undo丢失且存在未提交事务的恢复”
牛B
领导~！
SQL> startup mount pfile=’/oracle/a.ora’; — 其中我使用了隐含参数
ORACLE instance started.
Total System Global Area 234881024 bytes
Fixed SIZE 1266776 bytes
Variable SIZE 100666280 bytes
DATABASE Buffers 130023424 bytes
Redo Buffers 2924544 bytes
DATABASE mounted.
这里使用了哪几个隐含参数啊？我试了许多都不行啊~！指导一下？
to yangjianwei：
就这两个参数：
*._allow_resetlogs_corruption=TRUE
*._allow_error_simulation=TRUE
顶
这么好的博客。
壬辰年(龙)正月廿六 2012-2-17
又多了一个偶像。
Leave a Reply
You must be logged in to post a comment.