oerr "tool" for windows

Hi,
This is an old post I wrote, but I'm rewriting it because I just had a customer that needed this tool.

I've created a small oerr "tool" for use in windows. I don't have databases on windows machines, but i do use windows as a client and when I get an error in sqlplus i don't wish to ssh to a linux/unix server and run oerr there.

The "tool" is a batch file with gawk and the message files from a 10g oracle installation. The zip file contain information about how to use other oracle version messages.

You can get the file from here.

Know your regular expressions

New article from IBM - Know your regular expressions, shows how few techniques that helps in creating regular expressions.

Unistall Oracle Clusterware+ASM+ASMLib+Database 10.2.0.2

Hi,
This week I've installed Oracle RAC + ASM + ASMLib on RHEL4 (update 5), and it was exhausting!
I've done the linux administration part of the installation, but I also participated in the Oracle aspect.
Well, the first thing that I would like to talk about is the oracle Clusterware voting & CRS disks,
Oracle Clusterware needs raw access to the disks, also, specific privileges on the raw devices.
Linux raw devices are deprecated! The other way to do this job is that the application will open the devices with O_DIRECT. Oracle database supports O_DIRECT by using OCFS or ASM with ASMLib but not for the Clusterware disks.

So we will need to use raw devices if we are using RAC with Oracle Clusterware.
The problem with the raw devices is:
- It's deprecated
- Need to create the devices on every boot and change the devices permissions for oracle usage
- depends on the device name (sda, sdb...)

Why depending on the device name is a problem? Because there is no way to assure that the device sda will still be sda after reboot or after storage changes (Ex. adding a new disk).

Well, the way to do all of this and assure that the Clusterware will load and start the instances is:
- Use Udev to map the devices name to be something like /dev/vote1, /dev/ocr1
- Use Udev to change the raw device privileges on boot
- Use /etc/sysconfig/rawdevices file to create the raw devices when the server starts

One more thing is the cluvfy script in the Clusterware installation disk, the script is good for RHEL3 and partially for RHEL4, the last step of the script is to check the rpms needed by the installation, the rpm list is for RHEL3 and not for RHEL4 so you can ignore this check and start the installation.

The other thing is the uninstall of the Oracle Clusterware, the way to fully uninstall oracle is:
- Run the universal installer to uninstall the software for the database, ASM and Clusterware.
- Manually remove the files remains in the installation directories ($ORACLE_HOME, $ASM_HOME & $CRS_HOME).
- remove /etc/oracle directory.
- remove "/etc/oratab".
- remove "/etc/oraInst.loc".
- remove "/etc/crstab*".
- remove "/etc/init.d/init.*".
- edit /etc/inittab and remove the lines oracle added (usually 3 lines at the end).
- remove the ASM disks using "/etc/init.d/oracleasm deletedisk".
- For the finale dd the first 50MB of the voting disks and the crs disks (Without this the cluster will use the old configuration!!).
- Just to make sure also dd the first 50MB of the ASM disks.

After that you can start the installation again...

Oded.

Rehdat Linux kickstart file for Oracle database server

I've created a kickstart file for RHEL that will install and configure your Redhat linux server for Oracle database installation.

You can get the file from here or copy & paste this:

#############################################################################
# This Kickstart file generated by Oded Maimon - Tangram Soft Ltd.
# License: Do what ever you wish to do with this file, I don't care
#
# This Kickstart file is used to install the minimum RHEL server for use with
# Oracle database. It also removes unnedded services.
#
# The defualt root password is: password
#
# Values to change before using are:
# - Set the network - The hostname & Network configuration (DHCP/Static)
# - Partitioning & Swap size
# - Set a different timezone if not located in israel
# - If going to use rawdevices comment the "chkconfig rawdevices off" line
# - Set the shmmax kernel parameter to the correct value
# - If you are not going to install Oracle RAC, you can comment the hangcheck
# line in this file
#
# Things to do after the server is installed:
# Change the root and oracle accounts password
# Create the needed filesystem and set the owner to oracle
# Change the ORACLE_BASE, ORACLE_SID environment variables in
# ~oracle/.bash_profile to the correct values
# Set the server default run level to 3 or 4 (if using NFS/CIFS)
#############################################################################

# Uncomment the next line to enable interactive installation
#interactive

# Comment the next line to use GUI installation
text

install

