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

I. the generation of UNDO

The dml operation produces blocks of undo.

When update, server process will find the record block of the record in databuffer, and if not, find and read databuffer from datafile. Before modification, put the active state of the undo segment, and record the location of the data block in the undo segment in the data block header. The transaction slot will be occupied when reading and writing the block, and the transaction number will be recorded in the head of the data block. Then update, and put the block into the dirty list checkpoint queue, waiting for dbwr to write.

II. The role of UNDO

The main purpose of the introduction of the reduction section is to solve three problems.

1 transaction recovery: during the DML operation, during the insert, update and delete operations, the undo segment records the reverse operation of the transaction and the redo log also records the operation of the undo segment, that is, redo protects the information of the undo segment. When the instance shuts down or crashes unexpectedly, when open again, the instance needs to roll back the transaction without commit to complete the recovery of the transaction.

2 transaction rollback: the user does not perform commit after the DML operation, and the data before modification is needed. As long as the operation is protected by the undo segment, performing the rollback operation at this time can roll back to the state of the most recent record point or the state after the last commit operation, and return to the state before the data modification.

Read consistency: when performing a DML operation, the undo segment records the state of the data before it is changed (by constructing a consistent data block of the original data). If the user has not performed the commit operation, others will query this piece of data to see the status of the data before the change. Because the data read by other users is the data in the original data block in the undo segment, the consistency of data reading without commit is guaranteed.

4 flashback query: flashback query, flashback table

After 10G, new features are added one by one flashback. Flashback databases and tables using blocks that undo has committed. Block inactive data that has been committed cannot be committed, but can be rewind to a certain point in time.

SQL > select name, flashback_on from v$database

We often ignore monitoring of it, which can lead to the following problems with UNDO tablespaces:

1)。 The space utilization rate is 100%, which makes the DML operation impossible.

2)。 There are a large number of ORA-01555 alarm errors in the alarm log.

3)。 Instance recovery failed and the database could not be opened properly.

III. Analysis of undo parameters

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

1 initialization parameter undo_management

This initialization parameter is used to specify how UNDO data is managed. If you want to use automatic management mode, for AUTO; if you use manual management mode, for MANUAL.

When using automatic management mode, oracle uses undo tablespaces to manage undo management, and when using manual management mode, oracle uses rollback segments to manage undo data.

If you use automatic management mode, if the initialization parameter UNDO_TABLESPACE is not configured. Oracle will automatically select the first available UNDO table space to store UNDO data. If no UNDO table space is available, oracle will use the SYSTEM rollback segment to store UNDO records and record warnings in the ALTER file

2 undo_retention

This parameter is a time value. Indicates how long the transaction in the restore segment will be retained after it is committed, and perform operations such as flashback data for tools such as flashback. The default value of this parameter is 900s and can be modified dynamically.

When the retention time exceeds the time specified by undo_retention, the undo block can be overwritten by other transactions. When we use AUM and set undo_retention, the state of the undo block exists in the following four situations:

Active: active, indicating that the transaction that is using the block has not been committed or rolled back.

Inactive: inactive, indicating that there is no active transaction on the data block, and the data block in that state can be overwritten by other transactions.

Expired: if the time limit is reached, the inactive duration of the data block has exceeded the time specified by undo_retention. If there is no freed,

Freed: released, indicating that the data block is empty and has never been used.

To view the value of Oracle automatically adjusting undo_retention, you can obtain it from the following query:

Select to_char (begin_time,'mm/dd/yyyy hh34:mi:ss') begin_tiem,TUNED_UNDORETENTION from v$undostat

Application order for undo tablespaces to get spaces

Freed= > expired= > automatic extension (this parameter must be in yes state) = > inactive (the coverage of this state is explained in detail below) = > ORA-30036

And try to use a relatively short contiguous extent when using blocks, and use a more contiguous extent when it is insufficient. This can reduce the production of fragments. And try not to overwrite the data block in the inactive state, if there is enough space, it will maximize the information contained in the data block in this state.

Why ORA-1555 appears in data query, and how to avoid it?

There are two reasons for ORA-1555 errors.

First of all, explain how Oracle can ensure that the original data can be protected without submitting the changed data after the data is changed.

The original data has been operated by DML such as update and delete, but the changed data has not been commit yet. In this case, Oracle constructs the same consistent data block as the original data through the UNDO segment to ensure that other user data will not be read dirty. However, after commit, the transaction status of the rollback segment occupied by the transaction will be marked as inactive (inactive), and this section in the rollback segment can be overwritten and reused.

A. The rollback segment data is overwritten.

