Any ideas of how I would sell this to my significant other?

http://blag.xkcd.com/2007/11/19/growing-up/

oracle datapump, because there is no better one

In the last couple of days I have been busy with consolidating different databases into 2 big ones.
One of the challenges was that in the original databases there where a lot of objects in the “users” and “indx” tablespace (and the other tablespaces had no real naming convention either). To make it more fun, a high number of tables used lob datatypes.
(When you have a table with a lob datatype and one of the referenced tablespaces does not exist, then the table create statement fail, instead of failing over to the default tablespace of the schema owner. This because imp can not determine which tablespace clause – that of the table itself or that for the lob – caused the error)

In the old pre-10g ages, I would have had the choice between a) create tablespaces with the same name in the consolidated database and later on move the objects to a new tablespace, or b) precreate the tables with lob datatypes and then do the import.
Both options would be time consuming and error prone, not to mention that the import utility was not very fast when it came to importing lobs.

Fortuanatly these databases to be consolidated where all 10gR2 databases, so I had a third option: use oracle datapump to move the schemas to the new database.
With datapump you have this very nice parameter: remap_tablespace, which allows you to simple specify the old and the new tablespaces name and that is all you have to do to change tablespace_names during the import.
And to make it realy fun: you can use the network_link parameter to specify a database link, which will then be used to transport data from the source db to the target db (by by intermediate file).

Of course there are some drawbacks.
First of all: it is a server based tool (or better said, expdp/impdp are tools written arround server packages). That is, all processing happens on the server and the dmp file will be placed/read on/from the server itself.
While this gives you the possibility to start the expdp or impdp, disconnect and then reconnect at home, it may provide a (security) problem to work with files on the server itself. On nx environments NFS may provide a solution, as people can just mount the nfs volume on their local pc’s and work with the dmp files as if they where stored locally.
A second disadvantage is that datapump may be slower then exp/imp in case you have just a small dataset, but a high number of (empty or almost empty) tables.
What I also found annoying, is that the flashback_time parameter (which is more or less replacing the old consistent parameter) does not accept a datastamp value. This means that you can’t simply specify flashback_time=”systimestamp” to tell datapump you want all objects to be consistent to each other, but instead you have the specify the following: flashback_time=”to_timestamp(to_char(systimestamp, ‘DD/MM/YYYY HH24:MI:SS.FF’), ‘DD/MM/YYYY HH24:MI:SS.FF’)”.

Oh, and of course there is a bug to look out for: 5874989, which saids that data corruption can occur during a datapump import with characterset conversion.
luckily a patch exists.

linux / unix kernel parameters follow-up

This is a follow-up note on my prior linux / unix kernel parameters post

Yesterday I received some feedback from oracle about why the core dump has a 4 GB limit and why you should therefore limit the shmmax to a maximum of 4GB:

” Normally on x86_64 a coredump is not limited to 4 GB, but a coredump is affected in different levels: namely memory, I/O and disk.
Most of the limitations are due to the filesystem used.
If the coredump routine in the kernel is not using the right kernel calls or the filesystem cannot handle files bigger than 4 GB (like reiserfs or some old ext2),
then the coredump will be affected.

For this reason, setting shmmax < 4GB on x86_64 is more a recommendation.”

After reading this I, closed the service request…

Blog recommendation

Just received a mail from Geert De Paep, the guy behind dba-village, that he as well has started a blog: http://geertdepaep.wordpress.com.
As Geert is a collegue and friend, I know he has a very profound knowledge of oracle in general and high-availability in particular, so his blog is a real must read.

His first article is one about the use of oracle streams to migrate and upgrade databases.

October cpu patch

Yesterday I installed the October cpu patch on one of our client’s system.
Now, I don’t have much experience with cpu patches, but it looked simple enough. Just download the latest version of opatch, adjust the path settings and then use the napply command to apply the patch:

[oracle@xxxxxxxxxx:/data/software/oracle/cpu/OCT2007/6394981]$ opatch napply -skip_subset -skip_duplicate
Invoking OPatch 10.2.0.3.3

Oracle interim Patch Installer version 10.2.0.3.3
Copyright (c) 2007, Oracle Corporation.  All rights reserved..

Setting N-Apply implicit patch base-directory to /data/software/oracle/cpu/OCT2007/6394981
UTIL session

Oracle Home       : /opt/oracle/product/10.2.0/db_1
Central Inventory : /opt/oracle/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.3.3
OUI version       : 10.2.0.3.0
OUI location      : /opt/oracle/product/10.2.0/db_1/oui
Log file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2007-11-23_11-05-24AM.log

Unable to lock Central Inventory.  OPatch will attempt to re-lock.
Do you want to proceed? [y|n]
y
User Responded with: Y
OPatch will sleep for few seconds, before re-trying to get the lock...

At this point opatch hung, unable to lock the inventory.
After some searching, I found that the locks directory in the oraInventory directory was not owned by oracle but oraclecl:

[oracle@xxxxxxxxxx:/opt/oracle/oraInventory]$ ls -l
total 30
drwxrwx---   2 oracle   oinstall     512 Nov 14 17:09 Contents
drwxrwx---   2 oracle   oinstall     512 Nov 11 20:25 ContentsXML
-rwxrwx---   1 oracle   oinstall      38 Nov 14 18:05 install.platform
drwxr-xr-x   2 oraclecl oinstall     512 Nov 14 18:11 locks
drwxrwx---   3 oracle   oinstall    1536 Nov 23 11:11 logs
-rwxrwx---   1 oracle   oinstall      59 Nov 11 20:25 oraInst.loc
-rwxrwx---   1 oracle   oinstall     299 Nov 14 18:02 oraInstaller.properties
-rwxrwx---   1 oracle   oinstall    1265 Nov 11 20:25 orainstRoot.sh
drwxrwx---   2 oracle   oinstall     512 Nov 11 20:14 oui
-rwxrwx---   1 oracle   oinstall    3336 Nov 14 18:02 sessionContext.ser

[oracle@xxxxxxxxxx:/opt/oracle/oraInventory]$ cd locks
[oracle@xxxxxxxxxx:/opt/oracle/oraInventory/locks]$ ls -l
total 0
-rw-r--r--   1 oraclecl oinstall       0 Nov 14 18:11 writer.lock

Now, this oraclecl user was used to install an oracle client (the system is a test system, where different parts of the application are installed on the same server), and used the same oracle_base and inventory location as the oracle database itself.
I fixed the problem and ran the opatch again.
Only to fail again, this time with the message that several make files could not be found….
At this time I was very happy that this was not a production system :)

Fortunately a quick look on the known issues part in the patch readme showed that the problem occured because the advanced security option was not installed in this oracle home and that it could be fixed by removing the 6397929 directory from the patch directory.
And sure enough, after deleting the directory I managed to install the patch without further problems.
Of course, why a cpu patch fails when a separate licensed option is not installed is an entirely different question.

Installing a cpu patch, easy enough, no?

Oracle VM: a first installation attempt

Today I had finally some time to play with oracle VM.
Since an installation attempt of a collegue already showed that you could not run oracle vm in a vmware (yes, you can take virtualisation a step to far), I had already reserved some hardware in our test environment (read: I had stolen some old pc’s),  but until now I did not had the time to actually install it.

Because two pair of eyes see more then one pair, and because installing a completly new piece of software is always fun, a collegue of mine (Geert De Paep) joined me on this install fest.

Although some steps took a long time to complete, probably due to the state of the art hardware that we where using, the installation of the vm server went rather smoothly. Because we did not bother to read the installation guide we got our first surprise: Oracle VM is using ocfs2 as file system for the images!
Later on, Geert came with an explanation for this: much like with vmware esx, you can build a server farm in which the nodes use a shared storage to access the virtual images, hence the need of a clustered file system.
Installing the management part on a second pc gave us some more problems, but after cleaning up the system (there was still an 11g oracle installed on this system) we could complete the installation without further problems (it is nice to see that oracle vm is actually using XE to store its data).

After this, the system was ready to use and after some trial and error ( and eventually start reading the documentation) we found the default username / password and managed to login to the management console.
Next step was to upload some iso images containing oracle enterprise linux. Because we did not have a ftp server available, we directly copied the iso files to the /OVS/iso_pool/install_images directory on the oracle vm server. According to the documention (which we now had in front of us), we needed to use “internal ISO” as source during the import steps. Unfortunately we could not select an iso group or label during the next step and thus not complete the import. At this moment we where pretty much stuck and we found no solution in the remaining time we had.

Great was my surprise when I tried the import again from home, and without changing anything I could now see the iso group in the drop down box (install_images) and the iso labels (the filename of the iso files). After some further testing it seems that you have to logout and login again before you can see changes (removing, adding) on the iso files, when these changes are done outside of the ovm managment console.

Well, time to go to bed now. Creating an ovm guest will have to wait until tomorrow.

dba 2.0

After reading the prodlife blog about the dba 2.0 presentation on oracle open world and Tom Kyte’s blog, I must concur that an oracle dba indeed need management tools to administer an environment (that is a mid-size or large environment). Databases are growing bigger and bigger and maintenance windows are disappearing as snow for the sun, so having tools to help you with managing the databases are very welcome.
This being said, I hate the ongoing trend in oracle presentations and courses to solely focus on managing databases via these tools. In fact they don’t show / learn you anymore how to manage oracle, but how to operate a particular management tool (read: grid control).
What are you going to do when the grid control is not reachable or when you need to manage a database that is not registered in grid control?

I am a consultant dba, what means that I manage database for various customers on different sites. Sometimes this management is done on site, but often it is done via remote access (sometimes having limited bandwith or requiring hopping from one server to another) . So in most occasions the only tool that I can use is sqlplus. And you know what? I love sqlplus! True, sqlplus is not a very convenient tool for browsing through records or to quickly update a single record, but it is available on every platform and combined with a repository of scripts (the true treasure box of an oracle dba) it is a very powerfull tool.
Using scripts also force you (unless you are just copying the scripts from the internet) to investigate in which views oracle is storing which data and how to join the different views together, giving you a better understanding of how oracle works.