# From where to get the installation disks
cdrom
#url --url http://myserver/redhat/

lang en_US.UTF-8
langsupport --default=en_US.UTF-8 en_US.UTF-8
keyboard us
xconfig --resolution 800x600 --depth 16 --defaultdesktop gnome

# Change the hostname and peek the best eth configuration for you
network --device eth0 --bootproto dhcp --hostname srvoracle01
#network --device eth0 --bootproto static --ip x.y.z.v --netmask 255.255.255.0 --gateway x.y.z.v --nameserver x.y.z.t,x.y.z.v --hostname srvoracle01

rootpw --iscrypted $1$uKWECPhN$Im66UG8MpWd2/kpcHoyuy/
firewall --disabled
selinux --disabled
authconfig --enableshadow --enablemd5

# Set a different timezone if not located in israel
timezone Asia/Jerusalem

bootloader --location=mbr --append="rhgb quiet"


# Create a Volgroup device sda
clearpart --all --drives=sda
part /boot --fstype ext3 --size=100 --ondisk=sda
part pv.3 --size=0 --grow --ondisk=sda
volgroup vg00 --pesize=32768 pv.3

# Create the filesystems and logical volumes
logvol / --fstype ext3 --name=lvol00 --vgname=vg00 --size=2048
logvol /tmp --fstype ext3 --name=lvol01 --vgname=vg00 --size=2048
logvol /usr --fstype ext3 --name=lvol02 --vgname=vg00 --size=3500
logvol /usr/local --fstype ext3 --name=lvol03 --vgname=vg00 --size=1024
logvol /var --fstype ext3 --name=lvol04 --vgname=vg00 --size=1024
logvol /var/log --fstype ext3 --name=lvol05 --vgname=vg00 --size=1024
logvol /opt --fstype ext3 --name=lvol06 --vgname=vg00 --size=1024
logvol /home --fstype ext3 --name=lvol07 --vgname=vg00 --size=512

# Edit the swap space to be twice the size of the machine RAM, if you have 8GB of RAM or more set the swap to the same size as the RAM
logvol swap --fstype swap --name=lvol08 --vgname=vg00 --size=2048


# All packages needed by Oracle installation include Gnome desktop
%packages
@ system-tools
@ gnome-desktop
@ dialup
@ compat-arch-support
-openldap-clients
-ckermit
-wireshark
-bluez-pin
-OpenIPMI-tools
-samba-client
e2fsprogs
-screen
-xdelta
-zsh
-nmap
lvm2
sysstat
-open
kernel-smp
grub
binutils
compat-db
control-center
gcc
gcc-c++
glibc
glibc-common
gnome-libs
libstdc++
libstdc++-devel
make
pdksh
sysstat
xscreensaver


# This post script modify all the Oracle database prerequisites, includes oracle user and dba,oinstall groups
%post

# create user and groups
/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba
/usr/sbin/useradd -m -g oinstall -G dba oracle

# Change kernel parameters
cat >> /etc/sysctl.conf << EOF

##############################
# Oracle Database requirements
##############################

# The max value for shmmax on a 32bit machine is 4294967295
#kernel.shmmax = 536870912

kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 658576
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 1048536
net.core.wmem_max = 1048536
EOF

# Change ulimits for user oracle
cat >> /etc/security/limits.conf << EOF
oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile 65536
oracle hard nofile 65536
EOF


# Add hangcheck-timer module for RAC
echo "options hangcheck-timer hangcheck_tick=30 hangcheck_margin=180" >> /etc/modprobe.conf
echo "modprobe hangcheck-timer" >> /etc/rc.local


# Set oracle account environment variables
cat >> /home/oracle/.bash_profile << EOF

# Oracle environment variables
export ORACLE_BASE=/software/oracle
#export ORACLE_HOME=\$ORACLE_BASE/10g
#export TNS_ADMIN=\$ORACLE_HOME/network/admin
export ORACLE_SID=orcl1
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib
export PATH=\$PATH:\$ORACLE_HOME/bin

if [ "`ls -ld \$ORACLE_HOME | awk '{print \$3" "\$4}'`" != "oracle oinstall" ]
then
echo "The ORACLE_HOME (\$ORACLE_HOME) is not owned by oracle:oinstall"
fi
EOF

#############################
# Remove unneeded services!!!
#############################

# Comment this line if you are using Oracle Clusterware
chkconfig rawdevices off

