Openoffice calc – Insert date on keypress

My programmers editor ( UES ) has an F7 action that inserts the current date and time at the current cursor position. I’ve been looking for a similar action so that I can maintain my project management and planning spreadsheets, but surprisingly it’s quite complicated to get such a simple activity in calc.

The code snipet below is essentially a rip off from a blog I read by Andrew Pitonyak who describes creating the following procedure as an Openoffice basic implementation then attaching the macro execution to a keyboard shortcut.

I attached the execution of this procedure to my F7 key in Calc by modifying the F7 definition ( tools -> customize -> keyboard tab -> category=Openoffice macro – mydocument-mymodule -> function=InsertDateIntoCell ). This definition may seem a little complicated to follow, but it will make sense when you navigate through the keyboard configuration yourself!

What makes this code special is I wanted my F7 keypress to insert the date and format the cell to be “Day Month Day hour minute” which is OpenOffice cell style is “NN MMM DD HH:MM”. I found an example in the OpenOffice API examples and virtually copy pasted their solution into this example.

Now, when you press F7 the current date and time is inserted into the active cell which is also formatted as discussed above.


sub InsertDateIntoCell
Dim oDesktop As Object, oController As Object, oSelection As Object
Dim doc As Object

Dim NumberFormats As Object
Dim NumberFormatString As String
Dim NumberFormatId As Long
Dim LocalSettings As New com.sun.star.lang.Locale

oDesktop = createUnoService("com.sun.star.frame.Desktop")
oController = oDesktop.CurrentFrame.Controller
doc = oController.Model

If doc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then
oSelection = oController.Selection
'Set the date value
oSelection.Value = now()
' ----------------------------------------------------------
' MLH for now, configure cells to : NN MMM DD HH:MM
' ----------------------------------------------------------

LocalSettings.Language = "en"
LocalSettings.Country = "gb"

NumberFormats = Doc.NumberFormats
NumberFormatString = "NN MMM DD HH:MM"

NumberFormatId = NumberFormats.queryKey(NumberFormatString, LocalSettings, True)
If NumberFormatId = -1 Then
NumberFormatId = NumberFormats.addNew(NumberFormatString, LocalSettings)
End If
oSelection.NumberFormat = NumberFormatId

Else
MsgBox "This macro must be run in a spreadsheet document"
End If
end sub


Book Review: Oracle Information Integration, Migration, and Consolidation

Visit the publishes website or Buy online from Amazon

Reads like an Oracle product catalog with the marketing rubbish removed and the interesting technical details added.

If you are an Oracle specialist working as a DBA, Developer, Technical architect or a manager of legacy systems, if you need to design or re-engineer a company application that will be executing traditional data loads, if your customers demand a responsive user experience, if your design needs to keep operating costs down, if your design needs to scale out or scale up, then start reading the book today.

This is not an Oracle technical manual, it will not advise you how to develop or implement a single line of code. But in 280 pages of enjoyable reading this book will informatively introduce you to the entire array of Oracle data integration solutions, how they relate to each other, when you should expect to use them, and compares and balances the virtues of the solutions.

Like myself, most database administrators and technical architects struggle to find the time to get familiar with Oracle’s frighteningly large product catalog and really understand how each of the solutions relate to each other and when they could be used. Published in September 2011 this book really is up to date, the authors suggest when to use Golden Gate instead of streams or Data Guard. Should you consider RAC or go for Exadata. Advises how to get out of mainframes or legacy systems and onto today’s technology. Suggests how to organise business process execution instead of batches runs, Advises on your choice of off the shelf applications. Finally and this is a problem with every client I’ve ever worked with, how to start getting rid of your ancient performance killing data then how to keep life-cycle managing the data.

If I had a single criticism of this book, it would be that I didn’t laugh once, there’s not a single joke or witty comment, but it is clearly and intelligently written.

I’m a self employed Oracle DBA consultant with 15 years experience in UK blue chip businesses.

Linux IP Tables

Learning IP Table configuration takes loads of reading time spent studying example configurations, so I thought it would be useful to add a very simple example of setting up a firewall on my Linux server farm.

This example is very simplistic, but instructive, so consider that I have a small local area network on the private 192.168.1.0 range, and I want to restrict access my database hosts to

• SSH on port 22
• Oracle clients on port 1521
• Webserver on port 7777
• any inbound connection from 10.1.1.0 private network
• Forwarding DNS server on my local network
• ICMP allowed from local network
• ICMP only allowed from controlled list of Internet based machines

As my database server is a clustered host, that is I have two or more Linux computers that are clustered and share cache fusion information on the private 10.1.1.0 network, in fact, I have a private wired network operating on the 10 network that is physically secured in the machine room with the host computers, which means I am happy to accept any inbound port request from any source on the 10 network.

