Oracle Identity Manager 11gR2 PS2 - LOBs Migration to SecureFile

Version 6

    Oracle 11g introduced SecureFiles to eliminate the distinction between structured and unstructured content storage. The core Oracle Database infrastructure upon which future content management strategies will be based, this new re-architecture features entirely new disk formats, network protocol, space management, redo and undo formats, buffer caching and intelligent I/O subsystems.

     

    Article by Lokesh Gupta

     

    Downloads

     

    Compared to the storage structure of Large Objects (LOBs), that of SecureFiles delivers substantially improved performance along with optimized storage for unstructured data that resides in Oracle Database. LOBs from Oracle 10g and prior releases are still supported and are referred to as "BasicFiles."

     

    SecureFiles features that improve the capabilities of a relational database management system (RDBMS) to store unstructured data include:

     

    • Write-Gather cache
    • Space management
    • Reduced fragmentation
    • Intelligent pre-fetching
    • New network layer
    • No LOB index contention
    • No high-water (HW) mark contention
    • Easier manageability
    • Superior performance

     

    HW Enqueue Contention on LOB Segments

     

    Oracle Identity Management's (OIM) orchestration framework is a kernel that uses LOBs to store unstructured data extensively. In numerous cases, high HW enqueue contentions on LOB segments in a high concurrent environment slow down application behaviors and increase resources utilization, especially CPU. You will see these enqueue events in Oracle RDBMS Automatic Workload Repository (AWR) reports as one of the top wait events.

     

    SECUREFILE LOBs and hash partition primary keys (PKs) are highly recommended to solve high-water (H)W contention on the LOB segments of ORCHPROCESS and ORCHEVENTS tables. Currently, their LOB segments are sitting on BASICFILE LOBs, and migration to SECUREFILE is recommended. Migration is a DBA-intensive operation and requires that OIM servers be down during migration.

     

    Migration should be planned with proper testing. Below are more details about pre-requisites/assumptions, which LOB segments to migrate, hash partition and testing.

     

    Pre-Requisites And Assumptions:

    1. SECUREFILE requires Automatic Segment Space Management (ASSM) tablespace.
    2. The COMPATIBLE parameter should be 11.0.0.0 or above.
    3. The DB_SECUREFILE parameter is not set to IGNORE/NEVER. The default value for this init.ora is PERMITTED, and that should be fine.
    4. You should be familiar with the migration process before attempting it. Refer to the following MoS (My Oracle Support) notes:    
      • "High Waits On HW Enqueue due to insert into LOB column" (ID 1349285.1)
      • Video: "Secure File Migration and Accessing SecureFile Metadata Information" (ID 1170351.1)
    5. You must perform SECUREFILE migration in a stage/test environment first and make sure it works properly. Then run a few self-service use cases (e.g., Create User, Reset Password, etc.) to make sure OIM is functioning correctly before rolling out the migration in production and re-performing the same testing.
    6. The SQL statements/commands given in the steps below should not be used as-is. Changes (e.g., schema name) are required. These steps are for illustration purposes only.
    7. A proper backup of affected tables ORCHPROCESS and ORCHEVENTS is required.
    8. Make sure your OIM schema have all required privileges for this migration. Check the MoS notes referenced above for more information.
    9. The OIM server should be down until migration completes.

     

    LOB Segments To Be Migrated To SECUREFILE

     

    Table: ORCHPROCESS
      LOB Column: ORCHESTRATION
      Details:

     

    • Use CACHE, which implies LOGGING.
    • Remove CHUNK, which is not relevant in SECUREFILE.
    • No advanced options are required (i.e., Compress/Deduplication/Encryption). No advanced options are the default, so you do not have to specify NOCOMPRESS, etc.
    • Take the default for RETENTION (AUTO).

     

     

    Table: ORCHPROCESS
      LOB Column: CONTEXTVAL
      Details:

     

    • Use NOCACHE with LOGGING.
    • No advanced options required (i.e., compress/deduplication/encryption). No advanced options are the default, so you do not have to specify NOCOMPRESS, etc.
    • Take the default for RETENTION (AUTO).

     

     

    Table: ORCHEVENTS
      LOB Column: RESULT
      Details:

     

    • Use NOCACHE with LOGGING.
    • No advanced options required (i.e., Compress/deduplication/encryption). No advanced options are the default so you do not have to say NOCOMPRESS, etc.
    • Take the default for RETENTION (AUTO).

     

     

    Partition Option

     

    HASH partitions the PK, and indexing of the ORCHPROCESS and ORCHEVENTS tables is recommended.  Details are given in the next section. Note: An Oracle RDBMS Partitioning license is required.

     

    Migrating LOB Segments Using DBMS_REDEFINITION

     

    While there are a few ways to migrate a LOB segment to SECUREFILE, it's often fastest to use DBMS_REDEFINITON:

     

    To execute the steps below, use SYS/DBA user or provide the following privileges to the OIM schema:

     

    1. Grant execute on sys. DBMS_REDEFINITION to <OIM_SCHEMA)>;
    2. Grant CREATE ANY TABLE to <OIM_SCHEMA)>;
    3. Grant ALTER ANY TABLE to <OIM_SCHEMA)>;
    4. Grant DROP ANY TABLE to <OIM_SCHEMA)>;
    5. Grant LOCK ANY TABLE to <OIM_SCHEMA)>;
    6. Grant SELECT ANY TABLE to <OIM_SCHEMA)>;

     

    Note: Some of these grants will already be present.

    1. Check if both LOB segments on ORCHPROCESS are already on SECUREFILE storage. If yes, stop and proceed to the following section on migrating the LOB on the ORCHEVENTS table.

      • From <OIM_SCHEMA>
        SELECT SECUREFILE FROM USER_LOBS WHERE TABLE_NAME = 'ORCHPROCESS';
      • From SYS/DBA
        SELECT SECUREFILE FROM ALL_LOBS WHERE TABLE_NAME = 'ORCHPROCESS' AND OWNER='<OIM_SCHEMA>';
    2. Check if the ORCHPROCESS table can be redefined online. Do not proceed if it cannot be redefined online.

      SET SERVEROUTPUT ON
      BEGIN
      DBMS_REDEFINITION.CAN_REDEF_TABLE('<OIM_SCHEMA)>','ORCHPROCESS', DBMS_REDEFINITION.CONS_USE_ROWID); END;
      /
    3. Get the original Data Definition Language (DDL) of the ORCHPROCESS table and store it separately.

      select dbms_metadata.get_ddl('TABLE','ORCHPROCESS','<OIM_SCHEMA)>') from dual;
    4. After getting the DDL of the original ORCHPROCESS table, make the following changes in that DDL:

      1. HASH partition the PK.

        REPLACE  "CONSTRAINT "ORCHPROCESS_PK" PRIMARY KEY ("ID")  USING INDEX"
        WITH "CONSTRAINT ORCHPROCESS_PK PRIMARY KEY (ID) USING INDEX GLOBAL PARTITION BY HASH(ID) PARTITIONS 64"
      2. Use SECUREFILE for 'ORCHESTRATION' and 'CONTEXTVAL' BLOB columns in ORCHPROCESS table.
        REPLACE  'LOB ("ORCHESTRATION") STORE AS BASICFILE "ORCHPROCESS_LOB_SEG" TABLESPACE "<OIM_LOB_TABLESPACE>" ' 
        WITH ' LOB ("ORCHESTRATION") STORE AS SECUREFILE " ORCHPROCESS_ORCH_LOB" TABLESPACE "<OIM_LOB_TABLESPACE>" ' 
        REPLACE  " LOB ("CONTEXTVAL") STORE AS BASICFILE "
        WITH LOB ("CONTEXTVAL") STORE AS SECUREFILE "ORCHPROCESS_CONTEXTVAL_LOB" TABLESPACE "<OIM_LOB_TABLESPACE>"  '
    5. Change the original table name to ORCHPROCESS_TEMP (from ORCHPROCESS) in the DDL that you generated in step 3.
    6. Drop the PK constraint and index on the original ORCHPROCESS table and FK from the ORCH_BENEFICIARIES table, using the commands below:
      ALTER TABLE <OIM_SCHEMA>.ORCHPROCESS DROP CONSTRAINT ORCHPROCESS_PK;
      ALTER TABLE <OIM_SCHEMA>.ORCH_BENEFICIARIES DROP CONSTRAINT FK_ORCH_BENEFICIARIES_PROC_ID;
    7. Run the DDL script (from steps 3 and 4) to create an interim table called ORCHPROCESS_TEMP.
    8. Start the redefinition process using the command below.
      BEGIN
         DBMS_REDEFINITION.START_REDEF_TABLE(
                      uname => '<OIM_SCHEMA)>',
                      orig_table => 'ORCHPROCESS',
                      int_table => 'ORCHPROCESS_TEMP',
                      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
      END;
      /
    9. Synchronize the interim table with the original table.
      EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('STAGE_OIM','ORCHPROCESS','ORCHPROCESS_TEMP');
    10. Copy the dependent objects from the original table (ORCHPROCESS) to the interim table (ORCHPROCESS_TEMP) using the command below:
      DECLARE
           error_count pls_integer := 0;
      BEGIN
           DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('', 'ORCHPROCESS', 'ORCHPROCESS_TEMP', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
           DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
      END;
      /
    11. Finish the redefinition process using the command below:
      EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('<OIM_SCHEMA)>', 'ORCHPROCESS', 'ORCHPROCESS_TEMP');
    12. Check and make sure both LOB segments in the ORCHPROCESS table are now on SECUREFILE and that the PK of ORCHPROCESS is hash partitioned by executing the following queries:
      "     From <OIM_SCHEMA>
      SELECT SECUREFILE FROM USER_LOBS WHERE TABLE_NAME = 'ORCHPROCESS';
      SELECT count(*) FROM user_ind_partitions WHERE index_name='ORCHPROCESS_PK';
      "     From SYS/DBA
      SELECT SECUREFILE FROM ALL_LOBS WHERE TABLE_NAME = 'ORCHPROCESS' AND OWNER='<OIM_SCHEMA>';
      SELECT count(*) FROM all_ind_partitions WHERE index_name='ORCHPROCESS_PK' AND INDEX_OWNER='<OIM_SCHEMA>';
    13. Recreate the FK on the ORCH_BENEFICIARIES table using the command below:
      ALTER TABLE <OIM_SCHEMA>.ORCH_BENEFICIARIES ADD CONSTRAINT FK_ORCH_BENEFICIARIES_PROC_ID  FOREIGN KEY (PROCESSID

      ) REFERENCES ORCHPROCESS (ID) ENABLE;
    14. Gather table stats for the ORCHPROCESS table.
      exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> '<OIM_SCHEMA)>', tabname => 'ORCHPROCESS',ESTIMATE_PERCENT=>DBMS_ST

      ATS.AUTO_SAMPLE_SIZE, DEGREE=>8,cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE 254');


    15. Drop the interim table.
      DROP TABLE <OIM_SCHEMA>.ORCHPROCESS_TEMP;

    Steps To Consider When Redefinition of the ORCHEVENTS Table Fails or Must be Aborted:

    EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE ('<OIM_SCHEMA>','ORCHEVENTS','ORCHEVENTS_TEMP');
    DROP TABLE <OIM_SCHEMA>.ORCHEVENTS_TEMP;
    ALTER TABLE <OIM_SCHEMA>.ORCHEVENTS ADD CONSTRAINT ORCHEVENTS_PK PRIMARY KEY("ID") USING INDEX;

     

    About the Author

     

    Lokesh Gupta is a Project Lead with Oracle Server Technology group for Oracle Identity Manager, where he focuses on issues related to database, enterprise performance and sizing engineering, and security.
      small_linkedin_logo.png small_facebook_logo.png