Solution to bad partitions in the tablespace of the Oracle8i rollback segment

When I arrived at the company this morning, I received a complaint from the Netcom customer, saying that the network management database had a problem, the database had bad blocks, and some data in the rollback segment could not be read. I need to help solve this problem.

I checked the swappalrt. Log File and found the following errors:

Tue Sep 21 10:34:08 2004
Errors in file E:/Oracle/admin/swapp/bdump/sw1_mon. TRC:
ORA-01578: Oracle data block upted (file #2, block #24497)
ORA-01110: data file 2: 'e:/Oracle/oradata/swapp/rbs01.dbf'

It turns out that the tablespace data file in the rollback segment has bad blocks. Now that I know the problem, I can solve it right away. I have already thought about how to create a rollback segment tablespace and drop the previously broken rollback segment tablespace, create a rollback segment in the tablespace of the new rollback segment. The rollback segment to be created is the same as the previous one, write the generated rollback data to the new rollback segment. The train of thought was clear, and action began immediately.

First, stop listener, do not allow new applications to be connected to the database, and then drop the database. In order to clear the existing database session connection resources:

$ 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:
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 (start the database in restricted mode. To prevent other users from logging in, only the Administrator is allowed to log in)
Oracle instance started.

Total system global area 1134141116 bytes
Fixed size 102076 bytes
Variable Size 311750656 bytes
Database buffers 819200000 bytes
Redo buffers 3088384 bytes
Database mounted.
Database opened.

 

Find the tablespace corresponding to the rollback segment:

 

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

12 rows selected.

The tablespace In the rollback segment is RBS.

Check whether any activity exists in the tablespace of the current rollback segment:

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.

No activity, great. You can safely drop the rollback segment, which is exactly what I want.

Next, find the storage parameter information of the rollback segment:
SQL> Col tablespace_name format A10
SQL> Col segment_name format A12
SQL & gt; Set line 120
SQL> select segment_name, owner, tablespace_name, initial_extent, next_extent, min_extents, max_extents, pct_increase from dba_rollback_segs;

Segment_name owner tablespace initial_extent next_extent min_extents max_extents pct_increase
---------------------------------------------------------------------------------------
System sys system 57344 57344 2 505 0
Rbs0 public rbs01 524288 524288 8 4096 0
Rbs1 public rbs01 524288 524288 8 4096 0
Rbs2 public rbs01 524288 524288 8 4096 0
Rbs3 public rbs01 524288 524288 8 4096 0
Rbs4 public rbs01 524288 524288 8 4096 0
Rbs5 public rbs01 524288 524288 8 4096 0
Rbs6 public rbs01 524288 524288 8 4096 0
Rbs7 public rbs01 524288 524288 8 4096 0
Rbs8 public rbs01 524288 524288 8 4096 0
Rbs9 public rbs01 524288 524288 8 4096 0
Rbs10 public rbs01 524288 524288 8 4096 0
Rbs11 public rbs01 524288 524288 8 4096 0
Rbs12 public rbs01 524288 524288 8 4096 0
Rbs13 public rbs01 524288 524288 8 4096 0
Rbs14 public rbs01 524288 524288 8 4096 0
Rbs15 public rbs01 524288 524288 8 4096 0
Rbs16 public rbs01 524288 524288 8 4096 0
Rbs17 public rbs01 524288 524288 8 4096 0
Rbs18 public rbs01 524288 524288 8 4096 0
Rbs19 public rbs01 524288 524288 8 4096 0
Rbs20 public rbs01 524288 524288 8 4096 0
Rbs21 public rbs01 524288 524288 8 4096 0
Rbs22 public rbs01 524288 524288 8 4096 0
Rbs23 public rbs01 524288 524288 8 4096 0
Rbs24 public rbs01 524288 524288 8 4096 0
Rbs26 public rbs01 524288 524288 8 4096 0
Rbs27 public rbs01 524288 524288 8 4096 0
Rbs28 public rbs01 524288 524288 8 4096 0
Rbs25 public rbs01 524288 524288 8 4096 0
Apprbs public rbs01 2097152 10485760 50 32765 0

31 rows selected.

Record the values of initial_extent, next_extent, min_extents, max_extents, and pct_increase, and then create a new rollback segment.

Create a tablespace for the rollback segment in the LMT management mode (my database is oracle817 ):

SQL> Create tablespace rbs01 datafile '/opt/Oracle/DB02/oradata/orcl/rbs01.dbf' size 1024 M autoextend on next 1 m maxsize unlimited extent management local;

Tablespace created.

Create a rollback segment rbs31 in the tablespace for a test:

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

Failed to build because of an error.

After checking Metalink, we found that the rollback segment cannot be created in the tablespace managed by LMT for Oracle8i, but 9i solved the problem.

Metalink:

Explanation
-----------
Rollback segments cannot be created in locally managed tablespaces (a new feature in Oracle 8.1) with allocation type of 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 9.

Next, drop the created rbs01 tablespace and recreate the rbs01 tablespace:

SQL> drop tablespace rbs01 including contents;

Tablespace dropped.

$ RM/opt/Oracle/DB02/oradata/orcl/rbs01.dbf (delete the tablespace data file where rbs01 is located)

SQL> Create tablespace rbs01 datafile '/opt/Oracle/DB02/oradata/orcl/rbs01.dbf' size 1024 m
Autoextend on next 1 m maxsize unlimited;
Tablespace created.

SQL> select extent_management from dba_tablespaces where tablespace_name = 'rbs01 ';

Extent_man
----------
Dictionary

This time, the tablespace is not from LMT, but from DMT!

The following is what we really want to do. In short, we have two words: Careful, because it is a production database, and we are afraid to be careless.

SQL> set feedback off
SQL> set pages 0
SQL> select 'alter rollback segment' | segment_name | 'offline; 'from dba_rollback_segs;

Make a script to remove all rollback segments except the system rollback segments offline, saving one by one. The script results are as follows:
Alter rollback segment rbs0 offline;
Alter rollback segment rbs1 offline;
Alter rollback segment rbs2 offline;
Alter rollback segment rbs3 offline;
Alter rollback segment rbs4 offline;
Alter rollback segment rbs5 offline;
Alter rollback segment rbs6 offline;
Alter rollback segment rbs7 offline;
Alter rollback segment rbs8 offline;
Alter rollback segment rbs9 offline;
Alter rollback segment rbs10 offline;
Alter rollback segment rbs11 offline;
Alter rollback segment rbs12 offline;
Alter rollback segment rbs13 offline;
Alter rollback segment rbs14 offline;
Alter rollback segment rbs15 offline;
Alter rollback segment rbs16 offline;
Alter rollback segment rbs17 offline;
Alter rollback segment rbs18 offline;
Alter rollback segment rbs19 offline;
Alter rollback segment rbs20 offline;
Alter rollback segment rbs21 offline;
Alter rollback segment rbs22 offline;
Alter rollback segment rbs23 offline;
Alter rollback segment rbs24 offline;
Alter rollback segment rbs25 offline;
Alter rollback segment rbs26 offline;
Alter rollback segment rbs27 offline;
Alter rollback segment rbs28 offline;
Alter rollback segment apprbs offline;

Then run the drop rollback segment script:
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;

The script is ready. Do not forget to execute it.

After execution, create a rollback segment under the new tablespace of the rollback segment. The storage parameters are consistent with those of the original one:
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;

I also made a script to avoid hitting it one by one!

The following large rollback segments should be created separately. In short, it is better to have a large rollback segment in the system, which will be used when there are many things.

SQL> Create public rollback segment apprbs tablespace rbs01 storage (initial 2097152 next 10485760 minextents 50 maxextents 32765 );
Rollback segment created.

View the status of the new rollback segment:

SQL> select segment_name, owner, status, tablespace_name from dba_rollback_segs;
System sys online system
Rbs0 public offline rbs01
Rbs1 public offline rbs01
Rbs2 public offline rbs01
Rbs3 public offline rbs01
Rbs4 public offline rbs01
Rbs5 public offline rbs01
Rbs6 public offline rbs01
Rbs7 public offline rbs01
Rbs8 public offline rbs01
Rbs10 public offline rbs01
Rbs11 public offline rbs01
Rbs12 public offline rbs01
Rbs13 public offline rbs01
Rbs14 public offline rbs01
Rbs15 public offline rbs01
Rbs16 public offline rbs01
Rbs17 public offline rbs01
Rbs18 public offline rbs01
Rbs19 public offline rbs01
Rbs20 public offline rbs01
Rbs21 public offline rbs01
Rbs22 public offline rbs01
Rbs23 public offline rbs01
Rbs24 public offline rbs01
Rbs26 public offline rbs01
Rbs27 public offline rbs01
Rbs28 public offline rbs01
Rbs25 public offline rbs01
Apprbs public offline rbs01
30 rows selected.

Except for system, all are in offline status.

Continue with the script to make the rollback segments other than 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;

After executing the preceding script, do not forget to delete the original undo tablespace RBS:

SQL> drop tablespace RBS including contents;

Tablespace dropped.

$ RM/opt/Oracle/DB02/oradata/orcl/RBS. DBF (delete the data file of the RBS tablespace)

The required work is completed here. Well, the rest will be reserved for data testing, and the database testing results will be completed tomorrow.