1. Using Oracle Autonomous Database Serverless
  2. Reference
  3. Autonomous Database for Experienced Oracle Database Users
  4. Autonomous Database Views
  5. Oracle Cloud Infrastructure Logging Interface Views

Oracle Cloud Infrastructure Logging Interface Views

The Oracle Cloud Infrastructure (OCI) logging interface views enable authorized users to access log data through a set of views.

Oracle Cloud Infrastructure (OCI) logging is the central logging solution for OCI services, including VCN flow logs, load balancer logs, object storage logs, and others within the added capability to include custom logs. The logs are not stored in Autonomous Database. Instead, the logs are dynamically retrieved based on various predicates provided by the user, including date range, log group, log name, and others.

  • Oracle Cloud Infrastructure Logging Interface Overview
    Providing access to the log data inside an Autonomous Database in relational format provides a useful access method, along with the ability to get the logging data in relational or JSON format and enrich the analytics by joining with other data they may have in the Autonomous Database.
  • OCI_LOG_LIST View
    Displays a list of all logs in the tenant. The view does not show log data, but provides the metadata about the logs in a tenancy. This metadata is used to properly specify parameter values for other logging view. If there is no logs under a log group, that log group will not be included in this view.
  • OCI_LOG_DATA View
    The common view that provides access to all log types supported by the OCI Logging Service. The log content is in the DATA column.
  • OCI_VCN_FLOWLOGS View
    View that provides details about traffic that passes through the user's VCN. This log enables users to audit traffic and troubleshoot security lists. Each flow log record reflects logged traffic in one direction of a connection between two endpoints. For example, a single TCP connection, you may have two records in the capture window: one for ingress traffic, and one for egress traffic.
  • OCI_LBLOG_ACCESS View
    View that provides load balancer access logs capturing detailed information about requests sent to a load balancer. Each entry contains the time the request was received, client, intermediate HTTP proxy IP addresses, and times used by at the load balancer and backend to process the request.
  • OCI_LBLOG_ERRORS View
    View that provides load balancer error logs capturing detailed information about requests related to troubleshooting and monitoring. Each entry contains information such as the time the request was received, error type, and additional details of the specific error.

Parent topic: Autonomous Database Views

Oracle Cloud Infrastructure Logging Interface Overview

Providing access to the log data inside an Autonomous Database in relational format provides a useful access method, along with the ability to get the logging data in relational or JSON format and enrich the analytics by joining with other data they may have in the Autonomous Database.

Oracle logging service provides access to logs from Oracle Cloud Infrastructure (OCI) resources. These logs include critical diagnostic information that describes how resources are performing and being accessed. There are varying log types for each Oracle service. To learn more about the logging service and supported OCI services, see Oracle Cloud Infrastructure Logging Overview.

Through the Oracle Cloud Infrastructure Logging Interface, log data can now be accessed through an Autonomous Database in relational format. Users can query different log data in OCI across all compartments and regions. The implemented OCI views use the OCI$RESOURCE_PRINCIPAL credential.

To use these views, the following steps need to be done.
  1. An Administrator enables use of the OCI$RESOURCE_PRINCIPAL credential in the database.
  2. Users create a new dynamic group for the OCI$RESOURCE_PRINCIPAL credential.

To learn more about enabling the credential, setting up a dynamic group, and creating policy statements, see Use Resource Principal to Access Oracle Cloud Infrastructure Resources.

As an Administrator, enable the resource principal.
EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
The dynamic group must have the following rules that include the resource ID of the instance. 
resource.id = '<resource.id>'
There are two policy statements required to access logging views.
-- For OCI Logging Views
Allow dynamic-group <group-name> to use logging-family in tenancy
Allow dynamic-group <group-name> to use compartments in tenancy

All the views have mandatory and optional predicate values (column names).

Predicate values are as follows:

Note:

