sdegdbrepair

The sdegdbrepair command is used to identify and repair any inconsistencies between the adds (A) and the deletes (D) tables and versioning system tables of a versioned geodatabase.

Usage syntax

sdegdbrepair -o diagnose_metadata -d <{ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}>
[-H <home_directory>] [-u <ArcSDE_admin_user>] [-p <ArcSDE_admin_password>]
[-D <database_name>] [-s <server_name>] [-i <master_geodatabase_service>:<user_schema>] [-q]

sdegdbrepair -o diagnose_tables -d <{ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}>
[-r versioned table name | file=<table_list>] [-V {ALL | NONE | <version_name>} | STATE:<state_id>]
[-H <home_directory>] [-u <ArcSDE_admin_user>] [-p <ArcSDE_admin_password>]
[-D <database_name>] [-s <server_name>] [-i <master_geodatabase_service>:<user_schema>] [-q]

sdegdbrepair -o repair_metadata -d <{ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}>
[-H <home_directory>] [-u <ArcSDE_admin_user>] [-p <ArcSDE_admin_password>]
[-D <database_name>] [-s <server_name>] [-i <master_geodatabase_service>:<user_schema>] [-N] [-q]

sdegdbrepair -o repair_tables -d <{ORACLE10G|ORACLE11G|SQLSERVER|DB2|DB2ZOS|INFORMIX|POSTGRESQL}>
[-r versioned table name | file=<table_list>] [-V {ALL | NONE | <version_name>} | STATE:<state_id>]
[-H <home_directory>] [-u <ArcSDE_admin_user>] [-p <ArcSDE_admin_password>]
[-D <database_name>] [-s <server_name>] [-i <master_geodatabase_service>:<user_schema>] [-N] [-q]

sdegdbrepair -h

sdegdbrepair -?

Operations

Operation	Description
diagnose_metadata	Shows inconsistent rows for the versioning tables
diagnose_tables	Shows the number of unreferenced (orphaned) rows in the delta (A and D) tables
repair_metadata	Repairs inconsistent rows (as possible) for the versioning tables
repair_tables	Removes and repairs orphaned rows in delta (A and D) tables

Options

Options	Description
-d	Type of database management system (DBMS); Oracle9i, Oracle 10g, Oracle 11g, SQL Server, DB2, DB2 for z/OS, Informix, or PostgreSQL
-D	Database name (not supported on Oracle)
-h or -?	Use either of these options to see the usage and options for the command. Note: If using a C shell, use -h or "-\?".
-H	ArcSDE home directory (SDEHOME) This is only needed if the SDEHOME variable isn't set or multiple services are in use.
-i	Only used to specify a geodatabase stored in the schema of a user other than SDE Specify the ArcSDE service name or port number information for the master SDE geodatabase followed by the name of the schema in which the user-schema geodatabase resides. For example: -i 5151:bpanju Note: When repairing a user-schema geodatabase using a direct connection, use the same syntax as shown in the preceding example. Even though there is no service, use 5151. You will also need to add a 5151 service in the Windows services file of the machine from which you are issuing the sdegdbrepair command.
-N	No verification is performed; the operation begins immediately after being invoked.
-o	Operation
-p	DBMS user password for ArcSDE administrator
-q	Quiet—All titles and warnings are suppressed.
-r	The name of the versioned table or list of tables to be repaired If providing a list of tables, you must specify a file that contains the list. Each table must be on a separate line in the file.
-s	ArcSDE server host name (default: localhost)
-u	DBMS user name for ArcSDE administrator; must be provided in the command
-V	Option for checking and repairing duplicate rows ALL—All registered versioned tables will be checked for duplicates for all versions. NONE—No check for duplicates Version_name—Specify a version to check and repair duplicates. STATE:<State_ID>—Specify STATE: followed by the high state_id for which the trim during the compress operation failed. For example, if the following message is returned during compress: Warning: Unable to trim state 3385->3579 for table EDITOR.INFRA2 The high state_id is 3579.

Discussion

What the repair_table and repair_metadata operations for sdegdbrepair do

When you use the repair_tables operation, the sdegdbrepair utility scans all the versioned tables and attempts to repair any inconsistencies discovered between the adds and deletes tables. This is shown in the Examples section under "Running sdegdbrepair with the repair_tables operation".

If you know a certain versioned table or a specific version is affected, you can run the sdegdbrepair command with the –r option to repair the specific versioned table or use the –V option to repair the specific version. The –V option is to control the level of checking duplicate entries in the versioned tables. The default is to check duplicates for all versions.

Repairing just one table or version takes less time than scanning all the versioned tables for inconsistencies, so if you know which table or version is affected, you can expedite the repair process. For instance, if you compress the geodatabase and it fails with a unique constraint violation, you can check the compress output to determine which version failed. Then you can run the sdegdbrepair command with the –V option to only repair that version.

