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:
Read the rest of this entry
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:
Read the rest of this entry
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;
Read the rest of this entry
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…
Read the rest of this entry
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.
Read the rest of this entry
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.
Read the rest of this entry
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:
Read the rest of this entry
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.
Read the rest of this entry
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.
Read the rest of this entry