Oracle Database Discovery - Applicative Credentials

devpc · ‎04-02-2025

Hi everyone, I'm trying to discover all the PLUGABLE DATABASE under the Oracle Instance so I was looking at this documentation Oracle Database 12c discovery but it's not clear, at least not for me, what these Applicative Credentials are. I understand that they are needed if i want to discover this class of object but it's clear what are the username and password I must configure on SNOW. Can someone explain it to me? Thank you

James Behrens · ‎04-02-2025

@devpc , I highly recommend reconciling the list of Oracle databases you have in service now against a list from your DBAs. I was thinking the same thing that I was not seeing 'pluggable databases'. It turns out that I was, but the names were not as I expected and there is no other data on the database record. I am seeing all but 70 of the 270 they shared with me. I'll circle back with them but I don't think it has anything to do with 'pluggable database'. They look like administrative databases and stuff for the most part.

The Oracle Services in my case had much more recognizable names. Once you are at the Oracle Service level, you can see the instances and databases. I found it frustrating that the database has no link to the associated instances and certainly not the more recognizable services.

Check out these courses on PluralSight if you have an account. I found them super handy in learning the component relationships and meanings. I skipped over a lot of the tuning and security and invested just a few hours learning the basics. Highly recommend that.

Understand Oracle Database 19c Architecture

Understanding Database Architecture with Oracle

blanm · ‎05-02-2025

Hi There,

We had this same issue, as we had multiple PDBs in a single CDB. Before following my process below, make sure you can at least get connected to and discover the Oracle instance itself, even if the PDBs and Catalogs do not discover. I will also note, that all of our Oracle instances were on UNIX, so my reply is focused on that pattern specifically. You will want to check what the OS is for the instance.

For the credentials part, think of it as you signing into a server via the mid, but then there is a program on the server that you need to access that also requires a username and password. This is where applicative credentials come in. Although I can access the UNIX server via the mid server/discovery, I still need to be able to login to the Oracle DB application. It's a good practice to have a separate login like this - in our situation, we have a server team that is in charge of the servers, but have nothing to do with the Oracle DBs on the server themselves. Only our Oracle team needs access to that, so it is more secure and allows less of a chance of some one that is not an Oracle DB to mess something up.

Oracle Multitenant structure also makes things more complicated, since we are technically looking for 3 things: the instance (CDB), the smaller containers inside of them (PDB), and the owners of tables inside of the PDBs (referred to as catalogs or schemas in ServiceNow). Each of these owners have objects that belong to them, including tables and fields, just like the CMDB in ServiceNow. (As tempting as it would be to pull in names of tables, I would advise against it depending on how large your DBs are, and what level of security is needed on the data in the tables.)

First, our Oracle DBA had to create us our own common user account within the Oracle interface for discovery.

The key words here are common user, these usernames start with C## in Oracle and have more access within a database than other user types.

Once that is created, you need to make an applicative credential in ServiceNow using the username and password created by your DBA. Make sure you set the CI type as cmdb_ci_db_ora_instance.

The OOB pattern for Oracle catalogs are within the Oracle DB on UNIX pattern. It is in the extension section/shared library called Get Catalogs.

It took me a really long time to figure it out, but the permissions listed in the SNow documentation would give us very limited information, if at all, during discovery. After working with my Oracle DBA, it still only seemed to work on ONE PDB at a time, I could never get it to iterate over the multiple PDBs that it found in the extension section "Get Oracle Instance size info".

We finally figured out that the permissions that were being set were only set on the PDB level, NOT on the CDB level. The OOB SQL queries are designed to run from the CDB level, and I never found anything in the Snow documentation mentioning this. I was new to the CMDB and never dealt with Oracle, so I didn't know what to say to my Oracle DB to get them to understand what I needed (I can now fluently speak Oracle/SQL terms to them, which has been tremendously helpful). And even so, the OOB SQL commands were limited, and were grabbing owners from system tablespaces (meaning they exist on every instance), which was not what we wanted.

Below is the eventual permissions script that my DBA set WITHIN the Oracle interface for our common username. Please note, your Oracle DBA might have different permissions restrictions.

CREATE USER C##your_chosen_user_name_here
IDENTIFIED BY <password>
HTTP DIGEST DISABLE
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;

-- 1 Role for C##your_chosen_user_name_here
GRANT SELECT_CATALOG_ROLE TO C##your_chosen_user_name_here CONTAINER=ALL;
ALTER USER C##your_chosen_user_name_here DEFAULT ROLE ALL;

-- 1 System Privilege for C##your_chosen_user_name_here
GRANT CREATE SESSION TO C##your_chosen_user_name_here CONTAINER=ALL;

-- 1 Tablespace Quota for C##your_chosen_user_name_here
ALTER USER C##your_chosen_user_name_here QUOTA 100M ON USERS;

-- 1 Container Data Restriction for C##your_chosen_user_name_here
ALTER USER C##your_chosen_user_name_here
SET CONTAINER_DATA = ALL
CONTAINER = CURRENT;

Now, this gave us the permissions we wanted and commands were returning data, but I ran into an issue where we had two PDBs in the same instance that had identical "schemas" or owner names.

so exampleschema@pdb1@host was a valid record, but so was exampleschema@pdb2@host

With the way the IRE rules were set, which looks like exampleschema@host, it was deleting the second PDB's schemas, because it counted it as a duplicate.

I had to make my own attribute, which I called u_schema_identifier, to create this specific naming convention to include the PDB name. I then had to change the IRE rule to consider this instead of the original name attribute, as well as make changes to the pattern as our Oracle DBAs wanted to see the details like schema, PDB, CDB, host, and individual schema size on the Oracle catalog table. I also made changes to make sure to not include any system schemas/owners that are found in system tablespaces. I also added steps to make a relation to the PDB itself from the PDB instance table, which also populates on the catalog table in a field I named u_pdb, instead of just seeing a reference to instance@host.

This was some extreme customization, which took me 4 months to complete, but the end results were that we were able to pull all of that in successfully for any of our CDBs, regardless of how many PDBs existed in them.

My finalized sql command that retrieved everything I wanted is below, but I also had to parse the information that it pulled in with a parse command step. I made this in the Get Catalogs shared library in the extension section of the main pattern.

"export TNS_ADMIN=" + $ora_home_exe + "/network/admin;export ORACLE_HOME=" + $ora_home_exe + "; echo -e set head off feed off pages 0 line 3000\\n\"SELECT s.owner || ' ####' AS segment_owner, p.pdb_name || ' ####' AS pdb_name_parsed, TO_CHAR(CEIL(SUM(s.bytes) / (1024 * 1024) * 100) / 100, '9999999.99') || ' ####' AS total_size_mb FROM cdb_segments s JOIN cdb_users u ON s.owner = u.username AND s.con_id = u.con_id JOIN cdb_pdbs p ON u.con_id = p.con_id WHERE s.segment_type IN ('TABLE', 'INDEX', 'LOBSEGMENT', 'LOBINDEX') AND s.owner NOT IN ('APPQOSSYS', 'APEX_040200', 'AROLLINS', 'AUDSYS', 'CCONCANNON', 'CTXSYS', 'DBSFWUSER', 'DBSNMP', 'DVSYS', 'GSMADMIN_INTERNAL', 'LBACSYS', 'MDSYS', 'MKRUDWIG', 'ORDDATA', 'OJVMSYS', 'ORAPRD3', 'ORDSYS', 'OUTLN', 'SCOTT', 'SYS', 'SYSTEM', 'TOOLS', 'WMSYS', 'XDB') GROUP BY s.owner, p.pdb_name, s.con_id, u.con_id ORDER BY pdb_name_parsed, segment_owner;\" |" + "\"" + $ora_home_exe + "/bin/sqlplus\" -s " + "'$$username$$'/'$$password$$'@" + $computer_system.primaryHostname + ":" + $connect_sql[1].listener_port + "/" + $connect_sql[1].service_name

See attached pictures for reference. I also have hundreds of web pages saved on SQL command/tips, Oracle everything, and discovery pattern step references if you need it.

blanm · ‎05-02-2025