Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to speed up after UNDO creation maxed out Diskspace

Posted on 2007-10-19
10
Medium Priority
?
337 Views
Last Modified: 2013-12-19
We are running Oracle 9i on a Windows 2000 box with 4 gigs of RAM.
Drive C: is for the operating system and has 68G.
Drive D: is for Oracle and some tablespaces and has 68G
Drive G: is solely for tablespaces and has 280G.
We do not do transactions, so all logging is turned off.

We just  got drive G: and installed it. SOme of the tablespaces were moved to the new drive but their physical files were still on Drive D:  
Yesterday, someone utilized a temp Schema on Drive D: to run a very long query on 6 million records.
Another user did a long query on 6 million records on one of the permanent Schemas on D:

The temp space grew to 12.5G and a file called UNDOTBS1 grew to 12.5g - this, long with the tablespaces, took up the entire 68G on D:
This crashed the server - oracle disconnected since it couldnt find any more media.

we rebooted. and were able to drop an old tablespace and delete the data file  from D:
I am planing on moving the TEMP space to the larger drive.

Problem is it is still slow.
Any ideas on how to speed it up?
How can I minimize or get rid of the UNDOTBS1?
0
Comment
Question by:GNOVAK
  • 5
  • 5
10 Comments
 
LVL 9

Expert Comment

by:joebednarz
ID: 20111408
Well, you can't get rid of the UNDOTBS tablespace... however, you can impose some reasonable limits on it.

For instance, find the datafile associated with the UNDOTBS1 tablespace and issue this:

ALTER DATABASE DATAFILE '<undo_tbs_datafile>' RESIZE 1G;
ALTER DATABASE DATAFILE '<undo_tbs_datafile>' AUTOEXETEND ON MAXSIZE 2G;

This will drop the size first, then create a maximum that it will not exceed.
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20111441
Another thing... if the only thing these users are doing is running a "query on 6 million records" it shouldn't affect the UNDOTBS tablespace.  This tablespace is specifically used for undo-ing transactions such as INSERTS and UPDATES.  

There is another parameter you might want to look into and that is the UNDO_RETENTION time setting.  This parameter tells Oracle how long the records of changes are to be kept in the UNDOTBS tablespace.  Check this parameter... keep in mind that this setting is in seconds, so ours is set to 20 minutes by doing this:

ALTER SYSTEM SET UNDO_RETENTION = 1200
0
 

Author Comment

by:GNOVAK
ID: 20111445
If I try to alter the tablespace it will not let me go any smaller. It says ORA-03297: file contains used data beyond requested RESIZE value - someone else suggested that this means it could have met it's highwater mark at the end of the file.
(don't know what a highwater mark is, or how to change it).

It was suggested to make a new undo table and set the default undo to that table (no users on on the system). I can create the new undo table, but dont know how to (easily) make it the new default undo
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GNOVAK
ID: 20111467
PS -
I use TOAD 8.6 which really helps me do things easier.
However, I don't know which file contains any of the parameters that Oracle looks at when starting up.
If I could find that file, I suppose I could change it there, reboot the server and when Oracle comes back up it will be using the new Undo. I could then delete the old one.  
That said, I'm hoping there is SQL command that I can give to let Oracle know what the new UNDO is as long as there is no other users on the system.
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20111503
As the SYS user, create your new UNDOTBS2 (or some other name) then enter:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
ALTER SYSTEM SET UNDO_RETENTION=5;

Then you'll be able to work on the other UNDOTBS

Here is a website that might help:  http://www.oracle-base.com/articles/9i/AutomaticUndoManagement.php
0
 

Author Comment

by:GNOVAK
ID: 20111512
Once I enter that, I should be able to delete the old UNDO from the OS side of things?
0
 
LVL 9

Expert Comment

by:joebednarz
ID: 20111637
After creating new UNDOTBS2 tablespace, I would:

DROP TABLESPACE UNDOTBS1;

THEN you could drop the UNDOTBS1 files in the operating system... but that is only if you are going the route of adding the new UNDOTBS2 tablespace, etc.
0
 

Author Comment

by:GNOVAK
ID: 20111659
I did ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
ALTER SYSTEM SET UNDO_RETENTION=5;
and it took.
So could I then just drop the other?

I also found the pfile that was used to create the current spfile.
I created a new one with the appropriate settings...
0
 
LVL 9

Accepted Solution

by:
joebednarz earned 2000 total points
ID: 20111825
You can try and drop the UNDOTBS1 tablespace.  Once you do that, you can also delete the file related to it...

If you are making your changes to the pfile and are going to make those changes effective in the spfile, you could do one of two things:  

1) CREATE SPFILE FROM PFILE;

or,

2) When you issue a command like ALTER SYSTEM... make sure to include SCOPE=BOTH
0
 

Author Comment

by:GNOVAK
ID: 20123532
Question -
I issued the Alter System commands mentioned with the SCOPE=BOTH keywords.
I took the original UNDOTBS Offline.
When I tried to copy a proc from one schema to another, it tried to use the original UNDOTBS, even though it looks like it has been using the UNDOTBS2 (contains some space)
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month10 days, 18 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question