Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: COMPATIBLE parameter not being read...why?

Re: COMPATIBLE parameter not being read...why?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 11 Aug 1999 20:25:00 GMT
Message-ID: <37b2d820.5141963@newshost.us.oracle.com>


A copy of this was sent to "Christopher Allen" <progplusSPAMBEGONE_at_gte.net> (if that email address didn't require changing) On Wed, 11 Aug 1999 18:54:34 GMT, you wrote:

>I was editing the right init.ora file for the instance I was using, but your
>question got me to thinking, because the database has multipe instances.
>Checking the UNIX environment, I determined that a different instance was
>specified in the ORACLE_SID environment variable.
>
>So I edited the init.ora file for that instance, and the problem went away.
>Of course, a new one appeared. The problem I'm running into is rollback
>segments that are failing to extend (ORA-01562: failed to extend rollback
>segment number 2; ORA-01628: max # extents (121) reached for rollabck
>segment). When I try to increase the number of extents the rollback segment
>can use, I get the following error msesage:
> ORA-01545: rollback segment 'RBS_NAME_HERE' specified not available
>
>The Oracle documentation for this error message is useless. It states four
>cases in which the message can appear: the first two are when one is trying
>to bring an offline RBS online, and the other two are when one is trying to
>drop a RBS. Neither of these is the case here.
>
>If you have any thoughts about what could be causing this, I'd sure like to
>hear about it.

Actually - it was the fourth operation about altering a rollback segement to use maxextents that was getting you:

01545, 00000, "rollback segment '%s' specified not available"

// *Cause: Either:
//         1) An attempt was made to bring a rollback segment online that is
//            unavailable during startup; for example, the rollback segment
//            is in an offline tablespace.
//         2) An attempt was made to bring a rollback segment online that is
//            already online.  This is because the rollback segment is
//            specified twice in the ROLLBACK_SEGMENTS parameter in the
//            initialization parameter file or the rollback segment is already
//            online by another instance.
//         3) An attempt was made to drop a rollback segment that is
//            currently online.
//         4) An attempt was made to alter a rollback segment that is
//            currently online to use unlimited extents.
// *Action: Either:
//         1) Make the rollback segment available; for example, bring an
//            offline tablespace online.
//         2) Remove the name from the ROLLBACK_SEGMENTS parameter if the name
//            is a duplicate or if another instance has already acquired the
//            rollback segment.
//         3) Bring the rollback segment offline first. This may involve
//            waiting for the active transactions to finish, or, if the
//            rollback segment needs recovery, discover which errors are
//            holding up the rolling back of the transactions and take
//            appropriate actions.
//         4) Same as 3).


Unbeknownst to you, setting extents over 121 for the RBS is like setting unlimited extents. You were hitting #4 above. You must offline it. consider:

SQL> alter rollback segment r04 storage ( maxextents 200 ); alter rollback segment r04 storage ( maxextents 200 ) *
ERROR at line 1:
ORA-01545: rollback segment 'R04' specified not available

SQL> alter rollback segment r04 offline;

Rollback segment altered.

SQL> alter rollback segment r04 storage ( maxextents 200 )   2 /

Rollback segment altered.

SQL> alter rollback segment r04 online;

although, unless you have unlimited_rollback_segments set in your init.ora you may receive:

SQL> alter rollback segment r04 storage (maxextents 200); alter rollback segment r04 storage (maxextents 200) *
ERROR at line 1:
ORA-01681: Parameter unlimited_rollback_segments must be set to TRUE for this operation
ORA-02226: invalid MAXEXTENTS value (max allowed: 121)

telling you to set the unlimited_rollback_segments....

HOWEVER: if your rbs is going over 121 extents, i would suggest DROPPING it and recreating it with larger extent sizes. (you'll have to offline it to drop it as well)

>
>Thanks for your assistance,
>Christopher
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 11 1999 - 15:25:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US