Oracle Technologies Blog

By ASKM

Archive for the ‘Troubleshooting’ Category

SCAN LISTENER TRACING

Posted by Srikrishna Murthy Annam on May 12, 2011

This article describes enabling and disabling the SCAN listener tracing. Before capturing the scan listener tracing,please make sure your scan listener is configured as per the following Oracle Support IDs.
1070607.1
975457.1

Some where you should find some clue for your SCAN connectivity issues. If you are still unable to find the exact problem with the scan connectivity, you need to enable the tracing and analyze the trace files or upload the trace files to oracle support.

To enable level 16 tracing for SCAN listeners, add the following parameters to listener.ora and  try to reproduce the connectivity issue.

TRACE_LEVEL_<listener_name> = 16
TRACE_DIRECTORY_<listener_name> =<dir location>
TRACE_TIMESTAMP_<listener_name> = TRUE
DIAG_ADR_ENABLED_<listenername>=off

Then try to reload all the listeners after these parameters are in place using lsnrctl reload <listener_name>.

Reproduce the issue and verify that the trace and log files are created in the directory mentioned in the parameter TRACE_DIRECTORY_<listener_name>.

To disable scan listener tracing:

$lsnrctl
LSNRCTL>set current_listener <listener_name>
LSNRCTL>set trc_level OFF
LSNRCTL>save_config

In the above tracing we disabled diagnostic repository with the parameter DIAG_ADR_ENABLED_<listenername> and collected trace files. Now if you want to re-enable DIAG_ADR, just comment out DIAG_ADR_ENABLED_<listenername> in listener.ora and reload all the listeners.

NOTE: ADR is the new concept introduced from 11g and it is a file system repository to store all diagnostic data. Please review the article  Automatic Diagnostic Repository  to know more on ADR concepts.

Session Log to show enabling SCAN Tracing:

[grid@b600e6tr01 admin]$ cp listener.ora listener.ora_bak
[grid@b600e6tr01 admin]$ vi listener.ora   ==> Here add trace parameter
[grid@b600e6tr01 admin]$ clear
[grid@b600e6tr01 admin]$ ps -ef | grep -i tns
grid      5943  4623  0 07:12 pts/1    00:00:00 grep -i tns
grid     18139     1  0 May03 ?        00:01:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid     18142     1  0 May03 ?        00:01:14 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
grid     18164     1  0 May03 ?        00:01:18 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
[grid@b600e6tr01 admin]$ lsnrctl reload listener

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:12:38

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully

[grid@b600e6tr01 admin]$ lsnrctl reload LISTENER_SCAN2

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:12:55

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
The command completed successfully
[grid@b600e6tr01 admin]$ lsnrctl reload LISTENER_SCAN3

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:13:02

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
The command completed successfully
[grid@b600e6tr01 admin]$

[grid@b600e6tr02 admin]$ cp listener.ora listener.ora_bak
[grid@b600e6tr02 admin]$ vi listener.ora  ==> Here add trace parameters
[grid@b600e6tr02 ~]$ ps -ef | grep -i tns
grid      1042     1  0 May03 ?        00:01:20 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
grid      1171     1  0 May03 ?        00:01:04 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid      8231  7935  0 07:06 pts/1    00:00:00 grep -i tns
[grid@b600e6tr02 ~]
[grid@b600e6tr02 admin]$ lsnrctl reload LISTENER

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:13:26

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully
[grid@b600e6tr02 admin]$ lsnrctl reload LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:13:35

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
The command completed successfully
[grid@b600e6tr02 admin]$

Session Log to show disabling SCAN Tracing:

