...

Make sure we are getting as complete a copy of the data 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

...

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).

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

. oraenv
ORACLE_SID = [oracleLIMSJR1] ? LIMSJR<Enter>

sql your-nameyou_dba

sql>
alter tablespace labware read only;
exit

[oracle]$

...

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

Make the database read-write again

sql your-nameyou_dba
Password:

sql>
alter tablespace labware read write;
exit

[oracle]$

Test the export file

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

• Are there any errors? No is good. If presenterrors, find cause, maybe re-rrunrun.
• 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.

...

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.L

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

Note 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.

...

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.

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.

...

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/export';
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.

...

4. Restore database read/write capability.

From the prompt [oracle@k1 ~]$

run these command, supply the appropriate credential

Stop. Leave this window open for use below.

5. Compress the exports for transport. Cleanup. 

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

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.

...

6. 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.

The MCS pick up the full export from  oemjr:/backup/export.

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

...

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

...

.