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.

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:

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

• Set the Connection Parameters, including Username and Password.

Setting the port:

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

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%