Versions Compared

Old Version 4

changes.mady.by.user jr_fackler

Saved on

compared with

New Version 5

changes.mady.by.user jr_fackler

Saved on

Key

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

...

Formerly these instructions were written so as to generate log files that you and your colleagues could inspect. We no longer do that in this process since Feb 2023 (Exp 398).If you are

All TRANSFER procedures have been revised record activity to TRANSFER.LOG. Provides some bread-crumbs to review past activity. Several LIMS procedures and functions are also revised to record activity to this same log.

Q: A database process is taking too long, I want another way to check what's happening.
A:

...

 It is not always effective due to database security and models of atomicity, completeness, independence, and durability. Amount of parallelism changes how Oracle manages this information too.

This command is useful to review active SQL for a list of users. Modify the username list to see active SQL for other accounts.

Code Block
select u.sid, u.serial#, s.rows_processed, s.disk_reads, s.buffer_gets, s.last_active_time, s.physical_read_by
tes, s.physical_write_bytes, s.sql_id, u.username, substr(s.sql_text,1,50) from v$sql s, v$session u where s.sql_id
=u.sql_id and u.username in ('MY_DBA','GUEST','JRS_xxx');


Q: My command-line login is failing. What are some less usual causes?
A:

...

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

sql>
archive log list


Expect output like this

Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 27612
Current log sequence 27614


or this

Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30532
Current log sequence 30760

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

call transfer.util_cleanout_transfer_new_tables();
call transfer.box_asman_catalog_delete('EXPtest');
call transfer.box_lims_expedition_delete('test');


Most scripts take a single string parameter for expedition. The ASMAN scripts take a single string parameter indicating the catalog to be deleted--usually formed as 'EXP'+'theExpeditionNumber', e.g. 'EXP339'.   Catalogs: DESCINFO and DROPS are kept per user request.

Run the scripts from SQL, SQL*Plus or from within a SQLDeveloper worksheet. SQL and SQL*Plus have the advantage of being light-weight, and provides facilities to spool all transactions that transpire for post-cleanup review and comparison. SQLDeveloper has the advantage of readily reviewing and verifying the scripts being invoked.

The scripts are designed to provide feedback, however the Oracle mechanisms are not real-time. To turn on useful feedback mechanisms, apply these commands from the SQL*PLUS command-line mileage will vary if tried in the context of SQLDeveloper:

set timing on
set serveroutput on


To capture the content of a SQL*Plus session, do something like this:

spool box_processing-340T.log
set timing on
set serveroutput on
[other stuff is run here...]
spool off


The duration of the session is very dependent on the quantity of data collected. Be patient. It takes time to delete 14-20 million rows. Current takes about 30 minutes. Not too long ago took between 9 and 20 hours.
Special behavior for scripts is noted below. E.g. order of execution is important in some cases, some processes are fast, some are slow. Some should be double-checked.

Data cleanup is routine, but will have variances. The additional scenarios provided below attempt to clarify the variances we encounter. Apply the scenario or combination of scenarios that best fits current expedition requirements.

You will see a number of procedures in TRANSFER that are not documented here. These processes have been in disuse for so long additional thought, testing, and documentation should be applied to them when they come up again. Specific questions? Ask around. Read the code.

...

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

call transfer.util_cleanout_transfer_new_tables();
commit;

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.

call transfer.box_asman_catalog_delete('EXP123');
commit;


Repeat box_asman_catalog_delete for as many catalogs as you desire to remove. Confirm removal by re-running the survey script. Commit when done. The DESCINFO and DROPS catalogs are to be preserved--specific request by OPS and DESC technicians.

select count(*), catalog from lims.files
group by catalog;

Clean out LIMS 

This is a longer process, but no more than 30 minutes with current database size and hardware. 6-20 hours is thankfully a historical artifact. Progress of the script can still be checked by counting samples, tests, and particularly results.

call transfer.box_lims_expedition_delete('123');
commit;


Repeat box_lims_expedition_delete for as many expeditions as required. The smaller the quantity of data, the faster it will go.
See detailed process for setting of expedition specific variables.  ----

Recommend the various select count(*) variants as routine content checks before and after the removal step.

select count(*), x_expedition from lims.sample
group by x_expedition;
select count(*), x_project from lims.sample
group by x_project;
select count(*), x_project from lims.test
group by x_project;
select count(*), analysis from lims.result
group by analysis;


This procedure is slow. Data we want to keep is intermixed with data to be deleted. So we do it rows at a time. The database is a 24/7 tool--there's always some activity against it.

...

These are encoded in the TRANSFER script UTIL_LIMS_TEST_RESULT_DELETE.

delete from lims.result
where sample_number in (
select sample_number from sample
where x_expedition='999')
and analysis not in ('BHA', 'BITS', 'DRILLING', 'LATLONG', 'OBSLENGTH', 'ORIGDEPTH')
;
 
delete from lims.test
where sample_number in (
select sample_number from sample
where x_expedition='999')
and analysis not in ('BHA', 'BITS', 'DRILLING', 'LATLONG', 'OBSLENGTH', 'ORIGDEPTH')
;