Versions Compared

Old Version 2

changes.mady.by.user Unknown User (morgan)

Saved on

compared with

New Version Current

changes.mady.by.user Unknown User (fackler)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

BOX - beginning of expedition
EOX - end of expedition

The outgoing programmer(s) is(are) responsible for all of the end-of-expedition (EOX) activities.

...

This expedition is ending.

Make sure we are getting as complete a copy of the data

...

Please read this entire document and its companion. Expect to take 3 to 4 hours to complete this exercise. (I keep saying this only takes 15 minutes. But that really is just the individual database dump processes.) Do not hurry. If you are "fried"--work together. 

0. Readiness assessment.

to be warehoused on shore as we can.

This document does not recapitulate all items on the checklist. If an item is in need of elaboration, please add to it.

0. Negotiate a time window

Work with the LO, ALOs, EPM, MCS, laboratory Technical Staff. Three weeks before the end of the expedition you should have an (increasingly clear) idea how and when these activities will transpire.

  • When will the last core be on deck?
  • When will we be in port?
  • When do we need to be done collecting data for a given laboratory

...

  • ?
  • Has all the data content that

...

  • is required to be warehoused been stored?
      uploaded to the database
      copied to data1 OR
      copied to

...

  • uservol (workspace toward creation of EXPEDITION PROCEEDINGS

...

  • )
  • Are you aware of the varieties of data that have been collected?
    A review of data1 is helpful
    Though we presently do not fill this out, a data disposition spreadsheet/

...

  • form is available in s:\data1

...

  • that  exercises this concern.

If

...

not done, obtain estimates when

...

1. Notify everyone. Database snapshot in progress.

  • Notify jr_developer and the shipboard technical staff that the EOX database backup is in progress.
  • The database is available for reports, but content cannot be changed while this activity is in progress.

2. Make the database read only.

...

. oraenv
ORACLE_SID = [LIMSJR] ? LIMSJR
sqlplus your-name_dba
sql>
alter tablespace labware read only;

At the SQL> prompt, run the last command.
Stop. Leave this window open for use below.

Technical note. This command prevents write access to any table content that resides in the storage facility under the "labware" table space. In our database implementation, this happens to be all tables owned by the LIMS schema. Use of Drill Report ("ops" schema) is not affected. Aspects of DescLogik software configuration are also still open to maintenance, though use of DescLogik itself is locked out by this change.

3. Full snapshot as oracle@k1.

...

NOTE: While performing the EOX for expedition 384, it was discovered that the path represented by "data_pump_dir" did not exist.  You can discover what the data_pump_dir is supposed to be using this query (logged in as your DBA account):

select * from dba_directories;

The results have a lot of stuff that's not useful, but the DATA_PUMP_DIR should be there.  Currently it is /backup/LIMSJR/dpdump.  Note that "backup" is singular (it used to be plural) and there is apparently some desire to change this, so keep that in mind.  If you find that the folder does not exist, the command in this step will fail and you may need to get an MCS or a DBA on shore to help create it.

You can also try:

create directory data_pump_dir as '/backup/LIMSJR/dpdump';
grant read, write on directory data_pump_dir to transfer, system;

10/5/2021: Folder did not exist, as above, but MCS suggested I try creating it myself (in Putty, logged in as Oracle) using mkdir commands.  That initially seemed to work, but it broke some kind of symbolic or virtual link that caused the file to be written to the wrong physical location (with reduced disk space), so not recommended.

. oraenv
ORACLE_SID = [LIMSJR] ? LIMSJR
# Note that the folder you're cd-ing to here should be the "data_pump_dir" referenced in the next command.  This comes from the dba_directories table (see note above).
cd /backup/LIMSJR/dpdump
time expdp YOUR_DBA_ACCOUNT directory=data_pump_dir full=y logfile=limsjr_full-YOUR_EXP_NUMBER-export.log dumpfile=limsjr_full-YOUR_EXP_NUMBER-YOUR_INITIALS.dmpdp

...

:

  • routine core flow will be complete
  • data is uploaded and copied to data1
  • routine sampling will be complete

Pacing items are typically XRD (long powder prep times), MAD (24-hour oven drying time), SRM (typically last track run), some chemistry processes have lengthy sample prep and run procedures.

