Deleted the UNDOTBS01.dbf file 1

[Penny1818]

Smart girl that I am I deleted the UNDOTBS01.dbf file from my system. I have exports of the databases so I am not concerned about recreating the roll back transactions that are contained in the file - just access to the service.

Any ideas, help or directions to some material written would be greatly appreciated.

Thanks, penny

 

[SantaMufasa]

Penny,

Here is first, the process, then second, the syntax for doing what you want to do:

Process:
1) Offline your rollback/undo segments;
2) Drop your UNDO tablespace;
3) Recreate your UNDO tablespace;
4) Recreate your rollback/undo segments;

Here is the code (keeping in mind that I use traditional rollback segments):

1) alter rollback segment rbs1 offline; (repeat for all rbs).
2) alter tablespace rbs offline;
3) drop tablespace rbs including contents;
4) create tablespace rbs datafile 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
size 10m reuse autoextend on next 10m maxsize 2000m;
5) create rollback segment rbs1
storage (initial 1m next 1m optimal 20m maxextents unlimited)
tablespace system;
(Note: If you use traditional rollback segments in Oracle 9i, you will need to create a "dummy" rollback segment in the SYSTEM tablespace and bring it online before creating your first traditional rollback segment. You can drop the "dummy" rollback segment following the successful creation and on-lining of your first traditional rollback segment.)

6) alter rollback segment <name> online; (for each rollback segment).

This process should resolve your having inadvertently dropped one of your rollback/undo tablespace files.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 23:06 (02Aug04) UTC (aka "GMT" and "Zulu"), 16:06 (02Aug04) Mountain Time)

 

[Penny1818]

SantaMufasa!

Thank you, thank you, thank you.

 

[Penny1818]

SantaMufasa, I neglected to mention that I am running Oracle9i. Are these instructions appropriate for that version? Thanks, penny

 

[SantaMufasa]

Penny,

I tested all steps on my Oracle 9.2.0.4 database before posting them for you. As I mentioned, I use "traditional" rollback segments versus the "undo" segments that come standard with Oracle 9i. Frankly, I have not profiled the behaviour differences between them to my satisfaction.

In that absence of any intel from other Tek-Tipsters, I would feel confident using the process I posted. The worst that could happen is that you will end up with "traditional" rollback segments, which work just fine. Then, if you and I gather any information that suggests that we are far better off with Oracle 9i "undo"s, then we can certainly implement them when we wish.

Among the certainties are that you need to fix your current situation (which my code does for you). If you have ANY difficulty, you are welcome to post here. Depending upon the urgency, you may call me 24 hours at 801-733-5333 (in Sandy, Utah, USA).

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:27 (03Aug04) UTC (aka "GMT" and "Zulu"), 17:27 (02Aug04) Mountain Time)

 

[Penny1818]

Thanks, just wanted to confirm. penny

 

[dbtoo2001]

If undo table space was in the init parameters, won't it need one defined to start the database at 9i?


Why do you prefer rbs to undo?