My preference is to lock down the 10 network and only open the specific ports I am interested in, however the software product that runs over the 10 network uses a lot of ports and I’ve not reached a definitive list of ports that can be opened, so for now, I’m opening all ports.

On each on my Linux servers, I run the following batch script which configures the standard firewall security for my organisation’s internal network.  Note first 4 iptables executions removes existing firewall configuration, that is it baselines the firewall to fully locked down, the subsequent iptables executions relax the firewall so that acceptable levels of inbound traffic and allowed.

Note that I have allowed outbound traffic from the Linux servers, but in a controlled environment I would definitely reconsider this choice as data extraction is very much easier if the hacker configures automated jobs to exfiltrate data automatically via email and database to database transfers.

So, here’s my example firewall configuration script.

#!/bin/bash
# All business LAN traffic on private network range 192.168.1.1->192.168.1.255

# Flush all current rules from iptables
iptables -F

#---------------------------------------------------------------
# Set default policies for INPUT, FORWARD and OUTPUT chains
#---------------------------------------------------------------

iptables -P INPUT DROP
iptables -P FORWARD DROP
iptables -P OUTPUT DROP

#---------------------------------------------------------------
# Loopback activity is okay
#---------------------------------------------------------------
iptables -A INPUT -i lo -s 0/0 -d 0/0 -j ACCEPT

#---------------------------------------------------------------
# eth0 company network
# ssh, 1521(tns), 7777(ocfs2)
#---------------------------------------------------------------
iptables -A INPUT -j ACCEPT -p tcp -i eth0 -s 192.168.1.0/24 \
  -m multiport \
  --dport ssh,1521,7777

#---------------------------------------------------------------
# eth0 company network
# allow all from firewall to local network
#---------------------------------------------------------------
iptables -A OUTPUT  -j ACCEPT -p all -d 192.168.1.0/24 -o eth0


#---------------------------------------------------------------
# eth1 cluster interconnect traffic - allow all
#---------------------------------------------------------------
iptables -A INPUT   -j ACCEPT -p all -s 10.1.1.0/24 -i eth1
iptables -A OUTPUT  -j ACCEPT -p all -d 10.1.1.0/24 -o eth1


#---------------------------------------------------------------
# eth0 internet traffic from the firewall
#---------------------------------------------------------------

iptables -A OUTPUT -j ACCEPT -m state \
  --state NEW,ESTABLISHED,RELATED -o eth0 -p tcp \
  -m multiport \
  --dports http,https,nntp

#---------------------------------------------------------------
# DNS - IP is my local forwarding DNS
#---------------------------------------------------------------

iptables -A INPUT -p udp -s 192.168.1.254 --source-port 53 --destination-port 1024:65535 -j ACCEPT


#---------------------------------------------------------------
# ICMP anywhere in company network
#---------------------------------------------------------------

iptables -A INPUT  -j ACCEPT -p icmp -i eth0 -s 192.168.1.0/24 # allow local machines to ping me
iptables -A OUTPUT -j ACCEPT -p icmp -o eth0 -s 192.168.1.0/24 # allow ping out to world

iptables -A INPUT  -j ACCEPT -p icmp -i eth0 -s news.bbc.co.uk # ping in
iptables -A INPUT  -j ACCEPT -p icmp -i eth0 -s www.openfieldsolutions.co.uk # ping in


# Save the configuration changes to make them permanent
service iptables save

# List the configuration tables
iptables -L -n -v

VAT reference number checks

Why do we check VAT Numbers?

• HMRC tell us that we should.
• The main reason that we seek to check out VAT numbers is to make sure that the VAT we are being charged by our supplier is correctly charged so that we need have no fear about reclaiming that VAT on our VAT returns.
• And the VAT we charge to our customers does not end up forming part of a VAT ponti scheme that lands us in court with only the claim of ignorance as our defence.

Thankfully, there are several on-line VAT reference number validation tools available.

1. The first is provided by the European commission and can be used to validate any VAT number registered in the Europe wide area: http://ec.europa.eu/taxation_customs/vies/
2. The HMRC themselves: http://www.hmrc.gov.uk/tools/payinghmrc/vat-ref-checker.htm
3. Finally, the BBC describe how the VAT reference number check digit pair can be calculated and reference back to a real VAT number.  Of course, this is just for information because a VAT number could be regenerated that has not been issue by a European VAT body.  http://www.bbc.co.uk/dna/h2g2/A10287461

Tainted Kernel

