How to Take initial snapshot of number of objects

October 17, 2008 Leave a comment

 Take initial snapshot of number of objects, grants and space in empty database instance:

 select *
from (select count(*) num_objects from dba_objects) a,
(select round(sum(bytes)/1024/1024) free_mb from dba_free_space) b,
(select count(*) sys_privs from dba_sys_privs) c,
(select count(*) tab_privs from dba_tab_privs) d,
(select count(*) role_privs from dba_role_privs) e;

Find objects space

select owner, count(*), sum(bytes)/1024/1024 size_mb from dba_segments group by owner order by 1;

Advertisements
Categories: Uncategorized

USE full Oracle Stuff

October 17, 2008 Leave a comment

Invalid Objects count :

select owner, object_type, count(*) from dba_objects
where owner in (‘APPS’)
and status = ‘INVALID’
group by owner, object_type;

Constraints count on :

select owner, count(*) from dba_constraints
where owner in (‘APPS’)
group by owner;

Invalid objects count group by owner

select owner, status, count(*) from dba_objects group by owner, status order by status, owner;

select count(*) from dba_tablespaces;

 Find the Latest CPU patches intalled:
 

 select * from registry$history;

Write a script to generate maltiple Lines
select ‘alter database datafile ”’||file_name||”’ AUTOEXTEND ON NEXT 10M MAXSIZE 2047M;’ from dba_data_files order by 1;

Categories: Uncategorized

Find a Lock on a table :

October 17, 2008 Leave a comment
Categories: Uncategorized

How to Check the conncurrent mangers are running or not from ORACLE:

October 17, 2008 Leave a comment

Check the conncurrent mangers are running or not from back-end ORACLE

SELECT DISTINCT Concurrent_Process_Id CpId, PID Opid,
Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,
P.process_status_code Status,
TO_CHAR(P.Process_Start_Date, ‘MM-DD-YYYY HH:MI:SSAM’) Started_At
FROM   Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process
WHERE  Q.Application_Id = Queue_Application_ID
AND  Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
AND  Spid = Os_Process_ID
AND  Process_Status_Code NOT IN (‘K’,’S’)
ORDER  BY Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name;

Categories: Uncategorized

Finding the Patchset level

October 17, 2008 Leave a comment

Find Patchset level:

A script enabled browser is required for this page to function properly.

select a.APPLICATION_NAME ,b.APPLICATION_SHORT_NAME ,c.patch_level
from fnd_application_tl a,fnd_application b,fnd_product_installations c
where a.APPLICATION_ID = b.APPLICATION_ID
and b.APPLICATION_ID =c.APPLICATION_ID
and a.LANGUAGE =’US’
and b.APPLICATION_SHORT_NAME =’&x’

Find the Application TECH STACK:
SET head off Lines 120 pages 100
col n_patch format A65
col bug_number format A10
col patch_name format A10
spool atg_pf_ptch_level.txt
select ‘ atg_pf ‘ FROM dual;
/

select bug_number, decode(bug_number,
‘3438354’, ’11i.ATG_PF.H’
,’4017300′ ,’11i.ATG_PF.H.RUP1′
,’4125550′ ,’11i.ATG_PF.H.RUP2′
,’4334965′ ,’11i.ATG_PF.H RUP3′
,’4676589′ ,’11i.ATG_PF.H RUP4′
,’5382500′ ,’11i.ATG_PF.H RUP5 HELP’
,’5473858′ ,’11i.ATG_PF.H.5′
,’5674941′ ,’11i.ATG_PF.H RUP5 SSO Integrat’
,’5903765′ ,’11i.ATG_PF.H RUP6′
,’6117031′ ,’11i.ATG_PF.H RUP6 SSO 10g Integration’
,’6330890′ ,’11i.ATG_PF.H RUP6 HELP’
) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number
IN ( ‘3438354’, ‘4017300’, ‘4125550’, ‘4334965’, ‘4676589’, ‘5382500’, ‘5473858’, ‘5674941’, ‘5903765’, ‘6117031’, ‘6330890’ );

Categories: Uncategorized

SOA Error : It is not advisable to execute any operations on this soa-infra until this warning goes away.

May 11, 2016 Leave a comment
Warning
Initializing SOA…

Even though this soa-infra target is up, some SOA Fabric components and composite applications are still loading. You may need to allow some time for the initialization to complete, and later click the Refresh Page icon.

It is not advisable to execute any operations on this soa-infra until this warning goes away.

soaerror

Initializing SOA…

Even though this soa-infra target is up, some SOA Fabric components and composite applications are still loading. You may need to allow some time for the initialization to complete, and later click the Refresh Page icon.

It is not advisable to execute any operations on this soa-infra until this warning goes away.

 

Sol: At this time we cant perform any activity like undeploy the composites. Only thing is remove all composites from unix.

Download for ShareSoaInfraPartition.ear from below

 

We have followed the Meta link note 1380835.1 to un-deploy the corrupted composite and bring up the soa-infra application.

Thought of sharing the same here, it may help whoever facing the same issue.

The SOA composites can’t be un-deployed when the soa-infra application. The below steps will help you to un-deploy the SOA composites when the soa-infra application is down.

  • First check the soa logs and identify which composite is causing the problem
  • Download and copy the ShareSoaInfraPartition.ear file to $MWHOME/Oracle_SOA/common/bin
  • Execute $MWHOME/Oracle_SOA/common/bin/wlst.sh
  •  connect()

Provide username, password and server URL

  • Run the below command to deploy ShareSoaInfraPartition.ear

deploy(‘ShareSoaInfraPartition’,’ShareSoaInfraPartition.ear’,upload=’true’)

  • Now run the below command by changing the “toLocation” (‘/fmw11g/Middleware’ is some location path on SOA machine)

EXAMPLE:

-rwxrwxrwx. 1 oracle oinstall 692224 May 10 16:15 ShareSoaInfraPartition.ear
[STG:oracle@xxxxxxx:/opt/app/oracle/11g/mwhome/oracle_common/common/bin]$ . ./wlst.sh