It is Ok to ask repeatedly. It is an exercise in planning and preparation which helps us collectively focus and prioritize the tasks to be done.

1. Deliver the EOX tech report

Deliver the end-of-expedition technical report to the (place in Confluence specified by the) LO.
Please post a PDF export of it to jrso-developer on Slack.

2. Establish when the final database snapshot will be taken

Recapitulation of 0. Continue talking to the LOs, ALOs, EPM, MCS, and laboratory Technical Staff to clarify this timing.

  • It takes 20 minutes to run a full database export (for 61 GiB of data)--but we want it to be as complete as possible within the constraints of expedition activities.
  • The MCS routinely stage expedition content for convenience of copying via Science Office and User Room workstations. Timing of that activity is a common communication at the end of the expedition.

3. Spot check data copy efforts

Recapitulation of 0, 2. Be an additional set of eyes and ears.

What data didn’t get into the database? What remains to be staged on data1 or uservol?
As-needed, provide support to get data in or find an appropriate staging location for it. 

  • Spot check instrument host data folders. Are they getting over to data1?
  • If technical staff are new--are they aware of the scope of concerns for getting data back home?
  • If technical staff are old hands--learn about what they do and why.

Often there is both expedition-specific and individual variance. Much room for improvement in the current process.

4. Honor moratorium

Once content is uploaded to LIMS, and raw files have gone to data1, and routine backup cycles have occurred: expedition-specific data may (in-general) be cleaned off of instrument hosts and workstations.

These activities are the purview of laboratory technical staff. Often assistance is welcome.

There is precedent for this activity to be pushed to the (fresh) oncoming crew. Totally depending on timing of last core on deck and timing of arrival in port.

5. Conduct EOX database backup

Make the database read only

Run from PuTTY, Windows Terminal, Powershell, Hyperterminal.
You can run this step from SQL Developer too, but you'll need the command-line at the ODA for the data pump export command. If the oraenv script does not show LIMSJR1 as the default, contact the DBA (Sunil).

Code Block
ssh oracle@k1.ship.iodp.tamu.edu

. oraenv
ORACLE_SID = [LIMSJR1] ? <Enter>

sql you_dba

sql>
alter tablespace labware read only;
exit

[oracle]$

Export the database content

Code Block
cd /backup/export
ls -l
time expdp you_dba directory=dmpdir full=y logfile=limsjr_full-###-export.log dumpfile=limsjr_full-###.dmpdp

Make the database read-write again

Code Block
sql you_dba
Password:

sql>
alter tablespace labware read write;
exit

[oracle]$

Test the export file

Read the limsjr_full-###-export.log in k1:/backup/export.

  • Are there any errors? No is good. If errors, find cause, maybe re-run.
  • Review the number of samples for LIMS.SAMPLE, LIMS.TEST, LIMS.RESULT, OPS.RIS_DATA.

Log into the SHIPTEST instance. Run these commands to confirm ability to restore content from the file generated.

Code Block
ssh oracle@k2.ship.iodp.tamu.edu

. oraenv
ORACLE_SID = [SHIPTEST] ? <Enter>
[oracle]$

time impdp you_shiptest_dba directory=dmpdir logfile=exp###-test-export.log dumpfile=limsjr_full-###.dmpdp tables=lims.sample,lims.test remap_schema=lims:transfer remap_table=lims.sample:xxx_sample,lims.test:xxx_test exclude=trigger table_exists_action=replace

time impdp you_shiptest_dba directory=dmpdir logfile=exp###-test-export.log dumpfile=limsjr_full-###.dmpdp schemas=geodp### remap_schema=geodp###:geodp###_review

Use SQL Developer to review the content of the SHIPTEST TRANSFER.XXX_SAMPLE, XXX_TEST tables. Repeat for the restored GEODP###_REVIEW schema. Do not cleanup. Leave it for the oncoming crew to review.

Notify everyone that its done

Post the export log file on Slack jrso-developer. Note the size of the export file.

We will not zip it, nor send it over the wire. If needed to achieve earlier emplacement of content on shore, the person tagged with conducting the EOX Shore data processing will conduct those activities.

Email it to

  • MCS - jr_mcs@ship.iodp.tamu.edu
  • IT_OPS - it_ops@iodp.tamu.edu
  • Data Librarian - database@iodp.tamu.edu
  • programmers - programmers@iodp.tamu.edu
  • DBAs - sunil@rajaconsultancy.com, murli@rajaconsultancy.com

