Limelight

Posted by David Yahalom under Security, Oracle

I would like to thank the several people that have emailed me about this. Pete Finnegan, who is one of the most prominent figures in the world of Oracle databases and Oracle Security has mentioned AuthorityBase on his web site!

He has a writeup mentioning the Oracle Security presentation I’ve created for my company (XpertOne1) and he seemed to liked it.

Check it out at Pete’s site!

Intermittent ORA-12545 When Trying To Connect To RAC Database

Posted by David Yahalom under RAC, Oracle

Well, got another RAC tidbit for you today. I’ve been doing some very interesting RAC installations in the past few weeks and came across several “bugs” (or “random features” in Oracle-tongue ) I felt like sharing.

So, you have a brand new 10g RAC cluster installed and when you try to connect using OCI (probably JDBC thin as well) only to recieve intermittent ORA-12545 errors. The client will connect fine every other attempt or so.

This error happens when you enable server side load balancing but the client does not have domain address / DNS search function setup (or valid /etc/hosts file) so it cannot translate a hostname to a proper IP address.

The solution is simple, make sure that the client from which you are trying to connect is able to resolve all the hostnames in your RAC installation. Either via registering the nodes in a DNS server or by updateding the /etc/hosts file (remember, Windows also has an /etc/hosts file under <WINDOWS>\system32\drivers\etc\hosts).

I’ve seen many DBAs forget that for server-side LOB you need to be able to resolve each cluster node hostname from the client EVEN when you only use IPs in your TNSNAMES.ORA entries. Even without server-side LOB, it’s good practice to be able to resolve RAC hostnames from the client.

This isn’t actually a bug, it’s by-design and if you understand they way server-side LOB works in RAC you’ll see why the client has to be able to resolve RAC hostnames to IP addresses.

Ora-12520 When listeners on VIP in 10g RAC Setup

Posted by David Yahalom under RAC, Oracle

A few days ago I’ve stumbled across an annoying problem with Oracle 10.2.0.1 (yep, need to patch it) RAC causing my OCI client fail to connect with an ORA-12520 error (ORA-12520: TNS:listener could not find available handler for requested type of server ).

Everything was configured correctly on both the server side and the client.

1. Remote_Listener parameter is set to an alias which is defined in the Tnsnames.ora file on both the nodes.
2. The hostname is setup correctly in the tnsnames.ora file.
3. The IP address, VIP and hostname are all properly configured in /etc/hosts on all nodes.

This is a documented Oracle bug with a complete note on this in Metalink (342419.1). 10g RAC instances have a problem where the instances are not registering themselves correctly with the virtual IP address. They get registered to the real ip address where the listener is not listening.

The workaround is very simple:

Define a LOCAL_LISTENER entry for each node in the node’s local TNSNAMES.ORA file:

LISTENER_NODE_XXX =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = <node1-vip>)(PORT =1521))
)

Then issue the following command in the sqlplus prompt:

sql>Alter system set LOCAL_LISTENER= 'LISTENER_XXXX' scope=both sid='SID1'.

Remember to do the same for all other nodes in the cluster (each node should have an entry in its TNSNAMES.ORA file with the LISTENER_NODE_XXX definition + setting the LOCAL_LISTENER spfile parameter to the same TNSNAMES.ORA entry).

Simulate load on your linux server using a one-liner

Posted by David Yahalom under Unix, Linux, General IT

Yesterday I needed a quick way to simulate load on a Linux sever. I wanted to find out that my monitoring system is working and sending alerts.

While there are many many tools that can do this, all I needed was a simple bash one-liner shell script that can make the CPUs beg for mercy.

What I ended up using is this:

dd if=/dev/zero bs=100M | gzip | gzip -d | gzip | gzip -d | gzip | gzip -d > /dev/null &

Send a few of these babies to the background and you’ll start seeing smoke coming from your server soon enough.

Start your servers: Intel’s next-gen CPU smokes!