CLASSPATH=/opt/app/oracle/11g/mwhome/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/opt/app/oracle/11g/mwhome/patch_ocp371/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/opt/app/oracle/11g/mwhome/jdk/lib/tools.jar:/opt/app/oracle/11g/mwhome/wlserver_10.3/server/lib/weblogic_sp.jar:/opt/app/oracle/11g/mwhome/wlserver_10.3/server/lib/weblogic.jar:/opt/app/oracle/11g/mwhome/modules/features/weblogic.server.modules_10.3.6.0.jar:/opt/app/oracle/11g/mwhome/wlserver_10.3/server/lib/webservices.jar:/opt/app/oracle/11g/mwhome/modules/org.apache.ant_1.7.1/lib/ant-all.jar:/opt/app/oracle/11g/mwhome/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar::/opt/app/oracle/11g/mwhome/oracle_common/modules/oracle.jrf_11.1.1/jrf-wlstman.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/lib/adfscripting.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/lib/adf-share-mbeans-wlst.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/lib/mdswlst.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/auditwlst.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/igfwlsthelp.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/jps-wlst.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/jps-wls-trustprovider.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/jrf-wlst.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/oamap_help.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/oamAuthnProvider.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/ossoiap_help.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/ossoiap.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/ovdwlsthelp.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/sslconfigwlst.jar:/opt/app/oracle/11g/mwhome/oracle_common/common/wlst/resources/wsm-wlst.jar:/opt/app/oracle/11g/mwhome/utils/config/10.3/config-launch.jar::/opt/app/oracle/11g/mwhome/wlserver_10.3/common/derby/lib/derbynet.jar:/opt/app/oracle/11g/mwhome/wlserver_10.3/common/derby/lib/derbyclient.jar:/opt/app/oracle/11g/mwhome/wlserver_10.3/common/derby/lib/derbytools.jar::

Initializing WebLogic Scripting Tool (WLST) …

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands
wls:/offline> connect()
Please enter your username :soaadmin
Please enter your password :xxxxxxx
Please enter your server URL [t3://localhost:7001] :t3://xxxxxx.xxx.xxxxxx.com:60000
Connecting to t3://xxxxx.staging.xxxxxx.com:60000 with userid soaadmin …
Successfully connected to Admin Server ‘soa_adminserver’ that belongs to domain ‘soa_domain’.

Warning: An insecure protocol was used to connect to the
server. To ensure on-the-wire security, the SSL port or
Admin port should be used instead.

wls:/soa_domain/serverConfig> deploy(‘ShareSoaInfraPartition’,’ShareSoaInfraPartition.ear’,upload=’true’)
Deploying application from /opt/app/oracle/11g/mwhome/oracle_common/common/bin/ShareSoaInfraPartition.ear to targets (upload=true) …

<May 10, 2016 4:51:58 PM MDT> <Info> <J2EE Deployment SPI> <BEA-260121> <Initiating deploy operation for application, ShareSoaInfraPartition [archive: /opt/app/oracle/11g/mwhome/oracle_common/common/bin/ShareSoaInfraPartition.ear], to soa_adminserver .>
…..Completed the deployment of Application with status completed
Current Status of your Deployment:
Deployment command type: deploy
Deployment State : completed
Deployment Message : [Deployer:149194]Operation ‘deploy’ on application ‘ShareSoaInfraPartition’ has succeeded on ‘soa_adminserver’

 

wls:/soa_domain/serverConfig> exportMetadata(application=’ShareSoaInfraPartition’,server=’soa_adminserver’,toLocation=’/tmp’,docs=’/deployed-composites/deployed-composites.xml’)

Executing operation: exportMetadata.

Operation “exportMetadata” completed. Summary of “exportMetadata” operation is:
List of documents successfully transferred:

/deployed-composites/deployed-composites.xml

1 documents successfully transferred.
wls:/soa_domain/serverConfig>
wls:/soa_domain/serverConfig> importMetadata(application=’ShareSoaInfraPartition’,server=’soa_adminserver’,fromLocation=’/tmp’,docs=’/deployed-composites/deployed-composites.xml’)

Executing operation: importMetadata.

Operation “importMetadata” completed. Summary of “importMetadata” operation is:
List of documents successfully transferred:

/deployed-composites/deployed-composites.xml

1 documents successfully transferred.
wls:/soa_domain/serverConfig>

 

 

Categories: SOA issues

ASM FAQs

February 9, 2016 Leave a comment

1) What is ASM?

In Oracle Database 10g/11g there are two types of instances: database and ASM instances. The ASM instance, which is generally named +ASM, is started with the INSTANCE_TYPE=ASM init.ora parameter. This parameter, when set, signals the Oracle initialization routine to start an ASM instance and not a standard database instance. Unlike the standard database instance, the ASM instance contains no physical files; such as logfiles, controlfiles or datafiles, and only requires a few init.ora parameters for startup.

Upon startup, an ASM instance will spawn all the basic background processes, plus some new ones that are specific to the operation of ASM. The STARTUP clauses for ASM instances are similar to those for database instances. For example, RESTRICT prevents database instances from connecting to this ASM instance. NOMOUNT starts up an ASM instance without mounting any disk group. MOUNT option simply mounts all defined diskgroups

For RAC configurations, the ASM SID is +ASMx instance, where x represents the instance number.

2) What are the key benefits of ASM?

ASM provides filesystem and volume manager capabilities built into the Oracle database kernel. Withthis capability, ASM simplifies storage management tasks, such as creating/laying out databases and disk space management. Since ASM allows disk management to be done using familiar create/alter/drop SQL statements, DBAs do not need to learn a new skill set or make crucial decisions on provisioning.