In the email, note the size of the file and its location--if we're putting it in the right place, there's no need for the MCS to move it.

4. Clear out transfer schema content

From the command prompt i[oracle@k1 ~]$
Run sqlplus, login as the TRANSFER USER

sqlplus transfer

Run these commands at the SQL> prompt. Use your expedition.

spool eox_YOUR_EXP_NUMBER.log
set serveroutput on
set timing on
set pagesize 0
set head on
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
call cleanout_new_tables();

...

5. Snapshot subset of database content to go home. (Using transfer id)

...

call asman_catalog_to_new('EXP351');
call asman_catalog_to_new('DROPS');
call asman_catalog_to_new('DESCINFO');
call descinfo_to_new();

NOTE: Generally, you only need to do this for your expedition and "QAQC".  The middle lines below are required only when material from a previous expedition is studied on the current expedition.
Copy LIMS data to the transfer schema. Use entries like the middle ones only when material from a previous expedition is studied on the current expedition.

call lims_expedition_to_new('351');
call lims_expedition_to_new('195');
call lims_expedition_to_new('125');
call lims_expedition_to_new('60');
call lims_expedition_to_new('QAQC');

Leave this session open. It is used again in the next step.

Statistics. 344S: ~20 min. 345: 10 min for all steps.
396: ~22 min.

6. Count the records backed up. Row-counts are expected to be identical between source tables and transfer tables. 

At the SQL> prompt, run these commands

NOTE: Generally, you only need your expedition number and "QAQC".  There may be others if your expedition included data and/or material from other expedition(s).

select x_expedition, count(*) from transfer.new_sample where x_expedition in ('351', '195', '125', '60', 'QAQC') group by x_expedition order by x_expedition;
select x_expedition, count(*) from lims.sample         where x_expedition in ('351', '195', '125', '60', 'QAQC') group by x_expedition order by x_expedition;
 
select count(*) from transfer.new_test where sample_number in (select sample_number from lims.sample where x_expedition in ('351', '195', '125', '60', 'QAQC'));
select count(*) from lims.test         where sample_number in (select sample_number from lims.sample where x_expedition in ('351', '195', '125', '60', 'QAQC'));
 
select count(*) from transfer.new_result where sample_number in (select sample_number from lims.sample where x_expedition in ('351', '195', '125', '60', 'QAQC'));
select count(*) from lims.result         where sample_number in (select sample_number from lims.sample where x_expedition in ('351', '195', '125', '60', 'QAQC'));
 
select catalog, count(*) from transfer.new_files group by catalog order by catalog;
select catalog, count(*) from lims.files         group by catalog order by catalog;
 
select count(*) from transfer.new_softwareconfig;
select count(*) from descinfo2.softwareconfig;
 
select count(*) from transfer.new_valuelists;
select count(*) from descinfo2.valuelists;
 
spool off
exit

...

To review the log, switch to the other terminal session.
Supply these commands.

cd /backup/LIMSJR/dpdump
less eox_YOUR_EXP_NUMBER.log

Press 'q' to quit reviewing the log, or Ctrl+C.
Leave the console window open for later use.
Place the EOX database snapshot log in the R:\AD\support current expedition EOX directory,
Example from a DOS prompt on the Build Box.

# Log into Novell.
r:
cd \AD\support
pscp oracle@k1.ship.iodp.tamu.edu:/backup/LIMSJR/dpdump/eox*.log .
# that is a SPACE PERIOD at the end of the above line
go to the Novell directory and create an expedition specific sub directory and move the log there.

7. Make a transfer-only snapshot as oracle@k1.

...

# Note that the folder referenced in the cd command is the DATA_PUMP_DIR (see note at the top of this page about that)
cd /backup/LIMSJR/dpdump
# this next command is a single line
time expdp transfer directory=data_pump_dir schemas=transfer logfile=limsjr_transfer-YOUR_EXP_NUMBER-export.log dumpfile=limsjr_transfer-YOUR_EXP_NUMBER-YOUR_INITIALS.dmpdp

Statistics. 344S: 3 min 13 sec. 345: 4 min 23 sec.

8. Test the export files. Did you extract what you expected?

