Oracle by Madrid

ORA-00600 [kgiinb_invalid_obj]

2012-01-19T12:14:00.003-06:00

Another ORA-00600 error:

This error was recently reported to me,

ORA-00600: internal error code, arguments: [kgiinb_invalid_obj], [0x102BE8B38],
[0x12CF3EED8], [], [], [], [], [], [], [], [], []at
Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode,
OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object
src, String procedure ....

Oracle Server - Enterprise Edition - Version: 11.1.0.7
This problem can occur on any platform.
Oracle Server Enterprise Edition - Version: 11.1.0.7

After some research I found that basically this is due to a bug (7420394) which can be fixed by applying the patch for it, or upgrading to 11.2.0.

ORA-00700 [kesqsMakeSql-invstat:cpuTime]

2012-01-17T09:23:00.002-06:00

ORA-00700: soft internal error, arguments: [kesqsMakeSql-invstat:cpuTime], [], [], [], [], [], [], [
] ... hmmm ... it looks impressive, overall considering that an ORA-00600 is not one that probably is going to make your DBA day.

Actually it is not as tragic and epic as it could seem to be at first glance, it has to do with a bug. In this particular case it showed up in an Oracle 11g Rel.1 (11.1.0.7.0) version on a x64 windows platform. This error is triggered when the SYS_AUTO_SQL_TUNING_TASK runs, and it may spontaneously show up and keep on showing up for a while. It may happen to be annoying.

The root reason is the Bug 7025700 , and some other related bugs, Bug 7757533, Bug 8224438, Bug 7643188, the arguments of the ORA-00700 may be slightly different: [KESQSMAKESQL-INVSTAT:CPUTIME], or [KESQSMAKESQL-INVSTAT:ELPSTIME], and the solution is to apply the patch 7643188 or apply the PSU patch to upgrade to 11.1.0.7.2.

Applying the patch will fix the problem from occurring in the future, but it won't fix the current corrupted data at the internal statistics tables, but eventually those corrupt rows will be flushed away, so in the mean time you'll have jut to get used to acknowledge it and manually clear the errrors at your E.M. Console.

How ORA-13236 was about to steal Christmas

2011-12-16T11:27:00.002-06:00

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

2011-10-04T16:46:00.006-05:00

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

2011-10-04T16:32:00.003-05:00

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

2011-08-04T09:56:00.003-05:00

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

2011-07-07T13:54:00.003-05:00

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

2011-07-05T15:33:00.004-05:00

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

2011-06-28T11:36:00.002-05:00

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.

2011-04-11T16:07:00.002-05:00

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?

2011-01-18T15:38:00.002-06:00

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.

Reading the alert.log as a local table.

2010-11-10T16:41:00.003-06:00

Reading the alert.log file is a must for the DBA. This is the primary source of information about the Database. The traditional way to read it is by means of any text based tool that can open the file and lets you take a look at it and look for specific text patterns. This requires access to the Operating System, which probably is something the DBA can take for granted at most shops. However, it happens that because of security issues, access to the Operating System is restricted ... sounds familiar? ... either you have someone to send you the file on a regular basis, you have a link to the file (assuming proper permissions) or you have to create your own routines to access it. Another natural choice is to access it by means of either the EM Control Console or the Grid Console, and the of course an External Table.

In this example I will create an externa table to access the alert.log file

create table ALERT_LOG (
text_line varchar2( 512)
)
organization external (
type ORACLE_LOADER
default directory BACKGROUND_DUMP_DEST_DIR
access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile
)
location( 'alertORCL.log')
);

This example assumes the BACKGROUND_DUMP_DEST_DIR exists and you have access to it. It also assumes your Oracle instance is the classical ORCL instance.

Once the external table has been created just access it as a regular Oracle table with select only privileges.

SELECT * FROM ALERT_LOG;

TEXT_LINE
--------------------------------------------------------------------------------
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled

Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in client-side pfile C:\ORACLE\APP\ADMIN\ORCL\PFILE\I
NIT.ORA on machine HECTOR

System parameters with non-default values:
processes = 150
memory_target = 1648M
control_files = "C:\ORACLE\APP\ORADATA\ORCL\CONTROL01.CTL"
control_files = "C:\ORACLE\APP\ORADATA\ORCL\CONTROL02.CTL"
control_files = "C:\ORACLE\APP\ORADATA\ORCL\CONTROL03.CTL"

19 rows selected.

This works for 9i, 10g and 11g, (Rel. 1 and Rel. 2). In the particular case of 11g Oracle created a new X$ view which can be accessed just like any other X$ view to read the alert.log file, the X$DBGALERTEXT view. You must have proper privileges to access it.

Reference: Oracle Data and Database Management Utilities. Chapter 3

Oracle Magazine Nov-Dec 2010

2010-10-12T10:10:00.003-05:00

I want to thank Oracle Magazine for publishing the interview at the Peer-To-Peer section

Ref. http://www.oracle.com/technetwork/issue-archive/2010/10-nov/o60peer-176064.html

"Which new features in Oracle Database are you currently finding most valuable? Oracle Automatic Storage Management and the Volume Manager. Another nice feature is Secure Files, which improves performance, optimizes storage, and provides an additional security layer.

What advice do you have for those just getting into application development? When designing an application, seriously evaluate the amount of intelligence it’s going to manage. This will define the amount of coding, complexity, round-trips to the server, and scalability. And be aware that the application user is different from the big database user.

Tell us about your role with Oracle University (OU) and the value you see in this program. I’ve been a certified OU instructor for more than 15 years, and I really believe that an OU course is the best way to get acquainted with Oracle technology. The courses provide a good balance between theory and practice, and professionals can start being productive the day the course concludes."

ORA-00942 Querying Tablespaces from Enterprise Manager

2010-07-19T12:32:00.002-05:00

In order to meet the minimum security requirements, the SYSTEM user use must be restricted, only the actual DBA must have access to it. Other users requiring access to Enterprise Manager to monitor and "view" must be granted especific minimum privileges.
The SELECT_CATALOG_ROLE and the CONNECT roles are good enough to see most of the E.M. contents, but when trying to access the Tablespaces page from Enterprise Manager the ORA-00942 error shows up.
When tracing I discovered the query used to fill up the Tablespaces Page:

SELECT /*+first_rows */ d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0), DECODE(d.contents,'UNDO', NVL(u.bytes, 0)/1024/1024, NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024), DECODE(d.contents,'UNDO', NVL(u.bytes / a.bytes * 100, 0), NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)), a.autoext, DECODE(d.contents,'UNDO', NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, NVL(f.bytes, 0) / 1024 / 1024), d.status, a.count, d.contents, d.extent_management, d.segment_space_management/*, d.encrypted*/ FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, count(file_id) count, decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext from dba_data_files GROUP BY tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f, (SELECT tablespace_name, SUM(bytes) bytes FROM (SELECT tablespace_name,sum (bytes) bytes,status from dba_undo_extents WHERE status ='ACTIVE' group by tablespace_name,status
UNION ALL
SELECT tablespace_name,sum(bytes) bytes,status from dba_undo_extents WHERE status ='UNEXPIRED' group by tablespace_name,status ) group by tablespace_name ) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') AND d.tablespace_name like '%' /*:1*/ UNION ALL SELECT d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0), NVL(t.bytes, 0)/1024/1024, NVL(t.bytes / a.bytes * 100, 0), a.autoext, (NVL(a.bytes ,0)/1024/1024 - NVL(t.bytes, 0)/1024/1024), d.status, a.count, d.contents, d.extent_management, d.segment_space_management/*, d.encrypted*/ FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, count(file_id) count, decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext from dba_temp_files group by tablespace_name) a, (select ss.tablespace_name , sum((ss.used_blocks*ts.blocksize)) bytes from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name group by ss.tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' and d.tablespace_name like '%' /*:2*/ ORDER BY 1;

Basically all of the views mentioned here are accessible through the SELECT_CATALOG_ROLE, but SYS.TS$, which requires an explicit SELECT privilege granted from SYS on this view to the user.

ORA-00600 Stellium

2010-06-02T14:34:00.004-05:00

ORA-00600 [ARG1] [ARG2] [ARG3] [ARG4] [ARG5]
This is the kind of things a production DBA does not want to read at the alert.log file, particularly when the production database availability is compromised. At this time the only thing we would like to have is a magic wand that could make this dreaded ORA600 errors magically disappear. The truth is that so far there is no such magic wand and we all have to go through a troubleshooting procedure.

This is not a troubleshooting guide, and if you are reading this now it means either you are following my blog or you are googling after a problem you currently face and you have no clue on what is going on.

The basic troubleshooting procedure
Make sure you are at the latest patchset available for your current installed Oracle Home.
Collect and pack all the trace, and log files and raise a Service Request (SR) at Oracle Support Services (OSS).

In the mean time, try to read through the quasi-encrypted text trace and log files to find out if there is a known pattern, a readable entry point function, a particular environmental circumstance that precluded the ORA600, any thing that can help you out answer to the very basic question ... What's the root problem?

