Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

BOX - beginning of expedition
EOX - end of expedition

The DBA is responsible for shore EOX processing. In a pinch programmers and systems personnel can fill in (they just don't know it yet).

This expedition is ended. Within two weeks, science participants expect to have moratorium access to LIMS content. DBA, Systems/MCS, and sometimes Programmers need to coordinate to help this happen. The Database Librarian needs to know when these changes occur too.

Make sure we are getting as complete a copy of the data to be warehoused on shore as we can.

0. Notify that the data load is in progress

Lot's of folks want to know: Systems, Data Librarian, Programmers, EPMs, Managers, selected TAS staff.

It is helpful to chat with Curation. If there is a sampling party or post-cruise meeting in progress, it is preferrable to post-pone the Copy the content step to a less busy time. Otherwise activities up to that point can be completed without any production impact.

1. Retrieve data content from tape

Subsets of the EOX tapes are distributed to Publications, Data Librarian, DBA, Operations, and public access storage locations. - Systems

A copy of the shipboard database export is stage for DBA use at OEM:/backup/eox/

  • The database dump is backed up to tape via the host OEMJR:/backup/eox/.  TBD--confirm location and match in DEV and MCS SOPs.
  • When restored, the file is copied to OEM:/backup/eox/.

There should be one data dump file and an accompanying log of the export activity.

  • When the file contains a full shipboard database export the naming pattern is like limsjr_full-398.dmpdp
  • When the file contains selected schema exports it is named like limsjr_schemas-398.dmpdp

For the shore EOX work, the filename is typically shortened to the expedition designation, e.g. 398.dmpdp.

2. Restore and review

Restore routinely selected database content from the full backup to the shore production transfer schema. - DBA

Once staged extract, transfer, and load scripts may be run.

  • DBA scripting for the EOX (and other) processes are hosted at OEM:/backup/scripts/rci_work/exp/
  • This scripting is designed to be run from an ODA command-prompt.
  • Privileges to sudo to the ODA oracle user are required.

Other pre-requisites.

  • What database instance are you connecting to? You must be at a command-line for that ODA instance.

  • As the OS oracle user specify which database instance you are using with this syntax

    . oraenv

import_transfer.ksh

When executed, this script

  • Prompts for the local name (and extension) of the database export file. Upon <Enter>
  • Shipboard content is imported into the TRANSFER schema. Indexes are created for the largest rowcount tables: new_sample, new_test, new_result.
  • The script now prompts for the expedition of interest, e.g. 398, 397T, etc. Upon <Enter>
  • The script imports reference copies of GEODCAT (renamed to include the expedition) and GEODP### schemas.

On completion--of the schema imports above--a variety of SQL statements are run to highlight what data was brought back and how many rows.

  • This script imported 398 content over multiple tests between 30 and 40 minutes.
    Additional data review scripts take another 10-15 minutes to run.
  • The script requires no TRANSFER schema preparation--all the tables required are present, the import methodology truncates the tables before importing new content.
  • The content is imported directly from SCHEMAS exported on ship (LIMS, DESCINFO2, OPS, GEODCAT, GEODP###, etc.).
    There is no duplication of keys incurred by the ETL cycle now in use.

3. Establish moratorium credentials and controls

For the new expedition data. - DBA

Pick a name and credential. The account is shared by a science party for the moratorium period. The role created with the same name ensures that science party is granted the exception to see its moratorium data.

eox_gen_in_moratorium_script.ksh [moratorium-group]

This script accepts a moratorium name, e.g. SHACKLETON, SANTORINI, etc.

It generates another script (below) that will be run to modify NEW_TEST, NEW_RESULT, and NEW_GEOD_DATA tables for moratorium concerns.

  • If a moratorium group is not provided, the script will prompt.
  • The script does not check these requirement
    • The GROUP_NAME field that this string populates is varchar2(10)--10 characters or less. It is conventional to use a memorable uppercase name. Often the EPMs will provide both a name and a credential.
    • The string must also be a valid name for a ROLE. Example: should not start with a number; must be valid for naming a Unix file.

in_moratorium_[moratorium-group].ksh

Running this script

  • Ensures the current expedition content is tagged for moratorium control.
  • Do review the script. Sometimes the EOX expedition number is cleared before running eox_gen_in_moratorium_script.ksh.

Upon running the script--wait.

  • The NEW_RESULT table typically contains 20-30 million rows. This update takes the longest.
  • The NEW_TEST and NEW_SAMPLE table are much faster due to being 3 orders of magnitude smaller.
  • Timing is turned on--the scripts will show how long they took to run.

crt_user_exp.sql

Review the script.

  • Make a copy of it specifically for the expedition you are processing--e.g. crt_user_398.sql
  • Modify the user credential to reflect EPM requirements.

Run the script via SQL.
Provide an explicit path to the script to run it.
Pass the expedition as a parameter to the script.

sql / as sysdba
SQL>@/backup/scripts/rci_work/exp/crt_user_398.sql 398


4. Copy the content

Into the publicly accessible LIMS. - DBA

5. Update the Database Overview

Configuration updates provide summary of the expedition data for participants and future users. - DBA

To be worked in. The scripts below are applicable to steps 2. thru 5.

EOX load and merge - rci_work/exp

The collection of scripts in this location is 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.

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

Other script

tbd


  • No labels