Oracle releases overused undo table space ____oracle

Symptom: Undo table space is getting bigger and longer this end data crashes because of insufficient disk space;

Problem Analysis: The causes of the problem are mainly the following two points:
1. There is a large volume of transactions so that Oracle undo automatically expand, resulting in excessive disk space consumption;
2. A larger business is not contracted or is not submitted for guidance;
Note: This problem in Oracle System management is a relatively normal phenomenon, day-to-day maintenance pay more attention to the monitoring of disk space.
Backup: (If there is no online transaction, can not do, turn off listening)

$>exp Vas/vas file=/opt/oracle/data_1.dmp,/opt/oracle/data_2.dmp log=/opt/oracle/date.log Owner=vas rows=y Indexes=y compress=n buffer=65536 feedback=100000 volsize=0 filesize=1000m

Solution steps:
1. Start Sqlplus and log into the database with Sys.

#su-oracle
$>sqlplus/nolog
Sql*plus:release 9.2.0.4.0-production on Wed Nov 8 13:45:10 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Sql> Conn Sys/qq994238@ddptest as SYSDBA;
Connected.

2. Find the Undo table space name of the database

#cat $ORACLE _home/dbs/initddptest.ora
......
*.undo_management= ' AUTO '
*.undo_retention=10800
*.undo_tablespace= ' UNDOTBS2 '
......

3. Confirm the undo table space;

Sql> select name from V$tablespace;

NAME
------------------------------
Cwmlite
Drsys
EXAMPLE
INDX
Odm
SYSTEM
TOOLS
USERS
XDB
TEMP
Testlib
UNDOTBS2

4. Check the space occupied by the database undo table space and the location of data file storage;

Sql>select file_name,bytes/1024/1024 from Dba_data_files
2 where tablespace_name like ' UNDOTBS2 ';

5. See the rollback segment usage, which user is using the resources of the rollback segment, if the user has the best time to change (especially the production environment).

Sql> Select S.username, u.name from V$transaction T,v$rollstat R,
2 V$rollname u,v$session s where s.taddr=t.addr and
3 T.xidusn=r.usn and R.usn=u.usn order by S.username;

6. Check the undo segment state;

Sql> Select Usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from V$rollstat-rssize;

7. Create a new undo table space and set automatic extension parameters;

sql> Create undo tablespace undotbs1 datafile '/oradata/oradata/ddptest/undotbs1.dbf ' size 1000m reuse autoextend on NE XT 800m MaxSize Unlimited;

Tablespace created.

8. Dynamically change the SPFile configuration file;

Sql> alter system set UNDO_TABLESPACE=UNDOTBS1 Scope=both;

System altered.

9. Wait for the original undo table space all undo SEGMENT OFFLINE;

Sql> Select Usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from V$rollstat-rssize;

10. Then perform a look at the undo table space all undo SEGMENT ONLINE;

Sql> Select Usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from V$rollstat-rssize;

11. Delete the original undo table space;

Sql> drop tablespace undotbs2 including contents;

Tablespace dropped.

12. Confirms the success of the deletion;

Sql> select name from V$tablespace;

NAME
------------------------------
Cwmlite
Drsys
EXAMPLE
INDX
Odm
SYSTEM
TOOLS
USERS
XDB
TEMP
Testlib
UNDOTBS1

Rows selected.

13. Before doing this step, please go to $oracle_home/dbs/init$oracle_sid.ora to see if the following changes occur:

#cat $ORACLE _home/dbs/initddptest.ora
......
*.undo_management= ' AUTO '
*.undo_retention=10800
*.undo_tablespace= ' UNDOTBS2 '
......

If no change occurs, execute the following statement:

Sql> create Pfile from SPFile;

File created.

14. A data file in addition to the original undo table space whose file name is the result of the steps executed.

#rm $ORACLE _base/oradata/$ORACLE _sid/undotbs02.dbf

Appendix: Introduction to the Undo table space

The Undo table space is used to hold the undo data, and when the DML operation (Insert,update and delete) is performed, Oracle writes the old data of those operations to the undo segment, which is used before oracle9i to manage the undo data (Rollback Segment) completed. Starting with Oracle9i, managing undo data can not only use the rollback segment, but also use the Undo table space. Because planning and managing rollback segments is more complex, all Oracle database 10g has completely discarded the rollback segment. and use the Undo table space to manage the undo data.