Cause: if a query needs to use the data in the rollback segment consistent read block that has been changed to inactive and overwritten, and wants to achieve consistent read, then Oracle's famous ORA-01555 error will occur at this time. The reason is that the execution time of SQL statement is too long, the UNDO table space is too small, the transaction volume is too large, and the submission is too frequent. As a result, when the consistent read is performed during SQL, the modified original data (UNDO data) has been overwritten in the UNDO table space and cannot construct a consistent read block.

The way to avoid this: increase the capacity of the rollback segment as much as possible, and set the post-submission retention time in the undo_retention parameter as larger as possible. If you need to ensure that the original data can be found absolutely within the retention retention time, then on the premise of ensuring that the capacity of the rollback segment is sufficient, you can add retention guarantee to the tablespace parameters to ensure that the original data will not be overwritten by any circumstances. Optimize the faulty SQL and split large transactions into small transactions as far as possible, and do not commit frequently.

B. Block delay cleanup (Delayed Block Cleanout) causes ORA-1555

The reason for this: compared to the first, the probability of this kind of occurrence is much lower than 11g. Don't go deep.

Set undo_retention parameters

SQL > alter system set undo_retention=1500

Inquire again

SQL > show parameter undo_retention

NAME TYPE VALUE

-

Undo_retention integer 1500

3 UNDO tablespace guarantee attribute

SQL > SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1'

TABLESPACE_NAME RETENTION

UNDOTBS1 NOGUARANTEE

Modify RETENTION

SQL > alter tablespace undotbs1 retention guarantee

Inquire again

SQL > SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1'

TABLESPACE_NAME RETENTION

UNDOTBS1 GUARANTEE

Create and maintain undo tablespaces

1 create

SQL > create undo tablespace undotab1 datafile'/ ooradata/lxtab/test_undotab1.dbf' size 200m autoextend on

-- query

SQL > select tablespace_name,extent_management,contents,logging,status from dba_tablespaces where tablespace_name='UNDOTAB1'

TABLESPACE_NAME EXTENT_MAN CONTENTS LOGGING STATUS

UNDOTAB1 LOCAL UNDO LOGGING ONLINE

-- query

SQL > col FILE_NAME for A50

Select file_name,file_id,bytes/ (1024,1024) Merrill autoextensible from dba_data_files where tablespace_name='UNDOTAB1'

FILE_NAME FILE_ID M AUT

-

/ ooradata/lxtab/test_undotab1.dbf 7 200 YES

Specific syntax see the previous tablespace syntax section, created a 200MB undo tablespace has been online, management for local management, has been protected by logs, and space is insufficient to automatically grow space.

2 undo tablespace renaming

-- check what tablespaces undo has first.

SQL > select tablespace_name,status,contents from dba_tablespaces where CONTENTS='UNDO'

TABLESPACE_NAME STATUS CONTENTS

UNDOTBS1 ONLINE UNDO

UNDOTBS2 ONLINE UNDO

UNDOTBS ONLINE UNDO

UNDOTAB1 ONLINE UNDO

UNDOTAB2 ONLINE UNDO

-- query default undo tablespace

SQL > show parameter undo_tablespace

NAME TYPE VALUE

-

Undo_tablespace string UNDOTBS1

A) modify the non-system default UNDO tablespace name

SQL > alter tablespace UNDOTAB1 rename to UNDOTAB_one

Verification

SQL > select tablespace_name,status,contents from dba_tablespaces where TABLESPACE_NAME='UNDOTAB_ONE'

TABLESPACE_NAME STATUS CONTENTS

UNDOTAB_ONE ONLINE UNDO

Effective immediately after modification of non-system default UNDO tablespace

B) modify the system default undo tablespace name

SQL > alter tablespace UNDOTBS1 rename to UNDOTBS_ONE

Verification

SQL > show parameter undo_tablespace

NAME TYPE VALUE

-

Undo_tablespace string UNDOTBS1

The system default undo table space needs to be rebooted to take effect after modification.

SQL > shutdown immediate

SQL > startup

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 1500

Undo_tablespace string UNDOTBS_ONE

3 add data files to the undo tablespace

A) query UNDOTAB_ONE tablespace size

SQL > select tablespace_name,file_name,bytes/1024/1024 mforce autoextensible from dba_data_files where tablespace_name='UNDOTAB_ONE'

TABLESPACE_NAME FILE_NAME M AUT

UNDOTAB_ONE / ooradata/lxtab/test_undotab1.dbf 200 YES

B) add data files

SQL > alter tablespace UNDOTAB_ONE add datafile'/ooradata/lxtab/test_undo_tab1.dbf' size 20m

Inquire again

