2011-11-27

expdp slow after upgrade from 11.1.0.6 to 11.1.0.7

DataPump Export (EXPDP) Runs Very Slow After Upgrade From 11.1.0.6 to 11.1.0.7 (Doc ID 1075468.1)

2011-11-25

ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] [Address not mapped to object]

This error can occur , when you migrated from 32bit to 64 bit :
The solution can be found in
DB CONVERSION: 32 bit -->64 Bit Broke OLAP OPTION [ID 386990.1]

2011-11-23

how to send attachment from linux coomand line

A very good article : http://www.cyberciti.biz/tips/sending-mail-with-attachment.html

You must have mutt installed
Use mutt command as follows to send an email with attachment:
$ mutt -s "Test mail" -a /tmp/file.tar.gz vivek@nixcraft.co.in < /tmp/mailmessage.txt
Where,
  • vivek@nixcraft.co.in - is the recipient
  • /tmp/mailmessage.txt - is the main body of the e-mail (read message from the file "mailmessage.txt")
  • /tmp/file.tar.gz - is an attachment (with option -a)
  • "Test mail" - is a subject line (option -s)

Another way is to use uuencode and mail ... but uuencode takes more time .

2011-11-22

ORA-00600: internal error code, arguments: [ddfnetCFull-4], [Invalid Handle], [], [], [], [], [], [], [], [], [], []

There are bugs related to ORA-600 ddfnetCFull  in 11.2.0.2 release

Bug 12977043: ORA-600 [DDFNETCFULL-4] WHEN USING A SHARED PUBLIC DBLINK

WORKAROUND:
-----------
Use PUBLIC database link instead of SHARED PUBLIC database link

INS-35351

P: [INS-35351] Inconsistant Versions Detected.

S: Verify active version of clusterware (output of "$GRID_HOME/bin/crsctl query crs activeversion") is equal or higher than the database software that's being installed.
bug 12311806

2011-11-21

Typ=2 Len=1: 192

the following note/bug is related to http://oradbastuff.blogspot.com/2011/11/ora-07445-intelnewmemcpy382-sigill.html 


Batch Insert Of Data Sometimes Leaves "~" (Tilde) In A Number Field Using The -d64 Parameter In The Command Line [ID 1134992.1]

2011-11-18

how to copy statistics between partitions

When dealing with partitioned tables the Optimizer relies on both the statistics for the entire table (global statistics) as well as the statistics for the individual partitions (partition statistics) to select a good execution plan for a SQL statement. If the query needs to access only a single partition, the Optimizer uses only the statistics of the accessed partition. If the query access more than one partition, it uses a combination of global and partition statistics.
It is very common with range partitioned tables to have a new partition added to an existing table, and rows inserted into just that partition. If end-users start to query the newly inserted data before statistics have been gathered, it is possible to get a suboptimal execution plan due to stale statistics. One of the most common cases occurs when the value supplied in a where clause predicate is outside the domain of values represented by the [minimum, maximum] column statistics. This is known as an „out-of-range‟ error. In this case, the Optimizer prorates the selectivity based on the distance between the predicate value, and the maximum value (assuming the value is higher than the max), that is, the farther the value is from the maximum or minimum value, the lower the selectivity will be.
The "Out of Range" condition can be prevented by using the DBMS_STATS.COPY_TABLE_STATS procedure (available from Oracle Database 10.2.0.4 onwards). This procedure copies the statistics of a representative source [sub] partition to the newly created and empty destination [sub] partition. It also copies the statistics of the dependent objects: columns, local (partitioned) indexes, etc. The minimum and maximum values of the partitioning column are adjusted as follows;


It can also scale the statistics (such as the number of blocks, or number of rows) based on the given scale_factor. The following command copies the statistics from SALES_Q3_2011 range partition to the SALES_Q4_2011 partition of the SALES table and scales the basic statistics by a factor of 2.
BEGIN
DBMS_STATS.COPY_TABLE_STATS('SH','SALES','SALES_Q3_2002','SALES_Q4_2002', 2);
END;
/

how to compare statistics

One of the key reasons an execution plan can differ from one system to another is because the Optimizer statistics on each system are different, for example when data on a test system is not 100% in sync with real production system. To identify differences in statistics, the DBMS_STATS.DIFF_TABLE_STATS_* functions can be used to compare statistics for a table from two different sources.

In the example below, we compare the current dictionary statistics for the EMP table with the statistics for EMP in the statistics table TAB1; the SQL statement will generate a report .

SELECT report, maxdiffpct
FROM table(DBMS_STATS.DIFF_TABLE_STATS_IN_STATTAB(‘SCOTT’,’EMP’,’TAB1’ ));

