Error: Failed to upgrade geodatabase when trying to alter sde_logfiles table in user schema
When running the Upgrade Geodatabase tool, the pre-requisite check finishes without any warning/error, but upgrading the geodatabase fails.
In the sde_setup.log file found in %ARCGISHOME%/etc folder, there are the following error messages:
[Fri Jun 13 14:26:10 2014]
ST_Geometry Schema Owner: (SDE) Type Release: 1007
[Fri Jun 13 14:26:10 2014] Instance initialized for ((sde)) . . .
[Fri Jun 13 14:26:12 2014] SQL Stmt: <ALTER TABLE HRSD.sde_logfiles MODIFY LOGFILE_NAME NVARCHAR2(256)>
[Fri Jun 13 14:26:12 2014] Unable to upgrade registered tables, error = -25.
[Fri Jun 13 14:26:12 2014] DBMS error code: 1031
ORA-01031: insufficient privileges
[Fri Jun 13 14:26:12 2014] SDE release upgrade not completed(-25).
[Fri Jun 13 14:26:12 2014] ERROR installing/upgrading ArcSDE, Error = -25
Verified user sde has been granted ALTER ANY TABLE privileges.
The error is when running the following SQL statement as user sde:
ALTER TABLE HRSD.sde_logfiles MODIFY LOGFILE_NAME NVARCHAR2(256);
Although sde user has been granted ALTER ANY TABLE privilege, there are entries in the HRSD.sde_logfiles table, which caused the above sql statement to fail.
Solution or Workaround
Since the sde_logfiles table is for clients accessing ArcSDE data and when upgrading the geodatabase, there should be no user other than sde to access ArcSDE, we can truncate the table and then run upgrade geodatabase tool.
- Connect as the table owner in SQL*Plus (or any Oracle client).
- Truncate the table.
HRSD@ORCL>truncate table hrsd.sde_logfiles;
- Verify the index and constraint on sde.states table, they may be altered due to the failed upgrade.
Connect as user sde:
select index_name from user_indexes where table_name = 'STATES';
If the return is states_cuk, then we need to alter them back:
connect as user sde:
alter index states_cuk rename to states_uk;
alter table states rename constraint states_cuk to states_uk;
- Run the Upgrade Geodatabase tool.
- ArcSDE 9.3 for Oracle upgrade may fail during SDE_LOGFILES table upgrade due to insufficient permissions (ORA-01031)