The undo data, also known as rollback (ROLLBACK) data, is used to ensure data consistency. When a DML operation is performed, the data before the transaction operation is called the Undo record. The Undo section is used to save the old values of the modified data, which store the location of the modified block and the data before the modification,

The role of the undo data.

1, ROLLBACK TRANSACTION

When the DML operation modifies the data, the undo data is stored in the undo segment, and the new data is stored in the data segment, and if there is a problem with the transaction, the old need to rollback the transaction to cancel the transaction. Assumes that user a executed the statement update EMP SET sal=1000 WHERE empno=7788 found that the employee should be modified 7963 of the salary, rather than 7788 of the employee's salary, then by executing the rollback statement can cancel transaction changes. When the rollback command is executed, Oracle writes the undo data of the undo segment back to the data section of 800.

2, read consistency

When a user retrieves database data, Oracle always uses the user to see only the submitted data (read commit) or data at a specific point in time (the SELECT statement point in time). This ensures data consistency. For example, when user A executes statement update EMP SET sal=1000 where empno=7788, the undo record is stored in the rollback segment, and the new data is stored in the EMP segment, assuming that the data has not been committed at this time, and User B executes select sal from emp WHERE empno=7788, User B then obtains the undo data 800, which is obtained in the undo record.

3, transaction Recovery

Transaction recovery is part of routine recovery, which is done automatically by Oracle server. If a routine fails during the database run (such as a power outage, memory failure, background process failure, and so on), the background process Smon automatically performs routine recovery when the Oracle server is restarted. When performing a routine recovery, ORACL will redo all the records that were not applied. ROLLBACK UNCOMMITTED transaction.

4, flashbacks query (flashback query)

