History Tables

Oracle Utilities Network Management System includes various tables for recording and trending historical performance of SCADA analogs, feeder loading, and device violations. The data for this historical performance are stored in the description below. As with the model management table, it is strongly recommended that Oracle RDBMS partitioning technology be used to manage the contents of these tables in a more economical fashion, and, ultimately, allow retrieval (and eventual deletion) in an efficient manner. The nms‑setup script creates these tables as partitioned if the partitioning option is active in the $NMS_DATA_FILES/[project]_licensed_products.dat file. Similarly, if a migration from a previous version is performed, the tables are created as partitioned by the migration scripts.

As with the model tables, the partitioning scheme used is to PARTITION BY RANGE on a date column. The tables that are partitioned and their range column are:

Table Name	Range Column	Contents
SCADA_ANALOG_HISTORY	SCADA_TIME	History of SCADA analog values.
FLM_FDR_LOAD_HISTORY	ANALYSIS_DATE	History of Feeders' FLM performance.
FLM_FDR_LOAD_DETAILS_HISTORY	ANALYSIS_DATE	History of devices' FLM performance.
FLM_DEV_VIOLATIONS_HISTORY	UPDATE_DATE	History of the devices’ violations.

If the tables already exist in the schema as non-partitioned, then migration scripts will only alter them to the extent that modified columns are added, updated, or deleted. It is not possible to directly modify a non-partitioned table into a partitioned table; to do so requires a manual migration process.

If there is no historical data in FLM_FDR_LOAD_HISTORY, FLM_FDR_LOAD_DETAILS_HISTORY, FLM_DEV_VIOLATIONS_HISTORY, or SCADA_ANALOG_HISTORY, then it might be worthwhile to configure NMS for partitioning, dropping the aforementioned tables and then running the migration. The migration will create the tables (with partitioning) if they are not present.

If there is historical data in those tables that is worth keeping, then they will need to be semi‑manually migrated. To do that, the following steps should be performed after partitioning in NMS is activated:

1. Run the migration script to add the new columns to the existing tables.

2. Rename the tables with new names (for example, FLM_FDR_LOAD_HISTORY_0000).

3. Re-run the migration script (this will have to be done manually with ISQL ‑admin and not nms‑apply‑migrations) to create new versions of those tables with partitioning.

4. Copy the data from the original tables to the new ones with commands like:

insert into flm_fdr_load_history ( select * from

flm_fdr_load_history_0000);

5. Run nms‑setup to re-create triggers and procedures.

6. Drop or archive the copy tables.

The large quantity of data requires a Lifecycle Management process to be in place to prevent unbounded growth of the tables. Product configuration scripts create an RDBMS stored procedure (PURGE_HISTORY_TABLES) that will prune these history tables.

Note: The RETAIN_HISTORY_RECORDS entry in CES_PARAMETERS configures how many weeks of history to preserve on-line. By default, the value is four weeks.

Oracle recommends that you create a cron table entry that will run the PURGE_HISTORY_TABLES procedure weekly (for example, 1:00 a.m. on Sunday) using the following DDService command:

Action any.DDService purge_history

To purge all of the existing Feeder Contingency Analysis results, use the following PFService command:

Action any.PFService purge_fca

To purge all of the existing Feeder Data Analysis results, use the following PFService command:

Action any.PFService purge_fda