A tainted kernel relates to proprietary modules written by vendor/s that have not released the binary module with a GPL license.  For example a company may release a kernel module and keep control of the intellectual property, which implies they have probably kept the module source code secret and not released the source to the Kernel development community.

Certain software vendors, Oracle Corporation for example, will state that they cannot provide comprehensive platform support to a customer if that customer is using a tainted kernel.  This is a true and honest statement because many of Oracle’s solutions are complex and require open access to the Kernel software.

If you are asked to identify whether you are running a tainted kernel you can work through the following notes.

First of all take a look at

/proc/modules
/proc/sys/kernel/tainted

The process information pseudo-file system maintains a file called tainted, see

cat /proc/sys/kernel/tainted

which should return 0, any value greater than 0 represents the number of tainting modules.

Review the kernel with lsmod which will list the general taint state of the kernel and list the module causing the tainted state.

lsmod

and check the taint flags.

If a module is tainting the kernel, the module can be reviewed using

modinfo modulename

Take a look at the Linux Kernel mailing list

Installing ASM lib

This blog is a quick look at ASM in a Linux environment. For the record, I’m using Oracle Enterprise Linux (OEL) 4.8 on a basic kernel with no package or kernel updates. My OEL installation is running in a Virtual Machine.

Item of most interest to me is the installation of the ASM kernel driver because I discovered that Oracle do not include the base release ASM drivers for an OEL release version. For example, you will not find the ASM kernel driver for OEL 4.8 on the Technet website, however you will find the ASM kernel driver contained in the RPM package folder of the CD or DVD iso image; this is discussed in more detail below.

The basic steps I intended to discuss below are

• Split a disk into two partitioned devices; hdc1 and hdc2.
• Install the support and lib packages.
• Download and install ASM lib and ASM support packages.
• Install the ASM Kernel driver from the CD or DVD iso image.
• Configure the ASM disk manager.
• Allocate and label the hdc1 and hdc2 devices for ASM disk management.

Split a disk into two partitioned devices; hdc1 and hdc2.

Partition the hdc disk device into two partitions containing an equal number of disk blocks.

[root@OEL48 ~]# fdisk /dev/hdc

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 41610.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-41610, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-41610, default 41610): 20805

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 2
First cylinder (20806-41610, default 20806):
Using default value 20806
Last cylinder or +size or +sizeM or +sizeK (20806-41610, default 41610):
Using default value 41610

Command (m for help): p

Disk /dev/hdc: 21.4 GB, 21474836480 bytes
16 heads, 63 sectors/track, 41610 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes

Device Boot Start End Blocks Id System
/dev/hdc1 1 20805 10485688+ 83 Linux
/dev/hdc2 20806 41610 10485720 83 Linux

Command (m for help): w
The partition table has been altered!

 

Calling ioctl() to re-read partition table.
Syncing disks.
[root@OEL48 ~]#


Inform the OS of partition table changes.

partprobe


List the disk devices and partitions just to satisfy our own curiosity.

[root@OEL48 ~]# sfdisk -l

Disk /dev/hda: 20805 cylinders, 16 heads, 63 sectors/track
Warning: The partition table looks like it was made
for C/H/S=*/255/63 (instead of 20805/16/63).
For this listing I'll assume that geometry.
Units = cylinders of 8225280 bytes, blocks of 1024 bytes, counting from 0

Device Boot Start End #cyls #blocks Id System
/dev/hda1 * 0+ 12 13- 104391 83 Linux
/dev/hda2 13 1304 1292 10377990 8e Linux LVM
/dev/hda3 0 - 0 0 0 Empty
/dev/hda4 0 - 0 0 0 Empty

Disk /dev/hdc: 41610 cylinders, 16 heads, 63 sectors/track
Units = cylinders of 516096 bytes, blocks of 1024 bytes, counting from 0

 

Device Boot Start End #cyls #blocks Id System
/dev/hdc1 0+ 20804 20805- 10485688+ 83 Linux
/dev/hdc2 20805 41609 20805 10485720 83 Linux
/dev/hdc3 0 - 0 0 0 Empty
/dev/hdc4 0 - 0 0 0 Empty
[root@OEL48 ~]#


Download and install ASM lib and ASM support packages

Visit Oracle Linux technology pages, navigate to the downloads section, then ASMlib, then choose your operating system vendor ( Redhat, Suse and so on ). Select the operating system version.

The web address you need is at Oracle

Get the ASM support and lib RPM package appropriate to your Linux vendors and version.

I’m running OEL 4.8 which is an Oracle clone and rebadge of Redat Enterprise Linux 4.8. I don’t have an Oracle support contract so cannot use any of the excellent Oracle up2date or oracleasm kernel driver matching facilities available after the ASM support package is installed.

