Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: COMPATIBLE parameter not being read...why?
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