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

Http://blog.csdn.net/xujinyang/article/details/6822971

Before we begin, let's think about a few questions.

1. How does the ora-01555 error occur? Is there any way to solve it?

For this problem, refer to my Blog: Oracle ORA-01555 snapshot is too old

Http://blog.csdn.net/xujinyang/article/details/6832722

two。 What is the purpose of the rollback segment (rollback tablespace)?

3. How to load the rollback segment (rollback segment tablespace) when the database starts.

4. What formula is used to calculate the number of rollback segments?

5. How to determine the size of the rollback tablespace?

one。 What is undo?

A means of maintaining database information needed by Oracle databases to roll back, undo, or change data. The database information here refers to transaction information such as changes in records before the database is committed.

Undo information is mainly used for the following purposes:

When the system sends out rollback messages

Database recovery

Provide read consistency

When the system issues the rollback command, the undo information restores the changes in the database to the state before commit through the recorded information. During database recovery, undo information is used to undo any transactions from redo log that are not committed to the data file. When a user is accessing the data, the Undo record maintains the previous mirror data of the access data to ensure the read consistency of the database when other users change the same data.

In the past, databases used rollback segments to store undo information, which is very complex to manage. Now the database adopts undo to reduce the complexity of management and reduce the workload of dba at the same time. But only one of these two ways can be used in the database. You can define files in two ways in the database, but at the same time, you must specify which way the data uses. When you need to switch between the two modes, you must restart the system.

The Oracle database has been using the system rollback segment to complete system transactions. The rollback segment of the system is generated when the database is created and has been online since the system was started. Dba does not need to do anything to optimize it.

two。 How to specify Undo

There is an initialization parameter after oracle 9i: undo_management. The system uses redo tablespaces to manage rollback segments when undo_management is set to AUTO and rollback segments when it is set to MENUAL.

Oracle recommends redo tablespaces instead of rollback segments.

When the system uses auto to manage undo information, the system must specify an undo tablespace. This tablespace can be created when the database is created or created after the database is created.

When the instance starts, the system automatically selects the first valid undo table space or rollback segment. If no valid undo table space or rollback segment is available, the system uses system rollback segment. This situation is not recommended, when the system is running without undo, the system will record a warning message in alert.log.

2.1Automated Management Mode (Automatic Undo Management)

If the system uses to use auto to manage undo information, you need to specify which undo table space the system uses to store undo information by specifying the value of the initialization parameter undo_tablespace. If a value of undo_tablespace is specified, but such tablespaces do not exist in the system, those system boots will fail. What you can do at this point is to specify the correct name of the undo tablespace for undo_tablespace if there is an undo tablespace on the system, or comment the undo_tablespace. The system will adopt the existing undo tablespace. Otherwise, use the manual method.

Related initialization parameters:

Undo_tablespace specifies which redo tablespace the system uses.

When undo_suppress_errors is set to true, the system ignores errors when creating and using rollback segments.

After the undo_retention system is submitted, how long the data of the rollback segment will be retained, in seconds.

When the system is set to menual, these parameters are ignored.

SQL > show parameter undo

NAME TYPE VALUE

Undo_management string AUTO

Undo_retention integer 1000

Undo_tablespace string UNDOTBS1

Supplement: initialization parameter UNDO_RETENTION

This parameter is used to specify the maximum time in seconds for undo records to be saved. It is a dynamic parameter that can be modified at any time while the instance is running. The default is 900 seconds, that is, 15 minutes.

It is important to note that undo_retention only specifies the expiration time of undo data, which does not mean that the data in undo must be saved in the undo table space for 15 minutes. For example, at the beginning of a new transaction, if the undo table space is already full, the data of the new transaction will automatically overwrite the committed transaction data, regardless of whether the data has expired or not, so this comes back to the first point, when you create the

When an automatically managed undo table space, also pay attention to its space size, as far as possible to ensure that the undo table space has enough storage space.

At the same time, note that it does not mean that the data in the committed transaction is inaccessible as soon as the time specified in undo_retention is over, it is just invalid, as long as it is not overridden by other transactions, it will still exist and can be referenced by the flashback feature at any time. If your undo table space is large enough and the database is not so busy, then the value of the undo_retention parameter will not affect you, even if you set it to 1, it will continue to be valid as long as there are no transactions to overwrite undo data. So, here and again, pay attention to the size of the undo table space to make sure it has enough storage space.

There is only one case in which undo tablespaces can ensure that the data in undo must be valid before the time specified by undo_retention expires, that is, Retention Guarantee is specified for undo tablespaces. After that, oracle will not overwrite unexpired undo data in undo tablespaces.

For example:

SQL > Alter tablespace undotbs1 retention guarantee

If you want to disable undo tablespace retention guarantee

For example:

SQL > Alter tablespace undotbs1 retention noguarantee

