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

This article mainly introduces the production environment Oracle undo table space management related knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that everyone after reading this production environment Oracle undo table space management article will have a harvest, let's take a look.

What is undo and why is it undone in oracle?

The Oracle database has a way to maintain information to roll back or undo changes to the database. The Oracle database keeps a record of transaction operations before the transaction commits, and Oracle needs this information to roll back or undo changes to the database. These records are called rollback or undo records.

These records are used to:

Rollback transaction-when a ROLLBACK statement is issued, the undo record is used to undo changes made to the database by the uncommitted transaction.

Restore Database-during database recovery, the undo record is used to undo any uncommitted changes applied to the data file from the redo log.

Provide read consistency-undo records provide read consistency by maintaining a pre-image of the data for users who are accessing the data while another user is changing the data.

Use Flashback Query to analyze data from earlier points in time.

Use the flashback feature to recover from logical corruption.

Prior to Oracle 8i, Oracle used rollback segments to manage undo data. Oracle9i introduces automatic undo management, which allows dba to exert more control over the retention time of undo information, simplifies undo space management, and eliminates the complexity of managing rollback segments. Oracle strongly recommends that you use undo tablespaces to manage undo instead of rollback segments.

The space of the undo segment is dynamically allocated, used, released, and reused-- all under the control of the Oracle database, not DBA.

Starting with Oracle 9i, the rollback segment method is called "manual undo management mode," and the new method of undoing tablespaces is called "automatic undo management mode."

Description:

Although rollback segments and undo tablespaces are supported, they cannot be used in the same database instance, although for migration purposes, undo tablespaces can be created in databases that use rollback segments, or delete rollback segments in databases that use undo tablespaces. However, you must return the database to switch to another way to manage undo.

There is a system rollback segment in both modes.

When running in automatic undo management mode, any manual undo administrative SQL statements and initialization parameters are ignored and no error messages are issued, such as ALTER ROLLBACK SEGMENT statements.

Manage undo tablespaces 2.1 create undo tablespaces

There are two ways to create an undo tablespace:

The first method is to create an undo tablespace when you issue a CREATE DATABASE statement. This happens when you create a new database and the instance starts in automatic undo management mode (UNDO_MANAGEMENT = AUTO).

The second method: for existing databases. It uses CREATE UNDO TABLESPACE statements.

We cannot create database objects in the undo tablespace. It retains undo data for system administration. The Oracle database enables us to create single-file undo tablespaces.

The following statement illustrates the use of the UNDO TABLESPACE clause in the CREATE DATABASE statement. The undo tablespace is named undotbs_01 and is assigned a data file

SQL > CREATE DATABASE... UNDO TABLESPACE undotbs_01 DATAFILE'/ path/undo01.dbf' RETENTION GUARANTEE

If the undo tablespace cannot be successfully created during CREATE DATABASE, the entire operation will fail. The CREATE UNDO TABLESPACE statement is the same as the CREATE TABLESPACE statement, but the UNDO keyword is specified. The database determines most of the properties of the undo table space, but you can specify the DATAFILE clause.

This example creates a undotbs_02 undo tablespace:

SQL > CREATE UNDO TABLESPACE undotbs_02 DATAFILE'/ path/undo02.dbf' SIZE 2m REUSE AUTOEXTEND ON RETENTION NOGUARANTEE; 2.2 resize Undo tablespaces

Method 1: reduce the size of the undo table space

The allocated undo space will be reused, but will not be reassigned to the operating system. The best way to shrink the Undo table space is to switch to the new Undo table space and delete the old Undo table space. 、

The steps are:

Create a new undo tablespace of the same size (larger or smaller) based on your database requirements.

SQL > create undo tablespace UNDOTBS2 datafile'D:\ ORACLE\ PRODUCT\ 11.2.0\ ORADATA\ ORCL\ UNDOTBS02.DBF' size 5000m

Switch to the new Undo tablespace:

SQL > ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH

Check the status of the undo segment to determine whether the segment in the old undo tablespace is in the offline state.

Sql > select tablespace_name, status, count (*) from dba_rollback_segs group by tablespace_name, status

If there are Undo segments in the tablespace whose state is not OFFLINE to be deleted, we need to wait until they become OFFLINE. You may have to wait for the duration of the tuned_undoretention (from v$undostat) to ensure that all undo segments have become OFFLINE.

Sql > select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name= [undo tablespace to be dropped]

For example:

Sql > select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name='UNDOTBS1'

If all Undo segments in the old Undo tablespace are in the OFFLINE state, the tablespace is deleted.

Sql > select tablespace_name, status, count (*) from dba_rollback_segs group by tablespace_name, status

Validate and then discard:

Sql > drop tablespace [tablespace_name] including contents and datafiles

For example:

Sql > drop tablespace UNDOTBS1 including contents and datafiles

Method 2: add space to the undo tablespace

There are two options for increasing / resizing the undo tablespace:

Resize existing undo data files

Add a new undo data file to the tablespace.

Resize the existing undo data file:

Col T_NAME for A23 col FILE_NAME for A65 select tablespace_name title name, bytes/1024/1024 MB from dba_data_files where tablespace_name = (SELECT UPPER (value) FROM v$parameter WHERE name = 'undo_tablespace') order by file_name; alter database datafile' [COMPLETE_PATH_OF_UNDO_DBF_FILE] 'resize [SIZE] M

For example:

Sql > alter database datafile'D:\ ORACLE_DB\ TESTDB\ TESTDB\ UNDOTBS01.DBF' resize 1500m

To add a new data file:

Sql > alter tablespace [UNDO tbs name] ADD DATAFILE'[COMPLETE_PATH_OF_UNDO_DBF_FILE] 'size 20m

For example:

Sql > alter tablespace UNDOTBS1 ADD DATAFILE'D:\ ORACLE_DB\ TESTDB\ TESTDB\ UNDOTBS02.DBF' size 20m; III. Best practices for removing tablespaces / undoing management in Oracle

The following list of suggestions will help you manage the undo space to your maximum advantage.

Unless your system has flashback or LOB retention requirements, you do not need to set a value for the UNDO_RETENTION parameter.

Set aside 10% to 20% extra space in the undo tablespace to cope with some fluctuations in the workload.

Correctly set warning and critical alert thresholds for undoing tablespace alerts.

To adjust the SQL query or check for runaway queries, retrieve the SQL text and other details about the SQL from the V$SQL view using the values of the SQLID column provided in the long query or in the V$UNDOSTAT or WRH$_UNDOSTAT view.

This is the end of the article on "how to manage Oracle undo tablespaces in the production environment". Thank you for reading! I believe you all have a certain understanding of the knowledge of "how to manage Oracle undo tablespaces in production environment". If you want to learn more, you are welcome to follow the industry information channel.

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.