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'

ORA-01031: insufficient privileges upon instance startup

Posted by David Yahalom under Windows, Oracle

A friend of mine who is a junior DBA approached me today with a strange problem that was bugging him for hours. He installed a new Windows Oracle 9i server and wanted to create the databases manually (something I always recommend over DBCA, as you get finger grain control over your database creation process and you feel more “in control” of whats going on inside your DB).

He created an init.ora file for his Instance and since he wanted to use OS authentication for Oracle, he left the parameter remote_login_passwordfile to its default value (”none”).

He then proceeded to creating his instance using the following command:

oradim -new -sid InstanceSID -startmode m -pfile C:location_to_pfileinit.ora -intpwd password

After which, the instance was created. He proceeded to creating the database itself. Since he was all keen about doing this manually he opened up a Windows CMD and did the following steps:

SET ORACLE_SID=InstanceSID
C:> sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Wed Dec 13 10:18:27 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn sys/password as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=C:location_to_fileinit.ora;
ORA-01031: insufficient privileges
SQL>

As you see, he got an ORA-01031. He didn’t understand what is causing this. His O/S user was a member of the ORA_DBA group. He tried logging off windows and then logging backup in. Nothing.

The solution is actually quite simple. By default, if you install Oracle and don’t use the network configuration assistance you get a ORA_HOME without an SQLNET.ORA file. Without it, Oracle will not be able to use O/S authentication.

To solve this problem, simply create a file named sqlnet.ora in your %ORA_HOME%\network\network\admin directory and add to it the following line:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Save the file.

After this, try starting the instance using the same procedure described above and your O/S based logon should work just fine!

Materialized view will become invalid after a refresh - documented Oracle bug in older versions+ workaround.

Posted by David Yahalom under Oracle

Today I stumbled across a very annoying Oracle bug regarding Materialized Views becoming invalid after a refresh. I’m talking about Oracle Bug 2639679 (QUERY_REWRITE flag changes for MVIEW with DATE RANGE in WHERE) which affects Oracle 8i and even some versions of 9i (can’t be sure which versions exactly as Metalink says this is resolved in 9i but clearly this is not the case).

When you create a materialized view with DISABLE QUERY REWRITE option, when you refresh the materialized view (using DBMS_SNAPSHOT.REFRESH, DBMS_REFRESH.REFRESH or any other method), the QUERY REWRITE flag will automatically be turned back on. This is a problem if your materialized view has a where clause in it as QUERY REWRITE + a where clause is a no-no.

This bug will cause your materialize view to become invalid every time you refresh it. Very annoying.

The solution I found was to add a disable query rewrite command before and after the refresh of the materialized view.

EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite');
DBMS_SNAPSHOT.REFRESH( 'MV_NAME','C');
EXECUTE IMMEDIATE('alter materialized view SCHEMA.MV_NAME disable query rewrite');

You can put this in a database job to have the materialized view refresh automatically.

Hope this helps.

Insufficient privileges error while installing Oracle DBCA under Linux

Posted by David Yahalom under Oracle

When installing Oracle 9 on Linux, you may receive the following error during installation:

“you do not have sufficient privileges to write to the specified path Database Configuration Assistant 9.2.0.1.0…”

The problem usually happens when you forget to set the $ORACLE_BASE (not to be confused with $ORACLE_HOME) environment variable prior to initiating the OUI installation.

You want to place such variables in the .bash_profile file located under the oracle user home directory (usually /home/oracle).

If you still get this error even after you have set $ORACLE_BASE (you did remember to you a “-” when SUing to the oracle user, right? su - oracle) then make sure you have proper permissions set.

ORA-600 [16201] when dropping a procedure

Posted by David Yahalom under Oracle

In certain older versions of Oracle database (such as 8.1.7.4 like this case) you may receive an ORA-600 error when trying to drop or recompile a database PL/SQL package or procedure. Usually when the source is wrapped.

SQL> drop procedure schema.proc_name;

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], []

SQL> create or replace procedure schema.proc_name
as
begin
end;
/

*
ERROR at line 1:
ORA-00600: internal error code, arguments: [16201], [], [], [], [], [], [], []

This is a documented Oracle bug (No. 2422726). It affects Oracle versions 8.1.7.4, 9.0.1.4, 9.2.0.1 and fixed in Oracle 9.2.0.2 and 10g. However, even if you are running an older version of Oracle there’s a possible workaround available to solve this issue. Note, this is an ugly hack, but it works, and sometimes your only solution for this annoying problem.

The solution is as follows:

1) Connect as SYS to the problematic db.

2) Run the following query to identify the object# of the INVALID object you can’t drop.

SQL> select obj#,owner#,type# from sys.obj$ where name = 'PROC_NAME';

OBJ#     OWNER#      TYPE#
---------- ---------- ----------
1396504          5          7

3) Now try and select the procedure from v$procedure using the OBJ# from the above query.

SQL> select * from procedure$ where obj# in (1396504);

no rows selected

4) You can’t. That’s because of the above mentioned bug.

5) The only way to fix this is to insert a fake row into the procedure$ view to fool Oracle to allow you to successfully drop the procedure.

SQL> insert into procedure$ values (1396504, '-----------------------',NULL,2);

1 row created.

SQL> commit;

Commit complete.

6) Now you can successfully drop the INVALID procedure.

SQL> drop procedure schema.proc_name;

Procedure dropped.

As I said, it’s a hack, so you are better of upgrading your Oracle installation to a version not effected by this bug. But as we all know, many times this is simply not an option. And for those occasions the solution I’ve written here seems to be the only one.

Good luck!