2.2 manual management model

When the initialization parameter undo_management in the system is set to manual, the rollback segment mode is used to store undo information after the system is started. If the system does not specify undo_management, the system starts in manual mode by default, and even if the parameters in auto mode are set, these parameters will be ignored.

When the instance starts, the system confirms the number of rollback segment of online according to the following steps:

Initialization parameter rollback_segments

Initialization parameters transactions, transactions_per_rollback_segment

Initialization parameters related to menual

Rollback_segments specifies the rollback segment required when the instance is started

Transactions specifies the maximum number of concurrent transactions in the system

Transactions_per_rollback_segment specifies the number of concurrency supported for each rollback segment

Max_rollback_segments indicates the number of rollback segments of the maximum online supported by the system

Three. Manage undo tablespace

There are two ways to create a undo talespace:

1. Create a undo tablespace when the database is created

two。 Create in an existing database.

Database objects cannot be created in undo tablespace because this tablespace is prepared for the database recover.

3.1 create a undo tablespace when creating a database

You can create a undo tablespace when you create a database by specifying the undo clause, but this clause is not required.

If the system specifies auto schema but does not specify the name of undo tablespace when creating the database, the system creates a default rollback tablespace called sys_undotbs. This tablespace is created based on the default values defined by oracle. The initialization size is 10m and can be extended automatically. However, oracle recommends that it is best to use a specified size.

CREATE DATABASE rbdb1

CONTROLFILE REUSE

...

UNDO TABLESPACE undotbs_01 DATAFILE'/ u01qqoracleandrbdb1Universe undo0101.dbf'

Note: if the system fails to create the undo at this time, the entire command to create the database fails. At this time

Dba needs to delete the data files that have been created, correct errors, and rebuild the creation database.

Create using the create undo tablespace clause

CREATE UNDO TABLESPACE undotbs_02

DATAFILE'/ u01 SIZE Oracle Rbdb1Universe undo0201.dbf'According 2m REUSE

AUTOEXTEND ON

3.2 related operations of Undo tablespace

1. Add data files

ALTER TABLESPACE undotbs_01

ADD DATAFILE'/ u01 AUTOEXTEND ON NEXT oracle.rbdb1andundo0102.dbf'According 1m MAXSIZE UNLIMITED

two。 Rename data file

ALTER TABLESPACE undotbs_01 RENAME DATAFILE'/ u01qoracleUniverse rbdb1Universe undo0102.dbf' TO'/ u01andoracleUniplicaterbdb1andundo0101.dbf'

3. Make the data file online or offline

ALTER TABLESPACE undotbs_01 online | offline

4. Start or end an online backup

ALTER TABLESPACE undotbs_01 BEGIN | END BACKUP

5. Delete undo tablespace

Drop tablespace undotbs_01

Drop undo tablespaces can only be done when unused. If the undo tablespace is in use (for example, the transaction failed but the recovery has not been successful), the drop tablespace command will fail. You can use including contents in drop tablespaces.

6. Toggle undo tablespace

There are two ways to switch undo tablespaces:

1. Use the command to modify dynamically

two。 Restart the database after modifying the initialization parameters.

Alter system set undo_tablespace=undotbs1

When the switch command is complete, all transactions will take place in the new rollback tablespace.

The following situations can cause the switch command to fail:

1. Tablespace does not exist

2.. Tablespace is not a rollback segment tablespace

3. The tablespace is already used by another instance.

Note: the switch does not wait for the transaction of the old undo tablespace to commit. If there are transactions in the old undo tablespace that are not committed, the old undo tablespace goes into the pending offline state, in which all transactions can continue, but the undo tablespace cannot be used by other instances or deleted, and the undo tablespace does not enter offline mode until all transactions are committed.

7. Set up undo_retention

Dba can set the undo_retention initialization parameter to specify when the undo rollback tablespace retains undo information. When this parameter is set, the system will retain the undo information to reclaim the space after the specified time is cut off.

In general, the system will retain undo information until the specified time before reclaiming space, but if the system

There are a large number of transactions, and unexpired undo space is reclaimed for use.

8. The calculation formula of the design specification for the size of Undo table space

Undospace = UR * UPS * db_block_size+ redundancy

UR: represents the maximum number of seconds to hold in the undo, determined by the database parameter UNDO_ renew value.

UPS: represents the number of database blocks generated per second in undo.

The dynamic performance view v$undostat related to undo contains statistics for undo. Use this view to estimate the undo size currently required by the system.

V$rollstat is the view of the undo schema. Is the statistics for the undo segments of the undo tablespace

V$transaction contains information about undo segments.

Dba_undo_extents contains the commit time for each range in the undo tablespace.

four。 Manage rollback segment

4.1 guidelines for the use of rollback segments

4.1.1 use of multiple rollback segments