# Enable Network filesystems support for run level 4 only
chkconfig --level 12356 portmap off
chkconfig --level 12356 nfslock off
chkconfig --level 12356 rpcgssd off
chkconfig --level 12356 rpcidmapd off
chkconfig --level 12356 autofs off
chkconfig --level 4 portmap on
chkconfig --level 4 nfslock on
chkconfig --level 4 rpcgssd on
chkconfig --level 4 rpcidmapd on
chkconfig --level 4 autofs on

# Enable xfs support for run level 5 only
chkconfig --level 12346 xfs off
chkconfig --level 5 xfs on

chkconfig sendmaill off
chkconfig cups off
chkconfig hplip off
chkconfig anacron off
chkconfig apmd off
chkconfig arptables_jf off
chkconfig atd off
chkconfig bluetooth off
chkconfig canna off
chkconfig cups-config-daemon off
chkconfig gpm off
chkconfig hidd off
chkconfig hpoj off
chkconfig iiim off
chkconfig isdn off
chkconfig pcmcia off

chkconfig rhnsd off
chkconfig auth off

# RHEL 5 only services
chkconfig yum-updatesd off
chkconfig setroubleshoot off
chkconfig restorecond off
chkconfig pcscd off
chkconfig mcstrans off
chkconfig avahi-dnsconfd off
chkconfig avahi-daemon off

##########################################################
##########################################################



Enjoy.

I'm trying to get this blog alive again, with a different blog name and a different purpose.
In the blog i will talk about Linux, open source, oracle and more.

EnterpriseDB release 2

Hi,

A recent mail I recieved from EnterpriseDB:

Dear Customer,

I’m excited to tell you that EnterpriseDB 2005 Release 2 is now available for download from our website. This general availability release incorporates the most recent production version of PostgreSQL (v8.0.4) and includes several important new features. Please download the new release and give it a try. As always, we’re looking forward to your feedback.

EnterpriseDB 2005 Release 2 includes a new high-speed bulk data loading capability that’s 30% faster than current native PostgreSQL. In addition, we’ve added Oracle-style cursors to further improve our compatibility. Also, we’ve made incorporation into ISV products easier, with new installation options and an easy-to-follow methodology. Finally, we have integrated JasperReports, the leading open source reporting technology, into the platform, and a ready-to-run Jasper module and sample reports are available on the website.

Thanks again to everyone for your interest and your support. Don’t forget to drop us a line to tell us about your EnterpriseDB experience.

Sincerely,

Denis

 

Regards,

Guy

oerr "tool" for windows

Hi,
I've created a small oerr "tool" for use in windows. I don't have databases on windows machines, but i do use windows as a client and when i get an error in sqlplus i don't wish to ssh to a linux/unix server and run oerr there.

The "tool" is a batch file with gawk and the message files from a 10g oracle installation. The zip file contain information about how to use other oracle version messages.

Press here to download.

Oded.

Oracle XE, Mysql or Postgresql ?

With the Oracle XE, the purchase of InnoDB and the integration to PHP, Oracle surly wishes to kill Mysql, But we can't know when Oracle will stop Oracle XE development or change the license, because of that I think it will be better to go with postgresql than with Oracle XE (Unless you know that you are going to upgrade to oracle enterprise in the future) or mysql. From now Postgresql should be the default for a simple free database like what mysql was untill now. Postgresql has no company behind it that some other company can buy and destroy, it is an open community developed database, a very good database and now it's also got the commercial release (EnterpriseDB).

Oded.

Oracle worm

This summary is not available. Please click here to view the post.

Howto set Sqlplus Command history and other features

First of all, Haaaaaaaaaaaaaa!!!!

There is a way to add command line history and command line editing to all command line tools that doesn't support this feature and the tool I talk about is sqlplus (include support for TAB auto complete).

The utility that will make your life much easier is rlwrap

Well, this utility uses the readline library, so to install rlwrap you need to install readline.

Where to find rlwrap:
The latest rlwrap source code can be found in: http://utopia.knoware.nl/~hlub/uck/rlwrap
A bit old RPM version for linux i386 can be found at: http://www.dizwell.com/downloadables/rlwrap-0.18-1.i386.rpm
Other RPM versions can be found at: http://rpm.pbone.net/index.php3?stat=3&search=rlwrap&srodzaj=3 For now (27/10/2005) there are versions for Suse and ALTlinux.

