Oracle PDB Status Test

The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB. All Oracle databases before Oracle Database 12c were non-CDBs.

A container is either a PDB or the root container (also called the root). The root is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.

Every CDB has the following containers:

  • Exactly one root

    The root stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. The root container is named CDB$ROOT.

  • Exactly one seed PDB

    The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.

  • Zero or more user-created PDBs

    A PDB is a user-created entity that contains the data and code required for a specific set of features. For example, a PDB can support a specific application, such as a human resources or sales application. No PDBs exist at creation of the CDB. You add PDBs based on your business requirements.

If a user is experiencing errors when attempting to open a PDB, administrators must be able to quickly check the status of the PDB to figure out the reason for the error. For this purpose, administrators can use the Oracle PDB Status test. This test automatically discovers the PDBs and reports the current status and mode of every PDB.

Target of the test : An Oracle 12c server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every PDB on the Oracle server.

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The host for which the test is to be configured
  3. Port - The port on which the server is listening

  4. User – In order to monitor an Oracle database server, a special database user account has to be created in every Oracle database instance that requires monitoring. A Click here hyperlink is available in the test configuration page, using which a new oracle database user can be created. Alternatively, you can manually create the special database user. When doing so, ensure that this user is vested with the select_catalog_role and create session privileges.

    The sample script we recommend for user creation (in Oracle database server versions before 12c) for eG monitoring is:

    create user oraeg identified by oraeg

    create role oratest;

    grant create session to oratest;

    grant select_catalog_role to oratest;

    grant oratest to oraeg;

    The sample script we recommend for user creation (in Oracle database server 12c) for eG monitoring is:

    alter session set container=<Oracle_service_name>;

    create user <user_name>identified by <user_password> container=current default tablespace <name_of_default_tablespace> temporary tablespace <name_of_temporary_tablespace>;

    Grant create session to <user_name>;                                

    Grant select_catalog_role to <user_name>;

    The name of this user has to be specified here.

  5. Password – Password of the specified database user

    This login information is required to query Oracle’s internal dynamic views, so as to fetch the current status / health of the various database components.

  6. Confirm password – Confirm the password by retyping it here.
  7. listener name – Specify the Oracle listener name. By default, this will be the same as the Oracle SID.
  8. ISPASSIVE – If the value chosen is yes, then the Oracle server under consideration is a passive server in an Oracle cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable" by the agent if the server is not up.

  9. REPORTMANAGERTIME- By default, this flag is set to Yes, indicating that, by default, the detailed diagnosis of this test, if enabled, will report the shutdown and reboot times of the device in the manager’s time zone. If this flag is set to No, then the shutdown and reboot times are shown in the time zone of the system where the agent is running(i.e., the system being managed for agent-based monitoring, and the system on which the remote agent is running - for agentless monitoring).

  10. SSL- By default, this flag is set to No, as the target Oracle database is not SSL-enabled by default. If the target database is SSL-enabled, then set this flag to Yes.
  11. SSL Cipher-This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none. A cipher suite is a set of cryptographic algorithms that are used before a client application and server exchange information over an SSL/TLS connection. It consist of sets of instructions on how to secure a network through SSL (Secure Sockets Layer) or TLS (Transport Layer Security). In this text box, provide a comma-seperated list of cipher suites that are allowed for SSL/TLS connection to the target database. By default, this parameter is set to none.
  12. TRUSTSTORE FILE- This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none. TrustStore is used to store certificates from Certified Authorities (CA) that verify and authenticate the certificate presented by the server in an SSL connection. Therefore, the eG agent should have access to the truststore where the certificates are stored to authenticate and connect with the target database and collect metrics. For this, first import the certificates into the following default location <eG_INSTALL_DIR>/lib/security/mytruststore.jks. To know how to import the certificate into the truststore, refer toPre-requisites for monitoring Oracle Cluster. Then, provide the truststore file name in this text box. For example: mytruststore.jks. By default, none is specified against this text box.
  13. TRUSTSTORE TYPE-This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none.Specify the type of truststore that contains the certificates for server authentication in this text box. For eg.,JKS. By default, this parameter is set to the value none.
  14. TRUSSTORE PASSWORD-This parameter is applicable only if the target Oracle database is SSL-enabled, if not, set this parameter to none. If a Truststore File name is provided, then, in this text box, provide the password that is used to obtain the associated certificate details from the Truststore File. By default, this parameter is set to none.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Status:

Indicates the current status of this PDB.

 

The values that this measure can report and their corresponding numeric values are discussed hereunder:

Measure Value Description Numeric Value
new The PDB has never been opened since it was created. It must be opened inREAD WRITE mode for Oracle to perform processing needed to complete the integration of the PDB into the CDB and mark it NORMAL. An error will be thrown if an attempt is made to open the PDB read only 1
normal The PDB is ready to be used 2
unplugged The PDB has been unplugged. The only operation that can be performed on it is DROP PLUGGABLE DATABASE. 3
needs upgrade A PDB needs to be upgraded to the version of the CDB into which it was plugged 4
converting A non-CDB was plugged into the CDB and is undergoing conversion required to make it behave like a real PDB. 5
unusable The PDB is being created or an unrecoverable error was encountered during its creation. The PDB cannot be opened while its state is set to UNUSABLE. If the PDB remains in this state because of an error encountered during its creation, it can only be dropped. The alert log can be checked to determine if there was an error during PDB creation. 6

Note:

By default, this measure reports the above-mentioned Measure Values while indicating the current state of a PDB. However, in the graph of this measure, the same will be represented using the corresponding numeric equivalents only.

Mode:

Indicates the mode in which this PDB has been opened currently.

 

The values that this measure can report and their corresponding numeric values are discussed hereunder:

Measure Value Numeric Value
mounted 1
read write 2
read only 3
migrate 4

Note:

By default, this measure reports the above-mentioned Measure Values while indicating the mode in which the PDB is opened. However, in the graph of this measure, the same will be represented using the corresponding numeric equivalents only.

Uptime 

Indicates how long this PDB has been up and running.

Seconds

 

Uptime since last measure

Indicates the duration for which this PDB has been up since the last measurement period.

Seconds

If the value of this measure is lesser than the test frequency, it indicates that the PDB was rebooted during the last measurement period.

Is rebooted?

Indicates whether this PDB was rebooted or not.

 

This measure reports the value Yes if the PDB was rebooted in the last measurement period, and the value No if it was not rebooted. The numeric values that correspond to these measure values have been listed in the table below:

Measure Value Numeric Value
Yes 1
No 0

Note:

This test reports the Measure Values listed in the table above to indicate whether/not the PDB was rebooted. In the graph of this measure however, the same will be represented using the numeric equivalents.