2011-07-27
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.
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.
- Log in to the host as the user that installed the middle-tier instance.
- 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.
- 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.
- 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 -midVerify the logs
- On UNIX systems:
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:
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)
/
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.
It might be a good idea to wipe out all histograms in a database (gather your stats with
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.”
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 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 )
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 )
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
Do not use TPOL <n
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
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
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]
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
bos.adt.base
bos.adt.lib
bos.adt.libm
bos.perf.perfstat
bos.perf.libperfstat
bos.perf.proctools
2011-07-14
listener crashes
http://msutic.blogspot.com/2011/06/oracle-listener-crashes-with-core-dump.html
TNS Listener Crash with Core dump [ID 549932.1]
TNS Listener Crash with Core dump [ID 549932.1]
2011-07-12
WSOCKETS.DLL
Cannot find WSOCKETS.DLL Error in Risk Manager After New Client Installation [ID 1268578.1]
SQL16W95.dll in OFSA 4.5.39
General Protection Fault in Module SQL16W95.DLL Logging into OFSA 4.5 [ID 154664.1]
aix: how to find top swap consumers
In order to find the process which consumes swap : svmon -P -t 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.
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-06
how to change a 11.2.0.1 database to a 11.1.0.7 database
PARAMETERS TO CHANGE 11.2.0.1 TO 11.1.0.7 [ID 1096377.1]
how to change a 11.2.0.1 database to a 10.2.0.4 database
PARAMETERS TO CHANGE 11.2.0.1 TO 10.2.0.4 [ID 1274553.1]
uninstall of 11G agent change permissions
Deinstall 11.1.0.1 Agent change permissions of $HOME [ID 1330906.1]
2011-07-05
ORA-01009: missing mandatory parameter and Ora-16401
Ora-16401: Archivelog Rejected By Rfs [ID 1183143.1]
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).
"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]
|
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