ORA-30036: unable to extend segment by 8 in undo tablespace 'undotbs1 ′

 

Fault: The undo tablespace is getting bigger and bigger, so the final data crashes due to insufficient disk space;

Problem analysis: the cause of the problem is as follows:
1. There is a large volume of transactions that allow Oracle undo to automatically expand, resulting in excessive disk space occupation;
2. There are large transactions that have not been contracted or are not committed by the system;
Description: This problem is normal in Oracle System Management. Pay attention to disk space monitoring during routine maintenance.
Backup: (if there is no online transaction, you can disable the listener without doing so)

$> 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 = 1000 m

Solution:
1. Start sqlplus and log on to 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,200 2, Oracle Corporation. All rights reserved.
SQL> conn sys/qq994238 @ ddptest as sysdba;
Connected.

2. Find the Undo tablespace name of the database

# Cat $ ORACLE_HOME/dbs/initddptest. ora
......
*. Undo_management = 'auto'
*. Undo_retention = 10800
*. Undo_tablespace = 'undotbs2'
......

3. Confirm the Undo tablespace;

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 Undo tablespace in the database and the storage location of the data files;

SQL> select file_name, Bytes/1024/1024 from dba_data_files
2 Where tablespace_name like 'undotbs2 ';

5. Check the usage of the rollback segment. Which user is using the resources of the rollback segment? If there is a user, it is best to change the time (especially in 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 status;

SQL> select USN, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks
2 from V $ rollstat order by rssize;

7. Create a New undo tablespace and set automatic expansion parameters;

SQL> Create undo tablespace undotbs1 datafile '/oradata/ddptest/undotbs1.dbf' size 1000 m reuse autoextend on next 800 m maxsize unlimited;

Tablespace created.

8. Modify the spfile configuration file dynamically;

SQL> alter system set undo_tablespace = undotbs1 scope = both;

System altered.

9. Wait for all undo segment offline of the original undo tablespace;

SQL> select USN, xacts, status, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks
2 from V $ rollstat order by rssize;

10. Run the following command to check whether all undo segments in the Undo tablespace are online;

SQL> select USN, xacts, status, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks
2 from V $ rollstat order by rssize;

11. Delete the original undo tablespace;

SQL> drop tablespace undotbs2 including contents;

Tablespace dropped.

12. confirm whether the deletion is successful;

SQL> select name from V $ tablespace;

Name
------------------------------
Cwmlite
Drsys
Example
Indx
ODM
System
Tools
Users
XDB
Temp
Testlib
Undotbs1

12 rows selected.

13. Before proceeding to this step, please go to $ ORACLE_HOME/dbs/init $ oracle_sid.ora to see if the following content has changed:

# Cat $ ORACLE_HOME/dbs/initddptest. ora
......
*. Undo_management = 'auto'
*. Undo_retention = 10800
*. Undo_tablespace = 'undotbs2'
......

If no change occurs, run the following statement:

SQL> Create pfile from spfile;

File Created.

14. The data file in the volume except the original undo tablespace is named as the result of execution in the step.

# Rm $ oracle_base/oradata/$ oracle_sid/undotbs02.dbf

 

Appendix: Undo tablespace Introduction

The undo tablespace is used to store undo data. When you perform DML operations (insert, update, and delete), Oracle writes the old data of these operations to the Undo segment before Oracle9i, rollback segment is used to manage undo data. from Oracle9i, you can use not only rollback segments but also undo tablespaces to manage undo data. the planning and management of rollback segments are complex. All Oracle databases have completely discarded 10 Gb of rollback segments. the undo tablespace is used to manage undo data.

Undo data is also called rollback data, which is used to ensure data consistency. when a DML operation is executed, the data before the transaction operation is called an undo record. the undo segment is used to save the old value of the data modified by the transaction, where the location of the modified data block and the data before the modification are stored,

The role of Undo data.

1. roll back the transaction

When you perform the DML operation to modify the data, the Undo data is stored in the Undo segment, and the new data is stored in the data segment. If the transaction operation has problems, the old transaction needs to be rolled back, to cancel the transaction change. assume that user a executes the statement update EMP set sal = 1000 where empno = 7788 and finds that the employee's salary should be changed to 7963 instead of 7788, you can cancel transaction changes by executing the rollback statement. when the rollback command is executed, Oracle writes undo data 800 of the Undo data segment back to the data segment.

2. read consistency

When a user retrieves database data, Oracle always uses the data that the user can only see submitted data (read submitted) or specific time point (select statement time point ). this ensures data consistency. for example, when user a executes the update EMP set sal = 1000 where empno = 7788 statement, the Undo record is stored in the rollback segment, the new data is stored in the EMP segment. Assume that the data has not been submitted yet, and user B executes select Sal from EMP where empno = 7788. In this case, user B obtains the Undo data 800, the data is obtained in the Undo record.

3. transaction recovery

Transaction recovery is a part of routine recovery, which is automatically completed by the Oracle server. if a routine failure (such as power failure, memory failure, and background process failure) occurs during database operation, the background process SMON automatically executes routine recovery when the Oracle server is restarted, when a routine is executed for restoration, the distinct l will re-create all unapplied records. roll back uncommitted transactions.

4. Reverse query (flashback query)

Flashback query is used to obtain database data at a specific time point. It is a newly added feature of 9i. It is assumed that the current time is a.m, A user executes the update EMP set sal = 3500 where empno = 7788 statement at AM, modifies and submits the transaction (the employee's original salary is 3000). In order to get the employee's salary before, you can use flashback to query features.

Use undo Parameters

1, undo_management

This initialization parameter is used to specify the Undo data management mode. to use the automatic management mode, you must set this parameter to auto. If you use manual management mode, you must set this parameter to manual, oracle uses the Undo tablespace to manage undo data. In the manual mode, Oracle uses the rollback segment to manage undo data,

Note that when the automatic management mode is used, if the initialization parameter undo_tablespace is not configured, Oracle will automatically select the first available undo tablespace to store undo data. If there is no available undo tablespace, oracle uses the system rollback segment to store undo records and records warnings in the alter file.

2, undo_tablespace

This initialization parameter is used to specify the Undo tablespace to be used by the routine. When using the automatic undo management mode, you can configure this parameter to specify the Undo tablespace to be used by the routine.

In the RAC (real application cluster) structure, because an undo tablespace cannot be used by multiple routines at the same time, each routine must be configured with an independent undo tablespace.

3, undo_retention

This initialization parameter is used to control the maximum retention time of Undo data. Its default value is 900 seconds. From 9i, you can specify the retention time of Undo data by configuring this initialization parameter, so as to determine the earliest time point that can be viewed by the flashback query feature (flashback query.

Create an undo tablespace,

The undo tablespace is used to store undo data, and no Data Objects (tables, indexes, and clusters) can be created in the Undo tablespace)

1. Use the create database Command to create the Undo tablespace.

When you use the create database Command to create a database, you can create an undo tablespace by specifying the Undo tablespace option. The example is as follows:

Create Database db01

...

Undo tablespace undotbs_01

Datafile '/u01/Oracle/rbdb1/undo0101.dbf' size 30 m;

Note: The undo tablespace clause is not mandatory. If the automatic undo management mode is used and this clause is not specified, the Undo tablespace named sys_undotbs is automatically generated when the database is created.

2. Run the create undo tablespace command to create the Undo tablespace.

Create undo tablespace undotbs3

Datafile 'd: demoundotbs3.dbf 'size 10 m;

Modify the Undo tablespace,

Use the alter tablespace command to modify the Undo tablespace.

When the transaction uses up the Undo tablespace, use alter tablespace... Add datafile add data files

When the disk of the Undo tablespace is full, use alter tablespace... Rename datafiel command to move data files to other disks.

Use alter database... Offline/online: offline/online tablespace.

When the database is in archivelog mode, use alter tablespace... The begin backup/end BACKUP command backs up the Undo tablespace.

Switch undo tablespace.

After the routine is started and the database is opened, a specific routine can only use one undo tablespace at a time. Switching an undo tablespace refers to the Undo tablespace currently used by the stop routine and starting other undo tablespaces, the following describes how to switch the undotbs2 tablespace.

Alter system set undo_tablespace = undotbs02;

In the RAC (real application cluster) mechanism, different routines must use independent undo tablespace instead of the same undo tablespace.

Delete the Undo tablespace.

The undo tablespace being used by the current routine cannot be deleted. If you are sure to delete the Undo tablespace being used by the current routine, switch to the Undo tablespace first, and then delete the corresponding undo tablespace.

Drop tablespace undotbs3;

1. Determine the Undo tablespace used by the current routine.

Show parameter undo_tablespace

2. display all undo tablespaces of the database.

Select tablespace_name fromdba_tablespaces where contents = 'undo ';

3. displays the Undo tablespace statistics.

When using the automatic undo management mode, you need to properly set the size of the Undo tablespace. For example, to reasonably plan the size of the Undo tablespace, you should collect the statistics of the Undo tablespace during the peak hours of database operation. finally, the size of the Undo tablespace is determined based on the statistics. you can collect undo statistics by querying the dynamic performance view v % undostat.

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 indicates the start statistical time, end_time indicates the end statistical time, And undoblks indicates the number of data blocks occupied by undo data. Oracle generates a row of statistical information every 10 minutes.

4. displays the Undo segment statistics.

When the automatic undo management mode is used, Oracle automatically creates 10 undo segments in the Undo tablespace. by querying the dynamic information view v $ rollname, the names of all undo segments of the connected machine can be displayed, you can query the dynamic performance view v $ rolllistat to display the statistics of the Undo segment. by performing connection queries between V $ rollname and V $ rolllistat, You can monitor specific information of specific Undo segments.

Select a. Name, B. xacts, B. writes, B. extents

From v $ rollname A, V $ rollstat B

Where a. USN = B. USN;

Name indicates the name of the Undo segment, and xacts identifies the number of active transactions contained in the Undo segment,

Writes is used to identify the number of bytes written on the Undo segment, and extents is used to identify the number of Undo segments.

5. displays the active transaction information.

When a DML operation is performed, Oracle places the old data of the operation into the Undo segment. The dynamic performance view v $ session is used to display session details, dynamic Performance view v $ transaction is used to display transaction details. Dynamic Performance view v $ rollname is used to display the name of online undo segments. by performing a connection query between the three dynamic performance views, you can determine the session, the Undo segment used by the transaction, and the number of Undo blocks occupied by the transaction.

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 undo zone information

Data Dictionary view dba_undo_extents is used to display detailed information about all zones in the Undo tablespace, including the size and status of the Undo tablespace.

Select extend_id, bytes, status from dba_undo_extents

Where segment_name '_ syssmu5 $ ';

Extent_id is used to identify the area number, bytes is used to identify the area size, and status is used to identify the area status (active: indicates that the area is active, expired: indicates that the area is not used ).