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

ORA-07445: exception encountered: core dump [ptmak()+191] [SIGSEGV]

Usually this problem is related with debug scructures.Today one of our developers was complaining with ora-031114: Not Connected to Oracle and when i checked the alert log i saw ORA-07445: exception encountered: core dump [ptmak()+191] [SIGSEGV] in alert log and this problem can occur if last time debug mode was on and compiled package before upgrading to database and retry to compile the same package after upgrading.What you should do is to check if package has debug flag or not.if it has debug flag then just disable it.Good luck.

Installation Oracle OS Watcher and User's Guide

if your java version below 1.4.2 then you need to download latest java version and install it.

download latest java from the below link;

latest java

installation java on redhat

Step 1: installing rpm;

[oracle@isupdbsh opt]$  rpm -ivh jdk-7u9-linux-x64.rpm


Step 2: installation and configuration java

[oracle@isupdbsh /]$ alternatives  --install     /usr/bin/java   java /usr/java/jdk1.7.0_09/jre/bin/java 1


[oracle@isupdbsh /]$ alternatives --config java

There are 2 programs which provide 'java'.

  Selection    Command
-----------------------------------------------
*  1           /usr/lib/jvm/jre-1.4.2-gcj/bin/java
 + 2           /usr/java/jdk1.7.0_09/jre/bin/java

Enter to keep the current selection[+], or type selection number: 2

select the new location installed java and check java version;

Step 3: Checking java version;

[oracle@isupdbsh /]$ java -version
java version "1.7.0_09"
Java(TM) SE Runtime Environment (build 1.7.0_09-b05)
Java HotSpot(TM) 64-Bit Server VM (build 23.5-b02, mixed mode)

how to get column names,DATA_TYPE,DATA_LENGTH of any table....

select a.table_name,U.OWNER, column_name,DATA_TYPE,DATA_LENGTH from user_tab_columns a,ALL_CATALOG u
where a.TABLE_NAME=u.TABLE_NAME
AND u.table_name='table_name'
and column_name like upper('%column_name%')
and u.owner='owner'
order by DATA_LENGTH desc;