Linux: Hassle with “mail”-command

I just had a little annoying trouble with “mail”. Despite it’s silly reason I will post it here to keep others from having to spend unnecessarily time on it.

I wanted to mail a logfile via “mail” from my Linux-Box. Easy, one might think. ;o)
But I got stuck with this error:

WARNING: RunAsUser for MSP ignored, check group ids (egid=3270, want=51)
can not chdir(/var/spool/clientmqueue/): Permission denied
Program mode requires special privileges, e.g., root or TrustedUser.

I used this line in my script:

echo "Monitor-Log `date`" | /bin/mail -s "Monitor-Log" -a /some/dir/monitor.log $RECIPIENTS -c $CCRECIPIENTS

The reason for the error was the sequence of the variables holding the mail-recipients (“$RECIPIENTS” and “$CCRECIPIENTS”). The last parameter to “mail” must ever be the final recipient. So all “CC”s have to go before that. The correct line is this:

echo "Monitor-Log `date`" | /bin/mail -s "Monitor-Log" -a /some/dir/monitor.log -c $CCRECIPIENTS $RECIPIENTS

As the given error-message is not straight for the root, I think this post was worth written.

Windows: Enable/Disable your LAN or Firewall on Shell

In the distant past, when I still used ZoneAlarm’s desktop firewall, I got used to have a big, red emergency-button to cut off internet-access with one single click. This came in handy from time to time when I suspected strange things happening in the background or when I wanted to use or test a program I suspected to secretly send data home. In the old days of Windows XP the disabling of the network adapter could have also be done with two simple clicks via the tray-symbol. But from Windows 7 on we no longer have a straigth and short path to nearly instantly cut off internet-access. I grumbled upon this matter time and again but never was in the mood to seek for a resolution. Until now. Here are four short commands for the purpose to disable/enable your network-adapter or Windows-Firewall (run them as Admin!). The latter can be useful in cases we just want to cut internet-access but still have access to our local network. The first one completely deactivates our LAN-Adapter.

Disable LAN-Adapter:

netsh interface set interface "Local Area Connection" disabled

Enable LAN-Adapter:

netsh interface set interface "Local Area Connection" enable

Disable Windows-Firewall:

netsh advfirewall set currentprofile firewallpolicy blockinbound,blockoutbound

Enable Windows-Firewall:

netsh advfirewall set currentprofile firewallpolicy blockinbound,allowoutbound

For (de)activating the LAN-Adapter we have to find the exact internal name of our interface (above it’s the string “Local Area Connection”). We do this on the command-line (shell). To get on the shell we hit the keys “WINDOWS + R”, type “cmd” in the dialog-box and hit enter. On the shell we type this line:

netsh interface show interface

After hitting enter we get the name of our interface to paste in the first two commands above as a replacement for the string “Local Area Connection”. Mind to enclose the interface-name with quotes if it contains blanks. We can create a batch-script-file for every of the four commands, store them on desktop and this way have made our own “internet-off-emergeny-buttons”. Just mind to right-click the batch-files and execute them “as Admin” cause otherwise you will get a confusing error-message.

MySQL: Batch-Drop Tables

Dropping all tables of a given MySQL-Database is easy. We fetch all tablenames from the information_schema, concatenate them to a a series of “DROP TABLE”-Commands and pipe them to mysql for execution:

mysql -u root -p$MYSQL_PW -B -N -e "select concat('drop table $TARGETSCHEMA.',table_name,';') from information_schema.tables where table_schema='$TARGETSCHEMA'" information_schema | mysql -u root -p$MYSQL_PW $TARGETSCHEMA

You replace $MYSQL_PW with your MySQL-Root-Password and $TARGETSCHEMA with your target-database. If not root, you can also use another user-account with the necessary privileges.

-B stands for batch-mode. (can also be omitted)
-N suppresses the column-names of the result-set.
-e executes the following SQL.

A handy shell-script for our matter could look like this:

#!/bin/bash
# drop all schema-tables
# 2014-07-11 Marc Tempel

