...

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

• Open two PuTTY sessions. 
• Connect each 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 1

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

PuTTY window 2

[oracle@k1 ~]$ . oraenv
ORACLE_SID = [LIMSJR_HA] ? LIMSJR_HA
cd /backup/LIMSJR/dpdump
sqlplus transfer

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.

The "-- (number)" items refer to footnoted variances described below. "--" is a SQL comment, not part of the command.e

sql>
spool box_processing-349.log

set timing on
set serveroutput on

call cleanout_new_tables(); -- (1)
call asman_catalog_delete('EXP346'); -- (2) Last expedition
call lime_audit_delete(###);    -- (4)
call lims_expedition_delete('346');  -- (3) Last expedition
call lims_cleanup();
call lims_defrag(); -- (5)
spool off
exit

NOTE - participants_remove.sql found in SVN under - C:\develop\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 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 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. Then you can 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.  Only issue was Dev laptop rebooted from and Update but process was on Build Box so no problems.
  • 385 & 378T: 07:24:06.09 for 285 and only  couple 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;

Discussions

How should I connect? PuTTY, SQL Developer, command-line SSH...

For the TRANSFER schema script components, any of the tools will get the job done.
Methods for capturing a log vary per tool

Database Schemas

...

An Annoyance: Monitoring for Archive Log Space Filling

...

Monitoring Archive Log Generation

...

• 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 effect. 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

...

sql>
archive log list

...

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

...

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

...

call cleanout_new_tables();
call asman_catalog_delete('EXPtest'); 
call lims_expedition_delete('test');

...

set timing on
set serveroutput on

...

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

...

A Cookbook and Scenarios.

...

Clean the TRANSFER schema

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

call cleanout_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 asman_catalog_delete('EXP123');
commit;

...

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

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.

call lims_expedition_delete('123');
commit;

...

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;

...

• 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

...

Scenario: Load pre-existing Janus data

...

Scenario: Legacy data load scripts

...

alter table some-table disable all triggers;
[other work here]
alter table some-table enable all triggers;

...

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. 20220212 df No longer relevant since move to DAM MerlinOne circa 2021 Oct.

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 that you may not have been aware of.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.

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')
;

Deprecated

Managing Archive Log Space - ===This has not been a problem in years.===

We cannot get through the process at this time without doing this. Start this way until we implement a more efficient BOX process. Nota bene. The ODA consists of a LIMSJR (primary) and LIMSJR1 (standby) node. This instruction must be carried through for both nodes.

The first and best response to a pending archive logger stuck condition is to increase the available archive log space. Feedback from systems personnel indicate that 400G is the physical limit--so specifying 1000G is just silly.

==We set this size up and never set it back down.  We have not had a problem in years.  THis is for a potential issues that might happen as we get larger expeditions with more data.====

[oracle@k1 ~]$ . oraenv
ORACLE_SID = [LIMSJR] ? LIMSJR
[oracle@k1 ~]$ sqlplus your-name_dba
> alter system set db_recovery_file_dest_size=200G scope=memory;
> exit

[oracle@k1 ~]$ . oraenv
ORACLE_SID = [LIMSJR] ? LIMSJR1
[oracle@k1 ~]$ sqlplus your-name_dba
> alter system set db_recovery_file_dest_size=200G scope=memory;
> exit

...

• an independent Oracle session via PuTTY or SQL Developer.
• Run commands against the tables being operated on.

 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.

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

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

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