SQL > select tablespace_name,file_name,bytes/1024/1024 mforce autoextensible from dba_data_files where tablespace_name='UNDOTAB_ONE'

TABLESPACE_NAME FILE_NAME M AUT

UNDOTAB_ONE / ooradata/lxtab/test_undotab1.dbf 200 YES

UNDOTAB_ONE / ooradata/lxtab/test_undo_tab2.dbf 10 NO

Change the data file to automatic expansion

SQL > alter database datafile'/ ooradata/lxtab/test_undo_tab2.dbf' autoextend on

Database altered.

4 switch the current default UNDO tablespace

In the actual production, for example, the disk space of the restore tablespace is limited, and the disk where the reduced tablespace is located is too busy (contended by other processes). In order to reduce the high Imax O caused by contention with other processes or avoid the limitation of disk space. Disk performance of the database needs to be improved by switching restore tablespaces.

-- first check the current default undo tablespace

SQL > show parameter undo_tablespace

NAME TYPE VALUE

-

Undo_tablespace string UNDOTBS_ONE

-- switch to UNDOTAB2

SQL > alter system set undo_tablespace=UNDOTAB2

-- query again

SQL > show parameter undo_tablespace

NAME TYPE VALUE

-

Undo_tablespace string UNDOTAB2

V. use of undo tablespaces

1 UNDOTAB2 Tablespace Total size

SQL > select tablespace_name,sum (bytes/1024/1024) M from dba_data_files where tablespace_name='UNDOTAB2' group by tablespace_name

TABLESPACE_NAME M

UNDOTAB2 200

2 UNDOTAB2 tablespace usage

SQL > select owner,segment_name,bytes/1024 k from dba_segments where tablespace_name='UNDOTAB2'

OWNER SEGMENT_NAME K

SYS _ SYSSMU41_3529217193 $128,

SYS _ SYSSMU42_3691377120 $128,

SYS _ SYSSMU43_2276363185 $128,

SYS _ SYSSMU44_2801918226 $128,

SYS _ SYSSMU45_3144470353 $128,

SYS _ SYSSMU46_163396642 $128,

SYS _ SYSSMU47_813829394 $128,

SYS _ SYSSMU48_3430728809 $128,

SYS _ SYSSMU49_2193813215 $128,

SYS _ SYSSMU50_858471824 $128,

10 rows selected.

SQL > select segment_name, v.rssize/1024 k From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn (+) order by segment_name

SEGMENT_NAME K

--

_ SYSSMU3_1723003836 $

_ SYSSMU40_3968832332 $

_ SYSSMU41_3529217193 $120

_ SYSSMU42_3691377120 $120

_ SYSSMU43_2276363185 $120

_ SYSSMU44_2801918226 $120

_ SYSSMU45_3144470353 $120

_ SYSSMU46_163396642 $120

_ SYSSMU47_813829394 $120

_ SYSSMU48_3430728809 $120

_ SYSSMU49_2193813215 $120

From the above two queries, we can see that the values of the two views are almost the same. Usually, when patrolling, we are used to querying dba_segments views to determine the usage of UNDO tablespaces, but querying V$ROLLSTAT data is more accurate.

3 delete tablespace

SQL > drop tablespace UNDOTAB2 including contents and datafiles

5. Flashback

1 Flashback needs to be archived

A) View archive status

SQL > show parameter recovery

NAME TYPE VALUE

-

Db_recovery_file_dest string

Db_recovery_file_dest_size big integer 0

Recovery_parallelism integer 0

B) establish an archive path and change the authorization

ORACLE > mkdir / flash

ORACLE > chown oracle:oinstall / flash

C) set the size and archive path-be sure to set the size first

SQL > alter system set DB_RECOVERY_FILE_DEST_SIZE=4g scope=both

SQL > alter system set db_recovery_file_dest='/flash' scope=both

D) shut down the database and start to mount state

Shutdown immediate

Startup mount

E) start archiving

SQL > alter database flashback on

F) start the database

Alter database open

Verification

SQL > show parameter recovery

NAME TYPE VALUE

-

Db_recovery_file_dest string / flash

Db_recovery_file_dest_size big integer 4G

Recovery_parallelism integer 0

SQL > select name,flashback_on from v$database

NAME FLASHBACK_ON

--

TEST YES

Turn off flashback: you need to repeat the step dcente step is SQL > alter database flashback off; and then proceed to step f

Note: step c can also be used to modify parameters

ORACLE > cd $ORACLE_HOME/dbs

Vi intitest.ora

2 the effect of flashback

Query flashback file size

SQL > select name,bytes/1048576 m from v$sgastat where pool='shared pool' and name like'% flash%'

I'll talk about it in more detail later.

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.