Posted by David Yahalom under Hardware, Oracle

UPDATE: Whoops! Had bad URL in link to Nehalem Benchmarks. Fixed!

AnandTech, one of the best sources of hardware news & reviews,  have just published preliminary benchmarks of Intel’s next-gen CPU - the Nehalem.

Even when the CPU is not using final silicon and being coupled with a very early and unstable motherboard, it smoked the benchmarks suppressing a Penryn-based Core2 quad by as much as %50! In fact, a 2.6Ghz Nehalem is faster than a 3.2Ghz Penryn! Amazing.

This got me thinking about the x86 server market, especially when compared to non-x86 servers such as SPARC or PowerPC machines.
Even now we see that dollar-to-dollar, x86 servers can sometime outperform non-x86 hardware.

Nowadays when Oracle RAC clusters based on cheap $7000 x86 servers outperform single-instance super expensive high-end Sun/IBM servers, Intel’s new Nehalem could very well be the final proof many DBAs need in order to ditch their old SPARC/PowerPC servers and transition to Linux x86 RAC installations.

Anyway, AMD is going to launch a new chip design in early 2009 and I still see great promise in IBMs Cell CPU deign.

Interesting times ahead.

Read more about Nehalem’s architecture and on-die memory controller here.

Oracle Virtual Directory

Posted by David Yahalom under ETL, General IT, Oracle

Another presentation I’ve made not too long ago at an Israel Oracle User Group meeting (sponsored in part by Xpert-One1, the consulting and solution provider company I work for) was about a relatively little known product (at least by Oracle terms) called Oracle Virtual Directory.

OVD allows for Enterprise Level LDAP without synchronization.

Oracle Virtual Directory provides LDAP and XML views of existing enterprise identity information without synchronizing or moving data from its native locations.

OVD can connect to pretty much anything JAVA can connect to and expose several different LDAP directories and RDMBS data repositories as a single LDAP tree.

Think about it. You can virtually “unify” all the different directories in your organizations (be it RDBMS servers or LDAP directories) as a single directory service - which is so much easier to work with. And without any sort of synchronization.

Very very cool.

You can get my presentation here.

OVD is availiable for download, free, from OTN. 

The secure Oracle database - howto

Posted by David Yahalom under Security, Oracle

Last week I’ve held a 45 minute presentation about Oracle-made DB security solutions at a customer convention held by Xpert-One1, the company I work for (Xpert-one1).

It was a pretty neat presentation focusing on how to achieve 360 degrees of protection for your database, how most security exploits originate from within the organization (an IDC study shows as much as %80) and how while network security is well understood (firewalls, VPNs, etc) database security is almost always forsaken (not many businesses expect their DBAs to be security focused).

My presentation covers the following Oracle products and how the fit in creating a secure database:

Oracle Database Vault
Oracle Advanced Security
Oracle Transparent Table Encryption
Oracle Label Security & Oracle Virtual Private Database
Oracle Secure Backup
Oracle Grid Control

You can download my presentation here.

It’s high level, since the audience we were targeting were CTOs, IT managers, security guys and not actual DBAs.

Fell free to leave comments or contact me if you have questions or want more information.

The magic that is DUAL!

Posted by David Yahalom under Oracle

I’ve stumbled across a very interesting write-up at AskTom regarding Oracle’s DUAL table. Some very interesting discussion going there including the origin of the name “Dual” for the obviously singular dual table. Recommended read.

And for some quick trivia. What’s the command masked below responsible for such output?

SVRMGR> select * from dual;
D
-
X
1 row selected.

SVRMGR> XXXXXXXXXXXXXXXXXXXX;
Statement processed.

SVRMGR> select * from dual;
ADDR     INDX       INST_ID    D
-------- ---------- ---------- -
01680288          0          1 X
1 row selected.

To find the answer browse the post at AskTom.

How to identify CPU hogging Oracle sessions on a Windows server

