oracle| Solution
This morning just to the company received Netcom customer complaints telephone, said the network management database problem, the database has bad block, rollback section of the data can not read, need to help solve.
I checked the SwappALRT.log file and found the following error:
The original rollback segment table space data file has a bad block. Know the location of the problem, immediately resolved, I have thought of the idea is to create a rollback segment table space, drop off the previously broken rollback segment table space, build the rollback segment on the new rollback segment table space, and make the rollback segment the same as before, so that the resulting rollback data is written to the new rollback segment. The train of thought was clear and immediately began to act.
? First stop to listener, do not allow new applications to do operations on the database, and then down the database, in order to clear out 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 to 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>startup Restrict (starting the database in restricted mode to prevent other users from logging in to do related operations, this time only allow administrators to log in)
Find the table space for the rollback segment:
Sql> select Tablespace_name,status from Dba_tablespaces;
To see if there are any active things in the current rollback segment 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 no activity, too good, you can rest assured that the drop rollback section, which is the result I want.
Next find the rollback segment storage parameter information:
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;
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????????? ?????? 32765??????????? 0
Selected rows.
Record the value of the initial_extent,next_extent,min_extents,max_extents,pct_increase and leave it for later to create a new rollback segment to use.
Create a rollback segment tablespace for LMT management (my database is oracle817):
sql> Create tablespace rbs01 datafile '/opt/oracle/db02/oradata/orcl/rbs01.dbf ' size 1024m?autoextend on next 1M maxsi Ze unlimited extent management local;
Tablespace created.
Set up a rollback segment in this table space first rbs31 do a test:
Sql> Create public rollback segment RBS31 tablespace rbs01 storage (initial 524288 next 524288 minextents 8 maxextents 4 096 OPTIMAL 4194304);
Create public rollback segment RBS31 tablespace rbs01 storage (initial 524288 next 524288 minextents 8 maxextents 4096 OPTI MAL 4194304)
*
ERROR at line 1:
Ora-25151:rollback Segment cannot is created in this tablespace
The mistake, unexpectedly did not build the success, shit.
Check it out. Metalink found that for oracle8i, the rollback segment could not be created under LMT-managed tablespace, but 9i solved the problem.
Explanation on the Metalink:
Explanation
-----------
Rollback segments cannot is created in locally managed tablespaces (a new feature in Oracle 8.1) with allocation type of Utoallocate. They must is created in locally managed tablespaces with allocation type of uniform or in dictionary managed.
Note:this restriction has been lifted in Oracle 9.
Next drop the rbs01 table space that you just created, and re-establish the RBS01 table space:
sql> Create tablespace rbs01 datafile '/opt/oracle/db02/oradata/orcl/rbs01.dbf ' size 1024M
? autoextend on next 1M MaxSize Unlimited;
Tablespace created.
Sql> Select Extent_management from dba_tablespaces where tablespace_name= ' RBS01 ';
Extent_man
----------
DICTIONARY
This table space is not LMT, is the DMT, hehe!
The following is really starting to do the work, in short two words, careful, because it is the production of the library, not 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 offline the rollback segment except the system rollback segment, one of the provinces knocks, and 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 make a drop rollback script for the segment:
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 done, don't forget to do it.
After the execution begins, the rollback segment is built in the new rollback segment table space, and the storage parameters are consistent with the original:
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;
Also made a script to avoid a knock on one!
The following large rollback segment to be built separately, in short, the system is best to have a large rollback section, when there are big things come in handy.
Sql> Create public rollback segment Apprbs tablespace rbs01 storage (initial 2097152 next 10485760 minextents NTS 32765);
Rollback segment created.
To view the newly created rollback segment status:
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
Rows selected.
In addition to system, they are offline states.
Continue with the script to have the rollback segment online except system:
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 above script, delete the original undo table space RBS:
Sql>drop tablespace RBS including contents;
Tablespace dropped.
Do this here is completed the required work, well, the rest of the left to do data testing, work, tomorrow and other database test results.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