It is as Tom wrote on his blog “Having the ability and knowledge to command line when necessary, along with using the tools day to day is the way forward I think”, but is is such a shame that currently often the only focus is on the tool.

linux / unix kernel parameters

I have a confession to make: the linux/unix kernel parameters have always been somewhat of a mistery to me and while I have installed several databases on linux and solaris systems I never realy understood how to tune them. I just followed the oracle installation documentation and metalink note 169706.1.

But recently I had to perform an installation on a 64bit linux system. The database in question was going to be used to develop a data warehouse system and the company asked to have at least a buffer cache of 4 GB. Now the box had 16 GB of ram so this requirement should not pose any problem.
So I started preparing the os for the installation and looked to metalink for the recommended kernel values. At that point I noticed that the value for the shmmax parameter on linux 64bit was stated as “max(ram in bytes, 4GB) / 2″ “max(ram in bytes/2, 4 GB * 1024^3)”. This limit of 4 GB seems a little bit strange to me for a 64bit system, so I posted a service request on this and started a quest for more information on the kernel parameters.

I found an excellent explanation here, but after reading it I started to ask myself more questions on the values recommended by Oracle in the installation guide and on metalink.
As I understand it, the shmmax parameter is supposed to be the maximum size of a single shared memory segments (and the oracle sga is build out of these shared memory segments). The shmmni parameter gives the maximum number of shared memory segments system wide and shmall gives the total amount of shared memory (expressed in pages).
If I combine the 3 definitions I understand that the maximum shared memory will be the minimum between shmmax * shmmni and shmall * page_size.

When I calculate the parameters with the values specified in metalink note 169706.1 (for my system with 16 GB ram) I get the following results:

shmmax = min(4294967296, RAM in bytes) /2 = min(4294967296, 17179869184)/2 = 4294967296/2 = 2147483648
shmmax = min(RAM in bytes / 2 , 4294967296) = min (17179869184 / 2, 4294967296) = 4294967296
shmmni = 4096
shmall = shmmax / page_size = 2147483648 / 4096 = 524288
shmall = shmmax / page_size = 4294967296 / 4096 = 1048576

Which would mean that on my system I get a maximum of 2 GB 4 GB of shared memory, and thus a maximum sga of 2GB 4 GB (actually less, because this limit is system wide and not only for my oracle instance). Needless to say that when you are needing a 64bit system, this 2 GB 4 GB limit is a little bit on the low side…

In metalink note 169706.1 there is, next to the shmall parameter, a link to another note (301830.1) which states to set the shmall parameter to “ram size / page_size” instead of shmmax / page_size. For my system with 16 GB of ram, this would give a shmall of 4194304 instead of 524288 and thus indeed fixing my problem.
Also if you would compare the proposed values in the installation guide with those from metalink you will notice that the shmall in the installation guide (2097152) is much higher then on metalink (allowing for 8 GB of shared memory when your pagesize is 4096) and that there is no 4GB limitation in the calculation of the shmmax parameter.

During my searh Oracle also responded on my SR on metalink, saying that the 4 GB limit in the shmmax calculation is not an os limit but an oracle limit. They also said that this was because 4GB is the maximum size of a core dump. Now what the size of the core dump has to do with the size of a shared memory segment is yet unclear to me, and it also makes me wonder if my calculation of shmmax is correct. (After having received some feedback by oracle it indeed turned out that my formulla for shmmax was not correct, so I have made the necessary adjustments in the post)
I will keep searching for this and update the post when I have more information.

shmmax and solaris 10

A couple of days later, I needed to install oracle on a solaris 10 system. Now with solaris 10 the way to specify kernel parameters differs from the prior solaris versions. You now no longer need to put your kernel parameters in the /etc/system file, but instead use projects and projmod.
Because of past experiences on solaris 10 I already knew that the oracle installation guide was not very correct on how to work with projects (and because of my newly gained understanding of the kernel parameters) I started to search for more information on solaris 10 kernel parameters.

I soon found out that the parameter project.max-shm-memory which is replacing the old shmmax parameter differs not only in name and how to set it (note that using prctl to set the kernel parameter, as suggested in the installation guide will make the parameter not persistant), but also in definition!
When you look at the solaris tunable parameters reference manual you will note that the parameter is not specifying the maximum size of a single shared memory segment, but instead the maximum shared memory for your entire project. In the installation guide, however, oracle is still using the same value for the new project.max-shm-memory parameter as with the old shmmax parameter. So when you follow the recommended values for the installation guide you would suddenly limit the total sga size for all databases on your system to 4GB (unless you use different users with different projects for your databases).

So be warned: when it comes to kernel parameters, don’t just take the values specified in the installation guide.