OCI_LOG_LIST view does not have mandatory columns. If the REGION value is not provided, the view returns data for the home region.
  • REGION (Mandatory)
  • COMPARTMENT_ID (Mandatory)
  • LOG_GROUP_ID (Mandatory)
  • LOG_ID (Optional) - If the value is provided, query results will use the LOG_IDvalue. If no value provided, the value will be returned from the server.
  • START_TIME (Optional) - If no value provided, return values are for past 5 minutes in GMT.
  • END_TIME (Optional) - If no value provided, return values are for past 5 minutes in GMT.
  • SEARCH_CRITERIA (Optional) - If no value provided, null will be returned.

Since there can be different log types within a single log group, it's recommended to specify LOG_ID in a predicate when using OCI_VCN_FLOWLOGS, OCI_LBLOG_ACCESS, and OCI_LBLOG_ERRORS views.

Notes and Restrictions:
  • The common view OCI_LOG_DATA supports all log types, so COMPARTMENT_ID, LOG_GROUP_ID, and REGION are sufficient. Likewise, if a user uses the LOG_ID of a load balancer with the OCI_VCN_FLOWLOGS view, it will either not return correct data or any data at all.
  • For the OCI_LOG_LIST view, the select * from OCI_LOG_LIST will shows data only for the home region. If you need data for another region, you will have to use a REGION equality predicate in the WHERE clause.
  • If the OCI_LOG_DATA, OCI_VCN_FLOWLOGS, OCI_LBLOG_ACCESS, and OCI_LBLOG_ERRORS, the select * from <view> gives an ORA-20000: compartment_id,log_group_id and region are mandatory predicates, please provide valid values for them as equality predicate in the WHERE clause. provide the required predicate values.
  • The framework supports querying up to past 7 days (from current time ) log data to query from the supported views. Any values for the predicates START_TIME and END_TIME predicates outside this range will result in ORA-20000: start_time and end_time should be from current_timestamp to current_timestamp-7.
  • If you do not provide the listed mandatory predicates, you will see an ORA-20000: compartment_id,log_group_id and region are mandatory predicates, please provide valid values for them as equality predicate in the WHERE clause. Other columns can be used as predicates of any type (=, IN). The mandatory predicates can be only used once in a query, and they cannot be used multiple times with AND or NOT.
  • Values for START_TIME and END_TIME have to be given in DD-MM-YY HH24:MI:SS format. If not provided in this format, the query will not return any data.

Parent topic: Oracle Cloud Infrastructure Logging Interface Views

OCI_LOG_LIST View

Displays a list of all logs in the tenant. The view does not show log data, but provides the metadata about the logs in a tenancy. This metadata is used to properly specify parameter values for other logging view. If there is no logs under a log group, that log group will not be included in this view.

Column Datatype Description

REGION

VARCHAR2

OCI region name

COMPARTMENT_ID

VARCHAR2

Compartment OCID

LOG_GROUP_ID

VARCHAR2

LOG_GROUP OCID

LOG_GROUP_NAME

VARCHAR2

Log group name.

LOG_ID

VARCHAR2

LOG OCID

LOG_NAME

VARCHAR2

Log name

LOG_SERVICE

VARCHAR2

Log service

Parent topic: Oracle Cloud Infrastructure Logging Interface Views

OCI_LOG_DATA View

The common view that provides access to all log types supported by the OCI Logging Service. The log content is in the DATA column.

Column Datatype Description

DATE_TIME

TIMESTAMP

Same as the oracle.ingestedtime field

ID

VARCHAR2

A random UUID for each log entry

SOURCE

VARCHAR2

Resource name that generated the log message

SPEC_VERSION

NUMBER

Version of the CloudEvents specification this log message uses

LOG_TIME

TIMESTAMP

Log message generation time

TYPE

VARCHAR2

Log message type

COMPARTMENT_ID

VARCHAR2

Compartment OCID

INGESTED_TIME

TIMESTAMP

