I just arrived at Netcom customers this morning, I said that the network management database has a problem. The database has a bad block. The part of the data in the rollback segment cannot be read, and it is necessary to help solve it.
I have viewed the swappalrt.log file and found the following error:
Tue Sep 21 10:34:08 2004erroPs in file e: otta penppsmon.trc: ora-01578: Oracle Data Block Corrupted (File # 2, Block # 24497) ORA-01110: Data File 2 : 'E: /oracle/oradata/swapp/rbs01.dbf'
It turned out that there was a bad block in the tablespace data file. I know the problem, I'm trying to solve it, I have already thought about it. It is to create a new table space, drop the back of the table space before, and built back on the new rollback segment table space. Segment, the rollback segment you want to build and the previous touch, let the rollback data generated later to the newly built returns. The idea is clear and started immediately.
First stop to listner, no new applications are connected to the database, then DOWN off 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 = (pecol = tcp) (host = ipasdb)) 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 ProductionWith the Partitioning optionJServer Release 8.1.7.3.0 - 64bit Production SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down SQL> startup restrict (in restricted mode start the database, in order to prevent other users from landing came in to do related operations, this time only allows administrators to log in) ORACLE instance started. Total System Global Area 1134141116 bytesFixed Size 102076 bytesVariable Size 311750656 bytesDatabase Buffers 819200000 bytesRedo Buffers 3088384 bytesDatabase mounted.Database opened. Finding back Table space corresponding to roll: SQL> SELECT TABLESPACE_NAME, STATUS from DBA_TABLESPACES; TABLESPACE_NAME STATUS ------------------------------------------------------------------------------------------------------------------------------------------------------------ ------- System OnlineTools Onlinerbs OnlineTemp OnlineUsers Onlineindx OnlineDrSys Onlinewacos OnlinenMs OnlineTest Online 12Rows SELINEPERFSTAT Online 12Rows SELECTED. Strong segment table space is RBS. See if there is a event in the table space: 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. There is a good thing, you can rest assured that the drop returns, this is the result I want. . Then look rollback segment storage parameter information: SQL> col tablespace_name format a10SQL> col SEGMENT_NAME format a12SQL> set line 120SQL> select SEGMENT_NAME, OWNER, TABLESPACE_NAME, initial_extent, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE from dba_rollback_segs;
Segment_name oowner tablespace initial_extent next_extent min_extents max_extents PCT_INCREASE ---------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------- System Sys system 57344 57344 2 505 0RBS0 PUBLIC RBS01 524288 524288 8 4096 0RBS1 PUBLIC RBS01 524288 524288 8 4096 0RBS2 PUBLIC RBS01 524288 524288 8 4096 0RBS3 PUBLIC RBS01 524288 524288 8 4096 0RBS4 PUBLIC RBS01 524288 524288 8 4096 0RBS5 PUBLIC RBS01 524288 524288 8 4096 0RBS6 PUBLIC RBS01 524288 524288 8 4096 0RBS7 Public RBS01 524288 524288 8 4096 0RBS8 PUBLIC RBS01 524288 524288 8 4096 0RBS9 PUBLIC RBS01 524288 524288 8 4096 0RBS10 PUBLIC RBS01 524288 524288 8 4096 0RBS11 PUBLIC RBS01 524288 524288 8 4096 0RBS12 PUBLIC RBS01 524288 524288 8 4096 0RBS13 PUBLIC RBS01 524288 524288 8 4096 0RBS14 PUBLIC RBS01 524288 524288 8 4096 0RBS15 public RBS01 524288 524288
8 4096 0RBS16 PUBLIC RBS01 524288 524288 8 4096 0RBS17 PUBLIC RBS01 524288 524288 8 4096 0RBS18 PUBLIC RBS01 524288 524288 8 4096 0RBS19 PUBLIC RBS01 524288 524288 8 4096 0RBS20 PUBLIC RBS01 524288 524288 8 4096 0RBS21 PUBLIC RBS01 524288 524288 8 4096 0RBS22 PUBLIC RBS01 524288 524288 8 4096 0RBS23 public RBS01 524288 524288 8 4096 0RBS24 public RBS01 524288 524288 8 4096 0RBS26 public RBS01 524288 524288 8 4096 0RBS27 public RBS01 524288 524288 8 4096 0RBS28 PUBLIC RBS01 524288 524288 8 4096 0RBS25 PUBLIC RBS01 524288 524288 8 4096 0APPRBS PUBLIC RBS01 2097152 10485760 50 32765 0 31 rows selected. The initial_extent, next_extent, min_extents, max_extents, pct_increase values are recorded, created after leaving do New rollback segments are used. Create a slit segment table space for the LMT management (my database is Oracle817): SQL> CREATE TABLESPACE RBS01 DATAFILE '/OPT/OrCle/db02/oradata/orcl/rbs01.dbf' size 1024m Autoextend On Next 1M MaxSize Unlimited Extent Management Local; TableSpace Created. Create a rollover segment RBS31 in the table space to do a test: SQL> CREATE PUBLIC ROLLBACK SEGME
nt 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 table table, actually did not build success, Shit. Checked METALINK Discovery to create a return segment for Oracle8i management table space management, but 9i solves the problem. Explanation on metalink: Explanation ----------- Rollback segments can not 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. Then drop the newly established rbs01 table space to re-establish rbs01 table space: SQL> drop tablespace rbs01 including contents; tablespace dropped $ rm /. OPT / ORACLE / DB02 / ORADATA / ORCL / RBS01.DBF (Delete RBS01 "Space Data File) SQL> Create TableSpace RBS01 DataFile '/opt/oracle/db02/oradata/orcl/rbs01.dbf' Size 1024M Autoextend on Next NEXT 1M MaxSize Unlimited; TableSpace Created. SQL> SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'RBS01'; EXTENT_MAN ---------- Dictionary This return space is not LMT, it is DMT, huh, huh! Here is the work that really begins to do, in turn two words, careful, because it is a production library, do not dare to hit the horse. SQL> Set Feedback Offsql> Set Pages 0Sql> SELECT 'ALTER ROLLBACK Segment' || Segment_name || 'Offline;' from dba_rollback_segs; make a script to roll back to the SYSTEM rollback segment is OFFLINE, one one knock, 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 segmen
t 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; and then do DROP returns: 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 Rbs 20; Drop Rollback Segment Rbs22; Drop Rollback Segment Rbs23; Drop Rollback Segment Rbs24; Drop Rollback Segment Rbs25; Drop Rollback SEG
Ment Rbs26; Drop Rollback Segment Rbs27; Drop Rollback Segment Rbs28; Drop Rollback Segment AppRBS; Script is done, don't forget to execute. Started under the new rollback segment table space, store the parameters, store the parameters and the original consistent: SQL> SELECT '|| segment_name ||' TableSpace RBS01 Storage (Initial 524288 Next 524288 Mineltents 8 MaxExtents 4096 Optimal 4194304); From dba_rollback_segs; also made a script, free one knock! The next big back to the segment is to be built separately. In short, it is best to have a big returning segment. When you have something big, you will be used in the event. SQL> create public rollback segment APPRBS tablespace rbs01 storage (initial 2097152 next 10485760 MINEXTENTS 50 MAXEXTENTS 32765); Rollback segment created to view the new rollback state:. SQL> select segment_name, owner, status, tablespace_name from dba_rollback_segs; SYSTEM SYS ONLINE SYSTEMRBS0 PUBLIC OFFLINE RBS01RBS1 PUBLIC OFFLINE RBS01RBS2 PUBLIC OFFLINE RBS01RBS3 PUBLIC OFFLINE RBS01RBS4 PUBLIC OFFLINE RBS01RBS5 PUBLIC OFFLINE RBS01RBS6 PUBLIC OFFLINE RBS01RBS7 PUBLIC OFFLINE RBS01RBS8 PUBLIC OFFLINE RBS01RBS10 PUBLIC OFFLINE RBS01RBS11 PUBLIC OFFLINE RBS01RBS12 PUBLIC OFFLINE RBS01RBS13 PUBLIC OFFLINE RBS01RBS14 PUBLIC OFFLINE RBS01RBS 15 Public Offline RBS01RBS16 Public Offline RBS01RBS17 Public Offline RBS01RBS18 Public Offline RBS01RBS19 PUBLIC OFFLINE RBS01RBS20 PUBLI
C OFFLINE RBS01RBS21 PUBLIC OFFLINE RBS01RBS22 PUBLIC OFFLINE RBS01RBS23 PUBLIC OFFLINE RBS01RBS24 PUBLIC OFFLINE RBS01RBS26 PUBLIC OFFLINE RBS01RBS27 PUBLIC OFFLINE RBS01RBS28 PUBLIC OFFLINE RBS01RBS25 PUBLIC OFFLINE RBS01APPRBS PUBLIC OFFLINE RBS0130 rows selected. In addition system, are offline state. Continue to do the script so that the system in addition to the rollback 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 ONL ine; 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 above script, and finally do not forget to delete the original
