...
Statistics. Typical durations for the last step from previous expeditions. 344S: 16 min 23 sec. 345: 13 min 54 sec. 346: 18 min 46 sec. 341S: 11 min 42 sec. 351: 12 min 9 sec; 360: 16 min 57 sec; 363: 3 min 21 sec; 367: 3 min 38 sec.
ODA for 3 expeditions of data (11.1 GiB): 4 min 17 sec.
396: 6 min 54 sec
4. Clear out transfer schema content
From the command prompt i[oracle@k1 ~]$
Run sqlplus, login as the TRANSFER USER
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.
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.
...
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.
Need to test DMPDP.
Need to post log results.
11. Notify everyone. Database snapshot process is complete.
...