Teaching

Part of my job is to occasionally give oracle trainings to customers.
Although this has never been my favorite part of my job, I find it most times an interesting experience. It forces you to realy take a good look into the material you are teaching and by explaining something to others, you ofter gain a better insight yourself.

The downsize of these kind of trainings is that you don’t have much time to actually learn the students something. I mean, you can guide them through the course material and learn them the facts, but that still is something different then giving a real understanding of things. You know, the level of understanding that you have reached when you are able to reason why something works a certain way.

A good example of this is the question (I’m currently giving a 9i backup and recovery course) : “do I have to backup my undo tablespace?”
The simple answer is that yes, you should backup your undo tablespace, but if you understand how oracle processes data changes and how oracle performs recovery (with the roll-forward and roll-back phases) then you just know that the undo tablespace has to be backed-up.

However, as I said, it takes time to gain understanding and I am afraid that a 4 days of training are only enough to scratch the surface. Therefore I find that these training courses are only suited as a kind of starting point and that you have to learn the details afterwards on yourself.
One way to do this is by organizing regular internal workshops or hand-on trainings, in which each time a fellow dba explains one specific part of oracle. Another option can be to hire somebody for a couple of days per week to coach the internal dba team.

I want to end this blog entry with a couple of questions: How did you learn oracle? Are there regular internal workshops at your company? What are your feelings about oracle courses? Do you find them usefull or not?

8 trivial things about me

Looks like, thanks to Chen Shapira, the tagging storm has hit me as well:

1. I was born on December 27, 1978 in Lokeren, Belgium and have 1 younger brother. He has also studied computer science and is currenlty working as a sysadmin.
   As you can imagine we had some animated discussions at the dinner table that where hard to follow by our parents.

1. My father is a lifeguard in a swimming pool ( I could swim before I could walk ) and my mother is a laboratory technician (yes, we had petri dishes stored in our fridge).

1. I got maried in 2003, and we are expecting our first child in about 6 weeks.

1. Every August I spend 10 days working as a barkeeper at a music festival ( lokerse feesten ).

1. I have the bad habbit of always arriving late. So if the baby looks like me, he/she will be born in about 8 weeks :)

1. I drive a Toyota Prius, but I would really need to use my bike more often.

1. My favorit beer is Hoegaarden (an unfiltered white beer) and I also like to drink whisky (single malt, that is).

1. I like reading, mostly fiction (sci-fi, thrillers, detectives, that kind of stuff). When I’m reading I tend to forget about time and other duties and keep on reading until late at night, which explains a bit the late arrivings.

My time to tag.
Hmm, I tag Geert De Paep, Arul Ramachandran and Howard Rogers (call it a flaw in my character)

Can a select block a truncate ?

Recently a discussion was ongoing on the oracle-L list about the question if a select could block a truncate or not.
The first reaction that I had was that, when a select would be active reading from a table, the truncate would fail with the message: “resource busy and acquire with NOWAIT specified”. My reasoning behind this was that a truncate is a ddl statement, which generates no undo for the “deleted” records. So to guarantee that the select would be read consistent, Oracle would have to fail the truncate.
But when discussing this at the office, my colleague Geert claimed that a select placed no protection on the structure of a table and that it would be the select statement that would fail (with the message that the object does no longer exists).

At these moments, there is only 1 thing that you can realy do, and that is to test it. So I did and I found out that is not so strait forward.
Here we go:

setting db_cache_size when using automatic sga management

When using automatic sga management (sga_target), you might notice that your shared pool sometimes keeps growing, at the cost of the db_cache_size. I suspect this to be caused by applications that are not using bind variables, but I have not yet researched this further.

When you have such an occasion you can do 4 things :

• Instruct the programmers to fix the program (but you can only really do this when the application is developed in house)
• Leave it as it is (not really an option)
• Discard automatic sga management, and specify the different memory parameter yourself.
• Give a lower limit for the db_cache_size, by setting the db_cache_size without removing the sga_target (oracle would then use the db_cache_size as a lower bound)

I went with option number 4, as the application was not developed by us and I still wanted oracle to be able to move memory from one pool to the other.
For this system I also wanted to increase the sga_target, as several applications had been migrated to this database.

SQL> alter system set sga_target=3000M scope=spfile sid='*';

System altered.

SQL> alter system set db_cache_size=2000M scope=spfile sid='*';
alter system set db_cache_size=2000M scope=spfile sid='*'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache

SQL> column sid format a15
SQL> column name format a40
SQL> column value format a20
SQL> r
  1  select sid, name, value
  2  from v$spparameter
  3* where name = 'db_cache_size'

SID             NAME                                     VALUE
--------------- ---------------------------------------- --------------------
*               db_cache_size

*sight*

It seems like oracle is checking the new db_cache_size value (that you want to put in the spfile, not in the running instance) against the current values in the running instance.
On metalink I found the following bug note about this: bug 4919526 “alter system set db_cache_size=xxxx scope=spfile fails. ora-00384″, only this has been marked as not a bug (yeah, right).

