Disable Advanced Compression Oracle Option in FOM 4

Disable Advanced Compression Oracle Option in FOM 4

book

Article ID: KB0074569

calendar_today

Updated On:

Products Versions
TIBCO Order Management 4.x

Description

By default, FOM SERVER_CACHE table is created with advanced compression enabled (see:OMS_DDL.sql). As this is a paid Oracle option, customer may would like to run FOM without it. This article explains how to achieve this.

Issue/Introduction

This article deals with disabling advanced compression oracle option when using FOM 4 version

Environment

ALL

Resolution

In case of creating a new FOM Database schema, execute OMS_DDL.sql after having removed "securefile" and "COMPRESS HIGH" from SERVER_CACHE table section. Therefore, change will be from:
====
CREATE TABLE SERVER_CACHE
(
  CACHE_ID NUMBER(20,0) NOT NULL PRIMARY KEY,
  CACHE_NAME VARCHAR2(100 char) NOT NULL,
  CACHE_TYPE VARCHAR2(50 char),
  DATA BLOB NOT NULL,
  LAST_UPDATED_ON TIMESTAMP (6) WITH TIME ZONE,
  CACHE_VALIDITY VARCHAR2(1 char) NOT NULL
)
TABLESPACE &1
LOB (DATA)
STORE AS securefile
(
        TABLESPACE &1
        ENABLE STORAGE IN ROW
        COMPRESS HIGH
        CHUNK 8192
        PCTVERSION 0
        NOCACHE
        NOLOGGING
)
LOGGING
NOCACHE
NOPARALLEL
MONITORING;
===

To:
==
CREATE TABLE SERVER_CACHE
(
  CACHE_ID NUMBER(20,0) NOT NULL PRIMARY KEY,
  CACHE_NAME VARCHAR2(100 char) NOT NULL,
  CACHE_TYPE VARCHAR2(50 char),
  DATA BLOB NOT NULL,
  LAST_UPDATED_ON TIMESTAMP (6) WITH TIME ZONE,
  CACHE_VALIDITY VARCHAR2(1 char) NOT NULL
)
TABLESPACE &1
LOB (DATA)
STORE AS
(
        TABLESPACE &1
        ENABLE STORAGE IN ROW
        CHUNK 8192
        PCTVERSION 0
        NOCACHE
        NOLOGGING
)
LOGGING
NOCACHE
NOPARALLEL
MONITORING;
===

Now to remove this option from an existing FOM schema, please follow below steps: 
1/Stop FOM instances.
2/Drop server_cache table.
3/Recreate server_cache table without advanced compression. Obviously, no need to run oms_ddl.sql script, just recreate SERVER_CACHE table.
===
CREATE TABLE SERVER_CACHE
(
  CACHE_ID NUMBER(20,0) NOT NULL PRIMARY KEY,
  CACHE_NAME VARCHAR2(100 char) NOT NULL,
  CACHE_TYPE VARCHAR2(50 char),
  DATA BLOB NOT NULL,
  LAST_UPDATED_ON TIMESTAMP (6) WITH TIME ZONE,
  CACHE_VALIDITY VARCHAR2(1 char) NOT NULL
)
TABLESPACE &1
LOB (DATA)
STORE AS
(
        TABLESPACE &1
        ENABLE STORAGE IN ROW
        CHUNK 8192
        PCTVERSION 0
        NOCACHE
        NOLOGGING
)
LOGGING
NOCACHE
NOPARALLEL
MONITORING;
===

4/ restart FOM instances.

 Note that server_cache will fill automatically after restart. It will not harm existing functioning FOM Servers.