BOX - beginning of expedition
EOX - end of expedition
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.
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.
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/
OEMJR:/backup/eox/
. TBD--confirm location and match in DEV and MCS SOPs.OEM:/backup/eox/
.There should be one data dump file and an accompanying log of the export activity.
limsjr_full-398.dmpdp
limsjr_schemas-398.dmpdp
For the shore EOX work, the filename is typically shortened to the expedition designation, e.g. 398.dmpdp
.
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.
OEM:/backup/scripts/rci_work/exp/
Please create a subdirectory for expedition-specific outputs.Other pre-requisites.
As the OS oracle user specify which database instance you are using with this syntax
. oraenv ORACLE_SID = [oracle] ? LIMSHQ (or RTLIMS or LIMS19) |
From that command-line, the following shell and SQL scripts (bold header) are run. In the order shown.
When executed, this script
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.
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 # OPS #TABLES=OPS.RIS_DATA #REMAP_SCHEMA=OPS:TRANSFER #REMAP_TABLE=OPS.RIS_DATA:NEW_RIS_DATA |
For the new expedition data. - DBA
The credential is selected by the Director of the Staff Scientist group. Enquire with Leah Levay--or see the password safe for entries provided for FY2024. The moratorium name follows this pattern: EXP###--examples EXP399, EXP400T, EXP398, EXP398P, etc.
The account is shared by a science party for the moratorium period (typically 18 months)--it enables specific support for SDRM; it enables access to data in moratorium within the web-based LORE reporting system.
The moratorium name is generally selected and specified by the operator of this process.
This script accepts a moratorium name, e.g. SHACKLETON, SANTORINI, etc. Restrictions on that name are listed below.
The script (below) is generated by the above. It writes the SQL to specifically modify NEW_TEST, NEW_RESULT, and NEW_GEOD_DATA tables for moratorium concerns.
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`" |
Running this script
eox_gen_in_moratorium_script.ksh
.Upon running the script--wait.
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; +++ |
Review the script.
crt_user_398.sql
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 LONG_PASSWORD; grant connect to exp&1; create role "GROUPNAME"; grant "GROUPNAME" to exp&1; exit; |
The above script must be run for the test database instance selected, and for the production instance to which the data is merged.
Into the publicly accessible LIMS. - DBA
The data is first imported into a test database instance in the transfer schema. After the data has been remediated to support moratorium concerns, a snapshot of this data may be exported for re-use. The export will be re-imported into the production database.
sql my_dba@instance dp export -directory dmpdir -dumpfile ###-transfer.dmpdp -schemas transfer -filesize 15gb |
After completing the above export from the selected test instance, complete these commands at the production instance (LIMSHQ).
sql my_dba@limshq call transfer.util_cleanout_transfer_new_tables(); dp import -directory dmpdir -dumpfile ###-transfer.dmpdp -schemas transfer -nameexpr table="like 'NEW__%'" -includemetadata false -includerows true |
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 |
Run these scripts to obtain row counts by moratorium group, expedition, and record status after the moratorium remediation is complete. Preparatory step before data is merged. The output of these scripts are text files. Review them. File the info in the expedition-specific directory.
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; +++ |
This procedure merges TRANSFER schema NEW_* table content into LIMS. Including
Please review the procedure. To run it
sql / as sysdba SQL> call transfer.eox_shore_merge_transfer_new_to_lims() |
Noted
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.
Configuration updates provide summary of the expedition data for participants and future users. - DBA
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/
A new column of summary statistics should be visible for the new expedition.
DEV - The moratorium user must be modified within the https://web.iodp.tamu.edu/Auther/ application.
DEV - When moratorium and all major sampling parties are complete for this data, the Auther configurations may be removed. This tends to lag behind moving data out of moratorium.
Spot-check and test availability and completeness.
Notify interested parties that new moratorium data is available in LIMS
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
|