Space Manager with LiveReorg 9.0 - User Guide

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.

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.

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.

Concepts and Terms

This section describes concepts and terms that apply to live reorganizations.

See also, How Live Reorganizations Work.

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.