Examples using the –V and –r options are shown in the sections "Running sdegdbrepair with the repair_tables operation" and "Repairing a selected versioned table".

The repair rules for metadata are as follows:

Versions pointing at a nonexistent state will be made to point to the state of its parent version if possible. If the DEFAULT version is pointing at a nonexistent state, it will be updated to point at the base state (0).
States that have an invalid parent state are not repaired, but a warning is issued.
Records containing an extra lineage_id in the STATE_LINEAGES table will be removed.
Records containing state IDs in the MVTABLES_MODIFIED table that are pointing to nonexistent states will be removed.

The repair rules for the delta tables are as follows:

Records in the Adds table that reference nonexistent states are deleted.
Records in the Deletes table that reference nonexistent states are deleted.
When multiple versions of a record are present in the same state, all but the latest record is deleted.
When the same feature in the Adds or Base table is deleted multiple times in the same state, all but the latest delete entry is removed.

Before running sdegdbrepair with the repair operations

Prior to running the repair utility, do the following:

Make a backup copy of your database.
Oracle only: If you are going to run the repair operations against your Oracle instance, you must grant the following additional privileges to the ArcSDE administrator's DBMS user in Oracle:

ALTER ANY INDEX
ALTER ANY TABLE
ANALYZE ANY
CREATE ANY INDEX
CREATE ANY PROCEDURE
CREATE ANY SEQUENCE
CREATE ANY TRIGGER
CREATE ANY VIEW
CREATE SESSION
DROP ANY INDEX
DROP ANY TABLE
DROP ANY VIEW
DROP ANY PROCEDURE
DROP ANY SEQUENCE
EXECUTE ANY PROCEDURE
SELECT ANY SEQUENCE
SELECT ANY TABLE
UNLIMITED TABLESPACE
Make sure no one is editing in the geodatabase while you run the repair. Running repair while edits are in progress could result in corruption of the feature class being edited.
Connect as the ArcSDE administrator. Be sure the privileges listed above have been granted to the ArcSDE administrator DBMS account if you are using ArcSDE for Oracle.
If you don't use the -r option to specify tables that you are sure no other user is editing, be sure there are no users connected to the database when you run the utility.
Be sure you are using the correct version of the utility; for example, the 9.1 version of the utility will not run on an ArcSDE 9 geodatabase. Similarly, the DBMS specified with the -d option must match the type of DBMS you are using; for example, you wouldn't specify -d ORACLE10G if you were running the command on a SQL Server database.
Note: Oracle only: After you finish running the sdegdbrepair utility with the repair operations, you may revoke the following privileges from the ArcSDE administrator DBMS user account:

ALTER ANY INDEX
ALTER ANY TABLE
ANALYZE ANY
CREATE ANY INDEX
CREATE ANY TRIGGER
CREATE ANY VIEW
DROP ANY INDEX
DROP ANY TABLE
DROP ANY VIEW
DROP ANY PROCEDURE
DROP ANY SEQUENCE
EXECUTE ANY PROCEDURE
SELECT ANY SEQUENCE

Examples

Determining if there are potential errors in your database

Run the sdegdbrepair command with the diagnose_tables and diagnose_metadata operations to determine if your database contains orphaned or duplicate rows in the delta tables or versioned business table. If the list operation returns results similar to the examples below, you need to run the repair operation to repair the inconsistencies. Results are returned on screen but are also written to the sde_repair.log located in the SDEHOME\etc directory. To discover if your database contains inconsistencies, first run –o diagnose_tables.

Orphaned rows

In this example, the diagnose_tables operation is being run against a local Oracle database. It discovers orphaned records in the adds table for the feature class TOPO_AREA.

sdegdbrepair -o diagnose_tables -d ORACLE10G
Enter DBA Password:

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
1 unreferenced states found in GDB.A42 (GDB.TOPO_AREA)
Diagnose Tables: 220 multiversioned tables examined,
1 multiversioned tables had orphaned or missing rows.

The output will report the cumulative total number of versioned tables affected. This indicates that you have inconsistencies and should run the repair utility.

Next, execute sdegdbrepair with the diagnose_metadata operation.

sdegdbrepair -o diagnose_metadata -d ORACLE10G
Enter DBA Password:

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
All versions reference valid states.

All states have valid parent ids.

Lineage entry for lineage 6162 has a state id of 6292, which is invalid.

Table GDB.TOPO_AREA was reported modified in non-existing state 6292

Duplicate entries in deletes tables

The diagnose_tables operation can also detect if there are duplicate entries in the deletes tables. In the following example, duplicate entries are found in the deletes table in a local Oracle database.

sdegdbrepair -o diagnose_tables -d ORACLE9I
Enter DBA password:

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
1 redundant deletes entries found in MAP.D40 (MAP.CITIES)
Diagnose Tables: 110 multiversioned tables examined,
1 multiversioned tables had orphaned, duplicate, missing, or redundant rows.

