Shulou(Shulou.com)06/01 Report--

I have built three sets of oracle clusters in the company. According to my colleagues, cluster 3's undo table space is always full and resources are not released. Because of business problems, there can be no downtime. Then he kept creating new undo tablespaces all the way to undotbs6, which was supposed to be two by default (undotbs1 and undotbs2). Then the undo tablespace is getting bigger and bigger, so he deletes the undo tablespace by deleting the undo tablespace, but when deleting it, he deletes it incorrectly, deletes it to undotbs1, and deletes it altogether (undotbs1 and undotbs4,undotbs5,undotbs6).

At that time, undotbs2 and undotbs3 were still full, so I wanted to restart it by rebooting, and then my colleague asked me to restart it, and then I just rebooted without paying attention. And report it wrong.

Unable to find the corresponding undo tablespace. Then I thought that only by modifying pfile, by booting the single-node database to the mount state, through the

Create pfile='$ORACLE_HOME/dbs/ora_1214.ora' from spfile; then modifies the pfile, changes the undo tablespace to undotbs2 and undotbs3, then starts the database through pfile, and finally creates the spfile through the current pfile, otherwise an error will be reported next time.

This only solves the problem of normal opening of the library, and the biggest problem is the problem of full undo tablespaces. Deleting undo tablespaces is only a palliative rather than a permanent cure, and it will be repeated in the future. By looking up the data and the parameters of the database, we found a parameter: the time saved in the undo table space after the undo_retention transaction was committed was modified to 18000 seconds, that is, 5 hours.

Oh, my God, the default is 900s. No wonder there is not enough undo tablespace. This is the reason. The business is constantly used, and the data is constantly accumulated and not released. Finally found the reason, immediately changed it to 900 seconds.

Whoo ~ rookie, I've learned something again.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.