Saturday, May 12, 2012

Configuring a Tata Photon+ USB device in Linux

It has been a long time since my last post because I changed jobs three months ago, and in fact this post might give a hint about my new situation; anyway I hope to keep posting Linux and UNIX-related messages and this one is about dealing with a mobile broadband USB device. It is not so difficult to work with this kind of devices in Linux, and is very simple to configure it with NetworkManager, but first you need to have installed the usb-modeswitch software that changes the device's state from USB storage to USB modem in order to use the USB dongle as a modem. In Debian Squeeze you can do it installing the usb-modeswitch package. If you don't have the usb-modeswitch software you will see just an USB storage device, something like this:
May 12 15:23:38 olimpo kernel: [   64.172559] usb 2-1: new full speed USB device using uhci_hcd and address 2
May 12 15:23:38 olimpo kernel: [   64.339573] usb 2-1: New USB device found, idVendor=12d1, idProduct=1446
May 12 15:23:38 olimpo kernel: [   64.339584] usb 2-1: New USB device strings: Mfr=1, Product=2, SerialNumber=4
May 12 15:23:38 olimpo kernel: [   64.339593] usb 2-1: Product: HUAWEI Mobile
May 12 15:23:38 olimpo kernel: [   64.339600] usb 2-1: Manufacturer: HUAWEI TECHNOLOGIES
May 12 15:23:38 olimpo kernel: [   64.339607] usb 2-1: SerialNumber: XXXXXXXXXXXXXXXXXX
May 12 15:23:38 olimpo kernel: [   64.339850] usb 2-1: configuration #1 chosen from 1 choice
May 12 15:23:38 olimpo kernel: [   64.350119] scsi4 : SCSI emulation for USB Mass Storage devices
May 12 15:23:38 olimpo kernel: [   64.360054] usb-storage: device found at 2
May 12 15:23:38 olimpo kernel: [   64.360061] usb-storage: waiting for device to settle before scanning
After installing the usb-modeswitch software you can plug your USB modem, and you will see at the end something like this:
May 12 15:23:40 olimpo usb_modeswitch: switching 12d1:1446 (HUAWEI TECHNOLOGIES: HUAWEI Mobile)
May 12 15:23:46 olimpo kernel: [   70.408061] usb 2-1: new full speed USB device using uhci_hcd and address 3
May 12 15:23:46 olimpo kernel: [   70.576318] usb 2-1: New USB device found, idVendor=12d1, idProduct=140b
May 12 15:23:46 olimpo kernel: [   70.576330] usb 2-1: New USB device strings: Mfr=1, Product=2, SerialNumber=4
May 12 15:23:46 olimpo kernel: [   70.576339] usb 2-1: Product: HUAWEI Mobile
May 12 15:23:46 olimpo kernel: [   70.576345] usb 2-1: Manufacturer: HUAWEI TECHNOLOGIES
May 12 15:23:46 olimpo kernel: [   70.576352] usb 2-1: SerialNumber: XXXXXXXXXXXXXXXXXX
May 12 15:23:46 olimpo kernel: [   70.576600] usb 2-1: configuration #1 chosen from 1 choice
May 12 15:23:46 olimpo kernel: [   70.587360] scsi8 : SCSI emulation for USB Mass Storage devices
May 12 15:23:46 olimpo kernel: [   70.588245] usb-storage: device found at 3
May 12 15:23:46 olimpo kernel: [   70.588252] usb-storage: waiting for device to settle before scanning
May 12 15:23:46 olimpo kernel: [   70.785972] usbcore: registered new interface driver usbserial
May 12 15:23:46 olimpo usb_modeswitch: switched to 12d1:140b (HUAWEI TECHNOLOGIES: HUAWEI Mobile)
May 12 15:23:46 olimpo kernel: [   70.787067] USB Serial support registered for generic
May 12 15:23:46 olimpo kernel: [   70.788192] usbcore: registered new interface driver usbserial_generic
May 12 15:23:46 olimpo kernel: [   70.788201] usbserial: USB Serial Driver core
May 12 15:23:46 olimpo kernel: [   71.042606] USB Serial support registered for GSM modem (1-port)
May 12 15:23:46 olimpo kernel: [   71.048829] option 2-1:1.0: GSM modem (1-port) converter detected
May 12 15:23:46 olimpo kernel: [   71.050191] usb 2-1: GSM modem (1-port) converter now attached to ttyUSB0
May 12 15:23:46 olimpo kernel: [   71.050242] option 2-1:1.1: GSM modem (1-port) converter detected
May 12 15:23:46 olimpo kernel: [   71.050660] usb 2-1: GSM modem (1-port) converter now attached to ttyUSB1
May 12 15:23:46 olimpo kernel: [   71.050702] option 2-1:1.2: GSM modem (1-port) converter detected
May 12 15:23:46 olimpo kernel: [   71.056719] usb 2-1: GSM modem (1-port) converter now attached to ttyUSB2
May 12 15:23:46 olimpo kernel: [   71.056795] usbcore: registered new interface driver option
May 12 15:23:46 olimpo kernel: [   71.056803] option: v0.7.2:USB Driver for GSM modems
This is the most important part, to have detected your USB dongle as a GSM modem. If you like you can configure it easily with NetworkManager, but if you prefer something that you can do in a terminal then install the wvdial package and fill the /etc/wvdial.conf and /etc/ppp/chap-secrets files with this:
olimpo:/var/log# cat /etc/wvdial.conf
[Dialer Defaults]
Init1 = ATZ
Init2 = ATQ0 V1 E1 S0=0 &C1 &D2 +FCLASS=0
Modem Type = USB Modem
ISDN = 0
Phone = #777
New PPPD = yes
Modem = /dev/ttyUSB0
Username = internet
Password = internet
Baud = 230400

olimpo:/var/log# cat /etc/ppp/chap-secrets
# Secrets for authentication using CHAP
# client server secret   IP addresses
internet * internet
And if you're using Debian you can also issue the dpkg-reconfigure wvdial command and give all the information required to the configuration program. Then you can launch wvdial as root from a terminal prompt and connect to the Internet, and you will see something like this in the syslog:
May 12 15:47:06 olimpo pppd[11490]: pppd 2.4.5 started by root, uid 0
May 12 15:47:06 olimpo kernel: [ 1770.688570] PPP generic driver version 2.4.2
May 12 15:47:06 olimpo pppd[11490]: Using interface ppp0
May 12 15:47:06 olimpo pppd[11490]: Connect: ppp0 <--> /dev/ttyUSB0
May 12 15:47:09 olimpo pppd[11490]: CHAP authentication succeeded
May 12 15:47:09 olimpo pppd[11490]: CHAP authentication succeeded
May 12 15:47:09 olimpo kernel: [ 1773.210976] PPP BSD Compression module registered
May 12 15:47:09 olimpo kernel: [ 1773.225216] PPP Deflate Compression module registered
May 12 15:47:09 olimpo pppd[11490]: local  IP address 59.161.17.54
May 12 15:47:09 olimpo pppd[11490]: remote IP address 172.29.145.65
May 12 15:47:09 olimpo pppd[11490]: primary   DNS address 4.2.2.3
May 12 15:47:09 olimpo pppd[11490]: secondary DNS address 121.242.190.181

...

May 12 15:54:11 olimpo pppd[11490]: Terminating on signal 15
May 12 15:54:11 olimpo pppd[11490]: Connect time 7.1 minutes.
May 12 15:54:11 olimpo pppd[11490]: Sent 460447 bytes, received 1883330 bytes.
Of course, this procedure might be helpful for configuring other USB modems as well.

Monday, February 13, 2012

ORA-09817 error and full file systems

Let's say that you're trying to log into your database and suddenly got this error message:

oracle@mydb$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 13 15:47:27 2012

Copyright (c) 1982, 2008, Oracle. All rights reserved.

ERROR:
ORA-09817: Write to audit file failed.
SVR4 Error: 28: No space left on device
ORA-01075: you are currently logged on


Enter user-name:

oracle@mydb$

This is an easy problem to guess and the key is in the message No space left on device. The instance cannot write audit files because the file system is full so you have to figure out why is that file system full:

oracle@mydb$ set | grep ORA
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/orahome
ORACLE_SID=mydb

