Oracle database discovery

  • Release version: Xanadu
  • Updated August 1, 2024
  • 7 minutes to read
    • Summarize
    Summarized using AI
    This content was generated using new OpenAI-powered functionality. Results are provided on an as is basis and are not guaranteed to be accurate or complete.

    Summary of Oracle database discovery

    Oracle database discovery in ServiceNow identifies Oracle database instances running on UNIX or Windows, including Oracle clusterware supporting Real Application Clusters (RAC). It leverages discovery patterns that replace older probes for finding new instances, though probes can still be used by deactivating the Horizontal discovery probe. This discovery also collects detailed configuration and catalog information about Oracle databases to populate the CMDB.

    Show full answer Show less

    Credentials and Permissions

    • Applicative credentials: Required on target Windows hosts, including read access to tnsnames.ora and permission to run SQLPlus and SELECT queries on key Oracle tables.
    • Windows and SSH credentials: Optionally used for discovery; Windows users need read access to tnsnames.ora, SSH users need read access to oratab and system parameter files.
    • Process monitoring: Discovery detects Oracle instances by verifying running processes (orapmon for UNIX, oracle.exe for Windows) within the designated IP range.
    • Clusterware permissions: Additional read access to /etc/hosts/ and ability to run specific Oracle CRS and RAC commands, some requiring sudo privileges.

    Discovery Components

    • Classifiers and Patterns: Use the Horizontal discovery probe to launch patterns such as Oracle DB on Windows, Oracle DB on UNIX, and UNIX Cluster - Oracle Clusterware for cluster detection.
    • Probes: Some traditional probes are inactive for new instances in favor of patterns but can be reactivated if needed.

    Data Collected

    Discovery collects comprehensive details stored primarily in the cmdbcidborainstance table, including:

    • Instance details: SID, install directory, Oracle home, version, edition, parameter file location, and TCP ports.
    • Clusterware data: Cluster and node status, resource information, VIP configurations, and cluster IDs stored in relevant cluster tables.
    • Oracle options: Requires the Software Asset Management Professional for Oracle plugin and collects enabled Oracle options and usage data for license management.
    • Extended attributes: Additional instance details such as container database status, unique names, and replication enabled flags via an optional extension.

    Oracle Instances on Virtual Machines and Cloud

    Discovery models Oracle instances running on virtual machines, creating CI relationships that distinguish virtual machines from physical servers. It supports instances hosted on AWS and Azure clouds, mapping relationships from Oracle instances to virtual machines and cloud datacenters.

    Clusterware Relationships

    Relationships between clusters, cluster nodes, resources, and virtual IPs are established in the CMDB, reflecting the structure and dependencies of Oracle RAC environments.

    Practical Considerations for ServiceNow Customers

    • Ensure credentials have the necessary permissions to access required files and execute SQL queries for accurate discovery.
    • Verify the appropriate pattern is enabled in the Horizontal discovery probe to detect Oracle instances effectively.
    • Use the Oracle option extensions and related plugins to gain insights into licensed Oracle features and usage.
    • For environments with Oracle RAC or clusterware, confirm elevated permissions to run cluster-specific commands are configured.
    • Leverage the extended Oracle instance attributes for richer data and relational mapping, especially in virtualized or cloud infrastructures.

    Discovery can identify an Oracle database instance that is running on UNIX or Windows operating systems. It can also find Oracle clusterware, which runs Real Application Clusters (RAC).

    Patterns perform the discovery of Oracle installations. These patterns replace several probes for new instances. If you want to continue to use probes, you can deactivate the Horizontal discovery probe, which launches the pattern, and then activate the necessary probes.
    Note:
    Oracle DB on Unix and Oracle DB on Windows do not support RAC single-node discovery.
    The Oracle DB on Unix and Oracle DB on Windows patterns discover Oracle catalog objects through the pattern extension Get Catalog info. For more information, see Oracle Catalog discovery. For general information about database catalog discovery, see database catalogs.
    Note:
    For information on Probe to Pattern migration see the knowledge article KB0694477.

    Credentials

    Configure these credentials:
    Credential type Additional information

    Applicative credentials

    path on the target Windows host. Also, verify that the utilized Discovery Credentials can read tnsnames.ora file.

    The user needs permissions for the SQL*Plus command line tool and permissions to execute SELECT queries against the following tables:
    • PRODUCT_COMPONENT_VERSION
    • V$VERSION
    • V$DATABASE
    • V$PARAMETER
    Windows credentials You can also use Windows and SSH credentials in addition to applicative credentials. For Windows credentials, the user must have read permissions to the tnsnames.ora file. SSH credentials, must allow read permission to the oratab file and read permission to the System Parameter file.

    For a list of privileged commands that you need for Discovery and Service Mapping, see Service Mapping commands requiring a privileged user. This list includes commands that require elevated rights to discover and map Unix-based hosts in your organization.
    SSH credentials

    Additional requirements

    Discovery identifies a running instance of an Oracle database on UNIX from the process that starts with ora_pmon_. Ensure this process is running in the IP range you designate for Discovery. Discovery identifies a running instance of an Oracle database on Windows from the oracle.exe process. Ensure this process is running in the IP range you designate.

    For Oracle clusterware, you also need permissions to read the /etc/hosts/ file, which associates host names with IP addresses.

    The following commands are run for Oracle clusterware:
    Command Description

    ps -ef | grep 'crsd.bin' |grep -v grep

    		 Verifies if Oracle CRS process is running.

    ps -ef |grep LISTENER | grep -v $$ | head -1 | awk '{print $8}'

    		 Gets Oracle home.

    /u01/app/12.1.0.2/grid/bin/olsnodes -c | egrep -v 'error|return code'

    		 Gets Oracle CRS cluster name.

    /u01/app/12.1.0.2/grid/bin/crsctl query crs softwareversion| egrep -v 'error|return code'

    		 Gets Oracle CRS cluster ID.

    /u01/app/12.1.0.2/grid/bin/crsctl query crs softwareversion| egrep -v 'error|return code'

    		 Gets Oracle CRS version.

    /u01/app/12.1.0.2/grid/bin/olsnodes | egrep -v 'error|return code'

    		 Gets Oracle CRS nodes.

    /u01/app/12.1.0.2/grid/bin/crsctl status server | egrep -v 'error|return code'

    		 Gets Oracle CRS node status.

    /u01/app/12.1.0.2/grid/bin/crsctl stat res -f | tr '\n' '#'

    		 Gets Oracle CRS resources.
    /u01/app/12.1.0.2/grid/bin/crsctl stat res -p | grep -v CHECK_R |grep -v error |grep -v failed Gets Oracle CRS VIP resources.

    /u01/app/12.1.0.2/grid/bin/srvctl config scan | tr '\n' ‘ '

    		 Gets Oracle RAC SCAN VIP configurations.

    /u01/app/12.1.0.2/grid/bin/srvctl config vip -node ol7-121-rac1| egrep ' Name' | awk '{print $3}'

    		 Gets Oracle CRS VIPs name.

    /u01/app/12.1.0.2/grid/bin/srvctl config vip -node ol7-121-rac1| grep ' IPv4 Address:' | awk '{print $4}'

    		 Get Oracle CRS VIPs ip_address.
    Sudo permission to run: sudo /u01/app/12.1.0.2/grid/bin/ocrcheck | egrep -v 'error|return code'

    Classifiers, patterns, and probes

    Discovery uses these classifiers, probes, and patterns for Oracle database discovery:
    Classifier Trigger probes Pattern
    Oracle Instance
    • Horizontal discovery probe: launches patterns
    • Powershell-Oracle - Instance PFile (for Windows)*
    • Powershell-Oracle - Instance Version (for Windows)*
    • SSHCommand-Oracle - Instance Details (for Linux)*
    Application patterns:
    • Oracle DB On Windows Pattern
    • Oracle DB on UNIX Pattern

    This shared library pattern used by the Linux Server pattern:

    UNIX Cluster - ORACLE Clusterware
    Note:
    This shared library pattern is triggered if the crsd.bin process is running on the Oracle database server.
    Oracle Listener SSHCommand-Oracle - Listener Details none
    *Probes inactive for new instances.

    To use patterns, verify that the correct pattern is specified in the horizontal pattern probe on the classifier. See Add the Horizontal Pattern probe to a classifier for instructions.

    Data collected

    The following data is collected on the Oracle Instances [cmdb_ci_db_ora_instance] table for both UNIX and Windows:
    Table 1. Data collected on UNIX machines
    Label Field name Additional information
    SID sid Obtained from the name of the process that starts with ora_pmon_
    Install directory install_directory Obtained from the path of ORA_HOME
    Version version Obtained in this order:
    • From the output of the ORA_HOME/bin/sqlplus /NOLOG command
    • From the output of the ORA_HOME/bin/lsnrctl status command
    • From the path of ORA_HOME
    Parameter file pfile The following locations are explored for the location of the System Parameter File. If this file does not exist in one of the explored locations, Discovery does not find the file and reports an error.
    • oracle_home/dbs/spfileSID.ora
    • oracle_home/dbs/spfile.ora
    • oracle_home/dbs/initSID.ora
    Oracle Home oracle_home Obtained from the ORATAB file.
    Edition edition Obtained from the context.xml file.
    Name name Combination of SID name Host name in the format SID@HOST_NAME.
    TCP port(s) tcp_port Obtained from the tnsnames.ora file, or lsnrctl status command. If no valid information is retrieved, the default port 1521 is assigned.
    Note:
    See Oracle options for pattern extensions and additional information collected.
    Table 2. Data collected on Windows machines
    Label Field name Additional information
    SID sid Obtained from the process parameter that is passed to the oracle.exe process.
    Install directory install_directory
    Version version Obtained from the output of the ORA_HOME/bin/sqlplus.exe -V command.
    Parameter file pfile The following locations are explored for the location of the System Parameter File. If this file does not exist in one of the explored locations, Discovery does not find the file and reports an error.
    • oracle_home\database\spfileSID.ora
    • oracle_home\database\spfile.ora
    • oracle_home\database\initSID.ora
    Oracle Home oracle_home Parsed from the path of oracle.exe
    Edition edition
    Note:
    See Oracle options for pattern extensions and additional information collected.

    Oracle options

    For general information on Oracle options, see https://docs.oracle.com/en/.

    Discovery can also find the Oracle options that you enable on the instance using the legacy pattern extension, Oracle option extension for Windows/Unix. Note that to discover Oracle options, the Software Asset Management Professional for Oracle (com.snc.samp.oracle) plugin is required.

    Table 3. Oracle Options [samp_oracle_options]
    Label Field name
    Instance instance
    Option option
    Licensable licensable
    Currently used currently_used
    First usage date first_usage
    Last usage date last_usage
    Last sample date last_sample
    Detected usage detected_usage
    Sample interval sample_interval
    Table privileges needed Additional information
    The following tables are accessed as part of the Oracle option extension for UNIX and Windows:
    • DBA_FEATURE_USAGE_STATISTICS
    • CDB_FEATURE_USAGE_STATISTICS
    • V$DATABASE
    • V$VERSION
    • GV$PARAMETER
    • V$CONTAINERS
    • V$INSTANCE
    		 The following pattern extensions use the Oracle script: options_packs_usage_statistics.sql. This sql script is for DB Server version 11.2 and later. You need the Select any dictionary privilege to execute the script. See the support document ID13172651.1 on https://support.oracle.com/portal/ for more information about usage scripts.
    • Oracle option extensions for UNIX
    • Oracle option extensions for Windows
    Table 4. Data collected on clusterware
    Table Labels and field names
    Unix Cluster [cmdb_ci_unix_cluster]

    Name [name]

    Cluster status [cluster_status]

    Cluster type [cluster_type]

    IP address [ip_address]

    Short description [short_description]

    Cluster ID [cluster_id]

    Cluster version [cluster_version]
    cmdb_ci_unix_cluster_node

    Name [name]

    Node state [node_state]

    IP address [ip_address]

    Node status [node_status]
    cmdb_ci_unix_cluster_resource

    Name [name]

    Resource type [resource_type]

    Resource status [resource_status]

    Properties [properties]
    cmdb_ci_cluster_vip

    Name [name]

    IP address [ip_address]

    Cluster name [cluster_name]

    Cluster ID [cluster_id]

    On the dependency view, a +1 on an Oracle database server signifies that it is part of a cluster.

    Extend Oracle instance extension

    Note:
    Discovery can also find additional attributes for the instance using the Extend Oracle instance extension. See the Oracle website for more information on options https://docs.oracle.com/en/. Discovery saves these attributes to the Oracle Instances [cmdb_ci_db_ora_instance] table.
    Label Field name
    Instance instance
    Host Name host_name
    IS_CDB cdb_yn
    DB Unique Name db_unique_name
    DB Name database_name
    Replication Enabled replication_enabled
    Table privileges needed Additional information
    The following tables are accessed as part of the Extend Oracle Instance extension for UNIX and Windows:
    • V$DATABASE
    • V$INSTANCE
    		 The Extend Oracle Instance extension needs to be installed. This extension is included in the Discovery and Service Mapping Patterns application, version 1.0.72 or higher, which can be downloaded from the ServiceNow Store.

    Oracle instances on virtual machines

    When Oracle instances are hosted on virtual machines, the system creates relationships between CIs.
    CI Relationship CI
    Oracle instance Hosted on Computer [cmdb_ci_computer] or Server [cmdb_ci_server]
    Note:
    This is a virtual machine. The Is virtual field is true.
    Computer [cmdb_ci_computer] or Server [cmdb_ci_server]
    Note:
    This is a virtual machine. The Is virtual field is true.
    		 Virtualized by Computer [cmdb_ci_computer] or Server [cmdb_ci_server]
    Note:
    This is not a virtual machine.

    Oracle instances on virtual machines that are hosted on AWS or Azure clouds

    Base class Relationship Dependent class
    Oracle instance Hosted on Computer [cmdb_ci_computer]
    Note:
    This is a virtual machine. The Is virtual field is true.
    Computer [cmdb_ci_computer]
    Note:
    This is a virtual machine. The Is virtual field is true.
    		 Virtualized by Computer [cmdb_ci_computer]
    Note:
    This is not a virtual machine.
    Computer [cmdb_ci_computer]
    Note:
    This is a virtual machine. The Is virtual field is true.
    		 Virtualized by Virtual Machine Instance [cmdb_ci_vm_instance]
    Note:
    The vCPU count is populated in this table.
    Virtual Machine Instance [cmdb_ci_vm_instance] Hosted on AWS Datacenter [cmdb_ci_aws_datacenter] or Azure Datacenter [cmdb_ci_azure_datacenter]

    Relationships for clusterware

    CI Relationship CI
    Unix Cluster [cmdb_ci_unix_cluster] Virtualizes::Virtualized by Cluster Virtual IP [cmdb_ci_cluster_vip]
    Unix Cluster Resource [cmdb_ci_unix_cluster_resource] Defines resources for::Gets resources from Unix Cluster Node [cmdb_ci_unix_cluster_node]
    Cluster Virtual IP [cmdb_ci_cluster_vip] Uses::Used by Unix Cluster Node [cmdb_ci_unix_cluster_node]
    Unix Cluster Node [cmdb_ci_unix_cluster_node] Cluster of::Cluster Unix Cluster [cmdb_ci_unix_cluster]
    Unix Cluster Node [cmdb_ci_unix_cluster_node] Hosted on:Hosts Unix Cluster [cmdb_ci_unix_cluster]
    Unix Cluster Resource [cmdb_ci_unix_cluster_resource] Defines resources for::Gets resources from Unix Cluster [cmdb_ci_unix_cluster]

    Unix Cluster [cmdb_ci_unix_cluster]

    		 Hosts::Hosted on Linux Server [cmdb_ci_linux_server]
    Unix Cluster Node [cmdb_ci_unix_cluster_node] Hosted on::Hosts Linux Server [cmdb_ci_linux_server]