[grid@b600e6tr01 ~]$ ps -ef | grep -i tns
grid      5309  4623  0 07:06 pts/1    00:00:00 grep -i tns
grid     18139     1  0 May03 ?        00:01:01 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid     18142     1  0 May03 ?        00:01:14 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN2 -inherit
grid     18164     1  0 May03 ?        00:01:18 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN3 -inherit
[grid@b600e6tr01 ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:07:02

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener LISTENER
Current Listener is LISTENER
LSNRCTL> set trc_level OFF
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
LISTENER parameter "trc_level" set to off
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Saved LISTENER configuration parameters.
Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora
Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak
The command completed successfully
LSNRCTL> set current_listener LISTENER_SCAN2
Current Listener is LISTENER_SCAN2
LSNRCTL> set trc_level OFF
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
LISTENER_SCAN2 parameter "trc_level" set to off
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
Saved LISTENER_SCAN2 configuration parameters.
Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora
Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak
The command completed successfully
LSNRCTL> set current_listener LISTENER_SCAN3
Current Listener is LISTENER_SCAN3
LSNRCTL> set trc_level OFF
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
LISTENER_SCAN3 parameter "trc_level" set to off
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
Saved LISTENER_SCAN3 configuration parameters.
Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora
Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak
The command completed successfully
LSNRCTL>

[grid@b600e6tr02 ~]$ ps -ef | grep -i tns
grid      1042     1  0 May03 ?        00:01:20 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
grid      1171     1  0 May03 ?        00:01:04 /u01/home/11.2.0/grid/bin/tnslsnr LISTENER -inherit
grid      8231  7935  0 07:06 pts/1    00:00:00 grep -i tns
[grid@b600e6tr02 ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-MAY-2011 07:10:37

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener LISTENER
Current Listener is LISTENER
LSNRCTL> set trc_level OFF
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
LISTENER parameter "trc_level" set to off
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Saved LISTENER configuration parameters.
Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora
Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak
The command completed successfully
LSNRCTL> set current_listener LISTENER_SCAN1
Current Listener is LISTENER_SCAN1
LSNRCTL> set trc_level OFF
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
LISTENER_SCAN1 parameter "trc_level" set to off
The command completed successfully
LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
Saved LISTENER_SCAN1 configuration parameters.
Listener Parameter File   /u01/home/11.2.0/grid/network/admin/listener.ora
Old Parameter File   /u01/home/11.2.0/grid/network/admin/listener.bak
The command completed successfully
LSNRCTL>

Hope it helps
SRI

Advertisements

Posted in 11gR2 RAC, Troubleshooting | Tagged: , , , , , , , , | 1 Comment »

OEM Gridcontrol 10201 Installation Errors On Linux 5.4

Posted by Srikrishna Murthy Annam on March 17, 2011

In this article i am gooing to describe the issues we faced during 10.2.0.1 oem grid controll on a RHEL 5.4.

Issue 1:

When i start installing the oem grid control after completing all the pre requesites, we got the following error. The cursor hangs there instead of populating OUI window.

[oracle@dhcppc30 ~]$ cd /u01/dumps/oem/Disk1/
[oracle@dhcppc30 Disk1]$ ./runInstaller
Starting Oracle Universal Installer…Checking installer requirements…Checking operating system version: must be enterprise-4, enterprise-5, redhat-3, redhat-4, redhat-5, redhat-5.1, SuSE-9, SuSE-10, UnitedLinux-1.0, asianux-1 or asianux-2
PassedAll installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-03-17_11-43-44AM. Please wait …[oracle@dhcppc30 Disk1]$ Exception in thread “main” java.lang.UnsatisfiedLinkError: /tmp/OraInstall2011-03-17_11-43-44AM/jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory
at java.lang.ClassLoader$NativeLibrary.load(Native Method)
at java.lang.ClassLoader.loadLibrary0(Unknown Source)
at java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.lang.Runtime.loadLibrary0(Unknown Source)
at java.lang.System.loadLibrary(Unknown Source)
at sun.security.action.LoadLibraryAction.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.awt.NativeLibLoader.loadLibraries(Unknown Source)
at sun.awt.DebugHelper.<clinit>(Unknown Source)
at java.awt.Component.<clinit>(Unknown Source)
at oracle.sysman.emgc.oneclick.OneClickWizard.getGuiDefaults(OneClickWizard.java:239)
at oracle.sysman.emgc.oneclick.OneClickWizard.<init>(OneClickWizard.java:205)
at oracle.sysman.emgc.oneclick.OneClick.<init>(OneClick.java:230)
at oracle.sysman.emgc.oneclick.OneClickInstaller.<init>(OneClickInstaller.java:116)
at oracle.sysman.emgc.oneclick.OneClickInstaller.process(OneClickInstaller.java:268)
at oracle.sysman.emgc.oneclick.OneStartup.startup(OneStartup.java:359)
at oracle.sysman.emgc.oneclick.OneArgs.main(OneArgs.java:650)
at oracle.sysman.emgc.oneclick.OneStartup.main(OneStartup.java:367)

When i observe the error message i thought it may the error with LD_LIBRARY_PATH and tried all the possible values for this variable , but no luck. Then after googling i came to know that there is one rpm missing in the OS RHEL 5.4.  Then i installed “libXp-1.0.0-8.1.el5.i386.rpm” and tried. It works …..

RPMs Locations

Issue 2:

error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Solution: Install libaio and libaio-devel packages. If packages already installed and error still occurs try execute “ldconfig” as root.

Issue 3:

Failed in smi manipulation().
A problem has occurred reading the initial configuration and storing it into repository
Resolution:
Please refer to the base exception for resolution, or call Oracle support.
Base Exception:
/u01/app/oracle/oem/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
Resolution:
Please make sure the values entered in OHS configuration files are correct.
oracle.ias.sysmgmt.exception.InvalidConfigurationException: Base Exception:
/u01/app/oracle/oem/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
Resolution:
Please make sure the values entered in OHS configuration files are correct.
at oracle.ias.sysmgmt.repository.plugin.advanced.apache.StateTranslator.checkConfigFileValidity(Unknown Source)
at oracle.ias.sysmgmt.repository.plugin.advanced.apache.StateTranslator.validateConfigDuringEvaluate(Unknown Source)
at oracle.ias.sysmgmt.repository.plugin.advanced.apache.PlugInImpl.localConfigValidation(Unknown Source)
at oracle.ias.sysmgmt.repository.DcmPlugin.localConfigValidation(Unknown Source)
at oracle.ias.sysmgmt.repository.RepositoryImpl.performLocalValidation(Unknown Source)
at oracle.ias.sysmgmt.repository.SyncUpHandler._updatePluginConfigData(Unknown Source)
at oracle.ias.sysmgmt.repository.SyncUpHandler.syncUpFromLocalFiles(Unknown Source)
at oracle.ias.sysmgmt.repository.RepositoryImpl.syncUpFromLocalFiles(Unknown Source)
at oracle.ias.sysmgmt.utility.editpropagator.PropagateLocalEdit.repositoryInit(Unknown Source)
at oracle.ias.sysmgmt.persistence.utility.PMUtility.initConfiguration(Unknown Source)
at oracle.ias.sysmgmt.task.TaskMaster.initConfiguration(Unknown Source)
at oracle.ias.sysmgmt.task.TaskMaster.sysInit(Unknown Source)
at oracle.ias.sysmgmt.task.TaskMaster.sysInit(Unknown Source)
at oracle.ias.sysmgmt.task.InstanceManager.sysInit(Unknown Source)
at oracle.ias.sysmgmt.task.InstanceManager.init(Unknown Source)
at oracle.ias.sysmgmt.EntryPoint.init(Unknown Source)
at oracle.ias.webcache.config.WebcacheConfig.smiSetup(WebcacheConfig.java:483)
at oracle.ias.webcache.config.WebcacheConfig.smiEnableWebcache(WebcacheConfig.java:454)
at oracle.ias.webcache.config.WebcacheConfig.enableConfiguration(WebcacheConfig.java:299)
at oracle.ias.webcache.config.WebcacheConfig.main(WebcacheConfig.java:87)Command = /u01/app/oracle/oem/oms10g/jdk/bin/java has failedINFO: Configuration assistant “Web Cache Configuration Assistant” failed

Solution :

create a symbolic link “ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2”

chmod 755 /usr/lib/libgdbm.so.2.0.0
chmod 755 /usr/lib/libdb.so.2

[root@oemmach01 logs]# locate libdb.so.2
[root@oemmach01 logs]# locate libgdbm.so.2.0.0
/usr/lib/libgdbm.so.2.0.0
[root@oemmach01 logs]# ls -ldd libgdbm.so.2.0.0
ls: libgdbm.so.2.0.0: No such file or directory
[root@oemmach01 logs]# ls -ldd /usr/lib/libgdbm.so.2.0.0
-rwxr-xr-x 1 root root 25260 Jun  5  2007 /usr/lib/libgdbm.so.2.0.0
[root@oemmach01 logs]# ldd /usr/lib/libgdbm.so.2.0.0
linux-gate.so.1 =>  (0x0075b000)
libc.so.6 => /lib/libc.so.6 (0x0094c000)
/lib/ld-linux.so.2 (0x0092e000)
[root@oemmach01 logs]#

[root@oemmach01 logs]# ldd /usr/lib/libgdbm.so.2.0.0
linux-gate.so.1 =>  (0x0075b000)
libc.so.6 => /lib/libc.so.6 (0x0094c000)
/lib/ld-linux.so.2 (0x0092e000)
[root@oemmach01 logs]# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
[root@oemmach01 logs]# chmod 755 /usr/lib/libgdbm.so.2.0.0
[root@oemmach01 logs]# chmod 755 /usr/lib/libdb.so.2
[root@oemmach01 logs]#

Issue 4:

Command = /u01/app/oracle/oem/oms10g/jdk/bin/java -Djava.net.preferIPv4Stack=true -Djava.io.tmpdir=/tmp -mx512M -classpath /u01/app/oracle/oem/oms10g/dcm/lib/dcm.jar:/u01/app/oracle/oem/oms10g/dcm/lib/oc4j_deploy_tools.jar:/u01/app/oracle/oem/oms10g/opmn/lib/opmnplugin.jar -Doracle.ias.sysmgmt.logging.logdir=/u01/app/oracle/oem/oms10g/j2ee/home/log oracle.j2ee.tools.deploy.Oc4jDeploy -oraclehome /u01/app/oracle/oem/oms10g -verbose -inifile /u01/app/oracle/oem/oms10g/j2ee/deploy.ini  has failed
Execution Error : oracle.ias.sysmgmt.exception.InvalidSMISessionException: Base Exception:
This DCM Client Session has been invalidated due to changes made to config files by some other session. All unsaved changes will be lost.
Resolution:
Please close this DCM Client session, start another one and redo the changes made.
at oracle.ias.sysmgmt.entity.Session.verifySessionIsValid(Unknown Source)
at oracle.ias.sysmgmt.entity.Session.makeDeleteEntityListConsistent(Unknown Source)
at oracle.ias.sysmgmt.smi.SMIEntryPoint.saveChanges(Unknown Source)
at oracle.j2ee.tools.deploy.Oc4jDeploy.saveSMIChanges(Unknown Source)
at oracle.j2ee.tools.deploy.Oc4jDeploy.saveSMIChanges(Unknown Source)
at oracle.j2ee.tools.deploy.Oc4jDeploy.doDeploy(Unknown Source)
at oracle.j2ee.tools.deploy.Oc4jDeploy.execute(Unknown Source)
at oracle.j2ee.tools.deploy.Oc4jDeploy.deploy(Unknown Source)
at oracle.j2ee.tools.deploy.Oc4jDeploy.main(Unknown Source)INFO: Configuration assistant “OC4J Instance Configuration Assistant” failedSolution :

[root@oemmach01 logs]# /u01/app/oracle/oem/oms10g/jdk/bin/java -Djava.net.preferIPv4Stack=true -Djava.io.tmpdir=/tmp -mx512M -classpath /u01/app/oracle/oem/oms10g/dcm/lib/dcm.jar:/u01/app/oracle/oem/oms10g/dcm/lib/oc4j_deploy_tools.jar:/u01/app/oracle/oem/oms10g/opmn/lib/opmnplugin.jar -Doracle.ias.sysmgmt.logging.logdir=/u01/app/oracle/oem/oms10g/j2ee/home/log oracle.j2ee.tools.deploy.Oc4jDeploy -oraclehome /u01/app/oracle/oem/oms10g -verbose -inifile /u01/app/oracle/oem/oms10g/j2ee/deploy.ini

Reading ini file – ‘/u01/app/oracle/oem/oms10g/j2ee/deploy.ini’
Adding web-app ‘IsWebCacheWorkingWeb.war’ for app ‘IsWebCacheWorking’.
Initializing DCM…done.
OC4J instance ‘home’ already exists.
Starting OC4J instance ‘home’…done.
Deploying application ‘IsWebCacheWorking’ to OC4J instance ‘home’.
Notification ==> Application Deployer for IsWebCacheWorking STARTS [ 2011-04-17T04:04:15.915PDT ]
Notification ==> Undeploy previous deployment
Notification ==> Removing files for app file:/u01/app/oracle/oem/oms10g/j2ee/home/applications/IsWebCacheWorking.ear
Notification ==> Copy the archive to /u01/app/oracle/oem/oms10g/j2ee/home/applications/IsWebCacheWorking.ear
Notification ==> Unpack IsWebCacheWorking.ear begins…
Notification ==> Unpack IsWebCacheWorking.ear ends…
Notification ==> Initialize IsWebCacheWorking.ear begins…
Notification ==> Initialize IsWebCacheWorking.ear ends…
Notification ==> Initialize IsWebCacheWorkingWeb begins…
Notification ==> Initialize IsWebCacheWorkingWeb ends…
Notification ==> deleting:  /u01/app/oracle/oem/oms10g/j2ee/home/applications/IsWebCacheWorking.ear
Notification ==> deleting:  /u01/app/oracle/oem/oms10g/j2ee/home/applications/IsWebCacheWorking/IsWebCacheWorkingWeb.war
Notification ==> Application Deployer for IsWebCacheWorking COMPLETES [ 2011-04-17T04:04:16.023PDT ]

Calling updateConfig to notify DCM of new deployments…done.
Adding OC4J mount points for application ‘IsWebCacheWorking’…done.
Calling SMI to save changes.
SMISession.saveChanges succeeded.
Binding web app ‘IsWebCacheWorkingWeb’ to default-web-site for application ‘IsWebCacheWorking’ in OC4J instance ‘home’
Web app ‘IsWebCacheWorkingWeb’ bound successfully.
Calling updateConfig to notify DCM of new web-bindings…done.
Adding application ‘IsWebCacheWorking’ to the DCM repository…done.
Application ‘IsWebCacheWorking’ deployed successfully.
Stopping OC4J instance ‘home’…done.
Calling SMI to retry init of failed plugins…done.
Terminating DCM…done.
Copying /u01/app/oracle/oem/oms10g/j2ee/deploy.ini to /u01/app/oracle/oem/oms10g/j2ee/deploy.ini.1303038278047.bak.
Writing any undeployed entries back to /u01/app/oracle/oem/oms10g/j2ee/deploy.ini.

Oc4jDeploy tool completed successfully!

[root@oemmach01 logs]#

Issue 5:

Check complete. The overall result of this check is: Failed <<<<
Solution: Install missing package or set check system parameters (See reason of failure).

Hope it helps

SRI

Posted in OEM, Troubleshooting | Tagged: , , , , , , | 1 Comment »

JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener

Posted by Srikrishna Murthy Annam on February 18, 2011

Recently we resolved a  JDBC connection issue with RAC database.  I will detail the issue in 8 steps

My Env setup :
We have 11gR2 RAC database running using ASM storage and all the servers are in network domain domain1. Client is using one JAVA application and it is running on non-RAC 11g database and the servers are in a different network domain domain2.
Our task :
Our task is to migrate the database that the JAVA applications is using to the RAC setup on ASM storage and establish the JAVA applications to connect to the newly migrated database. The big challenge we faced here is due to the reason that the setups are in different domains. We are using the 11gR2 SCAN concept,which complicated the issue even more.
What we did :
I am giving here the outlined view of the migration plan, but we should consider many other things in the actual migration process.

  1. export database from source
  2. Move the dump files to target database node.
  3. prepare the target database for import
  4. import the database to target database
  5. run utlrp to compile all the objects
  6. Verify the errors from import logfile
  7. “Note invalids,objects and schema status”
  8. Compare the objects status with the source
  9. verify the db links on target

All went fine. We tested the database connection from the server where java applications are running. We tested TOAD connection as well. All is well. We requested client to test the application after providing the JDBC connection string.

What is the issue :
Client raised an issue that the application is not able to connect to the database.

Troubleshooting:
As i said earlier, we tested TOAD connection. We tested sqlplus connection to the database. Both are working fine with the provided connecting string. Then we tried to dig into the SCAN listener concepts suspecting it might be an issue and configured everything as per the oracle documents. Finally we concluded that it is the issue only with JDBC connections.

Issue resolution :
Then prepared a java script to test the database java connections. Modified various settings on the database side and tested JDBC connection. Atlast we found the issue. The issue is with the connection string domain name specification.

How to test a Java Connection to oracle:
Pls see the video demo

Explanation:
As i said in my first sentence that the source and target environments are working in different domains, being source using domain2 and target using domain1. SCAN in domain1(RAC side) resolves to three IPs and the same is configured in the other network using NATing. Now the scan name resolves to three IPs in both source and target and they are connected with NATing. The host name in the connectiong string be the same as the init.ora parameter remote_listener and it should also match to the SCAN name. We should not include any domain names with SCAN name , remote_listername and with HOST setting in connecting string.

<strong>On Source RAC side:</strong>
The HOST=scan-cluster should exactly match the remote_listener parameter in the database.

SQL> show parameter remote_list

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      scan-cluster:1521

<strong>On Applications Side: </strong>
<database name="defaultOracle"
debug="false"
password="xxxxxxxxxxxxxxxx"
connectString="jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=scan-cluster) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=racpoc.domain1.com)(FAILOVER_MODE =(TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))" />

<strong>$ nslookup scan-cluster</strong>
Server:         128.191.2.13
Address:        128.191.2.13#53

Name:   scan-cluster.domain2.com
Address: 128.191.224.227
Name:   scan-cluster.domain2.com
Address: 128.191.224.29
Name:   scan-cluster.domain2.com
Address: 128.191.224.30

The Java Script to test Oracle Connection is :

import java.sql.*;
public class testconn {
public static void main(String[] s)throws Exception {
Class.forName("oracle.jdbc.OracleDriver");

String url="jdbc:oracle:thin:@(DESCRIPTION= (LOAD_BALANCE=on) (ADDRESS=(PROTOCOL=TCP)(HOST=scan-cluster)(PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=racpoc.domain)))";

for (int i=0; i<20; i++) {
try {
long x= System.currentTimeMillis () ;
Connection conn = DriverManager.getConnection(url,"askm","askm");
long y= System.currentTimeMillis ();

System.out.println("Connection Succesful "+conn);
System.out.println("Connection time is "+(y-x)/1000+" ms");
Statement stmt =conn.createStatement();
ResultSet res= stmt.executeQuery(" select host_name from v$instance");

while(res.next()) {
System.out.println(res.getString(1));
}
stmt.close();
conn.close();
}
catch(Exception e) {
e.printStackTrace();}}}}

Hope it helps

SRI

Posted in 11gR2 RAC, Troubleshooting | Tagged: , , , , , , , | 77 Comments »

Troubleshooting ASM disk drop

Posted by Srikrishna Murthy Annam on February 2, 2011

The present article describes how to troubleshoot  if you are not able delete an ASM disk from  the ASM storage. The troubleshooting technique may vary from person to person and the following article describes the technique  i used to drop an ASM disk. There may be so many other possible solutions to the issue, but the one presented here worked for me.

The present issue is that , i have to delete an ASM disk from the ASM storage. The disk is already marked as ASM disk and when i try to unmark it for ASM disk, it throws an error. So i cant delete the OS disk without deleting it from the ASM disks.

Present issue occured in 11gR2 RAC on linux and we are using multi path ASM disks for the storage.

[root@rac01 disks]# /etc/init.d/oracleasm deletedisk DATAVOL1
Removing ASM disk “DATAVOL1”:                              [FAILED] 

 

So i verified the log file  /var/log/oracleasm and the content of the log file shows me the following error

Unable to clear disk “DATAVOL1”
Clearing disk header: oracleasm-write-label: Unable to open device “/dev/oracleasm/disks/DATAVOL1”: Device or resource busy
failed
Unable to clear disk “DATAVOL1”

I tried creating a diskgroup with this ASM disk and droped it and then tried to delete the ASM disk, but no use.

SQL> create diskgroup test external redundancy disk ‘/dev/oracleasm/disks/DATAVOL1’; 

Diskgroup created.

SQL> drop diskgroup test;
Diskgroup dropped.
SQL>

[root@rac01 disks]# /etc/init.d/oracleasm deletedisk DATAVOL1
Removing ASM disk “DATAVOL1”:                              [FAILED]

I verified with the following command if the disk is still marked as ASM disk or not. It is still marked as ASM disk.

[root@rac01 disks]# blkid|grep sd.*oracleasm|while read a b;do echo -n $a$b” scsi_id=”;(echo $a|tr -d [:digit:]|tr -d [:]|cut -d”/” -f3|xargs -i scsi_id -g -s /block/{})done | grep -i DATAVOL1;
/dev/sdc:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdf:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdi:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdl:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdo:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdp:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdu:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
/dev/sdx:LABEL=”DATAVOL1″ TYPE=”oracleasm” scsi_id=360050768019600fba80000000000036b
[root@rac01 disks]# 

[root@rac01 disks]# multipath -ll

..

..

vpath0 (360050768019600fba80000000000036b) dm-11 IBM,2145
[size=500G][features=1 queue_if_no_path][hwhandler=0][rw]
\_ round-robin 0 [prio=200][active]
\_ 2:0:7:0 sdo  8:224  [active][ready]
\_ 1:0:7:0 sdp  8:240  [active][ready]
\_ 2:0:6:0 sdu  65:64  [active][ready]
\_ 1:0:6:0 sdx  65:112 [active][ready]
\_ round-robin 0 [prio=40][enabled]
\_ 1:0:4:0 sdc  8:32   [active][ready]
\_ 1:0:5:0 sdf  8:80   [active][ready]
\_ 2:0:4:0 sdi  8:128  [active][ready]
\_ 2:0:5:0 sdl  8:176  [active][ready]
[root@rac01 disks]#

Then i tried clearing the disk with the OS command dd.

[root@rac01 disks]# dd if=/dev/zero of=/dev/dm-11 bs=1024 count=100
100+0 records in
100+0 records out
102400 bytes (102 kB) copied, 0.05526 seconds, 1.9 MB/s
[root@rac01 disks]# /etc/init.d/oracleasm deletedisk DATAVOL1
Removing ASM disk “DATAVOL1”:                              [  OK  ]
[root@rac01 disks]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATA1
FRADISK1
FRAVOL1
[root@rac01 disks]#

Now i am able to successfully delete the disk from ASM disk list.

On all other nodes in the RAC , do the following to sync

[root@rac02 disks]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATA1
DATAVOL1
FRADISK1
FRAVOL1
[root@rac02 disks]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]
[root@rac02 disks]# /etc/init.d/oracleasm listdisks
CRSVOL1
DATA1
FRADISK1
FRAVOL1
[root@rac02 disks]#

Content from the log file /var/log/oracleasm now shows …

Disk “DATAVOL1” defines an unmarked device
Dropping disk: done
Reloading disk partitions: done
Cleaning any stale ASM disks…
Validating disk “CRSVOL1”
Validating disk “DATA1”
Validating disk “FRADISK1”
Validating disk “FRAVOL1” 

 

Hope it helps

SRI

Posted in 11gR2 RAC, ASM, Database Articles, Troubleshooting | Tagged: , , , , , , , , | 3 Comments »

ORA-01157: cannot identify/lock data file

Posted by Srikrishna Murthy Annam on January 7, 2011

In the present article, we will see how to start the database working with ASM if someone accidentally deleted moved the datafile which resides on ASM storage to a different location.

The present case is similar to the case we do on normal storage.
If we delete moved database file to a different location and if it is not updated in database, we will not be able to start the database.
So we need to update the database with this information to start it successfully.

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739201677
ASMCMD> cp ‘TS1.256.739201677’ ‘/u01/datafiles/ts1.dbf’
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD> 

Remove  the datafile from ASM using rm command.

[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘+TESTDG/testdb/datafile/test1.257.738755445’

CRS-2674: Start of ‘ora.testdb.db’ on ‘dhcppc1’ failed
[oracle@dhcppc1 ~]$

SQL> startup
ORACLE instance started.

Total System Global Area  723984384 bytes
Fixed Size                  1338980 bytes
Variable Size             478151068 bytes
Database Buffers          239075328 bytes
Redo Buffers                5419008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘+TESTDG/testdb/datafile/test1.257.738755445’

SQL>

SQL> alter database datafile 7 offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database rename file ‘+DG1/testdb/datafile/TS1.256.739201677’ to ‘/u01/datafiles/ts1.dbf’;

Database altered.

SQL> alter database open;

Database altered.

SQL> select tablespace_name,status from dba_tablespaces where  tablespace_name=’TS1′;

TABLESPACE_NAME STATUS
————— ———
TS1             ONLINE

SQL> select file_name,status from dba_data_files where file_id=8;

FILE_NAME                                          STATUS
————————————————– ———
/u01/datafiles/ts1.dbf                             AVAILABLE

SQL>

 

Hope it helps

SRI

ASMCMD> cd +DG1/testdb/datafile
ASMCMD> ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   DEC 31 14:00:00  Y    TS1.256.739201677
ASMCMD> cp ‘TS1.256.739201677’ ‘/u01/datafiles/ts1.dbf’
copying +DG1/testdb/datafile/TS1.256.739201677 -> /u01/datafiles/ts1.dbf
ASMCMD>

Posted in ASM, Database Articles, Troubleshooting | Tagged: , , , | 5 Comments »

CRS-2640

Posted by Srikrishna Murthy Annam on January 6, 2011

This article describes how to modify the  resource dependencies in the cluster.

Some time back i dropped a diskgroup DG1 from the SQL prompt and it is dropped successfully without any errors. But when i see the cluster resources status, the diskgroup resource still displays in offline status. Further investigations revealed that the disk group details still exist in OCR.

When i try to restart the database, it gives the error CRS-2640. I tried to delete the OCR details as well with srvctl command and able to delete the OCR details. Tried again to start the database but failed with error. The work around to this error is to modify the OCR with the existing diskgroups.

[oracle@dhcppc1 ~]$ crs_stat -t
Name           Type           Target    State     Host
————————————————————
ora.DATA.dg    ora….up.type ONLINE    ONLINE    dhcppc1
ora.DG1.dg     ora….up.type ONLINE    OFFLINE
ora.FRA.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora….ER.lsnr ora….er.type ONLINE    ONLINE    dhcppc1
ora.OCR.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora.asm        ora.asm.type   ONLINE    ONLINE    dhcppc1
ora.cssd       ora.cssd.type  ONLINE    ONLINE    dhcppc1
ora.diskmon    ora….on.type ONLINE    ONLINE    dhcppc1
ora.testdb.db  ora….se.type ONLINE    OFFLINE
[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
ORA-15032: not all alterations performed
ORA-15017: diskgroup “DG1” cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup “DG1”

CRS-2674: Start of ‘ora.DG1.dg’ on ‘dhcppc1’ failed
[oracle@dhcppc1 ~]$ crs_stat -t
Name           Type           Target    State     Host
————————————————————
ora.DATA.dg    ora….up.type ONLINE    ONLINE    dhcppc1
ora.DG1.dg     ora….up.type ONLINE    OFFLINE
ora.FRA.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora….ER.lsnr ora….er.type ONLINE    ONLINE    dhcppc1
ora.OCR.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora.asm        ora.asm.type   ONLINE    ONLINE    dhcppc1
ora.cssd       ora.cssd.type  ONLINE    ONLINE    dhcppc1
ora.diskmon    ora….on.type ONLINE    ONLINE    dhcppc1
ora.testdb.db  ora….se.type ONLINE    OFFLINE

[oracle@dhcppc1 ~]$ srvctl disable diskgroup -g dg1
[oracle@dhcppc1 ~]$ srvctl remove diskgroup -g dg1
[oracle@dhcppc1 ~]$ crs_stat -t
Name           Type           Target    State     Host
————————————————————
ora.DATA.dg    ora….up.type ONLINE    ONLINE    dhcppc1
ora.FRA.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora….ER.lsnr ora….er.type ONLINE    ONLINE    dhcppc1
ora.OCR.dg     ora….up.type ONLINE    ONLINE    dhcppc1
ora.asm        ora.asm.type   ONLINE    ONLINE    dhcppc1
ora.cssd       ora.cssd.type  ONLINE    ONLINE    dhcppc1
ora.diskmon    ora….on.type ONLINE    ONLINE    dhcppc1
ora.testdb.db  ora….se.type ONLINE    OFFLINE
[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-2640: Required resource ‘ora.DG1.dg’ is missing.
[oracle@dhcppc1 ~]$ srvctl config database -d testdb
Database unique name: testdb
Database name: testdb
Oracle home: /u01/app/11.2.0/db
Oracle user: oracle
Spfile: +DATA/testdb/spfiletestdb.ora
Domain: testing.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
[oracle@dhcppc1 ~]$

[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-2640: Required resource ‘ora.DG1.dg’ is missing.
[oracle@dhcppc1 ~]$ srvctl remove diskgroup -g DG1 -f
PRCR-1001 : Resource ora.DG1.dg does not exist
[oracle@dhcppc1 ~]$ crsctl status resource ora.DG1.dg -f
CRS-210: Could not find resource ‘ora.DG1.dg’.

[oracle@dhcppc1 ~]$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-2640: Required resource ‘ora.DG1.dg’ is missing.
[oracle@dhcppc1 ~]$ srvctl status database -d testdb
Database is not running.
[oracle@dhcppc1 ~]$ srvctl stop database -d testdb
PRCC-1016 : testdb was already stopped
[oracle@dhcppc1 ~]$

[oracle@dhcppc1 ~]$ srvctl modify database -d testdb -a ‘DATA,FRA,OCR’
[oracle@dhcppc1 ~]$ srvctl status database -d testdb
Database is not running.
[oracle@dhcppc1 ~]$ srvctl start database -d testdb
[oracle@dhcppc1 ~]$ srvctl status database -d testdb
Database is running.
[oracle@dhcppc1 ~]$

Hope it helps
SRI

Posted in ASM, Troubleshooting | Leave a Comment »

ORA-02298 – parent keys not found

Posted by Srikrishna Murthy Annam on September 22, 2010

SQL> sho user
USER is “SH”
SQL> ALTER TABLE emp_det ADD CONSTRAINT fk_emp_det FOREIGN KEY ( name ) REFERENCES emp( name );
ALTER TABLE emp_det ADD CONSTRAINT fk_emp_det FOREIGN KEY ( name ) REFERENCES emp( name )
*
ERROR at line 1:
ORA-02298: cannot validate (SH.FK_EMP_DET) – parent keys not found

SQL>

In the above example, we are trying to create a foreign constraint on the table emp_det referencing column name in the table emp. But the error shows that there are some emp records exists in emp_det table for which there is no master entries in the table EMP.

So when ever we are creating foreign constraint on the detailed table(emp_det), all the column values which are referencing the master table should exist in the master table.

Troubleshooting

SQL> select unique name from emp_det ed
2  where ed.name is not null and not exists
3  (select null from emp e where e.name = ed.name);

NAME
——————————–
John
Marry
Farah

SQL>

SQL> select count(1) from emp_det where name in (‘JOHN’,’MARRY’,’FARAH’);

COUNT(1)
———-
162

SQL> select count(1) from emp where name in (‘JOHN’,’MARRY’,’FARAH’);

COUNT(1)
———-
0

SQL>

WorkAround

1) Need to delete the rows in the table emp_det table corresponding to the above specified Names
or
2) Insert the data in the table emp corresponding to the above specified Names

Hope it helps

SRI

Posted in Troubleshooting | Tagged: , , , , , | Leave a Comment »

 
%d bloggers like this: