Differences between Oracle SID and SERVICE_NAME connection types in the RAC agents (4308051)

RAC information is incorrect in the cartridge reports and views.

The Agent shows that the instance/instances are down even though the instances are up and running.

Instance status comes and goes with Instance down in the Up Since column.

In a RAC environment, Oracle channels the database connectivity to the available node/instance. This is done behind the scenes, and the Agent is not aware of the active instance at a time. When Oracle switches between node/instance, the Agent will keep reporting on the already connected instance even though it has been changed.

Note: This is NOT a bug, and the behavior is yielded from Oracle design which is using Service Name.

When Oracle switches node/instance on the fly, the Agent might also report instance un-availability because of the same reason described above.

SID and Service Name in a nutshell

When creating an Oracle Agent, there are two types of connections that can be used:

• SID
• SERVICE_NAME

The differences between those are:

• SID = the unique name of your DB
• ServiceName = the alias used when connecting

SID = unique name of the INSTANCE (eg the oracle process running on the machine
The SID is the local name of the database on your system, and the Service Name is the name of the system to the outside world.

For example, you might have a staging database and a production database with the same SID but referenced with 2 different service names:

STAGE.WORLD =
 (DESCRIPTION =
   (ADDRESS =
      (PROTOCOL = TCP)
       (PORT = 1521) 
     (HOST = LITTLECOMPUTER.ACME.ORG)
   )
   (CONNECT_DATA = (SID = MYSID))
)
PROD.WORLD =
 (DESCRIPTION =
   (ADDRESS =
      (PROTOCOL = TCP) 
      (PORT = 1521)
      (HOST = BIGCOMPUTER.ACME.ORG)
   )
   (CONNECT_DATA = (SID = MYSID))
)

The solution would be to change the Service Name to SID.

1. Navigate to Dashboards | Administration | Agents | Agent Status
2. Deactivate the agents involved from Agent Status
3. Navigate to Databases | Support Dashboard | Database Technical Support | Clear Agent Data
4. Highlight the RAC agent and/or nodes
5. Click the Delete Selected Agent Data button
6. Navigate to Databases | Support Dashboard | Database Technical Support | Clear Agent Model Data
7. Highlight the RAC agent and/or nodes
8. Navigate to Dashboards | Administration | Agents | Agent Status
9. Select a RAC agent and click the Edit Properties button
10. Click the link to modify the private properties for the agent
11. Change SERVICE_NAME to SID
    
12. Click the Save Changes button
13. Repeat steps 9-12 for the other Oracle agents to change
14. Activate the Oracle agents

Using SID will point directly to the instance.

What is the difference between Oracle SIDs and Oracle SERVICE NAMES?

Oracle SID is the unique name that uniquely identifies your instance/database where as Service name is the TNS alias that you give when you remotely connect to your database and this Service name is recorded in Tnsnames.ora file on your clients and it can be the same as SID and you can also give it any other name you want.

SERVICE_NAME is the new feature from oracle 8i onwards in which database can register itself with listener. If database is registered with listener in this way then you can use SERVICE_NAME parameter in tnsnames.ora otherwise - use SID in tnsnames.ora.

References: http://stackoverflow.com/questions/43866/how-sid-is-different-from-service-name-in-oracle-tnsnames-ora