Undo tablespaces increase abnormally due to insufficient space for printing and distribution.

Undo tablespaces increase abnormally due to insufficient space for printing and distribution.

Today, my colleague found that an exception occurred to a database server he was responsible for. The symptom was that the UNDOTBS tablespace increased, resulting in insufficient disk space. Please help me solve this problem.
The system is linux. In principle, this problem is actually a good solution. Create a New UNDOTBS tablespace, and then let the system use this tablespace by default. After the switchover is complete, delete the old UNDOTBS tablespace.
However, in actual solution, it is a trigger, which is summarized as follows:
At the beginning, I gave him a document about how to create a new UNDOTBS tablespace and delete the old one. For details, see the http://blog.csdn.net/wxlbrxhb/article/details/14448777.
However, he waited for a while and found that the existing UNDOTBS tablespace was not offline. The offline command manually executed is invalid. It is estimated that data is still being processed.

He was a little anxious, so he suggested that he restart the database and enable it again so that the database will directly use the new UNDOTBS tablespace by default, and the old one will be deleted directly.
However, an error occurred while enabling the database in sqlplus after the instance is restarted:

The prompt is that you do not have the permission. Sometimes oracle does not join the DBA user group, or you may have insufficient permissions. So let's check it and find that oracle is already in the DBA user group.

It seems that the idea is still wrong, not this problem. Try to log on directly using sqlplus/as sysdba. The result is as follows:
ORA-09945: Unable to initialize the audit trail file
Linux Error: 28: No space left on device
SQL>
./Dbstart: Database instance "tnitsora" warm started.

/U01/app/oracle/product/10.2.0/db_1/bin/dbstart: Starting up database "tnitsora"
Notice a prompt ORA-09945: Unable to initialize the audit trail file
The audit file cannot be initialized. It seems that oracle will involve a permission audit problem during normal use. Generally, an audit file will be created under/u01/app/oracle/admin/tnitsora/adump, coincidentally, the disk space is insufficient today. Is it because of this that new audit files cannot be created, leading to access failure to sysdba and database startup failure.
With this idea, I entered the oracle directory, and entered the/u01/app/oracle/admin/tnitsora directory. I used du-sh to view the usage of each directory and found the problem, bdump has 24 GB, which is too big. You have deleted most of the files in bdump. When you log on with sysdba, you will find that you can log on normally. It is also normal to enable the database after you log on.
The Bdump directory stores the background process trace file directory. Generally, files in this folder can be deleted.

After applying all the applications, my colleagues found that the new UNDOTBS tablespace increased very quickly and 8 GB was used in half an hour. The UNDOTBS increased normally, but it also increased too quickly, decide to continue the analysis.
I first asked if there were any changes to the database parameters recently. My colleague said no, but I have modified the table structure. Generally, if the undo_retention parameter is set too long, it is likely that a large amount of uncommitted data in the UNDOTBS tablespace will rapidly increase, and the show parameter undo will be used for viewing without exception.

Ask if the script or program for automatic database backup is running. It indicates no. I also looked at jobs that operate on a large amount of data and found it had nothing to do with the job. The job was executed at night.

Continue the analysis and think that if a table is not indexed, it will produce very low efficiency during dml, and may also cause an abnormal increase in the UNDOTBS tablespace. Generally, INSERT generates at least UNDO, because for INSERT, Oracle only needs to record the corresponding rowid of the "delete" Row. The second is the UPDATE operation. For this operation, only the modified bytes are recorded. Generally, in most cases, we only modify a small part of the row, UNDO will record this part. Compared with the preceding two operations, the DELETE operation generates the most UNDO, because Oracle must record the entire row of the front image to the undo segment.
Please compare the table structure of a database that has not encountered any problem with me to the tables with a large data volume, and find that the indexes are the same and there is no exception. What is the strange problem?
A statement to check the Transaction Status of the rollback segment early on the Internet
SELECT r. NAME rollback segment NAME, s. sid SID, s. serial # Serial,
S. username, s. machine name,
T. start_time start time, t. status,
T. used_ublk unblocks and USED_UREC undo records,
T. cr_get, t. cr_change,
T. log_io "logical I/O", t. phy_io "physical I/O ",
T. noundo NoUndo, g. extents Extents, substr (s. program, 1, 50) Operator
FROM v $ session s, v $ transaction t, v $ rollname r, v $ rollstat g
WHERE t. addr = s. taddr
AND t. xidusn = r. usn
AND r. usn = g. usn
Order by t. used_ublk desc
It is found that a process has been using the rollback segment and has been running for more than two hours. The status is still active.

The process name is FAKE_xxx _ xxx _ xxx.exe. It is estimated that the problem lies in it. After the program is closed, check the UNDOTBS tablespace again. At this time, the increase of the UNDOTBS tablespace has been reduced, and the database returns to normal.