The project can also be found at http://freshmeat.net/projects/rlwrap/

After you installed it, all you need to do is:
rlwrap sqlplus

Creating an alias for "rlwrap sqlplus" will make it easier to use:
alias sqlplus="rlwrap sqlplus"

I've managed to install rlwrap in RH and in Solaris 8, all I needed is nawk, gcc and gnu readline that can be found at http://cnswww.cns.cwru.edu/~chet/readline/rltop.html or at http://www.sunfreeware.com/programlistsparc8.html#readline (didn’t tried it)


Have fun,
Oded.

Nice tool for simulating oracle I/O workloads

Hi,

Oracle have a tool that can simulate the oracle I/O workloads (ORION) without the need to create an oracle instance.

With this tool we can find the best storage configuration to use with oracle.

Oded.

Starting TNS Listener Error

Hi,
few week ago I come up against a problem to start the TNS listener,
"Linux Error: 32: Broken Pipe"

Well, after checking if the configuration files didn't changed
I started googeling, and found out the link that explained that this error occur usually when
the listener log reached 2gb.

after deleting the file I was able to start the listener.

The OS was RHEL3.

Oded.

Zend Core for Oracle (Beta)

Hi,

Zend Technologies and Oracle have released a Beta of "Zend Core for Oracle".

Zend Core for Oracle, developed in partnership with Zend Technologies, delivers a stable, high performance, easy-to-install and supported PHP development and production environment fully integrated with the Oracle Database.

You can look for more details and download Zend Core at Oracle's PHP Developer Center.

Guy

Installing Oracle 10g R1,R2 on Redhat/FC

Hi,
Thought about writing my own installation guide, and then searched to see if there is any existing guide, and guess what? there is.

A very good guide is http://www.puschitz.com/InstallingOracle10g.shtml

Regards,
Oded.

Install yum on Redhat Enterprise Linux 4

It's not very intuitive to install yum on RHEL, you always need to rebuild yum from the srpm to compile it with the python version comes with redhat and resolve the dependencies:

• get yum source rpm from yum site
• run: rpmbuild --rebuild {source-rpm-file}
  
• get the latest python-urlgrabber source rpm from the fedora project
• run: rpmbuild --rebuild {source-rpm-file}
  
• get the latest python-sqlite rpm from dag repository
• get the latest python-elementtree rpm from dag repository
  
• get the latest sqlite rpm from dag repository
• get the latest python-celementtree rpm from dag repository. This rpm is not required as a dependency by the other rpms, but without it you will get the following error when you will try to run yum:

 version of python  2.3.4 (#1, Feb  2 2005, 11:44:49)
 [GCC 3.4.3 20041212 (Red Hat 3.4.3-9.EL4)]
 Please make sure the package you used to install yum was built for your 
 install of python.

Install all the rpms:
rpm -Uvh {all rpm files}

The packages I used are:
yum-2.3.3-1.src.rpm
python-urlgrabber-2.9.6-1.src.rpm
python-sqlite-0.5.0-1.2.el4.rf.i386.rpm
python-elementtree-1.2.6-1.2.el4.rf.noarch.rpm
sqlite-2.8.16-1.2.el4.rf.i386.rpm
python-celementtree-1.0.2-1.2.el4.rf.i386.rpm

Regards,
Oded.

Fixing third party application performance problems

Hi,

I bet you have all been in this situation - you are in charge of an Oracle DB for a third party application your company has purchased.

I am SURE that you make the top most effort at your company to tune your in-house applications, but the company that wrote this third party application has made some mistakes and written uneficient, badly tuned SQL's.

Now you have Added indexes (or dropped them), tried changing different init.ora parameters, but this can only make a limited contribution, especially when the design of the SQL's was really bad.

Until Oracle 10g you did not have many solutions to this problem.
Oracle introduced in 10g a package named DBMS_ADVANCED_REWRITE.
Basically this package enables you to strech the capabilities you know from query rewrite using materialized views, and lets you decide what the alternative SQL will be.

This is great !

I have been looking for this for a long time (even tried manipulating VPD and put in the VPD procedure somthing like ' 1=2 union all select ...', only to find out that the VPD comes in to play late in the parsing stage and only valid predicates can be used...)

I have built a simple senario demonstrating the use of DBMS_ADVANCED_REWRITE, note that some of the things I did where purely for the sake of demonstration and you would probably not use them in real life.

Additionaly, I have not tested this in a full blown production environment.

To the demo:

1) Connect to the DB as sys and create a user for the demonstration:

