BOX - beginning of expedition
EOX - end of expedition
The DBA is responsible for shore EOX processing.
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 a complete a copy of the shipboard data to be warehoused on shore.
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.
From that command-line, the following shell and SQL scripts (bold header) are run. In the order shown.
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.
EOX_EXP_DIR=`sqlplus -s / as sysdba << EOT
set pages 0 feedback off
SELECT directory_path FROM dba_directories WHERE directory_name = 'EOX_EXP_DIR';
exit
EOT`
# DIRECTORY_PATH
# --------------------------------------------------------------------------------
# /backup/eox
if [ ! -d $EOX_EXP_DIR ]
then
echo $EOX_EXP_DIR not found...
exit 1
fi
if [ $# -ne 1 ]
then
echo Enter dump file name
read DUMP_FILE_NAME
else
DUMP_FILE_NAME=$1
fi
if [ ! -r $EOX_EXP_DIR/$DUMP_FILE_NAME ]
then
echo $EOX_EXP_DIR/$DUMP_FILE_NAME not found
exit 2
fi
DT=`date +"%m%d%Y"`
SCHEMA_NAME=transfer
LOG_FILE_NAME=${SCHEMA_NAME}_import_${DT}.log
# Import LIMS, DESCINFO2, and OPS tables into target TRANSFER schema.
impdp system/$s directory=eox_exp_dir DUMPFILE=$DUMP_FILE_NAME LOGFILE=${LOG_FILE_NAME} VERSION=LATEST PARFILE=eox_impdp_to_transfer.par
while [ ${EOX}1 == "1" ]
do
echo Enter EOX#
read EOX
done
if [ ${EOX}1 == "1" ]
then
echo EOX is not set.
exit 1
fi
sql / as sysdba <<+++
drop user geodcat${EOX} cascade;
drop user geodp${EOX} cascade;
exit;
+++
# Import GEODCAT to an GEODCAT### where ### is the expedition being processed.
SCHEMA_NAME=geodcat${EOX}
LOG_FILE_NAME=${SCHEMA_NAME}_import_${DT}.log
impdp system/$s directory=eox_exp_dir DUMPFILE=$DUMP_FILE_NAME LOGFILE=${LOG_FILE_NAME} VERSION=LATEST CONTENT=ALL SCHEMAS=GEODCAT REMAP_SCHEMA=GEODCAT:${SCHEMA_NAME}
# Import GEODP### where ### is the expedition being processed.
SCHEMA_NAME=geodp${EOX}
LOG_FILE_NAME=${SCHEMA_NAME}_import_${DT}.log
impdp system/$s directory=eox_exp_dir DUMPFILE=$DUMP_FILE_NAME LOGFILE=${LOG_FILE_NAME} VERSION=LATEST CONTENT=ALL SCHEMAS=${SCHEMA_NAME}
# TBD--revise to log this output to a single file for reference after data modifications and copying.
sql / as sysdba <<+++
set newpage 0
set linesize 100
set pagesize 0
set timing on
set time on
set feedback on
set heading on
set trimspool on
set trimout on
set tab off
/* These indexes help speed up later bulk data manipulation and merge activites. */
drop index transfer.new_result_idx;
drop index transfer.new_result_result_number;
drop index transfer.new_test_idx;
drop index transfer.new_test_test_number;
drop index transfer.new_sample_sample_number;
create bitmap index transfer.new_result_idx on transfer.new_result ( sample_number, test_number, result_number ) tablespace transfer parallel;
create unique index transfer.new_result_result_number on transfer.new_result (result_number) tablespace transfer parallel;
create bitmap index transfer.new_test_idx on transfer.new_test ( sample_number, test_number ) tablespace transfer parallel;
create unique index transfer.new_test_test_number on transfer.new_test (test_number) tablespace transfer parallel;
create unique index transfer.new_sample_sample_number on transfer.new_sample (sample_number) tablespace transfer parallel;
/* Review the content loaded in TRANSFER in finer detail than the import activity. */
-- Review the sample, test, result content just loaded to TRANSFER
alter session set current_schema=transfer;
select 'REVIEW TRANSFER.NEW_SAMPLE' from dual;
select s.group_name, s.x_expedition, s.status, count(*) from transfer.new_sample s where s.x_expedition in ('${EOX}', 'QAQC', '999') group by s.group_name, s.x_expedition, s.status order by s.x_expedition, s.group_name, s.status;
select 'REVIEW TRANSFER.NEW_TEST' from dual;
select t.group_name, s.x_expedition, t.status, count(*) from transfer.new_sample s, transfer.new_test t where s.sample_number=t.sample_number and s.x_expedition in ('${EOX}', 'QAQC', '999') group by t.group_name, s.x_expedition, t.status order by s.x_expedition, t.group_name, t.status;
select 'REVIEW TRANSFER.NEW_RESULT' from dual;
select r.group_name, s.x_expedition, r.status, count(*) from transfer.new_sample s, transfer.new_result r where s.sample_number=r.sample_number and s.x_expedition in ('${EOX}', 'QAQC', '999') group by r.group_name, s.x_expedition, r.status order by s.x_expedition, r.group_name, r.status;
-- Compare to content in LIMS
alter session set current_schema=lims;
select 'REVIEW LIMS.SAMPLE' from dual;
select s.group_name, s.x_expedition, s.status, count(*) from lims.sample s where s.x_expedition in ('${EOX}', 'QAQC', '999') group by s.group_name, s.x_expedition, s.status order by s.x_expedition, s.group_name, s.status;
select 'REVIEW LIMS.TEST' from dual;
select t.group_name, s.x_expedition, t.status, count(*) from lims.sample s, lims.test t where s.sample_number=t.sample_number and s.x_expedition in ('${EOX}', 'QAQC', '999') group by t.group_name, s.x_expedition, t.status order by s.x_expedition, t.group_name, t.status;
select 'REVIEW LIMS.RESULT' from dual;
select r.group_name, s.x_expedition, r.status, count(*) from lims.sample s, lims.result r where s.sample_number=r.sample_number and s.x_expedition in ('${EOX}', 'QAQC', '999') group by r.group_name, s.x_expedition, r.status order by s.x_expedition, r.group_name, r.status;
-- Gather stats for the TRANSFER schema
exec dbms_stats.gather_schema_stats('TRANSFER', cascade=>TRUE);
-- Show info about tables imported from data pump export
select table_name, num_rows, avg_row_len, blocks, empty_blocks, owner from all_tables where owner='TRANSFER' order by owner, num_rows desc;
exit
+++
and its companion
CONTENT=DATA_ONLY
TABLE_EXISTS_ACTION=TRUNCATE
# Fully load all tables required.
# Complex filtering was problematic, so handling it post import.
# Nota bene--DESCINFO2 tables on ship will be retired within 2023.
# LIMS
TABLES=LIMS.FILES, LIMS.SAMPLE, LIMS.TEST, LIMS.RESULT, LIMS.X_EXTERNAL_KEYS, LIMS.X_SCALE, LIMS.X_HOLE_TO_SCALE, LIMS.X_SAMPLE_DEPTH, LIMS.SCORS_AFFINE, LIMS.SCORS_AFFINE_DETAIL, LIMS.SCORS_SPLICE, LIMS.SCORS_SPLICE_DETAIL, LIMS.X_LIME_AUDIT_MAIN, LIMS.X_LIME_AUDIT_DETAIL, LIMS.GEOD_METADATA, LIMS.GEOD_DATA
REMAP_SCHEMA=LIMS:TRANSFER
REMAP_TABLE=LIMS.FILES:NEW_FILES, LIMS.SAMPLE:NEW_SAMPLE, LIMS.TEST:NEW_TEST, LIMS.RESULT:NEW_RESULT, LIMS.X_EXTERNAL_KEYS:NEW_X_EXTERNAL_KEYS, LIMS.X_SCALE:NEW_X_SCALE, LIMS.X_HOLE_TO_SCALE:NEW_X_HOLE_TO_SCALE, LIMS.X_SAMPLE_DEPTH:NEW_X_SAMPLE_DEPTH, LIMS.SCORS_AFFINE:NEW_SCORS_AFFINE, LIMS.SCORS_AFFINE_DETAIL:NEW_SCORS_AFFINE_DETAIL, LIMS.SCORS_SPLICE:NEW_SCORS_SPLICE, LIMS.SCORS_SPLICE_DETAIL:NEW_SCORS_SPLICE_DETAIL, LIMS.X_LIME_AUDIT_MAIN:NEW_X_LIME_AUDIT_MAIN, LIMS.X_LIME_AUDIT_DETAIL:NEW_X_LIME_AUDIT_DETAIL, LIMS.GEOD_METADATA:NEW_GEOD_METADATA, LIMS.GEOD_DATA:NEW_GEOD_DATA
# DESCINFO2
# DEPRECATED--The need for this segment will go away within the year. DESCINFO2 tables on ship will be retired within 2023.
TABLES=DESCINFO2.ATTRIBUTES, DESCINFO2.FONTS, DESCINFO2.GROUPS, DESCINFO2.OBSERVABLES, DESCINFO2.PARAMS, DESCINFO2.PATTERNS, DESCINFO2.SOFTWARECONFIG, DESCINFO2.SUBSYSTEMS, DESCINFO2.SYMBOLS, DESCINFO2.VALUELISTS
REMAP_SCHEMA=DESCINFO2:TRANSFER
REMAP_TABLE=DESCINFO2.ATTRIBUTES:NEW_ATTRIBUTES, DESCINFO2.FONTS:NEW_FONTS, DESCINFO2.GROUPS:NEW_GROUPS, DESCINFO2.OBSERVABLES:NEW_OBSERVABLES, DESCINFO2.PARAMS:NEW_PARAMS, DESCINFO2.PATTERNS:NEW_PATTERNS, DESCINFO2.SOFTWARECONFIG:NEW_SOFTWARECONFIG, DESCINFO2.SUBSYSTEMS:NEW_SUBSYSTEMS, DESCINFO2.SYMBOLS:NEW_SYMBOLS, DESCINFO2.VALUELISTS:NEW_VALUELISTS
# OPS
#TABLES=OPS.RIS_DATA
#REMAP_SCHEMA=OPS:TRANSFER
#REMAP_TABLE=OPS.RIS_DATA:NEW_RIS_DATA
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.
DT=`date +"%Y%m%d_%H%M%S"`
LOG1=upd1_result_grp_name_${DT}.txt
LOG2=upd2_result_grp_name_${DT}.txt
echo "Started at `date`"
if [ $# -ne 1 ]
then
echo Enter GRP_NM
read GRP_NM
else
GRP_NM=$1
fi
KSH_SCRIPT=in_moratorium_${GRP_NM}.ksh
echo "sql / as sysdba <<+++" >${KSH_SCRIPT}
echo "alter session set current_schema=transfer;" >>${KSH_SCRIPT}
echo "set echo on" >>${KSH_SCRIPT}
echo "set pagesize 0" >>${KSH_SCRIPT}
echo "set linesize 132" >>${KSH_SCRIPT}
echo "set timing on" >>${KSH_SCRIPT}
echo "set heading off" >>${KSH_SCRIPT}
echo "spool in_moratorium_${GRP_NM}.log" >>${KSH_SCRIPT}
echo "update transfer.new_result set group_name='${GRP_NM}' where group_name='DEFAULT' and analysis not in ('BHA','BIT','DEPLETED','DRILLING','LATLONG','ORIGDEPTH','ROUTING','SCALINPUTS') and sample_number in (select sample_number from transfer.new_sample where x_expedition='${EOX}');" >>${KSH_SCRIPT}
echo "commit;" >>${KSH_SCRIPT}
echo "update transfer.new_test set group_name='${GRP_NM}' where group_name='DEFAULT' and analysis not in ('BHA','BIT','DEPLETED','DRILLING','LATLONG','ORIGDEPTH','ROUTING','SCALINPUTS') and sample_number in (select sample_number from transfer.new_sample where x_expedition='${EOX}');" >>${KSH_SCRIPT}
echo "commit;" >>${KSH_SCRIPT}
echo "update transfer.new_result set group_name='DEFAULT' where rowid in (select t.rowid from transfer.new_sample s, transfer.new_result t where s.sample_number=t.sample_number and s.x_expedition in ('QAQC','999'));" >>${KSH_SCRIPT}
echo "commit;" >>${KSH_SCRIPT}
echo "update transfer.new_test set group_name='DEFAULT' where rowid in (select t.rowid from transfer.new_sample s, transfer.new_test t where s.sample_number=t.sample_number and s.x_expedition in ('QAQC','999'));" >>${KSH_SCRIPT}
echo "commit;" >>${KSH_SCRIPT}
echo "select 'REVIEW CHANGED ROWS' from dual;" >>${KSH_SCRIPT}
echo "select count(*), r.group_name, s.x_expedition from transfer.new_sample s, transfer.new_result r where s.sample_number=r.sample_number group by r.group_name, s.x_expedition;" >>${KSH_SCRIPT}
echo "select count(*), t.group_name, s.x_expedition from transfer.new_sample s, transfer.new_test t where s.sample_number=t.sample_number group by t.group_name, s.x_expedition;" >>${KSH_SCRIPT}
echo "exit;" >>${KSH_SCRIPT}
echo "+++" >>${KSH_SCRIPT}
chmod 777 ${KSH_SCRIPT}
echo "Completed at `date`"
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.
The result table took 2-5 minutes to complete (with indexes in place) for each of ORTLIMS, LIMS19, RTLIMS, and LIMSHQ.
sql -s / as sysdba <<+++
alter session set current_schema=transfer;
set echo on
set pagesize 0
set linesize 132
set timing on
set heading off
spool in_moratorium_GROUPNAME.log
update transfer.new_result set group_name='GROUPNAME' where group_name='DEFAULT' and analysis not in ('BHA','BIT','DEPLETED','DRILLING','LATLONG','ORIGDEPTH','ROUTING','SCALINPUTS') and sample_number in (select sample_number from transfer.new_sample where x_expedition='###');
commit;
update transfer.new_test set group_name='GROUPNAME' where group_name='DEFAULT' and analysis not in ('BHA','BIT','DEPLETED','DRILLING','LATLONG','ORIGDEPTH','ROUTING','SCALINPUTS') and sample_number in (select sample_number from transfer.new_sample where x_expedition='###');
commit;
update transfer.new_result set group_name='DEFAULT' where rowid in (select t.rowid from transfer.new_sample s, transfer.new_result t where s.sample_number=t.sample_number and s.x_expedition in ('QAQC','999'));
commit;
update transfer.new_test set group_name='DEFAULT' where rowid in (select t.rowid from transfer.new_sample s, transfer.new_test t where s.sample_number=t.sample_number and s.x_expedition in ('QAQC','999'));
commit;
select 'REVIEW CHANGED ROWS' from dual;
select count(*), r.group_name, s.x_expedition from transfer.new_sample s, transfer.new_result r where s.sample_number=r.sample_number group by r.group_name, s.x_expedition;
select count(*), t.group_name, s.x_expedition from transfer.new_sample s, transfer.new_test t where s.sample_number=t.sample_number group by t.group_name, s.x_expedition;
exit;
+++
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 COMPLEX_PASSWORD requirements. If the EPM has provided a credential set, use that.
Run the script via SQL.
Provide an explicit path to the script to run it.
Pass the expedition as a parameter to the script. The pattern to follow based on expedition 398 activity
sql / as sysdba
SQL>@/backup/scripts/rci_work/exp/crt_user_398.sql 398
Note that this script may be run at any time. It is free of any dependencies on other scripts here.
This credential needs to be distributed to the following email distributions for filing: EPM for the expedition in processing, it_ops@iodp.tamu.edu, programmers@iodp.tamu.edu, database@iodp.tamu.edu (Data Librarian), webmaster@iodp.tamu.edu
set echo on
create user exp&1 identified by "secret-secure" default tablespace labware profile COMPLEX_PASSWORD;
grant connect to exp&1;
create role "GROUPNAME";
grant "GROUPNAME" to exp&1;
exit;
4. Copy the content
Into the publicly accessible LIMS. - DBA
chk_tablespace_free.ksh
Review the space available in LABWARE, LABWAREIDX.
DT=`date +"%Y%m%d_%H%M%S"`
OUT=chk_tablespace_free_${DT}.txt
TMP=chk_tablespace_free_${DT}.tmp
sqlplus -s "/ as sysdba" 2>/dev/null 1>$TMP <<+++
set newpage 0
set linesize 150
set pagesize 0
set echo off
set feedback off
set tab off
column TABLESPACE_NAME FORMAT A15 HEADING 'Tablespace';
column file_name FORMAT A45 HEADING 'File Name ';
COLUMN allocated_mb FORMAT 999,999,999,999 HEADING 'Tablespace|Pieces';
cOLUMN free_mb FORMAT 999,999,999,999 HEADING 'Free|Mbytes';
cOLUMN free_mb_per FORMAT 999.99 HEADING 'Free ';
SELECT
a.tablespace_name,
a.file_name,
(a.bytes/1024) allocated_mb,
(b.free_bytes/1024) free_mb,
(b.free_bytes/a.bytes)*100 free_mb_per
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;
exit;
+++
grep -v "^$" $TMP >$OUT
rm $TMP
egrep "LABWARE |LABWAREIDX |UNDOTBS1|TRANSFER|TEMP" $OUT
#cat $OUT
grp_cnt_transfer.ksh, grp_cnt_lims.ksh
Review. Run both of these scripts to gather baseline row counts by moratorium group, expedition, and record status. Preparatory step before data is merged.
DT=`date +"%Y%m%d_%H%M%S"`
OUT=grp_cnt_transfer_${DT}.txt
sql -s "/ as sysdba" 2>>$OUT 1>>$OUT <<+++
alter session set current_schema=transfer;
--connect transfer/HQtransfer:dbashare.hhhhh;
set newpage 0
set linesize 100
set pagesize 0
set echo on
set timing on
set time on
set feedback off
set heading off
set tab off
select 'REVIEW TRANSFER.TEST' from dual;
select t.group_name, s.x_expedition, t.status, count(*) from transfer.new_sample s, transfer.new_test t where s.sample_number=t.sample_number group by t.group_name, s.x_expedition, t.status order by s.x_expedition, t.group_name, t.status;
select 'REVIEW TRANSFER.RESULT' from dual;
select r.group_name, s.x_expedition, r.status, count(*) from transfer.new_sample s, transfer.new_result r where s.sample_number=r.sample_number group by r.group_name, s.x_expedition, r.status order by s.x_expedition, r.group_name, r.status;
exit;
+++
DT=`date +"%Y%m%d_%H%M%S"`
OUT=grp_cnt_lims_${DT}.txt
sql -s "/ as sysdba" 2>>$OUT 1>>$OUT <<+++
alter session set current_schema=lims;
set newpage 0
set linesize 100
set pagesize 0
set timing on
set time on
set feedback off
set heading off
set tab off
select 'REVIEW LIMS.TEST' from dual;
select t.group_name, s.x_expedition, t.status, count(*) from lims.sample s, lims.test t where s.sample_number=t.sample_number and s.x_expedition in ('${EOX}', 'QAQC', '999') group by t.group_name, s.x_expedition, t.status order by s.x_expedition, t.group_name, t.status;
select 'REVIEW LIMS.RESULT' from dual;
select r.group_name, s.x_expedition, r.status, count(*) from lims.sample s, lims.result r where s.sample_number=r.sample_number and s.x_expedition in ('${EOX}', 'QAQC', '999') group by r.group_name, s.x_expedition, r.status order by s.x_expedition, r.group_name, r.status;
exit;
+++
data_pre_03_dis_trig.ksh
Run this script to generate a pair of scripts that respectively enable and disable triggers.
Future: Remove this script by applying the use of "execute immediate" support for disabling respective triggers automatically in the TRANSFER.EOX_SHORE_MERGE_TRANSFER_NEW_TO_LIMS() script--around LIMS.SAMPLE, LIMS.TEST, LIMS.RESULT.
The LIMS.GEOD_DATA trigger should not be disabled. No need. Its a primary key trigger only. Data being merged (should) never have an empty primary key.
DT=`date +"%Y%m%d_%H%M%S"`
sqlplus -s "/ as sysdba" 2>/dev/null 1>/dev/null <<+++
set newpage 0
set linesize 100
set pagesize 0
set echo off
set feedback off
set heading off
set tab off
spool dis_trig_all.sql
set pagesize 50000
set linesize 160
set trimspool on
set trimout on
select 'alter trigger '||owner||'.'||trigger_name||' disable;' from all_triggers
where owner in ('LIMS') and table_name in ('SAMPLE')
order by owner;
spool off
exit;
+++
sed 's/ disable;/ enable;/g' < dis_trig_all.sql >enb_trig_all.sql
mv dis_trig_all.sql dis_trig_all_${DT}.sql
mv enb_trig_all.sql enb_trig_all_${DT}.sql
dis_trig_all_<yyyymmdd_hhmmss>.sql
Run this script via SQL to disable all schema triggers.
- Ensures the data goes in faster. And
- Doesn't get modified (again) on the way by.
Only the triggers shown should be disabled. All others are primary key generators which have no effect for data merge.
alter trigger LIMS.TRG_NAME_CHANGE_AI disable;
alter trigger LIMS.TRG_NAME_CHANGE_AIFER disable;
alter trigger LIMS.TRG_NAME_CHANGE_BEFORE disable;
alter trigger LIMS.TRG_NO_SAMPLE_NAME_BEFORE disable;
alter trigger LIMS.MAINTAIN_ORIG_LEN disable;
data_pre_05_without_DI_v6.ksh
Script deprecated--not modified for current use. Superseded by the script below.
call transfer.eox_shore_merge_transfer_new_to_lims()
This procedure merges TRANSFER schema NEW_* table content into LIMS. Including
- scales, depth, samples, tests, results; and
- two (recently added) GEODESC tables.
Please review the procedure. To run it
sql / as sysdba
SQL>
call transfer.eox_shore_merge_transfer_new_to_lims()
Noted
- Without a unique result_number index, the bulk merging of the NEW_RESULT content took upwards of 6 hours.
- With a unique index on NEW_RESULT.RESULT_NUMBER the bulk merge was observed to complete in 4 hours (this for ~26 million rows).
- Although the data is available, the script does not provide for the merge of DESCINFO2 data--system is no longer used, so omitting now.
Future: The scripts are constructed around MERGE. Test in LIMS19 or RTLIMS to see if this technique is more performant for the bulk data moves. Since RESULT is the largest table with the most indexes we can gain the most improvement in execution times by optimizing this table's activity.
execute immediate "alter table lims.result nologging;"
insert /*+append*/ lims.result
select * from transfer.new_result where result_number in
(select result_number from transfer.new_result
minus
select result_number from lims.result
);
execute immediate "alter table lims.result logging;"
commit;
grp_cnt_lims.ksh
Re-run this script for review purposes. Data formerly only in the TRANSFER schema should now be present in the same numbers in the LIMS schema.
enb_trig_all_<yyyymmdd_hhmmss>.sql
If the row counts match, re-enable operational triggers.
Reiteration: no need to disable/enable the LIMS.GEOD_DATA primary key trigger. Content being merged already has keys.
alter trigger LIMS.TRG_NAME_CHANGE_AI enable;
alter trigger LIMS.TRG_NAME_CHANGE_AIFER enable;
alter trigger LIMS.TRG_NAME_CHANGE_BEFORE enable;
alter trigger LIMS.TRG_NO_SAMPLE_NAME_BEFORE enable;
alter trigger LIMS.MAINTAIN_ORIG_LEN enable;
5. Update the Database Overview
Configuration updates provide summary of the expedition data for participants and future users. - DBA
dbo_compute_v5.sql <exp>
Deprecated. While still functional, prefer the use of the script below.
call transfer.dbo_compute_exp('###')
Run this script to compute new expedition summaries for display in the database OVERVIEW application. Substitute the desired expedition number.
sql / as sysdba
SQL>
set serveroutput on
call transfer.dbo_compute_exp('398');
Upon completion, review the database overview application from a browser: : https://web.iodp.tamu.edu/OVERVIEW/
6. Test
Spot-check and test availability and completeness.
Confirm that moratorium access is functioning. - TAS, DEV
In working ORTLIMS, LIMS19, RTLIMS, and LIMSHQ needed to run Auther application to assign the MORATORIUM_USER role to the moratorium user.
Ensure that GEODP### data is available. - DEV
Oracle grants and Auther configurations are not managed by this EOX process. Likely need to check both for full access to this legacy data. The GEODESC project schemas in theory (not yet practiced) may be expunged after post-cruise and the moratorium period is complete.
7. Notify interested parties
Notify interested parties that new moratorium data is available in LIMS
- EPM
- DBA - RCI
- Data Librarian
- Web Master
- Programmers
- IT OPS
Original process notes below.
Re-worked below into process above--polishing and efficiency improvements welcome.
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
|