Troubleshooting Inconsistent Oracle Discovery on Multi-Instance Hosts - Support and Troubleshooting

Troubleshooting Inconsistent Oracle Discovery on Multi-Instance Hosts Overview When discovering Oracle databases on a single host with multiple database instances, you may encounter scenarios where discovery succeeds for some instances but fails for others, even when:

The same credentials are used The same MID Server is used The host is reachable The credentials test successfully

Important Notes:

These steps are intended as a guide and may not resolve all issues due to variations in Oracle configurations, network environments, and ServiceNow deployments. This article assumes basic technical knowledge of Oracle administration and ServiceNow Discovery. Some steps require escalation to your Oracle Database Administration team. Results may vary depending on your specific environment, Oracle version, MID Server configuration, and network policies. Following these steps does not guarantee discovery will succeed or that all database instances will be discovered. If issues persist after completing these troubleshooting steps, or if you require assistance interpreting results, please contact your ServiceNow implementation team, Oracle support, or your internal IT operations team for additional support. Symptoms Discovery completes successfully but only collects data from 1-2 instances out of 5+ running instances on the host Some instances show complete results while others show connection errors Credential validation passes, but discovery fails for specific instances Different instances on the same host have different success/failure rates across multiple discovery runs Root Cause The most common cause is that not all running database instances are registered with the Oracle listener. The Discovery pattern connects via:

sqlplus credential@hostname:port/SERVICE_NAME If the listener doesn't know about a service name, it will return:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor This is NOT a credential issue—it's a database registration and listener configuration issue on the Oracle side.

Prerequisites for Oracle Discovery Before troubleshooting, verify these requirements are met:

✓ MID Server is Up and Validated ✓ Network connectivity from MID Server to Oracle host is working ✓ Oracle port (typically 1521) is accessible from MID Server ✓ Discovery credentials have been validated successfully ✓ All database instances are currently running on the target host Troubleshooting Steps Step 1: Verify All Database Instances Are Running Have the Oracle DBA run this command on the Oracle host:

bash ps -ef | grep pmon | grep -v grep

Expected output (example with 3 instances):

oracle 12345 1 0 Aug28 ? 00:15:23 ora_pmon_PROD oracle 12346 1 0 Aug28 ? 00:14:12 ora_pmon_DEV oracle 12347 1 0 Aug28 ? 00:18:45 ora_pmon_TEST What to look for:

Each running instance will have an ora_pmon_ process If an instance is missing, it's not running Action: If instances are not running, start them before retrying discovery.

Step 2: Check What Services the Listener Knows About Have the Oracle DBA run this on the Oracle host:

bash lsnrctl status Example output:

Service "PROD" has 1 instance(s). Instance "PROD", status READY, has 1 handler(s) for this service... Service "DEV" has 1 instance(s). Instance "DEV", status READY, has 1 handler(s) for this service... Service "TEST" has 1 instance(s). Instance "TEST", status READY, has 1 instance(s) for this service... Critical check: Compare this output with the running instances from Step 1.

Issue: If an instance is running but NOT listed in lsnrctl status , the listener doesn't know about it → Discovery will fail for that instance.

Step 3: Verify tnsnames.ora Configuration Have the Oracle DBA check if all instance service names are in tnsnames.ora:

bash cat $ORACLE_HOME/network/admin/tnsnames.ora | grep -i "^[A-Z]" | head -20 Expected output (example):

PROD = (DESCRIPTION = ... DEV = (DESCRIPTION = ... TEST = (DESCRIPTION = ... Check: Every instance should have a corresponding entry in tnsnames.ora.

Issue: If an instance service name is missing, the listener cannot resolve connections to it.

Step 4: Identify the Failing Instance from Discovery Logs Review the discovery logs in ServiceNow to identify which instances succeeded and which failed:

Navigate to: Discovery > Discovery Status (click the failed discovery)

Look for entries like:

2026-09-15 13:43:03: Executing SSH command: ... sqlplus ... @hostname:1521/SERVICE_A 2026-09-15 13:43:03: Command result: [SUCCESS - returns data] 2026-09-15 13:43:04: Executing SSH command: ... sqlplus ... @hostname:1521/SERVICE_B 2026-09-15 13:43:04: Command result: ERROR: ORA-12514: TNS:listener does not know... Document which service names failed. Provide this list to the Oracle DBA.

Step 5: Test Connectivity to Each Instance from MID Server Log into the MID Server and test each instance manually:

bash sqlplus / @ :1521/ Example tests:

bash sqlplus discovery_user/password@oraclehost:1521/SERVICE_A sqlplus discovery_user/password@oraclehost:1521/SERVICE_B sqlplus discovery_user/password@oraclehost:1521/SERVICE_C Expected result: Each should display SQL> prompt and successfully connect.

Document any failures and report the exact service names that fail.

Step 6: Restart the Oracle Listener (If Required) If instances are running and in tnsnames.ora but NOT in lsnrctl status , the listener needs to be restarted to re-register services.

Have the Oracle DBA:

bash lsnrctl stop lsnrctl start Then verify services are registered again:

bash lsnrctl status Note: This is a short outage and should be coordinated with the Oracle team.

Step 7: Verify Discovery User Permissions Ensure the discovery credentials have the required Oracle permissions to query all instances:

Have the Oracle DBA verify the discovery user has:

sql GRANT CONNECT TO ; GRANT SELECT ANY TABLE TO ; GRANT SELECT ANY DICTIONARY TO ; GRANT SELECT ON dba_tables TO ; GRANT SELECT ON dba_views TO ; Test these permissions:

bash sqlplus / @hostname:1521/ Then run:

sql SELECT * FROM v$version; SELECT * FROM dba_tables WHERE ROWNUM All should return data without errors.

Common Scenarios and Fixes Scenario 1: Some Instances Running, Others Stopped Symptom: Discovery works for running instances but fails for stopped instances.

Fix: Have the Oracle DBA start all required instances before discovery runs. If instances are scheduled to be offline, exclude them from discovery scheduling.

Scenario 2: Instances Running But Not Registered with Listener Symptom: ps -ef shows instance running, but lsnrctl status doesn't list it. Error: ORA-12514 .

Cause: Instance crashed and listener wasn't informed, or listener misconfigured.

Fix:

Verify instance is truly running: ps -ef | grep pmon Check listener registration: lsnrctl status If not registered, restart listener: lsnrctl stop; lsnrctl start Scenario 3: Missing tnsnames.ora Entry Symptom: Specific instances fail with ORA-12514 . Other instances on same host work fine.

Cause: Service name not defined in tnsnames.ora file.

Fix: Have Oracle DBA add missing service entries to tnsnames.ora in the correct format:

SERVICE_NAME = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SERVICE_NAME) ) ) Then reload listener: lsnrctl reload

