2011-07-27

how to reduce noise in your car

www.installdr.com/TechDocs/999503.pdf

how to see error messages in english

In order to see Oracle error messages in English, you must have in your registry, under HKEY_LOCAL_MACHINE... Software...Oracle  , for key NLS_LANG, the following value :  AMERICAN_AMERICA.WE8ISO8859P1

how to change hostname or domain for oracle application server

http://download.oracle.com/docs/cd/B14099_19/core.1012/b13995/host.htm#CIHJIBAG
Follow these steps for each middle-tier instance on your host. Be sure to complete the steps entirely for one middle-tier instance before you move on to the next.
  1. Log in to the host as the user that installed the middle-tier instance.
  2. Make sure your ORACLE_HOME environment variable is set to the middle-tier Oracle home. Do not use a trailing slash (UNIX) or backslash (Windows) when specifying the variable.
  3. On UNIX systems, set the LD_LIBRARY_PATH, LD_LIBRARY_PATH_64, LIB_PATH, or SHLIB_PATH environment variables to the proper values. The actual environment variables and values that you must set depend on the type of your UNIX operating system.
  4. Run the following commands in the middle-tier Oracle home:
    • On UNIX systems:
      cd ORACLE_HOME/chgip/scripts
      ./chgiphost.sh -mid
      
      
    • On Windows systems:
      cd ORACLE_HOME\chgip\scripts
      cmd /c chgiphost.bat -mid 
       
      Verify the logs 

2011-07-21

how to see how much cpu your oracle instance consumes

An excellent article: http://dboptimizer.com/2011/07/21/oracle-cpu-time/

Starting in 10g Oracle records both the CPU used by the instance as well as the load on the system in v$sysmetric. This is awesome as we can see how busy the system is and how much of the CPU Oracle is responsible for:
col metric_name for a25
col metric_unit for a25
select metric_name, value, metric_unit from v$sysmetric where metric_name like'%CPU%' where group_id=2;
 
 
with AASSTAT as (
           select
                 decode(n.wait_class,'User I/O','User I/O',
                                     'Commit','Commit',
                                     'Wait')                               CLASS,
                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS
           from  v$waitclassmetric  m,
                 v$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'Idle'
           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')
          union
             select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(value/100,3)                                     AAS
             from v$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
          union
            select 'CPU_OS'                                                CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS
            from
              ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS
             from v$active_session_history ash
             where SAMPLE_TIME > sysdate - (60/(24*60*60))
)
select
       ( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +
       CPU_ORA_CONSUMED +
        decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,
       decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,
       CPU_ORA_CONSUMED CPU_ORA,
       decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,
       COMMIT,
       READIO,
       WAIT
from (
select
       sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,
       sum(decode(CLASS,'CPU_ORA_DEMAND'  ,AAS,0)) CPU_ORA_DEMAND,
       sum(decode(CLASS,'CPU_OS'          ,AAS,0)) CPU_OS,
       sum(decode(CLASS,'Commit'          ,AAS,0)) COMMIT,
       sum(decode(CLASS,'User I/O'        ,AAS,0)) READIO,
       sum(decode(CLASS,'Wait'            ,AAS,0)) WAIT
from AASSTAT)
/
 

norocul se termina

http://tudorgalos.ro/2011/07/a-i-se-da-in-gura/

bind peeking in Oracle Siebel

Siebel Bind peeking performance degradation future enhancement [ID 1273535.1]

_optim_peek_user_binds

A very good article about bind peeking in Oracle Database 10G : http://www.pythian.com/news/867/stabilize-oracle-10gs-bind-peeking-behaviour-by-cutting-histograms/

exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

here are three things that might put you at risk of unstable plans due to bind variable peeking. Those are histograms, partitions, and range-based predicates.

histograms and bind variable peeking can cause an unstable plan.

In 10G, bind variable peeking happens only on a hard parse, which means that all following executions will use the same plan, regardless of the bind variable value.

It might be a good idea to wipe out all histograms in a database (gather your stats with FOR ALL COLUMNS SIZE 1 clause), and manually add them when you decide that you really need one.

 

2011-07-20

11.2.0.2.3

PSU 11.2.0.2.3 contains all fixes previously released in PSU 11.2.0.2.2 and the following new fixes:

2011-07-19

faster index creation

A good article : http://iggyfernandez.wordpress.com/2011/07/04/take-that-exadata-fast-index-creation-using-noparallel/

Leverage the Oracle cache! This is one case where “direct path reads” and parallelism hurt instead of helping. The trick is to use NOPARALLEL so that data is loaded into shared memory using “db file scattered reads”—instead of being loaded into private memory using direct path reads—and to run multiple CREATE INDEX statements simultaneously. In the words of David Aldridge, you’ll get “get 1 index creation process performing the physical reads and the other 9 (for example) processes greedily waiting for the blocks to load into the buffer.”

how to find the patch number for oracle database

Quick Reference to Patchset Patch Numbers [ID 753736.1]


how to install OFSA client

How to Install the latest OFSA 4.5.x Client Patch [ID 263144.1]

how to install OFSA 4.5.39 for AIX ( RQ Manager )

1. Download from metalink patch number 2902123 ( for AIX 32 bit OFSA_32_p2902123_450_AIX.zip ) and 4959183  (p4959183_450_AIX.zip this patch is required in order to run OFSA RQ manager woth Oracle Database 10G )

2. Create a folder ( ex : /home/oracle/ofsa )


3. Unzip the first patch(2902123) in the folder created before


4. Apply the second patch ( 4959183 )
Copy the p4959183_450_AIX.zip file to the folder
Type the following command to extract the files from patch:  unzip -o p4959183_450_AIX.zip

5. Set OFSA_HOME correctly  in .profile ( or .bash_profile )

6. Run .postinstall (we will find it in the folder created at step 2 )
When asked, provide the following information:
Remove tmp <y>
Do not use TPOL <n
7. Edit $OFSA_HOME/bin/rq and replace INSTALL_DIR with OFSA_HOME


8.   Modifiy $OFSA_HOME/etc/OFS.INI and set the corresponding values


9. Start RQ manager ( you should have already an entry in tnsnames.ora for the OFSA oracle database )



2011-07-18

how to see only the execution plan in oracle database

The easiest way :  in sqlplus type : set autot trace exp  and then the desired statement;

exadata x2-8 hardware specifications

The Exadata Database Machine X2-8 is a full rack system with 2 database servers  and 14 Exadata Storage Servers. Each  database server comes with 64 Intel CPU cores (8 x eight-core Intel® Xeon® X7560 processors) and 1 TB of memory

exadata x2-2 hardware specifications

how to expand your exadata

http://blog.oracle-ninja.com/2011/07/exadata-storage-expansion-rack/

Three versions of the Exadata Storage Expansion Rack are available. From the Full Rack configuration with 18 Exadata Storage Servers; to the Half Rack with 9 Exadata Storage Servers; to the Quarter Rack system with 4 Exadata Storage Servers; there is a configuration that fits any application.

All three versions of the expansion rack are delivered with the same 2 TB High Capacity SAS disks, and Exadata Smart Flash Cache, available in the Exadata Database Machine.

Exadata Storage Expansion Quarter Rack : 96 TB of raw disk data capacity

Ora-00245 and ORA-17500 after upgrade to 11.2

After Upgrade To 11.2.0.2 We Recieve Ora-00245 During Autobackup Of The Controlfile. [ID 1308378.1]

2011-07-15

ORA-21561 : OID generation failed

The error below may happen also during creation of a new database.
The problem is that /etc/hosts is not properly configured (usually  It does not contain an entry for the name of the server )

Connection Via 10.2.0.3 SQL*Net Fails With Error ORA-21561: OID Generation Failed [ID 559981.1]

aix packages requirements for installing oracle database

Required OS packages:
bos.adt.base
bos.adt.lib
bos.adt.libm
bos.perf.perfstat
bos.perf.libperfstat
bos.perf.proctools

ORA-01019

ORA-01019 Error Starting OFSA RQ on Oracle 10g/11g [ID 357464.1]

2011-07-11

semget failed, errno = 17, file ipcmutex.cpp, line 167

Mapping Rules Fail with User Requested Termination / Semget Failed Errors [ID 748700.1]

2011-07-08

rman with VTL deduplication

http://www.backupcentral.com/phpBB2/two-way-mirrors-of-external-mailing-lists-3/ibm-tsm-13/data-deduplication-60390/

According to Dilligent, when RMAN uses Multiplexing, it intermingles the
data from each RMAN so the data block will be different every time so the
blocks are different, similar to Multiplexing with Netbackup

1) Do the best you can to put like data together. (ie all Oracle
DB Backups go to the same de-dupe VirtualTape head (Repository),

2) Turn off all compression (Client and DB's)

3) Oracle Specific
Do not use RMAN's Multiplexing in RMAN will combine 4
Channels together and the backup data then will be unique every time thus
not allowing for de-duping)
Use the File Seq=1 (Then run multiple channels)

