How ORA-13236 was about to steal Christmas

Recently an error was reported to me ...

Message: ORA-00604: error occurred at recursive SQL level 1
ORA-13236: internal error in R-tree processing: [insertion at root (mdrbin_mem_ins_rt)]
ORA-13236: internal error in R-tree processing: [partition and pair bucket (mdrbin_optmz_mem_ins_node)]
ORA-13236: internal error in R-tree processing: [pair buckets (mdrbin_partition_pair_bckts)]
ORA-13236: internal error in R-tree processing: [mdrugnd - getting a node (mdrbin_pair_bckts)]
ORA-13234: failed to access R-tree-index table [MDRT Table]
ORA-29400: data cartridge error
Error - OCI_NODATA
ORA-06512: at "MDSYS.SDO_IDX", line 149
ORA-06512: at line 1

StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at X.SasquatchEngine.ProcessingUtilities.DataLayerRetrieval.RetrieveTaskData(Int32 orderTaskID, Int32 summitGroupID)



According to Oracle Support Note: Ora-13236 Internal Error In R-Tree Processing During Heavy DML [ID 443422.1] this error is due to a Bug (4570769) The solution procedure is described in the note. This bug was first seen in Oracle 10g, and so far it has not been updated.

The effects of this bug was the impossibility of insertion to a table containing an geometry element. Some geometries could be queried, some others on a very particular region couldn't be queried, so it was a symptom that a specific geographic region was corrupted inside the R-Index.

Actually we didn't try the workaround defined in the previously referred M.O.S. note, we rebuilt the R-Index instead.

The Oracle Open World, Day 1 - Key Conference I

This year the slogan is ... "Engineered for Innovation" According to the slogan is the kind of surprise Larry Ellison will have prepared for the Oracle community, so if you have some intuition and track the recent history of Oracle you may guess what the surprise from the big boss is about.

Larry Ellison entered in his own personal style, and the first phrase that he pronounced gave the guideline to what this was about ... he said "Sometime back when we bought Sun the customers said that Oracle was already into the hardware business, but we didn't receive the memo" ... referring to the hardware he was displaying on the stage he pointed at it and he said ... "we have a lot of hardware on the desktop". These keywords were the elements that defined what Larry Ellison wanted to show to the world this year.

The main goal of the hardware is to deliver the power of the hardware with highest performance and the lowest price. As usual the comparison against the IBM P795. Larry Ellison seems to enjoy these comparisons. The Exadata and Exalogic are aimed at achieve the maximum performance with the minimum cost, he referred the price of the servers is cheaper than the x86 servers world's cheapest servers.

What's the magic behind the servers ? The magic word is Parallelism and compression. The philosophy is to parallelize everything, this way data can be moved faster. The X machines parallelize both hardware and software. Since the current hardware cannot be made faster due to physical limitations, unless new technology arrive, in the mean time we have to use what's available and the challenge is to configure it so the best performance possible can be taken out of it.

Oracle is not creating a faster hardware, you cannot make the hardware faster due to physical constraints, so how can the hardware be made faster? Using the same hardware in a more efficient configuration. Not one big machine, not one big storage server, but a fundamental parallel everything architecture. If properly configured this increases throughput, performance, reduces energy and space consumption. The Exadata and Exalogic machines architecture probably is well known by now, infiniband connectors parallel infiniband network pipe connections moving data in and out. Infiniband is the fastest network. Faster, more reliable, and with a better performance than that of the internet.

"Performance is about moving data, not about processor", So if you think technology from this perspective, your goal will be to reduce the amount of data and increase the speed of moving it, if you are able to move less data at a faster speed the result will be the eXtreme performance.

The OS for the X machines was engineered to exploit the advantages of the parallelism, with all the advantages parallelism provides, such as no single point of failure and increased throughput and performance.

How can the amount of data be reduced? Well, from the hardware perspective the magic is created by compression. 10x data compression which means 10x less data to move, 10x data to store and another power related indirect consequences which makes this machine to require less physical space and have a small carbon dioxide footprint.

And what other surprises has revealed the big boss of Oracle? ... the Exanalytic machine (another X) and the Sun Supercluster (S), ... and I'll be talking about it in my next post.

The Oracle Open World, Day 0

Oracle Open World Day 0. Arrival to SFO.

Visiting the Oracle Open World in my personal experience is the opportunity to meet with peers, networking, updating knowledge, interact with people, meet the gurus, and have fun. My OOW started on Saturday, and the visit to the holy Oracle Headquarters is a must for me. It's the reminder of the long journey I've walked so far along with the company.

The temparature was nice, a typical autum californian day, beautiful, I love this weather. However, you never know, I remember some years back San Francisco had a severe cold front that was mixed with a high humidity level in the air and the consequence was that you could hear a lot of people sneezing and coughing in the conference rooms.

