SQLPLUS APPS Connection is intermittently failing

SQLPLUS APPS Connection is intermittently failing

Reason for this Issue: Invalid TNS address supplied or destination is not listening. This error can also occur because of underlying network transport problems.

For my case it was wrong gateway configuration.

Successful:

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>sqlplus apps/*************

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 29 15:42:35 2012

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Unsuccessful Attempts:

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>sqlplus apps/************

SQL*Plus: Release 8.0.6.0.0 - Production on Mon Oct 29 15:46:49 2012

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

ERROR:
ORA-12203: TNS:unable to connect to destination


Enter user-name: 

mkkebstestu1@mkktestserver1:/home/mkkebstestu1>netstat -rn
Routing tables
Destination        Gateway           Flags   Refs     Use  If   Exp  Groups

Route Tree for Protocol Family 2 (Internet):
default            172.30.0.1        UG        1     34949 en2      -      -   =>
default            172.30.0.10       UG        7    795361 en2      -      -
60.60.60.1         127.0.0.1         UGHS      0      1733 lo0      -      -   =>
60.60.60.1         60.60.60.1        UHSb      0      1733 en2      -      -   =>
60.60.60.1/24      60.60.60.1        U         0         0 en2      -      -
60.60.60.255       60.60.60.1        UHSb      0         4 en2      -      -
127/8              127.0.0.1         U        14    781913 lo0      -      -
172.30.0.0         172.30.0.92       UHSb      0         0 en2      -      -   =>
172.30/24          172.30.0.92       U         2   6576453 en2      -      -
172.30.0.92        127.0.0.1         UGHS      1       458 lo0      -      -
172.30.0.255       172.30.0.92       UHSb      0         4 en2      -      -

Route Tree for Protocol Family 24 (Internet v6):
::1                ::1               UH        0        92 lo0      -      -
mkkebstestu1@mkktestserver1:/home/mkkebstestu1>

>>>> If you notice the netstat spool there are two gatways are defined. This is not a correct configuration. Highlighted in RED.


Solution: Flush the Gateway from smitty

mkktestserver1[/] # smitty route

Press Enter AFTER making all desired changes.

                                                        [Entry Fields]
  Flush Routing Table in the Current Running System   yes                                                                          +
  Flush IPV4 Routes in the Configuration Data Base    yes                                                                          +
  Flush IPV6 Routes in the Configuration Data Base    no                                                                           +
   (effective in the next system restart)

ASM Related steps for 11gR2 Grid Infrastructure

ASM Related steps for 11gR2 Grid Infrastructure

I am putting this ASM related steps only, on request of a friend. In two projects where I handled HP EVA Storage also, I setup 2 node RAC for eBusiness Suite. If you require any other part of the implementation experience, would be happy to share.

This was done for Oracle Enterprise Linux

Prepare Storage and Disks

1. Run fdisk for RDBMS and GRID Binaries Multipathing Disk(Both Node)
2. Run partprobe for RDBMS and GRID Binaries Multipathing Disk(Both Node)
3. Run fdisk for ASM Related Multipathing Disk(Database Node1)
4. Run partprobe for ASM Related Multipathing Disk(Both Node)
5. Run mkfs.ext3 for RDBMS and GRID Binaries Multipathing Disk(Database Node1)

Multipathing Disks Used for ASM:

mpath2 - PRODDATA
mpath6 - PRODFRA
mpath5 - PRODOCR1
mpath4 - PRODOCR2
mpath3 - PRODOCR3

As an example fdisk and partprobe is shown for mapth2 disk, same needs to be done for others:

[root@mkkracdbserver1 ~]# fdisk /dev/mapper/mpath2
[root@mkkracdbserver1 ~]# partprobe /dev/mapper/mpath2

[root@mkkracdbserver1 ~]# ls -ltr /dev/mapper/mpath2*
brw-rw---- 1 root disk 253,  0 Aug  9 00:02 /dev/mapper/mpath2
brw-rw---- 1 root disk 253, 10 Aug  9 00:02 /dev/mapper/mpath2p1

[root@mkkracdbserver2 ~]# partprobe /dev/mapper/mpath2

[root@mkkracdbserver2 ~]# ls -ltr /dev/mapper/mpath2*
brw-rw---- 1 root disk 253, 0 Aug  6 18:07 /dev/mapper/mpath2
brw-rw---- 1 root disk 253, 9 Aug  9 00:06 /dev/mapper/mpath2p1



Install Oracle ASM Libraries RPM

# rpm -ivh oracleasm-support-2.1.4-1.el5.x86_64.rpm
# rpm -ivh oracleasmlib-2.0.4-1.el5.x86_64.rpm
# rpm -ivh oracleasm-2.6.18-238.el5-2.0.5-1.el5.x86_64.rpm

# /etc/init.d/oracleasm init
# /etc/init.d/oracleasm exit
# /etc/init.d/oracleasm status

To Load and initialize the ASMLib driver issue init
To Stop the ASMLib driver issue exit
To Display the status of the Oracle ASMLib driver issue status




Configure ASMLib Driver

This needs to be done on both the Database Node

# /etc/init.d/oracleasm configure

[root@mkkracdbserver1 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

Default user to own the driver interface []: oraprod
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

[root@mkkracdbserver2 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

Default user to own the driver interface []: oraprod
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]




Create ASM Disks

1. Use Oracleasm Createdisk Command on Database Node1
2. Use Oracleasm Listdisks Command on Database Node1
2. Use Oracleasm Scandisks Command on Database Node2
2. Use Oracleasm Listdisks Command on Database Node2

Important Note: Do not deviate from the above order, or else you may end up with non-visible ASM disks while doing the installation. You will have to change the diskstring parameter to asm_diskstring='/dev/oracleasm/disks/*' to make the disks visible. To overcome the issue, reboot both the database node and disks will be visible.


[root@mkkracdbserver1 ~]# oracleasm createdisk ASMDATA01 /dev/mapper/mpath2p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm createdisk ASMFRA01 /dev/mapper/mpath6p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm createdisk ASMOCR01 /dev/mapper/mpath5p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm createdisk ASMOCR02 /dev/mapper/mpath4p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm createdisk ASMOCR03 /dev/mapper/mpath3p1
Writing disk header: done
Instantiating disk: done

[root@mkkracdbserver1 ~]# oracleasm listdisks
ASMDATA01
ASMFRA01
ASMOCR01
ASMOCR02
ASMOCR03

[root@mkkracdbserver2 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
Instantiating disk "ASMDATA01"
Instantiating disk "ASMOCR03"
Instantiating disk "ASMOCR02"
Instantiating disk "ASMOCR01"
Instantiating disk "ASMFRA01"

[root@mkkracdbserver2 ~]# oracleasm listdisks
ASMDATA01
ASMFRA01
ASMOCR01
ASMOCR02
ASMOCR03


Implement Metalink Note 1059847.1 for Multipathing Disks

This needs to be done on both the Database Nodes

Important Note: If this is not done, then root.sh will fail in Database Node2.

11GR2 GRID INFRASTRUCTURE INSTALLATION FAILS WHEN RUNNING ROOT.SH ON NODE 2 OF RAC USING ASMLIB [ID 1059847.1]

1. Modify the /etc/sysconfig/oracleasm with:

ORACLEASM_SCANORDER="dm"
ORACLEASM_SCANEXCLUDE="sd"

# /etc/init.d/oracleasm restart

[root@mkkracdbserver1 ~]# /etc/init.d/oracleasm restart
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@mkkracdbserver1 ~]#

[root@mkkracdbserver2 ~]# /etc/init.d/oracleasm restart
Dropping Oracle ASMLib disks:                              [  OK  ]
Shutting down the Oracle ASMLib driver:                    [  OK  ]
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@mkkracdbserver2 ~]#

Cheers !!!!
Malay Khawas
Oracle Apps/Fusion DBA

How to do test of ar package, Installed on OS | Exception String: Error in invoking target 'nnfgt.o' of makefile

How to do test of ar package, Installed on OS | Exception String: Error in invoking target 'nnfgt.o' of makefile

a) create the following program called test.c :

main()
{
printf("hello\n");
}

b) compile it to a .o file :

gcc -c test.c

This generates an object file called test.o

c) Now test if basic ar functionality works;

ar cr myarch.a test.o

--> does the myarch.a file get created ok ?
--> if so can you query it with ar -t myarch.a ?

d) now test ar rv myarch2.a test.o 

Spool:

$ gcc -c test.c
$ ls -ltr
total 25970
-rw-r--r-- 1 apppoc dba 174 Apr 8 16:09 local.profile
-rw-r--r-- 1 apppoc dba 157 Apr 8 16:09 local.login
-rw-r--r-- 1 apppoc dba 136 Apr 8 16:09 local.cshrc
drwxr-xr-x 2 apppoc dba 96 May 2 12:18 bea
-rw------- 1 apppoc dba 13004722 May 5 02:03 core
-rw-r--r-- 1 apppoc dba 449 May 5 10:49 oamenv2.sh
-rw-r--r-- 1 apppoc dba 283726 May 6 08:07 core_strings.txt
-rw-r--r-- 1 apppoc dba 486 May 7 11:32 oamenv.sh
-rw-r--r-- 1 apppoc dba 30 May 8 12:07 test.c
-rw-r--r-- 1 apppoc dba 732 May 8 12:07 test.o
$ ar cr myarch.a test.o
Illegal Instruction(coredump)
$ ar rv myarch2.a test.o
a - test.o
Illegal Instruction(coredump)
$ cat test.c
main()
{
printf("hello\n");
}
$


Lesson Learned:

While doing OID Installation, config.sh was failing with following to execute the make command.
Exception String: Error in invoking target 'nnfgt.o' of makefile in OID Installation

Issue with opmnctl Startup or Status - Swap Issue

Issue with opmnctl Startup or Status - Swap Issue

applive WEBTIERLIVE /export/home/applive $ opmnctl status
Error occurred during initialization of VM
Could not reserve enough space for object heap
applive WEBTIERLIVE /export/home/applive $

Increase the OS Level Swap Space. It work fine for you.

Sending Mails using Sendmail with Subject and Attachment and Mail Body in Unix

Suppose you have a list of 10-15(or more) files to zip together and send to a mail. Take the filesnames to filelist.txt and all the mail addresses to listofmailids.txt file.

$ cat xxecms_sendmail_decrypt.sh
#!/bin/sh
while read FILE; do cp $FILE ZIP_HOLDER/ ; done < filelist.txt
ls -ltr ZIP_HOLDER/*; sleep 2
zip ZIP_HOLDER.zip ZIP_HOLDER/*
ls -ltr ZIP_HOLDER.zip; sleep 2
FILE="ZIP_HOLDER.zip"
echo "From: malay.khawas@mailaddress.local" > "MESSAGE_FILE"
echo "To: `cat listofmailids.txt`" >> "MESSAGE_FILE"
echo "Subject: Mail Attachment - From ECMS System" >> "MESSAGE_FILE"
echo "Content-type: multipart/mixed >/dev/null"
echo "Please find all the files in a Zipped Folder" >> "MESSAGE_FILE"
echo "Please contact sysadmin team if you need any help" >> "MESSAGE_FILE"
uuencode ${FILE} ${FILE} >> MESSAGE_FILE
cat MESSAGE_FILE | /usr/sbin/sendmail -t
rm -f ZIP_HOLDER/*
rm -f ZIP_HOLDER.zip
$


Sendmail with Subject and Attachment

FILE="SCM.ldt"
echo "From: malay.khawas@mailaddress.local" > "MESSAGE_FILE"
echo "To: malay.khawas@mailaddress.local" >> "MESSAGE_FILE"
echo "Subject: Report for Testing Bangladesh" >> "MESSAGE_FILE"
echo "Content-type: multipart/mixed >/dev/null"
echo "Hello How Are you" >> "MESSAGE_FILE"
echo "I have sent this as test mail" >> "MESSAGE_FILE"
uuencode ${FILE} ${FILE} >> MESSAGE_FILE
cat MESSAGE_FILE | /usr/sbin/sendmail -t

How to setup Passwordless SSH

Suppose I want to login from oradev1 to orapsup, without giving password, so I need to generate the key in oradev1 node.

In oradev1 Node:
ssh-keygen -t rsa
cat $HOME/.ssh/id_rsa.pub
Path of ssh-keygen: /usr/local/bin/ssh-keygen
/usr/local/bin/ssh-keygen -t rsa

In orapsup Node:
vi $HOME/.ssh/authorized_keys
And, put the contents of id_rsa.pub taken from oradev1 in the file
If .ssh directory doesnot exist, then create it by running the following

command:
ssh-keygen -t rsa

[oradev1@mkkdevserver1 ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/oradev1/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/oradev1/.ssh/id_rsa.
Your public key has been saved in /home/oradev1/.ssh/id_rsa.pub.
The key fingerprint is:
74:b3:cd:c9:8d:1c:40:ca:3c:ce:82:ab:04:7f:25:7f oradev1@mkkdevserver1.mkkdomain.local
[oradev1@mkkdevserver1 ~]$
[oradev1@mkkdevserver1 ~]$ cd .ssh/
[oradev1@mkkdevserver1 .ssh]$ ls
id_rsa  id_rsa.pub  known_hosts
[oradev1@mkkdevserver1 .ssh]$ cat id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAABIwAAAQEAtBBAsSSfzZC7Zu3fFlO8xCUaO1jzuXnPe+Z38nNboZLspjFkNaEkdYd9Ej6ZS+uBfTjrCVgCTBEdb0wLzAQlvLVLVro/M9gwjhoNGQyBKJyDQYmkUy7RSFuUdFupioT+YIOGfdQBbpn8YFbfeTWd23Khcaxkpdt/n4hlBY/PdAYUPxNvypnp49jtbideF6yiSqXF1gBDhWefh9Zd9KlIInKY6k0MNqTfn6WY57/PuSiWq+i4WKs/whZWOqnTKlulHVhBSSEjFaYKyPtPvgZbl13cbIQsvcB3TUMnYJT1JylmwMCw6ZGJIATLJMTA4WpNhHDO07e+6a7RtroyY63z5w== oradev1@mkkdevserver1.mkkdomain.local
[oradev1@mkkdevserver1 .ssh]$

Now whatever contents you have on file id_rsa.pub, you have to put that in authorized_keys file of orapsup.

[orapsup@mkkdevserver2 .ssh]$ ls -ltr /home/orapsup/.ssh/authorized_keys
-rw-r--r-- 1 orapsup dba 1374 Apr  5 13:52 /home/orapsup/.ssh/authorized_keys
[orapsup@mkkdevserver2 .ssh]$
View the /home/orapsup/.ssh/authorized_keys file and paste at the end of the file the above public key content.
Now you are ready to login from oradev1 to orapsup,  without giving any password.

Cheers !!!!!!!!!!!

Issue with GCC RPM Package Installation:

Issue with GCC RPM Package Installation:

# rpm -ivh gcc-4.4.4-13.el6.x86_64.rpm
warning: gcc-4.4.4-13.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
error: Failed dependencies:
 cloog-ppl >= 0.15 is needed by gcc-4.4.4-13.el6.x86_64
 cpp = 4.4.4-13.el6 is needed by gcc-4.4.4-13.el6.x86_64


Install glibc-header:

# rpm -ivh glibc-headers-2.12-1.7.el6.x86_64.rpm
warning: glibc-headers-2.12-1.7.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:glibc-headers          ########################################### [100%]

Install glibc-devel:

# rpm -ivh glibc-devel-2.12-1.7.el6.x86_64.rpm
warning: glibc-devel-2.12-1.7.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:glibc-devel            ########################################### [100%]

Install cpp package:(it will fail)

# rpm -ivh cpp-4.4.4-13.el6.x86_64.rpm
warning: cpp-4.4.4-13.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
error: Failed dependencies:
 libmpfr.so.1()(64bit) is needed by cpp-4.4.4-13.el6.x86_64

As it is asking for libmpfr.so, we need to install mpfr RPM:

# rpm -ivh mpfr-2.4.1-6.el6.x86_64.rpm
warning: mpfr-2.4.1-6.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:mpfr                   ########################################### [100%]

Install cloog-ppl RPM:(to correct further issues)

# rpm -ivh cloog-ppl-0.15.7-1.2.el6.x86_64.rpm
warning: cloog-ppl-0.15.7-1.2.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
error: Failed dependencies:
 libppl.so.7()(64bit) is needed by cloog-ppl-0.15.7-1.2.el6.x86_64
 libppl_c.so.2()(64bit) is needed by cloog-ppl-0.15.7-1.2.el6.x86_64

As it needs libppl.so, install ppl RPM:

# rpm -ivh ppl-0.10.2-11.el6.x86_64.rpm
warning: ppl-0.10.2-11.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:ppl                    ########################################### [100%]

Install cloog-ppl RPM:

# rpm -ivh cloog-ppl-0.15.7-1.2.el6.x86_64.rpm
warning: cloog-ppl-0.15.7-1.2.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:cloog-ppl              ########################################### [100%]

Install cpp package:(it would be successful)

# rpm -ivh cpp-4.4.4-13.el6.x86_64.rpm
warning: cpp-4.4.4-13.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:cpp                    ########################################### [100%]

Now, try with GCC Installation:

# rpm -ivh gcc-4.4.4-13.el6.x86_64.rpm
warning: gcc-4.4.4-13.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing… ########################################### [100%]
1:gcc ########################################### [100%]

Cheers !!!!!!!
Malay Khawas