4) Do not Mix Windows and Unix data, It wont de-dupe well.

2011-07-05

2011-07-04

datapump and long columns

If you are on 10.2.0.4 or prior versions of 10g Release 2, tables with long columns may not propagate properly in datapump. Therefore, they have to be migrated separately using the traditional export/import utilities. The fix to this issue is part of 10.2.0.5.

ORA-00600: internal error code, arguments: [kgscLogOff-notempty]

ORA-600 [kgscLogOff-notempty] On Session Logoff [ID 413120.1]

a new feature of 11G: direct read in case of a table bigger than %2 of buffer cache

An excellent article : http://coskan.wordpress.com/2011/06/24/plan-is-same-wait-profile-is-different/

"From the ouput above, you can see PRD database is doing direct path reads and QA database is using db_file_scattered reads and PRD is 3 times slower for each run. Somehow oracle favours doing 11G direct path implementation for full table scan (Links for direct path Doug Burns,Alex Fatkulin, Dion Cho ) on PROD. If you read the links you can see that this has to do something with the memory the buffer cache and small table threshold (for more information about this threshold Charles Hooper-great discussion with Jonathan Lewis on comments, Tanel Poder )."

There have been changes in 11g in the heuristics to choose between direct path reads or reads through buffer cache for serial table scans.
In 10g, serial table scans for "large" tables used to go through cache (by default) which is not the case anymore in 11g (where this decision to read via direct path or through cache is based on various stats).

2011-07-03

ORA-00600: internal error code, arguments: [2037] ORA-07445: exception encountered: core dump [kcbs_dump_adv_state]



During Startup (Open Database) Alert Log Shows ORA-600[2037] and ORA-7445[kcbs_dump_adv_state] (Doc ID 551993.1)

2011-07-02

How_to_stop_gather_stats_after_index_rebuild

How to stop collecting index statistics during an index rebuild :

Oracle 10g, by default, collects statistics for an index during 
index creation.  It is done by design. 
The internal parameter "_optimizer_compute_index_stats", is set to
TRUE by default.



 alter session  set "_optimizer_compute_index_stats"=FALSE;
 
IN this case, the time for rebuilding the index is much lower : 
15% faster for an index rebuild