Friday, November 26, 2010

Oracle enqueue wait tips

Oracle enqueue wait tips from Burleson Consulting site

Wednesday, November 17, 2010

Installation of Oracle 10gR2 database on RedHat Enterprise Linux/Oracle Enterprise Linux 5 (x86_64)

As root

1) Check Hardware Requirements

# grep MemTotal /proc/meminfo >= 1Gb
# grep SwapTotal /proc/meminfo
Between 1024 MB and 2048 MB 1.5 times the size of RAM
Between 2049 MB and 8192 MB Equal to the size of RAM
More than 8192 MB 0.75 times the size of RAM

# df -h
400 MB of disk space in the /tmp directory
Between 1.5 GB and 3.5 GB of disk space for the Oracle software, depending on the installation type
1.2 GB of disk space for a preconfigured database that uses file system storage (optional)
To determine the amount of free disk space on the system, enter the following command

2) Kernel Requirements

# uname -a >= 2.6.18-8

3) Disable SELinux or switch it to "Permissive" mode

SELinux status: disabled

If you see the previous output SELinux is disabled.

If SELInux is enabled then run :

Commands, as root:
getenforce (returns "Enforcing")
setenforce 0
getenforce (returns "Permissive")

End to restore it to enforcing
setenforce 1
getenforce (returns "Enforcing")

For more info check Oracle Metalink Note: 454196.1

4) Installing Required packages

Use rpm -qa | grep package_name for the following packages

binutils- (x86_64)
compat-db-4.2.52-5.1 (x86_64)
compat-libstdc++-296-2.96-138 (i386)
compat-libstdc++-33-3.2.3-61 (i386)
control-center-2.16.0-14 (x86_64)
gcc-4.1.1-52 (x86_64)
gcc-c++-4.1.1-52 (x86_64)
glibc-2.5-12 (x86_64)
glibc-2.5-12 (i686)
glibc-common-2.5-12 (x86_64)
glibc-devel-2.5-12 (x86_64)
glibc-devel-2.5-12 (i386)
glibc-headers-2.5-12 (x86_64)
ksh-20060214-1.4 (x86_64)
libaio-0.3.106-3.2 (x86_64)
libgcc-4.1.1-52 (i386)
libgcc-4.1.1-52 (x86_64)
libgnome-2.16.0-6 (x86_64)
libgnomeui-2.16.0-5 (x86_64)
libgomp-4.1.1-52 (x86_64)
libstdc++-4.1.1-52 (x86_64)
libstdc++-devel-4.1.1-52 (x86_64)
libXp-1.0.0-8 (i386)
make-3.81-1.1 (x86_64)
sysstat-7.0.0-3 (x86_64)

The version of the above packages is the lower needed. So if you are in higher you are OK


# rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep binutils
binutils- (x86_64)

5) Set the kernel parameters

kernel.shmall = physical RAM size / pagesize (getconf PAGESIZE) -- If the defaults are greater then leave it.
kernel.shmmax = 1/2 of physical RAM, but not greater than 4GB -- If the defaults are greater then leave it.
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 512 x processes (for example 65536 for 128 processes)
net.ipv4.ip_local_port_range =9000 65500
a.) The runInstaller (OUI) checks may expect this to be the old guidance of “1024 65000”. The new guidance from Oracle development is “9000 65500”. Please allow the runInstaller (OUI) to proceed with the new guidance from Oracle development.
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

# vi /etc/sysctl.conf

Set the values and then run :

# sysctl -p

6) Add Oracle user and groups and create the ORACLE_BASE directory for Oracle Software and database.

# groupadd oinstall
# groupadd dba
# useradd -g oinstall -G dba oracle
# passwd oracle

# mkdir -p /u01/app/oracle
# chown -R oracle:oinstall /u01/app/oracle

7) Set the session limits for Oracle User

Set the following to the /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Set the following to the /etc/pam.d/login
session required

Add the following to /etc/profile if Oracle user will use the bash shell.

if [ $USER = "oracle" ]; then
ulimit -u 16384
ulimit -n 65536

As oracle user
8) Login as Oracle and change the .bash_profile
Add the following in order to begin the installation
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl # You can not set the ORACLE_SID if you do not want write now
export DISPLAY=YOUR_XTERM_IP:0 in order to you an X session for the installation
$ . ./.bash_profile

to apply the new settings

9) Put the software for 10gR2 to a desired server directory.


Unzip the database and run
$ gunzip 10201_database_linux_x86_64.cpio.gz
$ cpio -idmv < 10201_database_linux_x86_64.cpio
$ cd database
$./runInstaller –ignoreSysPrereqs

Then unzip the companion and run
$ gunzip 10201_companion_linux_x86_64.cpio.gz
$ cpio -idmv < 10201_companion_linux_x86_64.cpio
$ cd companion
$./runInstaller –ignoreSysPrereqs