So, moving on, download the ASM support and ASM lib packages.

wget http://oss.oracle.com/projects/oracleasm-support/dist/files/RPMS/rhel4/amd64/2.1.4/oracleasm-support-2.1.4-1.el4.x86_64.rpm
wget http://download.oracle.com/otn_software/asmlib/oracleasmlib-2.0.4-1.el4.x86_64.rpm


Install the support and lib packages


rpm -Uvh oracleasm-support*.rpm \
oracleasmlib-*.rpm


Install the ASM Kernel driver from the CD or DVD iso image

If your kernel has not been updated and is running the Linux distribution base version release, then you can get the ASM Kernel driver from the distribution CD or DVD image. As follows:

As I already mentioned, I’m running OEL 4.8 within a virtual machine, so I attached the OEL 4.8 DVD iso image to the “IDE Controller” which associates the iso with the controller, all that remains is for the DVD to be mounted within the running virtual machine.

Log into the OEL 4.8 machine as root and mount the DVD ISO image and make change the current folder into the DVD.


su - root
password

mount /dev/cdrom


Move to the DVD folder that contains the ASM RPM package.


cd /media/cdrom/Enterprise/RPMS


Confirm to yourself the ASM Kernel driver is on the DVD.


ls oracleasm-`uname -r`*.rpm


Install the ASM kernel driver.


rpm -Uvh oracleasm-`uname -r`*.rpm


Simples, the kernel driver should now be installed.

Lets get a peek at the current ASM version.

oracleasm -V


Configure the ASM disk manager


/etc/init.d/oracleasm configure


Check that enabling, restarting and checking the status functions work.

/etc/init.d/oracleasm enable
/etc/init.d/oracleasm restart
/etc/init.d/oracleasm status


Allocate and label the hdc1 and hdc2 devices for ASM disk management

Scan all the ASM disks.

/etc/init.d/oracleasm scandisks


And list the disks. Of course there aren't any disks yet, but you get the idea.

/etc/init.d/oracleasm listdisks


After all this work, we can now create the two ASM disk discussed above, the first disk will be the DATA disk which will contain the database files, the second disk FRA will be for the database Faster Record Area - the FRA used to stand for flashback recovery area but this was quietly renamed by Oracle during 2010.

/etc/init.d/oracleasm createdisk DATA /dev/hdc1
/etc/init.d/oracleasm createdisk FRA /dev/hdc2


List the disks the ASM lib know about, which should simply be the DATA and FRA.


/etc/init.d/oracleasm listdisks


Another quick check that ASM lib is alive and kicking.


/etc/init.d/oracleasm status


 

Using the Microsoft Terminal Service Client (MSTSC) to connect to an Oracle Virtualbox guest machine

These notes quickly demonstrate configuring a Virtual Box guest machine so that it directs VM output to the RDP destination.

If you want to manage your Virtual box hosts and guest machines remotely, perhaps over an SSH connection as I do, then the following notes may be of some help.

Connect to the Virtualbox host machine using SSH

I generally use Putty as my ssh client.

ssh 192.168.1.160

Once you have the host shell, set up some environment variables

The virtual host machine IP address

VMhostADDR=192.168.1.160

The virtual host virtual machine guest name.  Too many virtual words here but I think the sentence makes sense.

VMname=vmt.OEL4.8

Modify the guest configuration so the RDP is enabled and listening on port 3391 of the host IP.

VBoxManage modifyvm ${VMname} --vrdeextpack default
VBoxManage modifyvm ${VMname} --vrde on

VBoxManage modifyvm ${VMname} --vrdeport 3391
VBoxManage modifyvm ${VMname} --vrdeaddress ${VMhostADDR}

Start the guest machine with output directed to the RDP interfaces.

VBoxHeadless --startvm ${VMname}

On the client machine which in my case is a laptop, fire up the RDP viewer and try to connect to the server.  I like to use the standard Microsoft RDP viewer MSTSC which can be launched as follows.

mstsc /v:192.168.1.160:3391

Please note that at any one time, only one RDP client can connect to a port.  If you need to have several adminstrators working on the guest machine using RDP you can add additions ports to the vrdeport configuration above, either a comma separated ( 3391,3392,3393 ) is acceptable or a range list ( 3391-3393 ).

One of the most common reasons for mstsc not connecting to the guest machine is due to the host machine firewall rules excluding inbound network access on the ports you defined earlier, 3391 in this case.  To resolve this issue, log onto the host machine and add 3391 as a used defined port in the firewall.  Use the GNOME GUI application rather than attempting to modify IP Table manually.