Posted by David Yahalom under Oracle

The Oracle Server process model is different between Windows and Linux/Unix. While in Linux the Oracle instance uses a dedicated process model on Windows server, the instance is composed from one oracle.exe process and many different threads. Each thread represent either a background “process” (PMON, SMON…) or a foreground user session.

So, when you are running Oracle server on Windows and encounter a situation where the server CPUs are nearing 100% utilization, a quick glimpse in Task Manager only reveals that oracle.exe hogging the CPU. Nothing regarding which specific thread or session is responsible for the high load.

For us to identify the taxing session we will need to do some basic digging.

First, we’ll need to know which thread within the oracle.exe process is hogging the processor. In order to do so we’ll need a process explorer that supports displaying threads within processes. A good free choice is QSlice, a free program that comes as part of the Windows2000 resource kit. You can get it here.

When running QSlice you’ll see a list of process IDs, names and CPU utilization for each process. Locate oracle.exe and double click it.

Now you’ll see a list composed of TID (Thread IDs), time / CS and % of CPU utilized within the oracle.exe process. Identify the thread which is taxing the CPU (for example, thread 6b88) by looking at the TID (Thread ID) column with the highest user or system CPU time and convert the value in TID column from HEX to DEC.

Open SQL*PLUS and logon as sysdba.

Use the following query to get details about the specific session

SQL> select proc.spid ThreadNO,
sess.username Username,
sess.osuser OSUser,
sess.status Status,
sess.sid SessionID,
sess.program Program
from v$process proc, v$session sess, v$bgprocess bg
where sess.paddr = proc.addr
and bg.paddr(+) = proc.addr
and proc.spid in (TID_VALUE_CONVERTED_TO_DEC)

(you can modify the query to include more columns to be displayed from v$session, v$process and v$bgprocess depending on your specific needs).

You can also retrive the specific SQL the CPU hogging session is running by running the following SQL:

SQL> select sqlarea.sql_text
from v$process proc,
v$session sess,
v$sqlarea sqlarea
where proc.addr = sess.paddr
and sess.sql_hash_value = sqlarea.hash_value
and proc.spid in (TID_VALUE_CONVERETED_TO_DEC)

You can now use the ALTER SYSTEM KILL SESSION command to kill the taxing session if needed.

Search for a given string in all fields of an entire schema

Posted by David Yahalom under Oracle

A developer friend of mine requested my help in writing a stored procedure that will allow him to search an entire schema for a certain “string”.

To help him I’ve written this small and dirty PL/SQL procedure. Keep in mind that while it will most likely work out of the box, it will probably require some debugging on your parts as it was written for a small dev project and isn’t fully tested. Feel free to leave a comment if you find anything borken with the code.

Otherwise, enjoy.

CREATE OR REPLACE procedure search_db (p_search varchar, p_type varchar)
/*
* 	This procedure will search a user's schema (all tables) for columns matching the user's input.
*
*	####### Please create the following table before you run this procedure:
*	create table search_db_results(result varchar2(256));
*
*
*	This table will contain the result of the procedure run so that you can view intermediate search results while the procedure is running.
*
*	You pass two parameters to this procedure:
*
*
*	1) Search string / number / date 			(REQUIRED)
*	2) Search datatype 					(REQUIRED)
*
*	Example:
*
*	exec search_db('hello','VARCHAR2') 		-- will search for rows in all tables that have a VARCHAR2 column with "hello" as the data.
*	exec search_db('01-JAN-2008','DATE') 	-- will search for all rows in all tables that have a DATE column with the data '01-JAN-2008' in it.
*	exec search_db(1000,'NUMBER') 			-- will search for all rows in all tables that have a NUMBER column with the data 1000 in it.
*
*
*	Allowed data types: VARCHAR2, CHAR, DATE, NUMBER, FLOAT.
*
*
*
*	**********************************************************************************************************************************************
*	WARNING!!!!! if you have a large schema be advised that the search can take anywhere from minutes to hours!

*	**********************************************************************************************************************************************
*/

