Running Oracle’s CSSCAN utility as sysdba

Posted on 2010-04-10 by Earl C. Ruby III

If you need to run Oracle’s CSSCAN utility to check a database prior to converting to a new character set, you may have problems getting it to run “as sysdba”. There are a number of articles on the web that explain how to do this, and most of the explanations don’t work.

Oracle.com says that you should use:

CSSCAN /AS SYSDBA FULL=Y

This gives the error:

LRM-00108: invalid positional parameter value 'SYSDBA'                         
failed to process command line parameters                                      

Scanner terminated unsuccessfully.

Others recommend:

csscan "SYS/[password] as sysdba" full=y tochar=AL32UTF8 array=1024000 process=16

Which results in:

LRM-00108: invalid positional parameter value 'as'                                                  
failed to process command line parameters                                                           

Scanner terminated unsuccessfully.

Various other guides on the web also fail to work. Trying to run csscan as “SYSTEM”, as suggested by Tech Republic and Oracle UK, you get the error:

CSS-00113: user system is not allowed to access data dictionary

Trying to run csscan as “SYS”, as suggested by Oracle Forums, results in the error:

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

ORACLE-BASE shows a method for running csscan in a Windows environment:

C:\>CSSCAN \"sys/password@db10g AS SYSDBA\" FULL=Y

This might work in Windows, on a Linux server you get:

CSS-00110: failed to parse userid
Scanner terminated unsuccessfully.

Here’s how you do it. Log in as a user with DBA rights and do the following:

> csscan full=y tochar=AL32UTF8 array=1024000 process=16

Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Sat Apr 10 18:41:37 2010

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

Username: sys/[password] as sysdba

That should run the database character scanner on your database. If you get the error:

CSS-00107: Character set migration utility schema not installed       

Scanner terminated unsuccessfully.

… then go back and install the character set migration utility schema, then try running csscan again. You can install the character set migration utility schema with:

> cd $ORACLE_HOME/rdbms/admin
> sqlplus /nolog                                         

SQL*Plus: Release 10.2.0.2.0 - Production on Sat Apr 10 18:40:04 2010

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

SP2-0640: Not connected
18:40:04 nolog> connect / as sysdba
Connected.                         

Session altered.

Elapsed: 00:00:00.00
18:40:08 sys> @csminst.sql

If you get this error you can ignore it:

grant READ on directory log_file_dir to system                                                             
                        *                                                                                  
ERROR at line 1:                                                                                           
ORA-22930: directory does not exist

Hope you find this useful.

Adding an external encrypted drive with LVM to Ubuntu Linux

Posted on 2010-02-10 by Earl C. Ruby III

I recently added an external eSATA drive to my home computer so I could back up critical data from my home network to one drive. I bought a Western Digital 1TB “green” drive and a Thermaltake external hard drive enclosure with eSATA and USB connectors.

Since my internal hard drives are encrypted it didn’t make sense to back up all of that data to an unencrypted external drive. I’d read Uwe Hermann’s excellent how-to article on disk encryption, but he didn’t cover setting up an LVM partition, which I always use so I can change drive volume sizes on the fly.

This is what I did to set up an external encrypted drive with LVM on an Ubuntu system:

Open a terminal
Get a root prompt:
```
sudo /bin/bash
```
Watch the system log:
```
tail -f /var/log/messages
```
Attach the external drive. The system log tells me that it was detected as /dev/sdc.
Check the drive for bad blocks (takes a couple of hours):
```
badblocks -c 10240 -s -w -t random -v /dev/sdc
```
Write random data to the entire drive. This step takes all night, but it ensures that never-written drive space can’t be differentiated from encrypted data if someone ever tries to crack the drive. (If you’re going to do this, you might as well do it right.)
```
shred -v -n 1 /dev/sdc
```

Create one big LVM partition on the drive using fdisk. Set up one big primary partition /dev/sdc1, set the tag to system id “8e” LVM, and write the changes to disk:

> fdisk /dev/sdc                                                                                                                                              
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel                                                                                                  
Building a new DOS disklabel with disk identifier 0xa6846916.                                                                                                                       
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 121575.
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): p                                                                                                                                            
                                                                                                                                                                                    
Disk /dev/sdc: 999.9 GB, 999989182464 bytes                                                                                                                                         
255 heads, 63 sectors/track, 121575 cylinders                                                                                                                                       
Units = cylinders of 16065 * 512 = 8225280 bytes                                                                                                                                    
Disk identifier: 0xa6846916                                                                                                                                                         
                                                                                                                                                                                    
   Device Boot      Start         End      Blocks   Id  System                                                                                                                      
                                                                                                                                                                                    
Command (m for help): n                                                                                                                                            
Command action                                                                                                                                                                      
   e   extended                                                                                                                                                                     
   p   primary partition (1-4)                                                                                                                                                      
p                                                                                                                                                                  
Partition number (1-4): 1                                                                                                                                          
First cylinder (1-121575, default 1): [ENTER]                                                                                                                      
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-121575, default 121575): [ENTER]
Using default value 121575

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): p

Disk /dev/sdc: 999.9 GB, 999989182464 bytes
255 heads, 63 sectors/track, 121575 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk identifier: 0xa6846916

   Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1      121575   976551156   8e  Linux LVM

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

Calling ioctl() to re-read partition table.
Syncing disks.

Use cryptsetup to encrypt the drive:

cryptsetup --verbose --verify-passphrase luksFormat /dev/sdc1

Unlock the drive:

cryptsetup luksOpen /dev/sdc1 backupexternal

Create the LVM physical volume:
```
pvcreate /dev/mapper/backupexternal
```

Create the LVM volume group:

vgcreate xbackup /dev/mapper/backupexternal

Create a logical volume within the volume group:
```
lvcreate -L 500G -n backupvol /dev/xbackup
```
At this point you have a device named /dev/xbackup/backupvol, so create a filesystem on the logical volume:
```
mkfs.ext4 /dev/xbackup/backupvol
```

Mount the volume:

mount /dev/xbackup/backupvol /mnt/backup

To get the volume to mount automatically at boot time add this line to your /etc/fstab file:
```
/dev/xbackup/backupvol      /mnt/backup     ext4    defaults        0 5
```
To be prompted for the decryption key / passphrase at boot time first get the drive’s UUID:
```
ls -l /dev/disk/by-uuid
```
(In my example I use the UUID for /dev/sdc1)

Then add this line to the /etc/crypttab file:

backupexternal UUID=[the UUID of the drive] none luks

That’s it. You now have an external, encrypted hard drive with LVM installed. You’ve created one 500GB volume that uses half the disk, leaving 500GB free for other volumes, or for expanding the first volume.

Hope you find this useful.

Pidgin, purple-remote, and solving the “No existing libpurple instance detected” error

Posted on 2009-11-21 by Earl C. Ruby III

I was Googling around tonight to find an answer to a question I was asked about purple-remote and duplicate status messages in the Pidgin Instant Messaging Client and I ran across many, many people posting messages that they couldn’t get purple-remote to work. They had no problem running purple-remote from a command line, but as soon as they tried to call it from a web page or a cron job or from some other process they started seeing the “No existing libpurple instance detected” error.

The solution to this problem is the same as the solution to my original Update Pidgin IM status on Ubuntu using cron article. purple-remote communicates with Pidgin using DBUS. If you’re running purple-remote from a cron job or from some a process that is not owned by you (for instance, an Apache CGI script being run by the “apache” user) then you need to set the DBUS_SESSION_BUS_ADDRESS environment variable for that process so that it matches the DBUS_SESSION_BUS_ADDRESS environment variable for your login. Once that’s done, any processes run by that user that communicate using DBUS will use the same DBUS session that you’re using, so when the “apache” user runs purple-remote it’ll talk to the copy of Pidgin that you are running.