Finally unzip the Patch Set and run (At the time this post was written only the patch was available):
$ unzip
$ cd Disk1
$ ./runInstaller –ignoreSysPrereqs

10) Create the database
Add the following to the .bash_profile
export PATH=$ORACLE_HOME/bin:.:$PATH

$ . ./.bash_profile
$ dbca

Create your desired database and you finished
After that run

$ netca

To create a listener

ATTENTION if your host name is more than 32 characters then you are about to have the following situation:

If you have choose to install EM and during its creation you get from dbca
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

then apply the following workaround:

1) Ignore error and continue with database creation
2) After successful finish of dbca do :

Verify the case using sqlplus
SQL> select length(host_name) from v$instance where rownum=1;


Here the length is 36 (>32) and for 10.2 this is a bug and em repository creation fails.
a) Make a backup copy of the file

$ cp $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql_bak

b) Change the l_host_name variable in self_monitor_post_creation.sql to have 128 characters
The l_host_name variable needs to be changed twice in the script.
$ vi $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/self_monitor/self_monitor_post_creation.sql

c) Drop and recreate the DB Control configuration files and repository
$ $ORACLE_HOME/bin/emca -deconfig dbcontrol db -repos drop
$ $ORACLE_HOME/bin/emca -config dbcontrol db -repos create

Check your em by using the URL mentioned.

Metalink Note ID 421308.1, 335118.1

Thursday, November 11, 2010

Flushing and closing trace files using oradebug

Lots of times we erase trace files on the $ORACLE_BASE/admin directory but the database is still open. The processes that have created these files have the inode of the file opened and thus removing the file any new information goes to it but we can not see the file until the process rotates it or close its.

We can use the oradebug utility to manual flush and close the file.


[oracle@labdb01 udump]$ sqlplus / as sysdba

SQL*Plus: Release - Production on Thu Nov 11 12:29:35 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

s.sid, s.serial#,
FROM v$session s, v$process p
WHERE s.paddr=p.addr
and s.program like '%LMD%'; 2 3 4 5

---------- ---------- ----------
1101 1 6

SQL> oradebug setorapid 6
Unix process pid: 17652, image: oracle@labdb01 (LMD0)
SQL> oradebug close_trace
Statement processed.
SQL> oradebug flush
Statement processed.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

In the above example we use the query to find the PID for the oracle lmd0 process.
The we use the oradebug in order to flush the data and close the file.

We can also use the O/S PID.

$ ps -ef | grep $ORACLE_SID | grep lmd

Would return the PID for Oracle background process LMD.


$ sqlplus / as sysdba
SQL>oradebug setospid
SQL>oradebug close_trace
SQL>oradebug flush
Now check the file in the bdump location, a new file would have been created

Wednesday, November 10, 2010

Oracle 10G Composite RANGE-LIST partitioning

Composite RANGE-LIST partitioned table maintenace.

1) Create

table mytable
PROC varchar2(30 byte),
ACTION varchar2(100 byte),
END_TIME date,
tablespace USERS enable row movement
partition by range (start_time)
subpartition by list (action)
subpartition template
subpartition added values ('ADD') tablespace users ,
subpartition removed values ('REMOVE') tablespace users
partition nov10 values less than (to_date('01/12/10','DD/MM/YYYY')) tablespace users,
partition dec10 values less than (to_date('01/01/11','DD/MM/YYYY')) tablespace users,
partition jan11 values less than (to_date('01/02/11','DD/MM/YYYY')) tablespace users

create index my_idx on mytable (ACTION) local;

All partitions are stored in the same tablespace and a local index is created too.

ORA-01450 maximum key length (num) exceeded

I faced this error when i was rebuilding and index online but without the online the index rebuilds fine.

This is caused by Bug:2525767. The online rebuild of the index creates a journal table and index and the table contains more columns in its index with total length greater than number reported in ORA-01450 error message.This is a feature of online rebuild.

Maximum key length is calculated with respect to the database block size. It
means that current value of the initialization parameter db_block_size is not
large enough so that the internal journal IOT can be created without errors.

Workaround to rebuild the index offline.

Monday, November 1, 2010

Oracle Event Codes

In the following link you can find all Oracle event Codes.

WARNING: inbound connection timed out (ORA-3136)

The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the SQLNET.INBOUND_CONNECT_TIMEOUT time.(Defaults to 60 secs after 10.2)

Take look of the following

1. Check whether local connection on the database server is successful & quick.
2. If local connections are quick ,then check for underlying network delay with the help of your network administrator.
3. Check whether your Database performance has degraded in anyway.
4. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them resolved first.
These critical errors might have triggered the slowness of the database server.