In my particular environment I had an ORA-00600 Stellium (a group of planets that from the observer's perspective they are linked together in a series of continuous conjunctions) It was a cascade of ORA-00600 errors that used to periodically flood my alert.log, trace files and incident library.

The ORA-00600 Stellium
ORA-00600 [KKPAMRFGET0]
ORA-00600 [kkdcacr: ptn_kxcp]
ORA-00600 [qerrmOFbu:invalid rowcount]
ORA-00600 [kkpamRFGet()+369]

The ORA-07445 companions
ORA-07445: exception encountered: core dump [kxccexi()+1219] [ACCESS_VIOLATION] [ADDR:0xC45] [PC:0x263EFAF] [UNABLE_TO_READ] [].

The technical environment
Oracle 11g Rel. 1 (11.1.0.6.0)
MS Windows 2003 x64 R2 Server 5.2 SP 2
PowerEdge 2950
4 CPU's
16 Gb RAM

Symptoms
The pattern found was, during a huge transactional activity on several partitioned tables containing spatial data columns suddenly all connections were broken and in order to continue with the production environment a the Oracle instance had to be restarted.

There was no specific time for this error to show up, it could be at night, at noon, on weekends, so there was actually not a specific time pattern. The key to solve this issue lied on the fact that there was too much transactional activity. After digging into the trace files I found out that there were memory issues, not related with the amount, but rather with the allocation. After the Oracle instance was running for a while (several days) on a stressful transactional activity it started to cause instability withe the memory allocation policies.

The SR Feedback
So far OSS told us the recurrence of the Ora600 errors documented at the production environment are due to a bug not yet identified so far by Oracle. The only tasks that can be done so far are to upgrade to 11.1.7.0 and wait for a patch that would be released on May 2010 that probably fixes this issue. The main argument OSS provided was the fact that the error could not be reproduced on their environment.

At this point Oracle does not have a patch or a direct procedure to avoid the kind of ORA600 errors seen at the production environment. The notes and white papers documented so far point to some issues found on the Intel x64 platform based environments. So it can happen both at Linux or Windows.

Troubleshooting
The troubleshooting path I followed in this case was:

Compile and pack the facts (trace and log files in an incident pack)
Raise a Service Request Informing Oracle about the issues
Track with the assigned Oracle Support Analyst
Downgrade the memory parameters behavior from 11g to 10g
Upgrade from 11.1.0.6.0 to 11.1.0.7.0
Upgrade Client Machines
Upgrade each client machine where an oracle client is installed
Upgrade the middle tier client that connects to the Oracle database.
Revert back the Oracle 11g memory allocation policy to 10g.

Three key factors
After struggling for months with these issues I was able to identify three key factors:
Upgrade to 11.1.0.7.0. Upgrading provides three advantageous points, first you don't have to wait for OSS to ask for an upgrade when raising a SR, second you prevent the root cause to be due after some known bugs, and third it is part of the best practices.
Revert back Memory allocation policy to 10g. 11g introduced a new instance parameter memory_target, which defines the total SGA + PGA memory allocated for the current instance, this way you don't have to worry about the way memory is individually distributed among the different memory component, meanwhile in 10g there were two different parameters, one named sga_target and the second pga_aggregate_target, this divided the memory allocation in two parts. In my personal belief I am strongly convinced that there was an issue with this new allocation policy on this particular Windows 2003 x64 platform. 11g for windows is the last port released by Oracle Corp., not too many big companies use Windows as the main production O.S. platform, and the new features have to go through a testing that sometimes cannot thoroughly reproduce a heavy weight production environment, so it is very likely that at the extreme conditions systems are sometimes exposed a very hidden in the deep internals bug is hit. This was the case in this environment, so I suggested to revert back to the 10g memory allocation policy.
Rewrite the application. Leaning on the original development team is great, you can manipulate the source code and have it tailored in case it is required. In this case there were some applications that wildly hit the production environment with a huge amount of transactions. In this particular point the credit goes to Anil R. who knew exactly how to rewrite the code that was still triggering an ORA-07455 error after the DB was upgraded.

After one month of activity no errors have shown up, and even though O.S.S. was not able to issue a conclusive reason why these errors showed up, the troubleshooting procedure was so far successful.

Conclusion
An ORA-00600 is still an error that lies within the Oracle jurisdiction, don't try to rewrite the oracle.exe code in case this kind of errors show up, but at least try to do your very best to abstract the 600 Black Box by identifying the key factors that trigger the error, and reading through the log and trace files. Answer the question Why this error showed up? and absolutely the very first troubleshooting point, raise a Service Request at Oracle Support Services.

ORA-00600: [kgeade_is_0]

2010-05-22T18:00:00.006-05:00

An ORA-00600 is something I don't like to see, particularly when I am in the just in time. And it is particularly boring when it is displayed once per each datafile to be renamed.

The Error
ORA-00600: internal error code, arguments: [kgeade_is_0], [], [], [], [], [], [], []
This error shows up under this circumstances:

The Environment

RDBMS version 10.2.0.4.0
Any Platform
Oracle DB Restore from an RMAN backup at the Standby Site
The original Database is in ASM.
The target Database is in File System.

Diagnostics
While googling i found a recommendation that suggsted to either use ASM or File System, but not both. In my case this is not an Option (and in my personal opinion not a good answer either), my primary site is a Real Application Cluster on Linux x64, so ASM is mandatory since we have no Cluster file system on the primary, and the stanby site is a File System based site.
According to Oracle Support Servivcivec they declared this as a fixable Bug.

Bug 7207932 OERI [kgeade_is_0] when renaming a file from ASM to a filesystem

The funny part of the diagnostics
The regular procedure to have an ORA600 error fixed is by diagnosing this at at the alert.log file, and by querying it at the ORA600 error lookup tool from Oracle Support Services; however, while using this last one to diagnose i found out that it didn't recognize the first argument as a valid argument.

The Correcting Procedure
There are basically two ways to have this problem solved on the standby site, and it is by applying a patch that fixes this issue, the other one is to upgrade to 10.2.0.5.0 or greater, where this bug has already been fixed.

References

ORA-00600 [KGEADE_IS_0] When Renaming A File From ASM TO FS [ID 742289.1]
Bug 7207932 - OERI [kgeade_is_0] when renaming a file from ASM to a filesystem [ID 7207932.8]

ORA-02019 : connection description for remote database not found

2010-05-13T11:46:00.003-05:00

This error has been appearing on a radom base at the alert.log file. There is no apparent reason for this to appear. And when I take a look at the detailes provided on the trace file the only thing I see is the same error meaningless pattern, the connection descriptor was not found.
In this particular case the contents at the alert.log file showed this information:

Tue Mar 09 15:45:43 2010

Errors in file ...\trace\orapro_reco_4892.trc:

ORA-02019: connection description for remote database not found

From the generated trace file name, it states that it is the RECO background process the one responsible for this trace. RECO is used in distributed transactions, and it happens that when a database goes down RECO tries to resend the pending transactions according to the Two Phase Commit procedure.
In this case there it used to be a database attached to the main database, but it was gone long ago, however some non applied transactions remained in the transaction queue, making RECO to keep on retrying forever. The database was deconfigured and all the related files and file descriptors were removed.

By taking a look at the DBA_2PC_PENDING view there there were a couple of transactions that remained in the queue and which were never applied.

SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT#
FROM DBA_2PC_PENDING;

LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIXED COMMIT#
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
9.21.102540 ORCL.ee36125b.9.21.102540 collecting no 9462810864
8.32.86528 ORCL.ee36125b.8.32.86528 collecting no 9462814781

In this case, and since there is no target database to apply the transactions to, then it is just enough to get rid of them by means of the PURGE_LOST_DB_ENTRY procedure:

execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('8.32.86528');
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('9.21.102540');
commit;

That's it, this way the ORA-02019 error is gone.

Data Guard ORA-16789 Health Check Warning

2010-05-12T10:49:00.006-05:00

After setting up the Physical Dataguard environment some errors still showed up. Even though it looked fine and the Standby database was properly receiving and applying the archivelogs, the Dataguard Broker still showed some errors

DGMGRL> show configuration verbose;

Configuration
Name: SBDBCONFIG
Enabled: YES
Protection Mode: MaxPerformance
Databases:
PRIDB - Primary database
SBDB - Physical standby database

Fast-Start Failover: DISABLED

Current status for "SBDBCONFIG":
Warning: ORA-16608: one or more databases have warnings

Dataguard broker uses an evaluation criteria that is some sort of everything or nothing at all. Either it works fine, or it doesn't but there is no intermediate point. The issue here is that sometimes you have to be patience to find the errors. This is an 11g release, and though the solution found can work on 10g too you should be aware that after the DIAGNOSTIC_DEST parameter in 11g the path to find the log files has changed : ORACLE_BASE/diag/rdbms/ORACLE_SID/ORACLE_SID/trace/drcORACLE_SID.log, the errors displayed there were as follows:

Error Displayed

RSM Error: LogXptMode value 'ASYNC' of requires this database to have status redo logs, but they are not configured.
RSM0: HEALTH CHECK WARNING: ORA-16789: standby redo logs not configured
Operation CTL_GET_STATUS continuing with warning, status = ORA-16789

Solution

In this case it is pretty obvious why it is failing ... there are two solutions, one is to add the required Standby logfiles after the configured LogXptMode ASYNC mode, or change the LogXptMode to ARCH. I choosed the first one,

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
2 'C:\ORACLE\APP\ORADATA\SBDB\SBREDO04.RDO' SIZE 16M;
Database altered.

The command is repeated for group 5 and 6. The sizes and paths shown in this example are just for instructional purposes, on the actual production environment the size of the standby redo logs should be the same used for the production logs.

A Second Error Shows Up

The second error shown refers to the fact that since adding the standby redo log files required the recovery process to be stopped, the Data guard manager complained about it.

DGMGRL> show configuration verbose;

Configuration
Name: SBDBCONFIG
Enabled: YES
Protection Mode: MaxPerformance
Databases:
PRIDB - Primary database
SBDB - Physical standby database

Fast-Start Failover: DISABLED

Current status for "SBDBCONFIG":
Warning: ORA-16607: one or more databases have failed

This time the Dataguard logfile reports the following:
DMON: HEALTH CHECK ERROR: ORA-16766: Redo Apply is stopped
Operation CTL_GET_STATUS canceled during phase 1, error = ORA-16766

In this case it was pretty obvious too, the redo apply process was stopped to be able to add the Standby Database while the Active Data Guard was open.

Just restart the Recovery process and that's it.

Solution

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.

DGMGRL> show configuration verbose;

Configuration
Name: SBDBCONFIG
Enabled: YES
Protection Mode: MaxPerformance
Databases:
PRIDB - Primary database
SBDB - Physical standby database

Fast-Start Failover: DISABLED

Current status for "SBDBCONFIG":
SUCCESS

It works as it is supposed to.

References

Oracle® Data Guard Broker
11g Release 2 (11.2)
Part Number E10702-01

Errror upgrading RMAN Catalog to 11.1.0.7.0

2010-05-04T11:22:00.008-05:00

WARNING.

The following post describes a particular issue I wanted to document and share, the solution found for this issue worked for my particular environment, which does not mean it will work with yours. Dealing with internal Oracle structures without Oracle Support Services approval and supervision will render your database unsupported and it may (and most probably will) compromise its availability.

When performing the upgrade of the recovery catalog database an error showed up. This error has to do with a known issue when upgrading the recovery catalog to 11.1.0.7.0 after applying the patchset against the database.
The upgrade procedure corrupts the DBMS_RCVCAT procedure, so it is enough to get rid of it and have the rman catalog owner to upgrade its catalog from an rman prompt, as follows:

1. Connected to a SQL*Plus prompt as the recovery catalog owner issue the following commands:
sqlplus rcatOwner/rcatPassword
SQL> drop package DBMS_RCVCAT;

2. From a Recovery Manager prompt issue the following command:
rman catalog rcatOwner/rcatPassword
RMAN> UPGRADE CATALOG;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG;

recovery catalog upgraded to version 11.01.00.07
DBMS_RCVMAN package upgraded to version 11.01.00.07
DBMS_RCVCAT package upgraded to version 11.01.00.07

At this moment the procedure has successfully upgraded the repository and the DBMS_RCVCAT stored unit has been properly rebuilt.

BUT ... Yes, the big BUT. In my case a particular issue arose. Instead of reading the above successful message this is what showed up instead:

RMAN> connect catalog rman/rman
connected to recovery catalog database
recovery catalog is partially upgraded to 11.01.00.07; UPGRADE CATALOG again

RMAN> upgrade catalog;
recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;
error creating upgcat_57
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-02293: cannot validate (RMAN.CDF_C_STATUS) - check constraint violated

I hate whe RMAN complains. And it likes to pretty often.

RMAN.CDF_C_STATUS stands for a constraint that is added to the CDF table. This table stores information about the registered datafile copies. This constraint is defined in the ?/rdbms/admin/recover.bsq file and it literally reads as follows:
<<< define upgcat_57
>>>
alter table cdf add constraint cdf_c_status check (status in ('A','U','D','X'))

Notice the clause define upgcat_57 and the error message above, error creating upgcat_57.
Originally the CDF table is created with an offline constraint that can be found in the same file:

CONSTRAINT cdf_c_status CHECK (status in ('A','U','D','X','F')).

But when the upgcat_57 step is executed it magically leaves the 'F' value out.
In my particular case the CDF table, had four registered datafile copies, these copies were already obsoleted, but they were there any way with a 'F' flag. Since the add constraint applied against the repository table it crashed and made the upgrade procedure abort.
The workaround found for this particular case was to update the CDF table and change the 'F' flag in the status column with a 'U' value.
This renders the backups as Unavailable. The flag values are not documented in the file, but as far as I know the 'A' value means Available, meanwhile 'U' stands for unavailable.
After manually updating the column value the procedure to upgrade the repository was run once again from the RMAN prompt, this time it was successful.
In my particular case these backups were taken a very long time ago, and those are no longer available, so there it was no problem at all in my case taking this direction.
However a big issue that comes to my attention is that Oracle either has a bug in this upgrade procedure or it forgot to update the constraint declaration with the proper value set.

Update ...

While I was finding out if this issue could be reproduced on 11g Rel. 2 i found at the recover.bsq file the following lines:

define upgcat_166
<<<>>>

define upgcat_167
<<<>
CHECK (status in ('A','U','D','X','F')) >>>

So it means that someone in Oracle realized about this mistake, then they dropped the current constraint and then add the constraint with all the flags there included.
I can see that if someone has not hit this bug on 11g then most probably they will not realize about it by the time they migrate to 11g Rel. 2.

ORA-42012: error occurred while completing the redefinition

2010-05-03T14:31:00.004-05:00

An error stack showed up when trying to use the DBMS_REDEFINITION package. After issuing the command

dbms_redefinition.finish_redef_table('MADRID', 'a_table', 'an_int_table', 'a_partition');

The following error stack showed up:

begin
*
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-04020: deadlock detected while trying to lock object MADRID.TABLE1_INT
ORA-06512: at "SYS.DBMS_REDEFINITION", line 78
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1680
ORA-06512: at line 2

When I was trying to remove the interim table to restart the redefinition procedure it conflicted with a couple of interim objects created on the fly.

SQL> drop table table1_int;
drop table table1_int
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "MADRID"."TABLE1_INT"

SQL> DROP MATERIALIZED VIEW "MADRID"."TABLE1_INT";
Materialized view dropped.

SQL> drop table table1_int;
Table dropped.

This is due to a bug in the 11.2.0 release on every OS platform. This will be fixed in future releases, in the mean time the workaround for this problem is to modify an Oracle Instance parameter, just set the deferred_segment_creation to FALSE and retry the operation.

ORA-39714 After upgrade

2010-04-07T11:40:00.003-05:00

After the execution of the @?/rdbms/admin/catupgrd.sql script on an 11.1.0.6.0 database on Windows x32 when trying to open the database it showed up this ORA-39714 error. As per the error catalog manual, it stated :

ORA-39714: upgrade script utlmmig.sql failed
Cause: A normal database open was attempted, but the upgrade script utlmmig.sql failed to complete.
Action: Use the UPGRADE option when opening the database and then run utlmmig.sql.

As per the manual, just ran the utlmmig.sql script. That's it.

I need to further find out why this showed up. I was trying to move a 64bit database to a 32bit platform, so I had to deal with the dabase objects and the data dictionary to have it working after downgrading to the 32bit platform. I had to recompile the database objects with the utlirp and utlrp procedures.

Error in Agent trying to install Grid Control

2010-02-19T16:01:00.003-06:00

An error showed when I tried to install Oracle Grid Control 1o.2.0 on a Windows 2003 SE x32. The error was pretty much the same as the error shown with the regular Enterprise Manager Console on this platform, it has to do with the time zone issue I have already reported some time ago in this blog.

Sometimes because of the timeout involved during the installation process, it may happen that after a second attempt the Agent can be installed, afterwards then take a look at the log files, there may be some major issue that requires special attention.

I took a look at the \log directory and looked for the emdctl.trc file, there I found the following lines:

2010-02-19 15:30:52 Thread-1744 ERROR main: nmectla_agentctl: Error connecting to https://tango.oracle.com:3872/emd/main/. Returning status code 1
2010-02-19 15:30:53 Thread-2872 ERROR main: nmectl.c: nmectl_validateTZRegion, agentTZoffset =-360,and testTZoffset for GMT:0 do not match
2010-02-19 15:30:54 Thread-2872 ERROR main: nmectl.c: nmectl_validateTZRegion, agentTZoffset =-360,and testTZoffset for GMT:0 do not match
2010-02-19 15:49:34 Thread-2552 ERROR main: nmectla_agentctl: Error connecting to https://tango.oracle.com:3872/emd/main/. Returning status code 1
2010-02-19 15:49:44 Thread-2660 WARN http: snmehl_connect: connect failed to (tango.oracle.com:3872): No connection could be made because the target machine actively refused it. (error = 10061)

By the way, tango.oracle.com is a fictitious server and does not have anything to do with Oracle corp.

At the log file a particular error came to may attention, the TZ Error. This one has to do with the time zone changes that took place some time back. I will apply the latest patchset on top of it, so I am not too much concerned about fixing it at the time, but since I want to have a 'clean' install I worked around this by commenting the line found at the \config\emd.properties file (a routine backup is highly advisable) and commented the last line. I changed the time zone in the windows machine and by the time the assistant is re-run it takes the right Time Zone.

emd.properties

###HRM: agentTZRegion=GMT
agentTZRegion=America/Chicago

That's it, my installation took place and I can proceed with the next tasks.

My recent experience with VMWare

2010-02-17T15:35:00.008-06:00

VMware ate my four CPU Cores

Today I noticed the high amount of CPU consumed by the vmware-authd.exe process. It raised the CPU consumption to 100% and leaves no CPU resources to any other process in the system.

The environment is a Windows 7 professional 64 bits with 4GB Ram and 4 cores (all of them at the top 100%).

The most recent post I found so far by googling is from Oct 19th 2009, and the author of the post states that a bug was filed (483679).

vmware-authd.exe is the executable for the VMware Authorization and Authentication Service for starting and accessing virtual machines. This process is required if you are not logged in with administrative privileges (which by the way is my case).

I shuted down this service. In the VMWare 2.0 edition the VMWare Auth. service is dependent from the VMWare Host Agent which provides remote command and administrative control over this VMWare Server host. After shutting down both processes the CPU monitor was back to normal.

This process is required in the VMWare Server 2.0 version, otherwise it will no be possible to launch the console. By shutting down and restarting the services the problem seems to be fixed. Most probably it has to do with a bug.

On the other hand, I don't have anything against the new Tomcat based console, but I miss the regular windows based console.

Host Unreachable

When trying to connect to my virtual machines through the network, they replied with a 'host unreachable' error, among other unpleasant related network errors.

The current environment I have:

DHCP on host real network adapter
Loopback adapter on real host
A fixed IP address through a bridged virtual network adapter

In this scenario the loopback adapter was required since I am installing an Oracle 11g Rel. 1 / 2 on this platform. The virtual machines were not visible or either were partially and intermittently visible.

I put the virtual network adapter to have a dynamic address provided by the DHCP server and it began to work. However I still need a fixed IP Address at the virtual machine since I am installing Oracle RDBMS Oracle 11g Rel 1 on top of it. So I installed a Loopback adapter inside the Virtual Machine.

I went through several Google references and most of them talked about de-installing and re-installing the VM protocol from the real network adapter, but it only lead me in this particular case to a waste of time and a server reboot. So far, everything is properly working.

When I pinged the host server I noticed a lot of time waiting for a reply, and when I tried to access a shared path from it my local machine replied with a timeout. Two issues were involved here, first the user at the host server is a domain user, not a local user, and the second one, it has to solve the hostname first, which took too long, so I added the address from the host to the hosts file at the virtual machine.

Network Communication to the VM was Deeeeeeadly Slooooooooooow

It is not enough eh!, well I tried to perfom file transfer from my host to the virtual machine, it happened that the performance was around 20Kb/s. so figure out what it was to transfer the Oracle XE installer executable (200,000 Kb), around 2:45 hrs to transfer the whole file, I don't want to image how long it would last to transfer the more than 1G file to install Ora11gR1 to the VM. Google you are the Geek's Nirvana, after googling a while I found a reference that stated some network parameters had to be configured, it was the advanced properties for the network adapter at the physical host.

At the advanced properties of the network adapter ther is one named Large Send Offload v2 (IPv4), this must turned off (disabled), it boosted the network performance to the Virtual Machine. A definition of what this parameter does can be found at this Microsoft Tech Note.

"DisableTaskOffload
Disables offloading of processor tasks to the network adapter. Offloading is designed to optimize performance of Windows 2000.

Network Driver Interface Specification (NDIS) 5.0 lets TCP take full advantage of intelligence in network adapters by letting the adapter do some of the tasks that the processor normally performs. Offloading these tasks to the network adapter leaves the processor free for tasks that only it can perform."

Addendum
A document that may help with some additional performance hints can be found here "VMWare ESX Server Performance Tuning Best Practices".

I go back to the VMWare, this VMWare session was very instructional for me.

Performance Problems with my Workstation

2010-01-29T11:02:00.003-06:00

My computer was deadly slow, several processes took over the computer resources, such as CPU, which almost always was permanently at 100%, IO resources, several processes were reading several Gb's of data, (what for?), it was a nightmare. The bigger the computers grow the slower they can become. I have a Laptop I use as a server with several VM Machines running when required which serve as test environments. I use Windows XP, yeap, I personally don't like Vista and if possible I avoided new releases, except for Windows 2003 and Windows 2008.

One of the resource consuming processes was the jqs.exe, a process that runs in the background with low priority but which can lead your computer to IO starvation. JQS is an acronym after Java Quick Starter, it is used to speed up the launch of java applets, but if you are not launching Java applets too much frequently it is not worth keeping it running. Now, if you want to disable the Java Quick Starter process you can do that in the Windows Control Panel. You find a Java entry there which will open the Java Control Panel. A click on Advanced and the selection of Miscellaneous will display the activated Java Quick Starter entry. Uncheck the box to disable the process. You have gotten rid of the jqs.exe process. Hasta la vista baby! ...

CCSCHST.EXE is another heavy weight process I want to get rid of, this process creates another IO and CPU bottleneck. I have Norton Antivirus, this was not a good choice for an anti-vrus, it deadly slowed down the computer performance. It is being said that it has to do with a conflict between the Microsoft update process and Norton.

I disabled the automatic windows update feature from the control panel. A red alert is displayed, this let me remember that I have to manually connect to the Microsoft site to perform the updates. Here it is an article that describes what is going on with this issue Norton Vs. Microsoft . I disabled the NAV security features so that Norton does not try to start the live update on its own. I choosed to manually launch the updates.
In the case of Norton AntiVirus I had to leave several features to the minimum, or even disable them from the NAV control panel.

Another nice application that consumes a lot of resources, particularly during system startup is the Weather Channel desktop, it is a very useful and nice application, particularly considering these unpredictable winter weather, but neither my machine nor my work are willing to go through the additional overhead required to startup this application at windows start time. So I disabled it from starting at windows start time. I'll go through the Weather channel web page to keep updated about the weather.

I also have the Virtual Machine server from VMWare, it requires several resources during startup, but this is one of the application I use the most, so there is not too many options but to let it be.

And finally firefox, I love FireFox, but I do not know what happened to the latest releases, particularly 3.X. There have been several upgrades, it added new features, and it is pretty nice, but it requires more resources. Either way after getting rid of all of the ballast my machine had to carry on during startup and production time, there are enough free system resources for fireFox, so this is definitely out of my personal deinstallation list.

The Oracle Open World 2009 is just a few weeks ahead

2009-09-21T15:24:00.000-05:00

The last OOW2008 was for me the first time I visited this event. This is the time to meet partners, colleagues and friends. There are tons of information, a lot of activity during the day evening and night. It all starts early, between 8:00 and 9:00 a.m., depending on the scheduled activities and the interest in key conferences or other early activities. If you have already confirmed your assistance make sure you have accessed the scheduler builder tool. By now most of the hands-on workshops have no more seats available, this is the first activity that is in high demand. There are lots of conferences and most of them are overlapped, so it may be difficult to be at two or more places at the same time unless you develop the ubiquity ability.
The schedule builder is a very important tool during the event. This allows you to better plan ahead how your time will be distributed, when you are going to take breaks, when you are meeting friends and partners, visit the exhibition hall, assist to the demo grounds or perform other relevant activities.
The last time I was there I didn't carry my iPod with me and my cell phone didn't have wireless capabilities. Never do that. It is very useful to have your schedule on your handheld device and it is important to have internet, it becomes a very useful mean of communication. There are computers where you can quickly access your email and internet, but since they are public, it becomes difficult to remain more than ten minutes when several folks are waiting in line. I suggest you to take your portable wireless device with you.
Last year the electronic presentations were not available but for those who buy them. There is a CDRom you can buy. So if you are interested you can get them from Oracle.
This year I will be signing my book, the scheduled time slot will be on Tuesday 13th at 16:30 hrs at the library. I hope to see you there.
This is just the beginning, there will be a lot of things more to comment about this event.

The Oracle ACE

2009-08-02T21:45:00.005-05:00

What is the Oracle ACE Award?

"Oracle ACEs and Oracle ACE Directors are known for their strong credentials as Oracle community enthusiasts and advocates, with candidates nominated by anyone in the Oracle Technology and Applications communities. The baseline requirements are the same for both designations; however, Oracle ACE Directors work more closely and formally with Oracle in terms of their community activity."

Ref. Oracle ACE Program

Andrew Clarke mentioned in his blog how things have changed since he was first nominated to receive this Award back in 2003: "The bar has certainly been raised since I was nominated. If I look at some of the people who are now ACEs I think I must have walked into the wrong club." He also mentions it is something that happens sometime along the way, and certainly it is, it happens when you least expect it. You cannot nominate yourself, "someone else spotted your efforts" and it is just a way to give you a Big Thank You for providing meaningful and valuable contributions to the Oracle Community.

I have been in the Oracle community for 20 years so far, but it was not because of those 20 years I received this Award, but specifically because of the last five years, I have shared knowledge with the community in several ways, by participating in the Oracle Forums, sharing knowledge by writing an Oracle related blog, and by writing an Oracle book.

In this Universe, whatever you do, right or wrong follows the third Newton's Law "For every action there is an equal and opposite reaction." and this ACE Award is the practical manifestation of this law.

I just want to say a Big Thank You to the Oracle Community.

What does it feel to be twenty?

2009-07-31T09:57:00.002-05:00

One day is a very long time if you have to wait for something to happen. Twenty years are as fast as a wink of an eye if you keep on making things happen.

The 80's, the Glowing Decade

Back in the 80's talking about mobile phones was not popular at all. The cassio fx100 was my programmable scientific calculator used to solve integrals by means of numerical methods. In the music, Madonna, Billy Joel, Elton John, Cindy Lauper, Aerosmith, Brian Adams, Dire Straits, Duran Duran, Eurythmics, Peter Gabriel, Huey Lewis & the news, Robert Palmer, Bruce Springsteen, Survivor, Tears for Fears, Van Halen, ZZ Top, only to name a few, were the singers we used to hear with the walkMan (some sort of iPod of the 80's) wich was our preferred music gadget to play audio tapes.
At that time the fluorescent pink, yellow, green and blue were the fashion colors found in every guard robe. People dressed in those colors easily glowed in the night with the Discotheque lights. These noisy colors still survive today only as text markers.
At that time Java was an Island found in the Pacific Ocean and Cobol was still the programming language for the business applications. Meanwhile Pascal was an emerging structured programming language which mostly remained in the research labs. Most of the people related to informatics at the time felt in love with Pascal but ended married with C Language until the word Java changed its widely known meaning to the programming language for the internet.