Log message ingest time

LOG_GROUP_ID

VARCHAR2

Log group OCID

LOG_ID

VARCHAR2

Log OCID

TENANT_ID

VARCHAR2

Tenant OCID of log object owner

DATA

CLOB

Log message

ORACLE_DETAILS

CLOB

Oracle-specific metadata

Parent topic: Oracle Cloud Infrastructure Logging Interface Views

OCI_VCN_FLOWLOGS View

View that provides details about traffic that passes through the user's VCN. This log enables users to audit traffic and troubleshoot security lists. Each flow log record reflects logged traffic in one direction of a connection between two endpoints. For example, a single TCP connection, you may have two records in the capture window: one for ingress traffic, and one for egress traffic.

Column Datatype Description

REGION

VARCHAR2

OCI region name

DATE_TIME

TIMESTAMP

Same as the oracle.ingestedtime field

ID

VARCHAR2

A random UUID unique to each log entry

ACTION

VARCHAR2

Possible values of ACCEPT or REJECT

FLOW_ID

VARCHAR2

Hash of key fields (source and destination addresses, ports, and protocol)

STATUS

VARCHAR2

Possible values of OK, NODATA, or SKIPDATA

END_TIME

TIMESTAMP

Capture window end time

PACKETS

NUMBER

Number of packets recorded in capture window

VERSION

VARCHAR2

Flow log record schema number

BYTES_OUT

NUMBER

Number of bytes recorded in capture window

PROTOCOL

NUMBER

IANA protocol number

START_TIME

TIMESTAMP

Capture window start time

SOURCE_PORT

NUMBER

Source IANA port number

PROTOCOL_NAME

VARCHAR2

IANA protocol name

SOURCE_ADDRESS

VARCHAR2

IP address of the source in IPv4 or IPv6 notation

DESTINATION_PORT

NUMBER

Destination IANA port number

DESTINATION_ADDRESS

VARCHAR2

IP address of the destination in IPv4 or IPv6 notation

TIME

TIMESTAMP

Same as START_TIME

TYPE

VARCHAR2

Log category

LOG_ID

VARCHAR2

Log OCID

TENANT_ID

VARCHAR2

Tenant OCID

VNIC_OCID

VARCHAR2

VNIC OCID

LOG_GROUP_ID

VARCHAR2

Log group OCID

INGESTED_TIME

TIMESTAMP

Time log ingested by OCI Logging

COMPARTMENT_ID

VARCHAR2

Log group compartment OCID

VNIC_SUBNET_OCID

VARCHAR2

VNIC subnet OCID

VNIC_COMPARTMENT_ID

VARCHAR2

VNIC compartment OCID

SOURCE

VARCHAR2

Resource name that generated log message

SPEC_VERSION

NUMBER

Version of the CloudEvents specification this log message uses

Parent topic: Oracle Cloud Infrastructure Logging Interface Views

OCI_LBLOG_ACCESS View

View that provides load balancer access logs capturing detailed information about requests sent to a load balancer. Each entry contains the time the request was received, client, intermediate HTTP proxy IP addresses, and times used by at the load balancer and backend to process the request.

Column Datatype Description

REGION

VARCHAR2

OCI region name

DATE_TIME

TIMESTAMP

Same as the oracle.ingestedtime field

ID

VARCHAR2

A random UUID unique to each log entry

BACKEND_ADDRESS

VARCHAR2

IP address and port number of the backend server which processed the client request

BACKEND_CONNECT_TIME

NUMBER

Time spent (in seconds, with millisecond precision) to establish backend server connection

BACKEND_PROCESSING_TIME

NUMBER

Total time taken (in seconds, with millisecond precision) from the load balancer establishing a connection to a backend until it completes

BACKEND_STATUS_CODE

NUMBER

Status code of the response from the target

CLIENT_ADDRESS

VARCHAR2

IP address and port number of the requesting client

