The on-coming developers are responsible for these processes

...

!

The intent of these processes is to establish database configurations for a new expedition. Only a small portion of this content is needed for any one expedition. Additional scenarios and combinations of data handling are considered.

Process  ----> DO THIS ON THE DEV BOX via a shared account like SHIP\DAQ

• Because it may take a long time and you don't want to tie up your laptop.
• Because your opposite may need to check in on progress if it runs longer than your shift.

If the off-going developers want to help and time permits--that's wonderful. It's a Catch-22 that probably indicates you need some rest.

Connection Notes

Log into operating system oracle user first; then conduct database user logins.

• Open a SQL Developer session.
• Connect to oracle@k1.ship.iodp.tamu.edu.
• From the prompt k1$ run the commands shown.
• Supply the appropriate credentials.

NOTE: For command-line tools--like sqlplus, rman→ if your password has spaces or symbol characters, you must quote the password, e.g.

...

> sqlplus x@yz
Password:"@ s3cr3t fr45e"

PuTTY window

...

[oracle@k1 ~]$ . oraenv
ORACLE_SID = [LIMSJR_HA] ? LIMSJR_HA
cd /backup/LIMSJR/dpdump
sqlplus your-name_dba - Used for Monitoring Space and Logs

Run the SQL scripts

Connect to Oracle as the TRANSFER schema owner. Recommend using sqlplus. Make appropriate substitutions for current expeditions. If you are new to the process, read the footnotes carefully. Ask other developers or DB's.

NOTE participants_remove.sql found in SVN under - https://build.ship.iodp.tamu.edu/svn/wapps/Tools/DbScript/participants needs to be run to remove all the old JRS_ scientist user id's.  Need to be run under a DBA Privileged account in SQL Developer.  The process will remove user ID's and will also remove dependent user configuration from the X_AUTH_ACCOUNT table as well.

Logging the effort quantifies the experience for future improvements. Please place the log file in an expedition folder under r:\ad\support\###...\box\

• The box_lims_expedition_delete() step is the longest. Monitoring is required.
• If material from another expedition was analyzed on this expedition, it may be necessary to delete that content too, e.g. call box_lims_expedition_delete('344(312)');

1. VARIANCE: If you need to keep some data from the previous expedition for the next expedition, skip this step. Even if you forget, you can still copy what you need from TRANSFER.NEW_tables without having to resort to the data pump import tool.
2. VARIANCE: If you need to keep some data from the previous expedition for reference on the next expedition, then custom SQL is required to preserve what you need. And you must communicate with the MCS to ensure what you need is preserved on the ASMAN file system--their routine is to expunge the previous expedition.
3. VARIANCE: If multiple expeditions need to be cleaned, repeat the command for each expedition.
4. You must look up and specify ###. The script as written deletes all audit keys <= ### from x_lime_audit_main. The deletion cascades to linked audit detail.
5. Defragmentation of the space used by SAMPLE, TEST, RESULT is recommended, but not required if you are pressed for time. "Coalescing" of the RESULT table alone typically takes 9 hours in the shipboard environment. Doing so on a warehouse does take days with spinning disk.

Statistics

• BOX lims_expedition_delete() for
  
  • 362: 4 hr 49 min 50 sec. One archive log stuck excursion. Fixed by the backup window we happened to overlap.
  • 367: 2 hrs 22 min. No archive log stuck excursion.
  • 372: 5 hr 30 min. No archive log stuck excursion.
  • 374: about 10 minutes, as 372 was light. No issues.
  • 382: 6 HOURS.  
  • 385 & 378T: 07:24:06.09 for 385 and only a couple of minutes for 378T
• BOX lims_cleanup() for
  • 372: 15 sec
  • 374: < 1 sec
  • 382: 17 seconds.
  • 385 & 378T: 24 seconds
• BOX lims_defrag() for:
  
  • 362: 9 hrs 32 min. Two archive log stuck excursions. Even with archive log allocation bumped to 1T on the second time.
  • 372: ???
  • 374: about 36 minutes.
  • 382: 4:31:59.73 total run time.  Longer than normal.
  • 375 & 378T: 05:18:51.12