Scenario 4: Listener on Different Port for Some Instances Symptom: Inconsistent failures even after verifying instances are running.

Cause: Some instances using different listener port (not 1521).

Check: Have Oracle DBA verify all instances are using the same listener port:

bash lsnrctl status | grep PORT Fix: If instances use different ports, either:

Update discovery to test each port separately, OR Consolidate all instances to use the same listener Verification After DBA Fixes Once the Oracle DBA has made corrections:

1. Test from MID Server Again bash sqlplus / @ :1521/ SELECT * FROM v$version; exit Should succeed for all instances.

2. Verify Listener Knows All Services bash lsnrctl status Should list all instances.

3. Rerun Discovery Navigate to Discovery > Discover Now Target: Same Oracle host Select: Oracle discovery pattern Submit discovery Monitor the discovery logs to verify all instances are now discovered.

Information to Collect Before Escalating If the issue persists after these steps, collect:

Output from the Oracle host: bash ps -ef | grep pmon | grep -v grep lsnrctl status cat $ORACLE_HOME/network/admin/tnsnames.ora Discovery logs from ServiceNow: Navigate to failed discovery status Export logs showing which instances failed and the specific error messages MID Server verification: MID Server name and status (Up/Validated) Test results from manual sqlplus commands to each instance Environment details: Oracle version Number of instances on host How many discovered successfully vs. failed List of service names that failed Credentials: Confirm discovery credentials are active and properly configured Related Articles Oracle Database Discovery Overview: https://www.servicenow.com/docs/r/guxhDnVk8OUAJKLm7WZsYA/u0Eb4TM899PEchM9lgm7JA

Discovery Landing Page: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0868418

Oracle Database Discovery Overview: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB1611943

Validating Discovery Credentials: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0657528

MID Server Troubleshooting: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0597571

Discovery Pattern Execution Logs: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0717357

These may be handy to have dependent on the Oracle DB endpoints as well in case you may need slightly different commands/options/locations of some of these directories:

Oracle Listener Control Utility (LSNRCTL) - Latest: https://docs.oracle.com/en/database/oracle/oracle-database/manage-listener-utility/index.html

Oracle LSNRCTL Reference (Oracle 11g Release 2): https://docs.oracle.com/cd/E18283_01/network.112/e10835/lsnrctl.htm

Oracle LSNRCTL Reference (Oracle 10g Release 1): https://docs.oracle.com/cd/B13789_01/network.101/b10776/lsnrctl.htm

Oracle Net Services Reference - Listener Control Utility (Oracle 9i): https://docs.oracle.com/cd/A91202_01/901_doc/network.901/a90155/lsnrctl.htm

Managing the Listener with LSNRCTL (Current): https://docs.oracle.com/cd/E18283_01/network.112/e10836/listenercfg.htm

These will give you the complete lsnrctl command syntax and options like lsnrctl status , lsnrctl stop , lsnrctl start , and lsnrctl reload that your Oracle DBA would use for troubleshooting listener issues.

Summary Flowchart Multi-Instance Discovery Failing for Some Instances? │ ├─ Step 1: Are all instances RUNNING? │ ├─ No → Start instances │ └─ Yes ↓ ├─ Step 2: Does LISTENER know about ALL instances? │ ├─ No → Restart listener or add to tnsnames.ora │ └─ Yes ↓ ├─ Step 3: Can MID Server connect to each SERVICE_NAME? │ ├─ No → Check network/firewall/port │ └─ Yes ↓ ├─ Step 4: Does discovery user have SELECT permissions? │ ├─ No → Grant required privileges │ └─ Yes ↓ └─ Success: Rerun discovery → All instances should now be discovered FAQ Q: Why do some instances work but others don't if we're using the same credentials? A: Credentials are valid, but the Oracle listener isn't aware of the failing instances. This is a database registration issue, not a credentials issue.

Q: Does ORA-12514 mean the credentials are wrong? A: No. ORA-12514 specifically means the listener doesn't know about that service. Credentials are irrelevant if the listener can't find the service.

Q: Should I restart the Oracle listener during business hours? A: No. Coordinate with your Oracle DBA and schedule during a maintenance window, as this affects all connections to that Oracle host.

Q: Do all instances need to be on the same listener? A: Not required, but recommended. If instances use different listeners/ports, each must be tested and discovered separately.

Q: What if the instance is stopped intentionally? A: Exclude it from the discovery schedule or accept it won't be discovered. Only running instances can be discovered.