English

How To: Restore an ArcSDE database from Oracle RAC to a non-clustered environment

Summary

Moving the ArcSDE database back from the RAC (clustered environment) to a non-clustered environment only involves resetting the 'sde.state_id_generator_nc' sequence.

Procedure

To migrate an SDE database back to a non-clustered environment from an RAC environment, update the state_id_generator_nc sequence as follows:

  1. Log in to SQL*Plus or SQL Developer as SDE.
  2. Run the code listed below to update the state_id_generator_nc:

    Code:
    BEGIN
    DECLARE
    STATE_ID_MAX NUMBER := 0;
    SEQ_VAL NUMBER := 0;
    L_DIFF NUMBER := 0;
    BEGIN
    SELECT MAX (STATE_ID) INTO STATE_ID_MAX FROM STATES;
    SELECT state_id_generator_nc.NEXTVAL INTO SEQ_VAL FROM DUAL;

    IF SEQ_VAL < STATE_ID_MAX then
    L_DIFF := STATE_ID_MAX - SEQ_VAL;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE state_id_generator_nc INCREMENT BY ' || L_DIFF;
    SELECT state_id_generator_nc.NEXTVAL INTO SEQ_VAL FROM DUAL;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE state_id_generator_nc INCREMENT BY 1' ;
    END IF;
    END;
    END;

  3. Commit the code.