FORWARDED_FOR_ADDRESS

VARCHAR2

IP address of the client and http proxies between client and load balancer

HOST

VARCHAR2

Domain name which resolves to IP address assigned to the load balancer

LB_STATUS_CODE

NUMBER

Load balancer status code

LISTENER_NAME

VARCHAR2

Listener which received the incoming traffic request on the load balancer's IP address

RECEIVED_BYTES

NUMBER

Total size of the request (in bytes) received from the client

REQUEST

VARCHAR2

Request line received from the client

REQUEST_PROCESSING_TIME

NUMBER

Total time taken (in seconds, with millisecond precision) from the load balancer receiving the request from the client until load balancer completes sending response to the client.

ROUTING_RULES_ENGINE_ERRORS

NUMBER

Routing rule engine error during policy evaluation of the request with a 0 (no error) or 1 (error). If an error occurred, requests are forwarded to the default backend attached to the listener.

ROUTING_RULES_MATCHED_RULE

VARCHAR2

Routing policy rule name, which was matched for this specific request

ROUTING_RULES_RULE_HITS

NUMBER

Number of routing rules evaluated to true for the request

ROUTING_RULES_RULE_MISSES

NUMBER

Number of routing rules evaluated to false for the request

SENT_BYTES

NUMBER

Total size of the request (in bytes) sent to the client

SSL_CIPHER

VARCHAR2

Negotiated SSL cipher between the client and the load balancer

SSL_PROTOCOL

VARCHAR2

Negotiated SSL protocol between the client and the load balancer

TIMESTAMP

TIMESTAMP

Log entry generation time

USER_AGENT

VARCHAR2

User agent used to send the request to the load balancer

TIME

TIMESTAMP

Log entry generation time

TYPE

VARCHAR2

Log category

LOG_ID

VARCHAR2

Log OCID

TENANT_ID

VARCHAR2

Tenant OCID

LOG_GROUP_ID

VARCHAR2

Log group OCID

RESOURCE_ID

VARCHAR2

Resource OCID

INGESTED_TIME

TIMESTAMP

Time log ingested by OCI Logging

COMPARTMENT_ID

VARCHAR2

Log group compartment OCID

SOURCE

VARCHAR2

Resource name that generated log message

SUBJECT

VARCHAR2

Subject of the log

SPEC_VERSION

NUMBER

Version of the CloudEvents specification this log message uses

Parent topic: Oracle Cloud Infrastructure Logging Interface Views

OCI_LBLOG_ERRORS View

View that provides load balancer error logs capturing detailed information about requests related to troubleshooting and monitoring. Each entry contains information such as the time the request was received, error type, and additional details of the specific error.

Column Datatype Description

REGION

VARCHAR2

OCI region name

DATE_TIME

TIMESTAMP

Same as the oracle.ingestedtime field

ID

VARCHAR2

A random UUID unique to each log entry

ERROR_LOG_TYPE

VARCHAR2

Log type

ERROR_LOG_ERROR_DETAILS

VARCHAR2

Detailed description of the error message

DATA_TIMESTAMP

TIMESTAMP

Log entry generation time

TIME

TIMESTAMP

Log entry generation time

TYPE

VARCHAR2

Log category

LOG_ID

VARCHAR2

Log OCID

TENANT_ID

VARCHAR2

Tenant OCID

LOG_GROUP_ID

VARCHAR2

Log group OCID

RESOURCE_ID

VARCHAR2

Resource OCID

INGESTED_TIME

TIMESTAMP

Time log ingested by OCI Logging

COMPARTMENT_ID

VARCHAR2

Log group compartment OCID

SOURCE

VARCHAR2

Resource name that generated log message

SUBJECT

VARCHAR2

Subject of the log

SPEC_VERSION

NUMBER

Version of the CloudEvents specification this log message uses

Parent topic: Oracle Cloud Infrastructure Logging Interface Views