sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle undo丢失且存在未提交事务的恢复详细步骤

时间:2016-11-29 22:52   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:分享一篇关于Oracle数据库紧急恢复的文章,该文章详细记录了非归档Oracle数据库undo丢失且存在未提交事务的恢复详细步骤。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: undo丢失且存在未提交事务的恢复

对于非归档模式,无备份,abort方式关掉数据库且存在活动事务,使用常规方法进行恢复是行不通的,而且mos文档也说是无法进行恢复的,其实不然,不过本文这种方法oracle并不推荐,仅限于大家研究玩玩!

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        Oracleoracleplus.net               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.
下面我们将undotbs offline,看看status$会发生什么变化

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

准备工作已经做完了,下面我们回到以前的问题上来,在恢复的时候报错:
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'


不管怎么样,都无法open数据库,做10046 trace后,发现其实是在bootstrap$初始化就失败了,失败的sql如下:

SELECT /*+ rule */ name,file#,block#,STATUS$,USER#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1
  FROM undo$
WHERE us#=:1

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

下面再次尝试做该实验
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方式。++++++'

下面我们用bbed来尝试
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
我们知道当前可用的回滚段是11个(虽然这里显示为21个,其中有10已经被删除了),kdbr[0]是对应的 system 回滚段那也就是说, 从kdbr[1] 到 kdbr[10]就是我们的undotbs1

如下所示:
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

下面来看下成果
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

-- 丢失了部分未提交事务
既然open成功了,那剩下来的时间就是重建undo了。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle undo丢失且存在未提交事务的恢复详细步骤

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1343.html

Oracle研究中心

关键词:

Oracle bbed使用案例

Oracle undo数据文件损坏的恢复案例

紧急恢复非归档异常关闭的Oracle数据库