A flashback query is used to obtain database data at a particular point in time, and it is a new addition to 9i, assuming the current time is 11:00, a user executes the update EMP SET sal=3500 where empno=7788 statement at 10:00, modifies and commits the transaction ( An employee's original salary is 3000, and in order to obtain an employee's salary before 10:00, the user can use the Flashback query feature.

Using the Undo parameter

1,undo_management

This initialization parameter is used to specify how the undo data is managed. If you want to use Automatic management mode, you must set this parameter to auto, and if you use manual management mode, you must set this parameter to manual, and when using Automatic management mode, Oracle will use the Undo table space management undo Management, When using manual management mode, Oracle uses the rollback segment to manage the undo data.

Note that when you use Automatic management mode, if you do not configure initialization parameters undo_tablespace,oracle automatically selects the first available undo table space to hold the undo data, and if there is no undo tablespace available, Oracle uses the system rollback segment to store the undo record and to record the warning in the alter file.

2,undo_tablespace

This initialization parameter is used to specify the undo table space to use for the routine, and by configuring this parameter, you can specify the undo tablespace to use for the routine by using the automatic undo management mode.

In the RAC (real application Cluster) structure, because an undo table space cannot be used by more than one routine, all must configure a separate undo tablespace for each routine.

3,undo_retention

This initialization parameter is used to control the maximum retention time for the undo data, with a default value of 900 seconds, starting at 9i, by configuring the initialization parameter to specify the retention time of the undo data to determine the earliest point-in-time that the Flashback query feature (flashback query) can see.

Create the Undo table space,

The Undo table space is dedicated to storing the undo data, and no data objects (tables, indexes, clusters) can be established in the Undo table space.

1, use the CREATE DATABASE command to create the undo table space.

When you use the CREATE DATABASE command to set up databases, you can create the undo table space by specifying the undo tablespace option. Examples are as follows:

CREATE DATABASE DB01

...

UNDO tablespace undotbs_01

DataFile '/u01/oracle/rbdb1/undo0101.dbf ' SIZE 30M;

Note: The Undo tablespace clause is not required, and if you use automatic undo management mode and you do not specify the clause, the undo table space named Sys_undotbs is automatically generated when you build the database.

2, use the Create undo tablespace command to create the undo table space.

CREATE UNDO tablespace UNDOTBS3

DataFile ' d:demoundotbs3.dbf ' SIZE 10M;

Modify the Undo table space,

Modify the Undo table space using the ALTER TABLESPACE command.

When a transaction runs out of the undo tablespace, use alter tablespace ... Add datafile Add data file

When the undo table space is filled with disk, use ALTER tablespace ... RENAME the Datafiel command to move the data file to another disk.

Using Alter DATABASE ... Offline/online the tablespace offline/online.

When the database is in Archivelog mode, use Alter tablespace ... The BEGIN backup/end Backup command backs up the undo table space.

Toggles the undo table space.

After starting the routine and opening the database, a specific routine at the same time can only use one undo table space, switching the undo table space is the undo table space currently in use to stop the routine, and the other undo tablespace is started, and the following is an example of how to toggle the Undo table space by enabling UNDOTBS2 tablespace.

ALTER SYSTEM SET Undo_tablespace=undotbs02;

In the RAC (real application Cluster) mechanism, different routines must use a separate undo table space instead of sharing the same undo tablespace.

Deletes the undo table space.

The Undo table space that the current routine is using cannot be deleted, and if you are certain that you want to delete the Undo table space that the current routine is using, you should first switch the undo tablespace. And then delete the corresponding undo table space.

DROP tablespace UNDOTBS3;

1, determine the undo table space that the current routine is using.

Show Parameter Undo_tablespace

2, display all undo table spaces for the database.

SELECT tablespace_name fromdba_tablespaces WHERE contents= ' UNDO ';

3, display the Undo table space Statistics.

When using the automatic undo management mode, you need to reasonably set the size of the undo table space, for example, to rationally plan the undo table space Size, you should collect statistics for the undo table space at the peak of the database operation. Finally, the dimensions of the undo table space are determined based on this statistic Undostat, you can collect undo statistics.

SELECT to_char (begin_time, ' HH24:MI:SS ') Begin_time,

To_char (end_time, ' HH24:MI:SS ') End_time,

Undoblks

From V$undostat;

Begin_time is used to identify the start statistic time, end_time is used to identify the ending statistic time, and undoblks to identify the number of blocks of data that the undo data occupies. Oracle generates one row of statistics every 10 minutes.

4, show the undo segment statistics.

When using the automatic undo Management mode, Oracle automatically creates 10 undo segments on the Undo Tablespace, and by querying dynamic information View v$rollname, you can display the names of all online undo segments by querying the dynamic performance view V$rolllistat You can display statistics for the undo segment. You can monitor specific information for a specific undo segment by performing a connection query between V$rollname and V$rolllistat.

SELECT A.name, B.xacts, B.writes, b.extents

From V$rollname A, V$rollstat b

WHERE A.usn=b.usn;

Name is used to identify the names of the undo segments, Xacts to identify the number of active transactions contained in the undo segment.

Writes is used to identify the number of bytes written on the undo segment, extents the number of extents used to identify the undo segment.

5, displays the active transaction information.

When the DML operation is performed, Oracle puts the old data of these operations into the undo segment, and dynamic performance View v$session the details of the session, v$transaction the dynamic performance view to display the details of the transaction, dynamic performance View v$ Rollname is used to display the name of the online undo segment. By performing a connection query between these 3 dynamic performance views, you can determine the session in which the transaction is being performed, the undo section used by the transactions, and the number of undo blocks occupied by the firm.

Col username Format A10

Col name format A10

SELECT A.username, B.name, c.used_ublk

From V$session A, v$rollname B, v$transaction c

WHERE A.saddr=c.ses_addr and B.usn=c.xidusn

and A.username= ' SCOTT ';

6, display the Undo area information

The Data dictionary view dba_undo_extents is used to display detailed information about all extents in the undo tablespace. Includes information such as the Undo area size and status.

SELECT extend_id, Bytes, status from Dba_undo_extents

WHERE segment_name ' _syssmu5$ ';

Where extent_id is used to identify the area number, Bytes is used to identify the size of the zone, and status is used to identify the zone state (active: Indicates that the zone is active, expired: identifies the area as unused).