Friday, December 11, 2015

Preparations to make before running dbms_redefinition on a large table



To avoid ORA-01555 "snapshot too old", you should take the following precaution before starting an online redefinition of a large table:

1. If possible, create a new BIGFILE undo tablespace:
CREATE BIGFILE UNDO TABLESPACE UNDOTBS2 DATAFILE
  '/u02/oradata/proddb01/undotbs02_01.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
RETENTION NOGUARANTEE;

2. Make the new UNDO tablespace database default:
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;

3. Alter the retention of UNDO data to GUARANTEED retention:
ALTER TABLESPACE UNDOTBS2 RETENTION GUARANTEE;

4. If possible, create a new BIGFILE temporary tablespace:
CREATE BIGFILE TEMPORARY TABLESPACE TEMP2 TEMPFILE
'/u02/oradata/proddb01/temp2_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

5. Make the new temporary tablespace the database default:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";

6. Empty the recyclebin. As sysdba:
PURGE RECYCLEBIN;

7. Adjust the parameter undo_retention
Set the parameter high enough to sustain operation of the database for the amount of time you expect the redefintion to last.
For example, if you expect the redefinition to run for 48 hours, set the undo_retention to 172800 seconds:
alter system set undo_retention = 172800 scope=memory;

When the online redefinition has completed, return the UNDO tablespace to its normal retention:
ALTER TABLESPACE UNDOTBS2 RETENTION NOGUARANTEE;

About RETENTION GUARANTEED:
... specifies that Oracle Database should preserve unexpired undo data in all undo segments of tablespace even if doing so forces the failure of ongoing operations that need undo space in those segments. This setting is useful if you need to issue an Oracle Flashback Query or an Oracle Flashback Transaction Query to diagnose and correct a problem with the data.

About RETENTION NOGUARANTEE:
... returns the undo behavior to normal. Space occupied by unexpired undo data in undo segments can be consumed if necessary by ongoing transactions. This is the default

Source: Oracle Documentation

More about undo retention can be found here

Setting the minimum undo retention period

No comments:

Post a Comment