conn sys as sysdba
create user guy identified by guy;

2) Grant the user the appropriate privs:

grant connect,resource to guy;
grant execute on dbms_advanced_rewrite to guy;
grant create materialized view to guy;

3) Connect to the new user and create a simple table:

conn guy/guy
create table SECRET_DATA (CREDIT_CARD NUMBER);
insert into secret_data values (12345678);
insert into secret_data values (1000);
commit;

4) Issue a select to validate the rows:

SQL> select * from secret_data;

CREDIT_CARD
-----------
12345678
1000

5) Set the appropriate session parameters:

alter session set query_rewrite_enabled=true;
#Default if OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher

alter session set query_rewrite_integrity=trusted;
#This was set to allow a rewrite with different results, in a real life senario you would
consider to have this enforce the integrity of the rewrite SQL

6) Execute the DBMS_ADVANCED_REWRITE package:

SQL> exec sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
('test_equiv','select * from secret_data','select * from secret_data where credit_card=1000',
false,'TEXT_MATCH');

7) You can view your rewrite equivalences:

SQL> select * from USER_REWRITE_EQUIVALENCES;

OWNER NAME SOURCE_STMT DESTINATION_STMT
--------- ------- ------------------ -------------------------
REWRITE_MO
----------------
GUY TEST_EQUIV select * from secret_data select * from secret_data where credit_card=1000
TEXT_MATCH

8) Issue the SQL again, this time with the rewrite:

# As you can see the rewrite works and we get only the "1000" row
SQL> select * from secret_data;

CREDIT_CARD
-----------
1000

9) You can disable the rewrite equivalence and see the normal result again:

SQL> exec sys.dbms_advanced_rewrite.alter_rewrite_equivalence('test_equiv','DISABLED');
SQL> select * from secret_data;

CREDIT_CARD
-----------
12345678
1000


That's all for the basic demo, for further information you can visit the otn documentation.

Regards,
Guy

Open Source EnterpriseDB

Hi,

I have read this article from linuxpipline (http://www.linuxpipeline.com/163700463).

It talks about a startup company named EnterpriseDB, which is developing an Open Source
DB based on PostgreSQL (http://www.enterprisedb.com/index.jsp).

They claim to support Oracle-style features like SQL syntax, datatypes, triggers, and stored procedures.

They have released a beta for download.
Sounds interesting, I will update you after I haved installed and played with it.

Guy

Understanding and Tuning the Shared Pool

Hi,

This is not a new article but a very usefull one.
It gives a good overview of Oracle Shared Pool issues and has links to other articles on the same subject.
I recommened every Oracle related person to read this article and keep a link to it (I use http://del.icio.us to save links).

The article can be found at Metalink 62143.1

Guy

Know which filesystems your linux kernel supports

syntax:
grep -n FS /boot/config-KERNEL-VERSION

for example, I would like to know if my machine supports NFS,
and the kernel I use is 2.6.11-1.1369_FC4, I will run:

grep -n NFS /boot/config-2.6.11-1.1369_FC4

Oded.

Well, unbelivable how this makes firefox browser much much faster !!!!

Well, Usually when you hear something like what I'm going to tell you,

you say: "Haaa. bullshit"

well, any way that what I said to my self.

do this:

in the url enter: about:config

this is not new, this is the advanced configuration for firefox, you can change there any thing!

1. right click on an empty area, click new->integer

enter: "nglayout.initialpaint.delay" for the name

enter 15 for the value

2. go to "network.http.pipelining" and double click it

goto "network.http .proxy.pipelining" and double click it also

goto "network.http.pipelining.maxrequests" and enter the value 15 (you can even enter 30)

ok, what will it do?

nglayout.initialpaint.delay = 1, will change the default firefox wait for page rendering from 250ms to 15ms, i tried 0, but then i got

errors when trying to view pages.

network.http.pipelining + network.http .proxy.pipelining = true, well, this one do magic, instead of waiting for each http request

firefox send, and then send another and so on until the page display, it will parallel it.

network.http.pipelining.maxrequests = 15, as you can guess, this will tell how many parallel requests can firefox do.

well,

you are all welcome to kiss me later...

Oded.