Restore an Enterprise database from Oracle RAC to a non-clustered environment

Summary

Moving the Enterprise 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 Enterprise 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.