This test verifies that some content can be read from the content exported above. Increases confidence that the exported content is good.
From the prompt [oracle@k1 ~]$
run these commands - using transfer logon

# this command is all one line with a space between the sections
time impdp transfer directory=data_pump_dir tables=lims.sample remap_tablespace=labware:transfer remap_schema=lims:transfer remap_table=sample:verify_full_sample exclude=GRANT exclude=INDEX exclude=CONSTRAINT exclude=STATISTICS exclude=TRIGGER dumpfile=limsjr_full-YOUR_EXP_NUMBER-YOUR_INITIALS.dmpdp
 
# this command is all one line
time impdp transfer directory=data_pump_dir tables=transfer.new_sample remap_table=new_sample:verify_xfer_sample exclude=INDEX exclude=CONSTRAINT exclude=STATISTICS dumpfile=limsjr_transfer-YOUR_EXP_NUMBER-YOUR_INITIALS.dmpdp

...

...
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TRANSFER"."VERIFY_FULL_SAMPLE"              176.0 MB  560884 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
...

Login to the transfer schema. Look at tables verify_full_sample, verify_xfer_sample. They are expected to contain the expedition data you just exported.
Remove the verification tables.

drop table transfer.verify_full_sample purge;
drop table transfer.verify_xfer_sample purge;

Statistics. 344S: 3 min 25 sec. 345: 3 min 43 sec. 351: full 1 min 25 sec; xfer 24 sec. For the integrity check on each export.

9. Restore database read/write capability.

From the prompt [oracle@k1 ~]$

run these command, supply the appropriate credential

sqlplus YOUR_DBA_ACCOUNT
sql>
alter tablespace labware read write;
exit

Stop. Leave this window open for use below.

10. Compress the exports for transport. Cleanup. 

From the prompt [oracle@k1 ~]$
run these commands

cd /backup/LIMSJR/dpdump
time bzip2 --best limsjr*YOUR_EXP_NUMBER*.dmpdp
# once the above completes, then run this
ls -l
md5sum *.bz2

Cut and paste the digest keys and file listing into a notification email. Enables remote users to verify the integrity of the files.

           Please log these in a txt file in the \AD\support\'expedition named sub directory'\ see other expeditions for example.

Statistics. 367: 22 min to compress both files (~5 GiB to < 1 GiB); 345: 22 min 53 sec for bzip step. 351: full 137 min 46 for ~8 GiB content.  360: 67 min 27 sec for ~5 GiB file. 45-60 min for high recovery expeditions.

Exp 396: we had trouble initially because we ran out of disk space.  This was apparently due to my creating the /backup/LIMSJR/dpdump folder under the Oracle account.  This broke some kind of link-up that gives the backup folder lots of additional drive space.  Don't do that.

11. Notify everyone. Database snapshot process is complete.

 Notify jr_developer and expedition technical staff that the process is done.

Speak with the MCS. They will pick up the data from here.

k1.ship.iodp.tamu.edu

oemjr:/backup/

LIMSJR/dpdump

export
    limsjr*.

bz2

dmpdp
    limsjr*.log


The MCS will copy the files over to oem.ship.iodp.tamu.edu:/mediapick up the full export from  oemjr:/backup/export--one of several staging areas for the EOX tape run.

Inquire Enquire with the MCS. Ensure the (above) database content and the (below) ASMAN content are being taken to media for transport to HQ.

olympus:/asman/catalogs
olympus:/asman/liveimages

12. Send the transfer content home. 

NOTE:   

Please transfer only the limjr_transfer file online. The full file can come on tape.

Rakesh

This is now routine. The Database Group expects this to occur. This method makes the data available a week earlier than can be achieved transporting tapes.

...

  • Hand the files over to the MCS, requesting that they be transferred to \\NORTH\FTP\FromShip\EOX\  << Preferred.

...

Take a copy of the export file

Login into the Oracle account at the ODA using WinSCP. Bring down a copy of the export file and its log to

  • your laptop--or other transfer device; and
  • stage a copy in \\novarupta\vol3\dml\oracle-data\
    for future local reference OR until we have to clean/preen for space.

6. Provide courier services

The backup tapes created by the MCS return to HQ as soon as possible. If your travel itinerary supports that goal, let them know you can provide courier services

...

.