Symptom
I. General information
==============
BRSPACE allows you to reorganize tables online using the Oracle Supplied Package DBMS_REDEFINITION. This method of reorganization is supported in the SAP environment as of Oracle 9.2, regardless of your SAP release.
Caution: Due to known issues with Oracle in this environment (for example, Oracle bug 2736436, which occurs during reorganization of partitioned tables), only reorganize live database online as of Oracle patchset 9.2.0.6.
The restrictions of the DBMS_REDEFINITION package apply to online reorganization. In particular, with Oracle 9.2, you cannot reorganize any tables with LONG and LONG RAW fields. BRSPACE simply skips these fields. In the SAP environment, this mainly concerns pool and cluster tables. In an Oracle 9.2 database, you must reorganize these tables offline using Export/Import (see below). As of Oracle 10g, an online conversion of the LONG fields into LOB fields is possible in connection with SAP kernel 7.00 (or higher). For SAP kernel 6.40, it is supported only in a restricted manner (see "Caution" below). You can reorganize all online tables after they have been converted.
Caution:As regards the LONG to LOB conversion, refer to the Oracle recommendation in Note 835552. Due to the field type incompatibilities caused in the ABAP Dictionary, the LONG to LOB conversion is no longer supported for systems based on the SAP Kernel 6.40 if the corrections from Note 988336 was not implemented. The LONG to LOB conversion is not supported for SAP systems with kernels below 6.40.
You can reorganize individual tables, lists of tables, all tables of one or more tablespaces or even all tables of a SAP database owner in one run. You can also use placeholders (wildcards) when selecting tables.
You can reorganize tables within the same tablespace, or you can move them to another tablespace. You can also move indexes into a separate tablespace. The new tablespaces must be locally managed tablespaces (LMTS), both for tables and indexes, and should be also autoextensible. You can also carry out reorganizations within the same tablespace with dictionary managed tablespaces.
The tables can be reorganized online in parallel using up to 15 parallel threads. To do this, set the "-p|-parallel" option of the "-f tbreorg" BRSPACE function.
Examples
---------
brspace -u / -f tbreorg -t sdbah
A table is reorganized online in its own tablespace.
brspace -u / -f tbreorg -t dbstattora,dbstatiora,dbstathora,dbstaihora
Four tables are reorganized online.
brspace -u / -f tbreorg -t "DBA*" -n PSAPSR3NEW
All tables whose names begin with "DBA" are reorganized online, and in the process are moved to a new tablespace PSAPS3NEW.
brspace -u / -f tbreorg -s psapddicd -t "*" -p 4
All tables from the PSAPDDICD tablespace (except for tables with LONG fields) are reorganized online, with 4 parallel threads. Instead of "*", you can also use the keyword "allsel" here:
brspace -u / -f tbreorg -s psapddicd -t allsel -p 4
Caution: Instead of using the BRSPACE command options, we recommend that you make the relevant entries in the input menus of BRGUI or BRTOOLS.
II. Migration to locally managed tablespaces
============================================
Migrating from dictionary managed tablespaces to locally managed tablespaces may be the main application of the online reorganization of tables. At the same time, as of SAP Basis Release 4.6C SR2, you could transfer the database from the old tablespace layout (with more than 20 tablespaces) to the new tablespace layout (with a main tablespace) (see Note 355771). Note that tables with LONG fields can only be reorganized offline using export/import. Of course, you can also use BRSPACE to store individual (large) tables on a separate tablespace.
The entire action can be carried out in the following steps:
1. Creating a new target tablespace
--------------------------------------
brspace -u / -f tscreate -t <new_tsp> -s <size> -a yes -m <max_size>
-i <incr_size> -l {all|<old_tsp>}
Make sure that the new tablespace is large enough to enter all tables. If necessary, extend the tablespace with additional files or use Auto-Extend.
brspace -u / -f tsextend -t <new_tsp> -s <size> -a yes -m <max_size>
-i <incr_size>
Caution: Do not forget to set the " -l" option (table data class), so that the new tablespaces copy *all* standard SAP data classes. If you only want to move all tables from *one* or *several* (but not all) tablespace[s] into a new tablespace, instead of "all", set the name[s] of the old tablespace[s] in the "-l" option: "-l <old_tsp_name>[,<old_tsp_name>,...]". The new tablespace thus copies the data types assigned to the old tablespace[s]. However, if you only want to move individual tables into a new tablespace, do not set this option. As a result, BRSPACE automatically generates a new data class that is assigned to the individual tables during the reorganization.
2. Online reorganization of tables without LONG fields
--------------------------------------------------
(if LONG fields exist in the tablespace to be reorganized)
brspace -u / -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> [-p <par_degree>]
You can reorganize an individual tablespace or groups of tablespaces.
3. Stop the SAP System
---------------------
(if LONG fields exist)
stopsap R3
4. Creating DDL statements for tables with LONG fields
----------------------------------------------------------
(if LONG fields exist)
brspace -u / -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> -d only_tab
brspace -u / -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> -d only_ind
brspace -u / -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> -d only_dep
The old tablespaces still only contain tables with LONG fields after a successful online reorganization.
Caution: The DDL script for the dependent objects that was generated in the last step consists, for SAP databases, entirely or almost entirely of comments.
5. Exporting data from tables with LONG fields
--------------------------------------------------
(if LONG fields exist)
brspace -u / -f tbexport -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-r yes -i no -c no -g no -e no
Indexes, constraints, grants or triggers are not exported for the tables with LONG fields. The DDL statements for these objects were already created in the previous step.
6. Deleting old tablespaces
---------------------------
brspace -u / -f tsdrop -t <tsp_name> -f
The old tablespaces, including the tables that still exist there with LONG fields, are deleted.
Caution: You should use option "-f" only if LONG fields exist.
7. Creating tables with LONG fields in the new tablespace
------------------------------------------------------------
(if LONG fields exist)
SQL> connect / as sysdba
SQL> @<SAPDATA_HOME>/sapreorg/<work_dir1>/ddl.sql
where the <work_dir1> directory in a BRSPACE run was created with the "-d only_tab" option.
8. Importing data from tables with LONG fields
--------------------------------------------------
(if LONG fields exist)
IMP utility:
brspace -u / -f tbimport -y full -r yes -i no -c no -g no -n yes
IMPDP utility:
brspace -u / -f tbimport -y full -r yes -a truncate
You can ignore the IMPDP warnings that are issued in relation to the truncate action.
All data from the tables with LONG fields is imported.
Caution: Since the IMP tool sends a commit by default after each record is entered into tables with LONG fields, the complete import may take a very long time (several days). You can accelerate the import significantly by making only one commit per table. To do this, set the "-m no" option when you call BRSPACE. However, this will only be successful if the rollback/undo tablespace is larger than the largest table to be imported.
9. Creating indexes and dependent objects
----------------------------------------
(if LONG fields exist)
SQL> connect / as sysdba
SQL> @<SAPDATA_HOME>/sapreorg/<work_dir2>/ddl.sql
SQL> @<SAPDATA_HOME>/sapreorg/<work_dir3>/ddl.sql
where the <work_dir2> directory was created with the "-d only_ind" option in the BRSPACE run and <work_dir3> was created in the run with the "-d only_dep" option.
10. Starting the SAP system
----------------------
(if LONG fields exist)
startsap R3
11. Creating new statistics for the reorganized tables
---------------------------------------------------------------
brconnect -u / -c -f stats -t <new_tsp> -f collect -p 4
The new statistics take account of the new space usage of the reorganized tables.
Caution: All reorganized tables always retain the old statistics. The tables with LONG fields that were reorganized using export/import have no statistics. For this reason, it is necessary to create new statistics (this is very urgent if you reorganized tables with LONG fields).
12. Renaming the new tablespace (as of Oracle 10g)
---------------------------------------------------
As of Oracle 10g, you can use the old names to rename the new tablespace:
brspace -u / -f tsalter -a rename -t <new_tsp> -n <old_tsp>
III. Converting the LONG fields into LOB fields online as of Oracle 10g
=================================================================
Converting the LONG fields into LOB fields (LONG -> CLOB, LONG RAW -> BLOB) is a one-off online action as of Oracle 10g. After you have done this, you can reorganize all tables online. As of Oracle 10g, online conversion in connection with SAP kernel 7.00 (or higher) is supported . For SAP kernel 6.40, it is possible only in a restricted manner (see "Caution" below). That means that conversion is supported for SAP ERP/ECC 7.00, for example, but not for SAP R/3 4.6C. This conversion is not relevant for newly-installed systems with SAP kernel 7.00 (or higher), because they no longer contain any LONG fields.
Caution:As regards the LONG to LOB conversion, refer to the Oracle recommendation in Note 835552. Due to the field type incompatibilities caused in the ABAP Dictionary, the LONG to LOB conversion is no longer supported for systems based on the SAP Kernel 6.40 if the corrections from Note 988336 was not implemented. The LONG to LOB conversion is not supported for SAP systems with kernels below 6.40.
The conversion is purely a database action that is carried out under the control of BRSPACE. No changes occur in the SAP ABAP dictionary, so the entire activity has no influence on the SAP kernel. The actual basis for the LONG to LOB conversion creates the Oracle OCI8 feature of transparent access to LOB fields using the LONG interface.
SAP recommends that you always position the new LOB segments in locally managed tablespaces (meaning the tables that previously had LONG fields).
Important: In SAP systems that are based on kernel 6.40, LONG (RAW) fields are created again during the ABAP conversion of a table. The BRSPACE LONG to LOB conversion must then be repeated.
You can carry out a conversion of the LONG fields into LOB fields using the following BRSPACE call:
brspace -u / -f tbreorg -a long2lob -t "*" [-n <new_tsp> [-i <ind_tsp>]] [-p <par_degree>]
Although all SAP tables are preselected here, only tables that have LONG or LONG RAW fields are converted. BRSPACE skips all other tables automatically. You should still specify a new locally managed target tablespace in any case, if the affected tables are still in a dictionary managed tablespace.
After the conversion, refresh the database statistics:
brconnect -u / -c -f stats -t all -f collect -p 4
IV. Tablespace reorganization with BRSPACE as of Oracle 10g
=======================================================
What is known as a "tablespace reorganization" (reorganizing all tables, while simultaneously creating the tablespace) is relatively simple as of an Oracle 10g environment. This uses the new Oracle 10g feature of renaming tablespace. The prerequisite, however, is that the affected tablespace has no tables with LONG fields, or else they must be converted beforehand into LOB fields (see section III). As a result, you must continue to use the procedure described in section II for tablespaces such as those from a SAP Basis Release 4.6C system that include LONG fields. However, this procedure can be used in higher releases as of Oracle 10g for migrating to locally managed tablespaces.
A tablespace reorganization is carried out in following steps, where:
<reorg_tsp> - is the tablespace to be reorganized
<new_tsp> - is the new tablespace that has another name temporarily
1. Creating a new tablespace
---------------------------------
The new tablespace <new_tsp> is created in such a way that the tab types (table data class) are transferred from the tablespace <reorg_tsp>:
brspace -u / -f tscreate -t <new_tsp> -d both -l <reorg_tsp>
Of course, the new tablespace must be large enough to admit all the tables from the tablespace to be reorganized (you may need to use Auto Extend).
2. Reorganizing tables into the new online tablespace
---------------------------------------------------------
brspace -u / -f tbreorg -s <reorg_tsp> -t "*" -n <new_tsp> [-p <par_degree>]
3. Deleting the old tablespace including data files
-----------------------------------------------------
brspace -u / -f tsdrop -t <reorg_tsp>
4. Renaming the new tablespace
----------------------------------
brspace -u / -f tsalter -a rename -t <new_tsp> -n <reorg_tsp>
The data files are also renamed in this process. This action requires the new tablespace to be set "offline" for a short period of time, which can disturb the running of the SAP system. Therefore, you can carry out this process asynchronously, when the system load is at its lowest.
5. Create new statistics for the reorganized tables
--------------------------------------------------------------
brconnect -u / -c -f stats -t <reorg_tsp> -f collect -p 4
Or if the new tablespace has not yet been renamed:
brconnect -u / -c -f stats -t <new_tsp> -f collect -p 4
V. Additional information
============================
Remark 1
-----------
Instead of using the BRSPACE command options, we recommend that you make the relevant entries in the input menus of BRGUI or BRTOOLS.
Remark 2
-----------
Since the reorganization is carried out by the user SYS, storage problems may sometimes occur in the SYSTEM tablespace if this is the temporary tablespace of the user SYS. If this occurs, change it to PSAPTEMP:
SQL> connect / as sysdba
SQL> alter user sys temporary tablespace psaptemp;
Remark 3
-----------
During the reorganization, Oracle creates some temporary objects, such as Materialized-View Logs (MLOG$_<table>) or indexes that support ROWID constraints (I_SNAP$_<table>#$), in the SAP user's (SAPR3 or SAP<SID>) default tablespace (PSAPUSER1D oder PSAP<SID>USR). You therefore need to make sure that there is enough free space in this tablespace. If necessary, extend the tablespace for the duration of the reorganization or set the tablespace to Auto Extend.
Remark 4
-----------
When using locally managed tablespaces (LMTS) and automatic segment space management (ASSM), you must refer to Notes 214995, 662900 and 620803.
Remark 5
-----------
During reorganization, some attributes of the tables or indexes can be changed (for example, the monitoring attribute of the tables). To do this, start the reorganization with the option "-d|-ddl first" (menu 353 - Create DDLs Statements (ddl) . [first]) and wait until BRSPACE stops, after the SQL statements have been created;
BR1115I Number of tables DDL statements were created successfully for:
At this stage, you can edit the ddl.sql file. You may not make any changes that render the table or index definitions in the Oracle dictionary incompatible with the ABAP dictionary. However, you can add additional SQL statements. You must end with the marker "#STMT". Ensure the SQL syntax of the DDL statements is correct, and also check the structuring of the ddl.sql file.
Remark 6
-----------
A reorganization with data files (as in SAPDBA) is not possible with BRSPACE directly. Tables may only be reorganized online within the same tablespace (data files) or a new online tablespace (with new data files). In the second case, the name of the tablespace in which the tables are located changes. Therefore, we recommend that you carry out reorganization into a new tablespace (for example, a locally managed tablespace with ASSM) at the same time as the transfer to the new tablespace layout. Renaming the new tablespace as the old name will be possible as of Oracle 10g. If you want to create a new tablespace in Oracle 9i, but still want to retain the old tablespace name, you must reorganize the tables twice with the following steps:
* Create a new tablespace with new names, with the option "-l|-class <old_tablespace>"
* Reorganize all tables from the old tablespace to the new tablespace
* Delete the old tablespace, and create a new one with the option "-l|-class <new_tablespace>"
* Reorganize all tables from the new tablespace into the new "old" tablespace
* Delete the new tablespace
See also Note 14 about a reorganization using the BRSPACE functions Export/Import.
Remark 7
-----------
Of course, you CANNOT reorganize the SYSTEM tablespace by using the methods described in this note. From a logical point of view, you can "reorganize" the SYSTEM tablespace by recreating the database. Note 748434 describes how this action can be performed with BRSPACE.
Remark 8
-----------
You can only use BRSPACE to reorganize tables that belong to a SAP schema (the schema name must begin with the prefix "SAP"). Tables from other schemas must be edited using Oracle's own tools. For example: In order to move the SAPUSER table (which comes from SAP, but belongs to the schema OPS$<OS_USER>) to another tablespace, use the following SQL command under the operating system user <sapsid>adm:
SQL> connect /
SQL> alter table sapuser move tablespace <tsp_name>;
You can move this table freely online, as it is only accessed by the SAP system during the setup connection for the database.
Remark 9
-----------
Reorganization can also be carried out without an operator (in background mode). To do this, call BRSPACE as follows:
brspace -u / -c force -f tbreorg -t <table_name>|<table_list>|"[<prefix>]*"
Note that you have to make a final selection of the tables on the command line.
It is more difficult - but not impossible - to execute a reorganization without an operator while simultaneously changing the table attributes. Proceed as follows:
* Start BRSPACE *without* the option "-c force":
brspace -u / -f tbreorg ...
You do not necessarily have to make a final selection of the tables here..
* Continue the activity until BRSPACE stops after the following message:
BR1115I Number of tables DDL statements were created successfully for:
* Now modify the ddl.sql file as required.
* In another command window, stop the activity with the following call:
brspace -u / -c force -f tbreorg -a suspend
* Press "c|cont" in the first window - BRSPACE stops.
Plan the following call in a scheduler (for example, cron or at) for the desired execution time to reinsert the reorganization:
brspace -u / -c force -f tbreorg -a resume
* After all tables are processed, confirm the activity once again with "c|cont" to terminate the reorganization.
Caution: In the time during which the reorganization is stopped, all tables to be reorganized are locked for structure changes (with ALTER TABLE ... DISABLE TABLE LOCK). This can disrupt the live operation of the system. The normal DML operations are allowed (such as SELECT, INSERT, UPDATE, DELETE), but actions such as attaching a new field or creating a new partition are no longer possible. In particular, this can cause problems in a BW system.
Remark 10
------------
In the case of tables that have a lot of partitions (several thousand), the following terminations can occur, which are caused by the length of the DDL statement:
BR1120E SQL statement 'CREATE TABLE' longer than 1024 KB"
BR1121E DDL buffer overflow - SQL statements longer than 5120 KB.
The internal buffers in BRSPACE allow tables with up to approximately 3,500 partitions to be reorganized, provided that they do not have more than four indexes. If you have even larger tables (in terms of the number of partitions/indexes) and the above-mentioned errors occur, set the following environment variables to avoid them:
setenv BR_MSL 10485760
setenv BR_DBL 41943040
These environment variables will increase the internal buffers in BRSPACE tenfold, so that no problems should occur with large tables. Now you can repeat the reorganization for the affected tables.
Remark 11
------------
When you have reorganized the default tablespace of the SAP user (for example, PSAPUSER1D) and have then deleted it, you should set the default tablespace of the SAP user to the new customer tablespace before starting a new reorganization, for example:
SQL> alter user SAPSR3 default tablespace PSAPSR3USR;
When you create a new tablespace, BRSPACE provides automatic support for it.
Remark 12
------------
If errors have occurred during reorganization (after some tables have already been reorganized successfully), do not try to repeat this reorganization under normal circumstances within the same BRSPACE run (for example, by going backward in menus using the Back button). This may cause unnecessary duplications and errors. Instead, terminate the BRSPACE run and start it again.
In general, you should not deviate from the proposed sequence (Cont/Back) unless you are an expert and are familiar with the exact consequences!
Remark 13
------------
There are two levels of parallel processing for the reorganization process in BRSPACE:
1. Parallel processing at BRSPACE level using several threads.
The number of parallel threads is specified in the option -p|-parallel and is limited to 15. In this case, BRSPACE dynamically distributes all tables to be reorganized between the available threads. This means that individual tables are always edited completely by one thread. This type of parallel processing is recommended if you are reorganizing a lot of small tables.
2. Parallel processing on the DBMS_REDEFINITION package level.
This is an internal Oracle parallel processing that uses the parallel query feature during the data copy process. This means that the parallel processing is carried out when reorganizing individual tables. This type of parallel processing is recommended if a few large tables are reorganized. The parallel processing level is specified here in the option -e|-degree. For more information, see the Oracle documentation.
Note the following: Both types of parallel processing can be used in combination.
Caution: We do not generally recommend setting parallel processing too high. A parallel processing level of more than four often does not greatly accelerate the process. This is possibly only useful for large installations (for example, with eight or more CPUs, a correspondingly large main memory and a high-performance I/O subsystem. Sometimes the opposite is even the case, especially as a result of conflict situations regarding resources.
Remark 14
------------
Occasionally, the hard-drive space available makes it impossible to create a suitably
large new tablespace, which means that you need to execute an online reorganization of tables and simultaneously copy them to a different tablespace. In this instance, it may be helpful to carry out an offline reorganization using the BRSPACE functions Export/Import, since it frequently consumes less additional hard drive space. This is not something that we would necessarily recommend, due to the high complexity and (long) downtime involved. This procedure is therefore best seen as a fallback solution or contingency plan.
Caution: This procedure is supported as of BRSPACE 6.40 Patch 36 and BRSPACE 7.00 Patch 11. The SAP system must be stopped during the entire procedure (as of step 1).
To carry out reorganization using the BRSPACE functions Export/Import, proceed as described below, where:
<reorg_tsp> - is the tablespace you want to reorganize (or a pair of Table-Tablespace <reorg_tsp>D and Index-Tablespace <reorg_tsp>I))
<aux_tsp> - is an auxiliary tablespace that is used only temporarily during the procedure.
1. Ensure that the tablespace to be reorganized is "self-contained" - that is, check if any references exist from the tablespace to outside the tablespace or from outside to the tablespace (cross-references):
sqlplus /nolog
SQL> connect / as sysdba
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK ('<reorg_tsp>', TRUE, TRUE);
Or, if you are reorganizing a pair of table- and index tablespaces:
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK ('<reorg_tsp>D,<reorg_tsp>I', TRUE, TRUE);
Afterwards, the following query should return no rows:
SQL> SELECT VIOLATIONS FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
You must not continue the procedure until this is the case. Otherwise, remove the cross-references. This situation can arise, for example, if the tablespace you want to reorganize contains indexes for tables from another tablespace. To move these indexes into the index tablespace that corresponds to the table tablespace for the tables to which they belong you can use the BRSPACE function "Rebuild indexes" (-f idrebuild").
The index tablespace <reog_tsp>I may not contain any tables, except when you reorganize a pair of table- and index tablespaces.
SQL> SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME = '<reorg_tsp>I';
no rows selected
2. Create the help tablespace <aux_tsp> with a size of 100 MB (auto extend), in such a way that the tab types (table data class) are transferred from the tablespace <reorg_tsp>:
brspace -u / -f tscreate -t <aux_tsp> -s 100 -a yes -m 1024 -i 10 -d both -l <reorg_tsp>
Or, if you are reorganizing a pair of table- and index tablespaces:
brspace -u / -f tscreate -t <aux_tsp> -s 100 -a yes -m 1024 -i 10 -d both -l <reorg_tsp>D
3. If the <reorg_tsp> tablespace contains any of the following tables: SDBAH,SDBAD,DBAML,DBATL,MLICHECK,TGORA,IGORA,TSORA,TAORA, IAORA,SVERS,DD02L,DD09L,DDNTT,DDART,DARTT or SAPLIKEY (SAPLIKEY is only available in NetWeaver 2004s or higher), then you should move them to the tablespace <aux_tsp> by online reorganization using BRSPACE:
brspace -u / -f tbreorg -t <table_list> -n <aux_tsp>
Where <table_list> includes only the tables from the above list that are located in the tablespace <reorg_tsp>. The individual tables in the list are separated with commas only (without spaces).
4. Export all tables from the <reorg_tsp> tablespace:
brspace -u / -f tbexport -s <reorg_tsp> -t "*" -u <dump_dir>
Or, if you are reorganizing a pair of table- and index tablespaces:
brspace -u / -f tbexport -s <reorg_tsp>D -t "*" -u <dump_dir>
Caution: You must make sufficient free disk space available in the export dump directory <dump_dir> (sapreorg is the default directory) to ensure that the tablespace data is exported successfully. If there is sufficient space in the sapreorg directory, you can omit the option "-u" - if there is insufficient space, specify a different directory. BRSPACE estimates the storage requirements for the export dump file using the statistics and checks the free disk space. Note the following: if a tablespace is fairly full, you can assume that the storage requirement is approximately half the tablespace size.
5. Delete <reorg_tsp> tablespace including data files:
brspace -u / -f tsdrop -t <reorg_tsp> -f
If you reorganize a pair of table- and index tablespaces, first delete the table tablespace (its name ends with "D", such as PSAPSTABD). When you have done this, delete the 'index' tablespace (whose name ends with "I", for example PSAPSTABI), without using the option " -f" (force).
brspace -u / -f tsdrop -t <reorg_tsp>D -f
brspace -u / -f tsdrop -t <reorg_tsp>I
After the first tablespace drop, the index tablespace should be empty. If this is not so, because the tablespace contains indexes for tables from another tablespace, move these indexes into the index tablespace corresponding to the table tablespace for their tables before the deletion. To do this, use the BRSPACE function "Rebuild indexes" ("-f idrebuild").
6. Create a new <reorg_tsp> tablespace so that the tab types (table data class) are copied from the <aux_tsp> tablespace:
brspace -u / -f tscreate -t <reorg_tsp> -l <aux_tsp>
Or, if you are reorganizing a pair of table- and index tablespaces:
brspace -u / -f tscreate -t <reorg_tsp>D -j <reorg_tsp>I -l <aux_tsp> -d table
7. Import the exported tables into the recreated <reorg_tsp> tablespace(s):
brspace -u / -f tbimport
8. If tables were moved into the <aux_tsp> tablespace, they are now moved back into the new <reorg_tsp> tablespace by means of the online reorganization with BRSPACE:
brspace -u / -f tbreorg -s <aux_tbs> -t "*" -n <reorg_tsp>
Or, if you are reorganizing a pair of table- and index tablespaces:
brspace -u / -f tbreorg -s <aux_tbs> -t "*" -n <reorg_tsp>D -i <reorg_tsp>I
9. Delete tablespace <aux_tsp> including data files:
brspace -u / -f tsdrop -t <aux_tsp>
10. Create statistics for the exported and reimported tables:
brconnect -u / -c -f stats -t <reorg_tsp> -f collect -p 4
Remark 15
------------
When you move BW tables (InfoCube and ODS tables) to a new tablespace, refer to Note 771191 and maintain the BW administrative data accordingly. This applies if a new table data class was generated when you created the new tablespace (the option " -l|-class" was not set). However, if the new tablespace copied the data classes from the old tablespace where the BW tables were (Option " -l <old_tsp>|<tsp_list>|all"), then the relevant BW administrative data has not changed.
Make the changes described in Note 771191 directly *before* the reorganization. This prevents new partitions, which are created during the reorganization, from appearing in the old tablespace.
Remark 16
------------
In certain situations, it may be useful to distribute the burden of reorganization to several BRSPACE processes. This could be the case for RAC installations, for example, if you want to use the CPU power of several RAC nodes.
The use of parallel BRSPACE processes is possible under the following circumstances:
* The parallel BRSPACE processes are started at exactly the same time.
* The quantity of tables that are processed by the individual BRSPACE processes are disjunct.
* The special option "-KST" (keep stats table) has been set in the BRSPACE command line.
Remark 17
------------
Due to a table reorganization, under certain circumstances (for example, after data archiving), the space requirement of the table may be reduced. However, the space that becomes free can be used in another way only if the INITIAL extent size of the table is not set too high. Otherwise, an unnecessarily large amount of space is allocated for the table segment, only some of which is occupied. You can determine the size of the INITIAL extent for the reorganization process using the BRSPACE option "-l|-initial <category>". You should use this option for tables with a very large INITIAL extent. For more information about this, see Note 914174 point 3.
Remark 18
------------
If tables are moved to a new tablespace during a reorganization and if new tab types (table data class) were assigned as a result, these changes may not be visible immediately in the SAP system or in the ABAP Dictionary due to table buffering. The problem can be solved by entering "/$TAB" in the OK code field. This command resets the table buffers on an application server. You can find more information in Note 26171.
Remark 19
------------
You often want or even need to reorganize tables of a certain size only. For example, this may be the case if you want to move only very large tables to a separate tablespace or want to compress large tables only. If the availability requirements permit this, then it is also makes sense to reorganize the many (thousand) smaller tables offline (that is, using ALTER TABLE MOVE), because this process is much faster than an online reorganization using the DBMS_REDEFINITION package.
The following special options of the function "tbreorg" are available for this as of BRSPACE 7.20 patch 23:
-RMB <blocks> - Reorg for a min. number of blocks
-RMB <rows> - Reorg for a min. number of rows
-RXB <blocks> - Reorg for max. number of blocks
-RXR <rows> - Reorg for max. number of rows
Tables that do not meet the condition set in one of the special options are simply skipped during the reorganization process.
During this, <blocks> refers to the value of BLOCKS (High Water Mark) and <rows> refers to the value of NUM_ROWS (latest statistic) in DBA_TABLES.
Important: These special options can also be implemented for the BRSPACE function "idrebuild". I this case, <blocks> refers to the value of LEAF_BLOCKS and <rows> refers to the value of NUM_ROWS in DBA_INDEXES.
==============
BRSPACE allows you to reorganize tables online using the Oracle Supplied Package DBMS_REDEFINITION. This method of reorganization is supported in the SAP environment as of Oracle 9.2, regardless of your SAP release.
Caution: Due to known issues with Oracle in this environment (for example, Oracle bug 2736436, which occurs during reorganization of partitioned tables), only reorganize live database online as of Oracle patchset 9.2.0.6.
The restrictions of the DBMS_REDEFINITION package apply to online reorganization. In particular, with Oracle 9.2, you cannot reorganize any tables with LONG and LONG RAW fields. BRSPACE simply skips these fields. In the SAP environment, this mainly concerns pool and cluster tables. In an Oracle 9.2 database, you must reorganize these tables offline using Export/Import (see below). As of Oracle 10g, an online conversion of the LONG fields into LOB fields is possible in connection with SAP kernel 7.00 (or higher). For SAP kernel 6.40, it is supported only in a restricted manner (see "Caution" below). You can reorganize all online tables after they have been converted.
Caution:As regards the LONG to LOB conversion, refer to the Oracle recommendation in Note 835552. Due to the field type incompatibilities caused in the ABAP Dictionary, the LONG to LOB conversion is no longer supported for systems based on the SAP Kernel 6.40 if the corrections from Note 988336 was not implemented. The LONG to LOB conversion is not supported for SAP systems with kernels below 6.40.
You can reorganize individual tables, lists of tables, all tables of one or more tablespaces or even all tables of a SAP database owner in one run. You can also use placeholders (wildcards) when selecting tables.
You can reorganize tables within the same tablespace, or you can move them to another tablespace. You can also move indexes into a separate tablespace. The new tablespaces must be locally managed tablespaces (LMTS), both for tables and indexes, and should be also autoextensible. You can also carry out reorganizations within the same tablespace with dictionary managed tablespaces.
The tables can be reorganized online in parallel using up to 15 parallel threads. To do this, set the "-p|-parallel" option of the "-f tbreorg" BRSPACE function.
Examples
---------
brspace -u / -f tbreorg -t sdbah
A table is reorganized online in its own tablespace.
brspace -u / -f tbreorg -t dbstattora,dbstatiora,dbstathora,dbstaihora
Four tables are reorganized online.
brspace -u / -f tbreorg -t "DBA*" -n PSAPSR3NEW
All tables whose names begin with "DBA" are reorganized online, and in the process are moved to a new tablespace PSAPS3NEW.
brspace -u / -f tbreorg -s psapddicd -t "*" -p 4
All tables from the PSAPDDICD tablespace (except for tables with LONG fields) are reorganized online, with 4 parallel threads. Instead of "*", you can also use the keyword "allsel" here:
brspace -u / -f tbreorg -s psapddicd -t allsel -p 4
Caution: Instead of using the BRSPACE command options, we recommend that you make the relevant entries in the input menus of BRGUI or BRTOOLS.
II. Migration to locally managed tablespaces
============================================
Migrating from dictionary managed tablespaces to locally managed tablespaces may be the main application of the online reorganization of tables. At the same time, as of SAP Basis Release 4.6C SR2, you could transfer the database from the old tablespace layout (with more than 20 tablespaces) to the new tablespace layout (with a main tablespace) (see Note 355771). Note that tables with LONG fields can only be reorganized offline using export/import. Of course, you can also use BRSPACE to store individual (large) tables on a separate tablespace.
The entire action can be carried out in the following steps:
1. Creating a new target tablespace
--------------------------------------
brspace -u / -f tscreate -t <new_tsp> -s <size> -a yes -m <max_size>
-i <incr_size> -l {all|<old_tsp>}
Make sure that the new tablespace is large enough to enter all tables. If necessary, extend the tablespace with additional files or use Auto-Extend.
brspace -u / -f tsextend -t <new_tsp> -s <size> -a yes -m <max_size>
-i <incr_size>
Caution: Do not forget to set the " -l" option (table data class), so that the new tablespaces copy *all* standard SAP data classes. If you only want to move all tables from *one* or *several* (but not all) tablespace[s] into a new tablespace, instead of "all", set the name[s] of the old tablespace[s] in the "-l" option: "-l <old_tsp_name>[,<old_tsp_name>,...]". The new tablespace thus copies the data types assigned to the old tablespace[s]. However, if you only want to move individual tables into a new tablespace, do not set this option. As a result, BRSPACE automatically generates a new data class that is assigned to the individual tables during the reorganization.
2. Online reorganization of tables without LONG fields
--------------------------------------------------
(if LONG fields exist in the tablespace to be reorganized)
brspace -u / -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> [-p <par_degree>]
You can reorganize an individual tablespace or groups of tablespaces.
3. Stop the SAP System
---------------------
(if LONG fields exist)
stopsap R3
4. Creating DDL statements for tables with LONG fields
----------------------------------------------------------
(if LONG fields exist)
brspace -u / -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> -d only_tab
brspace -u / -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> -d only_ind
brspace -u / -f tbreorg -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-n <new_tsp> -d only_dep
The old tablespaces still only contain tables with LONG fields after a successful online reorganization.
Caution: The DDL script for the dependent objects that was generated in the last step consists, for SAP databases, entirely or almost entirely of comments.
5. Exporting data from tables with LONG fields
--------------------------------------------------
(if LONG fields exist)
brspace -u / -f tbexport -s <tsp_name1>[,<tsp_name2>[,...]] -t "*"
-r yes -i no -c no -g no -e no
Indexes, constraints, grants or triggers are not exported for the tables with LONG fields. The DDL statements for these objects were already created in the previous step.
6. Deleting old tablespaces
---------------------------
brspace -u / -f tsdrop -t <tsp_name> -f
The old tablespaces, including the tables that still exist there with LONG fields, are deleted.
Caution: You should use option "-f" only if LONG fields exist.
7. Creating tables with LONG fields in the new tablespace
------------------------------------------------------------
(if LONG fields exist)
SQL> connect / as sysdba
SQL> @<SAPDATA_HOME>/sapreorg/<work_dir1>/ddl.sql
where the <work_dir1> directory in a BRSPACE run was created with the "-d only_tab" option.
8. Importing data from tables with LONG fields
--------------------------------------------------
(if LONG fields exist)
IMP utility:
brspace -u / -f tbimport -y full -r yes -i no -c no -g no -n yes
IMPDP utility:
brspace -u / -f tbimport -y full -r yes -a truncate
You can ignore the IMPDP warnings that are issued in relation to the truncate action.
All data from the tables with LONG fields is imported.
Caution: Since the IMP tool sends a commit by default after each record is entered into tables with LONG fields, the complete import may take a very long time (several days). You can accelerate the import significantly by making only one commit per table. To do this, set the "-m no" option when you call BRSPACE. However, this will only be successful if the rollback/undo tablespace is larger than the largest table to be imported.
9. Creating indexes and dependent objects
----------------------------------------
(if LONG fields exist)
SQL> connect / as sysdba
SQL> @<SAPDATA_HOME>/sapreorg/<work_dir2>/ddl.sql
SQL> @<SAPDATA_HOME>/sapreorg/<work_dir3>/ddl.sql
where the <work_dir2> directory was created with the "-d only_ind" option in the BRSPACE run and <work_dir3> was created in the run with the "-d only_dep" option.
10. Starting the SAP system
----------------------
(if LONG fields exist)
startsap R3
11. Creating new statistics for the reorganized tables
---------------------------------------------------------------
brconnect -u / -c -f stats -t <new_tsp> -f collect -p 4
The new statistics take account of the new space usage of the reorganized tables.
Caution: All reorganized tables always retain the old statistics. The tables with LONG fields that were reorganized using export/import have no statistics. For this reason, it is necessary to create new statistics (this is very urgent if you reorganized tables with LONG fields).
12. Renaming the new tablespace (as of Oracle 10g)
---------------------------------------------------
As of Oracle 10g, you can use the old names to rename the new tablespace:
brspace -u / -f tsalter -a rename -t <new_tsp> -n <old_tsp>
III. Converting the LONG fields into LOB fields online as of Oracle 10g
=================================================================
Converting the LONG fields into LOB fields (LONG -> CLOB, LONG RAW -> BLOB) is a one-off online action as of Oracle 10g. After you have done this, you can reorganize all tables online. As of Oracle 10g, online conversion in connection with SAP kernel 7.00 (or higher) is supported . For SAP kernel 6.40, it is possible only in a restricted manner (see "Caution" below). That means that conversion is supported for SAP ERP/ECC 7.00, for example, but not for SAP R/3 4.6C. This conversion is not relevant for newly-installed systems with SAP kernel 7.00 (or higher), because they no longer contain any LONG fields.
Caution:As regards the LONG to LOB conversion, refer to the Oracle recommendation in Note 835552. Due to the field type incompatibilities caused in the ABAP Dictionary, the LONG to LOB conversion is no longer supported for systems based on the SAP Kernel 6.40 if the corrections from Note 988336 was not implemented. The LONG to LOB conversion is not supported for SAP systems with kernels below 6.40.
The conversion is purely a database action that is carried out under the control of BRSPACE. No changes occur in the SAP ABAP dictionary, so the entire activity has no influence on the SAP kernel. The actual basis for the LONG to LOB conversion creates the Oracle OCI8 feature of transparent access to LOB fields using the LONG interface.
SAP recommends that you always position the new LOB segments in locally managed tablespaces (meaning the tables that previously had LONG fields).
Important: In SAP systems that are based on kernel 6.40, LONG (RAW) fields are created again during the ABAP conversion of a table. The BRSPACE LONG to LOB conversion must then be repeated.
You can carry out a conversion of the LONG fields into LOB fields using the following BRSPACE call:
brspace -u / -f tbreorg -a long2lob -t "*" [-n <new_tsp> [-i <ind_tsp>]] [-p <par_degree>]
Although all SAP tables are preselected here, only tables that have LONG or LONG RAW fields are converted. BRSPACE skips all other tables automatically. You should still specify a new locally managed target tablespace in any case, if the affected tables are still in a dictionary managed tablespace.
After the conversion, refresh the database statistics:
brconnect -u / -c -f stats -t all -f collect -p 4
IV. Tablespace reorganization with BRSPACE as of Oracle 10g
=======================================================
What is known as a "tablespace reorganization" (reorganizing all tables, while simultaneously creating the tablespace) is relatively simple as of an Oracle 10g environment. This uses the new Oracle 10g feature of renaming tablespace. The prerequisite, however, is that the affected tablespace has no tables with LONG fields, or else they must be converted beforehand into LOB fields (see section III). As a result, you must continue to use the procedure described in section II for tablespaces such as those from a SAP Basis Release 4.6C system that include LONG fields. However, this procedure can be used in higher releases as of Oracle 10g for migrating to locally managed tablespaces.
A tablespace reorganization is carried out in following steps, where:
<reorg_tsp> - is the tablespace to be reorganized
<new_tsp> - is the new tablespace that has another name temporarily
1. Creating a new tablespace
---------------------------------
The new tablespace <new_tsp> is created in such a way that the tab types (table data class) are transferred from the tablespace <reorg_tsp>:
brspace -u / -f tscreate -t <new_tsp> -d both -l <reorg_tsp>
Of course, the new tablespace must be large enough to admit all the tables from the tablespace to be reorganized (you may need to use Auto Extend).
2. Reorganizing tables into the new online tablespace
---------------------------------------------------------
brspace -u / -f tbreorg -s <reorg_tsp> -t "*" -n <new_tsp> [-p <par_degree>]
3. Deleting the old tablespace including data files
-----------------------------------------------------
brspace -u / -f tsdrop -t <reorg_tsp>
4. Renaming the new tablespace
----------------------------------
brspace -u / -f tsalter -a rename -t <new_tsp> -n <reorg_tsp>
The data files are also renamed in this process. This action requires the new tablespace to be set "offline" for a short period of time, which can disturb the running of the SAP system. Therefore, you can carry out this process asynchronously, when the system load is at its lowest.
5. Create new statistics for the reorganized tables
--------------------------------------------------------------
brconnect -u / -c -f stats -t <reorg_tsp> -f collect -p 4
Or if the new tablespace has not yet been renamed:
brconnect -u / -c -f stats -t <new_tsp> -f collect -p 4
V. Additional information
============================
Remark 1
-----------
Instead of using the BRSPACE command options, we recommend that you make the relevant entries in the input menus of BRGUI or BRTOOLS.
Remark 2
-----------
Since the reorganization is carried out by the user SYS, storage problems may sometimes occur in the SYSTEM tablespace if this is the temporary tablespace of the user SYS. If this occurs, change it to PSAPTEMP:
SQL> connect / as sysdba
SQL> alter user sys temporary tablespace psaptemp;
Remark 3
-----------
During the reorganization, Oracle creates some temporary objects, such as Materialized-View Logs (MLOG$_<table>) or indexes that support ROWID constraints (I_SNAP$_<table>#$), in the SAP user's (SAPR3 or SAP<SID>) default tablespace (PSAPUSER1D oder PSAP<SID>USR). You therefore need to make sure that there is enough free space in this tablespace. If necessary, extend the tablespace for the duration of the reorganization or set the tablespace to Auto Extend.
Remark 4
-----------
When using locally managed tablespaces (LMTS) and automatic segment space management (ASSM), you must refer to Notes 214995, 662900 and 620803.
Remark 5
-----------
During reorganization, some attributes of the tables or indexes can be changed (for example, the monitoring attribute of the tables). To do this, start the reorganization with the option "-d|-ddl first" (menu 353 - Create DDLs Statements (ddl) . [first]) and wait until BRSPACE stops, after the SQL statements have been created;
BR1115I Number of tables DDL statements were created successfully for:
At this stage, you can edit the ddl.sql file. You may not make any changes that render the table or index definitions in the Oracle dictionary incompatible with the ABAP dictionary. However, you can add additional SQL statements. You must end with the marker "#STMT". Ensure the SQL syntax of the DDL statements is correct, and also check the structuring of the ddl.sql file.
Remark 6
-----------
A reorganization with data files (as in SAPDBA) is not possible with BRSPACE directly. Tables may only be reorganized online within the same tablespace (data files) or a new online tablespace (with new data files). In the second case, the name of the tablespace in which the tables are located changes. Therefore, we recommend that you carry out reorganization into a new tablespace (for example, a locally managed tablespace with ASSM) at the same time as the transfer to the new tablespace layout. Renaming the new tablespace as the old name will be possible as of Oracle 10g. If you want to create a new tablespace in Oracle 9i, but still want to retain the old tablespace name, you must reorganize the tables twice with the following steps:
* Create a new tablespace with new names, with the option "-l|-class <old_tablespace>"
* Reorganize all tables from the old tablespace to the new tablespace
* Delete the old tablespace, and create a new one with the option "-l|-class <new_tablespace>"
* Reorganize all tables from the new tablespace into the new "old" tablespace
* Delete the new tablespace
See also Note 14 about a reorganization using the BRSPACE functions Export/Import.
Remark 7
-----------
Of course, you CANNOT reorganize the SYSTEM tablespace by using the methods described in this note. From a logical point of view, you can "reorganize" the SYSTEM tablespace by recreating the database. Note 748434 describes how this action can be performed with BRSPACE.
Remark 8
-----------
You can only use BRSPACE to reorganize tables that belong to a SAP schema (the schema name must begin with the prefix "SAP"). Tables from other schemas must be edited using Oracle's own tools. For example: In order to move the SAPUSER table (which comes from SAP, but belongs to the schema OPS$<OS_USER>) to another tablespace, use the following SQL command under the operating system user <sapsid>adm:
SQL> connect /
SQL> alter table sapuser move tablespace <tsp_name>;
You can move this table freely online, as it is only accessed by the SAP system during the setup connection for the database.
Remark 9
-----------
Reorganization can also be carried out without an operator (in background mode). To do this, call BRSPACE as follows:
brspace -u / -c force -f tbreorg -t <table_name>|<table_list>|"[<prefix>]*"
Note that you have to make a final selection of the tables on the command line.
It is more difficult - but not impossible - to execute a reorganization without an operator while simultaneously changing the table attributes. Proceed as follows:
* Start BRSPACE *without* the option "-c force":
brspace -u / -f tbreorg ...
You do not necessarily have to make a final selection of the tables here..
* Continue the activity until BRSPACE stops after the following message:
BR1115I Number of tables DDL statements were created successfully for:
* Now modify the ddl.sql file as required.
* In another command window, stop the activity with the following call:
brspace -u / -c force -f tbreorg -a suspend
* Press "c|cont" in the first window - BRSPACE stops.
Plan the following call in a scheduler (for example, cron or at) for the desired execution time to reinsert the reorganization:
brspace -u / -c force -f tbreorg -a resume
* After all tables are processed, confirm the activity once again with "c|cont" to terminate the reorganization.
Caution: In the time during which the reorganization is stopped, all tables to be reorganized are locked for structure changes (with ALTER TABLE ... DISABLE TABLE LOCK). This can disrupt the live operation of the system. The normal DML operations are allowed (such as SELECT, INSERT, UPDATE, DELETE), but actions such as attaching a new field or creating a new partition are no longer possible. In particular, this can cause problems in a BW system.
Remark 10
------------
In the case of tables that have a lot of partitions (several thousand), the following terminations can occur, which are caused by the length of the DDL statement:
BR1120E SQL statement 'CREATE TABLE' longer than 1024 KB"
BR1121E DDL buffer overflow - SQL statements longer than 5120 KB.
The internal buffers in BRSPACE allow tables with up to approximately 3,500 partitions to be reorganized, provided that they do not have more than four indexes. If you have even larger tables (in terms of the number of partitions/indexes) and the above-mentioned errors occur, set the following environment variables to avoid them:
setenv BR_MSL 10485760
setenv BR_DBL 41943040
These environment variables will increase the internal buffers in BRSPACE tenfold, so that no problems should occur with large tables. Now you can repeat the reorganization for the affected tables.
Remark 11
------------
When you have reorganized the default tablespace of the SAP user (for example, PSAPUSER1D) and have then deleted it, you should set the default tablespace of the SAP user to the new customer tablespace before starting a new reorganization, for example:
SQL> alter user SAPSR3 default tablespace PSAPSR3USR;
When you create a new tablespace, BRSPACE provides automatic support for it.
Remark 12
------------
If errors have occurred during reorganization (after some tables have already been reorganized successfully), do not try to repeat this reorganization under normal circumstances within the same BRSPACE run (for example, by going backward in menus using the Back button). This may cause unnecessary duplications and errors. Instead, terminate the BRSPACE run and start it again.
In general, you should not deviate from the proposed sequence (Cont/Back) unless you are an expert and are familiar with the exact consequences!
Remark 13
------------
There are two levels of parallel processing for the reorganization process in BRSPACE:
1. Parallel processing at BRSPACE level using several threads.
The number of parallel threads is specified in the option -p|-parallel and is limited to 15. In this case, BRSPACE dynamically distributes all tables to be reorganized between the available threads. This means that individual tables are always edited completely by one thread. This type of parallel processing is recommended if you are reorganizing a lot of small tables.
2. Parallel processing on the DBMS_REDEFINITION package level.
This is an internal Oracle parallel processing that uses the parallel query feature during the data copy process. This means that the parallel processing is carried out when reorganizing individual tables. This type of parallel processing is recommended if a few large tables are reorganized. The parallel processing level is specified here in the option -e|-degree. For more information, see the Oracle documentation.
Note the following: Both types of parallel processing can be used in combination.
Caution: We do not generally recommend setting parallel processing too high. A parallel processing level of more than four often does not greatly accelerate the process. This is possibly only useful for large installations (for example, with eight or more CPUs, a correspondingly large main memory and a high-performance I/O subsystem. Sometimes the opposite is even the case, especially as a result of conflict situations regarding resources.
Remark 14
------------
Occasionally, the hard-drive space available makes it impossible to create a suitably
large new tablespace, which means that you need to execute an online reorganization of tables and simultaneously copy them to a different tablespace. In this instance, it may be helpful to carry out an offline reorganization using the BRSPACE functions Export/Import, since it frequently consumes less additional hard drive space. This is not something that we would necessarily recommend, due to the high complexity and (long) downtime involved. This procedure is therefore best seen as a fallback solution or contingency plan.
Caution: This procedure is supported as of BRSPACE 6.40 Patch 36 and BRSPACE 7.00 Patch 11. The SAP system must be stopped during the entire procedure (as of step 1).
To carry out reorganization using the BRSPACE functions Export/Import, proceed as described below, where:
<reorg_tsp> - is the tablespace you want to reorganize (or a pair of Table-Tablespace <reorg_tsp>D and Index-Tablespace <reorg_tsp>I))
<aux_tsp> - is an auxiliary tablespace that is used only temporarily during the procedure.
1. Ensure that the tablespace to be reorganized is "self-contained" - that is, check if any references exist from the tablespace to outside the tablespace or from outside to the tablespace (cross-references):
sqlplus /nolog
SQL> connect / as sysdba
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK ('<reorg_tsp>', TRUE, TRUE);
Or, if you are reorganizing a pair of table- and index tablespaces:
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK ('<reorg_tsp>D,<reorg_tsp>I', TRUE, TRUE);
Afterwards, the following query should return no rows:
SQL> SELECT VIOLATIONS FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
You must not continue the procedure until this is the case. Otherwise, remove the cross-references. This situation can arise, for example, if the tablespace you want to reorganize contains indexes for tables from another tablespace. To move these indexes into the index tablespace that corresponds to the table tablespace for the tables to which they belong you can use the BRSPACE function "Rebuild indexes" (-f idrebuild").
The index tablespace <reog_tsp>I may not contain any tables, except when you reorganize a pair of table- and index tablespaces.
SQL> SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME = '<reorg_tsp>I';
no rows selected
2. Create the help tablespace <aux_tsp> with a size of 100 MB (auto extend), in such a way that the tab types (table data class) are transferred from the tablespace <reorg_tsp>:
brspace -u / -f tscreate -t <aux_tsp> -s 100 -a yes -m 1024 -i 10 -d both -l <reorg_tsp>
Or, if you are reorganizing a pair of table- and index tablespaces:
brspace -u / -f tscreate -t <aux_tsp> -s 100 -a yes -m 1024 -i 10 -d both -l <reorg_tsp>D
3. If the <reorg_tsp> tablespace contains any of the following tables: SDBAH,SDBAD,DBAML,DBATL,MLICHECK,TGORA,IGORA,TSORA,TAORA, IAORA,SVERS,DD02L,DD09L,DDNTT,DDART,DARTT or SAPLIKEY (SAPLIKEY is only available in NetWeaver 2004s or higher), then you should move them to the tablespace <aux_tsp> by online reorganization using BRSPACE:
brspace -u / -f tbreorg -t <table_list> -n <aux_tsp>
Where <table_list> includes only the tables from the above list that are located in the tablespace <reorg_tsp>. The individual tables in the list are separated with commas only (without spaces).
4. Export all tables from the <reorg_tsp> tablespace:
brspace -u / -f tbexport -s <reorg_tsp> -t "*" -u <dump_dir>
Or, if you are reorganizing a pair of table- and index tablespaces:
brspace -u / -f tbexport -s <reorg_tsp>D -t "*" -u <dump_dir>
Caution: You must make sufficient free disk space available in the export dump directory <dump_dir> (sapreorg is the default directory) to ensure that the tablespace data is exported successfully. If there is sufficient space in the sapreorg directory, you can omit the option "-u" - if there is insufficient space, specify a different directory. BRSPACE estimates the storage requirements for the export dump file using the statistics and checks the free disk space. Note the following: if a tablespace is fairly full, you can assume that the storage requirement is approximately half the tablespace size.
5. Delete <reorg_tsp> tablespace including data files:
brspace -u / -f tsdrop -t <reorg_tsp> -f
If you reorganize a pair of table- and index tablespaces, first delete the table tablespace (its name ends with "D", such as PSAPSTABD). When you have done this, delete the 'index' tablespace (whose name ends with "I", for example PSAPSTABI), without using the option " -f" (force).
brspace -u / -f tsdrop -t <reorg_tsp>D -f
brspace -u / -f tsdrop -t <reorg_tsp>I
After the first tablespace drop, the index tablespace should be empty. If this is not so, because the tablespace contains indexes for tables from another tablespace, move these indexes into the index tablespace corresponding to the table tablespace for their tables before the deletion. To do this, use the BRSPACE function "Rebuild indexes" ("-f idrebuild").
6. Create a new <reorg_tsp> tablespace so that the tab types (table data class) are copied from the <aux_tsp> tablespace:
brspace -u / -f tscreate -t <reorg_tsp> -l <aux_tsp>
Or, if you are reorganizing a pair of table- and index tablespaces:
brspace -u / -f tscreate -t <reorg_tsp>D -j <reorg_tsp>I -l <aux_tsp> -d table
7. Import the exported tables into the recreated <reorg_tsp> tablespace(s):
brspace -u / -f tbimport
8. If tables were moved into the <aux_tsp> tablespace, they are now moved back into the new <reorg_tsp> tablespace by means of the online reorganization with BRSPACE:
brspace -u / -f tbreorg -s <aux_tbs> -t "*" -n <reorg_tsp>
Or, if you are reorganizing a pair of table- and index tablespaces:
brspace -u / -f tbreorg -s <aux_tbs> -t "*" -n <reorg_tsp>D -i <reorg_tsp>I
9. Delete tablespace <aux_tsp> including data files:
brspace -u / -f tsdrop -t <aux_tsp>
10. Create statistics for the exported and reimported tables:
brconnect -u / -c -f stats -t <reorg_tsp> -f collect -p 4
Remark 15
------------
When you move BW tables (InfoCube and ODS tables) to a new tablespace, refer to Note 771191 and maintain the BW administrative data accordingly. This applies if a new table data class was generated when you created the new tablespace (the option " -l|-class" was not set). However, if the new tablespace copied the data classes from the old tablespace where the BW tables were (Option " -l <old_tsp>|<tsp_list>|all"), then the relevant BW administrative data has not changed.
Make the changes described in Note 771191 directly *before* the reorganization. This prevents new partitions, which are created during the reorganization, from appearing in the old tablespace.
Remark 16
------------
In certain situations, it may be useful to distribute the burden of reorganization to several BRSPACE processes. This could be the case for RAC installations, for example, if you want to use the CPU power of several RAC nodes.
The use of parallel BRSPACE processes is possible under the following circumstances:
* The parallel BRSPACE processes are started at exactly the same time.
* The quantity of tables that are processed by the individual BRSPACE processes are disjunct.
* The special option "-KST" (keep stats table) has been set in the BRSPACE command line.
Remark 17
------------
Due to a table reorganization, under certain circumstances (for example, after data archiving), the space requirement of the table may be reduced. However, the space that becomes free can be used in another way only if the INITIAL extent size of the table is not set too high. Otherwise, an unnecessarily large amount of space is allocated for the table segment, only some of which is occupied. You can determine the size of the INITIAL extent for the reorganization process using the BRSPACE option "-l|-initial <category>". You should use this option for tables with a very large INITIAL extent. For more information about this, see Note 914174 point 3.
Remark 18
------------
If tables are moved to a new tablespace during a reorganization and if new tab types (table data class) were assigned as a result, these changes may not be visible immediately in the SAP system or in the ABAP Dictionary due to table buffering. The problem can be solved by entering "/$TAB" in the OK code field. This command resets the table buffers on an application server. You can find more information in Note 26171.
Remark 19
------------
You often want or even need to reorganize tables of a certain size only. For example, this may be the case if you want to move only very large tables to a separate tablespace or want to compress large tables only. If the availability requirements permit this, then it is also makes sense to reorganize the many (thousand) smaller tables offline (that is, using ALTER TABLE MOVE), because this process is much faster than an online reorganization using the DBMS_REDEFINITION package.
The following special options of the function "tbreorg" are available for this as of BRSPACE 7.20 patch 23:
-RMB <blocks> - Reorg for a min. number of blocks
-RMB <rows> - Reorg for a min. number of rows
-RXB <blocks> - Reorg for max. number of blocks
-RXR <rows> - Reorg for max. number of rows
Tables that do not meet the condition set in one of the special options are simply skipped during the reorganization process.
During this, <blocks> refers to the value of BLOCKS (High Water Mark) and <rows> refers to the value of NUM_ROWS (latest statistic) in DBA_TABLES.
Important: These special options can also be implemented for the BRSPACE function "idrebuild". I this case, <blocks> refers to the value of LEAF_BLOCKS and <rows> refers to the value of NUM_ROWS in DBA_INDEXES.
Other Terms
BR*Tools, BRSPACE
Reason and Prerequisites
The prerequisite for online reorganization is:
Oracle 9.2 database or higher.
The prerequisite for online conversion of the LONG-fields into LOB-fields:
Oracle 10g database or higher.
Online conversion is supported for SAP kernel 7.00 or higher. For SAP kernel 6.40, it is supported only in a restricted manner (see "Caution" in point III). Conversion is not supported for SAP systems with a kernel lower than 6.40.
Oracle 9.2 database or higher.
The prerequisite for online conversion of the LONG-fields into LOB-fields:
Oracle 10g database or higher.
Online conversion is supported for SAP kernel 7.00 or higher. For SAP kernel 6.40, it is supported only in a restricted manner (see "Caution" in point III). Conversion is not supported for SAP systems with a kernel lower than 6.40.
Solution
Download the most recent patch for BR*Tools 6.40 or 7.00 from the SAP
Service Marketplace. The precise procedure is described in Notes 12741
and 19466.
Header Data
| Released On | 26.09.2013 09:03:55 |
| Release Status | Released for Customer |
| Component | BC-DB-ORA-DBA Database Administration with Oracle |
| Priority | Recommendations / Additional Info |
| Category | Consulting |
Validity
This document is not restricted to a software component or software component version
References
This document refers to:
SAP Notes
This document is referenced by:
SAP Notes (18)
No comments:
Post a Comment