IS
TYPE tab_name_arr IS VARRAY(10000) of varchar2(256);

v_tab_arr1 tab_name_arr;	 		/* ARRAY TO HOLD ALL TABLES IN THE USER SCHEMA */
v_col_arr1	tab_name_arr;		/* ARRAY TO HOLD ALL COLUMNS IN EACH TABLE */

v_amount_of_tables number(10);		/* this holds the amount of tables in the current user schema so that the for loop will know how many times to run */
v_amount_of_cols	number(10);		/* when searching in a table, this holds the amount of columns in that table so that the for loop searching the table will know how many iterations it needs */
v_search_result	number(10);		/* when searching the table, this holds the amount of results found. We use this is that if the amount of result found is greated than 0 we will print the name of the table and the column */
v_result_string	varchar2(254);
BEGIN

v_tab_arr1 := tab_name_arr();	/*INITIALIZE THE ARRAY*/
v_col_arr1 := tab_name_arr();	/*INITIALIZE THE ARRAY*/

v_col_arr1.EXTEND(1000);		/* INITIALIZE THE ARRAY to the maximum amount of columns allowed in a table */

/* This will return the amount of tables in the user schema so that we know how many times we need to invoke the for loop */
select count(table_name)
into v_amount_of_tables
from user_tables;

v_tab_arr1.EXTEND(v_amount_of_tables);	/*INITIALIZE THE ARRAY to the number of tables found in the user's schema */

FOR i in 1..v_amount_of_tables LOOP		/*LOOP until we reach the maximum amount of tables in the user schema */

/* start populating the tables array with table names. The data is read fomr the data dictionary */

select table_name
into v_tab_arr1(i)
from
(
select rownum a, table_name
from user_tables
order by table_name
)
where a = i;

END LOOP;

/* now, after we have an array with all the names of the tables in the user's schmea, we'll start going
over each table and get all of its columns so that we can search every column */

FOR i in 1..v_amount_of_tables LOOP

/*select the amount of columns in the table where the data_type matches the data type the user passed as a parameter to the procedure */

select count(*)
into  v_amount_of_cols
from user_tab_columns
where table_name = v_tab_arr1(i)
and data_type = p_type;

/* start searching the clumns ONLY IF there is at least one column with the requested data type in the table */

if v_amount_of_cols <> 0 then

/* do the search for every column in the table */

FOR j in 1..v_amount_of_cols LOOP

select column_name
into v_col_arr1(j)
from
(
select rownum a, column_name
from user_tab_columns
where table_name = v_tab_arr1(i)
and data_type = p_type
)
where a = j;

/* each type of data_type has its own SQL query used to search. Here we execute different queries based on the user passed parameter of requested data type */

IF p_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') then
execute immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || lower(v_col_arr1(j)) || ' like ' || '''' || '%' || lower(p_search) || '%' || '''' into v_search_result;
end if;

if p_type in ('DATE') then
execute immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || v_col_arr1(j) || ' = ' || '''' || p_search || '''' into v_search_result;
end if;

if p_type in ('NUMBER', 'FLOAT') then
execute immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || v_col_arr1(j) || ' = ' ||  p_search into v_search_result;
end if;

/* if there is at least one row in the table which contains data, return the table name and column name */

if v_search_result > 0 then
v_result_string :=  v_tab_arr1(i) || '.' || v_col_arr1(j);
execute immediate 'insert into search_db_results values (' || '''' || v_result_string || '''' || ')';
commit;
end if;

END LOOP;

end if;

end loop;

END;
/

Of course in case you don’t mind loosing flexibility a bit you can also use this, much simpler, dynamically generated SQL code:

select 'select '||column_name||' from '||table_name||' where '||column_name||' > sysdate;'
from user_tab_columns
where data_type='DATE'