Re: Rollback segment question (need help)

In article <10925_at_tamsun.tamu.edu> ffeng_at_cs.tamu.edu (Fang Feng) writes:
>Thanks for those who replied me directly or posted on the
>group. I tried the way you told me, but the problem still there. My steps
>are as follows:

OK. I've been watching this drama play out over the past week or so and you've got my curiousity peaked. Let's try figuring this sucker out by getting a little bit more particular about some key items that might be missing here. Clues, if you will, that might hold the key to the problem. I will follow your recipe and ask a few questions that might not have been asked before and you go back and see if what I mention is correct, OK?

Let's get down to it, eh?

>1. Create a second rollback segment (I called it 'dummy') before
> I created any tablespace other than the 'SYSTEM' one.

OK

>2. Shut the database down.
>3. Change the file 'init.ora' to include the rollback segment
> dummy by
> rollback_segments = dummy

Now, before we go any further, when you created the database, what name did you give the database? The reason that I ask, is that the file init.ora is a "template" init file that Oracle uses when building the database from ground zero. Once the database is created, an init file called init[oracle_sid].ora is created (where [oracle_sid] is the name that you gave your instance). If you edited the init.ora file and restarted using the command:

        SQLDBA>startup         

without adding

        dfile=?/path/init.ora         

you have done nothing other than start the database with the init[oracle_sid].ora file not the init.ora file that you just edited! This is the default! Tricky, eh?

>4. Start the database up with the changed init.ora

Hmmmmm. See above. Are you SURE! Let's continue shall we?

>5. Create new tablespace 'mc_space' by
> create tablespace mc_space
> datafile 'path/filename' size 80M resue
> default storage (initial 50M next 50K
> minextent 1 maxextents 600
> pctincrease 0)

OK, fine. A couple nitpicks. Why do you use "80M reuse"? Why do you define an initial extent of 50M then define maxextents of 600 when due to the size of the database file, it will never be able to extend? Just curious.

>6. Create new rollback segment by
> create rollback segment roll_mc
> tablespace mc_space
> storage (initial 50000 next 50000
> minextents 1 maxextents 100
> pctincrease 0)
 

> This SQL command caused error:
> ORA-01552: cannot use system rollback segment for non-system
> tablespace 'MC_SPACE'
> So, that means this statement was not successful.

Yep. Do one thing here. As SYS issue the query:

        select * from dba_rollback_segs where segment_name = 'DUMMY';

What does it say under the STATUS column? If it says 'AVAILABLE' then the rollback segment is there but has not been "turned on." This goes back to a point I made earlier WRT the init.ora file that you edited. Either shutdown the database and issue:

        SQLDBA> startup pfile=?/dbs/init.ora

	or edit the init[oracle_sid].ora file to include the rollback
	segment definition ala your init.ora file and issue:

	SQLDBA> startup

Either way, you are guaranteed that the rollback segment will become 'IN USE'.

>7. I looked up the dictionary by
> select * from sys.dba_rollback_segs
> Only 'system' and 'dummy' were there.

Yes, but what was the STATUS for 'dummy'? I bet dimes to dollars it was 'AVAILABLE'.
>8. Then I shut down the database, changed 'init.ora' to include the
> rollback segment 'roll_mc' (I tried to create for tablespace 'mc_space'),
> and then start up the database. I still couldn't find the segment
> 'roll_mc'. So that means the command in step 6 didn't succeed.

That's right...the error message that you received above is proof enough. (ORA-01552...) Go back to your point 6.

>9. During the process, I used userid and password 'system/manager', it
> didn't work. Then I tried use other userid and password, say
> 'oracle/tti1'. It still didn't work.

What didn't work? This whole process? Doesn't make any difference as long as you used the wrong init file.

> Did I do anything wrong or in wrong order? I looked up the manual and
> couldn't find the answer. Do you have any idea?

I trust the problem has been remedied. If not please call me.

The fun thing about Oracle and their glorious manuals is that there is a lot going on in the background that they either neglect to mention or some of the things in the manuals are downright WRONG.

        DBA Guide Pg B-43 Under STARTUP. PFILE=filespec

        And I quote: "Uses the specified parameter file while starting up."

        This is true, but...

	"If no file is specified, the default is the INIT.ORA file found
	 in its default location."

	WRONG!  That should read ...is the INIT[oracle_sid].ORA file...

> I really appreciated your reply.

Hey, no charge...

Let's hope this works...

If not, you get a full refund ;)

> - Fang
>
>--
>Texas A&M University "Time will erase everything"
>Department of Computer Science "Who knows what will happen
>(409)847-8609 tomorrow?"
>internet: ffeng_at_cs.tamu.edu -- Feim Dhdaih

Dale Cooper, DBA
Center for Seismic Studies
Arlington, VA (703)276-7900 x27 cooper_at_seismo.css.gov Received on Wed Mar 18 1992 - 22:56:46 CET