Multiple rollback segments are used to share the contention of rollback segments to improve system performance. The system allocates the rollback segment in a cyclic way. When oracle creates a database, the system automatically allocates a system rollback segment in system to complete system transactions, which is not shared by everyone. So the system can finally have at least one rollback segment to store user rollback information.

The number of user rollback segments that the system can load is related to the following initialization parameters:

Transactions_per_rollback_segment specifies the number of concurrency supported for each rollback segment

Max_rollback_segments indicates the number of rollback segments of the maximum online supported by the system

Rollback_segments specifies the rollback segment required when the instance is started

4.1.2 Select the type of rollback segment

Private must specify a name through an instance before it can be used.

For example, rollback_segments must be specified in the initialization parameters before the instance can be started, or online can only be used after the instance is started.

Public is automatically discovered by the system when the instance is started, and the rollback segment is determined by the system according to the initialization parameters.

4.1.3 specify a rollback segment for the transaction

Specify the required rollback segment at system startup

4.1.4 estimate the size of the rollback segment

The rollback segment size should be based on the largest transaction in the system. If the rollback segment is too small, it is easy to produce ora-01555 errors. You can use the optimize option to restrict automatic recycling of rollback segments. The size of the rollback segment should be 10% of the size of the maximum table, which can specify the number of maxextents.

4.1.5 create rollback segment groups with equal range size and number

Generally speaking, a rollback segment should contain 10 to 20 ranges.

S=T/n

S is the size of the range defined at initialization, T is the size of the rollback segment initialized, and n is the number of ranges. From this, the parameters of the clauses that define the rollback segment can be determined.

4.1.6 define the value of optimal

Setting this parameter can avoid the unlimited expansion of the rollback segment and the automatic recovery of space by the system. The minimum is the size of two ranges.

Set rollback segments in different tablespaces

1: if the system has only one rollback table space, then there is a problem in the rollback segment, which affects the system can not run.

2: tablespaces containing rollback segments are often allocated and deallocated and easily fragmented.

3: when the rollback tablespace is offline, the system will have no rollback tablespace available.

MINEXTENTS minimum equals 2

The minimum OPTIMAL should be set to two extents sizes

INITIAL and NEXT are best the same, except that the application uses the specified rollback segment

4.2 related operations of the rollback segment

4.2.1 create a rollback segment

When creating a rollback segment, the system must have CREATE ROLLBACK SEGMENT system permissions.

The created rollback segment will be online and the storage parameters are specified

CREATE ROLLBACK SEGMENT RB01 TABLESPACE RBS1STORAGE (

INITIAL integer K | M NEXT integer K | M MINEXTENTS integer MAXEXTENTS integer OPTIMAL integer K | M)

4.2.2 modify rollback segment

Modify rollback segment when modifying a rollback segment, the system must have ALTER ROLLBACK SEGMENT system permissions.

Make rollback segment online or offline

ALTER ROLLBACK SEGMENT RB01 ONLINE

Modify storage parameters

ALTER ROLLBACK SEGMENT RB01 STORAGE (MAXEXTENTS 200OPTIMAL 2048K)

Shrink rollback segment

ALTER ROLLBACK SEGMENT RB01 SHRINK

Note: if there is an OPTIMAL parameter, it will be reduced to the OPTIMAL value; if there is no OPTIMAL parameter, it will be reduced to the corresponding size of MINEXTENTS.

ALTER ROLLBACK SEGMENT RB01 SHRINK TO 2048K

ALTER ROLLBACK SEGMENT RB01 STAROGE (MAXEXTENTS 120)

4.2.3 Delete rollback segment

When deleting a rollback segment, the system must have DROP ROLLBACK SEGMENT system permissions.

In principle, INITIAL should always be equal to NEXT, except for rollback segments that use SET TRANSACTION USE ROLLBACK SEGMENT XXX. Since INITIAL cannot be modified directly, it can only be created after drop.

DROP ROLLBACK SEGMENT RB01

CREATE ROLLBACK SEGMENT RB01 TABLESPACE RBS1

STORAGE (INITIAL 100K NEXT 100K MINEXTENTS 20 MAXEXTENTS 121OPTIMAL 2000K)

Use a specific rollback segment in a transaction

SET TRANSACTION USE ROLLBACK SEGMENT RB_LARGE1

With this command, you can:

Decide which rollback segment to use according to the size of the transaction volume

Put large query transactions into a separate rollback segment

When there is a large query using a transaction, you can put it into a large rollback segment.

4.3 performance views related to the rollback segment

DBA_ROLLBACK_GEGS describes the information of the rollback segment, including the name and tablespace of the rollback segment

Additional information that DBA_SEGMENTS describes the rollback segment

V$ROLLNAME lists the name of the online rollback segment

V$ROLLSTAT contains statistics for rollback segments

V$TRANSACTION contains statistics for undo

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.