Cleaning OVERVIEW Menu and Summary Data

...

delete from lims.x_dboverview where expedition in ('360', '361');
commit;

Discussion

Database Schemas

The database consists of distinct schemas. Each schema serves different functions

• DESCINFO2. Records configuration of parameters, templates, value lists, and users for the descriptive information eco-system.
• LIMS. The sample catalog and repository of experimental results against those samples. Includes a catalog of files (ASMAN) associated with those samples.
• OPS. Repository of drilling operations information. Bathymetry and navigation content was removed from here as of Subic Bay Tie-up 353P Oct 23, 2014. The content is time and activity based, not sample based. Other workflows manage the bulk of this data outside Oracle. What is recorded here is a small subset of the total operational content we keep.
• TRANSFER. Contains the scripts and tables for data transfer and cleanup processes you will be running.

Cleaning Process Architecture

End-of-expedition processing leaves data in LIMS, TRANSFER. That content may be removed. The beginning-of-expedition processing removes that content. The procedures to conduct the removal are owned by the TRANSFER schema.

An Annoyance: Monitoring for Archive Log Space Filling

Oracle treats the changes resulting from end-of-expedition processing like any other transactions. Due to the size of these transactions it is likely that the 100GiB reserved for archive logging will be consumed. When this occurs, the database blocks all activity until archive log space is freed. All attempts at new transactions will receive the message ORA-00257: archiver stuck.

This method of releasing the archiver assumes there is actually some spare space on the system. Via a DBA account--to increase the amount of archive log space available:
alter system set db_recovery_file_dest_size=1000G scope=memory;

The ODA systems carry plenty of disk. The above will get Oracle back up and running with enough breathing room for you to connect and do the following.

Monitoring Archive Log Generation

The HTTP-based Oracle Enterprise Manager (OEM) provides a page for detailed monitoring and management of archive logs. Pre-requisites for usage

• Request a starter set of credentials and permissions for OEM from the DBAs. Specify which environment you are in.
• OEM credentials are distinct from RDBMS credentials. You must have DBA level privileges and credentials to the database being managed.
• OEM and RDBMS credentials are distinct from operating system credentials. You must have access to the host oracle account for some operations.

...

(2)
Select the menu Target > Databases. Click on the link corresponding to the database you wish to manage.
Login with your DBA privileged RDBMS credential.

(3)
To simply monitor archive log usage visit this page.
On the secondary menu select  Administration > Storage > Archive Logs
Refresh the page as-needed to update the statistics.

To manage the archive logs visit this page
On the secondary menu select Availability > Backup & Recovery > Manage Current Backups
Operating system credential is required to send RMAN (recovery manager) commands.

TODO Provide more detail here as we get more practice.

• Must be logged into this page and monitoring the database before the archive logger blocks. If the archive logger is already blocked current experience indicates that OEM is not effective. Direct host login to RMAN becomes necessary.
• The OEM times out your login in about 10/15 minutes. If running out of archive log space is still a real concern with a terabyte of disk, refresh the page frequently.
• Until MCS and developers become more comfortable with the backup process, it is preferable to increase archive log space by the alter system commands above.
• The OEM provides email alerts that trigger on thresholds (e.g. percentage of archive log space filled). Notification settings are determined by the OEM manager.

More info about archive logging

Run this SQL as the DBA to verify info about archive logging status.

...

...

How to Invoke PL/SQL Scripts

All scripts are run from procedures owned by the TRANSFER account. The typical data cleanup and preening session is shown above and repeated here

...

...

...

A Cookbook and Scenarios.

The routine processes are described first. Then less common scenarios are described.

Clean the TRANSFER schema

Irrevocably and quickly drop all content in the TRANSFER schema tables with "NEW" in their names. No parameter required.

Clean out the ASMAN file catalogs(will also remove Autosaves from Descinfo2)

This script removes the database records only. It is a separate step to clean out the file system for the catalog. You are responsible for the database cleanup. The MCS take care of the file system. Talk with each other.

