« Exadata现场演示与锁机制－ACOUG 7月活动小记 | Blog首页 | 《循序渐进Oracle》第二版一书相关代码 »

Oracle数据恢复:SYSTEM回滚段损坏案例一则

Thu Jul 07 15:18:20 CST 2011

ARC0: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2

Thu Jul 07 15:18:20 CST 2011

ORA-01555 caused by SQL statement below (SQL ID: 7bd391hat42zk, Query Duration=0 sec, SCN: 0x000a.79ed044d):

Thu Jul 07 15:18:20 CST 2011

select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1

Thu Jul 07 15:18:20 CST 2011

Errors in file /home/oracle/oracle/admin/EDB01/udump/edb01_ora_1208.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small

Error 604 happened during db open, shutting down database

USER: terminating instance due to error 604

Instance terminated by USER, pid = 1208

ORA-1092 signalled during: alter database open...

select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1

SQL> select segment_name,block_id,blocks from dba_extents where segment_name='SYSTEM';

SEGMENT_NAME                     BLOCK_ID     BLOCKS

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

SYSTEM                                  9          8

SYSTEM                                 17          8

SYSTEM                                385          8

SYSTEM                                393          8

SYSTEM                                401          8

SYSTEM                                409          8

create tablespace SYSTEM datafile "D_DBFN" 

  "D_DSTG" online

/

create rollback segment SYSTEM tablespace SYSTEM

  storage (initial 50K next 50K)

/

When a database is first created using the CREATE DATABASE command, only a single rollback segment is created.  

This is the system rollback segment and it is created in the system tablespace.  

The system rollback segment has one basic difference from any other rollback segment, including any other rollback segments that are created in the system tablespace.  

This difference is that the system rollback segment can only be used for transactions that occur on objects inside the system tablespace.  

This is done because the main purpose of the system rollback segment is to handle rollback for DDL transactions - that is transactions against the data dictionary tables themselves.  Making the system rollback usable only for the system tablespace was simply an easy way to enforce that.  

It is possible for the system rollback segment to be used for non-data dictionary tables, but only if those tables are created inside the system tablespace (which is very bad development practice).

When other rollback segments are available, the SYSTEM rollback segment is only used for the changes to UNDO$ associated with bringing other rollback segments online, or taking them offline.

By eygle on 2011-08-05 14:14 | Comments (1) | Backup&Recovery | 2847 |

Recovery