Procedure for a Reorganization (SAP Library - Database Administration (Oracle) with SAPDBA)

Procedure for a Reorganization

Preparation Phase (Creation of all Scripts)

All reorganizations (except the options Resize and Move/Rename) are made by writing the export dump files of the objects into one or more directories (it is also possible to define tape devices). The directories (tapes) must be large enough for the objects to be reorganized. The scripts, however, are always stored in the working directory on the hard disk. The export/import can be performed in parallel by setting the degree of parallelism exp_imp_degree >1 and the number of dump files > 1. See Parallel Export/Import.

When a reorganization is started, the system checks whether a user sapr3 is logged on to the database (e.g. whether an R/3 System is running). SAPDBA outputs a warning.

SAPDBA creates the necessary scripts for the reorganization process.

SAPDBA checks whether there is sufficient storage space in the tablespace (for example, it is possible that the tablespace cannot be created by compressing the extents of an object into one extent because the largest available freespace area is too small).

Before starting the reorganization process, SAPDBA checks whether the reorganized data will fit in the available data file(s).

Features Specific to Tablespace Reorganization with Data Files:

You can change the default values for file size and file directory. You can thus optimize your disk space utilization through appropriate distribution of the files.

You can change the number of data files, their respective sizes, and their path. This is subject to the conditions that each of the files is smaller than 2 GB and that at least one data file is created. SAPDBA checks the freespace in all relevant file systems and generates links if new directories are specified that do not conform to SAP naming conventions.

Once all checks have been successfully completed, you can decide if and when you wish to start the actual reorganization (main phase):

Directly ( Start script(s) immediately )

After scheduling it in the background ( Start script(s) in batch )

At a later time (or not) using the restart function ( End (later restart is possible) )

Main Phase (Processing of Created Scripts)

SAPDBA performs the reorganization (except the option Resize) according to the scripts created. If a user sapr3 is logged onto the database, a reorganization of a tablespace (with or without data files) is terminated when the main phase is started.

After the export phase of a reorganization, the object is deleted from the database. The physical data files are also deleted during a reorganization with data files. Since tables and indexes are removed from the database, please adhere to the Backup Concepts in Reorganizations.

In the next phase, the data is imported back into the reorganized object. The reorganization is now complete.

After changing the structure of the database (reorganization of tablespaces with data files) we recommend performing a full database backup; it is essential that you at least back up the relevant tablespace. See Backup Concept in Reorganizations.

Features Specific to Background Reorganization

If you want to run the reorganization process in the background, find out about the necessary configuration of the at command (for example, see Configuring the UNIX Command at). The jobs created with at are transparent and can therefore be displayed using operating system methods. You will find the necessary environment variables in this kind of file, as well as the command sapdba -r <complete path/timestamp> , to start the reorganization.

Note that tables and indexes which were created before the reorganization but after creation of the scripts are not taken into consideration during the reorganization, which executes at a later time in the background.
If a tablespace is reorganized without data files, it might terminate due to a lack of storage space. Such objects are lost together with their data in a tablespace reorganization including data files. You should therefore only plan to perform a reorganization at a later time if no new objects were created in between.

To avoid risks when scheduling several reorganizations one after the other at a later time, note the following:

The export dump files for the individual reorganizations must be on different disks.
In the case of a reorganization with data files, the old and new files of the tablespaces must be on different disks.

When scheduling several reorganizations one after the other, suitable time intervals should be chosen so that the individual processes do not overlap. Furthermore, you are not permitted to perform several reorganizations parallel, as this can lead to bottlenecks in the ORACLE Dictionary or an incorrect space check.

The runtime entries in the main log reorg<DBSID>.log will assist you in scheduling the reorganization (see Main Log).