Undo Tablespace
It is recommended for every database to maintain information that can be rolled back or undo the changes to the Database. Just like CTRL + Z.
In ORACLE Databases UNDO records are used to:
- Rollback transactions whenever a rollback statement is issued.
- To recover the database.
- To provide read consistency.
- Flashback and analyze the data as of an earliest point.
- Recover from logical corruptions
When a rollback is issued, undo records are used to undo changes that were made to the database by the uncommitted transactions.
Automatic UNDO Management
You create an UNDO tablespace and server automatically manages undo information and space.
Just set UNDO_MANAGEMENT initialization parameter to AUTO. A default UNDO tablespace is created at database creation.
What if ‘NO’ UNDO Tablespace
When the instance is started, the database automatically selects the first available UNDO tablespace. If no UNDO tablespace is available then the instance starts without an UNDO tablespace and stores the undo information in SYSTEM tablespace.
Create UNDO Tablespace
Create undo tablespace UNDOTBS01 datafile ‘/u01/oracle12c/oradata/undotbs01.dbf’ size 1g autoextend on;
Tablespace created.
ADD DATAFILE TO UNDO
Alter tablespace UNDOTBS01 add datafile '/u01/oracle12c/oradata/undotbs02.dbf' size 1g autoextend on;
Tablespace altered.
UNDO RETENTION
Minimum amount of time that the Oracle database attempts to retain old undo information before overwriting it.
ADJUST UNDO RETENTION
SQL> show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
It is 900 seconds by default, Lets change it to 4 hours i.e 14400 seconds.
SQL> Alter system set undo_retention=14400 scope=both;
Undo retention can also be changed in parameter file.
*.UNDO_RETENTION=14400
No comments:
Post a Comment