Entries missing from the deletes tables

The diagnose_tables operation also detects duplicates in the adds table that don't have corresponding rows in the deletes table. In the example that follows, duplicate rows are found in the adds table that are missing rows in the deletes tables for two feature classes in a remote SQL Server database.

sdegdbrepair -o diagnose_tables -d SQLSERVER -D porcupine -s monstro
Enter DBA password:

ESRI ArcSDE Server Repair Utility
-----------------------------------------------------------
1 duplicate rows found in PORCUPINE.POTENTIAL_SITES
1 duplicate rows found in PORCUPINE.STREETS
Diagnose Tables: 66 multiversioned tables examined
2 multiversioned tables had orphaned, duplicate, or missing rows

No inconsistencies

If no inconsistencies are detected when you run the diagnose_tables operation, as shown below, no further action is required. In the example below, the diagnose_tables operation is being run against a DB2 database.

sdegdbrepair -o diagnose_tables -d DB2 -s rocco
Enter DBA Password:

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Diagnose Tables: 180 multiversioned tables examined,
0 multiversioned tables had orphaned, duplicate, missing, or redundant rows.

Running sdegdbrepair with the repair_tables operation

To repair the inconsistencies reported by the diagnose operations, run the sdegdbrepair command with the repair_tables/metadata operations. The output for the repair is put in the sde_repair.log located in the SDEHOME\etc directory.

The following is an example of running the sdegdbrepair –o repair_tables command:

sdegdbrepair -o repair_tables -d ORACLE10G -p sde42

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Repair Instance Delta Tables, Are you sure? (Y/N): y
Repair operation completed without error.

The output in the SDEHOME/etc/sde_repair.log

[Wed Sep 06 11:14:38 2006] Mvdata Clean: Successfully cleaned GDB.TOPO_AREA
[Wed Sep 06 11:14:38 2006] Clean: 1 adds rows removed.
[Wed Sep 06 11:14:38 2006] Clean: 1 delete rows removed, 0 delete rows recreated.
[Wed Sep 06 11:14:38 2006] Mvdata Clean: Nothing to fix for GDG.CARTO_TEXT
[Wed Sep 06 11:14:38 2006] Mvdata Clean: Nothing to fix for GDB.TOPO_LINE

You can run the command with the diagnose_tables operation again to make sure all inconsistencies have been repaired, as shown in the following example:

sdegdbrepair -o diagnose_tables –d ORACLE10G -p sde42

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Diagnose Tables: 220 multiversioned tables examined,
0 multiversioned tables had orphaned or missing rows.

Note: If you run the repair_tables operation without specifying the -r option and any of the tables are locked, the repair will fail with a message similar to the one shown below:

sdegdbrepair -o repair_tables -d ORACLE10G -H C:\ArcSDE\ArcSDE\ora10gexe
Enter DBA password:

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Repair Instance Delta Tables, Are you sure? (Y/N): y
Error: Lock request conflicts with an established lock (-49).
Error: Unable to complete repairing orphaned rows.

Similarly, if you run the repair_tables operation and specify the -r option and the specified table (or tables) are locked, the repair will also fail. If, however, you run the repair_tables operation and specify the -r option and none of the tables specified are locked, the repair will complete successfully. See the section "Running sdegdbrepair with the repair_tables operation and repairing a selected versioned table".

Running sdegdbrepair with the repair_metadata operation

The following is an example of running the sdegdbrepair –o repair_metadata command against a database named develop on a named SQL Server instance:

sdegdbrepair -o repair_metadata -d SQLSERVER -D develop -s santo\fortuna
Enter DBA password:

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Repair Instance Versioning Metadata, Are you sure? (Y/N): y
Repair operation completed without error.

The output in the SDEHOME\etc\sde_repair.log would be as follows:

[Wed Sep 06 11:22:59 2006] All versions already reference valid states.
[Wed Sep 06 11:22:59 2006] All states have valid parent ids.
[Wed Sep 06 11:22:59 2006] 1 lineage entries with invalid state ids removed.
[Wed Sep 06 11:22:59 2006] 1 mvtables modified rows with invalid state ids removed.

Note: After repairing tables and metadata, run the diagnose operations again to check if any other inconsistencies exist. It does sometimes happen that when one inconsistency is fixed, it allows another one to be exposed.

Running sdegdbrepair with the repair_tables operation and repairing a selected versioned table

The following is an example of running the sdegdbrepair –o repair_tables command to repair a single table.

Execute the sdegdbrepair command with the diagnose_tables operation to determine if you need to repair your database. In this example, the versioned table is tom.fittings.

