Page tree
Skip to end of metadata
Go to start of metadata

 

Changes to Oracle Database modeling

To support multitenancy introduced in Oracle 12c, the changes to modeling Database Details has been introduced: all the Database Details, like Schemas and Tables, are now linked to the logical databases instead of the Database Software Instance. For versions earlier that Oracle 12c, and for the root database in Oracle 12c and later, the Database Details are also linked to the Software Instance.

The old model, where the Database Details are only linked directly to the Software Instances, is outdated.

Pattern Goal

The goal of the Oracle RDBMS Database Detail patterns is to obtain a detailed mapping of a given Database structure. The Database pattern (Atrium Discovery 8.3 and later) creates a DatabaseDetail node that represents an instance of the Oracle Database Server which allows the CMDB sync mechanism to map it to the BMC_Database CI in Atrium CMDB. The SchemasAndTables pattern returns the list of Schemas managed by a given database (represented by a SID or a server_name), and, furthermore, it returns (optionally, and disabled by default) the list of all Tables within these Schemas. The TablespacesAndDataFiles pattern returns the list of Tablespaces for a given SID or service_name, and, furthermore, it can return (optionally, and disabled by default) the list of all DataFiles, for each Tablespace.

Note

The patterns will not by default return the list of all tables within schemas nor all datafiles within tablespaces as this can have a marked impact on discovery of hosts with large database installations

The Oracle schema and table information is then stored within the Atrium Discovery Model as DatabaseDetail Nodes.

The patterns with exception of the Database pattern connect to the Oracle DB Server using a JDBC connection.

Configuration Options

There are several configuration options available for this Extended Discovery:

  • retrieve_schemas := true; - allows to retrieve schemas
  • ignore_schemas := [ 'SYSTEM', 'SYS', 'SYSMAN', 'DBSNMP', 'OLAPSYS', 'MDSYS', 'WMSYS', 'XDB', 'APEX_030200' ]; - allows to ignore selected schemas
  • ignore_schemas_substring := [ 'ISALOG' ]; - allows to ignore schemas which have substring
  • max_num_schemas := 1000; -  maximum number of schemas to be discovered
  • retrieve_tables := false; - allows to obtain tables in each schema
  • retrieve_tablespaces := true; -  allows to retrieve tablespaces
  • retrieve_datafiles := false; - allows to retrieve data files in each tablespace
  • use_all_service_names := false; - allows to try running the showPDBs query with all found service_names, if the provided Oracle credential is service based

Old DatabaseDetails hierarchy

Non-CDB Oracle installations (All Oracles prior to 12c) had 1 default "Oracle Database <ORA_SID>" Database Details and another (SQL Database Schemas, Oracle Tablespace) Database Details were linked directly to "Oracle Database Server" SoftwareInstance, like:

New DatabaseDetails hierarchy

New DatabaseDetail hierarchy is used to match non-CDB and mulitenant(CDB) Oracle installations:

  • for nonCDB (Oracle 12c and all prior ) - 1 default "Oracle Database <ORA_SID>" is created
  • for CDB (if feature is enabled in Oracle 12c) - multiple "Oracle Database <PDB_NAME>"  are created
    and another (SQL Database Schemas, Oracle Tablespace) Database Details are linked to related "Oracle Database" Database Details:

 

Note

New DatabaseDetails hierarchy could have impact on CAM models in ADDM and sync to CMDB since "SQL Database Schema", "Oracle Tablespace" DatabaseDetails are now attached to correspondent 'Oracle Database' DatabaseDetail instead of 'Oracle Database Server' SI, though DatabaseDetail's keys (for default non-CDB instance / CDB$ROOT PDB) are left unchanged.

Pluggable Database Listing

Pattern extracts list of pluggable database using methods:

  • sql query: "SELECT NAME, PDB FROM V$SERVICES ORDER BY PDB, NAME"
  • "<ora_base>/oradata/<db_unique_name>" directory listing. If "oradata" directory is located in ASM storage than this method fails.

Configuration Required

The Database Detail Patterns require additional configuration before they can be used.

Setting of Database Integration Point

Setting the port:

The pattern uses 'success_login_cred' attribute of related "Oracle Database Server" SI to run DB Query.

Note

The database query to Oracle Database Server will only run if the extended_sql_discovery option is enabled in the Oracle Database pattern configuration section. It is enabled by default.

Editing Pattern Configuration

  • Decide whether the pattern should attempt to retrieve Tables and DataFiles, or not, and whether any specific Databases should be ignored

Database User Configuration

In order for Atrium Discovery to retrieve the Database details, it will need to be able to access the Database as a legitimate user, with privileges sufficient to execute the SQL Queries below.

SQL Queries Executed for credentials using Oracle Database Server SID

The pattern executes the following SQL Queries.

  • Obtaining a list of Schemas for a SID: SELECT username FROM all_users ORDER BY username
  • Obtaining a list of Tables within a Schema: SELECT table_name FROM all_tables WHERE owner = %schema%
  • Obtaining a list of Tablespaces for a SID: SELECT * FROM dba_tablespaces
  • Obtaining a list of DataFiles within a Tablespace: SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = %tablespace_name%

SQL Queries Executed for credentials using Oracle Database Server service_name

The pattern executes the following SQL Queries.

  • Obtaining a list of Schemas: SELECT username FROM all_users ORDER BY username
  • Obtaining a list of Tables within a Schema: SELECT table_name FROM all_tables WHERE owner = %schema%
  • Obtaining a list of Tablespaces: SELECT * FROM dba_tablespaces
  • Obtaining a list of DataFiles within a Tablespace:SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = %tablespace_name%


Note

The following pattern configuration options allow to filter out not needed schemas:
ignore_schemas - Ignore schemas which fully match,
ignore_schemas_substring - Ignore schemas which have substring

3 Comments

  1.  

    Setting of Database Integration Point

     

    The above link seems to be 404 now...

     

  2. Hi all,

    I need some clarification in order to get the list of pluggable databases. This chapter is too vage  in my opinion.

    • We are  on 11.3.0.3 with TKU  Aug 2019 
    • New JDBC driver (by service name) is setup an used within Oracle SQL Credential
    • What we get actually for Non-CDB and CDB is the instance name. In te case of a Non-CDB this is fine since it's also the DB name

      Software Instance Oracle Database Server 12cR2 identified as abcdef on ServerXyz
      Maintaining Pattern Oracle.OracleRDBMS_Extended.Database
      Instance abcdef 

    • But what we need to configure for "deep diving" into the CDB in order to get the PDB list?

    Thanks for some hints.

    Roland

    1. Official comment can be found here:

      https://communities.bmc.com/thread/194925

      Also in TKU October 2019 we added an option to try all service names to discover PDBs. Please note that this option is set to false by default and will only be used if the associated discovery method in success_login_credential is service. You may still need to ask the DBA to setup the Oracle user which can be used in BMC Discovery accordingly to get the tablespaces and schemas for them.