Bug: arcsde_database_startup.sql execution causes post-installation to fail with ORA-12704 when start_token characterset conflicts with expected NVARCHAR2.
The arcsde_database_startup script creates a startup trigger that runs whenever the Oracle instance is started and acts as a safety net to assure that no locks or orphaned sessions persist. The trigger cleans up any orphaned session information that remains in the
ArcSDE system tables following an instance failure. This trigger is
optional because ArcSDE eventually cleans up orphaned session
information during its normal operation. The startup trigger merely
guarantees that orphaned session information is not present following
the Oracle instance startup.
When the arcsde_database_startup.sql script is executed as part of the database initialization process for ArcSDE 10 it will cause Oracle error ORA-12704: character set mismatch to be thrown, thus preventing a successful upgrade due to a failure in creation of the GDB_* tables.
A mismatch between the start_token characterset and the characterset the ArcSDE codebase expects will cause this issue to arise and the post-installation to fail.
A change in the codebase at the v10 release reads the “start_token” variable in the trigger as NVARCHAR2 where before it was just VARCHAR2. Changing the start_token definition in the trigger to NVARCHAR2 will not coirrect the issue
In order to correct this issue and move forward with the post-installation:
Drop the trigger the arcsde_database_startup.sql script creates...
drop trigger sys.arcsde_database_startup;
and run sdesetup -o install.
The following is only necessary if the logic within the script was executed outside of the trigger
If the logic within the script was executed outside of the trigger the pipes will persist within the instance until the database instance is restarted or the pipes are removed via a script such as:
-- Explicit public pipe.
l_result := DBMS_PIPE.remove_pipe(pipename => 'ARCSDE_STARTUPPIPE');
-- Explicit private pipe.
l_result := DBMS_PIPE.remove_pipe(pipename => 'ARCSDE_STARTUPCONFIRMPIPE');