English

How To: Preserve Oracle pipes, such as ArcSDE row_id values, when shutting down an Oracle instance

Summary

Instructions provided describe a solution to preserve pipe values prior to shutting down an Oracle instance and the ability to recreate the pipes and place the values back onto the according pipe when the Oracle instance is started.

Users who frequently edit their geodatabase feature classes and routinely shut down their Oracle instance benefit by this solution.

Background

ArcSDE uses Oracle pipes as a mechanism to cache unused row_id values when a client, or gsrvr, process exits.

For example, when using ArcSDE maintained row_id columns, such as an OBJECTID column in the geodatabase, row_id values are generated by a sequence for each table. The sequences are named R<#>, where the value of '#' is the table's registration_id found in the sde.table_registry table.

Each time the sequence is referenced, the sequence is incremented by 16; these 16 row_id values are consumed by the gsrvr client. If the gsrvr client does not consume all 16 values, the remaining values are returned to the server and placed in an Oracle pipe.
 
An Oracle pipe is a memory structure in the shared pool which allows sessions to place messages onto a public or private memory structure which other process may then consume. ArcSDE names the pipes ARCSDE_IDPIPE<#>, where the value of # is the table's registration_id.

To see what pipes are present in the Oracle instance, execute the following query as the SYS or SYSTEM user:
SELECT name FROM v$db_pipes;

When an Oracle instance is shutdown, Oracle explicitly destroys each pipe; unused row_id values in the pipe are lost.

Procedure

To implement this solution, connection as both the SDE user and SYS user is required.

  1. Execute the following statements as the SYS user:
    GRANT SELECT ON v_$db_pipes TO sde;
    GRANT EXECUTE ON dbms_pipe TO sde;
  2. Create the following table as the SDE user:
    CREATE TABLE sde.pipe_residue
     (registration_id NUMBER NOT NULL,
      rowid_start     NUMBER NOT NULL,
      rowid_count     NUMBER NOT NULL);
    Additional storage and extent clauses can be specified if the defaults are not acceptable.
  3. Create the stored procedure which unloads and loads the pipes when the Oracle instance is started or shutdown as the SDE user:
    CREATE OR REPLACE PACKAGE sde.preserve_pipes
    IS
    
     PROCEDURE unload_pipes;
     PROCEDURE load_pipes;
    
    END preserve_pipes;
    /
    
    CREATE OR REPLACE PACKAGE BODY sde.preserve_pipes
    IS
    
      /* Package Globals. */
    
      g_pipe_result  INTEGER;
    
     PROCEDURE unload_pipes IS
    
      PRAGMA AUTONOMOUS_TRANSACTION;
    
      CURSOR active_pipes IS
       SELECT name FROM v$db_pipes
       WHERE name LIKE 'ARCSDE_IDPIPE%';
    
      rowid_start  NUMBER;
      rowid_count  NUMBER;
    
     BEGIN
    
       FOR all_pipes IN active_pipes LOOP
     
         LOOP
           g_pipe_result := sys.dbms_pipe.receive_message(all_pipes.name,2);     
           EXIT WHEN g_pipe_result != 0; 
           sys.dbms_pipe.unpack_message(rowid_start);
           sys.dbms_pipe.unpack_message(rowid_count);
           INSERT INTO sde.pipe_residue VALUES (CAST(SUBSTR(all_pipes.name,14) AS NUMBER),rowid_start,rowid_count);   
         END LOOP;
    
         g_pipe_result := sys.dbms_pipe.remove_pipe(all_pipes.name);
    
       END LOOP;
    
       COMMIT;
    
     EXCEPTION
       WHEN OTHERS THEN
       NULL;
    
     END unload_pipes;
    
     PROCEDURE load_pipes IS
    
      PRAGMA AUTONOMOUS_TRANSACTION;
    
      CURSOR arcsde_pipes IS
       SELECT registration_id, rowid_start, rowid_count
       FROM sde.pipe_residue
       ORDER BY registration_id;
    
      prv_regid  NUMBER DEFAULT 0;
    
     BEGIN
    
       FOR all_pipes IN arcsde_pipes LOOP
    
         IF all_pipes.registration_id > prv_regid THEN
           g_pipe_result := sys.dbms_pipe.create_pipe('ARCSDE_IDPIPE'||all_pipes.registration_id||'',8192,FALSE);
         END IF;
    
         sys.dbms_pipe.reset_buffer;
         sys.dbms_pipe.pack_message(all_pipes.rowid_start);
         sys.dbms_pipe.pack_message(all_pipes.rowid_count);
         g_pipe_result := sys.dbms_pipe.send_message('ARCSDE_IDPIPE'||all_pipes.registration_id||'',2);
    
         prv_regid := all_pipes.registration_id;
    
       END LOOP;
    
       DELETE FROM sde.pipe_residue;
       COMMIT;
    
     EXCEPTION
       WHEN OTHERS THEN
       NULL;
    
     END load_pipes;
    
    END preserve_pipes;
    /
  4. Create the triggers that execute when the Oracle instance is started or shut down as the SYS user:
    CREATE OR REPLACE TRIGGER upon_startup AFTER STARTUP ON DATABASE
    
     BEGIN
    
      sys.dbms_system.ksdwrt(2,to_char(sysdate, 'DY MON DD HH:MI:SS YYYY'));
      sys.dbms_system.ksdwrt(2,'Loading ArcSDE pipes...');
      sde.preserve_pipes.load_pipes;
      sys.dbms_system.ksdwrt(2,to_char(sysdate, 'DY MON DD HH:MI:SS YYYY'));
      sys.dbms_system.ksdwrt(2,'Successfully loaded ArcSDE pipes...');
    
     END;
    /
    
    
    CREATE OR REPLACE TRIGGER before_shutdown BEFORE SHUTDOWN ON DATABASE
    
     BEGIN
    
      sys.dbms_system.ksdwrt(2,to_char(sysdate, 'DY MON DD HH:MI:SS YYYY'));
      sys.dbms_system.ksdwrt(2,'Unloading ArcSDE pipes...');
      sde.preserve_pipes.unload_pipes;
      sys.dbms_system.ksdwrt(2,to_char(sysdate, 'DY MON DD HH:MI:SS YYYY'));
      sys.dbms_system.ksdwrt(2,'Successfully unloaded ArcSDE pipes...');
    
     END;
    /
    
    In addition, the triggers write a message to the alert.log during the startup or shutdown with a time stamp indicating the pipes are loading or unloading.
    TUE MAR 15 09:10:02 2005
    Loading ArcSDE pipes...
    TUE MAR 15 09:10:03 2005
    Successfully loaded ArcSDE pipes...
    The time it requires to load the pipes depends on the number of pipes that were present when the instance was shut down. Typically, it should take no more than a few seconds.
    Note:
    If there is no need to preserve the pipes and their values, the triggers, stored procedure and table can be dropped.
    
    See the Oracle documentation for additional information on pipes, triggers and stored procedures.