2011-11-17

ORA-07445 [__INTEL_NEW_MEMCPY()+382] [SIGILL]

an ugly oracle bug which apeears in 11.2.0.2 (with or without PSU ).
see also http://marist89.blogspot.com/2011/09/interesting-error.html

The original cause may be  here : http://oradbastuff.blogspot.com/2011/06/bug-of-jdbc-102-with-jdk-15.html 

intel_new_memcpy ( __INTEL_NEW_MEMCPY ) problems are related to bad data

ORA-04030: (Kxs-Heap-W,Qesagetnewbuf:Buffer) With Invalid Numeric Data [ID 1321682.1]

2011-11-16

oracle statistics

http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1354477.pdf

Oracle Database 11g introduced a new sampling algorithm that is hash based and provides deterministic statistics. This new approach has the accuracy close to a 100% sample but with the cost of, at most, a 10% sample. The new algorithm is used when ESTIMATE_PERCENT is set to AUTO_SAMPLE_SIZE (the default)

It is highly recommended that from Oracle Database 11g onward you let ESTIMATE_PRECENT default.

The METHOD_OPT parameter controls the creation of histograms during statistics collection. Histograms are a special type of column statistic created when the data in a table column has a non-uniform distribution.

A unique column will not have a histogram created on it if it is only seen in equality predicates.


how to clear caches in linux

http://www.linuxinsight.com/proc_sys_vm_drop_caches.html
To free pagecache, dentries and inodes:
  • echo 3 > /proc/sys/vm/drop_caches
As this is a non-destructive operation, and dirty objects are not freeable, the user should run "sync" first in order to make sure all cached objects are freed.

2011-11-14

_ksmg_granule_size

'startup migrate' failed with ORA-64 while upgrading to 10.2.0.2 with DBUA [ID 386855.1]

2011-11-12

oracle kernel parameters in suse

How to permanently set kernel parameters on Linux [ID 242529.1]

qmkmgetConfig

ORA-07445 [qmkmgetConfig()+52] During Catupgrd.sql (11.2.0.1) [ID 1127179.1]

2011-11-11

ora.cvu

http://oraclehandson.wordpress.com/2010/11/01/whats-new-in-oracle-11-2-0-2/

New resource ora.cvu (Cluster Verification Utility) is added in 11.2.0.2
This resource invokes cluster verification utility (I guess it is executed after every 6 hours) and store it’s output in $GRID_HOME/log/nodename/cvu/cvulog/cvu20101101111536.log
Detail trace of this utility is also saved under  $GRID_HOME/log/nodename/cvu/cvutrc
-rw-r–r–   1 oragrid  oinstall 5720101 Nov  1 11:16 cvutrace.log.0
-rw-r–r–   1 oragrid  oinstall 26213985 Nov  1 05:15 cvutrace.log.3

segfault error 6 in exectask after upgrade to 11G

see also : http://juliandyke.wordpress.com/2010/10/25/investigating-cvu-failures/
exectask -getfileinfo Dumps Core When Executing cluvfy [ID 1359659.1]

CLSR-0002

Srvctl Start Service' Fails With PRKP-1030, CRS-0215 And ORA-1003, CLSR-0002 In Database Imon Logs [ID 818997.1]

installation / relink of oracle binaries is slow on NETAPP NFS mount

Relink of binaries on NetApp NFS filesystems is slow [ID 602858.1]

X_$KGLLK and X_$KSLEI after upgrade to 11G

Invalid X_$ Views & Synonyms After Upgrading to 11g [ID 878623.1]

2011-11-10

rollback segment needs recovery

In case there are some problems ( incomple or unfininshed recovery, media error ) , youo probably have a rollback segment that is in the state  needs recovery .

In this case, the best way is to follow this link : http://www.runningoracle.com/product_info.php?products_id=283  .

Put the following event
event="10015 trace name context forever, level 10"
 Find the rollback segment that is corrupted ( eg _SYSSMU6$ )  and put it offline in the init parameter file.
_offline_rollback_segments=_SYSSMU6$
 
You can also see if there is an active transaction in your database (see note 1291009.1)
 
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
              KTUXESTA Status,
              KTUXECFL Flags
               FROM x$ktuxe
     WHERE ktuxesta!='INACTIVE' ;

do not put redo logs on SSD disks

Oracle’s redo log is a frequent source of intensive IO and, by design, Oracle sessions will normally wait for
redo log IO to complete. However, the nature of redo log IO operations is essentially optimal for magnetic
disk but least suitable for solid state disk. Redo log IO consists almost entirely of sequential write operations,
which are the worst case for solid state disk and the best case for magnetic spinning disk. Placing
redo logs on solid state disk is generally not recommended

