ASM file metadata operation (Non-Exadata)

    Today our developers were complaining about performance issue , and there were nothing special which could cause high cpu usage on server  but  there were many sessions with wait event "ASM file metadata operation" and "ksv master wait'"...after googling and digging the oracle support,  finally found doc id (1308282.1) related to problem with paramater "cell_offload_processing".. whydoes oracle use cell_offload_processing? 

     In Exadata Database Machine , Cell Offloading:The storage cells are saving the database node from processing some workload  to inside them..this process can be referred to as cell offloading...In a Non-Exadata Oracle database , when someone selects a single column in a row , entire block related to row will be writting from the disks to  buffer cache and then selected row will be extracted from this block..but Exadata database machine can pull desired rows from disks directly instead of writing entire block to buffer cache(this is also know as smart-scan)..Smart-scan can help to reduce I/O on database server.

  

     But in a Non-Exadata Oracle database, cell_offload_processing can be set as "FALSE" to solve this problem...

"alter system set cell_offload_processing = false;" 

This issue is also known as a bug and can be handled by applying Patch 11800170...

- Erol

Oracle Linux Desktop Gui Problem

You have to install these packages while installing oracle linux to have desktop gui...

"X Window System" , "Desktop General Purpose", "Desktop Graphical Administration Tools", "Legacy X Window System compatibility"

If u miss installing these packages.. u can install with yum

yum groupinstall "X Window System" Desktop "General Purpose Desktop"  "Graphical Administration Tools" "Legacy X Window System compatibility"
if u dont have internet u need to install rpms manually from installation cd

-Erol

Oracle Sample Rman Scripts

Rman full backup script;
Rman Level 1 Cumulative;

ORA-12991: column is referenced in a multi-column constraint (Related to GoldenGate)

After moving our 10g database to 11g with golden gate...our developers were complaining about ORA-12991: column is referenced in a multi-column constraint...This problem is related to enabling Supplemental Logging with "ALTER DATABASE ADD SUPPLEMENTAL LOG DATA" , when u try to drop a column...First of all u have to find what  log_group_name causes the problem...

SQL> select log_group_name from dba_log_groups where table_name = 'TABLE_NAME' ;

LOG_GROUP_NAME
============================
GGS_DROP_COLS_24242


Then drop it;


SQL> alter table table_name drop supplemental log group GGS_DROP_COLS_24242;

table altered.



- Erol

re-executing root.sh when installation fails

rootcrs.pl -deconfig -force

how to set NLS_DATA_FORMAT on linux before starting rman

NLS_DATE_FORMAT='DD-MON-YYYY:HH24:MI:SS'; export NLS_DATE_FORMAT

 rman target sys/password

Creating user with dbms_metadata.get_ddl

select dbms_metadata.get_ddl('USER',username) from dba_users where username in ('user_1',user_2',user_3','user_4')


user_1,user_2,user_3,user_4 should be defined by dba as real user names...

Oracle Linux 6.3 Installation Guide

Click Next to select language

Select which language u want to install

every 5 minutes how to copy files after certain date time (linux)

here the script  to copy  archive logs ,created after certain date time, into different location every 5 minutes for our production database; simply , writing the last time of archive log created by oracle on os into a log file and then read the time from this log file to find out which archive logs should be copied next time.create a sh file and copy paste this script into file and then add it into crontab ,if u have any question feel free to ask...


REF=.tmp.$$
export date_name=`cat last_time`
touch -t $(date +%m%d)$date_name $REF
ls -lra | tail -1 | awk '{print $8}' | sed 's/\://g' > last_time
find . -newer $REF -exec cp {} /opt/oracle / \;
rm -f $REF



before run this script create a file called last_time in archive logs destination folder and write the time after files u wanna copy.for example write 1900  in last_time file to copy files after 19:00..


adding crontab to execute sh every 5 mins;

crontab -e then add this line ;

*/5 * * * * /opt/name_of_file.sh

PRVF-5637 and PRVF-9802 installing oracle database on oracle linux or redhat 6.x

Solution for PRVF-5637
if u have more than one  node then perform these steps for each nodes;

# mv /usr/bin/nslookup /usr/bin/nslookup.orig
# echo '#!/bin/bash
/usr/bin/nslookup.orig $*
exit 0' > /usr/bin/nslookup
# chmod a+x nslookup

its well-known bug for OEL6 or RHEL6 while installing oracle 11.2.0.3.

 grid installer returning exit code of 1 which means as a problem;



* Mon May 07 2012 Adam Tkac <atkac redhat com> 32:9.8.2-0.9.rc1
- fix race condition in the resolver module
- nslookup: return non-zero exit code when fail to get answer (#816164)

simply returning nlslookup behaviors like OEL 5.8 will solve your problem.

  Good luck.

Solution for PRVF-9802

u need to download oracleasmlib from the below link and install it

oracleasmlib.rpm

after installing rpm u can see the disks with "/usr/sbin/oracleasm-discover"


[root@rac1 opt]# rpm -ivh oracleasmlib-2.0.4-1.el5.x86_64.rpm
warning: oracleasmlib-2.0.4-1.el5.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 1e5e0159: NOKEY
Preparing...                ########################################### [100%]
   1:oracleasmlib           ########################################### [100%]
[root@rac1 opt]#  /usr/sbin/oracleasm-discover
Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DISK1 [3078144 blocks (1576009728 bytes), maxio 512]

Also check permissions for disks, they must belong to grid user..


[root@rac1 opt]# cd /dev/oracleasm/disks/
[root@rac1 disks]# ll
total 0
brw-rw---- 1 grid oinstall 8, 1 May 15 14:57 DISK1

- Erol