20 Years of professional Career devoted to Oracle

Oracle has been evolving and it is as challenging, aggressive and innovative as the first time I met it. It was Oracle version 5.0 the first Oracle version I used to issue my first SQL statements. 16 Mb RAM, two hard disks of 512 Mb and 320Mb inside an HP9000 runnig HPUX 9.0 were the leading edge technology standards capable of carrying the data payload required to move the Metropolitan University data. Today this amount of computer power is hilarious, but if compared with the average PC of the time (640K RAM + Extended memory + 40Mb HD + 256 Colors monitor) this was a powerful Server.
My professional career has evolved side by side with Oracle. I have run at the same pace Oracle runs, stuck to it as its shadow. During this time I have seen a lot of people come and go to the Oracle community, and very few of them still join me today. Even though very few things have changed. The two main Kernel directives were not very different from today's kernel directives,
I. High Availability
II. High Performance
Oracle is conceived as a highly fault tolerate rdbms, whose memory and physical structures provide a good balance between recoverability and performance. I have met all the major and minor releases since then up to the latest available 11g release today and I can say that even though Oracle's main directives are the same, each day it becomes more robust and more complex.
It's been a long journey that has run at a very fast pace. I have met a lot of people, I have met a lot of places. And at the end here I am still working with Oracle. I enjoy this professional activity as much as the first time I met Oracle, it's been some sort of falling in love at the first sight that has ended in a marriage so fresh and new as the first time.
This is the train of a never ending trip where a lot of people climb up and others descend from it, but the most important part of it is the trip itself, not the destination.

ORA-00600: [LibraryCacheNotEmptyOnClose]

2009-07-23T18:13:00.003-05:00

Recently a database in 9iR2 (9.2.0.8.0) was migrated to 10gR2 (10.2.0.4.0), this database was already having a hard time with memory issues and frequent ORA-04030 errors. At the time the workaround for this issue was to increase the value of the PGA_AGGREGATE_TARGET instance parameter. Since 10g consumes more resources than 9i this error re-appeared and the local operators found easy to rebounce the oracle instance when this problem was more frequently shown. However, after the migration to 10gR2 this procedures throwed the above mentioned ORA-00600 error.

Googling around I found that this errors is due to a bug (Bug 4483084 OERI[LibraryCacheNotEmptyOnClose] on shutdown), and it is harmless, so this can be ignored. There are people who have never seen this error but they started facing it after migration to 64 bit platforms.

According to Shervin Sheidaei, he stated that purging the memory region prior to the shutdown process using a shutdown event trigger the problem can be worked around.

Code taken from his blog:
CREATE or replace TRIGGER flush_shared_pool
BEFORE SHUTDOWN ON DATABASE
BEGIN
execute immediate 'ALTER SYSTEM FLUSH SHARED_POOL';
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (num => -20000, msg => 'Error flushing pool');
END;

Packt Publishing Press Release

2009-07-23T14:33:00.003-05:00

Oracle 10g/11g Data and Database Management Utilities is a new book from Packt that helps Oracle professionals improve performance and manageability using the advanced features of Oracle Utilities. Written by Hector R. Madrid, a certified DBA and Java curriculum instructor for the Oracle University, this book helps users to master the utilities to optimize the efficiency, management, and performance of their daily database tasks.

Database interaction is a part of the daily routine for all database professionals, and by using Oracle Utility tools such as Oracle Data Pump and Oracle SQL*Loader, users can benefit from improved maintenance windows related to data management tasks, optimized backups, faster data transfers, and more reliable security, thereby allowing them to do more with the same amount of time and resources.

Using the advanced features of direct export or import utilities, readers will learn to improve performance and manageability of different databases. Readers will make use of the Oracle Scheduler to specify maintenance windows, assign priorities, configure job classes and many more features. With the help of the Oracle Universal Installer tool, the installation tasks becomes more efficient, and allows users to execute effective default and comprehensive database creations.

Readers will also learn about the Oracle Wallet Manager and how it is used to increase the security in an Oracle environment, protect the backups, and manage its certificates. Using OPatch, users will be able to manage software updates related to Critical Patch Updates (CPU) or individual patches and perform installations in batch environments. They will also learn to use DBCA to create and configure a database and manage it in an ASM environment.

Oracle professionals interested in working with several powerful tools to help improve performance and manageability of utilities among different databases will find this book useful. This book is out now and is available from Packt. Fore more information, please visit http://www.packtpub.com/oracle-10g-11g-data-and-database-management-utilities/book

Does your database look complicated? Are you finding it difficult to interact with it? Database interaction is a part of the daily routine for all database professionals. Using Oracle Utilities the user can benefit from improved maintenance windows, optimized backups, faster data transfers, and more reliable security and in general can do more with the same time and resources.

"Oracle 10g/11g Data and Database Management Utilities" is written using a practical approach that guides you through different practical scenarios. It provides a brief introduction to the topics; this way you can quickly get to know the main features, start being productive with the tool, and grow with it at a fast pace. If you wish to read through the book extract, please feel free to click here: Book Extract

My recently published Book

2009-07-22T13:12:00.007-05:00

"Oracle 10g/11g Data and Database Management Utilities" is the tittle of my recently released book that was published by Packt Publishing. This book represents for me my debut as an Oracle Author. This book was written after all the cases I have faced for already 20 years of professional carreer as an Oracle Professional.

There are times when a process doesn't seem to end, and when it is about to reach the final stages something happens and it breaks up. If we had a tool to monitor and at lest stop the process before this fatal ending it would be great. The tool actually exists, but the problem with several DBA's or DBO's is that they don't know this tool exist and even if some of them know this exists, very few of them know how to master it so they can use it in a practical environment.

This is not the only case I expose in my book, there are other scenarios that relate to tools such as DataPump. Today there are a lot, of people out there that still talk about the traditional exp/imp. Data Pump is a very old tool, considering this was first released in 2003 along with the 10g Rel. 1 RDBMS, and since the technology gets obsolete in five years (avg.) this can be considered that Data Pump has already had three evolution stages (10gR1, 10gR2, 11gR1 and very soon 11gR2). What about exp/imp, it is more than an obsoleted tool that has already given what it has to give and is maintained only for upward compatibility.

Another tool has to do with sql*loader, you may say it is not new and you are right, but how about using it to load CLOB, BLOB files? Can this be used to create External Tables and have access to log files so they can be analyzed at the database level by means of SQL commands? The answer is yes, and SQL*Loader can be used in a very creative way. You can get a network performance meter by means of a sqlloader exeternal table, you can have another view of the alert log and you can query it directly from the database.

There are several topics I address in the book, but overall I do a practical approach, I introduce practical examples that can help the DBA in the day by day tasks so the DBA can perform its dutys in a more efficient way.

