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.