...

Clean out LIMS 

This is the big one. Monitor archive logs. Start it in a stable environment that you know won't be rebooted in anywhere from 6-20 hours. Check on it using SQL to count sample/test/result rows, or watch it via Monitor Sessions in SQLDeveloper. With curr

...

...

• Allow 3 hours per 10 million rows of results.
• Allow 3 hours for defragmenting and condensing space allocated to tables and indexes.

This procedure should NOT be run during routine laboratory data collection. For selected tables, it turns off triggers, and reduces archive logging.The procedure MAY be run during periods where only data reporting is being done.

Scenario: Brand new expedition, previous content being removed.

This is the most typical expedition cleanup scenario:

• One expedition of OPS data to delete.
• One expedition of ASMAN content to remove.
• One expedition of LIMS content to remove.

Scenario: Current expedition is continuation of previous.

Previous expedition content is being preserved on ship due to continuation with a new science party. Previously curated and collected sampled are for reference only. The "legacy" content should be locked down so that it is not unintentionally modified.There is no special handling for ASMAN content. You have to remember that this expedition is now legacy and remove it at the appropriate time.

Scenario: Remove request codes used by the previous expedition.

The curator manages this using the request code manager application. No developer involvement is required.

Load information being transferred from shore.

Transfer of content has been on an ad hoc basis. In general the need should be flagged before the expedition and managed as needed. Common scenarios are noted here.

Scenario: New containers to be loaded for MAD analyses.

The names and masses of these containers are presently delivered in an Excel spreadsheet via the physical properties technicians.A set of SQL scripts are available to upload the content. The spreadsheet content must be reformatted into SQL.  See example scripts in \\jr1\vol1\tas\support\{expedition}\containers.This is one of those processes that could use improved automation, but doesn't yet occur with sufficient repeatability and frequency to bother. Contact Fackler.

Scenario: Load pre-existing LIMS data

Some expeditions are continuations or extensions of previous work. For convenience they may wish to have a local copy of previous samples and analytical content. The resteasy-lims-sync services enables over-the-satellite transport of low-bandwidth content. The example shows a synchronization request that copied all SRM data from 339 to the RTIF environment on shore. If a larger set of data is being applied than is desirable via resteasy-lims-sync, Oracle’s data pump utility will be used to extract and reload the appropriate data content. Handling of high bandwidth content—e.g. ASMAN files and images—should be planned out in advance of the expedition. In these cases, required content will be transported by tape/disk and restored to the appropriate shipboard locations. Legacy data should be flagged as such to prevent overwrites if this is a concern.

Scenario: Load pre-existing Janus data

Some expeditions are continuations or extensions of previous work. For convenience, the science party may require a local copy of previous samples and analytical content.If small amounts of legacy material is brought out for re-sampling, re-analysis, it is easiest to just re-catalog the material in LIMS with SampleMaster.Legacy data are available online and are already on ship. For most purposes there is not really a requirement for the legacy Janus content to be migrated to LIMS.Should the migration requirement be real: a tool exists to do so. However, Janus2Lims is so infrequently used it will require modification to be made functional for the need. So plan ahead.

Scenario: Legacy data load scripts

Preserved for historical reference. These scripts still exist. Preferred syntax. Used in complement, bracketing work-to-be-done in the middle. The alternate syntax is robust over gradual changes over time. It ensures no triggers are missed on the tables we work with most: sample, test, result. 

...

CONTAINER LOAD. See Excel spreadsheet and SQL script method at https://build.iodp.tamu.edu/svn/wapps/Tools/containers.

LIMS_LOAD_LEGACY. Deprecated. Not used.

Cumulus. Migration of Cumulus content between ship and shore is managed by the MCS and shore systems personnel. Do make sure YOUR account still works. You may be asked to carry media. In general, redundant media, carriers, and routes are used.

Cleaning test data

