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

First, the failure phenomenon:

When deleting the data of a very large table, there was an error: ORA-30036:

SQL > delete fromCRT_CURING_PRESS_TEMPHISTORY

2 whereRECORD_TIMEselect tablespace_name,file_name fromdba_data_files

2. Check the undo tablespace usage:

SQL > select file_name,bytes/1024/1024from dba_data_files where tablespace_name like 'UNDOTBS%'

Or,

SQL > SELECT a.tablespace_name as tablespace_name

To_char (b.totalUniverse 1024Universe 1024999999.99) as Total

To_char ((b.total-a.free) / 1024Universe 1024999999.99) as Used

To_char (a. Freebase 1024) as Free

To_char (round ((total-free) / total,4) * 100999.99) as Used_Rate

FROM (SELECT tablespace_name, sum (bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a

(SELECT tablespace_name, sum (bytes) total FROM DBA_DATA_FILES GROUP BYtablespace_name) b

WHERE a.tablespace_name=b.tablespace_name

ANDa.tablespace_name='UNDOTBS1'

ORDER BY a.tablespace_name

3. Wait for all the UNDO SEGMENT OFFLINE of the original UNDO tablespace

SQL > select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024,shrinks from v$rollstat order by rssize

SQL > selectt.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segst

4. Add undo data file!

SQL > alter tablespace undotbs1 add datafile'D:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ UNDOTBS02.DBF' size 1000m AUTOEXTEND on next200m maxsize unlimited

5. Delete the original UNDO tablespace

SQL > create pfile from spfile

SQL > drop tablespace undotbs1 includingcontents

Finally, you need to delete the data file under the path where the data file is stored after restarting the database or computer (why delete it manually: the above steps only delete the logical relationship of the undo tablespace in ORACLE, that is, delete the association of the data file in the data dictionary, not the data file associated with the item automatically).

Droptablespace undotbs1 including contents and datafiles

Add undo data file!

SQL > alter tablespace undotbs1 add datafile'D:\ APP\ ADMINISTRATOR\ ORADATA\ ORCL\ UNDOTBS02.DBF' size 1000m AUTOEXTEND on next200m maxsize unlimited

3. The principle is transparent.

The UNDO tablespace is used to store UNDO data. When performing DML operations (INSERT,UPDATE and DELETE), oracle writes the old data of these operations to the UNDO segment, which is completed by using (Rollback Segment) to manage UNDO data before oracle9i. Starting with oracle9i, managing UNDO data can use not only rollback segments but also UNDO tablespaces. Because of the complexity of planning and managing rollback segments, all oracle database 10g have been completely discarded. And use UNDO tablespaces to manage UNDO data.

UNDO data, also known as 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 segment is used to hold the old value of the transaction modified data, which stores the location of the modified data block and the pre-modified data.

The role of UNDO data.

1. Fallback transaction

When the DML operation is performed to modify the data, the UNDO data is stored in the UNDO segment, while the new data is stored in the data segment. If there is a problem with the transaction operation, it is necessary to roll back the transaction to cancel the transaction change. Assuming that user An executes the statement UPDATEemp SET sal=1000 WHERE empno=7788 and finds that 7963 of the employee's salary should be modified instead of 7788 of the employee's salary, the transaction change can be cancelled by executing the ROLLBACK statement. When executing the ROLLBACK command, oracle writes the UNDO data 800 (payroll) of the UNDO segment back to the data segment.

2, read consistency

When a user retrieves database data, oracle always uses that the user can only see the submitted data (read submission) or data at a specific point in time (SELECT statement point in time). This ensures the consistency of the data. For example, when user An executes the 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 submitted and user B executes SELECT salFROM emp WHERE empno=7788, user B will get 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 oracleserver. If routine failure occurs during database operation (such as power outage, memory failure, background process failure, etc.), the background process SMON will automatically perform routine recovery when oracle server is restarted, and oracle will redo all unapplied records when routine recovery is performed. Roll back uncommitted transactions.

4, flashback query (FlashBack Query)

Flashback query is used to get database data at a specific point in time. It is a new feature of 9i. Assuming that the current time is 11:00, a user executes the UPDATE emp SET sal= 3500 WHERE empno=7788 statement at 10:00 and modifies and commits the transaction (the original salary of the employee is 3000). In order to get the employee salary before 10:00, the user can use the flashback query feature.

Use the UNDO parameter

1,UNDO_MANAGEMENT

This initialization parameter is used to specify how UNDO data is managed. If you want to use automatic management mode, you must set this parameter to AUTO. If you use manual management mode, you must set this parameter to MANUAL. When using automatic management mode, oracle will use undo tablespace to manage undo management, and when you use manual management mode, oracle will use rollback segments to manage undo data.

It should be noted that when using automatic management mode, if the initialization parameter UNDO_TABLESPACE,oracle is not configured, the first available UNDO table space will be automatically selected to store UNDO data. If there is no available UNDO table space, oracle will use the SYSTEM rollback segment to store UNDO records and record warnings in the ALTER file.

2,UNDO_TABLESPACE

This initialization parameter is used to specify the UNDO table space to be used by the routine. When using the automatic UNDO management mode, the UNDO table space to be used by the routine can be specified by configuring this parameter.

In the RAC (RealApplication Cluster) structure, because a UNDO table space cannot be used by multiple routines at the same time, each routine must be configured with a separate UNDO table space.

3,UNDO_RETENTION

The initialization parameter is used to control the maximum retention time of UNDO data, and its default value is 900s. Starting from 9i, by configuring this initialization parameter, you can specify the retention time of undo data and determine the earliest time point at which flashback query features (FlashbackQuery) can be seen.

Establish UNDO tablespace

UNDO tablespaces are dedicated to storing UNDO data, and no data objects (tables, indexes, clusters) can be created in UNDO tablespaces.

1. Use the CREATE DATABASE command to establish the UNDO tablespace.

When using the CREATEDATABASE command to build a database, you can create an UNDO tablespace 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. If you use automatic UNDO management mode and do not specify this clause, a UNDO tablespace named SYS_UNDOTBS will be automatically generated when you set up the database.

2, use the CREATE UNDOTABLESPACE command to establish the UNDO tablespace.

CREATE UNDO TABLESPACE undotbs2

DATAFILE'Dpart demoundotbs2.dbf' SIZE 10m

Modify UNDO tablespace

Use the ALTER TABLESPACE command to modify the UNDO tablespace.

When the transaction runs out of UNDO table space, use ALTER TABLESPACE... ADD DATAFILE adds data files

When the disk where the UNDO table space is located is full, use ALTER TABLESPACE. The RENAME DATAFIEL command moves the data file to another disk.

Use ALTER DATABASE... OFFLINE/ONLINE takes tablespaces offline / online.

When the database is in ARCHIVELOG mode, use ALTER TABLESPACE... The BEGIN BACKUP/END BACKUP command backs up the UNDO tablespace.

Switch UNDO tablespaces.

After starting the routine and opening the database, only one UNDO table space can be used by a specific routine at the same time. Switching UNDO table space means to stop the UNDO table space currently used by the routine and start other UNDO table spaces. The following takes enabling undotbs2 table spaces as an example to illustrate the method of switching UNDO table spaces.

ALTER SYSTEM SET undo_tablespace=undotbs02

In the RAC (Real Application Cluster) mechanism, different routines must use independent UNDO table spaces, but can not share the same UNDO table space.

Delete UNDO tablespaces.

The UNDO table space being used by the current routine cannot be deleted. If you are sure to delete the UNDO table space being used by the current routine, you should first switch the UNDO table space. Then delete the corresponding UNDO tablespace.

DROP TABLESPACE undotbs1

1. Determine the UNDO tablespace being used by the current routine.

Show parameter undo_tablespace

2, display all the UNDO table spaces of the database.

SELECT tablespace_name FROMdba_tablespacesWHERE contents='UNDO'

3, display UNDO tablespace statistics.

When using the automatic UNDO management mode, it is necessary to set the size of the UNDO table space reasonably, take it as an example to plan the size of the UNDO table space reasonably, and collect the statistical information of the UNDO table space during the peak period of database operation. Finally, the size of the UNDO table space is determined according to the statistical information. 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 is used to identify the start statistical time, END_TIME is used to identify the end statistical time, and UNDOBLKS is used to identify the number of blocks occupied by UNDO data. Oracle generates a row of statistics every 10 minutes.

4. Display UNDO segment statistics.

When using automatic UNDO management mode, oracle automatically establishes 10 UNDO segments on the UNDO table space. The names of all online UNDO segments can be displayed by querying the dynamic information view V$ROLLNAME, and the statistical information of UNDO segments can be displayed by querying the dynamic performance view V$ROLLLISTAT. You can monitor specific information for specific UNDO segments by performing join queries 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 name of the UNDO segment, and xacts is used 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, and extents is used to identify the number of extents of the UNDO segment.

5. Display active transaction information.

When performing DML operations, oracle puts the old data of these operations into UNDO segments, the dynamic performance view v$session is used to display session details, the dynamic performance view v$transaction is used to display transaction details, and the dynamic performance view v$rollname is used to display the names of online UNDO segments. By executing join queries between the three dynamic performance views, we can determine the session performing the transaction operation, the UNDO segments 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 bjorn vicious transaction c

WHERE a.saddr=c.ses_addr AND b.usn=c.xidusn

AND a. Usernamekeeper curve'

6, display UNDO area information

The data dictionary view dba_undo_extents is used to display details of all extents of the UNDO tablespace. Including UNDO area size and status and other information.

SELECT extend_id, bytes, status FROMdba_undo_extents

WHERE segment_name'_SYSSMU5 $'

Among them, extent_id is used to identify the area number, bytes is used to identify the size of the area, and status is used to identify the status of the area (ACTIVE: indicates that the area is active, EXPIRED: indicates that the area is not used).

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.