Yet another CRS-0223: Resource 'ResourceName' has placement error

2009-07-22T12:55:00.005-05:00

Recently I faced this error. The scenario took place after a full RAC reboot process. The difference with error I had already documented here was that it was useless to restart each RAC service. Either way it was sending the placement error for all RAC related services. In this case I took a look at the Metalink Note: 726925.1 "srvctl start instance fails with PRKP-1001; srvctl trace shows error connecting to CRSD".

First I checked the alert.log file related to the cluster operation. It reported nothing unusual, everything seemed to be normal.
Next I took a look at the output of the command:

crsctl check crs

This command reported everything was working just as normal. Definitely it had to do with the syncronization at startup time. It is pretty weired since a normal node reboot should not lead to such inconsistency. I must point out that the environment used was 10gR2 (10.2.0.1.0) on RHEL4 (Red Hat Enterprise Linux AS release 4 (Nahant Update 3) 2.6.16 xenU (32-bit)), this was a scenario faced while I was teaching the RAC 10g course for Oracle. Since this environment is not patched at the start of the course, I would not be surprised to find out that this is due to an already filed bug.

The procedure was to kill (as root) all crsd.bin process on all participating nodes, then a simple crs_stop -all / crs_start -all was just enough to put everything back to normal.

Setting up ASM in a Windows environment

2009-05-17T02:58:00.002-05:00

Recently at the OTN Oracle Install forum I found a question about setting up ASM in a windows environment. The procedure to setup ASM in windows is quite different from the procedure in a linux environment. The reason is because of the partition concepts and the way windows adminsiters partitions.

ASM Setup

There is a procedure to prepare raw disks to be used in a ASM environment in a Window platform. I should point out that you didn't specified the actual windows version. The following procedure applies to a Windows 2003 O.S.

Disk Layout

At least a raw partition is available.
the DISKPART utility should be used (Win2K3) or Disk Manager (Win2K & Win2k3).

Windows does not automatically mount raw disks and make them visible. You must enable automounting. Using Diskpart at the Diskpart, at the Diskpart prompt type:
DISKPART> automount enable

At the Command Prompt type diskmgmt.msc this will start the Disk Management windows utility.
In case the disk is in dynamic mode, change it to Basic mode.
Create a new partition on the empty disk and select an extended partition. Select the partition size to fill the disk. Once the wizard is ready it will create the extended partition.

Logical Partitions

Once the extended partition is created, next step is to create the logical partitions. Being in the disk management utility, you should be able to see the extended partition created. Right click on the extended partition and create as many logical partitions as required. Make sure you don't assign a drive letter to the partition. One more thing, considering this partition MUST remain as a raw partition, do not format the logical partitions; the assistant displays the option, ensure no format is performed on the raw disk.
At this point you should be able to see the logical partition created.
Repeat these steps for as many logical partitions as required.

Setup ASM

Once you are ready with the logical partitions, the next phase is to setup the ASM environment. Once in the Configure ASM assistant, define the Disk Group Name (DATA for example) click on the Stamp Disks, since there are currently no disk labeled, the asmtool performs the disk labeling. At the asmtool you should be able to see the partitions, there you can see the disk status, if the flag is 'Candidate devivce' then it ca be selected and labeled. The disk name format is something like \Device\Harddisk1\Partition_N. Once they are labeled they will appear as candidate disks, you should be able to see them as a candidate disk back in the ASM assistant, there you should be able to see the candidate raw device in the format similar to this:
\\.\ORCLDISKDATA_N

In the final step you should be able to see the candidate disks, just compose the ASM disk groups as required and you are done with the ASM setup procedure.

What Happens during a hot backup?

2008-11-11T19:20:00.003-06:00

There are a couple of myths around the hot backup or online backup process:

Myth #1: The hot backup generates "a lot" of redo information
Myth #2: The archivelog mode "dramatically slows down" the database
Myth #3: When a hot backup is in progress the target datafile is frozen.

There are two ways to generate a hot backup, the first one is by a user managed backup and the second one with recovery manager. The database is required to be in archivelog mode for it to be able to perform an online backup. Both ways to perform an online backup work in a similar way, as I will further explain later, rman is more efficient than the user managed backup.

User managed backup

alter tablespace ts_name begin backup;

When the command is issued, at this point in time a checkpoint is performed against the target tablespace; then the datafile header is frozen, so no more updates are allowed on it (the datafile header), this is for the database to know which was the last time the tablespace had a consistent image of the data.

The datafiles with the backup in progress will still allow read/write operations just as a regular datafile, I/O activity is not frozen.

Each time a row is modified, not only the row, but the complete block is recorded to the redo log file, this will only happen the first time the block is modified, subsequent transactions on the block will only record the transaction just as normal.

During the user managed backup process the "fractured block" event may be present. Let's remember that the oracle block is the minimum IO unit, and an oracle block is made out of several OS blocks; let's assume a block size of 8K and an OS block of 512b, this will give 16 OS blocks. If during the backup process of a block there is a write operation on the block then the backup will contain a before image and an after image of the oracle block, the complete block in the backup media will be corrupt. This is normal, consistency is not guaranteed on the backup, that is why the header must be frozen to mark the point where the recovery process will have to start, and that is why oracle record a complete block image on the redo log file.

At the time the alter tablespace ts_name end backup; command is issued then the backup process is finished and the datafile header resumes its regular IO activity.

Recovery manager backup
The same process happens when a rman backup takes place, the only difference is that rman better handles the fractured block issue, it doesn't write block fragments or partial blocks to the backup, it writes the complete consistent block image to the backup media. So recovery manager doesn't need to record the complete block to the redo log file.

Some further comments on the rman case, rman doesn't freeze the datafile header, it continues to checkpoint just as regular, but it does perform a checkpoint to the tablespace.

From my perspective, the user managed backup (UMB) is a backup method that is less frequently seen on production environments, since Oracle 9i Rel 2 most DBA's considered rman as part of the regular backup/recover strategy, it performs better that the UMB, it is able to perform a block level backup, meanwhile in the UMB the whole datafile must be backup even if a lot of clean blocks are present.

Some advices for the people who still use UMB, don't let the BEGIN BACKUP run for long periods of time, it is very likely that the more time it takes to perform the backup, the more blocks are likely to change, which may generate more blocks contents to be written to the redo log files.

libawt.so: libXp.so.6: cannot open shared object file

2008-11-10T17:43:00.002-06:00

During the installation of Oracle 10g Rel 2 on Linux this error may appear because the required library is not installed by default. Before installing Oracle, you must make sure all required packages according to the installation guide are present.

There are two libraries which are not installed by default in OEL5, libXp-1.0.0 and compat-db, these files are found on Disk3 in OEL 5.2.

The command used to install the required RPM's on OEL 5.2 is:

rpm -ivh compat-db-4.2.52-5.1.i386.rpm compat-libstdc++-296-2.96-138.i386.rpm compat-libstdc++-33-3.2.3-61.i386.rpm gcc-4.1.2-42.el5.i386.rpm gcc-c++-4.1.2-42.el5.i386.rpm glibc-devel-2.5-24.i386.rpm glibc-headers-2.5-24.i386.rpm libgomp-4.1.2-42.el5.i386.rpm libstdc++-devel-4.1.2-42.el5.i386.rpm libXpm-devel-3.5.5-3.i386.rpm
done libXp-1.0.0-8.1.el5.i386.rpm

This assumes all RPM's are present on a stage area.

Oracle Forums Down After Upgrade

2008-08-28T10:50:00.002-05:00

It was about three months ago when the OTN launched a better release. It had to be rolled back with all the implied consequences. People who had participated for the new release saw their work simply disappear.

For the second time a new attempt to release the forum was made, it fairly worked for one week. Afterwards the forum had to be shutdown once again. What is going on with the oracle team who had assembled the forum? Is this really an escalable application?

The attempted version was pretty nice. The editor functionality was improved, there were reward points for the people who really aported valuable knowledge to the forum.

However it happenend that this solution simply doesn't work. It is very nice, and it would be even nicer if this solution worked, but ... the big BUT ...

"Oracle forums is experiencing technical difficulty. We are aware of the issue and are working as quick as possible to correct the issue. Please try again in a few moments.
We apologize for any inconvenience this may have caused.
To speak with an Oracle sales representative: 1.800.ORACLE1.
To contact Oracle Corporate Headquarters from anywhere in the world: 1.650.506.7000.
To get technical support in the United States: 1.800.633.0738."

I like the philosophy of this forum, this way people like Sybrand "senior RTFM answers" Bakker can be easily spotted and automatically self-banned. This goes for all the similar inflated egos posters who are just playing a bullying games with junior level posters.

I wish Oracle Corporation really invested time, effort and money in its flag ship forum, forums.oracle.com

ORA-01460: Unimplemeted conversion requested

2008-08-13T20:08:00.003-05:00

When performing a SQL*Loader of a BLOB file an error showed up:

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST 5 INTEGER
NAME NEXT 50 , CHARACTER
IMAGE DERIVED * EOF CHARACTER
Dynamic LOBFILE. Filename in field NAME

Record 1: Rejected - Error on table IMAGES.
ORA-01460: unimplemented or unreasonable conversion requested

Record 2: Rejected - Error on table IMAGES.
ORA-01460: unimplemented or unreasonable conversion requested

Record 3: Rejected - Error on table IMAGES.
ORA-01460: unimplemented or unreasonable conversion requested

The original controlfile used had this format:

LOAD DATA
INFILE *
INTO TABLE IMAGES
REPLACE
FIELDS TERMINATED BY ','
(
id INTEGER(5),
name CHAR(50),
image LOBFILE (name) TERMINATED BY EOF
)
BEGINDATA
001,OracleHQ1.jpg
002,oracleHQ2.jpg
003,OracleHQ3.jpg

The problem was because a data format was forced from the controlfile, just by changing the data type definition and letting SQL*Loader to have it define solved the problem, so the control file after the modification was:

LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id,
file_name,
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,OracleHQ1.jpg
002,oracleHQ2.jpg
003,OracleHQ3.jpg

ORA-06544 when performing network expdp between 11g and 10g

2008-07-29T23:46:00.003-05:00

When I was trying to perform a network export datapump between 11g and 10g I found an ORA-06544 error.

[oracle@alpha admin]$ impdp SCOTT/TIGER tables=emp_10g,dept_10g network_link=db10g

Import: Release 11.1.0.6.0 - Production on Tuesday, 29 July, 2008 22:37:01

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39113: Unable to determine database version
ORA-04052: error occurred when looking up remote object SYS.DBMS_UTILITY@DB10G
ORA-00604: error occurred at recursive SQL level 3
ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [55916]
ORA-02063: preceding 2 lines from DB10G

ORA-39097: Data Pump job encountered unexpected error -4052

This error is an interoperability error due to the bug 4511371, which is fixed by applying the 10.1.0.5.0 patchset for 10gR1 and 10.2.0.2.0 patchset for 10gR2.

Ref. Metalink note 4511371.8

ORA-39001 When executing Export DataPump on RAC

2008-07-24T15:43:00.006-05:00

While I was performing a data pump test export from one of the demo accounts a very weired error showed up. I have looked for it at the web, metalink, forums, and it seems to be the first case.

Scenario
A simple expdp proces trying to export whatever data shows up this:

-bash-3.00$ expdp sh/sh dumpfile=shSchema directory='default_dp_dest' schemas=sh
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/home/oracle/default_dp_dest/shSchema.dmp"
ORA-27041: unable to open file
Linux Error: 22: Invalid argument
Additional information: 2

I have already made some expdp testing, and it has worked, the only difference during this testing is that it was made on a RAC environment.

When taking a look at the background dump dest trace files I found this:

-bash-3.00$ more rdbkdi1_dm00_31645.trc
/u01/app/admin/RDBKDI/bdump/rdbkdi1_dm00_31645.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: eg6886
Release: 2.6.16-xenU
Version: #2 SMP Tue Oct 24 16:33:50 CDT 2006
Machine: i686
Instance name: RDBKDI1
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 31645, image: oracle@eg6886 (DM00)
*** ACTION NAME:(EXPHR01) 2008-07-24 19:38:22.781
*** MODULE NAME:(Data Pump Master) 2008-07-24 19:38:22.781
*** SERVICE NAME:(SYS$USERS) 2008-07-24 19:38:22.781
*** SESSION ID:(125.291) 2008-07-24 19:38:22.781
kswsdlaqsub: unexpected exception err=604, err2=24010
kswsdlaqsub: unexpected exception err=604, err2=24010

If ever someone finds this error solution or reference to it I'll be thankful to hear about it.

OMS version not checked yet ...

2008-07-22T11:25:00.003-05:00

When a grid control agent was installed during a RAC setup an error showed up at the end of the agent configuration assistant.

The agent was started successfully, but it was not possible to force the agent to have the XML files uploaded to the grid server, it was when the dreaded error "EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet.." showed up.

The problem basically was due to a password typing error during the configuration phase.

The Troubleshooting Procedure Outline was:

Setup the Agent Home environment
export AGENT_HOME=/u01/app/oracle/pruduct/10.2.0/agent10g
export PATH=$AGENT_HOME/bin:$PATH

Startup the Agent
emctl start agent

Clear the Agent status
emctl clearstate agent

Reset Credentials
emctl secure agent

Retry and verify synchronization operations
emctl upload agent
emctl status agent

###
### EM Agent Troubleshooting log
###

$ export AGENT_HOME=/u01/app/oracle/product/10.2.0/agent10g
$ export PATH=$AGENT_HOME/bin:$PATH

$ # Start agent
$ emctl start agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Starting agent ......... started.
$
$
$ # Trying to force XML files uploading
mct$ emctl upload agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

$ # Clear EM Agent status
$ emctl clearstate agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
EMD clearstate completed successfully

$ # Configuring OMS Credentials
$ emctl secure agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Enter Agent Registration password : *******
Agent successfully stopped... Done.
Securing agent... Started.
Requesting an HTTPS Upload URL from the OMS... Done.
Requesting an Oracle Wallet and Agent Key from the OMS... Done.
Check if HTTPS Upload URL is accessible from the agent... Done.
Configuring Agent for HTTPS in CENTRAL_AGENT mode... Done.
EMD_URL set in /u01/app/oracle/product/10.2.0/agent10g/eg6881.us.oracle.com/sysm
an/config/emd.properties
Securing agent... Successful.
Agent successfully restarted... Done.

$ # Retrying operations
$ emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.1.0
OMS Version : 10.2.0.1.0
Protocol Version : 10.2.0.0.0
Agent Home : /u01/app/oracle/product/10.2.0/agent10g/eg6881.us.oracle.com
Agent binaries : /u01/app/oracle/product/10.2.0/agent10g
Agent Process ID : 5307
Parent Process ID : 5260
Agent URL : https://eg6881.us.oracle.com:3872/emd/main
Repository URL : https://eg6876.us.oracle.com:1159/em/upload
Started at : 2008-07-22 15:40:37
Started by user : oracle
Last Reload : 2008-07-22 15:40:37
Last successful upload : 2008-07-22 15:40:58
Total Megabytes of XML files uploaded so far : 0.79
Number of XML files pending upload : 18
Size of XML files pending upload(MB) : 1.86
Available disk space on upload filesystem : 27.54%
Last successful heartbeat to OMS : 2008-07-22 15:40:43
---------------------------------------------------------------
Agent is Running and Ready

$ emctl upload agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

$ emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.1.0
OMS Version : 10.2.0.1.0
Protocol Version : 10.2.0.0.0
Agent Home : /u01/app/oracle/product/10.2.0/agent10g/eg6881.us.oracle.com
Agent binaries : /u01/app/oracle/product/10.2.0/agent10g
Agent Process ID : 5307
Parent Process ID : 5260
Agent URL : https://eg6881.us.oracle.com:3872/emd/main
Repository URL : https://eg6876.us.oracle.com:1159/em/upload
Started at : 2008-07-22 15:40:37
Started by user : oracle
Last Reload : 2008-07-22 15:40:37
Last successful upload : 2008-07-22 15:41:13
Total Megabytes of XML files uploaded so far : 2.65
Number of XML files pending upload : 1
Size of XML files pending upload(MB) : 0.03
Available disk space on upload filesystem : 27.54%
Last successful heartbeat to OMS : 2008-07-22 15:40:43
---------------------------------------------------------------
Agent is Running and Ready
$

Blog not properly displayed in Firefox

2008-05-23T08:03:00.005-05:00

Recently, without a warning, my blog site stopped being properly displayed, when drilling down on the circumstances that prevented it from properly displaying at least, so far, I have found that when it is displayed with Firefox browsers the left navigation bar 'disappears'. If displayed with Internet Explorer it is properly displayed.

I had not realized about this fact, but when Eduardo Legatti asked me what happened with the left navigation bar it was when I entered as a regular non authenticated user, I saw something happened with my Blog Archive and my Topics menus, those were gone. Eduardo told me he uses also Firefox.