For new participants and staff to experience and practice using integrated shipboard systems, it is helpful to have some data to play with. The same is required for development that continues between expeditions.
Records are de facto accumulated against an expedition/project called TEST 999. After awhile it is helpful to clean out tests and results. Be judicious and selective. Ask around: is anyone using this data? For example, sometimes end-user testing or development is in progress of which you may not have been aware. Once you have go-ahead, it is often sufficient to clean out tests and results. Unless specifically testing sample cataloging we prefer to avoid the effort of recreating a complete and representative set of samples. These clean out scripts should only be used in the scenario where samples are being preserved [curatorial tests and results], but science data [non-curatorial] tests and results are being removed.

...

Example of the above from Windows Terminal (wt.exe), Powershell, PuTTY, Hyperterminal or any other such tool.

ssh oracle@k1.ship.iodp.tamu.edu    # Connect to an ODA with a terminal session.
cd /backup/export                   # Export file and log will be placed here.
. oraenv                            # Confirm which database instance is the default
ORACLE_SID = [LIMSJR1] ?            # LIMSJR1 is the expected default. Press <Enter> for no change.
                                    # If anything else shows, contact the DBA.
                                    # The default for K2 is expected to be SHIPTEST.
sql me                              # Supply your DBA account in place of "me".
                                    # ! Quote your password if it contains spaces or symbol characters.
                                    # Example. Password:"@ s3cr3t fr45e"

Run data cleaning and preening scripts

Connect to Oracle as TRANSFER (schema owner) or your DBA account. Doing this work in SQL Developer is often convenient.

• Make appropriate substitutions for current expeditions.
• If you are new to the process, read the footnotes. Ask.

set timing on
-- GEODESC. Remove last expedition's GEODESC project.
drop user geodp### cascade;