The following are some key benefits of ASM:

  • ASM spreads I/O evenly across all available disk drives to prevent hot spots and maximize performance.
  • ASM eliminates the need for over provisioning and maximizes storage resource utilization facilitating database consolidation.
  • Inherent large file support.
  • Performs automatic online redistribution after the incremental addition or removal of storage  capacity.
  • Maintains redundant copies of data to provide high availability, or leverages 3rd party RAID functionality.
  • Supports Oracle Database as well as Oracle Real Application Clusters (RAC).
  • Capable of leveraging 3rd party multipathing technologies.
  • For simplicity and easier migration to ASM, an Oracle database can contain ASM and non-ASM files.
  • Any new files can be created as ASM files whilst existing files can also be migrated to ASM.
  • RMAN commands enable non-ASM managed files to be relocated to an ASM disk group.
  • Enterprise Manager Database Control or Grid Control can be used to manage ASM disk and file activities.

3) Describe about ASM architecture.

Automatic Storage Management (ASM) instance

Instance that manages the diskgroup metadata

Disk Groups Logcal grouping of disks

Determines file mirroring options

ASM Disks LUNs presented to ASM

ASM Files Files that are stored in ASM disk groups are called ASM files, this includes database files

Notes:

Many databases can connect as clients to single ASM instances

ASM instance name should only be +ASM only

One diskgroup can serve many databases

4) How does database connects to ASM Instance?

The database communicates with ASM instance using the ASMB (umblicus process) process. Once the database obtains the necessary extents from extent map, all database IO going  forward is processed through by the database processes, bypassing ASM. Thus we say ASM is not really in the IO path. So, the question how do we make ASM go faster…..you don’t have to.

4) What init.ora parameters does a user need to configure for ASM instances?

The default parameter settings work perfectly for ASM. The only parameters needed for 11g ASM:

  • PROCESSES*
  • ASM_DISKSTRING*
  • ASM_DISKGROUPS
  • INSTANCE_TYPE

5) How does the database interact with the ASM instance and how do I make ASM go faster?

ASM is not in the I/O path so ASM does not impede the database file access. Since the RDBMS instance is performing raw I/O, the I/O is as fast as possible.

6) Do I need to define the RDBMS FILESYSTEMIO_OPTIONS parameter when I use ASM?

No. The RDBMS does I/O directly to the raw disk devices, the FILESYSTEMIO_OPTIONS  parameter is only for filesystems.

7) Why Oracle recommends two diskgroups?

Oracle recommends two diskgroups to provide a balance of manageability, utilization, and performance.

8) We have a 16 TB database. I’m curious about the number of disk groups we should use; e.g. 1 large disk group, a couple of disk groups, or otherwise?

For VLDBs you will probably end up with different storage tiers; e.g with some of our large customers they have Tier1 (RAID10 FC), Tier2 (RAID5 FC), Tier3 (SATA), etc. Each one of these is mapped to a diskgroup.

9) We have a new app and don’t know our access pattern, but assuming mostly sequential access, what size would be a good AU fit?

For 11g ASM/RDBMS it is recommended to use 4MB ASM AU for disk groups. See Metalink Note 810484.1

10) Would it be better to use BIGFILE tablespaces, or standard tablespaces for ASM?

The use of Bigfile tablespaces has no bearing on ASM (or vice versa). In fact most database object related decisions are transparent to ASM.

11) What is the best LUN size for ASM?

There is no best size! In most cases the storage team will dictate to you based on their standardized LUN size. The ASM administrator merely has to communicate the ASM Best Practices and application  characteristics to storage folks :

  • Need equally sized / performance LUNs
  • Minimum of 4 LUNs
  • The capacity requirement
  • The workload characteristic (random r/w, sequential r/w) & any response time SLA

Using this info , and their standards, the storage folks should build a nice LUN group set for you.

12) In 11g RAC we want to separate ASM admins from DBAs and create different users and groups. How do we set this up?

For clarification

  • Separate Oracle Home for ASM and RDBMS.
  • RDBMS instance connects to ASM using OSDBA group of the ASM instance.

Thus, software owner for each RDBMS instance connecting to ASM must be

a member of ASM’s OSDBA group.

  • Choose a different OSDBA group for ASM instance (asmdba) than for

RDBMS instance (dba)

  • In 11g, ASM administrator has to be member of a separate SYSASM group to

separate ASM Admin and DBAs.

13) Can my RDBMS and ASM instances run different versions?

Yes. ASM can be at a higher version or at lower version than its client databases. There’s two

components of compatiblity:

Software compatibility

Diskgroup compatibility attributes:compatible.asm

compatible.rdbms

14) Where do I run my database listener from; i.e., ASM HOME or DB HOME?

It is recommended to run the listener from the ASM HOME. This is particularly important for RAC env, since the listener is a node-level resource. In this config, you can create additional [user] listeners from the database homes as needed.

15) How do I backup my ASM instance?

Not applicable! ASM has no files to backup, as its does not contain controlfile,redo logs etc.

16) When should I use RMAN and when should I use ASMCMD copy?

    • RMAN is the recommended and most complete and flexible method to backup and transport database files in ASM.

ASMCMD copy is good for copying single files

  • Supports all Oracle file types
  • Can be used to instantiate a Data Guard environment
  • Does not update the controlfile
  • Does not create OMF files

17) I’m going to do add disks to my ASM diskgroup, how long will this rebalance take?

    • Rebalance time is heavily driven by the three items:

1) Amount of data currently in the diskgroup

2) IO bandwidth available on the server

3) ASM_POWER_LIMIT or Rebalance Power Level

18) We are migrating to a new storage array. How do I move my ASM database from storage A to storage B?

Given that the new and old storage are both visible to ASM, simply add the new disks to the ASM disk group and drop the old disks. ASM rebalance will migrate data online.

Note 428681.1 covers how to move OCR/Voting disks to the new storage array

19) Is it possible to unplug an ASM disk group from one platform and plug into a server on another platform (for example, from Solaris to Linux)?

No. Cross-platform disk group migration not supported. To move datafiles between endian-ness platforms, you need to use XTTS, Datapump or Streams.

20) How does ASM work with multipathing software?

It works great! Multipathing software is at a layer lower than ASM, and thus is transparent.

You may need to adjust ASM_DISKSTRING to specify only the path to the multipathing pseudo devices.

21) Is ASM constantly rebalancing to manage “hot spots”?

No…No…Nope!! ASM provides even distribution of extents across all disks in a disk group. Since each disk will equal number of extents, no single disk will be hotter than another. Thus the answer NO, ASM does not dynamically move hot spots, because hot spots simply do not

occur in ASM configurations. Rebalance only occurs on storage configuration changes (e.g. add, drop, or resize disks).

  1. Q) What are the file types that ASM support and keep in disk groups?
  • Control files
  • Flashback logs
  • Data Pump dump sets
  • Data files
  • DB SPFILE
  • Data Guard configuration
  • Temporary data files
  • RMAN backup sets
  • Change tracking bitmaps
  • Online redo logs
  • RMAN data file copies
  • OCR files
  • Archive logs
  • Transport data files
  • ASM SPFILE
  1. Q) List Key benefits of ASM?
  • Stripes files rather than logical volumes
  • Provides redundancy on a file basis
  • Enables online disk reconfiguration and dynamic rebalancing
  • Reduces the time significantly to resynchronize a transient failure by tracking changes while disk is offline
  • Provides adjustable rebalancing speed
  • Is cluster-aware
  • Supports reading from mirrored copy instead of primary copy for extended clusters
  • Is automatically installed as part of the Grid Infrastructure
  1. Q) What is ASM Striping?

ASM can use variable size data extents to support larger files, reduce memory requirements, and improve performance.

Each data extent resides on an individual disk.

Data extents consist of one or more allocation units.

The data extent size is:

  • Equal to AU for the first 20,000 extents (0–19999)
  • Equal to 4 × AU for the next 20,000 extents (20000–39999)
  • Equal to 16 × AU for extents above 40,000

ASM stripes files using extents with a coarse method for load balancing or a fine method to reduce latency.

  • Coarse-grained striping is always equal to the effective AU size.
  • Fine-grained striping is always equal to 128 KB.
  1. Q) How many ASM Diskgroups can be created under one ASM Instance?

ASM imposes the following limits:

  • 63 disk groups in a storage system
  • 10,000 ASM disks in a storage system
  • Two-terabyte maximum storage for each ASM disk (non-Exadata)
  • Four-petabyte maximum storage for each ASM disk (Exadata)
  • 40-exabyte maximum storage for each storage system
  • 1 million files for each disk group
  • ASM file size limits (database limit is 128 TB):
  1. External redundancy maximum file size is 140 PB.
  2. Normal redundancy maximum file size is 42 PB.
  3. High redundancy maximum file size is 15 PB.

27) What is a diskgroup?

A disk group consists of multiple disks and is the fundamental object that ASM manages. Each disk group contains the metadata that is required for the management of space in the disk group. The ASM instance manages the metadata about the files in a Disk Group in the same way that a file system manages metadata about its files. However, the vast majority of I/O operations do not pass through the ASM instance. In a moment we will look at how file

I/O works with respect to the ASM instance.

  1. Q) Diagram that how database interacts with ASM when a request is to read or open a datafile.

1A. Database issues open of a database file

1B. ASM sends the extent map for the file to database instance. Starting with 11g, the RDBMS only receives first 60 extents the remaining extents in the extent map are paged in on demand, providing a faster open

2A/2B. Database now reads directly from disk

3A.RDBMS foreground initiates a create tablespace for example

3B. ASM does the allocation for its essentially reserving the allocation units

for the file creation

3C. Once allocation phase is done, the extent map is sent to the RDBMS

3D. The RDBMS initialization phase kicks in. In this phase the initializes all

the reserved AUs

3E. If file creation is successful, then the RDBMS commits the file creation

Going forward all I/Os are done by the RDBMS directly.

  1. Q) Can my disks in a diskgroup can be varied size? For example one disk is of 100GB and another disk is of 50GB. If so how does ASM manage the extents?

Yes, disk sizes can be varied, Oracle ASM will manage data efficiently and intelligent by placing the extents proportional to the size of the disk in the disk group, bigger diskgroups have more extents than lesser ones.

Here I would like to share one of best Oracle ASM feature known as “ASM Fast Mirror Resync”

111

  • Fraction of time to re-establish redundancy
  • Only changed blocks are resynced.
Categories: Uncategorized

ResourceLimitException : No resources currently available in pool to allocate to applications – Oracle SOA 11g

November 20, 2015 Leave a comment

ResourceLimitException : No resources currently available in pool to allocate to applications – Oracle SOA 11g

You could have observed the below exception frequently in the weblogic server log file, weblogic server throws the below exception when there is not enough database connections in the pool to serve the incoming requests reporting increasing the connection pool count. This will affect the overall system  performance
java.sql.SQLException: Internal error: Cannot obtain XAConnection weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool SOADataSource to allocate to applications, please increase the size of the pool and retry..
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResourceInternal(ResourcePoolImpl.java:577)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:342)
at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:329)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:417)
at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:324)

The above exception is due to there is not enough connections available in the pool to service the requests.
To identify which datasource / connection pool requires more connection do the following.

  • Go to Weblogic Admin console
  • Go to Environment -> Servers
  • Select the server on which the connection pool needs to be monitored
  • Go to Monitoring -> JDBC
  • Customize the table to add the below two properties

 

test1

This page will list all the datasources/ connection pools targeted to that particular server and how many connections are waiting to get the connection. If more number of connections are waiting, that is not a good which will cause performance impact and also cause stuck threads on the server if the wait time is going to be beyond the thread stuck time (By default it’s 10 mins).

 

test2

(This screen shot taken by reducing the max capacity to 5 for blogging purpose, unfortunately I did not captured the actual screen shot)

 

test3