On the other hand, taking pictures of the architecture is always an amazing experience. The reflecting windows of the cylindrical shaped buildings that in my personal opinion emulate the physical shape of hard disks built with steel, concrete and glass. The blue colored windows disguise the buildings with the sky. Always an amazing landscape.

I don't know why the OOW doesn't start on Saturday, it would be great for me to use two weekend days and three vacation days rather than using one weekend day and four vacation days to attend the event. Most probably the logistics and the convenience for most of the people who attend the event.

The OOW is always a great experience that is absolutely worthy.

Display a formatted time difference

This is a simple query to display the difference between two dates in a formatted way.

Let's assume a given date in a default DD-MON-RR format like this:

select sysdate - to_date('08-OCT-75') from dual;
SYSDATE-TO_DATE('08-OCT-75')
----------------------------
13084.4081

Define a Variable which will hold the number in days and fraction of days

define DateDay = 13084.4081

Then the query

SELECT
TO_NUMBER(SUBSTR(A,1,4)) - 2000 years,
TO_NUMBER(SUBSTR(A,6,2)) - 01 months,
TO_NUMBER(SUBSTR(A,9,2)) - 01 days,
SUBSTR(A,12,2) hours,
SUBSTR(A,15,2) minutes,
SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE('20000101','YYYYMMDD')
+ &DateDay,'YYYY MM DD HH24:MI:SS') A
FROM DUAL);


and you've got your formatted output.

YEARS MONTHS DAYS HO MI SE
---------- ---------- ---------- -- -- --
35 9 27 09 47 40

Moving a datafile Online

A datafile cannot be completely moved online in a user transparent way, but what it can be done is to minimize the time required to perform the operation, and avoid the traditional procedure executed when the database is mounted, which permits more availability for the non affected production tablespaces.

The operation can be performed by creating a datafile copy, either with the traditional commands or by means of recovery manager. In this case the following script was prepared to move a datafile.

run {
copy datafile ''
to '';
sql 'ALTER TABLESPACE OFFLINE IMMEDIATE';
set newname for datafile ''
to '';
switch datafile all;
recover tablespace TABLESPACE_NAME;
sql 'ALTER TABLESPACE TABLESPACE_NAME ONLINE';
}

The most time consuming task is moving or copying the file by itself, so this operation goes first, once it's ready, then the datafile goes offline and the switch command is issued, this updates the controlfile contents and performs the recover from the time the tablespace was backed up until it was moved.

This is the part of the process that will generate errors if used with concurrent operation on this.

ERROR at line 1:
ORA-00376: file cannot be read at this time
ORA-01110: data file 7: ''

So a Maintenance window has to be open to be able to work on the affected tablespaces.

What happens with a Dataguard Environment?
It is not affected when datafiles are moved at the primary database since the recovery process looks for the datafile ID, not the datafile location at the destination.

The listener supports no services

This error means in general there are communication issues. It can be generic, so the outline here only depicts a very particular case.

Environment:

• Windows 2003, x86
• Oracle 11.1.0.7.0

When trying to start the listener, it took more time than usual, and after a while it showed up the following messages on the console:

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.1.0.7.0 - Production
...
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully


And when trying to display the services this was shown by the lsnrctl services command:
...
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=echo.world)(PORT=15
21)))
TNS-12535: TNS:operation timed out
TNS-12560: TNS:protocol adapter error
TNS-00505: Operation timed out
32-bit Windows Error: 60: Unknown error
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

The trace was enabled and the stack of messages was recorded at the trace file. By taking a look at the file a particular message came to my attention:
...
2011-07-05 15:09:59.231337 : nsinh_hoff:connection inherited
2011-07-05 15:09:59.231349 : nsinherit:connected
2011-07-05 15:09:59.237138 : nsglma:Listener's pid=3056
2011-07-05 15:09:59.249332 : nsglbgetRSPidx:returning ecode=0
2011-07-05 15:09:59.249389 : nsc2addr:(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=echo.oracle.com)(PORT=1521)))
2011-07-05 15:09:59.249561 : snlinGetAddrInfo:getaddrinfo() failed with error 11001
2011-07-05 15:09:59.249633 : nttbnd2addr:looking up IP addr for host: echo.oracle.com

From this point on, several other errors showed up, but this is the first point where everything began to be not normal.

The call getaddrinfo() failed, which means there was an inconsistency when trying to resolve the host IP address.

By taking a look at the hosts file and the ipconfig command output the discrepancies appeared.
Contents at the windows hosts file:
192.168.50.78 echo.world echo

ipconfig output:

C:\Oracle\app\product\11.1.0\db_1\NETWORK\ADMIN>ipconfig
Windows IP Configuration

Ethernet adapter Loopback Adapter:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 192.168.2.115
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

Ethernet adapter Local Area Connection:

Connection-specific DNS Suffix . : world
IP Address. . . . . . . . . . . . : 192.168.50.68
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.50.1

The network adapter is configured in a DHCP environment, and according to the Oracle installation manual for DHCP environments, a loopback adapter has to be configured, and its address is the one configured at the local hosts file.

So configuring the adapters according to the installation guide it makes the system to run as expected.

Yet Another ORA-00600

ORA-600 [kkoipt:incorrect pwj].

There are some ORA-600 which can be easily googled, and there are others which are pretty difficult to find. This is the case. M.O.S reports this ORA600 to be related to a bug (9929660) on the 11.2 platform and which is fixed on 12.0.

Recently this error showed up on a Windows 2003 platform with Oracle 11.1.0.7.0. After deciphering the almost cryptic related trace and dump file and diagnosing after the possible environment causes. I found out this error was triggered at one of the internal performance maintenance task, and the root cause was related to a lack of processes in the database.

This error was fixed in this particular case and platform by increasing the value of the PROCESSES instance parameter.

The only one who has the authority to decode and understand the trace files related to the Oracle internals is Oracle support ... unless you are willing to take the quest of reading them and trying to understand them as your last resource.

The information provided in this post is only for information purposes, and to share a very particular experience in my local environment. The only one authorized to provide a diagnose about an ORA-00600 is Oracle Support Services.

V$OBJECT_USAGE view empty.

This is a very old issue, but one that sometimes is hard to remember when needed. I tried to monitor index usage to get rid of some indexes which in my opinion are not in use. I implemented the traditional method of index monitoring:

Start monitoring the index.
alter index SchemaOwner.IndexName monitoring usage;

After a while, query the V$OBJECT_USAGE view.

select * from V$OBJECT_USAGE;

It came to my surprise that the view was empty. The reason ... I knew it some time back ... but I forgot. I googled and I found a very good reference from Alex Gorvachev, who went through some sort of similar experience ( v$object_usage empty ? ).

The reason is because internally the v$object_usage view displays filters the objects that belong to the user who performs the query. If you are the owner of the index, or if you have access to the schema owner password you just go ahead, logon to the database with the schema owner password and query the view, if you don't have access to the password or you are not the owner of the index, even if you have DBA privileges you won't be able to query the view.

So the trick in this case is to create a home made view that works around this issue. It is assumed you have enough privileges to access the underlying SYS objects queried by the view.

create or replace view V$ALL_OBJECT_USAGE
(OWNER
,INDEX_NAME
,TABLE_NAME
,MONITORING
,USED
,START_MONITORING
,END_MONITORING
)
as
select u.name
, io.name
, t.name
, decode(bitand(i.flags, 65536), 0, 'NO', 'YES')
, decode(bitand(ou.flags, 1), 0, 'NO', 'YES')
, ou.start_monitoring
, ou.end_monitoring
from
sys.user$ u
, sys.obj$ io
, sys.obj$ t
, sys.ind$ i
, sys.object_usage ou
where
i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#
/

enjoy!

The view source code was taken as is from the Alex Gorvachev's blog.

Where is the password column from DBA_USERS in 11g?

Oracle 11g brought several security enhancements, as it is well known by the 11g users. On previous Oracle versions it was possible to query the DBA_USERS PASSWORD column to get the hashed password string. It was useful when someone tried to temporarily reset the user's password and restore it to its original value without actually knowing it.

The command:

ALTER USER IDENTIFID BY VALUES '14C785FC66029BF9';

it could take the hashed value from the DBA_USERS data dictionary view. However starting with Oracle 11g this column is null ... so where are we supposed to take this hashed value from?.

SQL> SELECT USERNAME, PASSWORD
2 FROM DBA_USERS
3 WHERE USERNAME='SYSTEM';

USERNAME PASSWORD
--------------- ------------------------------
SYSTEM <>


By taking a look at the underlying data dictionary table where the DBA_USERS view is built on, we can easily find the data dictionary table is SYS.USER$, and it has a column named ... guess ... PASSWORD.

Oracle 11g only makes it a little bit more difficult to get the hashed password, but if you have enough privileges you can still apply the traditional procedure to temporarily reset the password, and still have access to the hashed password.

SQL> SELECT NAME, PASSWORD
FROM SYS.USER$
WHERE NAME = 'SYSTEM' ;

NAME PASSWORD
------------------------------ ------------------------------
SYSTEM 2D594E86F93B17A1


SQL> ALTER USER SYSTEM IDENTIFIED BY tempPasswd;
User altered.

SQL> connect system/tempPasswd@orcl;
Connected.

SQL> ALTER USER SYSTEM IDENTIFIED BY VALUES '2D594E86F93B17A1'
User altered.

SQL> connect system/manager@orcl
Connected.