No internet connections but Google

Had a little weird problem today: my Thunderbird could not connect to it’s mailservers, despite their connect data was correctly set. Googling for a resolution showed me the results of my search, but none of them was reachable. Also ping requests to those hosts were unsuccessful because the system was unable to resolve the host-names via DNS. This applied to all my google-searches: no destination was ever reachable. On my other box all was fine: mailservers reachable and full access to all resources on internet.

Analyzing my net-config with “ipconfig /all” (this is a Windows-box) brought up, that my box had no IPv4-address set – just an IPv6 one. Also “Standardgateway” and “DNS-Server” were just set for IPv6 (IPv4 was blank).
Knowing that Facebook uses IPv6 too, I tried to connect to them: worked. So, all connects to IPv6-capable hosts where OK, while all IPv4 hosts were unreachable.

My router was set to hand out IP-addresses via DHCP. I have no clue why this got stuck. Maybe a restart of the whole equipment would have helped, but I decided to fix this problem by giving my box a static IP-address beyond the DHCP-range of my router. Also I set the known IP-address of my router for standardgateway and DNS-server manually. This cured the problem and full acces to internet was regained.

Advertisements

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 "SET FOREIGN_KEY_CHECKS = 0; 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  "
	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! (ENTER)"

mysql -u root -p$MYSQL_PW -B -N -e "SET FOREIGN_KEY_CHECKS = 0; 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

Edit 2018-02-27:
To don’t be blocked by foreign-key-checks, I added “SET FOREIGN_KEY_CHECKS = 0;” above to disable FOREIGN_KEY_CHECKS.

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.