Above screenshot shows SOADataSource state is “Overloaded” and also the current wait is 19. There are 19 resources waiting for connection . The above setup is definitely a problem which will result database calls performance issue.

If the connection pool count is not increased with respect to the invoker thread count, then it will cause this kind of contention cause lot of calls to be waiting for connection.

test4

Solution to the above problem is to increase the number of connections in the connection pool.

Steps:

  • Go to WLS Console
  • Select Services -> Datasources -> select the datasource you want to change the pool size
  • Go to Configuration -> connection pool
  • Change the Maximum Capacity to the count which is required for your environment.

test5

 

Categories: Uncategorized

Weblogic Managed server status in STARTING

November 19, 2015 Leave a comment

Weblogic Managed server status struck in STARTING

We faced the issue while starting the weblogic managed server, the status of the server struck in STARTING.

W could not able to find a valid error messages in the log files

Managed Server Log File:

<30-Oct-2014 11:13:29 o’clock GMT> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to STARTING> 

No logs are getting printed after this.

Node Manager Log File:

<30-Oct-2014 11:09:44> <INFO> <SOACoreDomain> <MS1> <Server failed during startup so will not be restarted>

<30-Oct-2014 11:09:44> <WARNING> <Exception while starting server ‘MS1’>

java.io.IOException: Server failed to start up. See server output log for more details.

      at weblogic.nodemanager.server.AbstractServerManager.start(AbstractServerManager.java:200)

      at weblogic.nodemanager.server.ServerManager.start(ServerManager.java:23)

      at weblogic.nodemanager.server.Handler.handleStart(Handler.java:604)

      at weblogic.nodemanager.server.Handler.handleCommand(Handler.java:121)

      at weblogic.nodemanager.server.Handler.run(Handler.java:71)

      at java.lang.Thread.run(Thread.java:662)

The root cause of this issue is somehow the ldap directory of the server got corrupted.

To resolve this issue:

  •  Kill the managed server
  •  Remove the ldap folder from the following location  <<DOMAIN_HOME>>/servers/<<Managed Server>>, this file will be auto generated while restarting the server.
  • Restart the server
Categories: Uncategorized

How to Avoid Long Delay during Login to EM console

November 19, 2015 4 comments

Enable Discovery Cache to Avoid Long Delay during Login to EM console – Oracle SOA Suite 11g

Enable Discovery Cache to Avoid Long Delay during Login to EM console in Oracle SOA Suite 11g

Logging into Enterprise Manager of Fusion Middleware Control 11g (fmwctl) takes a long time, the fmwctl discovery is always performed as part of login.  For installations with thousands of targets (e.g. Composites), fmwctl discovery may take 45-60 seconds. This time is expected because EM discovery Mbeans need to be invoked for every target. If the number of fmwctl monitored targets (e.g. Deployed Composites) increases, the login time will go up as well.

Solution is to cache the discovery results in the servlet context and use it for subsequent logins. This discovery result will be shared by all the fmwctl users. This will still require the entire discovery to be done at least once.

New parameters are added to enable/disable and to control the usage of the cached discovery results.

  • If the caching is enabled, fmwctl will use the cached discovery results.
  • The default setting is “not use the cached results”

We have observed this issue in Oracle SOA Suite version 11.1.1.5.0 and Oracle suggested the patch 13251077 to enable the fmwctl discovery cache.

Steps to enable fmwctl discovery cache:

  1. Upgrade to FMw 11.1.1.6 or apply available patch 13251077 for your version.
    If applying patch 13251077, set ORACLE_HOME to MW_HOME/oracle_common before applying.
  2. Navigate to fmwctl System mBean browser.

untitled

  SOA-INFRA ->Administration ->System MBean Browser

  1.           3. Access following AdminServer mBean for setting the cache property.
    • emoms.props:Location=AdminServer,name=emoms.properties,type=Properties,Application=em
    • Select Operations, Setproperty

untitled (1)

emoms.props->emoms.properties : setProperty in Operations.

  1. Setting following three properties.  Unless using non-default values, the last two properties are optional.

 

untitled (2)

# Enable caching of FMw Discovery data and use it for other subsequent users.
# Values=true/false   Default=false
oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_USE_CACHED_RESULTS=true

# If caching of discovery data is true, this parameter indicates how long the discovery data
# from cache should be used before requiring a fresh discovery.
# Time value is in milliseconds.  Default is 7200000 milliseconds.
oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_MAX_CACHE_AGE=7200000

# If caching of discovery data is true, a user logs in and a discovery session is in progress,
# this parameter indicates how long the user can wait for current discovery to complete.
# After this wait time is elapsed and discovery is still not finished:  If there is already data
# in cache it will be used, else the user will launch a new discovery session.
# Time value is in milliseconds.  Default is 10000 milliseconds.
oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_MAX_WAIT_TIME=10000

    • Following is example of setting oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_USE_CACHED_RESULTS=true
  1. If new targets (e.g. new Composites are deployed) are added after enabling discovery cache and new targets are not displayed in fmwctl, perform a manual refresh of the Farm to update the discovery cache.
Categories: Uncategorized

How to Troubleshoot Connectivity Issue with 11gR2 SCAN

November 19, 2015 Leave a comment

Installed the Oracle 11g RAC successfully, But when tried connecting from remote client via SCAN, it used to raise ORA-12537. This is the most common issues that the happens with SCAN listener. And there are few common mistakes that generally cause this issue..
So, here is how we can troubleshoot the connectivity issues with SCAN, and cases out the possibilities.

1) Check if Local_listener and remote_listener parameter are set properly on all nodes.

2) The very common issue is with permissions. SCAN will always be created under grid user (Grid cluserware installation user). Oracle will also create one local listener “LISTENER” during grid infrastructure installation. But if that is not present then always make sure that you create a local listener with grid user. This is required to handover the connection between remote and local listener.

3) Also “oracle” executable should have given to oracle and grid user i.e. 6751. Under $ORACLE_HOME/bin. If permission are not proper the use root user to set the permissions. Else it will raise error (ORA-12537, )
-rwsr-s–x 1 oracle oinstall 106M Aug 25 2012 oracle

