快捷搜索:  test  as

Oracle8i回滚段表空间的坏块(1) - 回滚段

问:当数据库呈现坏块,回滚段里的部分数据不能读取时,应该用什么措施办理。

答: 详细措施如下;

首先该当反省一下swappALRT.log文件,会发明以下差错:

Tue Sep 21 10:34:08 2004

Errors in file E:oracleadminswappdumpswappSMON.TRC:

ORA-01578: ORACLE data block corrupted (file # 2, block # 24497)

ORA-01110: data file 2: 'E:ORACLEORADATASWAPPRBS01.DBF'

此种征象阐明回滚段表空间数据文件呈现了坏块。这时必要新建一个回滚段表空间,把曩昔坏了的回滚段表空间drop掉落,在新的回滚段表空间上建回滚段,所要建的回滚段和曩昔的一摸一样,让今后孕育发生的回滚数据都写到新建的回滚段上。

首先停掉落listener,不容许有新的利用连到数据库上做操作,然后down掉落数据库,为了清除掉落已有的数据库会话连接资本:

$lsnrctl stop

LSNRCTL for Solaris: Version 8.1.7.3.0 - Production

on 21-SEP-2004 17:40:36

(c) Copyright 1998 Oracle Corporation.? All rights

reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(

HOST=ipasdb)(PORT=1521)))

The command completed successfully.

$sqlplus internal/oracle

SQL*Plus: Release 8.1.7.0.0 - Production on Tue

Sep 21 17:41:24 2004

(c) Copyright 2000 Oracle Corporation.?

All rights reserved.

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit

Production

With the Partitioning option

JServer Release 8.1.7.3.0 - 64bit Production

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>startup restrict (以受限模式启动数据库,为了防止其他用户登岸进来做相关操作,这时刻只容许治理员登岸)

查找回滚段对应的表空间:

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME??????????????? STATUS

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

SYSTEM???????????????????????? ONLINE

TOOLS????????????????????????? ONLINE

RBS??????????????????????????? ONLINE

TEMP?????????????????????????? ONLINE

USERS????????????????????????? ONLINE

INDX?????????????????????????? ONLINE

DRSYS????????????????????????? ONLINE

WACOS????????????????????????? ONLINE

NMS??????????????????????????? ONLINE

TEST?????????????????????????? ONLINE

FS???????????????????????????? ONLINE

PERFSTAT?????????????????????? ONLINE

12rows selected.

回滚段表空间为RBS.

查看当前回滚段表空间里是否有活动的事物:

SQL> SELECT s.username,t.xidusn,t.ubafil,

t.ubablk,t.used_ublk? FROM

v$session s,v$transaction t WHERE s.saddr=t.ses_addr;

no rows selected.

发明没有活动事物后,drop回滚段。

接下来查找回滚段存储参数信息:

SQL> col tablespace_name format a10

SQL> col SEGMENT_NAME format a12

SQL> set line 120

SQL> select SEGMENT_NAME,OWNER,TABLESPACE_NAME,

initial_extent,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,

PCT_INCREASE from dba_rollback_segs;

31 rows selected.

把initial_extent,next_extent,min_extents,max_extents,pct_increase的值都记录下来,留做今后创建新的回滚段应用。

创建LMT治理要领的回滚段表空间(我的数据库是oracle817):

SQL> create tablespace

rbs01 datafile '/opt/oracle/db02/oradata/ORCL/rbs01.dbf'

size 1024M?autoextend on next 1M maxsize unlimited extent

management local;

Tablespace created.

先在该表空间下建立一个回滚段rbs31做一个测试:

SQL> create public rollback segment RBS31 tablespace rbs01

storage(initial

524288

next 524288 MINEXTENTS 8 MAXEXTENTS 4096 OPTIMAL 4194304);

create public rollback segment RBS31 tablespace rbs01

storage(initial

524288 next

524288 MINEXTENTS 8 MAXEXTENTS 4096 OPTIMAL 4194304)

*

ERROR at line 1:

ORA-25151: Rollback Segment cannot be created in this tablespace

呈现上例阐明掉足了,没有立建成功。

这阐明对付oracle8i来讲在LMT要领治理的表空间下不能创建回滚段,但9i办理了该问题。

metalink上的解释:

Explanation

-----------

Rollback segments cannot be created in locally

managed tablespaces (a new feature in Oracle 8.1) with allocation

typeof AUTOALLOCATE. They must be created in locally managed

tablespaces with allocation type of UNIFORM or in

dictionary managed tablespaces.

NOTE: This restriction has been lifted in Oracle

然后做个drop回滚段的脚本:

SQL>? select 'drop rollback

segment '||segment_name||';' from dba_rollback_segs;

drop rollback segment RBS0;

drop rollback segment RBS1;

drop rollback segment RBS2;

drop rollback segment RBS3;

drop rollback segment RBS4;

drop rollback segment RBS5;

drop rollback segment RBS6;

drop rollback segment RBS7;

drop rollback segment RBS8;

drop rollback segment RBS9;

drop rollback segment RBS10;

drop rollback segment RBS11;

drop rollback segment RBS12;

drop rollback segment RBS13;

drop rollback segment RBS14;

drop rollback segment RBS15;

drop rollback segment RBS16;

drop rollback segment RBS17;

drop rollback segment RBS18;

drop rollback segment RBS19;

drop rollback segment RBS20;

drop rollback segment RBS21;

drop rollback segment RBS22;

drop rollback segment RBS23;

drop rollback segment RBS24;

drop rollback segment RBS25;

drop rollback segment RBS26;

drop rollback segment RBS27;

drop rollback segment RBS28;

drop rollback segment APPRBS;

脚本做好了,履行。

履行完后开始在新的回滚段表空间下建回滚段,存储参数和原本维持同等:

SQL> select? 'create public rollback

segment '||segment_name||' tablespace rbs01 storage

(initial 524288 next 524288 MINEXTENTS 8

MAXEXTENTS 4096 OPTIMAL 4194304); from dba_rollback_segs;

除了system,都是offline状态。

继承做脚本让除system外的回滚段online:

SQL> select 'alter rollback segment

'||segment_name||' online;'? from dba_rollback_segs;

alter rollback segment RBS0 online;

alter rollback segment RBS1 online;

alter rollback segment RBS2 online;

alter rollback segment RBS3 online;

alter rollback segment RBS4 online;

alter rollback segment RBS5 online;

alter rollback segment RBS6 online;

alter rollback segment RBS7 online;

alter rollback segment RBS8 online;

alter rollback segment RBS9 online;

alter rollback segment RBS10 online;

alter rollback segment RBS11 online;

alter rollback segment RBS12 online;

alter rollback segment RBS13 online;

alter rollback segment RBS14 online;

alter rollback segment RBS15 online;

alter rollback segment RBS16 online;

alter rollback segment RBS17 online;

alter rollback segment RBS18 online;

alter rollback segment RBS19 online;

alter rollback segment RBS20 online;

alter rollback segment RBS21 online;

alter rollback segment RBS22 online;

alter rollback segment RBS23 online;

alter rollback segment RBS24 online;

alter rollback segment RBS26 online;

alter rollback segment RBS27 online;

alter rollback segment RBS28 online;

alter rollback segment RBS25 online;

alter rollback segment APPRBS online;

履行以上脚本后,删除原本的undo表空间RBS:

SQL>drop tablespace rbs including contents;

Tablespace dropped.

操作完成。

您可能还会对下面的文章感兴趣: