About Live Reorganizations
Live reorganization with the Reorg Manager is designed to resolve space-use problems with minimum impact on data availability. When tables are reorganized using this method, applications can remain online and users can continue activity against the tables. Data remains available for Data Manipulation Language (DML) statements, including INSERTS, UPDATES, and DELETES.
When the online switch is used for a live reorganization, tables remain available throughout the entire reorganization, including the switch from original table to reorganized copy table. When the T-Lock switch is used, original and copy tables are locked very briefly for the switch.
Note: Before you can perform live reorganizations with Space Manager, the LiveReorg option must be licensed. In addition, the QSA Server Agent must be installed, current, and running.
Live reorganization supports the following. For a detailed list of what is not supported by live reorganizations, see What is Not Supported for Live Reorganizations.
- Non-partitioned and partitioned tables
- Individual partitions and subpartitions
- Tables with LOBs
- Tables with LONGs that are up to two gigabytes in size
- Tables with both a LOB and a LONG
Related Topics
How Live Reorganizations Work
What Is Not Supported for Live Reorganizations
This topic lists which Oracle features are not supported for live reorganizations. It also lists which features are not supported for the T-Lock switch or the Online switch.
Use the information in this topic when selecting which reorganization method to use, which objects to include in a reorganization, and which type of table switch to use in a live reorganization.
Not Supported for Live Reorganization
The following Oracle features are not supported for live reorganization.
| Tables and Indexes |
Entire system-partitioned tables
Note: Individual partitions of system-partitioned tables are supported for live reorganization. |
| |
Entire reference-partitioned tables
Note: Individual partitions of reference-partitioned tables are supported for live reorganization using the T-Lock switch. |
| |
Parent tables of reference-partitioned table |
| |
Indexes without their tables
To reorganize an index online independently of its table, perform a standard reorganization with Rebuild ONLINE |
| |
Temporary tables |
| |
Tables with dimensions |
| |
Advanced Queuing tables |
| |
Replication tables; objects that are part of a replication group |
| |
External tables |
| |
Tables owned by SYS or SYSTEM or with names starting with QUEST_% |
| |
Individual partitions of a partitioned table are not supported if Space Manager needs to create a ROWID posting index and the UseRowidForPartitions parameter is not set to true.
In this special case, the entire partitioned table is reorganized (if supported). See ROWID Posting Indexes for more information. |
| IOTs |
Entire partitioned IOTs
Note: Individual partitions of IOTs are supported for live reorganization. |
| Clusters |
Clusters, clustered tables, cluster indexes, indexes on clustered tables |
| Materialized Views |
Materialized views |
| User Defined Data Types |
Object tables (data types for tables) |
| |
Nested tables |
| Various Data Types |
XMLType tables |
| Miscellaneous |
Oracle Label Security |
| |
Tables with security policies enabled |
Not Supported for T-Lock Switch
The following features are supported for live reorganization, but not the T-Lock switch. When a live reorganization is performed on an object containing one of these features, the Online switch is automatically used.
| IOTs |
Non-partitioned IOTs |
| Materialized Views |
Materialized view logs
Do not refresh views or access associated tables during reorganization; T-Lock switch can be used for partitions and subpartitions when they are reorganized individually(EXCHANGE partition reorganization method is used) |
Not Supported for Online Switch
The following features are supported for live reorganization, but not when using the Online switch. When performing a live reorganization on objects containing these features, the T-Lock switch is required. To create a live reorganization script for objects containing one of these features, do not select the Exclude tables requiring the T-Lock switch (online switch only) option in the Reorg Wizard. See Select Global Reorganization Options.
Note: Oracle’s Partitioning Option must be installed and licensed to use the Online switch.
Note that for the supported partitioned tables and indexes, when the online switch is used, partitions are reorganized individually.
| Tables and Indexes |
Non-partitioned tables with partitioned indexes |
| |
Partitioned tables with global indexes |
| |
Individual partitions of reference-partitioned tables |
| LONG Data Types |
LONGs and LONG RAWs |
Concepts and Terms
This section describes concepts and terms that apply to live reorganizations.
See also, How Live Reorganizations Work.
|
LiveReorg Objects and Tablespace |
During a live reorganization, QSA uses LiveReorg objects and the LiveReorg tablespace. LiveReorg objects are created at the beginning of a live reorganization and stored in the LiveReorg tablespace. This is specified with the agent’s LW_TABLESPACE parameter.
LiveReorg objects include a collection table that is used for storing information on live transactions. One set of LiveReorg objects is created for each table in a live reorganization script. The objects are dropped after the switch from original table to reorganized copy table. |
| Original Table |
This is the table being reorganized. The term “original table” is used for non-partitioned tables, partitioned tables, table partitions, and table subpartitions. |
| Copy Table |
This is a copy of the original table. It is created at the beginning of a live reorganization and all original data is copied into it. Data is reorganized as it is inserted into the copy table. The copy table uses the storage attributes specified in Reorg Manager. If attributes are not specified, the original table’s attributes are used. |
| Live Transactions |
As the copy table and its indexes are created, live DML activity can continue against the original table. Information on live transactions is stored in a collection table. The transactions are then posted (copied) to the reorganized copy table by QSA. |
| Posting |
Posting is the process where live transactions against the original table are copied to its reorganized copy table. Posting is performed by QSA using information on live transactions that is stored in the agent’s collection table. |
| Posting Index |
A posting index is used when live DML transactions are posted to the reorganized copy table. It can serve one or two purposes, depending on whether it is a selective or non-selective index:
- Selective posting indexes are used to uniquely identify rows in the reorganized copy table. They ensure that live transactions are posted to the correct rows. A selective index is one where every index entry points to a single location in table data. The PRIMARY KEY and unique indexes are selective indexes. A nonselective index is one where each index entry points to multiple locations in table data.
- All posting indexes are used to speed up the posting process by increasing the efficiency of searches on the copy table.
What type of index is used as the posting index depends on what is available for a table. Space Manager tries to use the most selective index. See How Space Manager Chooses a Posting Index for more information. |
| Switch |
The switch is the point in a live reorganization when the original table is replaced with its reorganized copy table. At this point, all live transactions have been posted to the copy table so that the original table and copy table are identical in terms of the data they contain. There are two switch styles (or modes) available:
- Online switch—When the online switch is used, a table remains available for DML activity throughout a live reorganization, including the switch.
- T-Lock switch—When the T-Lock switch is used, the table is unavailable during the switch. Any application trying to access the table during the switch will get a database error. Be sure that your application will handle these errors appropriately. See Switch Mode for more information.
|
Best Practices for Live Reorganizations
Read this topic before creating and running live reorganization scripts.
This section provides best practices to follow before and during a live reorganization. It also provides best practices to follow if a live reorganization script is interrupted.
Note: Space Manager provides the best Reorg Need and Wasted space information when statistics are current in the Repository. Be sure to run the Repository Update job on regular or nightly basis (see Change Repository Update Schedule).
CAUTION: Reorganization commands must not be modified or removed from a script. If they are changed in any way, a reorganization can fail or unpredictable results can occur. Most commands begin with QUEST_EXEC.
Before Running a Live Reorganization
Review the following best practices prior to running a live reorganization
- Reserve the LiveReorg tablespace—Only use the LiveReorg tablespace for LiveReorg objects. If you locate a non-LiveReorg object in that tablespace, do not select the object for a live reorganization. Also, do not select a LiveReorg object for a live reorganization. The LiveReorg tablespace is specified with QSA’s LW_TABLESPACE parameter.
- Check free space in the LiveReorg tablespace—Make sure sufficient free space is available in the tablespace being used for LiveReorg objects. If sufficient space is not available, a live reorganization will fail.
- Do not select a table and its log together—If a table has a materialized view log, do not select both the table and the log for the same reorganization. Also, if the table is included in one live reorganization script and the log is included in another live reorganization script, do not run the scripts at the same time.
- Check for constraints that prevent NULLs in LONG columns—Make sure tables with LONG columns do not have constraints that prevent insertion of NULLs into LONGs. If NULLs cannot be inserted into LONG columns, a live reorganization will fail with an error such as “ORA-01400: Cannot insert NULL” or “ORA-02290: Check constraint violated”. Examples of constraints that prevent insertion of NULLs include NOT NULL constraints and CHECK constraints with at NOT NULL condition.
- Select the T-Lock switch for ROWID posting indexes—If a ROWID index must serve as the posting index, select a combination of online and T-Lock switch (the default option). If you select online-only, tables that must use a ROWID index are excluded from reorganization.
- Note time factors for the T-Lock switch and multiple partitions—When multiple partitions or subpartitions from the same table are reorganized with the T-Lock switch, the switch is made individually for each partition or subpartition. If there are global indexes on the parent-partitioned table, these indexes are reorganized along with each partition or subpartition. Due to both factors, it may be faster to reorganize the entire table. Only one switch is made and global indexes are reorganized only once. (When the online switch is used, partitions are always reorganized individually even when the entire parent table is selected for reorganization.)
- Note that SQL is used for tables with UDTs—If a table contains a user-defined datatype (UDT), SQL is used as the data movement method for that table. Tables with UDTs are not supported for FastCopy. Tables with a UDT and a LONG column larger than 32,760 bytes cannot be reorganized on a live basis. This is because neither data movement method can be used for the table. FastCopy cannot be used because of the presence of a UDT. SQL cannot be used because of the size of the LONG column.
- Schedule a live reorganization—Schedule a live reorganization script so that it is run by QSA. LiveReorg commands can only be processed by the agent. Do not run a live reorganization script from the SQL Editor. If you try to do this and ignore error messages, data loss can occur.
Best Practices During a Live Reorganization
-
Do not run DDL statements—Do not run Data Definition (DDL) statements against the objects being reorganized. (DML statements can be run.) DDL activity against a table should be stopped before you open Reorg Manager for that table. It should not be resumed until reorganization of the table is complete. Examples of DDL statements include ALTER TABLE, CREATE INDEX, and DROP TRIGGER.
-
Do not run multiple scripts for the same tables—Do not run a reorganization script at the same time as another Space Manager script when both include the same objects or tables that share referential integrity.
- Avoid operations that cannot be posted—Avoid operations that cannot be posted to copy tables during a live reorganization. These operations are:
- SQL*Loader direct path load.
- SQL*Loader with the REPLACE option.
- TRUNCATE.
- Avoid certain transactions and operations—Avoid long transactions during a live reorganization. Ideally, you should commit after each transaction. Also avoid operations that generate a lot of activity. Examples of these operations include bulk loads and updates to every row in the table being reorganized.
- Avoid large batch jobs —Avoid running large batch jobs during an online switch. One way to do this is to schedule a reorganization script for a time when no large batch jobs are running.
-
Avoid canceling a reorganization—Avoid canceling a reorganization script at any point, especially the switch. Although it is possible to cancel and restart reorganization scripts, you should only stop and start the script due to script failures and system shutdowns. Do not use the Cancel and Clean up command if you intend to restart a script.
Best Practices During Interrupted Execution
- Restart most interrupted live reorganization scripts—If a live reorganization script fails, resolve the problem that caused the failure. Then restart the script using the Restart function in the Scripts/Job Monitor. See Restart for Live and Partitioning Scripts for more information. It is recommended that you restart most interrupted live reorganization scripts, especially scripts that were interrupted during a T-Lock switch. If you decide not to restart a script, contact Quest Software Technical Support for assistance in cleanup and recovery.
- Do not make changes to objects being reorganized—While a live reorganization script is waiting to be restarted, do not run DDL statement against the objects being reorganized. Do not make changes to the structure of an original table or its copy table. Also, do not make changes to indexes, constraints, or triggers, including the collection trigger. DML activity can continue against an original table if the script was interrupted prior to the switch or during an online switch. It should be avoided if the script was interrupted during a T-Lock switch.
- Do not make changes to LiveReorg objects—While a live reorganization script is waiting to be restarted, do not drop or make changes to the temporary LiveReorg objects used by QSA. Leave these objects in place. They will be used when the script is restarted.
- Troubleshoot collection-trigger failures—If an error occurs during execution of the triggers used for collecting live transactions, a live reorganization fails during posting with this error: “Aborted QSA-20391 ORA-20777 during internal SQL”. To identify the cause of the error, check the script execution log. To recover from the error, resolve the problem that caused it. Then restart the script using the Restart function in the Scripts/Job Monitor.
- Troubleshoot materialized view log errors—If a row cannot be inserted in the materialized view log used for collecting direct UPDATEs to LOBs, the following error is returned to the application that is accessing the table being reorganized: “ORA-12096: Error in materialized view log on ‘string’.’string’.” In most cases, the error occurs when the LiveReorg tablespace does not have enough space for the materialized view log to grow. In this case, you can recover by increasing the amount of free space in the LiveReorg tablespace. Transactions can then continue against the table being reorganized. QSA automatically continues the live reorganization from the point at which it was interrupted.
Best Practices for RAC/OPS Environments
This section provides best practices to follow for live reorganizations in active-active Real Application Clusters (RAC) and Oracle Parallel Server (OPS) environments:
- Instance and node—Space Manager server components and QSA should be installed on the primary instance for a cluster database. The agent should be installed for only that instance. See “Install the Agent on OPS or RAC” in the Space Manager with LiveReorg Installation Guide.
- Instance connection—When you generate a live reorganization script, the Space Manager client must be connected to the instance where QSA is installed. You can make it possible to consistently connect to the agent instance by performing connection setup. See “Connection Setup for OPS or RAC” in the Space Manager with LiveReorg Installation Guide.
- Changes to configuration—Your RAC or OPS configuration should not be changed in terms of active and inactive instances while a live reorganization is in progress. Active instances should remain active. Inactive instances should remain inactive. If your configuration is changed during a live reorganization, the reorganization script could fail. If this happens, restart the failed script after RAC or OPS configuration is complete.
- Failover—QSA does not fail over from one instance to another in an active-active environment. If the agent instance fails during a live reorganization, script execution aborts. To address this situation, bring up the instance where the agent is installed and restart script execution. If you cannot bring up the agent instance, contact Quest Software Technical Support for assistance in installing the agent on another instance. Then restart script execution using the Restart function in the Scripts/Job Monitor.
