ORA-16792 configuration property value is inconsistent with database setting

As per the reference this error literally means:

Cause: The values of one or more configuration properties were inconsistent with database in-memory settings or server parameter file settings. This may happen by altering initialization parameters directly instead of altering property values using Data Guard broker.

Actions: Query property the InconsistentProperties on the database or check the Data Guard broker log to find which properties are set inconsistently. Reset these properties to make them consistent with the database settings. Alternatively, enable the database or the entire configuration to allow the configuration property settings to be propagated to to the initialization parameters.

When the dataguard broker was queried about the status, it showed the following results:

DGMGRL> show configuration verbose

Configuration - OMCDG1120

  Protection Mode: MaxPerformance
  Databases:
    OMCPRO - Primary database
    OMCADG - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with database setting

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: DISABLED

Configuration Status:
WARNING


The parameter involved in this particular case was  db_file_name_convert, the paths defined at the spfile were different from those known to the Dataguard Broker. db_file_name_convert is a static parameter which means the standby database had to be restarted so the parameter was effective.  The actual actions that took place to solve this issue were to set the parameter at the database level and modify the value at the dgmgrl prompt.

Step 1. Modify the instance parameter.

alter system set db_file_name_convert='C:\Oracle11g\Oradata\OMCPro', 'J:\Oracle11g\Oradata\OMCDG', 'D:\Oracle\Oradata\OMCPro', 'D:\Oracle\Oradata\OMCDG' scope=spfile;

Step 2. Modify the parameter at the DGMgrl prompt

C:\> dgmgrl

DGMGRL> edit DATABASE "OMCADG" SET PROPERTY DbFileNameConvert = 'C:\Oracle11g\Oradata\OMCPro, J:\Oracle11g\Oradata\OMCDG, D:\Oracle\Oradata\OMCPro, D:\Oracle\Oradata\OMCDG' ;

Property "dbfilenameconvert" updated

Once the parameter is consistent both at the spfile and the Dataguard broker it updates its status to SUCCESS.

DGMGRL> SHOW CONFIGURATION VERBOSE;

Configuration - OMCDG1120

  Protection Mode: MaxPerformance
  Databases:
    OMCPRO - Primary database
    OMCADG - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Flash Player 11.3 kept crashing with Firefox 13.01

The browser I use the most is Firefox, a personal reason that started back at the time when the way to avoid internet worms from infecting the computer was to either endlessly keep on updating the I.E. or by using low maintenance reliable browser.  Ever since Firefox became my preferred choice.

Recently something weird happened, one of those auto updates that bring down the system and ask for restart of applications, and suddenly  my Firefox stopped displaying the Enterprise Manager performance graphs. I had to temporarily switch to Internet Explorer while I was able to find a solution for it.  The funny thing is that it only replied with a kind of progress bar that read RSL Error 1 of 3 or RSL Error 2 of 3 and a small caps error message at the bottom of the display where the graph was supposed to be shown that read Error #2032.  Nothing else ... so after a long Google search that lasted the whole morning, digging for a clue on what was going on  I finally came up with a diagnostics and a possible solution.

Every time I tried to load a page with flash player contents it crashed, and in the case of YouTube it just mentioned the contents couldn't be played at this time and it asked to retry later (some sort of insanity, it doesn't matter how many times I'd have tried nor how later I'd retried the result would have been always the same). So none of the contents that required the flash player plugin could be played.

The solution was found in this Firefox support article "Flash 11.3 doesn't load video in Firefox"
I had installed the Real Player and the feature to download and save flash contents was making the Flash 11.3 plugin to crash.  The solution, just as it was described in the above article was to start the RealPlayer and from the main menu / Preferences / Download & Recording submenu, I unchecked the "Enable web download & recording for these installed browsers" option.  I restarted Firefox and everything came back to normal.  Since this is my work machine, I'm not too much worried about not being able to download my favorite videos by means of the Realplayer plugin.  I'm much more concerned about being able to display my Performance graphs and accessing the flash version of My Oracle Support.

ORA-00600 [kgiinb_invalid_obj]

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]

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

Recently an error was reported to me ...

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

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



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

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

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

The Oracle Open World, Day 1 - Key Conference I

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

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

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

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

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

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

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

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

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

The Oracle Open World, Day 0

Oracle Open World Day 0. Arrival to SFO.

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

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

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

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

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

Display a formatted time difference

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

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

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

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

define DateDay = 13084.4081

Then the query

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


and you've got your formatted output.

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

Moving a datafile Online

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

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

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

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

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

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

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

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

The listener supports no services

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

Environment:

• Windows 2003, x86
• Oracle 11.1.0.7.0

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

Starting tnslsnr: please wait...

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


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

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

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

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

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

ipconfig output:

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

Ethernet adapter Loopback Adapter:

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

Ethernet adapter Local Area Connection:

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

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

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