Oracle9i UNDO

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]

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

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 ???

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

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

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

Quote:

---

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?

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

Quote:

---

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?

Quote:

---

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.

Quote:

---

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

---

It is OK.

Quote:

---

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

---

Use what?

Quote:

---

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.

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

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

Thanks

Quote:

---

Originally posted by ashley75
what would you use if you have 9i on your production database???

---

I would not use OMFs.

Quote:

---

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

Thanks

---

Learn, learn, learn.

Quote:

---

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

Thanks

---

Learn, learn, learn.
[/B][/QUOTE]

What I mean was would you use UNDO_MANAGEMENT=AUTO on your production??? and how would you change your undo segment (naming convention)on the database ???? Please show me some instructions.

I am using oracle created roll back segment only..
it works fine to me.
Though we have option to use user created rollback i did not try.
note:I have created 516 m UN_DO tablespace because we have huge loading oftenly.
Thanks.
Thanigaivasan.

You should definitely use the automatic undo management. In future versions manual will be deprecated.

Why do you care about the name of the undo segments. You should never need to do anything with them anyway???

Quote:

---

What I mean was would you use UNDO_MANAGEMENT=AUTO on your production???

---

Yes, definitely.

Quote:

---

and how would you change your undo segment (naming convention)on the database ???? Please show me some instructions.

---

When using AUM you cannot rename ORA_whatever to for example QWERTY. Oracle using naming convention for AUM.

what would you guys do if this happens

undo tablespace is eating up 99% of a filesystem however there is only one transaction going on but not committed. We have to reduce the size of undo tablespace but since it is not possible whatshould we do? I can only think of creating another undo tablespace set the new undo to this new one and drop the old undo but since there is a transaction going in the old undo we cannot drop it, if this were manually managed we could shrink the RBS manually but I dont think this is possible in undo.... this could happen sort of tricky

Autoextend gone mad :)

yea but what if file system is 99% full and you need to free the file system, obviously we could add datafiles in other file systems but since we cannot reduce undo tablespace size this can get tricky!
And considering smon only wakes up every 12 hours..... to free the unused undo

Quote:

---

Originally posted by pando
and drop the old undo but since there is a transaction going in the old undo we cannot drop it

---

I could, just tried (9.2). I got really surprised, did not expect for that to happen. I had a pending transaction in UNDOTBS1 (non-committed). I created UNDOTBS2, switched the system to the new tablespace, ran drop tablespace UNDOTBS1. It got dropped. The transaction was automatically commited by Oracle after dropping UNDOTBS1.

Quote:

---

Originally posted by julian
I could, just tried (9.2). I got really surprised, did not expect for that to happen. I had a pending transaction in UNDOTBS1 (non-committed). I created UNDOTBS2, switched the system to the new tablespace, ran drop tablespace UNDOTBS1. It got dropped. The transaction was automatically commited by Oracle after dropping UNDOTBS1.

---

That's extremely strange! I can't test this right now, but I would be really surprised if Oracle really automatically commited that pending transaction. Are you sure, Julian?

Unless, of course, you performed DROP TABLESPACE from within the same session that started that uncommited transaction. If this is the case, then of course it was you that implictly commited the transaction, not the database system itself.

Quote:

---

Originally posted by julian
The transaction was automatically commited by Oracle after dropping UNDOTBS1.

---

comitted... what if the user didnt want to commit that data

ah by the way, I was playing around the other day offlining the tablespaces because I read this note from Metalink that you have to put tablespace in READ ONLY then OFFLINE if we want to rename a datafile however in ILT course there was no such requirement, offline was enough

So I did a DML in a session, put the tablespace where the segment I was modifying belongs offline. Commit the transaction, got comitted. So far so good I guess the commit data is saved in redo log, then I did a checkpoint to see if the comitted data would get written to the disk since the tablespace was offline I was expecting complains. I didnt try it but I guess I would lose the tablespace if I was in noarchive log, redo logs which contains the transaction gets overwritten and I try to put tablespace online, may be this is the reason why we must put tablespace in read only?

Quote:

---

Originally posted by jmodic
[BUnless, of course, you performed DROP TABLESPACE from within the same session that started that uncommited transaction. If this is the case, then of course it was you that implictly commited the transaction, not the database system itself. [/B]

---

You are right Jurij, I just tested again. First, from the same session, it worked as I explained above and the commit is implicit as you suggested. But if you try to alter the undo_tablespace from a separate session, you get, well I got, ORA-30013: undo tablespace UNDOTBS is currently in use.

So, there seems to be nothing unsual :-)

so it´s back to my assumption, what can we do here, we cannot shrink the RBS manully

so this is tricky

Quote:

---

Originally posted by pando
so it´s back to my assumption, what can we do here, we cannot shrink the RBS manully

so this is tricky

---

You mean if for example we have a runway transaction? Leading to consuming a lot of undo space?

yea read my previous posts, sort of like that