4) Your database instance is registered with Local Listener?

5) Check if scan config are proper and showing the details similar to :
[oracle@mynode] srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521
[oracle@mynode] srvctl config scan
SCAN name: sales1-scan, Network: 1/133.22.67.0/255.255.255.0/
SCAN VIP name: scan1, IP: /sales1-scan.example.com/133.22.67.192
SCAN VIP name: scan2, IP: /sales1-scan.example.com/133.22.67.193
SCAN VIP name: scan3, IP: /sales1-scan.example.com/133.22.67.194

6) Check if all above 3 SCAN IPs are responding the ping.

7) Your Oracle Client must be 11g R2 and above release in order to use SCAN functionality.

8) Following is the Oracle Document ID 975457.1. Which helped me for ORA-12537. Last case was valid for me.

9) If the following all settings are proper then try to enable SQL Net tracing on Client site and check if the packer size between client and server are compatible.
(This you can confirm by connecting the oracle with normal connection. i.e. try using SQL>conn user/password@server-ip/oracle_sid. if this is not working then you need to check packet size on TCP else you have oracle client 11gR2 then you may need to troubleshoot it with https://support.oracle.com only J)
How to Troubleshoot Connectivity Issue with 11gR2 SCAN Name [ID 975457.1]
Applies to:
Oracle Server – Enterprise Edition – Version: 11.2.0.1 and later [Release: 11.2 and later ]
Information in this document applies to any platform.
Goal
The goal of this document is to provide checklist when connection through 11gR2 Grid Infrastructure (CRS) SCAN name to database fails.
Solution
Troubleshooting Steps
When client program connects to RAC database through SCAN name, SCAN listener will accept t he request and redirect the connection to local listener. To identify connection issue, first try to connect to each local listener through node VIP, then try each SCAN listener through each SCAN VIP.

To test through node VIP:
sqlplus <username>/<password>@<nodename-vip.domain>:<local-listener-port>/<service-name>

Example:

sqlplus scott/tiger@racnode1-vip.us.eot.com:1521/testsvc

Repeat the same test for all local listener/node VIP in the cluster.

If GNS is used, node VIP name will be in the format of nodename-vip.gnssubdomain (example racnode1-vip.us.eot.com)

If connection through local listener fails, check whether service/instance is registered properly to that local listener with “lsnrctl service <local-listener-name>”.

To test through SCAN VIP address:
sqlplus <username>/<password>@<scan-ipn>:<scan-listener-port>/<service-name>

Example:

sqlplus scott/tiger@120.0.0.205:1521/testsvc

Note it’s IP address instead of SCAN name

Repeat the same command for all SCAN IP
If connection through SCAN listener fails, check whether service/instance is registered properly to that SCAN listener with “lsnrctl service <SCAN-listener-name>”.

Other client tool (JDBC or such) can also be used to test connection though sqlplus is preferred for the purpose of testing.
Example Output
Configuration

Below is an example output from a 2-node cluster with the following configuration:
SCAN name and VIP:
nslookup eotcs.us.oracle.com
..
Name: eotcs.us.oracle.com
Address: 120.0.0.207
Name: eotcs.us.oracle.com
Address: 120.0.0.205
Name: eotcs.us.oracle.com
Address: 120.0.0.206
Ping doesn’t have to go through if ICMP is disabled but should return correct IP for corresponding name.
ping -c 1 eotcs.us.oracle.com
PING eotcs.us.oracle.com (120.0.0.207) 56(84) bytes of data.
64 bytes from 120.0.0.207: icmp_seq=1 ttl=64 time=3.37 ms
..

ping -c 1 eotcs.us.oracle.com
PING eotcs.us.oracle.com (120.0.0.206) 56(84) bytes of data.
64 bytes from 120.0.0.206: icmp_seq=1 ttl=64 time=1.85 ms
..

ping -c 1 eotcs.us.oracle.com
PING eotcs.us.oracle.com (120.0.0.205) 56(84) bytes of data.
64 bytes from 120.0.0.205: icmp_seq=1 ttl=64 time=2.45 ms
..

ping -c 1 eotcs
PING eotcs.us.oracle.com (120.0.0.207) 56(84) bytes of data.
64 bytes from eotcs.us.oracle.com (120.0.0.207): icmp_seq=1 ttl=64 time=3.18 ms
Node Public Name/IP Address
Name: eyrac1f.us.oracle.com Address: 120.0.0.111
Name: eyrac2f.us.oracle.com Address: 120.0.0.112

ping -c 1 eyrac1f.us.oracle.com
PING eyrac1f.us.oracle.com (120.0.0.111) 56(84) bytes of data.
64 bytes from eyrac1f.us.oracle.com (120.0.0.111): icmp_seq=1 ttl=64 time=3.36 ms
..

ping -c 1 eyrac2f.us.oracle.com
PING eyrac2f.us.oracle.com (120.0.0.112) 56(84) bytes of data.
64 bytes from eyrac2f.us.oracle.com (120.0.0.112): icmp_seq=1 ttl=64 time=3.37 ms
..
Nodes VIP Name/IP Address
Name: eyrac1fv.us.oracle.com Address: 120.0.0.211
Name: eyrac2fv.us.oracle.com Address: 120.0.0.212

nslookup eyrac1fv.us.oracle.com

..
Name: eyrac1fv.us.oracle.com
Address: 120.0.0.211

nslookup eyrac2fv.us.oracle.com

..
Name: eyrac2fv.us.oracle.com
Address: 120.0.0.212
ping -c 1 eyrac1fv.us.oracle.com
PING eyrac1fv.us.oracle.com (120.0.0.211) 56(84) bytes of data.
64 bytes from eyrac1fv.us.oracle.com (120.0.0.211): icmp_seq=1 ttl=64 time=4.04 ms
..

ping -c 1 eyrac2fv.us.oracle.com
PING eyrac2fv.us.oracle.com (120.0.0.212) 56(84) bytes of data.
64 bytes from eyrac2fv.us.oracle.com (120.0.0.212): icmp_seq=1 ttl=64 time=1.98 ms
..
Database Name: b2no
Service Name: sno
TNS Connection String
sno =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = eotcs.us.oracle.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = sno)
)
)

1. Checklist on RAC Cluster Nodes
Prior to the following checking, please set environment variable GRID_HOME to home of 11.2 Grid Infrastructure installation, for example:
GRID_HOME=/ogrid/gbase
export GRID_HOME

Please note:

* Oracle Network related files (sqlnet.ora, tnsnames.ora, listener.ora etc) are in $TNS_ADMIN or $ORACLE_HOME/network/admin if TNS_ADMIN is not set.
* This note assumes SCAN VIP and node VIP are all up and running (can be verified through “srvctl status nodeapps” and “srvctl status scan” or “crsctl stat res”), troubleshooting of SCAN VIP or node VIP startup issue is out of scope of this note.

A. SCAN Listener Resource Status
A1. SCAN Configuration:
$GRID_HOME/bin/srvctl config scan

SCAN name: eotcs.us.oracle.com, Network: 1/120.0.0.0/255.255.255.0/eth3
SCAN VIP name: scan1, IP: /120.0.0.206/120.0.0.206
SCAN VIP name: scan2, IP: /120.0.0.207/120.0.0.207
SCAN VIP name: scan3, IP: /120.0.0.205/120.0.0.205

A2. SCAN Listener Configuration:
$GRID_HOME/bin/srvctl config scan_listener
SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521
SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

A3. SCAN Listener Resource Status:
$GRID_HOME/bin/crsctl stat res -w “TYPE = ora.scan_listener.type”
NAME=ora.LISTENER_SCAN1.lsnr
TYPE=ora.scan_listener.type
TARGET=ONLINE
STATE=ONLINE on eyrac1f

NAME=ora.LISTENER_SCAN2.lsnr
TYPE=ora.scan_listener.type
TARGET=ONLINE
STATE=ONLINE on eyrac2f

NAME=ora.LISTENER_SCAN3.lsnr
TYPE=ora.scan_listener.type
TARGET=ONLINE
STATE=ONLINE on eyrac2f

B. SCAN Listener Status and Service
Log on to corresponding RAC node to find out SCAN listener status and service once SCAN listener resource status is confirmed. All SCAN listener should have same service served. Please set ORACLE_HOME environment variable prior to run any lsnrctl command, for example:
ORACLE_HOME=$GRID_HOME
export ORACLE_HOME
B1. SCAN Listener Status:
$GRID_HOME/bin/lsnrctl status LISTENER_SCAN2
..
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
STATUS of the LISTENER
————————
..
Listener Parameter File /ogrid/gbase/network/admin/listener.ora
Listener Log File /ogrid/gbase/log/diag/tnslsnr/eyrac2f/listener_scan2/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN2)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=120.0.0.207)(PORT=1521)))
..
B2. SCAN Listener Service:
$GRID_HOME/bin/lsnrctl service LISTENER_SCAN2
..
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))
Services Summary…
Service “b2no” has 2 instance(s).
Instance “b2no1”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=eyrac1fv)(PORT=1521)))
Instance “b2no2”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=eyrac2fv)(PORT=1521)))
Service “sno” has 2 instance(s).
Instance “b2no1”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=eyrac1fv)(PORT=1521)))
Instance “b2no2”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=eyrac2fv)(PORT=1521)))
C. Node Listener Status and Service
C1. Node Listener Status:
$GRID_HOME/bin/lsnrctl status LISTENER
..
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
————————
..
Listener Parameter File /ogrid/gbase/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/eyrac2f/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=120.0.0.112)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=120.0.0.212)(PORT=1521)))
Services Summary…
Service “b2no” has 1 instance(s).
Instance “b2no2”, status READY, has 1 handler(s) for this service…
Service “sno” has 1 instance(s).
Instance “b2no2”, status READY, has 1 handler(s) for this service…
C2. Node Listener Service:
$GRID_HOME/bin/lsnrctl service LISTENER
..
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary…
Service “b2no” has 1 instance(s).
Instance “b2no2”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “sno” has 1 instance(s).
Instance “b2no2”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
D. Database Service Status
D1. Service Resource Configuration
$GRID_HOME/bin/srvctl config service -d b2no -s sno -a

Service name: sno
Service is enabled
Server pool: b2no_sno
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 2
TAF failover delay: 20
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Preferred instances: b2no1,b2no2
Available instances:
D2. Service Resource Status:
$GRID_HOME/bin/srvctl status service -d b2no -s sno -v

Service sno is running on instance(s) b2no1,b2no2
E. Instance Listener Parameter Setting:
E1. remote_listener setting:
For 11gR2 database
SQL> show parameter remote_listener

NAME TYPE VALUE
———————————— ———– ——————————
remote_listener string eotcs.us.oracle.com:1521

For pre-11gR2 database
SQL> show parameter remote_listener

NAME TYPE VALUE
———————————— ———– ——————————
remote_listener string (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 120.0.0.206)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 120.0.0.207)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 120.0.0.205)(PORT = 1521)))

OR

remote_listener string LISTENERS_SCAN

Note tnsnames.ora must have the following entry for LISTENERS_SCAN

LISTENERS_SCAN =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 120.0.0.206)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 120.0.0.207)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 120.0.0.205)(PORT = 1521))
)

If sqlnet.ora does not contain EZCONNECT in NAMES.DIRECTORY_PATH list, remote_listener should set to LISTENERS_SCAN as in above example.
E2. local_listener setting:
For Instance1:
SQL> show parameter local_listener

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=eyra
c1fv)(PORT=1521))))

For Instance2:
SQL> show parameter local_listener

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=eyra
c2fv)(PORT=1521))))

