Simon Krenger

When deploying a new database, one thing I usually do is to set up a logrotate configuration for the Oracle Listener log. The Oracle Listener logs every connection he makes and when using an Application Server to interface the database, this file can grow quite a bit. So we need to make sure that we properly rotate this log and compress the old logs.

To achieve this, I usually create the following configuration for logrotate:

I just noticed that every time I need to create a password file, I have to look up the Oracle documentation for the correct syntax of the ORAPWD arguments. So this post is mainly for my own reference and contains the proper syntax for UNIX and Windows systems as well as an example for each system.

UNIX syntax:

Today a colleague approached me and asked about the difference between the following two statements, because they return different results:

select count(*) from mytable;
select count(name) from mytable;

During maintenance, I had to disable all constraints of a table. I knew that Oracle SQL Developer (I really like it even though it is a developer tool and not aimed at Database Administrators) had built-in functions to do this, but since I could only access the database machine via SSH, I had to do it in SQL*Plus.

Luckily, SQL Developer is quite transparent about the commands it uses and I could therefore easily see what is going on when you disable all constraints on a table using the GUI. So here it goes…

After upgrading a database from 10.2.0.1 to 10.2.0.5, I was unable to start the Oracle Enterprise Manager. Whenever I tried to do so, the log showed that it failed to start Database Control. Even when I deconfigured the Enterprise Manager, deleted the repository and started from scratch, I still ran into the same problem. In this post, I will describe the symptoms I encountered and also provide a solution.

On one database, I noticed that SQL*Plus did not show the heading of the columns when I ran a query. The result was this:

SQL> select instance_name,status from v$instance;
mydb          OPEN

Even when entering “set heading on” didn’t change anything. I then investigated and found out that someone had changed the $ORACLE_HOME/sqlplus/admin/glogin.sql file (SQL*Plus runs this file on startup) and added the following lines:

SET LINESIZE 150
SET PAGESIZE 0

While this is quite nice and replaced the suboptimal default values, this was the cause for my problems mentioned above. Changing the line “SET PAGESIZE 0” to “SET PAGESIZE 1000” solved the problem and now, the query shows up the way I wanted:

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
mydb             OPEN

So when it comes to deploying a new application there is always the question if you should create a new instance on the database server or use the existing instance and just add a new schema? These both are valid options, but lets have a look at both options.

Lets assume you already have one instance running one application, now you need to provide a new database to a user. So depending on the application, you have the choice of creating a new instance for this specific new application or to use an existing database instance and just creating a new schema.

Last week I had to perform an installation of Oracle 11g on a Windows Server 2008 R2 machine. When I tried to install a 11g database with the -silent and -responsefile options, I received the following error:

[SEVERE] - Email Address Not Specified

Obviously, I needed to specify an e-mail address for My Oracle Support. Lets have a look in the responsefile:

Out of curiosity, I decided to run the IOzone tests I performed with a RAID 10 (see this post) on a RAID-Z and compare it to the RAID 5 of the hardware RAID controller. For this test, I am using IOzone and two older HP DL380 G2 servers.

As I am currently fiddling around with Oracle Solaris and the related technologies, I wanted to see how the ZFS file system compares to a hardware RAID Controller. The ZFS file system allows you to configure different RAID levels such as RAID 0, 1, 10, 5, 6. In this post, I want to test the RAID 10 performance of ZFS against the performance with the HP RAID Controller (also in a RAID 10 configuration) over 4 disks.