Simon Krenger

Since most of our databases are not licensed with the Oracle Enterprise Manager Diagnostic Pack, we cannot use AWR (Automatic Workload Repository) and ADDM (Automatic Database Diagnostic Monitor). So we have to use the good old Oracle STATSPACK.

The goal of this article is to provide a quick reference for installing and maintaining STATSPACK for an Oracle database. It is based on the excellent “STATSPACK Survival Guide” of Akadia AG. If you need more information on STATSPACK, please refer to the Survival Guide or the Oracle documentation, since this article is only a really quick summary on how to install, configure and use STATSPACK. Read the rest of this entry »

On a few test databases, test managers often need to preserve certain states in the database. This is why we use daily datapump scripts to create exports for archival.

It is important to note that such scripts are never a replacement for a proper RMAN backup, but an easy way to preserve multiple states of a database and reuse data where applicable.

This batch file uses the expdp tool provided by Oracle and the 7-zip archiver to compress the exports for archival. The export tool itself creates a full export of the whole database (full=y). Also, the flashback_time parameter is specified to get a consistent export.

Read the rest of this entry »

Ok, here is a quick trick that I just found out about earlier this week. I am currently in an Oracle Database 11g: Administration 11 course and learn many new things about database administration.

One thing you will definitely encounter when working with Linux is that it is not possible to scroll back though your SQL*Plus history with the arrow keys. If you try to do so, the following happens:

SQL> select owner, count(1) from dba_tables group by owner;

OWNER				 COUNT(1)
------------------------------ ----------
[..]
SYS				      673
SIMON				       88


7 rows selected.

SQL> ^[[A     " - rest of line ignored.
SQL> command "
SQL> 

Read the rest of this entry »

One thing DBAs regularly do is to gather information on the schemas in the database for reporting. So we want to know how many rows there are in the tables of the schema “SIMON”. When doing so, one is tempted to query the DBA_TABLES view:

SQL> SELECT table_name, tablespace_name, num_rows
  2  FROM dba_tables WHERE owner='SIMON' ORDER BY num_rows DESC;

TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ----------
HUGE_TABLE                     DZ_DATA01                         8227990
[..]

However, if we query the table directly, we get a different result:
SQL> SELECT count(*) FROM simon.HUGE_TABLE;
COUNT(*)
----------
8230310

Why is that so? Read the rest of this entry »

Well, I was quite busy before the holidays, so here is another post I just keep for my reference.

For each database, I believe it is important to automate database shutdown and database startup. This way, in case of an emergency, a systems administrator can start and stop database services without the need for a database administator. Oracle provides an excellent article on this topic, but the Oracle documentation is quite generic. So I hereby provide a step-by-step guide for Red Hat Linux.

Read the rest of this entry »

In the last weeks, I had a few projects involving Oracle Advanced Replication and immediately stumbled upon the problem of not knowing which user and which instance was on which prompt. Instead of constantly issuing “SHOW USER” and “SELECT * FROM global_name”, I decided to add a few lines to my glogin.sql.

So before investing precious time, I fired up Google and found that other people also had the idea of changing the SQL*Plus prompt. I found the template for my own glogin.sql here and modified it so it fits my needs.

Note that my script does not display the GLOBAL_NAME of the databse, but only the INSTANCE_NAME read from v$instance. This means the prompt is usually a lot shorter:
repadmin@kdb3:SQL>

Read the rest of this entry »

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.

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.

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.

Read the rest of this entry »