OracleRAC & ASM
Sessions per instance and service
How connections are spread across RAC nodes and services. A lopsided spread usually means a service isn't running where you think it is.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 100 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN inst_id HEADING "INST" FORMAT 9994COLUMN service_name HEADING "SERVICE" FORMAT A355COLUMN total HEADING "SESSIONS" FORMAT 99,9906COLUMN active FORMAT 99,9907 8SELECT inst_id, service_name,9 COUNT(*) AS total,10 SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active11FROM gv$session12WHERE type = 'USER'13GROUP BY inst_id, service_name14ORDER BY inst_id, service_name;Save it as ora-rac-services.sql and run it with SQL> @ora-rac-services.
Open in denrepoRelated tool: Connection string builder
Helps with
- ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
- ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
More Oracle scripts: RAC & ASM
- Stop, start and check a RAC database (srvctl)Clusterware commands to stop the database, start it read-only, check its status, and start a single instance in MOUNT mode. Run one line at a time;…
- ASM disk group spaceSize, free and usable space per disk group. Usable GB already accounts for mirroring, so it's the number to alert on. Works from the ASM or database…
- ASM disks and their statusEvery disk ASM can see, including CANDIDATE disks not yet in a group. Check MODE and STATE after storage maintenance.
- ASM rebalance progressRebalance operations in flight with power level and estimated minutes left. No rows means nothing is rebalancing.
- Global cache (interconnect) waits by instanceCluster-class waits per instance since startup. High average times on gc cr or gc current events point at the interconnect or at hot blocks shared…
- Cluster status commands (srvctl and crsctl)The Clusterware commands for checking a RAC database, its services and every cluster resource.