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.