if [ $# -ne 2 ]
then
	echo "usage: $0 <targetschema> <mysql_root_pwd>"
	exit
fi

export MYSQL_PW=$2
TARGETSCHEMA=$1
TCOUNT=`mysql -u root -p$MYSQL_PW -B -N -e "select count(*) from information_schema.tables where table_schema='$TARGETSCHEMA'" information_schema`

read -p "Dropping all $TCOUNT tables of schema $TARGETSCHEMA. This can't be undone!"

mysql -u root -p$MYSQL_PW -B -N -e "select concat('drop table $TARGETSCHEMA.',table_name,';') from information_schema.tables where table_schema='$TARGETSCHEMA'" information_schema | mysql -u root -p$MYSQL_PW $TARGETSCHEMA

#EOF

Oracle: Show installed components/options and feature-usage

To check which components are installed in a database:

SELECT * FROM DBA_REGISTRY;

This view also shows the coresponding schema to a component – and also it’s related schemas.

To check which options are enabled:

SELECT * FROM V$OPTION;

To check which features are used:

SELECT * FROM DBA_FEATURE_USAGE_STATISTICS;

Oracle: “LOGOFF BY CLEANUP” Action Name in DBA_AUDIT_TRAIL

Developer reports problems with a Tomcat-Server. Something with established connection-pool-sessions which suddenly cease to work, but apparently have not disconnected. Blaming the database as the cause for that mess.

So, I checked the DBA_AUDIT_TRAIL for that servers connections. There were tons of normal “LOGON”- / “LOGOFF”-actions recorded, but also occasionally a bunch of “LOGOFF BY CLEANUP”-actions. “CLEANUP” to me smelled like PMON cleaned up some abandoned connections whose client-sides died away. To verify my assumption I checked the Oracle support docs at MOS (Doc ID 274697.1), which states:

(...)
"LOGOFF BY CLEANUP" Action Name in SYS.AUD$ Table
-------------------------------------------------
This audit entry is written by PMON when it clears a dead process/session once
the DBA enabled auditing on :

CREATE SESSION or
CONNECT or
SESSION

When a server process dies abruptly, it would not have got the opportunity to
put the audit record.

The PMON finds this dead process and starts cleaning up this dead process.
At this time, PMON writes this audit record indicating the process termination
was abnormal.

The known situations when we have a dead process is when the server process is
either killed at the OS level or within the database using the ALTER SYSTEM KILL
SESSION command.
When the server process cannot contact the client, the server process exits
gracefully.
This does not constitute a LOGOFF BY CLEANUP record, but a LOGOFF record.
(...)

This seemed to prove me wrong, as this note says that the problem lies on the DB-server-side. So I started off examining my otherwise smooth running DB-Server for anomalies, being the root of that evil. But in vain. “LOGOFF BY CLEANUP”-actions still happened leaving me without a clue.

Remembering my first assumption I set up a test-case for to check what action would be really recorded in the audit-trail when I simply kill my connected sqlplus-process at the client-side. According to the note above, this should end up in a “gracefully exited server process” with a plain “LOGOFF” record. But guess what…

After some testing I found the above MOS-Note to be wrong and misleading. According to my tests, the opposite of the note’s assertion is true.

As I presumed, the “LOGOFF BY CLEANUP” is recorded, when the client-side vanishes without a clean disconnect. It’s the client-process that “dies abruptly” – not the server’s one.
Killing the session via “ALTER SYSTEM KILL SESSION” or killing the server-process at OS-Level leaves no trace in the audit-log – contradicting the note above.

Tested with Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production.

Oracle: Getting the DDL for scheduler-jobs

Getting the DDL for tables, views etc. is easy using the package DBMS_METADATA:

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

For example, to extract the DDL for table HELP in the schema SYSTEM we use this:

SELECT DBMS_METADATA.GET_DDL('TABLE', 'HELP', 'SYSTEM') FROM DUAL;

and we get that:

"
  CREATE TABLE "SYSTEM"."HELP" 
   (	"TOPIC" VARCHAR2(50) NOT NULL ENABLE, 
	"SEQ" NUMBER NOT NULL ENABLE, 
	"INFO" VARCHAR2(80), 
	 CONSTRAINT "HELP_TOPIC_SEQ" PRIMARY KEY ("TOPIC", "SEQ")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE
   ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 49152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" "

But if we try to adapt this for to get the DDL of a scheduler-job, we’ll get stuck:

select dbms_metadata.get_ddl('JOB','JOB01') from dual;

ORA-31604: invalid NAME parameter "NAME" for object type JOB in function SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4517
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8252
ORA-06512: at "SYS.DBMS_METADATA", line 2752
ORA-06512: at "SYS.DBMS_METADATA", line 2639
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
31604. 00000 -  "invalid %s parameter \"%s\" for object type %s in function %s"
*Cause:    The specified parameter value is not valid for this object type.
*Action:   Correct the parameter and try the call again.

The same for scheduler-programs or -schedules:

select dbms_metadata.get_ddl('SCHEDULE','EVERY_MONDAY_800PM') from dual;

ORA-31600: invalid input value SCHEDULE for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2576
ORA-06512: at "SYS.DBMS_METADATA", line 2627
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1
31600. 00000 -  "invalid input value %s for parameter %s in function %s"
*Cause:    A NULL or invalid value was supplied for the parameter.
*Action:   Correct the input value and try the call again.

The trick is to use “PROCOBJ” (stands for ‘procedural object’) as object-type for all scheduler-objects:

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', ['JOBNAME' | 'SCHEDULE' | 'PROGRAM' ]) FROM DUAL;

select dbms_metadata.get_ddl('PROCOBJ','REBUILD_INDEXES') from dual;

"  
BEGIN 
dbms_scheduler.create_job('"REBUILD_INDEXES"',
job_type=>'STORED_PROCEDURE', 
job_action=>'REBUILDINDEXES', 
number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('07-MAR-2014 12.00.00,000000000 AM CET','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), 
repeat_interval=> 'FREQ=DAILY; BYHOUR=20', 
end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', 
enabled=>FALSE, 
auto_drop=>FALSE,
comments=> 'Rebuild all indexes dependent on the Index-to-Table-Size-Ratio'
);
dbms_scheduler.enable('"REBUILD_INDEXES"');
COMMIT; 
END; 
"

Edit 2014-08-05:
Please note that it is not possible to get the DDL for scheduler-jobs owned by user SYS.

[oracle@testsrv ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 5 13:27:34 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select owner, job_name, job_type from dba_scheduler_jobs where job_name='PURGE_AUDIT_TRAIL';

OWNER                          JOB_NAME                       JOB_TYPE
------------------------------ ------------------------------ ----------------
SYS                            PURGE_AUDIT_TRAIL              PLSQL_BLOCK

SQL>

SQL> SELECT DBMS_METADATA.GET_DDL('PROCOBJ','PURGE_AUDIT_TRAIL','SYS') FROM DUAL;
ERROR:
ORA-31603: object "PURGE_AUDIT_TRAIL" of type PROCOBJ not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1

no rows selected

SQL>

As per MOS-Notes this is “because by using PROCOBJ the package [DBMS_METADATA] is using similar functionality as which datapump export would use and as known SYS objects are marked as non-exportable” (Doc ID 567504.1).
This note also states, that the lack of exporting Scheduler-DDL by using “JOB” as object-type is filed as a bug at Oracle. So there is hope, that one day we will have a more intuitive way for extracting Scheduler-DDL.

Oracle: Short introduction to the Oracle Scheduler

The Oracle-Scheduler is a nice alternative to cronjobs or alike – and it is not as complicated as it may seem at a first glance.

Well, with the package DBMS_SCHEDULER it is possible to define very granular scheduler-objects. There are “programs” that hold the definition for to be executed things, or “schedules” that hold the definition for certain schedule-types. And all of these can be combined to “jobs” consisting of “programs” and “schedules”. And even job-chains are possible, that respond to certain events and start the next job-chain-job dependent on an event.

But if we just want a certain procedure to be executed at a regular intervall it is quite easy. We just need the DBMS_SCHEDULER.CREATE_JOB:

DBMS_SCHEDULER.CREATE_JOB (
   job_name             IN VARCHAR2,
   job_type             IN VARCHAR2,
   job_action           IN VARCHAR2,
   number_of_arguments  IN PLS_INTEGER              DEFAULT 0,
   start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   repeat_interval      IN VARCHAR2                 DEFAULT NULL,
   end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
   enabled              IN BOOLEAN                  DEFAULT FALSE,
   auto_drop            IN BOOLEAN                  DEFAULT TRUE,
   comments             IN VARCHAR2                 DEFAULT NULL,
   credential_name      IN VARCHAR2                 DEFAULT NULL,
   destination_name     IN VARCHAR2                 DEFAULT NULL);

For a simple job that executes my already created procedure “REBUILDINDEXES” every day at 20:00 the following would suffice:

begin
dbms_scheduler.create_job
 (job_name => 'REBUILD_INDEXES',
  job_type => 'STORED_PROCEDURE',
  job_action=> 'REBUILDINDEXES',
  start_date=> trunc(sysdate),
  repeat_interval=> 'FREQ=DAILY; BYHOUR=20;',
  enabled=>true,
  comments=>'Rebuild all indexes dependent on the Index-to-Table-Size-Ratio');
end;

The “job_type” can be one of this:

PLSQL_BLOCK – an anonymous block of PL/SQL-Code
STORED_PROCEDURE – a defined procedure
EXECUTABLE – a shell-script or alike
CHAIN – a job chain

To alter a created job, here to execute the above job every 30 minutes, we use DBMS_SCHEDULER.SET_ATTRIBUTE:

DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          IN {BOOLEAN|DATE|TIMESTAMP|
                        TIMESTAMP WITH TIME ZONE|TIMESTAMP WITH LOCAL TIME ZONE|
                        INTERVAL DAY TO SECOND});
begin
dbms_scheduler.set_attribute
('REBUILD_INDEXES',
'repeat_interval',
'FREQ=MINUTELY; INTERVAL=30');
end;

To see what jobs are created, we use the views USER_SCHEDULER_JOBS or DBA_SCHEDULER_JOBS. There we can see what jobs are enabled, what they shall do or for what time the next run is scheduled.

Information about run jobs can be obtained from the views USER_SCHEDULER_JOB_LOG and USER_SCHEDULER_JOB_RUN_DETAILS.

The details of logging and the retention-time of the log-data are controlled by job_classes. Per default our job is set to the “DEFAULT_JOB_CLASS”. Information about all job_classes can be found in DBA_SCHEDULER_JOB_CLASSES.

To create an own JOB_CLASS we use DBMS_SCHEDULER.CREATE_JOB_CLASS.

DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name            IN VARCHAR2,
   resource_consumer_group   IN VARCHAR2 DEFAULT NULL,
   service                   IN VARCHAR2 DEFAULT NULL,
   logging_level             IN PLS_INTEGER
                                DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
   log_history               IN PLS_INTEGER DEFAULT NULL,
   comments                  IN VARCHAR2 DEFAULT NULL);

To alter a JOB_CLASS we use the DBMS_SCHEDULER.SET_ATTRIBUTE like above.

The default log_history is set to 30 days, as we can see in DBA_SCHEDULER_GLOBAL_ATTRIBUTE.

To alter global scheduler attributes we use DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE.

Per default an oracle-maintenance-job is run every nigth at 3 a.m. which purges obsolete log-data according to the JOB_CLASS-Definition. Notice that there are pre-defined job-classes with a log_history of 1 million days; so eventually a regular (manual) check and purge with DBMS_SCHEDULER.PURGE_LOG is needed.

Follow

Get every new post delivered to your Inbox.