Oracle9i UNDO

[ashley75]

I guess I am bit confused when I tried to create the database in Oracle 9i manually.

On Oracle8i, we have:
ROLLBACK_SEGMENTS=(RB1,RB2,RB3,RB4) in the parameter file.

I am going to create the database in Oracle9i, and I won't use OMF option, and UNDO_MANAGEMENT I set it to manual instead of AUTO(since UNDO_MANAGEMENT will use SYS_UNDO plus the name convention is not nice) since we don't use rollback and I set UNDO_MANAGEMENT=manual so the question is:

Do I have to have UNDO_SEGMENTS on my parameter file??? I add this on my init.ora and I got error.

Everything works fine if I leave as

ROLLBACK_SEGMENTS=(RB1,RB2,RB3,RB4)

another word, If I create the UNDO tablespace and create rollback segment to UNDO tablespace, everything works fine. Is this correct ????



can someone give me some advises???
Thanks


[Edited by ashley75 on 06-13-2002 at 11:18 AM]

[TimHall]

You have a choice. You can use manual or automatic undo management. Manual uses rollback segments like the old days. Auto uses an undo tablespace with no rollback segments defined in it. My guess is you are trying to do something in between, hence you problem.

You can't define rollback segments in your init.ora is you are using automatic. you'll probably only have something like this:

UNDO_MANAGEMENT = AUTO # Default is MANUAL
UNDO_TABLESPACE = undotbs_01 # The name of the undo tablespace.
UNDO_RETENTION = 900 # The time undo is retained.
# Default is 900 seconds.
UNDO_SUPPRESS_ERRORS = TRUE # Suppress errors when MANUAL undo admin
# SQL statements are issued.

Take a look at this article and try again:

http://www.oracle-base.com/Articles/...Management.asp

Cheers

[ashley75]

Tim,

Thanks for your responce, this is that I have in my init.ora

undo_management = auto
undo_tablespace = undotbs
undo_retention = 600

and below is the sample of my create scripts:

create database MYTEST
controlfile reuse
maxdatafiles 1022
maxlogfiles 32
maxlogmembers 5
maxinstances 10
character set "WE8ISO8859P1"
datafile 'C:\ORADATA\MYTEST\system01.dbf' size 400m
logfile ('C:\ORADATA\MYTEST\log1MYTEST_A.rdo',
'C:\ORADATA\MYTEST\log1MYTEST_B.rdo') size 10M,
('C:\ORADATA\MYTEST\log2MYTEST_A.rdo',
'C:\ORADATA\MYTEST\log2MYTEST_B.rdo') size 10M,
('C:\ORADATA\MYTEST\log3MYTEST_A.rdo',
'C:\ORADATA\MYTEST\log3MYTEST_B.rdo') size 10M
UNDO TABLESPACE undotbs_01 DATAFILE 'C:\ORADATA\MYTEST\undo0101.dbf';

I got the foloowing error:

create database MYTEST
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


could you please let me know what I did wrong ???

[ashley75]

Tim,

I found out the problem is you have to specify the size of the UNDO tablespace. On your web site, you didn't specify the size of the UNDO tablespace so that I got the error above.

Could you please confirm that we have to specify the size of the UNDO tablespace??? Is somehting I did wrong ??? Another questions I have is: When you use undo_management = auto . Oracle will create the UNDO segment by its own name convention , right ????

Please let me know if I do this right


Thanks

[TimHall]

Hi.

You have your undo tablespace called undotbs_01 but your init.ora parameter undo_tablespace = undotbs. This should be undotbs_01.

Yes, the sizing was missing since it was only a small section of the create database command. I've added it in now to save any further confusion. Of course, if your using OMF you don't need the size or the file path to be specified.

If you use the Database Configuration Assistant (DBCA) you can get it to save the creation script rather than run it. You will then see exactly what the statement should look like.

Cheers

[ashley75]

Thanks Tim,

I am bit clearer now with this new function, one last question, So if you set UNDO_MANAGEMENT=AUTO, Oracle will automatically create the UNDO segment with its own naming convention??? Its naming convention is not nice at all, is there a way for you to change it????

Thanks

[julian]

Originally posted by ashley75
Thanks Tim,

I am bit clearer now with this new function, one last question, So if you set UNDO_MANAGEMENT=AUTO, Oracle will automatically create the UNDO segment with its own naming convention??? Its naming convention is not nice at all, is there a way for you to change it????

Thanks

First, you have to create the undo tablespace. Oracle will create the undo segments. If the datafile(s) is OMF, then you can rename but with a lot of restrictions. You know for one that a file is considered OMF if its base file name had:
- an "ora_" prefix
- and a ".dbf", ".tmp", ".log" or ".ctl" extension
What would you like to rename? From what to what?

[ashley75]

Julian,

Please correct me if I am wrong.

I didn't have want to have OMF on my database, I only want to turn on UNDO_MANAGEMENT=auto. couple questions , please correct me if I misunbderstand:

1. If you have OMF, Oracle will manage on of your file(control file, redo log file, and datafile), what about Rollback???
2. Oracle will manage undo tablespace automatically IF you set UNDO_MANAGEMENT=AUTO, right??? Another word, OMF is seperate function with UNDO_MANAGEMENT, right???

On my database now, I don't use OMF but I set UNDO_MANAGEMENT=AUTO, is it ok ??? what do you use for your production database, how every other DBA use for their database???

when I use UNDO_MANAGEMENT=AUTO, Oracle created the follwing rollback segment:

_SYSSSMU1$
......
.....


The bottom line I really like to undertsand is OMF and UNDO_MANAGEMENT si two seperate function in 9i right???

Sorry I am bit confused now.

thanks

[julian]

Originally posted by ashley75
1. If you have OMF, Oracle will manage on of your file(control file, redo log file, and datafile), what about Rollback???

Could you please reformulate the question?

2. Oracle will manage undo tablespace automatically IF you set UNDO_MANAGEMENT=AUTO, right??? Another word, OMF is seperate function with UNDO_MANAGEMENT, right???

Right.

On my database now, I don't use OMF but I set UNDO_MANAGEMENT=AUTO, is it ok ???

It is OK.

what do you use for your production database, how every other DBA use for their database???

Use what?

The bottom line I really like to undertsand is OMF and UNDO_MANAGEMENT si two seperate function in 9i right???

They are separate things, yes.

[ashley75]

what would you use if you have 9i on your production database???

What is the best way for DBA to manage their database???

Thanks