oracle@mydb$ strings $ORACLE_HOME/dbs/*mydb.ora | grep audit_file_dest
*.audit_file_dest='/oracle/admin/mydb/adump'

oracle@mydb$ df -k | egrep '(Filesystem)|(/oracle)'
Filesystem kbytes used avail capacity Mounted on
/dev/vx/dsk/orafs/oracle 16327680 16327680 0 100% /oracle

In this case the audit file destination is in the Oracle base file system, therefore the problem might be in other directory:

oracle@mydb$ du -k /oracle | sort -nr | head
16035094 /oracle
8412062 /oracle/diag
8297523 /oracle/diag/rdbms/mydb/mydb
8297523 /oracle/diag/rdbms/mydb
8297523 /oracle/diag/rdbms
8250027 /oracle/diag/rdbms/mydb/mydb/trace
6926099 /oracle/orahome
1631834 /oracle/orahome/mydirectory
938085 /oracle/orahome/mydirectory/somefiles
727017 /oracle/orahome/bin

oracle@mydb$ du -ka /oracle/diag/rdbms/mydb/mydb/trace | sort -nr | head
8250027 /oracle/diag/rdbms/mydb/mydb/trace
5737533 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trc
2506955 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trc
1946 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trm
969 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trm
457 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_3695.trc
380 /oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log
314 /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_8800.trc
314 /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_13295.trc
314 /oracle/diag/rdbms/mydb/mydb/trace/mydb_j000_23311.trc

Here is the problem: there are two very big trace files that occupy 8 gigabytes, half the file system size. When you know why your file system is full you have to decide what to do, like erasing, moving or compressing files, just be sure not to discard files you might need.

Wednesday, February 8, 2012

Creating partitioned tables and ORA-00922 error

There is a lot of different version of the Oracle Database software and even if the logic and operation of the database is more or less the same across versions, it is also more or less different across versions; for example, there is no drop database sentence in Oracle 9i.

So you work usually with 11g databases and love the tables partitioned by intervals, and you create one:

oracle@my11gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:43:42 2012

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> CREATE TABLE MY_TABLE
(
SOMETEXT VARCHAR2(4000),
MOREINFO VARCHAR2(256),
ADATE DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);

Table created.

And you try to create that same table in a 10g database just to find that you can't:

oracle@my10gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 8 12:46:15 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


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

SQL> CREATE TABLE MY_TABLE
(
SOMETEXT VARCHAR2(4000),
MOREINFO VARCHAR2(256),
ADATE DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);
INTERVAL (NUMTODSINTERVAL(1,'day'))
*
ERROR at line 7:
ORA-00922: missing or invalid option

Look no more: the ORA-00922 error in a 10g database means that you're trying to use a functionality that doesn't exist; in a 11g database it would mean missing brackets or syntax errors.

oracle@my11gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:50:54 2012

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> CREATE TABLE MY_TABLE
(
SOMETEXT VARCHAR2(4000),
MOREINFO VARCHAR2(256),
ADATE DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'));
partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
*
ERROR at line 8:
ORA-00922: missing or invalid option

More information:

CREATE TABLE (10g Release 2)
CREATE TABLE (11g Release 1)
Partition Administration

Tuesday, February 7, 2012

Adding numbers in a file

If you have a small file filled with numbers and are tasked to add all of them, you can do it with bc and paste:

cronos@olimpo:~$ cat file.txt
1|11
2|22
3|33
4|44
5|55
|
6|66
7|77
8|88
9|99

cronos@olimpo:~$ cat file.txt | cut -d '|' -f 1 | grep -v '^$' | paste -sd '+' - | bc
45

With cut you select the field to sum, with grep you select out empty fields, with paste you add at the end of each line a + sign, and with bc you do the addition.

But if you have a very big file this won't work; you can instead use awk:

cronos@olimpo:~$ cat file.txt | awk 'BEGIN {FS="|";OFMT="%.2f"} {a+=$1;b+=$2} END {print "First field:",a,"Second field:",b}'

First field: 45 Second field: 495

With FS you set the field delimiter and with OFMT you set the number format, in this case two decimal places with no scientific notation. With awk a nice extra is to be able to add two or more columns at the same time.

More information:

Sum of numbers in file - UNIX alternatives

Tuesday, January 31, 2012

Locking statistics of tables

Having accurate statistics of database objects is mandatory in order to get the best execution path, and usually you can collect statistics using the DBMS_STATS package with no problem at all. But sometimes you have special tables that needs special treatment, like "temporary" tables (normal tables that are populated and its data modified very fast) that can change a lot the execution path of a SQL sentence and drive you crazy trying to pinpoint the root cause of that execution path changes.

As a demonstration of this behavior we will create two tables, one that will change a lot (test1) and other that is more or less static (test2):

SQL> create table test1 (id number primary key, id_char varchar2(15));

Table created.

SQL> declare
mynum number := 1;
begin
while mynum < 10 loop
insert into test1 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> create table test2 (id number primary key, id_char varchar2(15));

Table created.

SQL> declare
mynum number := 1;
begin
while mynum < 100000 loop
insert into test2 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

At the beginning test1 table has 9 records, and we want to join test1 and test2 tables to get a few records, but first we will gather statistics of test1:

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
2 /

PL/SQL procedure successfully completed.

SQL> select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

ID ID_CHAR
---------- ---------------
4 4
5 5
6 6
7 7

Next we will get the current execution plan for this small query:

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 56 | 4 (0) | 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 56 | 4 (0) | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 5 | 55 | 4 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004858 | 5 | | 3 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 3 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("B"."ID">=4 AND "B"."ID"<=7)
4 - access("A"."ID"="B"."ID")
filter("A"."ID">=4 AND "A"."ID"<=7)

18 rows selected.

This execution plan looks good and really doesn't matter a lot because we got just a few records, but wait! Nine records is not the typical size of test1 table; now we will create more records:

SQL> delete from test1;

9 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10000 loop
insert into test1 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

You might think that there is not a lot of difference having ten or ten thousand records in test1, but Oracle's optimizer does not think the same way and this time the execution plan is different:

SQL> EXPLAIN PLAN FOR
2 select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2418654178

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0) | 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 14 | 3 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004857 | 1 | 3 | 1 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004858 | 1 | | 1 (0) | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 11 | 2 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."ID">=4000 AND "A"."ID"<=7000)
4 - access("A"."ID"="B"."ID")
filter("B"."ID"<=7000 AND "B"."ID">=4000)

19 rows selected.

You might think that retrieving four records is different of retrieving four thousand and that's right, but the optimizer choose the execution path based in wrong data; look the amount of rows Oracle thinks it will work to.

You will see it clearly refreshing the test1 table statistics and getting the new execution path:

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'SYS', TABNAME => 'TEST1'); END;
2 /

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3001 | 45015 | 17 (12) | 00:00:01 |
| 1 | NESTED LOOPS | | 3001 | 45015 | 17 (12) | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 3002 | 33022 | 15 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004858 | 3002 | | 8 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 4 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("B"."ID">=4000 AND "B"."ID"<=7000)
4 - access("A"."ID"="B"."ID")
filter("A"."ID">=4000 AND "A"."ID"<=7000)

18 rows selected.

Can you see the problem? Having to deal with this kind of tables you may: use hints to force the optimal execution of the SQL sentence, refresh statistics every time you change a table like this, or lock the statistics when you have a typical table size or optimal execution path.

We will consider that at this point the test1 table has a typical size and its statistics help the optimizer to chose an optimal execution plan, therefore we will lock its statistics this way:

SQL> exec DBMS_STATS.LOCK_TABLE_STATS ('MYUSER', 'TEST1');

PL/SQL procedure successfully completed.

Now we will repopulate again the test1 table with nine records:

SQL> delete from TEST1;

9999 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10 loop
insert into test1 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
2 /
BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

As long as the statistics of test1 table were locked you cannot gather new statistics unless you unlock the statistics of the table. Anyway, the point of doing this is to help the optimizer to chose a good execution plan:

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 60 | 4 (0) | 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 60 | 4 (0) | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 5 | 55 | 4 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004858 | 5 | | 3 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 4 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("B"."ID">=4 AND "B"."ID"<=7)
4 - access("A"."ID"="B"."ID")
filter("A"."ID"<=7 AND "A"."ID">=4)

18 rows selected.

SQL> delete from TEST1;

9 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10000 loop
insert into test1 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3001 | 45015 | 17 (12) | 00:00:01 |
| 1 | NESTED LOOPS | | 3001 | 45015 | 17 (12) | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 3002 | 33022 | 15 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004858 | 3002 | | 8 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 4 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("B"."ID">=4000 AND "B"."ID"<=7000)
4 - access("A"."ID"="B"."ID")
filter("A"."ID">=4000 AND "A"."ID"<=7000)

18 rows selected.

As you can see, this time the execution plan was the same for ten or ten thousand records in test1 table and the number of rows managed in the execution plans were more or less accurate.

By the way, if you want to unlock the statistics of a table you can do it this way:

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS ('MYUSER', 'TEST1');

PL/SQL procedure successfully completed.

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
2 /

PL/SQL procedure successfully completed.


More information:

Locking or Unlocking Statistics
Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS

Monday, January 30, 2012

Oracle and OCFS2 not releasing space used

Sometimes you have to drop a tablespace to recover storage space and use it to create other tablespace, like when you have to resize an undo tablespace. And usually is not a big issue unless you have OCFS2 file systems:

oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'
Filesystem 1M-blocks Used Available Use% Mounted on
24576 24572 4 100% /mydb/undo

oracle@myserver$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 26 22:57:35 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


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

SQL> drop tablespace myundo including contents and datafiles;

Tablespace dropped.

SQL> create undo tablespace mynewundo datafile '/mydb/undo/undo.dbf' size 10g;
create undo tablespace mynewundo datafile '/mydb/undo/undo.dbf' size 10g
*
ERROR at line 1:
ORA-01119: error in creating database file '/mydb/undo/undo.dbf'
ORA-27044: unable to write the header block of file
Linux-ia64 Error: 28: No space left on device
Additional information: 4

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'
Filesystem 1M-blocks Used Available Use% Mounted on
24576 24572 4 100% /mydb/undo

oracle@myserver$ ls -la /mydb/undo
total 16
drwxr-xr-x 4 root root 4096 Jan 20 2010 .
drwxr-xr-x 4 root root 4096 Dec 17 2009 ..
drwxr-xr-x 2 root root 4096 Dec 17 2009 lost+found

As you might notice there is no space recovered from dropping that undo tablespace, and since we need to create a new undo tablespace in the same file system we have to do something.

I don't have a lot of dabatases placed in OCFS2 file systems so I didn't care about finding the root cause of this problem, and since I had this database with no users connected I just created a small undo tablespace somewhere else, shut down the instance, and after doing it I got that space back:

oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'
Filesystem 1M-blocks Used Available Use% Mounted on
24576 359 24217 2% /mydb/undo

oracle@myserver$ ls -la /mydb/undo
total 16
drwxr-xr-x 4 root root 4096 Jan 20 2010 .
drwxr-xr-x 4 root root 4096 Dec 17 2009 ..
drwxr-xr-x 2 root root 4096 Dec 17 2009 lost+found

oracle@myserver$ mount|grep /mydb/undo
/dev/mapper/myvg-undo on /mydb/undo type ocfs2 (rw,_netdev,datavolume,nointr,heartbeat=local)

oracle@myserver$ uname -a
Linux myserver.localdomain 2.6.9-67.EL #1 SMP Wed Nov 7 13:43:35 EST 2007 ia64 ia64 ia64 GNU/Linux

oracle@myserver$ /sbin/lsmod|grep ocf
ocfs2 744096 26
debugfs 27340 2 ocfs2
ocfs2_dlmfs 53416 1
ocfs2_dlm 424192 2 ocfs2,ocfs2_dlmfs
ocfs2_nodemanager 316791 32 ocfs2,ocfs2_dlmfs,ocfs2_dlm
configfs 65468 2 ocfs2_nodemanager
jbd 149272 2 ocfs2,ext3

oracle@myserver$ /sbin/modinfo ocfs2
filename: /lib/modules/2.6.9-67.EL/kernel/fs/ocfs2/ocfs2.ko
license: GPL
author: Oracle
version: 1.2.9 1B43458FD47258934A48F1C
description: OCFS2 1.2.9 Mon May 19 14:04:34 PDT 2008 (build a693806cb619dd7f225004092b675ede)
depends: ocfs2_nodemanager,ocfs2_dlm,jbd,debugfs
vermagic: 2.6.9-67.EL SMP ia64gcc-3.4

I know this is just a workaround and not a good one, but it works fine and you can move on the next thing.

Wednesday, January 25, 2012

How to configure a VPN PPTP connection

If you need to open a VPN PPTP connection from a Linux computer, you can use the pptp client to configure a PPP connection and launch the pppd daemon to make the connection. You need to know at least the VPN server, an authorized user to make the connection and its password, and we will work with the root account for practical purposes but it's not mandatory.

First, if you don't have the pptp client you have to install it or configure the PPP connection by hand or by other means, in Debian Squeeze you could install the pptp-linux package.

Next, this is the easy way to configure your VPN PPTP connection:

pptpsetup --create MYVPN --server myvpnserver --username myvpnuser --password mypassword

You have to substitute myvpnserver, myvpnuser and mypassword with the VPN server, the user and password respectively; MYVPN is the name of the PPP configuration you will create and is chosen by you.

After this you will have a new configuration file in /etc/ppp/peers and also an extra line in /etc/ppp/chap-secrets:

olimpo:~# cat /etc/ppp/peers/MYVPN
# written by pptpsetup
pty "pptp myvpnserver --nolaunchpppd"
lock
noauth
nobsdcomp
nodeflate
name myvpnuser
remotename MYVPN
ipparam MYVPN

olimpo:~# cat /etc/ppp/chap-secrets
# Secrets for authentication using CHAP
# client server secret IP addresses

# added by pptpsetup for MYVPN
myvpnuser MYVPN "mypassword" *

If you want to configure the pppd daemon by hand, you just have to create a file in /etc/ppp/peers and add the authentication information in /etc/ppp/chap-secrets and it must be the same.

At this time you can open the PPP connection launching the pppd daemon:

olimpo:~# pppd call MYVPN updetach
Using interface ppp0
Connect: ppp0 <--> /dev/pts/3
CHAP authentication succeeded
local IP address 10.5.15.222
remote IP address 10.5.15.127

olimpo:~# tail --lines=16 /var/log/syslog
Jan 25 13:04:12 olimpo pppd[29036]: pppd 2.4.5 started by root, uid 0
Jan 25 13:04:12 olimpo pppd[29036]: Using interface ppp0
Jan 25 13:04:12 olimpo pppd[29036]: Connect: ppp0 <--> /dev/pts/3
Jan 25 13:04:13 olimpo pptp[29037]: anon log[main:pptp.c:314]: The synchronous pptp option is NOT activated
Jan 25 13:04:13 olimpo pptp[29041]: anon log[ctrlp_rep:pptp_ctrl.c:251]: Sent control packet type is 1 'Start-Control-Connection-Request'
Jan 25 13:04:13 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:739]: Received Start Control Connection Reply
Jan 25 13:04:13 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:773]: Client connection established.
Jan 25 13:04:14 olimpo NetworkManager[1919]: SCPlugin-Ifupdown: devices added (path: /sys/devices/virtual/net/ppp0, iface: ppp0)
Jan 25 13:04:14 olimpo NetworkManager[1919]: SCPlugin-Ifupdown: device added (path: /sys/devices/virtual/net/ppp0, iface: ppp0): no ifupdown configuration found.
Jan 25 13:04:14 olimpo pptp[29041]: anon log[ctrlp_rep:pptp_ctrl.c:251]: Sent control packet type is 7 'Outgoing-Call-Request'
Jan 25 13:04:14 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:858]: Received Outgoing Call Reply.
Jan 25 13:04:14 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:897]: Outgoing call established (call ID 0, peer's call ID 8918).
Jan 25 13:04:14 olimpo modem-manager: (net/ppp0): could not get port's parent device
Jan 25 13:04:17 olimpo pppd[29036]: CHAP authentication succeeded
Jan 25 13:04:17 olimpo pppd[29036]: local IP address 10.5.15.222
Jan 25 13:04:17 olimpo pppd[29036]: remote IP address 10.5.15.127

olimpo:~# ifconfig ppp0
ppp0 Link encap:Point-to-Point Protocol
inet addr:10.5.15.222 P-t-P:10.5.15.127 Mask:255.255.255.255
UP POINTOPOINT RUNNING NOARP MULTICAST MTU:1500 Metric:1
RX packets:7 errors:0 dropped:0 overruns:0 frame:0
TX packets:7 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:3
RX bytes:99 (99.0 B) TX bytes:93 (93.0 B)

olimpo:~# route
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
10.5.15.127 * 255.255.255.255 UH 0 0 0 ppp0
static.customer 10.123.46.1 255.255.255.255 UGH 0 0 0 br0
10.123.46.0 * 255.255.255.0 U 0 0 0 br0
default 10.123.46.1 0.0.0.0 UG 0 0 0 br0

That's it, you have a VPN connection open to you VPN server! Or it's almost done, because we have a routing problem.

olimpo:~# ping 10.5.15.123
PING 10.5.15.123 (10.5.15.123) 56(84) bytes of data.
^C
--- 10.5.15.123 ping statistics ---
7 packets transmitted, 0 received, 100% packet loss, time 6047ms

olimpo:~# traceroute 10.5.15.123
traceroute to 10.5.15.123 (10.5.15.123), 30 hops max, 60 byte packets
1 10.123.81.4 (10.123.81.4) 0.407 ms 0.429 ms 0.492 ms
2 10.123.148.1 (10.123.148.1) 0.313 ms 0.352 ms 0.345 ms
3 * * *
4 * * *
5 * * *
6 *^C

As you might notice in the route information, there is no route to the VPN network from your Linux computer. You could append defaultroute to the pppd command, but this way you will have connectivity ONLY to the VPN network; to put it simple: is like if you were attached directly to the other network. If you had internet access and there is no way to access internet in the VPN network then you'll have no internet access, or if you had access to other networks after using pppd with defaultroute you will no longer have access to them.

And in fact that's the way a VPN is supposed to work. For example, if you got a trojan in your computer and that program is tailored specifically for your organization, and you connect your computer to the VPN network and still have access to the internet, then that trojan might steal information and send it to someone else easily. Or you might thing that leaving your VPN connection open and your computer accessible from the internet would be handy to share your VPN connection; this way you're creating a security hole in the VPN network so be careful and do it if you really need to.

Therefore, if you need to keep connectivity to other networks and don't want to append defaultroute to the pppd command, you have to create the route by hand. In this case, we want to access the VPN network 10.5.15.0/24, therefore we will issue this route command:

olimpo:~# route add -net 10.5.15.0 netmask 255.255.255.0 dev ppp0

As you can see in the route and traceroute information, this time you can reach the example server (10.5.15.123) through your PPP gateway (10.5.15.127):

olimpo:~# route
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
10.5.15.127 * 255.255.255.255 UH 0 0 0 ppp0
static.customer 10.123.46.1 255.255.255.255 UGH 0 0 0 br0
10.123.46.0 * 255.255.255.0 U 0 0 0 br0
10.5.15.0 * 255.255.255.0 U 0 0 0 ppp0
default 10.123.46.1 0.0.0.0 UG 0 0 0 br0

olimpo:~# traceroute 10.5.15.123
traceroute to 10.5.15.123 (10.5.15.123), 30 hops max, 60 byte packets
1 10.5.15.127 (10.5.15.127) 163.061 ms 163.027 ms 163.010 ms
2 10.5.15.123 (10.5.15.123) 174.981 ms 174.967 ms 174.954 ms

Finally, in order to end your PPP connection you have to end the pppd daemon (gracefully); you might do this getting the pppd daemon PID and issuing a kill command:

olimpo:~# ps ax|grep pppd
10094 pts/1 S+ 0:00 grep pppd
29037 pts/1 S 0:00 pptp myvpnserver --nolaunchpppd
29041 pts/1 S 0:00 pptp myvpnserver --nolaunchpppd
29044 pts/1 S 0:00 pppd call MYVPN updetach

olimpo:~# cat /var/run/ppp0.pid
29044

olimpo:~/bin# kill -TERM 29044

olimpo:~/bin# tail --lines=11 /var/log/syslog
Jan 25 15:06:43 olimpo pppd[29044]: Terminating on signal 15
Jan 25 15:06:43 olimpo pppd[29044]: Modem hangup
Jan 25 15:06:43 olimpo pppd[29044]: Connect time 122.5 minutes.
Jan 25 15:06:43 olimpo pppd[29044]: Sent 1140 bytes, received 1056 bytes.
Jan 25 15:06:43 olimpo pptp[29041]: anon log[callmgr_main:pptp_callmgr.c:258]: Closing connection (shutdown)
Jan 25 15:06:43 olimpo pptp[29041]: anon log[ctrlp_rep:pptp_ctrl.c:251]: Sent control packet type is 12 'Call-Clear-Request'
Jan 25 15:06:43 olimpo pptp[29041]: anon log[call_callback:pptp_callmgr.c:79]: Closing connection (call state)
Jan 25 15:06:44 olimpo pppd[29044]: Connection terminated.
Jan 25 15:06:44 olimpo avahi-daemon[1488]: Withdrawing workstation service for ppp0.
Jan 25 15:06:44 olimpo NetworkManager[1919]: SCPlugin-Ifupdown: devices removed (path: /sys/devices/virtual/net/ppp0, iface: ppp0)
Jan 25 15:06:49 olimpo pppd[29044]: Exit.

olimpo:~# ps ax|grep pppd
11587 pts/1 S+ 0:00 grep pppd

More information:

The Point-to-Point Protocol