sdegdbrepair -o diagnose_tables -d INFORMIX -r tom.fittings -p sde

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
1 unreferenced states found in tom.fittings
Diagnose Tables: 28 multiversioned tables examined,
1 multiversioned tables had orphaned or missing rows.
Execute the command using the repair_tables operation and –r option, specifying the name of a versioned table you want to repair.

sdegdbrepair -o repair_tables -d INFORMIX -r tom.fittings -p sde

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Repair Instance Delta Tables, Are you sure? (Y/N): y
Repair operation completed without error.

[Wed Sep 06 15:42:09 2006] Mvdata Clean: 1 duplicates found in tom.fittings at state 6318, lineage name 6162.
[Wed Sep 06 15:42:09 2006] Mvdata Clean: Successfully cleaned tom.fittings
[Wed Sep 06 15:42:09 2006] Clean: 1 adds rows removed.
[Wed Sep 06 15:42:09 2006] Clean: 2 delete rows removed, 1 delete rows recreated.
To make sure the repair worked and there are no other inconsistencies in the database, execute the command with the diagnose_tables operation again:

sdegdbrepair -o diagnose_tables -d INFORMIX -p sde

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Diagnose Tables: 28 multiversioned tables examined,
0 multiversioned tables had orphaned or missing rows.
You can also specify a list of tables to repair using the -r option by indicating a file that contains a list of the tables to repair. In the following example, the file tbls_list.txt contains a list of tables to be repaired. Each table in the file is on a separate line, as shown below:

map.cities
map.parcels
map.hydrants
map.LAKES
map.powerlines
map.DISPATCH

To repair these tables, specify the file with the -r option.

sdegdbrepair -o repair_tables -d INFORMIX -r file=tbls_list.txt -p sde

Running sdegdbrepair with the repair_tables operation and repairing duplicates for a selected version

The following is an example of detecting and repairing duplicates for a specific version. The duplicates are typically detected with the trim operation and compress or save edits.

sdeversion -o compress -u sde -p sde

ESRI ArcSDE Server Repair Utility
------------------------------------------------------------
Compress state tree: Are you sure? (Y/N): y
Error: Underlying DBMS error (-51).
Error: Unable to compress state tree.
ORA-00001: unique constraint (GDB.A40_PK) violated

Use the –V option to specify a particular version. In the example below, the name of the selected version is GDB.V1.

sdegdbrepair -o repair_tables -d ORACLE10G -r GDB.TOPO_AREA -V GDB.V1 -p sde

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Repair Instance Delta Tables, Are you sure? (Y/N): y
Repair operation completed without error.

The output in the SDEHOME/etc/sde_repair.log is shown below.

[Wed Sep 06 15:16:29 2006] Mvdata Clean: 1 duplicates found in GDB.TOPO_AREA at state 6315, lineage name 6162.
[Wed Sep 06 15:16:29 2006] Mvdata Clean: Successfully cleaned GDB.TOPO_AREA
[Wed Sep 06 15:16:29 2006] Clean: 0 delete rows removed, 1 delete rows recreated.

Running sdegdbrepair with the repair_tables operation and repairing a specific state

Use the -V option to specify a specific state. This state is the highest state returned with the failed compress operation warning.

In the following example, the compress operation returned a warning that a trim could not be performed on certain states. The highest state_id in the warning is used in the repair_tables operation.

sdeversion -o compress -u sde -i 3666 -s bigserver

Version Administration Utility
-----------------------------------------------
Enter DBMS User Password:
Compress state tree: Are you sure? (Y/N): y

Underlying DBMS error[ORA-00001: unique constraint (SHALA.A600_PK) violated][SDE.DEFAULT]
Failed to execute (Compress).

Checking the ArcSDE error log (sde_<service_name>.log or sdedc_<DBMS>.log, depending on the type of connection made), additional information is found about the compress failure.

[Tue Oct 7 12:23:18 2008] [16301] SDE_Oracle Error: 1 ORA-00001: unique constraint (SHALA.A600_PK) violated

[Tue Oct 7 12:23:18 2008] [16301] DB_registered_table_trim_state (SHALA.VACANTLOTS,91370) Execute error (1).
Error when updating kept rows in adds table.
[Tue Oct 7 12:23:18 2008] [16301] Warning: Unable to trim state 91349->91370 for table SHALA.VACANTLOTS, error = -51
[Tue Oct 7 12:23:18 2008] [16301] DBMS error code: 1
ORA-00001: unique constraint (SHALA.A600_PK) violated."

Using the highest state_id listed, 91370, run the repair operation against that state.

sdegdbrepair -o repair_table -d ORACLE10G -V STATE:91370 -u sde -s bigserver

ESRI ArcSDE Server Repair Utility
----------------------------------------------------------
Enter DBMS User Password:
Repair Instance Delta Tables, Are you sure? (Y/N): y
Repair operation completed without error.

Home