Sunday, November 20, 2005

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

Saturday, November 19, 2005

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.

Sunday, November 06, 2005

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.

Thursday, November 03, 2005

Thursday, October 27, 2005

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.

Saturday, September 24, 2005

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.

Tuesday, August 30, 2005

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

Saturday, July 23, 2005

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.

Thursday, June 30, 2005

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.

Tuesday, June 28, 2005

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

Tuesday, June 21, 2005

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

Monday, June 20, 2005

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

Saturday, June 18, 2005

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.

Tuesday, June 14, 2005

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.



Monday, June 13, 2005

RPM Commands

List of basic linux rpm commands:

  • Install an rpm:
      rpm -ivh <rpm package>
  • Remove an rpm:
      rpm -e <rpm package>
  • Upgrade an rpm:
      rpm -Uvh <rpm package>
    Note: This command is also for installing a new rpm, the different between -ivh to -Uvh is that ivh will not
    install the rpm if a previous version of that rpm is already installed.
  • List all rpms installed on the server:
      rpm -qa
  • List files of installed rpm:
      rpm -ql <rpm package>
  • List files in an rpm file:
      rpm -qpl <package name>
  • Get the rpm that owns a file:
      rpm -qf <full path file>

Oded.

Tuesday, June 07, 2005

Oracle 10g Release 2 new features

Well, Oracle 10g second release is on his way (in about 2 more months or so, as i know), and there are some new  features and tools worth checking:

  • First of all, looks like oracle streams will do a very good job, and will be a good rival to other tools we had to acquire to have a good replication solution.
  • Oracle ASM (Automatic Storage Management), well, I wouldn't use it in a regular one instance database, but it could be a replacement for the not so perfect OCFS in RAC.
  • Transportable tablespaces are online now. No need to enter the tablespace to read only mode before exporting.
  • ADDM – Automatic Database Diagnostic Monitor, helps tune your database.
  • Well, as much as I hate GUI tools, especially Oracle java GUI tools, I think the Oracle Enterprise Manager (OEM) with the grid control is now worth checking. OEM now use SGA-attach mechanism for monitoring oracle (Just like Quest and Veritas).
  • Need to build small apps? back-end apps? need to do it quick? well, oracle now have HTML DB, looks good, and easy to use.
  • Like PhpMyAdmin, oracle got the Project Columbus for schema/object web browsing/altering/creating
  • Oracle Backup, a new tool that will give rman the ability to backup to tapes without the need for a third party products. Supprts rman 8i, 9i and 10g.

Well, much work to do, lot of things to learn, and no time!!!

Oded.

 

Oracle Intermedia Text - 8.1.7

 Well, today I experienced using the Oracle Intermedia Text, so that what i got from it:
  • Intermedia Index is from type DOMAIN

  • When creating an Intermedia index oracle creates 4-5 tables and an index, all have the DR$ prefix

  • A good metalink note about creating intermedia indexes you can found here

  • "ORA-29863: warning in the execution of ODCIINDEXCREATE routine" when runing ddl's on the text indexes, this error can occur, with no farther information!. Check the errors table (CTX_INDEX_ERRORS) for more information.

  • Do not use ctxsrv for maintaning your indexes, read metalink note: 132689.1 for more information about maintaining intermedia indexes.

  • Use ctxsys.ctx_ddl instead of alter index when ever it is possible

  • Oracle Intermedia Text FAQ metalink note

  • Do not create an oracle intermedia index online, this will result an error for each insert/update/delete on the table, if you wish to do it online

then you can do it by: 1) create the index with nopopulate property, this will create the index with no information 2) update the indexed column on each row in the table to it’s current value 3) sync the index

  • Get all index errors

select err_timestamp, err_text from ctxsys.CTX_INDEX_ERRORS order by err_timestamp desc;

  • Get all changes made for the indexes that are in pending mode (waits for index sync)

select * from ctxsys.ctx_pending

  • Create storage preferences for the oracle text indexes

Begin

Ctx_Ddl.Drop_Preference ('my_text_storage' );

Ctx_Ddl.Create_Preference('my_text_storage', 'BASIC_STORAGE');

ctx_ddl.set_attribute('my_text_storage','I_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','K_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','R_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','N_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','I_INDEX_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

ctx_ddl.set_attribute('my_text_storage','P_TABLE_CLAUSE', 'storage (MAXEXTENTS unlimited initial 10M next 10M)');

end;

/

  • Create an index with the new storage prefs

create index mytextindex on mytable(data) indextype is ctxsys.context parameters ('storage my_text_storage')

Oracle bench mark project

Swingbench is a free load generator (and benchmarks) designed to stress test an Oracle database (9i or 10g). And view the load in some graphs.

http://www.dominicgiles.com/swingbench.php

Monday, June 06, 2005

Oracle Recovery Manager - RMAN - Commands

# set these parameters before running rman
NLS_LANG=american
NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'


# Create tablespace for rman
CREATE TABLESPACE rman LOGGING DATAFILE '/oracle_db/db1/test/rman.dbf' SIZE 100M REUSE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;


# create user for rman
CREATE USER rman IDENTIFIED by rman
DEFAULT TABLESPACE rman
TEMpORARY TABLESPACE temp
QUOTA UNLIMITED ON rman;


# grant rman catalog user the needed privs
GRANT RECOVERY_CATALOG_OWNER TO rman;
GRANT CONNECT, RESOURCE TO rman;

# create the catalog
% rman TARGET system/manager CATALOG rman/cat@catdb
RMAN> CREATE CATALOG


# Register the target database with rman, each database needs to be backed up, run:
% rman TARGET system/manager@bpdb CATALOG rman/rman@rmandb
REGISTER DATABASE;


# configure rman to save backups for 14 days!
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;

# only when backups to disks (when backup to sbt, then user PARAM) - change the backup PATH, %U => unique value
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/oracle_backup/%U';

# configure the default device to disk (this is already the default :)
CONFIGURE DEFAULT DEVICE TYPE TO DISK;

# Backing up the database and control file and archive log
backup database current controlfile archivelog all;
or
backup database ;
backup current controlfile ;
backup archivelog all;


# backup all archive log and delete the files after
backup archivelog all delete input;

Saturday, June 04, 2005

Install Oracle 9i on FC3 (Fedora Core 3)

By following this tutorial here
i've installed oracle 9i on my FC3

Errors that happened to me:
1.
Unable to load native library: /tmp/OraInstall2003-10-25_03-14-57PM/jre/lib/i386/libjava.so:
symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6


This error fixed by adding the libcwait.so path to /etc/ld.so.preload

2.
Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2005-06-04_12-23-54PM/jre/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1419)
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1335)
at java.lang.Runtime.loadLibrary0(Runtime.java:749)
at java.lang.System.loadLibrary(System.java:820)
at sun.security.action.LoadLibraryAction.run(LoadLibraryAction.java:53)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.NativeLibLoader.loadLibraries(NativeLibLoader.java:41)
at sun.awt.DebugHelper.(DebugHelper.java:29)
at java.awt.Component.(Component.java:356)
at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:593)


This one can be fixed by installing the openmotif rpm that depends on the xorg-x11-deprecated-libs rpm that contains the libXp.so.6 file.

MyOSS Magazine

Malaysian Online Open Source Magazine

Cool open source project for exporting closed oracle database

FlushUnload
The FlashUnload project gives users the ability to export data from corrupted Oracle databases. It can also be used to export data in a very fast way, as the Oracle buffer cache is not used. Additionally only subsets of the data can be read.

Oracle - String Aggregation Techniques

How to select rows into columns

First post

This, is my first post to my blog, just for checking.