Oct 18, 2013

How to set up Oracle Gateway for Informix (1)

Databases

informix 11.5
Oracle 11.2.0.2
Gateway 11.2.0.2  (Oracle suggests I can use 11.2.0.4, Gateway version can be higher than RDBMS version)

1.  Install the gateway
unzip the Oracle 11.2.0.2 installation software,


[oracle@homer-db 11202]$ ls *.zip
p10098816_112020_Linux-x86-64_1of7.zip  p10098816_112020_Linux-x86-64_5of7.zip
p10098816_112020_Linux-x86-64_2of7.zip  p10098816_112020_Linux-x86-64_6of7.zip
p10098816_112020_Linux-x86-64_3of7.zip  p10098816_112020_Linux-x86-64_7of7.zip
p10098816_112020_Linux-x86-64_4of7.zip


    unzip p10098816_112020_Linux-x86-64_1of7.zip
    unzip p10098816_112020_Linux-x86-64_2of7.zip
    unzip p10098816_112020_Linux-x86-64_3of7.zip
    unzip p10098816_112020_Linux-x86-64_4of7.zip
    unzip p10098816_112020_Linux-x86-64_5of7.zip
    unzip p10098816_112020_Linux-x86-64_6of7.zip
    unzip p10098816_112020_Linux-x86-64_7of7.zip

[oracle@homer-db 11202]$ ls
[oracle@homer-db 11202]$ cd gateways
[oracle@homer-db gateways]$ ls
doc  install  legacy  readme.html  response  runInstaller  stage  welcome.html

[oracle@homer-db gateways]$./runInstaller



Nov 5, 2011

TNS-12518: A new bug in Oracle 11gR2

TNS-12518 is an old error, but has new cause in 11GR2.

Environment: Oracle 11.2..0.2 3-node RAC
RHEL 5

Problem: On one of the 3-nodes, Oracle listener failed to establish the connection.
As we used the shared server and dedicated server, the shared server connection is working fine but the dedicated server doesn't work.

Here is the error message,

[root@waleucp02 trace]# tail -f listener.log
28-OCT-2011 17:46:59 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PRD)(CID=(PROGRAM=perl@REMOTE)(HOST=REMOTE)(USER=nagios))) * (ADDRESS=(PROTOCOL=tcp)(HOST=*******.8.17)(PORT=41612)) * establish * PRD * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
 TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
   Linux Error: 32: Broken pipe

Troubleshooting procedure:

1)Follow the Metlink article:


TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection [ID 550859.1]

2)As this is a DEDICATED server, we increased the Oracle parameter "processes" from 150 to 500. After I bounce the database, but it doesn't work

3)Initially, we thinks it was caused by Firewall setting, but the networking team didn't find anything suspicious.

4Finally, we figured out it is caused by the file permission (setuid)of oracle executable. This is the second time we encountered the similar issues since installing 11GR2 on two different servers.

go to $ORACLE_HOME/bin, the oracle's permission should be 6751, but for some reason, it was changed to 4751.


Note: I encountered a case, if tar backup by root, and recovered by oracle user, it may cause this issue. However, our cluster is brand new installation.

Incorrect permission.
# cd /u01/app/oracle/product/11.2.0/bin
# ll oracle
-rwxr-s--x 1 oracle asmadmin 228886426 Aug 17 14:25 oracle
Correct one:
# chmod 6751 oracle    
# ll oracle
-rwsr-s--x 1 oracle asmadmin 228886426 Aug 17 14:25 oracle

After setting the correct setuid, listener started working again.
Similarly, one months ago, we encountered a similar issue for ASM. Please reference Metalink ID:
"DBCA Does Not Display ASM Disk Groups In 11.2 [ID 1177483.1]". 
That error is also caused by the setuid bit of oracle executable under the $GRID_HOME/bin
We've submitted this error to Oracle and Oracle support has created a bug for this error.



Nov 4, 2011

How to change dbsnmp password in 11gR2 RAC?

Environment:  
Oracle   3-nodes RAC 11.2.0.2


Problem: 
EM user dbsnmp password was messed up, so EM failed to get agent information, then lock the dbsnmp user. So we need reset to the correct password.


Solution:
Step 1). On all nodes, stop EM.
$emctl stop dbconsole

Step 2). On each node, e.g host #1 (host01),
cd  /u01/app/oracle/product/11.2.0/host01_<DBNAME>/sysman/emd
vi targets.xml


There are two changes, one for oracle_database, the other for rac_database.
change 1)
VALUE="****" changed to VALUE="PASSWORD"
change 2)
ENCRYPTED="TRUE" changed to ENCRYPTED="FALSE"


For example,
1)
<Target TYPE="oracle_database" NAME="DBNAME_INSTANCE2">
........
       <Property NAME="UserName" VALUE="d12147da3ed00cb9" ENCRYPTED="TRUE"/>
       <Property NAME="password" VALUE="PASSWORD" ENCRYPTED="FALSE"/>
2)
<Target TYPE="rac_database" NAME="DBNAME">
........
       <Property NAME="UserName" VALUE="d12147da3ed00cb9" ENCRYPTED="TRUE"/>
       <Property NAME="password" VALUE="PASSWORD" ENCRYPTED="FALSE"/>

Also did the same steps on the following configuration files,

vi /u01/app/oracle/product/11.2.0/host02_DBNAME/sysman/emd/targets.xml
vi /u01/app/oracle/product/11.2.0/host03_DBNAME/sysman/emd/targets.xml
repeat the above steps,


Step 3)After everything is changed, changed the dbsnmp password and unlock the user,
SQL> alter user dbsnmp identified by PASSWORD;
SQL> alter user dbsnmp account unlock; User altered.


Step 4)  Restart the EM
$ emctl start dbconsole
Step 5) Go to node2 and node3, repeat the above step
All EMs would be fine now.