-- LIMS. Remove last expedition's LIMS content.
-- Review TRANSFER.LOG entries to review these activities.
call transfer.util_cleanout_transfer_new_tables(); -- (1)
call transfer.box_asman_catalog_delete('EXP346'); -- (2) Last expedition
call transfer.box_lime_audit_delete(###);    -- (4)
call transfer.box_lims_expedition_delete('346');  -- (3) Last expedition
call transfer.box_lims_cleanup();
call transfer.box_lims_defrag(); -- (5)

-- OPS. Remove last expedition's IRIS data.
-- The timestamps below will surely not be correct. Review when the database export was conducted. Review the data based on a time range.
delete from ops.ris_data where observed_time <= to_timestamp('2023-08-11 13:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff');
delete from ops.ris_configuration where last_modified <= to_timestamp('2023-08-11 13:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff');
delete from ops.ris_event where event_timestamp <= to_timestamp('2023-08-11 13:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff');

The box_lims_expedition_delete() step is the longest. Monitoring is helpful. Currently takes about 30 minutes--mostly to delete millions of results. Historically saw ranges of 2-8 hours (older hardware, fewer CPUs, slower disks).

If material from another expedition was analyzed on this expedition, it may be necessary to delete that content too, e.g. call box_lims_expedition_delete('344(312)');

1. VARIANCE: If you need to keep some data from the previous expedition for the next expedition, skip this step. Even if you forget, you can still copy what you need from TRANSFER.NEW_tables without having to resort to the data pump import tool.
2. VARIANCE: If you need to keep some data from the previous expedition for reference on the next expedition, then custom SQL is required to preserve what you need. And you must communicate with the MCS to ensure what you need is preserved on the ASMAN file system--their routine is to expunge the previous expedition.

3. VARIANCE: If multiple expeditions need to be cleaned, repeat the command for each expedition.
   Run the command again if you see errors like this
   

   Code Block
   Error report - ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 32 ...

   The above error should no longer be occurring. Use of DBMS_OUTPUT has been replaced with inserts to TRANSFER.LOG for all TRANSFER procedures and some LIMS functions and procedures.
   
   Alternatively "set serveroutput off". Then use SQL like that below to monitor progress of transfer.box_lims_expedition_delete() from another terminal window or SQL Developer worksheet. Example
   

   Code Block
   select x_expedition, count(*) from lims.sample group by x_expedition order by x_expedition;

   
   

4. Look up and specify ### (lims.x_lime_audit_main.audit_key). The script as written deletes all audit_key <= ### from lims.x_lime_audit_main. The deletion cascades to linked lims.x_lime_audit_detail.
5. Defragmentation of the space used by SAMPLE, TEST, RESULT is recommended, but not required if you are pressed for time. Defragmentation on 398 took an hour to run with current ODAs and configs. Older ODAs and configs experienced 9-14 hours.

2/13/2024: Removed constraints  from the end of the drop user geodp###  command. Was causing the script to fail.

Remove SCALES not relevant to current shipboard work

Expunge the associated depth maps, affines, and splices from LIMS.

There is presently no script for this process. The SCORS application will cancel affines and splices, but not expunge the records.

The BOX_LIMS_EXPEDITION_DELETE() procedure contains a superset of this process, but must usually be copied and customized for specific need. Review the code.

Use SQL Developer to remove content not relevant to the current expedition.

INSERT INTO lims.x_sample_depth (SAMPLE_NUMBER, SCALE_ID, TOP, BOT)
    Select s.SAMPLE_NUMBER, '11311' AS SCALE_ID, 0 AS TOP, 0 AS BOT
    from lims.sample s
        LEFT OUTER JOIN lims.x_sample_depth sd
            ON sd.sample_number = s.sample_number
                and sd.scale_id = '11311'
    where s.x_expedition = '999'
--        and s.x_site = 'U9999'
--        and s.x_hole = 'A'
--        and s.x_core = '1'
        and sd.sample_number is null;
        
INSERT INTO lims.x_sample_depth (SAMPLE_NUMBER, SCALE_ID, TOP, BOT)
    Select s.SAMPLE_NUMBER, '11331' AS SCALE_ID, 0 AS TOP, 0 AS BOT
    from lims.sample s
        LEFT OUTER JOIN lims.x_sample_depth sd
            ON sd.sample_number = s.sample_number
                and sd.scale_id = '11331'
    where s.x_expedition = '999'
--        and s.x_site = 'U9999'
--        and s.x_hole = 'A'
--        and s.x_core = '1'
        and sd.sample_number is null;

INSERT INTO lims.x_sample_depth (SAMPLE_NUMBER, SCALE_ID, TOP, BOT)
    Select s.SAMPLE_NUMBER, '11311' AS SCALE_ID, 0 AS TOP, 0 AS BOT
    from lims.sample s
        LEFT OUTER JOIN lims.x_sample_depth sd
            ON sd.sample_number = s.sample_number
                and sd.scale_id = '11311'
    where s.x_expedition = 'QAQC'
--        and s.x_site = 'U9999'
--        and s.x_hole = 'A'
--        and s.x_core = '1'
        and sd.sample_number is null;
        
INSERT INTO lims.x_sample_depth (SAMPLE_NUMBER, SCALE_ID, TOP, BOT)
    Select s.SAMPLE_NUMBER, '11331' AS SCALE_ID, 0 AS TOP, 0 AS BOT
    from lims.sample s
        LEFT OUTER JOIN lims.x_sample_depth sd
            ON sd.sample_number = s.sample_number
                and sd.scale_id = '11331'
    where s.x_expedition = 'QAQC'
--        and s.x_site = 'U9999'
--        and s.x_hole = 'A'
--        and s.x_core = '1'
        and sd.sample_number is null;

Cleaning OVERVIEW Menu and Summary Data

Participants for a new expedition have no need to see either menus or data from the previous expeditions database OVERVIEW.
Menus. OVERVIEW menus are stored in LIMS.LIST_ENTRY. View the content where LIST = 'MENU'. Change old expeditions for new expeditions. Drop menus that are not needed. The ODP and DSDP menus should always be retained.
Data clean. The summary content displayed by OVERVIEW lives in LIMS.X_DBOVERVIEW. The table is indexed by expedition, site, and hole. It is sufficient to clean out specific content with a query like this (example)

Containers to be loaded for MAD analyses

See the MAD container load - process. And additional related documentation on the shore wiki. The names and masses of containers are delivered in a spreadsheet (or text file).

This is not done every expedition.