Create the Rich History Database

This topic contains information to help you specify an Oracle database connection and choose channels to create the rich history database. You’ll use this database to make analytics reports and visualizations of your ledger’s activities.

What's the Rich History Database?

The rich history database is external to Oracle Blockchain Platform and contains data about the blockchain ledger’s transactions on the channels you select. You use this database to create analytics reports and visualization about your ledger’s activities.

For example, using the rich history database, you could create analytics to learn the average balance of all of the customers in your bank over some time interval, or how long it took to ship merchandise from a wholesaler to a retailer.

Internally, Oracle Blockchain Platform uses the Hyperledger Fabric history database to manage the ledger and present ledger transaction information to you in the console. Only the chaincodes can access this history database, and you can’t expose the Hyperledger Fabric history database as a data source for analytical queries. The rich history database uses an external Oracle database and contains many details about every transaction committed on a channel. This level of data collection makes the rich history database an excellent data source for analytics. For information about the data that the rich history database collects, see Rich History Database Tables and Columns.

You can only use an Oracle database such as Oracle Autonomous Data Warehouse or Oracle Database Cloud Service with Oracle Cloud Infrastructure to create your rich history database. You use the Oracle Blockchain Platform console to provide the connection string and credentials to access and write to the Oracle database. Note that the credentials you provide are the database’s credentials and Oracle Blockchain Platform doesn’t manage them. After you create the connection, you’ll select the channels that contain the ledger data that you want to include in the rich history database. See Enable and Configure the Rich History Database.

You can use any analytics tool, such as Oracle Analytics Cloud or Oracle Data Visualization Cloud Service, to access the rich history database and create analytics reports or data visualizations.

Create the Oracle Database Cloud Service Connection String

You must collect information from the Oracle Database Cloud Service deployed on Oracle Cloud Infrastructure to build the connection string required by the rich history database. You must also enable access to the database through port 1521.

Find and Record Oracle Database Cloud Service Information

The information you need to create a connection to the Oracle Database Cloud Service is available in the Oracle Cloud Infrastructure Console.

  1. From the Infrastructure Console, click the navigation menu in the top left corner, and then click Database.

  2. Locate the database that you want to connect to and record the Public IP address.

  3. Click the name of the database that you want to connect to and record the values in these fields:

    • Database Unique Name

    • Host Domain Name

    • Port

  4. Find a username and password of a database user with permissions to read from this database, and make a note of these. For example, the user SYSTEM.

Enable Database Access Through Port 1521

Add an ingress rule that enables the rich history database to access the database through port 1521.

  1. In the Oracle Cloud Infrastructure home page, click the navigation icon and then under Databases click DB Systems.

  2. Click the database that you want to connect to.

  3. Click the Virtual Cloud Network link.

  4. Navigate to the appropriate subnet, and under Security Lists, click Default Security List For <Target Database>.

    The Security List page is displayed.

  5. Click Edit All Rules.

  6. Add an ingress rule to allow any incoming traffic from the public internet to reach port 1521 on this database node, with the following settings:

    • SOURCE CIDR: 0.0.0.0/0

    • IP PROTOCOL: TCP

    • SOURCE PORT RANGE: All

    • DESTINATION PORT RANGE: 1521

    • Allows: TCP traffic for ports: 1521

Build the Connection String

After enabling access to the Oracle database, use the information you collected to build the connection string in the Configure Rich History dialog.

Construct the connection string like this: <publicIP>:<portNumber>/<database unique name>.<host domain name>

For example, 123.213.85.123:1521/CustDB_iad1vm.sub05031027070.customervcnwith.oraclevcn.com

Ensure the Database User has Correct Privileges

In order for the rich history functionality to be able to manage its database sessions and to recover from temporary database or network downtime, ensure the database user registered with Oracle Blockchain Platform has the following two privileges:
grant select on v_$session to <user>;
grant alter system to <user>;
If the database user doesn't have those already, they will need to be granted by the system database administrator.

Without these privileges Oracle Blockchain Platform will be able to replicate to the database but it won't be able to recover from situations leading to a damaged database session, preventing the rich history from catching up with recent transactions for an extended period.

Enable and Configure the Rich History Database

Use the console to provide database connection information and select the channels with the chaincode ledger data that you want to write to the rich history database. By default channels aren’t enabled to write data to the rich history database.

Note the following information:

  • Each blockchain network member configures its own rich history database.

  • You must use an Oracle database. No other database types are supported.

  1. Enter connection and credential information for the Oracle database that you want to use to store rich history information.
    1. Go to the console and click the Options button and click Configure Rich History. This button is located above the bar that contains the tabs that you use to navigate to nodes, channels, and chaincodes.
      The Configure Rich History dialog is displayed.
    2. Enter the user name and password required to access the Oracle database.
    3. In the Connection String field, enter the connection string for the database that you’ll use to store rich history data. What you enter here depends on the Oracle database you’re using.
      • If you’re using Oracle Autonomous Data Warehouse, then you’ll enter something similar to <username>adw_high. To find Oracle Autonomous Data Warehouse’s connection information, go to its credential wallet ZIP file and open its TNS file.
      • If you’re using Oracle Database Cloud Service with Oracle Cloud Infrastructure, see Create the Oracle Database Cloud Service Connection String.
      • If you're using a non-autonomous Oracle database (a database that doesn't use a credential wallet) and want to use the sys user to connect to the database, then you must append ?as=sys[dba|asm|oper] to the connection string. For example, 123.123.123.123:1521/example.oraclevcn.com?as=sysdba
    4. If you’re using an Oracle Cloud autonomous database instance (for example, Oracle Autonomous Data Warehouse or Oracle Autonomous Transaction Processing), then use the Wallet Package File field to upload the required credential wallet ZIP file. This file contains client credentials and is generated from the Oracle autonomous database.
    5. Click Save.
  2. Enable rich history on the channels that contain the chaincode data that you want to write to the rich history database.
    1. Go to the console and select the Channels tab.
    2. Locate the channel that contains the chaincode data that you want to write to the rich history database. Click its More Options button and select Configure Rich History.
      The Configure Rich History dialog is displayed.
    3. Click the Enable Rich History checkbox. Click Save.

Modify the Connection to the Rich History Database

You can change the rich history database’s connection information.

  1. Go to the console and click the Options button and click Configure Rich History. This button is located above the bar that contains the tabs that you use to navigate to nodes, channels, and chaincodes.
  2. If needed, update the user name and password required to access the Oracle database.
  3. If needed, in the Connection String field, modify the connection string for the database that you’ll use to store rich history data. What you enter here depends on the Oracle database you’re using.
    • If you’re using Oracle Autonomous Data Warehouse, then you’ll enter something similar to <username>adw_high. To find Oracle Autonomous Data Warehouse’s connection information, go to its credential wallet ZIP file and open its TNS file.
    • If you’re using Oracle Database Cloud Service with Oracle Cloud Infrastructure, see Create the Oracle Database Cloud Service Connection String.
    • If you're using a non-autonomous Oracle database (a database that doesn't use a credential wallet) and want to use the sys user to connect to the database, then you must append ?as=sys[dba|asm|oper] to the connection string. For example, 123.123.123.123:1521/example.oraclevcn.com?as=sysdba
  4. If you’re using an Oracle Cloud autonomous database instance (for example, Oracle Autonomous Data Warehouse or Oracle Autonomous Transaction Processing), then use the Wallet Package File field to upload or re-upload the required credential wallet file. This file contains client credentials and is generated from the Oracle autonomous database.
  5. Click Save.

Choose the Channels that Write Data to the Rich History Database

You can add channels to write chaincode ledger data to the rich history database, or you can stop channels from writing data to the rich history database.

You must specify information to connect to the rich history’s database before you can select channels that write to the rich history database. See Enable and Configure the Rich History Database.
  1. Go to the console and select the Channels tab.
  2. To add or remove a channel, locate the channel that you want to modify access for. Click its More Options button and select Configure Rich History.
    The Configure Rich History dialog is displayed.
  3. To add the channel, click the Enable Rich History checkbox. To remove the channel, clear the Enable Rich History checkbox.
  4. Click Save.

Rich History Database Tables and Columns

The rich history database contains three tables for each channel: history, state, and latest height. You’ll query the history and state tables when you create analytics about your chaincodes’ ledger transactions.

History Table

The <instanceName><channelName>_hist table contains ledger history. The data in this table tells you the chaincode ID, key used, if the transaction was valid, the value assigned to the key, and so on.

Note that the value and valueJson columns are used in a mutually exclusive way. That is when a key value is valid json, then the value is set into the valueJson column. Otherwise the value is set in the value column. The valueJson column is set up as a json column in the database, which means users can query that column using the usual Oracle JSON specific extensions.

Column Datatype
chaincodeId VARCHAR2 (256)
key   VARCHAR2 (1024)
txnIsValid     NUMBER (1)
value VARCHAR2 (4000)
valueJson CLOB
blockNo NUMBER NOT NULL
txnNo NUMBER NOT NULL
txnId VARCHAR2 (128)  
txnTimestamp TIMESTAMP
txnIsDelete NUMBER (1)

State Table

The <instanceName><channelName>_state table contains data values replicated from the state database. You’ll query the state table when you create analytics about the state of the ledger.

Note that the value and valueJson columns are used in a mutually exclusive way. That is when a key value is valid json, then the value is set into the valueJson column. Otherwise the value is set in the value column. The valueJson column is set up as a json column in the database, which means users can query that column using the usual Oracle JSON specific extensions.

Column Datatype
chaincodeId VARCHAR2 (256)
key VARCHAR2 (1024)
value VARCHAR2 (4000) 
valueJson CLOB
blockNo NUMBER
txnNo NUMBER

Latest Height Table

The <instanceName><channelName>_last table is used internally by Oracle Blockchain Platform to track the block height recorded in the rich history database. It determines how current the rich history database is and if all of the chaincode transactions were recorded in the rich history database. You can’t query this database for analytics.