ORA-27040: file create error, unable to create file

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It's not the first time i write about the stack of errors returned by data pump operations and ORA-31641 in particular, however the error messages may vary slightly with the operating system. For instance the following error stack is returned on XE running on top of Ubuntu, whereas the previously linked page refers to an error condition occurring on XE for Windows.

expdp user/pwd directory=EXPDIR dumpfile=dump.dp tables=emp,dept nologfile=Y

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/home/flavio/documents/dump.dp"
ORA-27040: file create error, unable to create file
Linux Error: 13: Permission denied

The problem is caused by the missing privileges of user oracle on the directory pointed to by EXPDIR.
EXPDIR is pointing to /home/flavio/documents.

ls -ld /home/flavio/documents

drwxr-xr-x 4 flavio flavio 4096 2009-04-10 19:00 /home/flavio/documents/

As you see the directory belongs to user flavio, group flavio, moreover no write privileges are granted other than to the directory owner. Now, as Oracle processes are running under the user oracle, in order to be able to dump a file in this directory we have two possibilities:

1. to change the other group permissions on the directory i.e. by executing chmod o+w documents.
   
2. to add user oracle to group flavio and grant write permissions on documents to group flavio by executing sudo usermod -a -G flavio oracle; chmod g+w documents.

Note that option #1 will work instantly while option #2 requires a db restart. If you do not restart the db, you will still get the permission denied error. In either case, the db user must have READ and WRITE permissions on EXPDIR or hold CREATE ANY DIRECTORY privilege.

Whether the optimal solution is either of the two options above it really depends on the application security requirements.
In the end may be it's better to create a public directory where everybody can dump files freely or create a shared directory EXPDIR where only oracle can create files and users belonging to a datapump group can read them. This approach avoids tampering with the existing permissions on user's private folders and it doesn't require a db restart.

Final notes: while looking for the Optimal Flexible Architecture document, i realized that dpdump is nowhere to be found in the installation documents through version 11gR1. This directory is created at time of installation and it is an OFA directory, no matter what the books say or don't. Directory object DATA_PUMP_DIR, which is the default directory for data pump operations, indeed maps to dpdump.
If i remember correctly in 10gR2 the directory object is automatically created at installation time whilst in 10gR1 you need to create it manually.

See message translations for ORA-27040 and search additional resources.

LD_LIBRARY_PATH is not set! in alert_XE.log

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This morning i discovered the following messages in the alertXE.log, located in the bdump folder of a xubuntu virtual machine:

Fri Apr 10 15:01:15 2009
Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for /proc/self/exe
Unable to find dynamic library libocr10.so in search paths
RPATH = /ade/aime1_build2101/oracle/has/lib/:/ade/aime1_build2101/oracle/lib/:/ade/aime1_build2101/oracle/has/lib/:
LD_LIBRARY_PATH is not set!
The default library directories are /lib and /usr/lib
Unable to find dynamic library libocrb10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
...

After a quick search on the web, i read this posting from Mohammad, so i concluded i was not the first one to see this error...
He suggests to set the variable LD_LIBRARY_PATH in the .bash_profile or .profile of the oracle user, however i preferred to change the main startup script located in /etc/init.d containing the settings for most of the Oracle variables, as shown in the image:



After fixing oracle-xe, the alert_XE.log file doesn't show anymore the warning messages:

Fri Apr 10 15:06:43 2009
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
...

Someone may argue that changing the script does not guarantee against future installations (or re-installations), however hopefully Oracle will fix the bug in the meantime, so Oracle 11XE will not be affected by this flaw.

In the end i'd be curious to know which functionality was affected by the bug because this Oracle instance has been running for quite some time without showing any particular problems.

How to increase the disk space on a xubuntu VM

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Although this problem is not strictly oracle related, it may be useful for anyone who needs to increase the available disk space on a xubuntu (and ubuntu as well i guess) vmware virtual machine where an Oracle instance is running.

The following procedure has been carried out on VMWare Fusion 2.0 (for Apple Macs platforms).

Original problem: i had a rather small xubuntu VM with 1GB RAM and 8GB (7GB+1GB swap disk) and i wanted to increase the space of both sda1(the main partition) and sda5 (swap partition) up to a total of 32GB (28GB for sda1 and 4GB for sda5).

Before starting i recommend that you make a copy of your VM files, just in case and of course the usual disclaimers apply.

1. Increase the virtual disk space setting to the desired amount in the configuration panel of the VM. This operation is currently allowed only if there are no VM snapshots, so, you'll have to delete them first;
   
2. Download gparted;
3. Use the gparted ISO image as a virtual CD in VMWare or burn a physical CD using the file image;
4. Reboot xubuntu with the CD inserted and connected;
5. If your VM doesn't boot from CD, then you need to change the device boot sequence in the BIOS. Restart the VM and keep pressing F2 until the BIOS screen shows up.
   
6. Resize and/or move the existing partitions until you get the desired size for all the partitions and apply the changes;
   
7. Eject the CD and restart the VM normally;
8. By changing the partition properties of the swap area, most likely the existing UUID of the partition has changed as well: verify with the command sudo blkid the current UUID of the swap partion and compare it with the UUID stored in /etc/fstab;
9. If the two UUIDs are different, perform the following three additional steps:
1. sudo swapoff -U old_UUID where old_UUID is the value contained in /etc/fstab;
2. update /etc/fstab by executing: sudo vi /etc/fstab in order to update the file (copy and paste the new UUID from the output of blkid);
3. perform: sudo swapon -a;
10. If no errors are returned, you should be able to see the updated amount of available memory with the command: free -m;

The procedure worked out well in my case and i completed all the tasks in less than 30 minutes.
Actually it took me a while to figure out initially what was wrong with the command swapon -a, because i was getting the somewhat funny message "cannot canonicalize /dev/disk/by-uuid/...", at that point i realized that i had to update the UUID in /etc/fstab.

Shorter solutions are welcome :-)