• Created by Unknown User (fackler), last modified on Nov 26, 2023

BOX=beginning of expedition
EOX=end of expedition
ETL=etract, transfer, load

Overview

This document reviews scripts found in all IODP data TRANSFER schemas. An overview of DBA scripts found on the OEM (Oracle Enterprise Manager) NFS shares is also provided--these scripts are used to perform a variety of bulk database management tasks.

TRANSFER schema procedures

All of these scripts were designed to be executed with TRANSFER schema owner privileges--operating within a given database instance and its web of application-specific permissions and configurations.

BOX script

These scripts assist in automating shipboard beginning of expedition (BOX) database preparations.

BOX_ASMAN_CATALOG_DELETE
BOX_LIME_AUDIT_DELETE
BOX_LIMS_CLEANUP
BOX_LIMS_DEFRAG
BOX_LIMS_EXPEDITION_DELETE
BOX_SM_DELETE_REQUEST_CODES

Container load script

One of the shipboard systems (moisture and density) requires data for pre-weighed containers. This script aids in loading content from an Excel file into the shipboard LIMS.

CL

Data copy script - formerly applied for EOX ETL

These COPY_* scripts were (routinely) applied by shipboard developers to prepare expedition data for transport to shore via an export of the TRANSFER schema. This EOX activity no longer occurs (since Dec 2022, Exp 398). Prior to Dec 2022 the scripts were named as-shown, but without the COPY_ prefix.

The scripts are retained for occasional use. They provide a semi-automated means for aggregating an expedition's data for transport into a test environment via the supporting tables in the TRANSFER schemas.

COPY_ASMAN_CATALOG_TO_NEW
COPY_DESCINFO_TO_NEW
COPY_LIMS_EXPEDITION_TO_NEW

Data cleaning and preening--between ETL activities

These scripts automate occasional data cleaning activities.

UTIL_CLEANOUT_TRANSFER_NEW_TABLES
UTIL_LIMS_TEST_RESULT_DELETE

Implements Shore EOX ETL

This script merges content staged (and modified) at the TRANSFER schema into the LIMS warehouse.

EOX_SHORE_MERGE_TRANSFER_NEW_TO_LIMS

iRIS

No merge scripting is in place for IRIS data. Direct imports are taken from export files to the OPS schema for tables RIS_CONFIGURATION, RIS_EVENT, and RIS_DATA.

OVERVIEW script

These scripts support the computation of summary data presented by the database OVERVIEW application. Each script summarizes a different kind of data. The scripts were revised to log activity and progress to TRANSFER.LOG. Formerly the DBMS_OUTPUT package was used. Current usage is more transparent. The log content provides a resource for process auditability.

DBO_COMPUTE_1001
DBO_COMPUTE_1011
DBO_COMPUTE_1021
DBO_COMPUTE_1041
DBO_COMPUTE_1051
DBO_COMPUTE_1061
DBO_COMPUTE_1071
DBO_COMPUTE_1081
DBO_COMPUTE_1091
DBO_COMPUTE_1101
DBO_COMPUTE_1111
DBO_COMPUTE_1121
DBO_COMPUTE_1131
DBO_COMPUTE_1141
DBO_COMPUTE_1151
DBO_COMPUTE_1161
DBO_COMPUTE_1171
DBO_COMPUTE_1181
DBO_COMPUTE_1191
DBO_COMPUTE_1201
DBO_COMPUTE_1211
DBO_COMPUTE_1221
DBO_COMPUTE_1225
DBO_COMPUTE_1231
DBO_COMPUTE_1241
DBO_COMPUTE_1251
DBO_COMPUTE_1255
DBO_COMPUTE_1261
DBO_COMPUTE_1271
DBO_COMPUTE_1281
DBO_COMPUTE_1291
DBO_COMPUTE_1301
DBO_COMPUTE_1311
DBO_COMPUTE_1321
DBO_COMPUTE_1331
DBO_COMPUTE_1341
DBO_COMPUTE_1351
DBO_COMPUTE_1361
DBO_COMPUTE_ALL
DBO_COMPUTE_EXP

Database management scripts

The OEM (Oracle Enterprise Manager) hosts on shore and ship host an NSF mount point used by each ODA (Oracle Data Appliance). As the mount point is read-write to all ODAs, it is a convenient space to store scripts for DBA operations.

Scripts used for a variety of database management purposes may be found here and in underlying subdirectories: /backup/scripts.

The script sets named for an expedition indicate collections of work done to complete the shore side of EOX merges of ship data into shore: /backup/scripts/rci_work. The /exp folder is the generic and parameterized template that is carried forward.

EOX load and merge - rci_work/exp

The collection of scripts in this location was used to automate the shore components of EOX processing: retrieve last expedition content from the database dump, create and apply moratorium supports, merge the data into production; provide statistics which give quality assurance/quality control on the process. Excepting import_transfer.ksh, in_moratorium.ksh, and crt_user_exp.ksh--all other scripts are considered legacy examples of OS automation for database processes.

These are the scripts (in-order) that are typically used

# Shore EOX processing for a data load
# per Sunil Armarni from a Slack thread.
export EOX=#
chk_eox_count.ksh
select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'TRANSFER'
import_transfer.ksh
transfer_upd_stat.ksh
data_pre_01_crt_unq.ksh
data_pre_01_chk_dup.ksh
eox_gen_in_moratorium_script.ksh <GROUP>
in_moratorium_<GROUP>.ksh
crt_user_exp.ksh $EOX
chk_tablespace_free.ksh
grp_cnt_transfer.ksh
data_pre_03_dis_trig.ksh
dis_trig_all_<YYYMMDD_HHMMSS>.sql
chk_eox_count.ksh exp#
exec transfer.lims_expedition_delete('#')
chk_eox_count.ksh
grp_cnt_lims.ksh
data_pre_05_without_DI_v6.ksh # calls scors also
grp_cnt_lims.ksh
chk_scale_data.ksh
data_pre_05_scale_data.ksh
chk_scale_data.ksh
enb_trig_all_<YYYMMDD_HHMMSS>.sql
merge_descinfo2_data_v2.ksh
insert_dbo_#.sql - N/A
dbo_compute_v4.sql exp#
data_post_06_upd_stat.ksh
  • No labels