To overcome this issue I specified the db_cache_size parameter in the pfile that pointed to the spfile (which was located in asm) and after that changed the parameter again in the spfile itfself and removed the setting from the pfile.

SQL> !
[oracle@eocpc-rc01:~]$ echo db_cache_size=2000M >> $ORACLE_HOME/dbs/initTRIDION1.ora
[oracle@eocpc-rc01:~]$ cat $ORACLE_HOME/dbs/initTRIDION1.ora
spfile=’+DGTRIDION/TRIDIONUK/spfileTRIDION.ora’
db_cache_size=2000M
[oracle@eocpc-rc01:~]$ exit
exit

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 3154116608 bytes
Fixed Size                  2033408 bytes
Variable Size             620763392 bytes
Database Buffers         2516582400 bytes
Redo Buffers               14737408 bytes
Database mounted.
Database opened.

SQL> alter system set db_cache_size=2000M scope=spfile sid=’*';

System altered.

SQL> column sid format a15
SQL> column name format a40
SQL> column value format a30
SQL> set linesize 120
SQL> select sid, name, value
from v$spparameter
where name = ‘db_cache_size’;  2    3

SID             NAME                                     VALUE
————— —————————————- ——————————
*               db_cache_size                            2097152000

SQL> !
[oracle@eocpc-rc01:~]$ echo spfile=\’+DGTRIDION/TRIDIONUK/spfileTRIDION.ora\’ > $ORACLE_HOME/dbs/initTRIDION1.ora
[oracle@eocpc-rc01:~]$ cat $ORACLE_HOME/dbs/initTRIDION1.ora
spfile=’+DGTRIDION/TRIDIONUK/spfileTRIDION.ora’

Of course, recreating the spfile is also an option

Oracle VM part 2

Recently I wrote about installing Oracle VM.
At that moment I just had managed to import the iso images so I could use them to setup a guest system.
Unfortuanatly I uploaded the oracle enterprise linux 4.0, which is not a supported paravirtualized guest system (and imposible to install as we soon discovered) and we did not have the newer versions available. Since our company internet connection is not very fast, it was impossible to download EL 4.5 or EL 5.0, and ended that days installation attempt.

After downloading EL 4.5 that evening at home (broadband connection :) ), I could start the installation again the next day. And this time I could actually start the guest system and start with the os installation. However that was about as far as I got, as early in the guest setup I ran against the following error:

When I clicked OK, a nfs setup screen was given, in which the nfs server and directory path given during the guest definition phase where already filled in.
I tried several things to get it working, but all without success. At that point in time I was rather fed up with oracle vm, and it looked like I was not the only one to struggle.

Today, after letting it rest for some days, I had some spare time and started to test the installation again.
At a certain moment I tried to mount the installation directory (which was located on the vm server) from the vm management server and I received a “No route to host” error. Until that time I only mounted the directory from the vm server itself, thinking it would be the vm server that would do the mounting (as the, to be installed, guest is running on that server).
After some searching I found that the vm server had the iptables service running, which was blocking the nfs mount requests. And indeed, after stopping the iptables service the installation was able to mount the directory and continue with the installation.

What does this learn us:

• Oracle is mounting the installation directory from the managment server, not from the vm server and then “streams” it to the vm server running the guest system.
• During the setup of the vm server, oracle is disabling nfs access to it
• There is still some serious work to do on the documentation

Before I came to the solution of disabling the iptables service, I had already tested it to create a single directory containing the contents of the 4 installation cd’s (with a modified .discinfo file). It was this directory that I used for the installation after solving the iptables problem. It could be that this is also a necessity, but I have not tried an installation by just mounting the first of the iso images, so I am not sure.

At last I can start installing some guest systems and test the working of oracle vm.

disaster recovery troubles

First of all an excuse: these days I mainly use rman for backup and recovery tasks. I have used user managed backup and recovery in the past but it all has fade away….

Last Thursday and Friday I was at a client site to setup and test snapmanager for oracle (smo). With this tool you can use Netapp snapshots to backup Oracle databases and create “virtual” clone databases (a db clone without needing the disk space of the original db). Giving a full explanation of what netapp snapshots are and how smo works will lead too far for this post (I am working on a separate post about it), so just think about smo as a user managed backup.
Now this client wanted to extend the functionality of smo and wanted the snapshots (backups) to be replicated to a remote site. These replicated snapshots would then be used to create test/dev databases and for disaster recovery purposes.
One of my tasks was to write a disaster recovery procedure.

In short smo is performing the following steps when taking a full db backup:

1. Place the entire database in backup mode.
2. Take snapshots of all the netapp volumes containing database files. (think: take an os copy of all the data located on volumes containing data files).
3. Take the database out of online backup mode.
4. Issue an archive log current.
5. Generate backup control files.
6. Take a snapshot of the volumes containing the archive log files and the backup controlfile (think: take an os copy of all the data located on volumes containing archived redologs).

This backup data is then copied to a remote site.

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.