SAP BASIS How to solve reorganization table online not finish completely and have a problem by BRSPACE tools

** Myself
How to solve reorganization table online not finish completely and have a problem by BRSPACE tools
Cause
- Connection loss during online reorganized.
- Cancel by admin,server,db.

 
How to brspace online reorganized logic
1) Determination of CREATE commands for target table and indexes based on DBMS_METADATA.GET_DDL
2) Creation of target table with naming convention <source_table>#$
3) Export of source table CBO statistics based on DBMS_STATS.EXPORT_TABLE_STATS
4) Copy of source table data to target table based on DBMS_REDEFINITION.START_REDEF_TABLE. This package will implicitly create a materialized view log MLOG$_<source_table> to track all changes that are performed on the source table while the reorganization is running.
5) Creation of target indexes with naming convention <source_index>#$
6) Import of source table CBO statistics to target table based on DBMS_STATS.IMPORT_TABLE_STATS
7) Finalizing of the online reorganization using DBMS_REDEFINITION.FINISH_REDEF_TABLE. In this step all changes in the materialized log are applied to the target table and the names of source and target table are exchanged.
7.1 The source table is dropped.
7.2 The „#$“ suffix of the target table indexes is removed.

 
Solve (base on Oracle db command)
1) Check materialized view from brspace tool (test on arfcdata transparent table)
#Verify materialized view create by brspace
SELECT owner,mview_name, refresh_mode, refresh_method,
last_refresh_type, last_refresh_date
FROM dba_mviews;
#Verify materialized log create by brspace
SELECT log_owner, master, log_table
FROM dba_mview_logs;
2) Drop materialized view.
                    #DROP MATERIALIZED VIEW [schema.] materialized_view
                    DROP MATERIALIZED VIEW sapr3.arfcdata#$
3) Drop materialized view log.
                    #DROP MATERIALIZED VIEW LOG ON [schema.]table;
                    DROP MATERIALIZED VIEW LOG ON sapr3.MLOG$_arfcdata;
 
4) Rerun reorganize online again.
#brspace -f tbreorg -t <table_name>
brspace -f tbreorg -t arfcdata

2 comments:

Anonymous said...

Good analysis of brtools and good to know, have been in this situation sometimes and this helps!

Anonymous said...

It's really help in solving the real time issue.

Loading