ALTER TABLE MOVE PARTITION failes with DBMS_INDEX_UTL ORA-01422

I have been contacted by one of our customers with quite interesting problem.

They have upgraded their oracle database to 11.2.0.3.1 and faced really non-typical problem:

ALTER TABLE OWN.TABLE_LOG MOVE PARTITION p_201202 tablespace LOG_ARCHIVE compress
UPDATE INDEXES (
   OWN.I_TABLE_LOG_SYSTEMDAY (partition p_201202 tablespace LOG_ARCHIVE)
 , OWN.I_TABLE_LOG_ID (partition p_201202 tablespace LOG_ARCHIVE)
 )
 ;

DBMS_INDEX_UTL: Error executing ALTER INDEX "OWN"."I_TABLE_LOG_SYSTEMDAY" REBUILD
 PARTITION "p_201202" NOPARALLEL
 DBMS_INDEX_UTL: Caused by ORA-01422: exact fetch returns more than requested number of rows
 DBMS_INDEX_UTL: Error executing ALTER INDEX "OWN"."I_TABLE_LOG_ID"
 REBUILD  PARTITION "p_201202" NOPARALLEL
 DBMS_INDEX_UTL: Caused by ORA-01422: exact fetch returns more than requested number of rows

Customer’s DBA was able to rebuild mentioned indexes by running ALTER INDEX command provided inside error message, so why they don’t work inside ALTER TABLE … MOVE PARTITION … UPDATE INDEXES ?

There are not many references to DBMS_INDEX_UTL on My Oracle Support site and this one is definitely not among them, so I have decided that it may be a new BUG or some kind of Dictionary Corruption

Continue reading

Investigation of the 11g Oracle*Net connection delay when DNS server is unreachable

Oleksandr Denysenko:

“I’d like to introduce new Contributor to this Blog – Robert Oliynichenko.

Hi will make post on topics that are related to main direction of this Blog.

So, here we have The First Post about Slow 11.2 SQL*Net connections on AIX

Problem description:

Oracle 11g utilities like tnsping, lsnrctl or sqlplus on AIX 6.1 hanged for about 2,5 minutes (150 seconds) before connection with alias to any destination when DNS server was unreachable. In the same conditions (host and configuration) 10g utilities connected without delay. Oracle*Net was configured for local naming method only – NAMES.DIRECTORY_PATH=(tnsnames) in the both 10g and 11g Oracle Home. The tnsnames.ora files where identical in the 10g and 11g Oracle Home. The aliases in the tnsnames.ora used host names instead of IP addresses in the connection descriptions; if a host name was substituted by IP address the delay disappeared for this alias. Nevertheless all the host names from tnsnames where described in the /etc/hosts and resolution order was hosts = local, bind in the netsvc.conf (analog of  the nsswitch.conf in other UNIXs). This configuration should guarantee local name resolution when DNS server was unreachable. What is the root reason of this delay in 11g Oracle*Net ? Continue reading

Intermittent Slow Oracle Net from Windows 7/Vista/2008 machines

Hi.

I have faces some interesting issue that worth a blog post.

After rebooting Windows 2003 Server with installed Oracle Database 9.2 some clients machines(with installed Windows 7) started to work very slowly with Oracle Database. Other clients, with installed Windows XP, worked as usual – fine. There no network hardware issues for Windows 7 machines – both machines were tested for the same network cable: Windows XP works fine, but Windows 7 is very slow…

Oracle sessions were tracing with 10046(level 12) event and it revealed that there are no issues with database – just network…

Listener was rebooted – didn’t helped…

System administrators noticed that Windows 2003 machine was claiming that it was not able to update root certificates, so they decided to provide Internet connection and update root certificates. Updates required server reboot and… vuala… after server rebooted everything was OK from every workstation.

Nobody was able to explain what was THE REASON, but EVERYBODY  WERE HAPPY.

Later, in two days, they required to reboot mentioned server once again, and after reboot Windows 7 workstation started working slowly…

I was there so it was time to see what is happening. Continue reading

Enabling SQL*Net Tracing with Oracle Instant Client

Just a quick note for myself about Enabling SQL*Net Tracing with Oracle Instant Client.
There are absolutely nothing special for Instant Client, except the note that clients starting with version 11.1 use Automatic Diagnostic Repository (ADR) for storing log and trace data, so for tracing clients up to 10.2 we use next parameters in SQLNET.ORA:

TRACE_LEVEL_CLIENT=(OFF | USER | ADMIN | SUPPORT)
TRACE_DIRECTORY_CLIENT=D:\ORACLE\PRODUCT ($ORACLE_HOME/network/trace | %ORACLE_HOME%\network\trace)
TRACE_FILE_CLIENT=client_trace.trc (sqlnet.trc)
TRACE_TIMESTAMP_CLIENT=(ON | true | off | false)
TRACE_UNIQUE_CLIENT=(ON | true | off | false)

TRACE_FILELEN_CLIENT=max size of the client trace files in kilobytes
TRACE_FILENO_CLIENT=1

Starting from Oracle version 11.1 there are two possible destinations for trace and log files: Automatic Diagnostic Repository (ADR) and non-ADR(old pre 11.1-style).
For ADR-destination we specify:

TRACE_LEVEL_CLIENT=(OFF | USER | ADMIN | SUPPORT)
DIAG_ADR_ENABLED=ON
ADR_BASE=D:\ORACLE\LOG

(default for ?nux:  ~/oradiag_username/clients/user_username/host*/trace

for windows %systemdrive%\Documents and Settings\%username%\oracle\oradiag_%username%\diag\clients\user_%username%\host*\trace)
TRACE_TIMESTAMP_CLIENT=(ON | true | off | false)

For non-ADR(old style – pre 11.1) destination we specify:

TRACE_LEVEL_CLIENT=(OFF | USER | ADMIN | SUPPORT)
DIAG_ADR_ENABLED=OFF
TRACE_DIRECTORY_CLIENT=D:\ORACLE\PRODUCT
TRACE_FILE_CLIENT=client_trace.trc (sqlnet.trc)
TRACE_UNIQUE_CLIENT=(ON | true | off | false)
TRACE_TIMESTAMP_CLIENT=(ON | true | off | false)

TRACE_FILELEN_CLIENT=max size of the client trace files in kilobytes
TRACE_FILENO_CLIENT=1

PS: for Oracle Instant Client preferred locations for SQLNET.ORA are:

  • current directory
  • location specified by TNS_ADMIN variable

References:

  • 454927.1 Using and Disabling the Automatic Diagnostic Repository (ADR) with Oracle Net for 11g
  • 219968.1 SQL*Net & Oracle Net Services – Tracing and Logging at a Glance
  • 834822.1 Oracle Net Diagnostics
  • 1076022.1 Examples of Troubleshooting Slow Oracle Net Connections