2. Checklist on Client
A successful tnsping to TNS connection string for SCAN doesn’t necessarily mean the connection will be successful, client should be able to resolve to SCAN name, node VIP name

For supported client version, refer to section “VERSION AND BACKWARD COMPATIBILITY” of the following:

http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf
A. SCAN Name Resolution
nslookup and ping of SCAN name should return correct SCAN VIP(s), ORA-12545 could be reported if client can’t resolve SCAN name properly
B. Node VIP name:
By default, pfile/spfile parameter local_listener is set to short node VIP name instead of FQDN name, client need to be able to resolve to short VIP name as well as FQDN name; for example with following local_listener setting, client should be able to resolve short VIP name:
SQL> show parameter local_listener

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=eyra
c1fv)(PORT=1521))))

ping -c 1 eyrac1fv
PING eyrac1fv.us.oracle.com (120.0.0.211) 56(84) bytes of data.
64 bytes from eyrac1fv.us.oracle.com (120.0.0.211): icmp_seq=1 ttl=64 time=4.04 ms
..

ping -c 1 eyrac2fv
PING eyrac2fv.us.oracle.com (120.0.0.212) 56(84) bytes of data.
64 bytes from eyrac2fv.us.oracle.com (120.0.0.212): icmp_seq=1 ttl=64 time=1.98 ms
..
If client can resolve FQDN node VIP name but not short node VIP name (client in different domain), ORA-12537 could be reported and pfile/spfile local_listener need to be adjusted with FQDN node VIP name:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=eyrac1fv.us.oracle.com)(PORT=1521))))’ sid=’b2no1′;

SQL> show parameter local_listener

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=eyra
c1fv.us.oracle.com)(PORT=1521))))

Once instance updated local_listener setting to listeners, SCAN listener should have similar output like following:
$GRID_HOME/bin/lsnrctl service LISTENER_SCAN2
..
Services Summary…
Service “b2no” has 2 instance(s).
Instance “b2no1”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:4 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=eyrac1fv.us.oracle.com)(PORT=1521)))
Instance “b2no2”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:3 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=eyrac2fv)(PORT=1521)))
Service “sno” has 2 instance(s).
Instance “b2no1”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:4 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=eyrac1fv.us.oracle.com)(PORT=1521)))
Instance “b2no2”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:3 refused:0 state:ready
REMOTE SERVER
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=eyrac2fv)(PORT=1521)))

Note for node1 it’s FQDN name but for node2 it’s still short name as node2 is not updated yet

If for some reason, client can’t resolve FQDN node VIP name nor short node VIP name, pfile/spfile local_listener need to be adjusted with IP of VIP name:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=120.0.0.211)(PORT=1521))))’ sid=’b2no1′;

SQL> show parameter local_listener

NAME TYPE VALUE
———————————— ———– ——————————
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=120.
0.0.211)(PORT=1521))))

Categories: Uncategorized

Virtual IP in RAC

October 26, 2015 Leave a comment

Virtual IP in RAC

How new connection establish in Oracle RAC?
For failover configuration we should need to configure our physical ip of host name in listener configuration. Listener process is accepting new connection request and handover user process to server process or dispatcher process in Oracle.
Means using listener new connection is being established by Oracle. Once connection gets established there is no need of listener process. If new connection is trying to get session in database and listener is down then what will be happening. User process gets error message and connection fails. Because listener is down in same host or something else problem. But in Oracle RAC database environment database is in sharing mode. Oracle RAC database is shared by all connected nodes. Means more than 1 listeners are running in various nodes.
In Oracle RAC database if user process is trying to get connection with some listener and found listener is down or node is down then Oracle RAC automatically transfer this request to another listener on another node. Up to Oracle 9i we use physical IP address in listener configuration. Means if requested connection gets failed then it will be diverting to another node using physical IP address of another surviving node. But during this automatically transfer, connection should need to wait up to get error message of node down or listener down using TCP/IP connection timeout. Means session should need to wait up to getting TCP/IP timeout error dictation. Once error message is received oracle RAC automatically divert this new connection request to another surviving node.
Using physical IP address there is biggest gap to get TCP/IP timeout for failover suggestion. Session should need to wait for same timeout. High availability of Oracle RAC depends on this time wasting error message.
Why VIP (Virtual IP) needs in Oracle RAC?
From Oracle 10g, virtual IP considers to configure listener. Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners. Once ONS found any listener down or node down, it will notify another nodes and listeners with same situation. While new connection is trying to establish connection to failure node or listener, virtual IP of failure node automatically divert to surviving node and session will be establishing in another surviving node. This process doesn’t wait for TCP/IP timeout event. Due to this new connection gets faster session establishment to another surviving nodes/listener.

Characteristic of Virtual IP in Oracle RAC:
Virtual IP (VIP) is for fast connection establishment in failover dictation. Still we can use physical IP address in Oracle 10g in listener if we have no worry for failover timing. We can change default TCP/IP timeout using operating system utilities or commands and kept smaller. But taking advantage of VIP (Virtual IP address) in Oracle 10g RAC database is advisable. There is utility also provided to configure virtual IP (vip) with RAC environment called VIPCA. Default path is $ORA_CRS_HOME/bin. During installation of Oracle RAC, it is executed.
Advantage of Virtual IP deployment in Oracle RAC:
Using VIP configuration, client can be able to get connection fast even fail over of connection request to node. Because vip automatically assign to another surviving node faster and it can’t wait for TNS timeout old fashion.
Disadvantage of Virtual IP deployment in Oracle RAC:
Some more configurations is needed in system for assign virtual IP address to nodes like in /etc/hosts and others. Some misunderstanding or confusion may occur due to multiple IP assigns in same node.
Important for VIP configuration:
The VIPs should be registered in the DNS. The VIP addresses must be on the same subnet as the public host network addresses. Each Virtual IP (VIP) configured requires an unused and resolvable IP address.
Categories: Uncategorized
%d bloggers like this: