Fluentd write to Oracle DB - CloudCommandos/JohnChan GitHub Wiki

Assumptions

This guide assumes that you have an Oracle DB set up and are able to receive external connections via SQL*Plus. This guide is a continuation of Fluentd and Fluentbit.

Set up Fluentd node (Host1) environment

Install Oracle Instant Client

  • Client Package - Basic or Basic Lite
  • Client Package - SDK
  • Client Package - SQL*Plus

from here E.g.

wget https://download.oracle.com/otn_software/linux/instantclient/19600/instantclient-basic-linux.x64-19.6.0.0.0dbru.zip
wget https://download.oracle.com/otn_software/linux/instantclient/19600/instantclient-sqlplus-linux.x64-19.6.0.0.0dbru.zip
wget https://download.oracle.com/otn_software/linux/instantclient/19600/instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip

Unzip the packages (Change the path accordingly)

mkdir -p /opt/oracle
cd /opt/oracle
unzip path/to/instantclient-basic-linux.x64-19.6.0.0.0dbru.zip
unzip path/to/instantclient-sqlplus-linux.x64-19.6.0.0.0dbru.zip
unzip path/to/instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip

Install dependency

sudo apt-get install libaio1

Set environment variables (change the instantclient folder name accordingly)

nano ~/.bashrc
...
export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_6
export PATH=/opt/oracle/instantclient_19_6:$PATH

Exit your current session and login again or run the following commands

export LD_LIBRARY_PATH=/opt/oracle/instantclient_19_6
export PATH=/opt/oracle/instantclient_19_6:$PATH

Install Oracle adapter and dependencies on your Fluentd node

td-agent-gem install ruby-oci8 -v "~> 2.2.5" --no-ri --no-rdoc
td-agent-gem install fluent-plugin-sql -v "~> 0.6.1" --no-ri --no-rdoc
td-agent-gem install activerecord-oracle_enhanced-adapter -v "~> 1.8.2" --no-ri --no-rdoc

Fluentd Node (Host1) td-agent.conf

Add Input plugin config into td-agent.conf. Change to your Oracle DB credentials accordingly.

...
## Output plugin: This is to write memory usage logs tagged as 'memory_usage' into Oracle DB
<match memory_usage>
  @type sql
  host 10.0.1.77
  port 1521
  database ORCLCDB
  adapter oracle_enhanced
  username sys
  password Oradoc_db1

  <table>
    table fluentd_log
    column_mapping 'timestamp:created_at,Mem.total:mem_total,Mem.used:mem_used'
    # This is the default table because it has no "pattern" argument in <table>
    # The logic is such that if all non-default <table> blocks
    # do not match, the default one is chosen.
    # The default table is required.
  </table>
</match>
...

Restart and check the fluentd service

sudo systemctl restart td-agent.service
sudo systemctl status td-agent.service

If there is an error, you can run

td-agent

To get more detailed error message.

If the error is about conflicting versions of tzinfo, uninstall the tzinfo with version > 2.0

td-agent-gem uninstall tzinfo

Also make sure that you have the following table created in your Oracle DB if you are following this guide to log memory usage.

CREATE TABLE FLUENTD_LOG
(
  ID          NUMBER(8),
  CREATED_AT  VARCHAR2(50 BYTE),
  MEM_TOTAL   VARCHAR2(50 BYTE),
  MEM_USED    VARCHAR2(50 BYTE)
);

Fluentbit Node (Host2) td-agent-bit.conf

Configure Host2 to send memory usage logs

...
## Input plugin: This is to collect memory usage per second
[INPUT]
    Name       mem
    Tag        memory_usage

## Output plugin: This is to forward logs tagged with 'memory_usage'
[OUTPUT]
    Name  forward
    Match memory_usage
    Host          10.0.1.77
    Port          24284
    Self_Hostname fluentbit_10.0.1.78
...

Restart Fluentbit on Host2

sudo systemctl restart td-agent-bit.service
⚠️ **GitHub.com Fallback** ⚠️