Rollback and rollforward are important means to ensure that the data in the Oracle database is in a consistent state.
--Before 9i version
Oracle uses rollback segments in the database to implement rollback operations when uncommitted data or an instance crash due to system failure
Each table space needs to create a rollback segment. Each table space implements its own management of the rollback segment.
-In 9i and later
Provides a new way to manage data that is rolled back using Oracle's undo tablespace
Automatically revoke the management table space to uniformly manage all DML rollback operations, simplifying the management of rollback work
The rollback segment in 9i, 10g is only used to maintain backward compatibility
The undo section replaces the rollback section in the original version, so all descriptions in this article use undo
The essence of revocation means returning the changes made to the state before the modification, that is, rewinding all DML statements
Second, the content of the revocation paragraph and related features
For any DML operation, both data blocks and undo blocks must be processed, and redo information is also generated
In ACID, A, C, and I require revocation, and D requires redo generation.
INSERT: undo the rowid of the record inserted into the record, if you need to undo, delete the record based on the rowid
UPDATE: Undo the original value of the updated field in the segment record, and overwrite the original value with the new value when undoing
DELETE: Undo segment records the entire row of data, and perform the reverse operation to insert the record into the original table when undoing
-As can be seen from the above, the content of the UNDO paragraph is summarized as follows:
Data is a copy before modification
Obtained from every transaction that changes data
Retained until the end of the transaction
The role of data in the --UNDO section:
Used for rollback operations
Read consistency and flashback query
For recovery in case of transaction failure
--UNDO segment and transaction:
At the start of a transaction, Oracle will assign only one UNDO segment to it. If the segment is used up, Oracle will automatically add another extent to the UNDO segment
One UNDO segment can serve multiple transactions at the same time
--UNDO segment and UNDO table space:
The contents of the UNDO segment are stored in the UNDO tablespace
Only one UDNO table space can be used at any given time
UNDO table spaces must be created as persistent, locally managed, and auto-expandable table spaces
The UNDO tablespace in use cannot be undone or deleted
The UNDO table space uses a circular write method, which is similar to writing online log files. The difference is that UNDO can set the undo_retention retention time.
--UNDO two management methods:
AUTO automatic management (recommended)
MANUAL (only reserved)
Three parameters related to revocation
--Check the version of Oracle in this machine
SQL> SELECT * FROM v $ version;
BANNER
-------------------------------------------------- --------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL / SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production
--View parameters related to UNDO
SQL> SHOW PARAMETER undo;
NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
--undo_management:
Set whether the undo section of the database uses the automatic management mode. The value can be auto or manual. When it is manual, the undo section is not used, that is, the automatic management mode is not used.
This parameter is a static parameter. After modification, the instance must be restarted to take effect.
--undo_retention:
Specifies the time, in seconds, that the undo segment data is retained before it is overwritten after it is inactive in the undo segment. Effective when undo_management bit is auto, it is a dynamic parameter
--undo_tablespace:
Specifies which table space is used to implement data undo. It takes effect when the undo_management bit is auto. It is a dynamic parameter.
--retention guarantee clause:
Guarantee to withdraw reservations, use the following operation to achieve
ALTER TABLESPACE undo_tablespace_name RETENTION GUARANTEE;
--The following query is the result set when undo_management is manual. It can be seen that the undo segments of the undo tablespace are offline.
SQL> SELECT segment_name, tablespace_name, status FROM dba_rollback_segs;
-When the undo_management parameter is manual, insert a new record into scott.emp and receive an error prompt
-Non-system table spaces cannot use rollback segments
SQL> INSERT INTO scott.emp (empno, ename, salary) VALUES (6666, ‘Jenney’, 3000);
INSERT INTO scott.emp (empno, ename, salary)
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’
--Check the type of the segment and found that only the ROLLBACK segment exists in the system tablespace, so the previous insert statement received an error prompt
SQL> SELECT DISTINCT segment_type, tablespace_name FROM dba_segments ORDER BY tablespace_name;
SEGMENT_TYPE TABLESPACE_NAME
------------------ ------------------------------
INDEX EXAMPLE
INDEX PARTITION EXAMPLE
LOBINDEX EXAMPLE
LOBSEGMENT EXAMPLE
NESTED TABLE EXAMPLE
TABLE EXAMPLE
TABLE PARTITION EXAMPLE
INDEX SYSAUX
INDEX PARTITION SYSAUX
LOB PARTITION SYSAUX
LOBINDEX SYSAUX
LOBSEGMENT SYSAUX
NESTED TABLE SYSAUX
TABLE SYSAUX
TABLE PARTITION SYSAUX
CLUSTER SYSTEM
INDEX SYSTEM
LOBINDEX SYSTEM
LOBSEGMENT SYSTEM
NESTED TABLE SYSTEM
ROLLBACK SYSTEM-rollback segments compatible with previous versions
TABLE SYSTEM
TABLE TBS1
TYPE2 UNDO UNDOTBS1-Undo segment used after 9i
INDEX USERS
LOBINDEX USERS
LOBSEGMENT USERS
NESTED TABLE USERS
TABLE USERS
--The undo_management is changed to support automatic management, you need to restart the instance
SQL> ALTER SYSTEM SET undo_management = ‘auto’ SCOPE = SPFILE;
System altered.
Total System Global Area 251658240 bytes
Fixed Size 1218796 bytes
Variable Size 67110676 bytes
Database Buffers 180355072 bytes
Redo Buffers 2973696 bytes
Database mount
ed.
Database opened.
-Check the dba_rollback_segs view again. All the undo segments are online.
-Note the first line of the undo table of the system table space, for the undo of the system table space
SQL> SELECT segment_name, tablespace_name, status FROM dba_rollback_segs;
-From the example above:
ROLLBACK section:-Rollback section compatible with previous versions
TYPE2 UNDO section: --9i undo section used after
Regarding rollback, only one segment type can be used at a time, that is, either the rollback segment compatible with the previous version or the undo segment
In fact, only the undo section is supported after 9i, which can be confirmed from the wrong prompt above
-View the transactions generated by the DML statement
SQL> SHOW USER;
USER is "SYS"
SQL> SELECT * FROM scott.emp WHERE ename = ‘SCOTT’;
SQL> UPDATE scott.emp SET sal = sal * 2 WHERE ename = ‘SCOTT’;
1 row updated.
SQL> SELECT addr, xidusn, status, start_time, used_ublk FROM v $ transaction;
ADDR XIDUSN STATUS START_TIME USED_UBLK
-------- ---------- ---------------- ---------------- ---- ----------
2DA2B17C 9 ACTIVE 07/10/10 20:29:08 1
-See which users currently use the undo segment and the segment size, startup time, activity status, etc.
SQL> SELECT t.xidusn, t.start_time, t.used_ublk, t.status, s.username, r.segment_name FROM v $ transaction t
JOIN v $ session s ON t.ses_addr = s.saddr JOIN dba_rollback_segs r ON r.segment_id = t.xidusn;
Fourth, the creation and management of UNDO tablespace
--Create UNDO table space
Creation syntax: CREATE UNDO TABLESPACE tablespace_name DATAFILE ‘...’ SIZE n
More table space creation: please refer to: Oracle table space and data files
--Switch UNDO table space
Multiple UNDO table spaces allowed in the instance
Can switch from one UNDO tablespace to another UNDO tablespace
Only one UNDO table space can be specified at any time
Use ALTER SYSTEM SET undo_tablespace = undo_tablespace_name to switch
--Delete UNDO table space
DROP TABLESPACE undo_tablespace_name
UNDO tablespace of any instance can be deleted in inactive state
For an active UNDO tablespace, you should switch to another tablespace first, and then delete the tablespace after all transactions are completed
--Demonstration to create, switch and delete UNDO tablespace
--View the table space in the current system
SQL> SELECT file_name, tablespace_name FROM dba_data_files;
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/u01/app/oracle/oradata/orcl/tbs1_2.dbf TBS1
/u01/app/oracle/oradata/orcl/tbs1_1.dbf TBS1
/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE
/u01/app/oracle/oradata/orcl/users01.dbf USERS
/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM
-Create a new UNDO tablespace undo2
SQL> CREATE UNDO TABLESPACE undo2 DATAFILE ‘/u01/app/oracle/oradata/orcl/undotbs02.dbf’ SIZE 3M AUTOEXTEND ON;
Tablespace created.
SQL> SELECT file_name, tablespace_name FROM dba_data_files WHERE tablespace_name LIKE ‘UNDO%’;
--View the UNDO tablespace used by the current system as UNDOBS1
SQL> SELECT name, value FROM v $ parameter WHERE name LIKE ‘undo%’;
NAME VALUE
------------------------------ -------------------- ------------------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900
--Create a table tb_test for demonstration, assuming the session is session1
SQL> CREATE TABLE tb_test (ID INT, Name VARCHAR2 (20));
Table created.
-Insert a record into tb_test, at this time uncommitted will generate UNDO information
SQL> INSERT INTO tb_test SELECT 1, ‘Robinson’ FROM dual;
1 row created.
-At this point open another session, suppose it is session2, switch tablespace in session2
SQL> ALTER SYSTEM SET undo_tablespace = ‘undo2’;
System altered. --Undotbs1 There are uncommitted transactions, can they be successfully switched? I guess there will be problems when flashbacks are like this
SQL> SHOW PARAMETER undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------- -----------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undo2
-Perform a commit in session1
SQL> COMMIT;
Commit complete .-- Commit was successfully executed, and the following query saw the results after submission
SQL> SELECT * FROM tb_test;
ID NAME
---------- ------------------------------
1 Robinson
--After logging out of the system and checking again, the results still exist, and I'm wonder
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
[uniread] Saved history (716 lines)
[[email protected] ~] $ sqlplus / as sysdba;
SQL * Plus: Release 10.2.0.1.0-Production on Sat Jul 10 21:29:36 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT * FROM tb_test;
ID NAME
---------- --------------------
1 Robinson
--Delete UNDO table space
--Insert a new record in session1
SQL> INSERT INTO tb_test SELECT 2, ‘Jack’ FROM DUAL;
1 row created.
-Switch the undo tablespace to undobots1 in session2
SQL> ALTER SYSTEM SET undo_tablespace = ‘undotbs1’;
System altered.
-Immediately delete undo2 in this conversation, indicating that it is in use
SQL> DROP TABLESPACE undo2;
DROP TABLESPACE undo2
*
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDO2’ is currently in use
-Commit transaction in session1
SQL> COMMIT;
Commit complete.
--Delete the tablespace undo2 again in session2, received the same error message
SQL> /
DROP TABLESPACE undo2
*
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDO2’ is currently in use
SQL> SHOW PARAMETER undo --Check that it is indeed switched to undotobs1
NAME TYPE VALUE
------------------------------------ ----------- --- ---------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs1
-Perform the tablespace switch to undobots1 again in session2
SQL> ALTER SYSTEM SET undo_tablespace = ‘undotbs1’;
System altered.
-At this time, undo2 is successfully deleted. It can be seen that the undo tablespace can be successfully deleted after the active transaction is committed or rolled back, and then switched.
SQL> DROP TABLESPACE undo2;
--Check the usage of undo tablespace after inserting records into tb_test
SQL> BEGIN
2 FOR i IN 1..20000
3 LOOP
4 INSERT INTO tb_test VALUES (i, ‘Unkown Name’);
5 END LOOP;
6 END;
7 /
PL / SQL procedure successfully completed.
-You can see that only one block was used in the UNDO tablespace
SQL> SELECT addr, xidusn, used_ublk FROM v $ transaction;
174 * 8 |
------
1392KB
Five, calculate the size of the UNDO table space
Calculation formula:
MAX (undoblks) / 600 * MAX (maxquerylen) at v $ undostat
* db_block_size is at v $ parameter
--Create a presentation environment
SQL> INSERT INTO tb_test SELECT employee_id, first_name FROM hr.employees;
107 rows created
SQL> INSERT INTO tb_test SELECT * from tb_test;
109 rows created.
--Execute the above command multiple times, the following is the number of records in the tb_test table
SQL> /
892928 rows created.
SQL> COMMIT;
Commit complete.
--View the size of the current undo tablespace
SQL> SELECT t.name, d.name, d.bytes / 1024/1024 as TotalSize, t.flashback_on, d.status FROM v $ tablespace t JOIN v $ datafile d USING (ts #) WHERE t.name LIKE 'UNDO % ';
NAME NAME TOTALSIZE FLA STATUS
--------------------------------- ----------------- -------------------------- ---------- --- -------
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf 30 YES ONLINE
-Modify the undo tablespace to RETENTION GUARANTEE and turn off automatic expansion
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
Tablespace altered.
SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/orcl/undotbs01.dbf‘ AUTOEXTEND OFF;
Database altered.
SQL> SELECT tablespace_name, contents, retention FROM dba_tablespaces WHERE tablespace_name LIKE ‘UNDO%’;
-Modify the retention time to minutes
SQL> ALTER SYSTEM SET undo_retention = 120;
System altered.
-Cyclic deletion of records in tb_test, prompting that the undo tablespace space is insufficient
SQL> BEGIN
2 FOR i IN 1..1000
3 LOOP
4 DELETE FROM tb_test WHERE rownum <10
01;
5 COMMIT;
6 END LOOP;
7 END;
8 /
BEGIN
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’
ORA-06512: at line 4
--Modify the time parameters of the conversation
SQL> ALTER SESSION SET nls_date_format = ‘yyyy-mm-dd HH24: MI: SS’;
Session altered.
--View the v $ undostat view for relevant information
SQL> SELECT begin_time, end_time, undoblks, maxquerylen, ssolderrcnt, nospaceerrcnt FROM v $ undostat;
-Calculate the required size of the undo tablespace
SQL> SELECT (
2 (SELECT MAX (undoblks) / 600 * MAX (maxquerylen) FROM v $ undostat) *
3 (SELECT value FROM v $ parameter WHERE name = ‘db_block_size’)) / 1024/1024 as Need_Size
4 FROM dual;
For very long transactions or improper SQL scripts will consume a large amount of UNDO table space, using UNDO table space quotas can improve resource utilization
For different groups of users, different maximum UNDO tablespace quotas can be allocated
When a group exceeds the maximum resource limit, the group does not allow new transactions to occur until the UNDO table space of the current group is released or terminated
Seven, undo two common mistakes
--1.ORA-1555 snapshot too old
Configure appropriate retention time (undo_retention)
Adjust the size of the undo tablespace
Consider the use of retention guarantees
--2. ORA-30036 unable to extend segment in undo tablespace
Adjust the size of the undo tablespace
Ensure that a large number of transactions can be submitted periodically
Several related views involved in UNDO:
V $ TRANSACTION
V $ SESSION
DBA_ROLLBACK_SEGS-show all segments
V $ ROLLSTAT
V $ UNDOSTAT
V $ ROLLNAME-Show segments that are currently online
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