When IO bottlenecks are limited to specific segments (tables, partitions, or indexes) then relocating those
segments to solid state disk will almost always be effective in optimizing IO to those segments, regardless of
the type of IO to which the segments are subjected

from guy harrison  blog : http://guyharrison.squarespace.com/blog/2011/10/27/using-flash-disk-for-redo-on-exadata.html

you don't need RAC

a very good white paper http://www.my-idconcept.de/downloads/You_Probably_Dont_Need_RAC.pdf

One way of looking at availability is this: If you have a standalone Unix box it will
usually give you 99.9% availability over a year (some say 99.5, some say 99.9). It just runs. And so does Oracle usually. If you have a two-node Unix cluster the availability over a year drops to 98%.

If you need to cover your behind politically in your organisation, you can choose to buy clusters, Oracle, RAC and what have you, and then you can safely say: “We’ve bought the most expensive equipment known to man. It cannot possibly be our fault if something goes wrong or the system goes down”.
Otherwise, you probably don’t need RAC. Alternatives will usually be cheaper , easier to manage and quite sufficient.

directio and NFS

Potential Lost Writes: RAC on NAS using NFS may not use DIRECTIO or HP on NFS [ID 566545.1]

Symptoms
  • When RAC is used, O_DIRECT is the default option to open files in NFS.  However, there are some cases where O_DIRECT is not used and the database may lose a write.  For this case the NFS mount point options are in accordance with Note 359515.1
  • There is another case that is specific to HP when NFS is used without the forcedirectio IO option where the database may end up losing IO.
  •  Lost IO may cause different errors like:
ORA-600 [4193]
ORA-600 [4194]
ORA-600 [4137]
ORA-600 [3020] in a media recovery
ORA-600 [kccchb_3]
ORA-600 [kdsgrp1]
ORA-600 [qertbFetchByRowID]
ORA-600 [kcbz_check_objd_typ_3]
ORA-1499 on "analyze table validate structure cascade"
ORA-8102 on delete/update
  • Some of these errors are signs of logical corruptions on undo segments, redo log files and inconsistencies between tables and indexes.

Cause

  • There are some cases where O_DIRECT is not used in RAC environment due to unpublished bug 5856342.  Without this fix in a RAC environment using NFS the database may lose a write unless DIRECTIO is explicitly specified.
@ See Bug 7006179

  • There is another case that is HP specific where Oracle does not check for forcedirectio in the mount options due to Bug 6922729.  See Note 6922729.8

Solution

  • For Bug 5856342 in order to prevent this issue install an Oracle RDBMS version greater than 10.2.0.3 or specify DIRECTIO in the database parameter file like:
filesystemio_options=DIRECTIO or SETALL
  • For Bug 6922729 (HP specific) install an one off patch for it or specify forcedirectio in the mount options for Oracle Files.  See Note 6922729.8



2011-11-08

List of Patches Available for Oracle Warehouse Builder 11.2

List of Patches Available for OWB 11.2 [ID 1271208.1]

How To Connect With The Oracle Warehouse Builder 11.2.0.2 Client to an OWB 11.2.0.3 Repository

How To Connect With The OWB 11.2.0.2 Client to an OWB 11.2.0.3 Repository [ID 1361915.1]

how to increase stability for your Intel server

In case of HP Proliant G7 series server not used as virtualized server, there are some recommendations ,  in order to improve stability on your Intel x64 server :

Bios settings:
 intel virtualization technology   --> disabled
 intel hyperthreading options     ---> disabled
 intel turbo boost technology    ---> disabled
 intel vt-d                                 ----> disabled
 processor core disabled         --->  all cores enabled


2011-11-03

ktspNextL1

Update: you could disable the automated scheduled job AUTO_SPACE_ADVISOR_JOB
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."AUTO_SPACE_ADVISOR_PROG"',
force=>TRUE);
END;
/


ORA-600 [ktspNextL1:4] ORA-600 [ktspNextL1:4] from SYS.DBMS_SPACE/SYS.DBMS_ADVISOR/WRI$_ADV_OBJSPACE_TREND_DATA [ID 841158.1]

2011-11-02

error linking INS_EMDB.MK IN SLES 11

SLES 11: ERROR INVOKING TARGET 'COLLECTOR' OF MAKEFILE '$O_H/SYSMAN/LIB/INS_EMDB.MK' [ID 957982.1]

2011-11-01

Oracle NoSQL

http://dbmsmusings.blogspot.com/2011/10/overview-of-oracle-nosql-database.html

Like most NoSQL systems, the Oracle NoSQL database does not support joins. It only supports simple read, write, update, and delete operations on key-value pairs.