There are many ways you can disclose the value of your login’s DBUS_SESSION_BUS_ADDRESS value to other programs. The easiest way I’ve found is just to save the value to a file and then have your cron job or Apache CGI script execute that file as a script. Check out the Update Pidgin IM status on Ubuntu using cron article for examples of exactly how to do this.

One-line search and replace command using Perl

Posted on 2009-09-21 by Earl C. Ruby III

I’ve been using these one-line commands for over a decade. They’re a really easy way to do a search and replace across many files using a Perl regular expression.

This example replaces all instances of the word “old” with the word “new” in “myfile”, saving the old file version as “myfile.bak”:

# perl -i.bak -pe 's/old/new/g;' myfile

This replaces all instances of the word “old” with the word “new” in all of the .html files in the current directory:

# perl -i.bak -pe 's/old/new/g;' *.html

This does the same thing, but also updates all .html files in all subdirectories:

# find . -name '*.html' -exec perl -i.bak -pe 's/old/new/g;' {} \;

Hope you find this useful.

Add an entire directory tree to CVS

Posted on 2009-09-21 by Earl C. Ruby III

A web application project I’m working on uses YUI widgets for the user interface and CVS as the version control repository. We needed to keep separate versions of YUI around for development, testing and production, so we separate the versions by their release numbers.

Adding a new YUI build tree to CVS means adding several hundred new files to CVS. Unlike Subversion or Git, if you add a directory to an existing CVS project CVS doesn’t automatically add all of the subdirectories and files. If you try to add all of the files in a subdirectory using “*” you will also get the CVS directory that CVS uses to store information about the directory’s contents, which will generate warning messages from CVS until the end of time.

If you combine the “find” command with “cvs add” you can add all of the subdirectories and files with one command, skipping all of the CVS subdirectories. The syntax is a little arcane, so I thought it was worth posting here.

For the initial setup assume that I have an existing CVS project “apache” with a directory “js”. I add the directory ~/apache/js/yui to my local copy of the project, copy the YUI zip file into the directory, and unzip it. Unzipping it creates the directory “yui” (in this case ~/apache/js/yui/yui) but since I want separate versions of each copy of YUI I rename the directory and call it “2.8.0r4”. Then I add the new directories to CVS.

# mkdir ~/apache/js/yui # cp yui_2.8.0r4.zip ~/apache/js/yui/ # cd ~/apache/js/yui # unzip yui_2.8.0r4.zip # mv ~/apache/js/yui/yui ~/apache/js/yui/2.8.0r4 # cd ~/apache/js # cvs add yui # cvs add yui/2.8.0r4

I want to add the directory ~/apache/js/yui/2.8.0r4/build to CVS as well, but I don’t want to add the “~/apache/js/yui/2.8.0r4/examples” or “~/apache/js/yui/2.8.0r4/docs” directories, just ~/apache/js/yui/2.8.0r4/build, so I use a “find” command that specifies just the “build” directory and I prune out any directories containing the name “CVS” from the results returned by find:

# cd ~/apache/js/yui/2.8.0r4/ # for f in `find ./build -name CVS -prune -o -print | sort`; do cvs add $f; done # cvs ci -m "Adding YUI v2.8.0r4 to the CVS repository"

Piping find’s results to the “sort” command puts the files in order, so directories always get added before the files that they contain.

To use this technique for your own directories, just go to the parent of the directory you just added and use the above “find” command, just substitute the name of your new directory for “./build”.

A side note: We add a symlink that points to the version we want to use at the moment so that the application code can be run with whichever version we want to test with:

# cd ~/apache/js/yui # ln -sf 2.8.0r4 current

A script tag in the application would look like this:

<script src="/js/yui/current/build/utilities/utilities.js" type="text/javascript" />

Hope you find this useful.