How to speed up after UNDO creation maxed out Diskspace
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?
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?
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
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
ASKER
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
(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
ASKER
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.
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.
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
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
ASKER
Once I enter that, I should be able to delete the old UNDO from the OS side of things?
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.
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.
ASKER
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...
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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)
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.