In my personal opinion my default choice is Firefox, just like 24% of the blog users, not Internet Explorer, unless due to VPN restrictions or bank access requirement. I have tried to change my layout, and unsuccessfully it behaves the same. So the workaround in the mean time is to click on the item menu or use Internet Explorer :(

Some people have reported differences between firefox and internet explorer [Resolving Browser Display Discrepancies Between IE and Firefox].

... After a couple of hours after publishing this post the left navigation bar is back again, it looks like there was some issue wiht the cache. I have removed the cache from firefox, but I am not sure this was the actual solution.

Missing libraries when installing EM Grid Control

2008-05-22T10:14:00.005-05:00

When I was trying to install and setup an Enterprise Manager Grid Control 10g R2 (10.2.0.1.0) on an Oracle Enterprise Linux 4 (OEL4) platform, I surprisingly found I didn't meet the package list install requirement, it stated there were three libraries missing, I did install and reinstall them from the OEL4 disks, unsuccessfully, since the error still persist. I should have STWF (Searched The Web First) for further cases where something curious like this had happened to somebody( Linux OEM Grid Control 10.2.0.1.0 installation issue), and yes, it happened to this individual who was very kind in documenting this at the Oracle forums.

The error is found with these three missing RPM's:

compat-libstdc++-296-2.96-132.7.2 (failed)
libstdc++devel-3.4.3-22.1 (failed)
openmotif-21-2.1.30-11 (failed)

It can be seen that openmotif-21 is misspelled, it should read openmotif21, without the hyphen.
I did further search at Metalink [Note:343364.1, Prerequisites and Install Information for EM 10g Grid Control Components on Red Hat EL 4.0 Update 2 (and higher updates) Platforms] and I found that when installing EM Grid Control with a fresh database on 10g, it always shows this library error. Experience always comes when it is no longer required. The installer has a flaw, so it will show an error even if the three missing libraries are installed on the system, it is because a bad list of RPM's in the installer. This error can be user checked and proceed with installation, it will succeed. Just, this error is ever found, I suggest to proceed with caution, and make sure the list of RPM's meet the minimum requirements, if this manual check is successful, the missing RPM's error can be skipped.

rpm -q glibc glibc-2.3.4-2.13
rpm -q make make-3.80-5
rpm -q binutils binutils-2.15.92.0.2-15
rpm -q gcc gcc-3.4.4-2
rpm -q libaio libaio-0.3.103-3
rpm -q glibc-common glibc-common-2.3.4-2.13
rpm -q setarch setarch-1.6-1
rpm -q pdksh pdksh-5.2.14-30.3
rpm -q openmotif21 openmotif21-2.1.30-11.RHEL4.4
rpm -q sysstat sysstat-5.0.5-1
rpm -q gnome-libs gnome-libs-1.4.1.2.90-44.1
rpm -q libstdc++ libstdc++-3.4.4-2
rpm -q libstdc++-devel libstdc++-devel-3.4.4-2
rpm -q compat-libstdc++-296 compat-libstdc++-296-2.96-132.7.2
rpm -q compat-db compat-db-4.1.25-9
rpm -q control-center control-center-2.8.0-12.rhel4.2
rpm -q xscreensaver xscreensaver-4.18-5.rhel4.9

Enjoy!

CRS-0223: Resource 'ResourceName' has placement error

2008-05-15T14:55:00.003-05:00

While I was attempting to configure a RAC environment, I noticed some services were started and some other weren't, when I tried to start the services, the 'placement' error showed up, this was due because when trying to start the crs with the crsctl start crs command on both nodes, there was not enough time between the start process in node 1 and node2, so the VIP address has switched its control from node 1 to node2, and when I tried to bring back the service to node 1 it by explicitly starting it on node 1 the error showed up.

The Oracle error manual states:

Oracle Error : CRS-0223: Resource '%s' has placement error.
Cause: There was no host available on which to failover/start the resource based on the Placement Policy for the resource.
Action: Check the target host for the resource and restart the resource using the crs_start command.

$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....A1.inst application ONLINE OFFLINE
ora....A2.inst application ONLINE ONLINE eg3704
ora.RDBHRA.db application ONLINE ONLINE eg3704
ora....SM1.asm application ONLINE OFFLINE
ora....03.lsnr application ONLINE OFFLINE
ora.eg3703.gsd application ONLINE ONLINE eg3703
ora.eg3703.ons application ONLINE ONLINE eg3703
ora.eg3703.vip application ONLINE ONLINE eg3704
ora....SM2.asm application ONLINE ONLINE eg3704
ora....04.lsnr application ONLINE ONLINE eg3704
ora.eg3704.gsd application ONLINE ONLINE eg3704
ora.eg3704.ons application ONLINE ONLINE eg3704
ora.eg3704.vip application ONLINE ONLINE eg3704

[oracle@eg3703 bin]$ ./crs_start ora.eg3703.LISTENER_EG3703.lsnr
eg3703 : CRS-1018: Resource ora.eg3703.vip (application) is already running on eg3704
eg3704 : CRS-1019: Resource ora.eg3703.LISTENER_EG3703.lsnr (application) cannot run on eg3704

CRS-0223: Resource 'ora.eg3703.LISTENER_EG3703.lsnr' has placement error.
[oracle@eg3703 bin]$ ./crs_start ora.eg3703.ASM1.asm
eg3703 : CRS-1018: Resource ora.eg3703.vip (application) is already running on eg3704
eg3704 : CRS-1019: Resource ora.eg3703.ASM1.asm (application) cannot run on eg3704

CRS-0223: Resource 'ora.eg3703.ASM1.asm' has placement error.

At first hand, the procedure was to shutdown services and start them in order, so when that each service could take its place.

Name Type Target State Host
------------------------------------------------------------
ora....A1.inst application ONLINE ONLINE eg3703
ora....A2.inst application ONLINE ONLINE eg3704
ora.RDBHRA.db application ONLINE ONLINE eg3703
ora....SM1.asm application ONLINE ONLINE eg3703
ora....03.lsnr application ONLINE ONLINE eg3703
ora.eg3703.gsd application ONLINE ONLINE eg3703
ora.eg3703.ons application ONLINE ONLINE eg3703
ora.eg3703.vip application ONLINE ONLINE eg3703
ora....SM2.asm application ONLINE ONLINE eg3704
ora....04.lsnr application ONLINE ONLINE eg3704
ora.eg3704.gsd application ONLINE ONLINE eg3704
ora.eg3704.ons application ONLINE ONLINE eg3704
ora.eg3704.vip application ONLINE ONLINE eg3704

Adding a voting disk online fails

2008-05-15T13:19:00.003-05:00

This happened on a RHEL4/Ora10gR2 Ver. 10.2.0.1.0.
In a cluster environment it is important to keep RAC availability at maximum and avoid unnecessary either planned or unplanned down times. That is why the online commands and functionality are for.

When attempting to add a Voting disk to my RAC configuration an error, "Cluster is not in a ready state for online disk addition" after issuing this command:

$ sudo /u01/crs1020/bin/crsctl add css votedisk /dev/raw/raw8

It was not possible to perform this task, even though the manual states it is possible, after a bit of research in metalink I found this metalink note:"ADDING VOTING DISK ONLINE CRASH THE CRS" This is due to the bug 4898020 and the platforms affected are, so far, Linux at the 10.2.0.1.0 release, One more reason to keep updated with the latest patchset available.The only workaround for this is to shutdown the crs and retry the operation with the force flag

So in my case the complete procedure to perform this task that could have been directly performed online was:

1. Stop all application processes

$ /u01/crs1020/bin/srvctl stop nodeapps -n eg7910
$ /u01/crs1020/bin/srvctl stop nodeapps -n eg7909

2. Verify services were down

$ /u01/crs1020/bin/crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....H1.inst application OFFLINE OFFLINE
ora....H2.inst application OFFLINE OFFLINE
ora.RDBHRH.db application OFFLINE OFFLINE
ora....SM1.asm application OFFLINE OFFLINE
ora....09.lsnr application OFFLINE OFFLINE
ora.eg7909.gsd application OFFLINE OFFLINE
ora.eg7909.ons application OFFLINE OFFLINE
ora.eg7909.vip application OFFLINE OFFLINE
ora....SM2.asm application OFFLINE OFFLINE
ora....10.lsnr application OFFLINE OFFLINE
ora.eg7910.gsd application OFFLINE OFFLINE
ora.eg7910.ons application OFFLINE OFFLINE
ora.eg7910.vip application OFFLINE OFFLINE

3. Stop CRS on both nodes

$ sudo /u01/crs1020/bin/crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

$ ssh eg7910 sudo /u01/crs1020/bin/crsctl stop crs
Stopping resources.
Successfully stopped CRS resources
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

4. Add the votedisks with the force flag.

$ sudo /u01/crs1020/bin/crsctl add css votedisk /dev/raw/raw8 -force
Now formatting voting disk: /dev/raw/raw8
successful addition of votedisk /dev/raw/raw8.

$ sudo /u01/crs1020/bin/crsctl add css votedisk /dev/raw/raw9 -force
Now formatting voting disk: /dev/raw/raw9
successful addition of votedisk /dev/raw/raw9.

5. Verify the voting disks were added

$ /u01/crs1020/bin/crsctl query css votedisk
0. 0 /dev/raw/raw7
1. 0 /dev/raw/raw8
2. 0 /dev/raw/raw9
located 3 votedisk(s).

6. Restart CRS and applications.

$ sudo /u01/crs1020/bin/crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly
$ ssh eg7910 sudo /u01/crs1020/bin/crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly

This should have happened on line, and this could mean a momentarily loss of RAC availability.

How to manually remove a service

2008-05-08T18:24:00.002-05:00

When performing an Oracle deinstall, sometimes services are left behind, such as the TNSListener Service. In order for you to get rid of it, there are two options, first one, remove it from the regedit, this choice must be carefully executed, you don't want to remove other service.
1. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
2. Locate the service name, right click it and select Delete

It is always advisable to have a regedit backup prior to performing this task.

The second choice is the sc command
sc delete

This command is also used to manually create services
sc create

The complete syntax options for the sc command line utility is:
DESCRIPTION:
SC is a command line program used for communicating with the
NT Service Controller and services.
USAGE:
sc

Why is Varchar2 oversizing bad?

2008-05-07T19:33:00.003-05:00

Apparently the same space consumption would be the same if a Varchar2(4) is used versus a Varchar2(4000), if you require one byte, then one byte will be allocated, and performance should be the same, but when taking a look at the performance statistics there are some surprises that arise.
When fetching information from Oracle, buffers and fetching structures are being prepared beforehand to be able to manage the maximum column lenght, applications such as toad may run out of memory trying to allocate resources to retrieve the information from an unnecessarily oversized varchar2 column.

Let's analyze this case:

Table 1: Varchar2LenghtDemo

SQL> desc Varchar2LenghtDemo
Name                                      Null?    Type
----------------------------------------- -------- ----------------------
ID                                                 NUMBER(10)
VCCOL                                              VARCHAR2(4000)
VCCOL2                                             VARCHAR2(4000)
VCCOL3                                             VARCHAR2(4000)
VCCOL4                                             VARCHAR2(4000)
VCCOL5                                             VARCHAR2(4000)
VCCOL6                                             VARCHAR2(4000)
VCCOL7                                             VARCHAR2(4000)
VCCOL8                                             VARCHAR2(4000)
VCCOL9                                             VARCHAR2(4000)
VCCOL10                                            VARCHAR2(4000)
VCCOL11                                            VARCHAR2(4000)
VCCOL12                                            VARCHAR2(4000)
VCCOL13                                            VARCHAR2(4000)
VCCOL14                                            VARCHAR2(4000)
VCCOL15                                            VARCHAR2(4000)
VCCOL16                                            VARCHAR2(4000)
VCCOL17                                            VARCHAR2(4000)
VCCOL18                                            VARCHAR2(4000)
VCCOL19                                            VARCHAR2(4000)
VCCOL20                                            VARCHAR2(4000)

23:39:55 SQL> select count(*) from Varchar2LenghtDemo;

COUNT(*)
----------
 8388608

Elapsed: 00:00:10.18

Statistics
----------------------------------------------------------
        5  recursive calls
        0  db block gets
    55719  consistent gets
    55669  physical reads
        0  redo size
      414  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
        1  rows processed

If we compare the statistics from one table and the other:

SQL> desc Varchar2LenghtDemoShort
Name                                      Null?    Type
----------------------------------------- -------- --------------------

ID                                                 NUMBER(10)
VCCOL                                              VARCHAR2(4)
VCCOL2                                             VARCHAR2(4)
VCCOL3                                             VARCHAR2(4)
VCCOL4                                             VARCHAR2(4)
VCCOL5                                             VARCHAR2(4)
VCCOL6                                             VARCHAR2(4)
VCCOL7                                             VARCHAR2(4)
VCCOL8                                             VARCHAR2(4)
VCCOL9                                             VARCHAR2(4)
VCCOL10                                            VARCHAR2(4)
VCCOL11                                            VARCHAR2(4)
VCCOL12                                            VARCHAR2(4)
VCCOL13                                            VARCHAR2(4)
VCCOL14                                            VARCHAR2(4)
VCCOL15                                            VARCHAR2(4)
VCCOL16                                            VARCHAR2(4)
VCCOL17                                            VARCHAR2(4)
VCCOL18                                            VARCHAR2(4)
VCCOL19                                            VARCHAR2(4)
VCCOL20                                            VARCHAR2(4)

23:43:02 SQL> r
1* select count(*) from Varchar2LenghtDemoShort

COUNT(*)
----------
 8388608

Elapsed: 00:00:05.50

Statistics
----------------------------------------------------------
        0  recursive calls
        0  db block gets
    55756  consistent gets
    22077  physical reads
        0  redo size
      414  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
        1  rows processed

Almost all statistics are the same, except for these two:

Operation: Select count(*) from table

Table          Rows      Time Elapsed  Phy Rds
Varchar(4)     8,388,608  00:00:05.50   22,077
Varchar(4000)  8,388,608  00:00:10.18   55,669

Since the number of physical reads is meaningful different a question arises:
How many rows per block? Is the row density the same?

Table          Avg Rows per Block
Varchar(4)         150.99
Varchar(4000)      150.99

Quite surprising, Row density is the same, but physical blocks processed are doubled.

Conclusion

Even though Varchar2 columns allow a variable number of characters and it will just use the number of bytes required, it is absolutely better to properly size the actual requirements, otherwise a meaningful performance issue unnecessarily will be created.

Dump an Oracle Block to the trace

2008-05-01T07:40:00.002-05:00

This is the script I use to dump an Oracle block.

set pagesize 0
set def =
set def &
column header_block new_value m_block
column header_file new_value m_file

select
header_file,
header_block + &m_offset header_block
from
dba_segments
where
segment_name = upper('&m_segment')
and nvl(partition_name,'xxx') = nvl(upper('&m_partition'),'xxx')
and owner = upper('&m_owner')
;

alter system dump datafile &m_file block min &m_block block max &m_block;

Oracle Index Cluster Storage

2008-04-02T02:30:00.002-05:00

A Cluster Index is an object oriented to gain performance when frequent master/detail relation ships are found between two tables. This is the fastest way to perform a join between two tables, but there are storage issues when this storage structure is used. The purpose of this article is to analyze the way Oracle reserves storage for a Cluster Index

Create cluster and verify Cluster storage

SQL> l
1 create cluster cluster_emp
2 (department_id NUMBER(4))
3* index

SQL> create index i_cluster_emp on cluster cluster_emp;

Index created.

SQL> r
1 create table emp cluster cluster_emp(department_id)
2 as
3* select employee_id, first_name, last_name, hire_date, salary, department_id from employees

Table created.

SQL> create table dept
2 cluster cluster_emp(department_id)
3 as
4 select * from departments;

Table created.

Verify where data is stored

SQL> r
1 select owner, segment_name, file_id, block_id, blocks, bytes
2 from dba_extents
3 where segment_name in ('I_CLUSTER_EMP','CLUSTER_EMP')
4* order by segment_name, extent_id

OWNER SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS BYTES
-------- ------------- ---------- ---------- ---------- ----------
HR CLUSTER_EMP 4 3825 8 65536
HR CLUSTER_EMP 4 1817 8 65536
HR CLUSTER_EMP 4 3817 8 65536
HR CLUSTER_EMP 4 3809 8 65536
HR I_CLUSTER_EMP 4 3833 8 65536

Determine how many rows are retrieved for each department Id when join is performed

SQL> R
1 SELECT D.DEPARTMENT_ID, COUNT(*)
2 FROM EMP E, DEPT D
3 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
4 GROUP BY D.DEPARTMENT_ID
5* ORDER BY D.DEPARTMENT_ID

DEPARTMENT_ID COUNT(*)
------------- ----------
10 1
20 2
30 6
40 1
50 45
60 5
70 1
80 34
90 3
100 6
110 2

11 rows selected.

Display where specific rows are stored.

SQL> R
1 SELECT D.DEPARTMENT_ID,
2 DBMS_ROWID.ROWID_RELATIVE_FNO(D.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(D.ROWID) DEPT_ROW,
3 DBMS_ROWID.ROWID_RELATIVE_FNO(E.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(E.ROWID) EMP_ROW,
4 COUNT(*)
5 FROM DEPT D, EMP E
6 WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
7 GROUP BY
8 D.DEPARTMENT_ID,
9 DBMS_ROWID.ROWID_RELATIVE_FNO(D.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(D.ROWID),
10 DBMS_ROWID.ROWID_RELATIVE_FNO(E.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(E.ROWID)
11* ORDER BY DEPARTMENT_ID

DEPARTMENT_ID DEPT_ROW EMP_ROW COUNT(*)
------------- -------- -------- ----------
10 4:1821 4:1821 1
20 4:1822 4:1822 2
30 4:3829 4:3829 6
40 4:1823 4:1823 1
50 4:3830 4:3830 45
60 4:3832 4:3832 5
70 4:1824 4:1824 1
80 4:1819 4:1819 34
90 4:3831 4:3831 3
100 4:3828 4:3828 6
110 4:1817 4:1817 2

11 rows selected.

There it can be seen that for each department Id one block is assigned, this will make Oracle cluster to require more storage than that required to store individual independent tables. On the other hand if few employee rows are assigned to a department, a lot of block space will be wasted. Transactional operations over the individual cluster participant tables will make rows to be physically reallocated, thus making Oracle clusters suitable for fast join operations, but slow for transactional tables.

Block dumped to verify physical row storage from the first two cluster rows.

C:\Oracle\product\10.1.0\admin\alpha\udump>more alpha_ora_6088.trc
Dump file c:\oracle\product\10.1.0\admin\alpha\udump\alpha_ora_6088.trc
Wed Apr 02 01:12:30 2008
ORACLE V10.1.0.5.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1077M/2046M, Ph+PgF:3110M/3938M, VA:1701M/2047M
Instance name: alpha

Redo thread mounted by this instance: 1

Oracle process number: 13

Windows thread id: 6088, image: ORACLE.EXE (SHAD)

*** 2008-04-02 01:12:30.265
*** ACTION NAME:() 2008-04-02 01:12:30.250
*** MODULE NAME:(SQL*Plus) 2008-04-02 01:12:30.250
*** SERVICE NAME:(SYS$USERS) 2008-04-02 01:12:30.250
*** SESSION ID:(152.1209) 2008-04-02 01:12:30.250
Start dump data blocks tsn: 4 file#: 4 minblk 1821 maxblk 1822
buffer tsn: 4 rdba: 0x0100071d (4/1821)
scn: 0x0000.001deadc seq: 0x01 flg: 0x06 tail: 0xeadc0601
frmt: 0x02 chkval: 0x1b2c type: 0x06=trans data
Block header dump: 0x0100071d
Object id on Block? Y
seg/obj: 0xc312 csc: 0x00.1dea96 itc: 2 flg: E typ: 1 - DATA
brn: 1 bdba: 0x1000ef1 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.021.0000051a 0x008005bb.02be.34 C--- 0 scn 0x0000.001dea72
0x02 0x0003.00a.000008e7 0x00800526.04cd.12 --U- 1 fsc 0x0000.001deadc

data_block_dump,data header at 0x6051264
===============
tsiz: 0x1f98
hsiz: 0x20
pbl: 0x06051264
bdba: 0x0100071d
76543210
flag=--------
ntab=3
nrow=3
frre=-1
fsbo=0x20
fseo=0x1f47
avsp=0x1f27
tosp=0x1f27
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=1 offs=1
0x16:pti[2] nrow=1 offs=2
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f60
0x1e:pri[2] offs=0x1f47
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 2 comc: 2 pk: 0x0100071d.0 nk: 0x0100071d.0
col 0: [ 2] c1 0b
tab 1, row 0, @0x1f60
tl: 34 fb: -CH-FL-- lb: 0x0 cc: 5 cki: 0
col 0: [ 2] c2 03
col 1: [ 8] 4a 65 6e 6e 69 66 65 72
col 2: [ 6] 57 68 61 6c 65 6e
col 3: [ 7] 77 bb 09 11 01 01 01
col 4: [ 2] c2 2d
tab 2, row 0, @0x1f47
tl: 25 fb: -CH-FL-- lb: 0x2 cc: 3 cki: 0
col 0: [14] 41 64 6d 69 6e 69 73 74 72 61 74 69 6f 6e
col 1: [ 2] c2 03
col 2: [ 2] c2 12
end_of_block_dump
buffer tsn: 4 rdba: 0x0100071e (4/1822)
scn: 0x0000.001deadc seq: 0x01 flg: 0x06 tail: 0xeadc0601
frmt: 0x02 chkval: 0x028c type: 0x06=trans data
Block header dump: 0x0100071e
Object id on Block? Y
seg/obj: 0xc312 csc: 0x00.1dea96 itc: 2 flg: E typ: 1 - DATA
brn: 1 bdba: 0x1000ef1 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.02a.0000051a 0x008005bb.02be.36 C--- 0 scn 0x0000.001dea73
0x02 0x0003.00a.000008e7 0x00800526.04cd.13 --U- 1 fsc 0x0000.001deadc

data_block_dump,data header at 0x6051264
===============
tsiz: 0x1f98
hsiz: 0x22
pbl: 0x06051264
bdba: 0x0100071e
76543210
flag=--------
ntab=3
nrow=4
frre=-1
fsbo=0x22
fseo=0x1f2c
avsp=0x1f0a
tosp=0x1f0a
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=2 offs=1
0x16:pti[2] nrow=1 offs=3
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f5c
0x1e:pri[2] offs=0x1f41
0x20:pri[3] offs=0x1f2c
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 3 comc: 3 pk: 0x0100071e.0 nk: 0x0100071e.0
col 0: [ 2] c1 15
tab 1, row 0, @0x1f5c
tl: 38 fb: -CH-FL-- lb: 0x0 cc: 5 cki: 0
col 0: [ 3] c2 03 02
col 1: [ 7] 4d 69 63 68 61 65 6c
col 2: [ 9] 48 61 72 74 73 74 65 69 6e
col 3: [ 7] 77 c4 02 11 01 01 01
col 4: [ 3] c3 02 1f
tab 1, row 1, @0x1f41
tl: 27 fb: -CH-FL-- lb: 0x0 cc: 5 cki: 0
col 0: [ 3] c2 03 03
col 1: [ 3] 50 61 74
col 2: [ 3] 46 61 79
col 3: [ 7] 77 c5 08 11 01 01 01
col 4: [ 2] c2 3d
tab 2, row 0, @0x1f2c
tl: 21 fb: -CH-FL-- lb: 0x2 cc: 3 cki: 0
col 0: [ 9] 4d 61 72 6b 65 74 69 6e 67
col 1: [ 3] c2 03 02
col 2: [ 2] c2 13
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1821 maxblk 1822

C:\Oracle\product\10.1.0\admin\alpha\udump>

Data Dictionary Statistics Gathering

2008-03-22T12:46:00.002-05:00

Normally data dictionary statistics in 9i is not required unless performance issues are detected. Metalink note where data dictionary gathering is discouraged (245051.1) dates back from 16-APR-2004, almost four years ago. In 10g data dictionary statistics are collected just the same as any other schema object. There are official documents that suggest the statistics gathering in 9i (216550.1). It is commonly suggested to gather data dictionary statistics in the Oracle Applications environments. In fact, when performing a 9i database upgrade in an applications database, it is listed as a post upgrade step.

In 10g this is an official recommendation ML (457926.1).

Known issues when data dictionary statistics are present in sys schema.
It is suggested to gather data dictionary statistics in 9i under direct oracle support advice when performance problems are detected.
In catpach for 9.2.0.2, 9.2.0.3 and 9.2.0.4 there was an note documenting the issue that the catpatch process took too much time due to the existing of statistics
This document doesn't state that data dictionary statistics musn't be collected, it only suggest data dictionary statistics must be deleted prior to the upgrade process and once it finishes statistics can be collected once more.

In fact the official suggestion states this:
dbms_stats.delete_schema_stats('SYS'); -- prior to perform the upgrade process
dbms_stats.gather_schema_stats('SYS'); -- once the process has finished

Ref. Gathering Data Dictionary Statistics
Doc ID: Note:245051.1

There is anohter documented issue, listed back on 14-JAN-2005, which states that querying dba_free_space hangs if data dictionary statistics are collected, but the metalink note never states that statistics are not suggested, but it rather suggests a procedure to solve this issue.
Another documented issue dates back to 20-Aug-2003, and reported issues when working with portal 3.0.9.8.1 if data dictionary were present.

Identify if data dictionary statistics have been collected.
If there are performance problems due to large data dictionary access, it is advisable to check if data dictionary statistics have been collected. It can be easily displayed by querying data dictionary tables belonging to sys.

select table_name, last_analyzed
from dba_tables where owner='SYS'
and table_name='FET$';

This other query is useful to know how many sys tables have statistics:

select distinct trunc(last_analyzed), count(*)
from dba_tables
where owner='SYS'
group by trunc(last_analyzed)

Upgrade guide.

If collecting data dictionary statistics during the upgrade process is a time consuming task, it is advisable to collect them prior to perform the upgrade task. Both in Oracle 8i and 9i, the procedure that must be run is DBMS_STATS.GATHER_SCHEMA_STATS, in fact the procedure suggested is by means of EXEC DBMS_STATS.GATHER_DICTIONARY_STATS, which was added starting DB10gR1.

References and documentation.

Complete Checklist for Manual Upgrades to 10gR2
Doc ID: Note:316889.1

Oracle Applications Release 11i with Oracle9i Release 2 (9.2.0)
Doc ID: Note:216550.1

Select COUNT(*) from DBA_FREE_SPACE Hangs
Doc ID: Note:121729.1

Initial Portal Page Request Takes A Lot of Time
Doc ID: Note:198471.1

Gather Optimizer Statistics For Sys And System
Doc ID: Note:457926.1

Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)?
Doc ID: Note:35272.1

Ref. How to quickly verify whether data dictionary statistics has been collected
Doc ID: Note:333175.1

java.lang.Exception: Exception in sending Request :: null

2008-02-28T11:58:00.007-06:00

When accessing Enterprise Manager DB Control Console a couple of error messages display, right after providing the sys credentials, the console appears with a down arrow and at the header it says:

java.lang.Exception: Exception in sending Request :: null

If you try to move to another tab, such as the performance tab, it asks for credentials and responses:

Io exception: Unknown host specified

These are symptoms of a not properly configured Agent Time Zone.

Troubleshooting Agent Time Zone

This case happened to me on a Windows XP Professional in Mexican Spanish after a timezone change. I notice a similar error shows up on databases affected by the automatic time zone changes after synchronization with the time.windows.com, which recently has included two time zones for Mexico - one named old and the other named new-

Troubleshooting
0. Consider these files when performing the troubleshooting phase:
emd.properties
supportedtzs.lst

1. Stop the console
\bin\emctl stop dbconsole

2. Backup the file located at ORACLE_HOME/hostname_SID/sysman/config/emd.properties

3. Edit the emd.properties file and look for the agentTZRegion (generally appears at the end of the file)

At the file emd.properties, located at the ORACLE_HOME/hostname_instanceName/sysman/config

change the value

agentTZRegion=GMT To agentTZRegion=America/Mexico_City

Evidently, this is for the particular case of Mexico City, but similar issues could apply to other time zones.
For other timezones affected, such as the one corresponding to Egypt, change the value of this parameter from GMT+2 to Egypt which is included in the supportedtzs.lst file.
So the parameter will be agentTZRegion=Egypt

4. When agent is unable to find a proper time zone it will adopt GMT, so it could be the value registered so far. Change this value by the value corresponding to the OS time zone, this time zone should be one listed at the ORACLE_HOME/sysman/admin/nsupportedtzs.lst file.

5. Execute this command
emctl resetTZ agent
emctl config agent getTZ

After issuing the resetTZ command a similar issue like this one may appear:
To complete this process, you must either:

connect to the database served by this DBConsole as user 'sysman', and execute:

SQL> exec mgmt_target.set_agent_tzrgn('pc06.oracle.com:3938','America/Mexico_City')

-- or --

connect to the database served by this DBConsole as user 'sys', and execute:

SQL> alter session set current_schema = SYSMAN;
SQL> exec mgmt_target.set_agent_tzrgn('pc06.oracle.com:3938','America/Mexico_City')

In order for these commands to be successful, you are required the agent to have registered some values at the EM repository. Check this query, there should be similar information displayed when connected as sysman:
SQL> select target_name, target_type from mgmt_targets;

TARGET_NAME TARGET_TYPE
------------------------------------
pc06.oracle.com host
orcl.oracle.com oracle_database
pc06.oracle.com:3938 oracle_emd
Management Services and Repository oracle_emrep
LISTENER_pc06.oracle.com oracle_listener

6. The previous command will ask to perform some actions at the sysman repository level. Execute the reset at the repository level by:
SQL> alter session set current_schema = SYSMAN;

SQL> exec mgmt_target.set_agent_tzrgn('hostname:3938','TimeZone');

This command should be successful, otherwise it could be because the agent hasn't ever started and it has never registered, even with the wrong TZ at the repository.

In order for you to verify this has ever run and the agent is properly registered, issue this query as sysman:

SQL> select target_name, target_type from mgmt_targets;
The target with the default port 3938 is the target we are looking for.

The target related to the port 3938 should be listed. Otherwise try to start the agent so it can register this target. If agent is not starting, please veriy at the logs what could be a reason that prevents agent from starting. Most of the times it is because of a wrong specified

6. Try to login to the dbconsole and check if the error still exists.

7. Now start the EM Console, the problem should be fixed by now.

Notes.
The value of the time zone is a valid value listed at ORACLE_HOME/sysman/admin/nsupportedtzs.lst Make sure the time zone matches that of the host computer.

if when executing the emdctl command an error related to java appears, it is because the right java version is not being invoked. Make sure the path environment variable properly includes the jdk environment provided at the same Oracle Home where the database is related to.

path=%PATH%;C:\Oracle\product\10.2.0\db_1\jdk\jre\bin;C:\Oracle\product\10.2.0\db_1\jdk\bin
path=%path%;C:\Oracle\product\10.2.0\db_1\jdk\jre\bin\client

References.

Problem: Startup Agent: ORA-20233: Invalid agent name when running mgmt_target.set_agent_tzrgn procedure in repository
Doc ID: Note:388280.1 --> This note has particularly helped me to solve my particular issue.

Problem: Startup Agent: EM Agent will not start due to Timezone mismatch (Daylight Savings changes for Australia)
Doc ID: Note:362888.1 --> some useful ideas were taken from this note

Problem: Startup Agent: Agent Fails to Start due to Incorrect Timezone File Used as Pointed by ORA_TZFILE
Doc ID: Note:409121.1

Problem: Startup Agent: ORA-20233: Invalid agent name when running mgmt_target.set_agent_tzrgn procedure in repository
Doc ID: Note:388280.1

Enable Automatic logon on Windows 2003

2008-02-09T12:51:00.001-06:00

Verbatim from the Microsoft Article Id. 324737

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.
To use Registry Editor (Regedt32.exe) to turn on automatic logon, follow these steps:

Click Start, and then click Run.

In the Open box, type Regedt32.exe, and then press ENTER.

Locate the following subkey in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon

Double-click the DefaultUserName entry, type your user name, and then click OK.

Double-click the DefaultPassword entry, type your password, and then click OK.NOTE: If the DefaultPassword value does not exist, it must be added. To add the value, follow these steps:

a.	On the Edit menu, click New, and then point to String Value.
b.	Type DefaultPassword, and then press ENTER.
c.	Double-click DefaultPassword.
d.	In the Edit String dialog, type your password and then click OK.

NOTE: If no DefaultPassword string is specified, Windows automatically changes the value of the AutoAdminLogon key from 1 (true) to 0 (false), disabling the AutoAdminLogon feature.

On the Edit menu, click New, and then point to String Value.

Type AutoAdminLogon, and then press ENTER.

Double-click AutoAdminLogon.

In the Edit String dialog box, type 1 and then click OK.

10.

Quit Registry Editor.

11.

Click Start, click Shutdown, and then type a reason in the Comment text box.

12.

Click OK to turn off your computer.

13.

Restart your computer. You can now log on automatically.

Notes To bypass the AutoAdminLogon process and to log on as a different user, hold down the SHIFT key after you log off or after Windows restarts.

Registry change will not work if the “Logon Banner” is defined on the server either by a Group Policy object (GPO) or by a local policy. When policy is changed to not impact server, the feature works as expected.

An interactive console logon that has a different user on the server changes the DefaultUserName registry entry as the last logged on user indicator. AutoAdminLogon relies on the DefaultUserName entry to match the user and the password. Therefore, AutoAdminLogon may fail. You may configure a shutdown script to set the correct DefaultUserName entry for AutoAdminLogonAs. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

119364 (http://support.microsoft.com/kb/119364/) AutoAdminLogon loses DefaultUserName

Issues Installing Patchset 10.2.0.3.0 on Windows XP

2008-02-05T13:33:00.002-06:00

When attempting to install the Patchset 10.2.0.3.0 upgrade OUI complainted about a msvcr71.dll file in use. All Oracle services were shutdown, as directed by the Installation Guide. So this file shouldn't be in use by any Oracle executable. So where the culprit is? I found it by means of the Process Explorer, this is an utility that can be downloaded from the microsoft site, by navigating through the url: http://www.sysinternals.com/, the Microsoft Technet site can be reache, a couple of clicks and you're done, you can download it. A useful tool is the ListDLL too.

I found a svchost.exe process grabbing the msvcr71.dll file, I just killed it and the Installation process just went on to the end. I should point out that killing an OS process is a very serious matter, don't kill it unless you are absolutely sure what you are killing. DLL's shouldn't be locked by third party processes, so Oracle can just smoothly perform the upgrade. By means of the graphical tool previously mentioned I managed to know which process was locking the DLL.

Side Note, msvcr71.dll: is a module containing standard C library functions such asprintf, memcpy, and cos. It is a part of the Microsoft C RuntimeLibrary.

Orastack

2008-01-31T18:47:00.000-06:00

The orastack utility is a tool provided by oracle to set the stack size reserved or commited on a per thread basis in the Oracle Server on Windows Operating Systems. I had to use it because on a 10gR2 Winx32 database frequent ORA-04030 errors displayed along with TNS-12518.

The database must be shutdown prior to issue the command, otherwise this error will show up:

C:\Oracle\product\10.2.0\db_1\BIN>orastack oracle.exe 524288

Couldn't open file with CreateFile()

GetLastError() == 32

The default value is 1024K, it can be reduced to 512K, but it is not recommended to reduce the value below 300K, otherwise the ORA-01331 may start to show up.

Syntax:

orastack {no arguments} will display a useful README 1st.
orastack executableName will display the current settings
orastack executableName newValue will reset the current value to the specified newValue

ORA-04030 After 10gR2 Upgrade on Windows 2003

2008-01-31T18:27:00.000-06:00

I have faced this exact situation, a large awful Error stack with errors such as:
ORA-04030, TNS-12518.

The particular environment where I faced these issues was:

Oracle 10gR2 (10.2.0.1.0) for Windows x32

Windows 2003 Enterprise Edition 32 bits

It was Right after migrating a user processes intensive application from 9iR2 (9.2.0.8.0) to the above referred 10gR2 release.
I had documented this issue with the metalik Note: 10gR2 Dedicated Connections Intermittently Fail with TNS-12518 Doc ID: Note:371983.1

One of the recommendations was to reduce memory consumption, and among one of the solutions was the use of orastack.exe to achieve this reduction. Right now I am documenting the issues and drawbacks of performing this procedure.

Aonother recommendation is to reduce the SGA consumption, this was achieved by reducing the values of the SGA_TARGET and SGA_MAX_SIZE instance parameters.

Update 11/Feb/2008

The orastack utility was helpful in reducing both, the Oracle and tnslsnr executables stack size. Additionally, a reduction in users' contention greatly helped in reducing the frequency of ORA-04030 errors, it doesn't mean this error was completely wiped out, I am still waiting for the 10.2.0.4.0 patchset to assess if I can completely get rid of this issue.

Forums Code of Conduct

2008-01-30T01:20:00.002-06:00

Having actively participated at the Oracle forums, I have collected some useful recommendations for making life at forums easier for everybody.

1. Have a meaningful subject line. The title should properly summarize the problem you are facing, this will attract people who may already have had a similar issue more easily.

2. Give details about versions and technologies (DB, OS, working environment). Depending on the version combinations a suitable solution can or can't be found, if not properly specified the answer scope may be unnecessarily wide and blind guesses will start to be posted.

3. Give detailed error messages. Posting monosyllabic or partially specified error messages or being non specific won't bring you specific answers for sure.

4. If possible provide steps to reproduce the problem. A problem could be reproduced by the posters and configuration issues could be easily spotted. Lab environments could be easily set up.

5. Search before you post. Don't try to reinvent the wheel when someone else has already done it for you. A suitable answer can be found, a 10 minute goggling effort could find you an answer.

6. Get a name. Anonymous users are like ghosts without a name. user615770, for example, says nothing about a person. Everybody can take a few minutes to give themselves a real id, this will make posters more responsible for what they are postings, since they a real person with a real name and they are not hiding behind the mask of a generic and anonymous User Id.

7. If you solve it on your own, share the solution and mark problems with [Solved] in your thread title. There are people who not only feedback if the solutions provided helped in solving their issue, they never track their threads (specially seen when people mark their threads as Urgent). Thinking about other people who are looking for a similar solution, they never know if the provided steps lead to a successful or an unsuccessful result, or even if another workaround was found.

8. Never judge people because of their english level. Most of the people at the international forums aren't English native speakers (including myself), and they do their best to make themselves clear and understandable, let's help them instead of laughing at them, particularly if posting on a technical forum, which should not be used as an English language level assessment tool.

9. Never use discriminatory comments, nor references to ethnic groups or religions, nor make any comment that could be offensive to anybody, in a technical forum, strictly technical comments are never offensive to anybody.

10. Kill Uppercase and exclamation marks. This is considered as yelling and it could be offensive for some people, including me. 'Please' is always an advisable and suggestible keyword.

11. Think of your threads and contributions as if they were written on stone. More people than you could imagine is reading or will read the thread, and each time this thread is read people will read it as is.

12. RTFM is not an answer. I have seen several times self-qualified senior level whatever providing this kind of answers. RTFM is equal as saying "don't bother me, stop @%##~ me", and this is not an acceptable technical answer, specially for those who pretend to be self qualified as senior.

13. Address the community with respect. Self explanatory.

Thanks to Shay Shmeltzer's Blog out of which, this code of conduct proposal is based.

Issues found when upgrading 9.2.0 to 10.2.0

2008-01-30T00:32:00.001-06:00

Note: I strongly encourage people to read the Upgrade guide, read metalink Notes, get involved with 10g new features and setup a testing environment prior to perform the actual upgrade on a production environment.

ORA-00093: _shared_pool_reserved_min_alloc must be between 4000 and 0

In my particular case, the reason why I faced this issue was because I enabled ASMM and I didn't declare a value for SGA_TARGET. All parameters managed by ASMM were set to zero, but I forgot to explicitly set SGA_TARGET and SGA_MAX_SIZE. After setting suitable values for these parameters I was able to start my database. _shared_pool_reserved_min_alloc is a hidden parameter and this error appears due to ASMM missconfiguration.

This was on a manually upgraded database. I followed the below mentioned documentation, which was of great help.

Upgrade Guide
Oracle 10g New Features

Metalink Notes:

Complete checklist for manual upgrades of Oracle databases: 421191.1

Heap size nK exceeds notification threshold (2048K) warning
An error similar to this one is frequently seen at the alert.log file. Several queries which used to work without any issue, suddenly start to be reported at the alert.log file without apparent reason.
Memory Notification: Library Cache Object loaded into SGA
Heap size 11369K exceeds notification threshold (2048K)
Details in trace file d:\oracle\admin\aonmxdbf\udump\aonmxdbf_ora_3624.trc
KGL object name :EXPLAIN PLAN SET STATEMENT_ID = 'TRACE156742' INTO DBO.IW_PLAN_TABLE FOR Select

This is actually not an error, and the database is not about to crash, it only means a Heap size is greater than the value registered as a threshold. This is an issue on 10.2.0.1.0, this issue has been fixed on later patchsets. If you want to get rid of this warning, then an internal parameter has to be modified to a value greated than 2M. This should be made from a sql plus prompt connected as sysdba:

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

ORA-04030: out of process memory
An error similar to the one shown below is written at the alert.log file, a process has run out of memory. The error manual is funny, according to it you don't have to do any thing.

ORA-04030: out of process memory when trying to allocate string bytes (string,string)
Cause: Operating system process private memory has been exhausted
Action: none

Errors in file d:\oracle\admin\aonmxdbf\udump\aonmxdbf_ora_3176.trc:
ORA-04030: out of process memory when trying to allocate 66172 bytes (pga heap,kco buffer)
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x60504019] [ADDR:0x4]
[UNABLE_TO_WRITE] []

This error showed up when an end user tried to access an application with a view ranked as one of the top queries in the database. I have increased the PGA_AGGREGATE_TARGET instance parameter and I have enabled ASMM.

I should further clarify, this happened on a Windows 2003 environment, and I found out that the boot didn't have the /3G switch required to access more than 2G RAM memory. So I have set it too since the memory requirements were very close to the 2G limit.

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /3GB

The ORA-04030 was accompanied by TNS-12518 (TNS:listener could not hand off client connection)

It happened intermitently and when workload increased. As detailed on my other post this was due to excesive resources allocated by all the new features for this release. It is documented at the metalink note 371983.1.

Instance parameters compatible and log_archive_format
Once the database was upgraded, according to the install guide, the compatible parameter remained at 9.2.0.0.0, once the upgrade process was stabilized the parameter changed to 10.2.0.1.0, this made instance parameter such as log_archive_format to have a mandatory different format: it must contain the archive format mask %s %r %t.

Zayle Letzel Trojan

2007-11-04T09:02:00.000-06:00

"Todo por ti Letzel"

What's the Zayle Trojan
The Zayle or Letzel is a trojan virus that attacks the USB devices, such as regular pen drives, USB storages, digital cameras, ipods, mp3 players, etc. This can be recognized when you see the hidden system file autorun.inf along an executable named crsvc.exe (Zarteck), this prevents the pen drive from being safely removed, and it may prevent the device from being formatted.

The autorun.inf file can be opened with notepad and a couple of text images can be seen (as shown below).

Zayle could be a rearrange of the name Elyza, and the message posted by the hacker reads "TODO POR TI LETZEL", which means "All for you Letzel". This could be dedicated either to the hacker's girlfriend or this could be dedicated to the architect Jan Letzel, who created the Hiroshima Peace Memorial, just mere speculation.

HowTo get rid of Zayle

Disable the system restore feature. Go to the Control Panel -> System -> System Restore And disable the Restore system by clicking on the Deactivate System Restore on all units check box. Accept it and close this dialog window.
From the Task Manager find a process named crsvc.exe and kill it.
Disable the 'hide protected Operating system files' option from the folder options. Go to Tools -> folder options -> See -> Hide Protected operating system files and deselect this option, so the hidden system files appear listed on the explorer.
From the explorer, do not open the pendrive with double click, this will re-enable the trojan. Explore it instead by right clicking on the drive letter icon.
Delete the autorun.inf and the crsv.exe files.
Open the regedit, Start menu -> run (or click [Win]+r then type regedit on the dialog box and accept. On the regedit explorer look for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run, and there you will find an entry named syslog that calls the crsvc.exe file, remove this entry and restart your computer.
The infection is gone.

Zayle's Signature
Autorun signature left by this trojan at the autorun.inf file when it infects the pendrive (very creative and romantic):

[autorun]
open=crsvc.exe
shell\1=ZAYLE
shell\1\Command=crsvc.exe
shellexecute=crsvc.exe

TODO POR TI LETZEL (All for you Letzel)
[A heart text image]

[A rose text image]

Enterprise Manager Troubleshooting

2007-10-27T01:01:00.000-05:00

Most frequently seen problems with EM console have to do with non properly configured networking environments and skipping the dhcp warning at the installation phase. Proper networking configuration prerequisites have to be met before proceeding with installation.

1. DHCP issues.
Assigning a dynamic IP address makes the EM console to fail. Since the repository is configured with the environment it collects at install time, if a dynamic address is assigned, next time the computer unplugs (most frequently seen on laptop computers) EM stops working.

2. Use of IE with Enhanced Security Option enabled.
This is another issue frequently seen, when IE Enhanced Security option is enabled EM stops working, even though the console can be started.

3. Enterprise Manager can be accessed but Performance and maintenance tab request again for login credentials
This is another issue due to improperly configuring the networking and host name resolution environment. Oracle reads the hosts file to establish both the IP Address and the fully qualified host name. If it fails to properly read the hosts file, or if this doesn't properly identify the host, then the localhost.localdomain will be taken to configure the EM Console. When Attempting to solve the tns entry to access the performance tab or the maintenance tab Oracle won't be able to identify the host declared at the tns entry and it will fail to access, even though the provided credentials and the tns entry seem to be well configured.

4. Changing hostname or fixed IP Address
When networking changes happen then an EM reconfiguration is required.

For further references on HowTo troubleshoot and reconfigure Enterprise Manager I suggest to refer to this link --> Enterprise Manager DB Control Console.

Who's Who at Oracle Forums

2007-10-25T15:19:00.001-05:00

The more time I spend on the Oracle forums the more people I get to know, even though I have never physically seen most of them, only by the kind of regular answers I have been able to 'see' Who's who.

Sybrand Bakker AKA sybrandb
It's funny to see people on the forums who are self qualified as 'Senior Oracle DBA', and when trying to google them on the web the only kind of references obtained so far are at an extreme poor, simple and moronic (thanks Howard for this vocabulary addendum), no white papers found, no meaningful forum participation, no semminars, no references, no history, no blog, no personal web page, no nothing but simple racist, aggressive and rude answers at the Oracle related forums.

I have always said that reading the manual is the first source of Oracle information, but for heavens sake!, RTFM is not a 'Senior DBA Level Answer' I would like to hear, and guess what, just issue a search at the Oracle forum for the RTFM string, and you'll see who is one of the posters who most frequently provides this as an answer.

I though this gentleman was participating at the Oracle forums only, but when googling for 'Sybrand Bakker' I have found several other interesting references on the web with the same signature and the same 'Seior Oracle DBA' Level Answers. Just a xenofobic who shows a lot of aggressivity, lack of manners and lack of knowledge. I couldn't even say if this fellow poster has the OCA and I have not been able to find someone who makes a good reference for Mr. Bakker.

I still cannot understand why someone who definitely hates to write for free and whose contributions are less than binary rubbish, wastes his time writing for nothing but to build up a very ugly reputation.

My roots

2007-10-07T02:46:00.001-05:00

Madrid surname was first found in Old Castile in the heart of Spain, where the name originated in Visigothic times. It means one who came from Madrid, the capital of Spain. When Jews converted to Christianity in Spain in the 15th century, whether voluntarily or by force, they often took a last name based on their town or city or origin.

Some of the first settlers of this family name or some of its variants were: Among the early explorers of the New World were Juan De Madrid, who came to America in 1510; Gonzalo De Madrid, who came to the Dominican Repulic in 1560.

I was born in Mexico City, my roots are both, my family came from Hidalgo, where immigrants from England and Spain settled attracted by the gold and silver mines at Real del Monte, where in 1830 the Cornish first installed the steam mining machinery. One of the family branches later became farmers and settled in Tulancingo, where my mom was born. On my father's side, his family came from Huauchinango to Tulancingo, and there was where my parents met, however they didn't get married there. They lost contact and it was several years later when they met again in Mexico City, where finally I was born.

Is it possible to decrypt the Oracle password?

2007-10-01T10:50:00.001-05:00

Oracle's hashed password algorithm, even though is supposed to be secret, it has been shown to be weak due to several vulnerabilities.
Weak Salt SelectionNon-random salt values.Lack of alphabetic case preservation.

On 1993 there was a post on the comp.databases.oracle newsgroup which describes the algorithm in detail, identifying an unknown fixed key as an input parameter. The key was later published on a book named "Special Ops". This has provided enough information to reproduce the algorithm:

1. Concatenate the username and the password to produce a plain text string;

2. Convert the plain text string to uppercase characters;

3. Convert the plain text string to multi-byte storage format; ASCII characters have the high byte set to 0x00;

4. Encrypt the plain text string (padded with 0s if necessary to the next even block length) using the DES algorithm in cipher block chaining (CBC) mode with a fixed key value of0x0123456789ABCDEF;

5. Encrypt the plain text string again with DES-CBC, but using the last block of the output of the previous step (ignoring parity bits) as the encryption key. The last block of the output is converted into a printable string to produce the password hash value.

Let's suppose a user's password is about 12-16 characters, then the algorithm would perform between 6 and 8 DES encryptions to compute the hash value.

The hash algorithm has another weaknesses, it can be calculated out from rainbow tables, which are precomputed hashed passwords which can be used to compare the actual hash password value later.

Ref. An Assessment of the Oracle Password Hashing Algorithm
Carlos CidInformation Security GroupRoyal Holloway, University of Londoncarlos.cid@rhul.ac.uk
Joshua WrightSANS Institutejwright@sans.org
18. Oct. 2005

HowTo Reset SYSMAN's password

2007-09-12T12:42:00.001-05:00

Resetting SYSMAN's password Oracle 10gR2

1. Set environment variables
set ORACLE_HOME=yourOracleHome
set ORACLE_SID=yourSID
set PATH=$ORACLE_HOME/bin:$PATH (assuming unix OS)

2. Shutdown EM and ensure your console is completely shutdown.
emctl stop dbconsole
emctl status dbconsole

3. From a SQL plus prompt modify sysman's password
SQL> alter user SYSMAN identified by yourNewPassword;

4. Verify you can open a sqlplus session with the sysman user using the reseted password.
sqlplus sysman/yourNewPassword
SQL>

5. Reconfigure password
a. Change directory to ORACLE_HOME/hostname_sid/sysman/config
b. Backup file emoms.properties
c. Edit the file emoms.properties
d. Look for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwd=
Replace the encrypted value by the new password value
e. Look for the line:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
Replace TRUE by FALSE

6. Start the EM service
emctl start dbconsole

7. Verify the above written password has been changed to an encrypted version in the emoms.properties file.

8. Your EM DB Control should be up and running by now.

Oracle Enterprise Linux vs. Red Hat Enterprise Linux

2007-09-05T14:44:00.001-05:00

Market Share War
After the Oracle's announcement to release OEL, Redhat suffered a 700 million dollars loss of profit, as its shares felt 24%, which can be interpreted in sever al ways as market analyst have stated.

Oracle may be looking to buy it and it is expecting to lower the market share price, so the strategy could be to strangle Red Hat until it has no more oxygen to breath, as since the announcement, the share fell,having the investors stampede in october 2006 when more than 113 million shares had changed hands, compared to the daily average of 6 million. The share reached its bottom price of $15.71 usd. Today it closed at $19.81 usd per share [Ref. 1] but this cannot be compared with the $24.71 usd it reached in the past.

The Strategy
With the announcement, Oracle gets to steal the leading product directly from the leading Linux vendor and package it as part of the application stack they sell. And yes many sites want a single support point for the whole thing.

Edward Screven, Oracle's CTO, said: "We think it's important not to fragment the market. We will maintain compatibility with Red Hat Linux. Every time Red Hat distributes a new version we will resynchronize with their code. All we add are bug fixes, which are immediately available to Red Hat and the rest of the community. We have years of Linux engineering experience. Several Oracle employees are Linux mainline maintainers." [Ref. 2]

Why Oracle isn't the clone of another distro, or even more, why Oracle didn't create its own Oracle distro from scratch when it has the money and the people?

This introduces another message between lines. Oracle will be a high fidelity Red Hat clone because Oracle is hunting Red Hat, it is easier to eat it if it already fits inside.

Why it is better
As Larry Ellison stated on the Open World announcement, he cited a range of problems with current Linux support:
- True Enterprise Support Unavailable from Linux Vendors
- Support from Leading Linux Vendors is Expensive
- No intellectual Property Indemnification from Linux Vendors

Oracle thinks it can do better on service levels:
- Dedicated linux development, test, and delivery teams
- Backed by world's largest software support organization
- Ready 24x7
- 145 countries and 27 languages [Ref. 3]

So, where does this leave us? It will be interesting to see how Red Hat responds. Red Hat is still the premium provider of Linux, that's true so this complicates things, but doesn't significantly change them.

Conclusion
I don't think the day when Oracle will announce the end of support for RHEL will ever come, I think Oracle will do another Block buster buy with Red hat, and it will wipe off the name of red hat and replace it by Oracle Enterprise Linux, alias the Unbreakable Linux.

infrom.exe on my USB

2007-07-09T11:26:00.001-05:00

infrom.exe

I have found the infrom.exe on my USB. This virus has spreaded through my USB memories and my digital camera.

This virus could easily be deleted with updated virus scanners, and it is sometimes reported to be adware, or virus.

Troj/ShipUp-A is a Trojan for the Windows platform. When first run Troj/ShipUp-A copies itself to \ccPrxy.exe.

The following registry entry is created to run ccPrxy.exe on startup:
HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Run\ccPrxy.exe - ccPrxy.exe

The following registry entry is set:

HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer
NoDriveTypeAutoRun
9f

Registry entries are created under:
HKLM\SOFTWARE\Microsoft\ShipUp\

The form of infections can be seen as a list of hidden files.

09/04/2004 05:58 AM 21,504 infrom.exe
07/08/2007 10:33 PM 96 AUTORUN.INF
07/08/2007 10:33 PM ms.config
07/08/2007 10:33 PM rm

H:\>dir /s/a ms.config
Directorio de H:\ms.config
07/08/2007 10:33 PM 23,552 ldup.exe
1 archivos 23,552 bytes

H:\>dir /s/a rm
Directorio de H:\rm
07/08/2007 10:33 PM 25,088 sy.exe
1 archivos 25,088 bytes

C:\WINDOWS\ldjs.txt is a logfile that shows all the infections carried out so far.

Sample of contents
2007-05-27 07:43:26
H:\ Space:256M,FreeSpace:9M
Copy File ldup.exe OK!
Copy File sy.exe OK!

file C:\Windows\ldlist.txt (hidden)
ms.config\ldup.exe
rm\sy.exe

There it can be seen the file names and the infection timestamp.

The contents of the AUTORUN.INF file is:
[AutoRun]
open=infrom.exe
shellexecute=infrom.exe
shell\Auto\command=infrom.exe
shell=Auto

Clean Up

In order to get rid of it first, those files have to be deleted from the media. This is just to avoid this to keep on spreading. Next, the root of infections,

delete the entry form HKLM\software\windows\system32\ccprx.exe This name could change, as from other blogs I have seen it registered with different names.
Delete c:\winodws\infrom.dat, c:\windows\ldjs.txt, c:\windows\ldlist.txt, c:\windows\c:\windows\ldup.exe and c:\windows\sy.exe, all of them hidden files.
From the Windows task Manager locate the ccprxy.exe and kill it
Remove the HKLM\Software\Microsoft\Windows\Run\ccprxy.exe entry
attrib -h -s C:\Windows\System32\ccprxy.exe
Remove the C:\Windows\System32\ccprxy.exe file
Reboot the computer.

I:\>rd /s/q ms.config
I:\>rd /s/q rm
I:\>attrib -h -s infrom.exe
I:\>attrib -h -s AUTORUN.INF
I:\>del infrom.exe

About me

2006-08-06T08:55:00.000-05:00

Héctor Rivera Madrid

Oracle Certified Master
OCP DBA since 7.3