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 Classic 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 standard tables or blockchain tables to store the rich history database. Blockchain tables are tamperproof append-only tables, which can be used as a secure ledger while also being available for transactions and queries with other tables. For more information, see Oracle Blockchain Table.

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 Classic Cloud Service Connection String

You must collect information from the Oracle Database Classic 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 Classic Cloud Service Information

The information you need to create a connection to the Oracle Database Classic 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 user name 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 then 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 using the following syntax: <publicIP>:<portNumber>/<database unique name>.<host domain name>

For example, 192.0.2.0:1521/CustDB_iad1vm.sub05031027070.customervcnwith.oraclevcn.example.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>;

Additionally, if the rich history database uses Oracle Autonomous Data Warehouse, the database user must have the following privilege:

grant unlimited tablespace to <user>;

If the database user doesn't have those privileges already, they must be granted by the system database administrator.

Without these privileges Oracle Blockchain Platform can replicate to the database but it cannot recover from situations leading to a damaged database session, which prevents the rich history from catching up with recent transactions for an extended period. Without these privileges on Oracle Autonomous Data Warehouse, no rich history data is saved.

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.

• Each channel that writes to the rich history database must contain at least one peer node.

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 box 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 Classic Cloud Service with Oracle Cloud Infrastructure, see Create the Oracle Database Classic 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.

      Note:

      When you open the Configure Rich History dialog box again after you configure rich history, the wallet file name is not displayed. If you update other settings, you must upload the wallet ZIP file again before clicking Save. If you click Save while no wallet file name is displayed, the configuration is updated not to use a wallet file.
   5. To use blockchain tables to store the rich history database, select Use Database Blockchain Table.
      The underlying database must support blockchain tables. For more information, see Oracle Blockchain Table.

      • To specify the number of days to retain tables and rows, select Basic Configuration, and then enter the number of days to retain tables and rows. Enter 0 to retain tables or rows permanently. To prevent further changes to the retention values, select Locked.
      • To specify table and row retention by using a data definition language (DDL) statement, select Advanced Configuration Query and then enter the DDL statement.
   6. 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 check box. To store private data collections in the rich history database, enter a list of private data collection names, separated by commas. For more information about private data collections, see What Are Private Data Collections?. To add transaction details to the rich history database, select the details that you want added. Click Save.

The rich history database is configured, but tables are not created in the database immediately. When the next relevant transaction or ledger change happens, the tables are created in the rich history database.

Modify the Connection to the Rich History Database

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

After tables are created in the database for a channel, modifying the rich history configuration for the channel has no effect, even after you click Save, unless you change the user name and password or the connection string. If you change the user name and password, tables are created in the same database. If you change the connection string and credentials, a different database is configured, and tables are created after the next relevant transaction or ledger change. You cannot change a rich history database from standard tables to blockchain tables, and you cannot change retention times, unless you also change the credentials or connection string.

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 Classic Cloud Service with Oracle Cloud Infrastructure, see Create the Oracle Database Classic 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.

   Note:

   When you open the Configure Rich History dialog box again after you configure rich history, the wallet file name is not displayed. If you update other settings, you must upload the wallet ZIP file again before clicking Save. If you click Save while no wallet file name is displayed, the configuration is updated not to use a wallet file.
5. To use blockchain tables to store the rich history database, select Use Database Blockchain Table.
   The underlying database must support blockchain tables.

   • To specify the number of days to retain tables and rows, select Basic Configuration, and then enter the number of days to retain tables and rows. Enter 0 to retain tables or rows permanently. To prevent further changes to the retention values, select Locked.
   • To specify table and row retention by using a data definition language (DDL) statement, select Advanced Configuration Query and then enter the DDL statement.
6. Click Save.

Configure the Channels that Write Data to the Rich History Database

You can enable channels to write chaincode ledger data to the rich history database, and you can stop channels from writing data to the rich history database. You can also configure an individual channel to use a different rich history database configuration than the global setting.

You must specify the global information to connect to the rich history database before you can select channels that write to the rich history database. See Enable and Configure the Rich History Database.

After tables are created in the database for a channel, modifying the rich history configuration for the channel has no effect, even after you click Save, unless you change the user name and password or the connection string. If you change the user name and password, tables are created in the same database. If you change the connection string and credentials, a different database is configured, and tables are created after the next relevant transaction or ledger change. You cannot change a rich history database from standard tables to blockchain tables, and you cannot change retention times, unless you also change the credentials or connection string.

1. Go to the console and select the Channels tab.
2. 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 enable collection of rich history data for the channel, select the Enable Rich History check box. To disable collection of rich history data for the channel, clear the Enable Rich History check box.
4. To configure the channel to collect rich history data using a different database or different settings, select Use channel level configuration, and then specify the settings to use.
   For more information about the rich history settings, see Enable and Configure the Rich History Database.
5. Click Save.

Monitor the Rich History Status

After configuring the rich history database, you can use the console to monitor the rich history replication status.

1. Go to the console and select the Channels tab.
2. In the channels table, click the More Actions button for the channel that you want to monitor, and then click Rich History Status.
   The Rich History Status dialog box is displayed, which includes details about replication and configuration status.
3. Click Refresh to display the latest status.

Limit Access to Rich History

You can use channel policies and access control lists (ACLs) to limit the organizations that can configure the rich history database and retrieve rich history status or configuration information.

By default, all organizations that have administrative access to a channel can configure rich history collection and can retrieve rich history status and configuration details. To limit this access to, for example, the founder organization, you create a channel policy and apply the policy to the resources that control access.

1. Go to the console and select the Channels tab.
   The Channels tab is displayed. The channel table contains a list of all of the channels on your network.
2. In the channel table, click the name of the channel where you want to limit access.
3. Click Channel Policies, and then create a signature policy that includes the organization members that will access the rich history functions.
   For more information about channel policies, see Work With Channel Policies and ACLs.
   For example, create a policy that includes only the identity of the founder organization, not the identity of any participant organizations.
4. Click ACLs.
5. In the Resources table, locate the resource that you want to update to use the new policy. Click Expand for the resource and then select the policy to assign to the resource
   The following table shows the resources that control access to rich history.

   Resource	Access control
   obpadmin/ConfigureRichHistoryChannel	Controls configuring, enabling, and disabling rich history for a channel.
   obpadmin/GetRichHistoryChannelStatus	Controls retrieving rich history replication status for a channel.
   obpadmin/GetRichHistoryChannelConfig	Controls retrieving the current rich history configuration for a channel.

6. Click Update ACLs.

The rich history access is now controlled by the new policy. Organization members that are not included in the new policy will receive an error message when they attempt to access a resource that is controlled by the policy.

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. If you've chosen to select any of the transaction details when enabling the rich history, an additional table will be created with the transaction details.

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.

If configured, private data is also stored in this table. For private data, the chaincode ID uses the following format: <chaincodeName>$$<collectionName>.

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.

Transaction Details Table

The <instanceName><channelName>_more table contains attributes related to committed transactions. When enabling the rich history database, you can select which of these attributes you want to record in this table. The transaction details table only captures information about endorser transactions - not configuration transactions or any other kind of Hyperledger Fabric transactions.

Column	Datatype
CHAINCODEID	VARCHAR2 (256)
BLOCKNO	NUMBER
TXNNO	NUMBER
TXNID	VARCHAR2(128)
TXNTIMESTAMP	TIMESTAMP
SUBMITTERCN	VARCHAR2(512)
SUBMITTERORG	VARCHAR2(512)
SUBMITTEROU	VARCHAR2(512)
CHAINCODETYPE	VARCHAR2(32)
VALIDATIONCODENAME	VARCHAR2(32)
ENDORSEMENTS	CLOB
INPUTS	CLOB
EVENTS	CLOB
RESPONSESTATUS	NUMBER(0)
RESPONSEPAYLOAD	VARCHAR2(1024)
RWSET	CLOB
BLOCKCREATORCN	VARCHAR2(512)
BLOCKCREATORORG	VARCHAR2(512)
BLOCKCREATOROU	VARCHAR2(512)
CONFIGBLOCKNUMBER	NUMBER(0)
CONFIGBLOCKCREATORCN	VARCHAR2(512)
CONFIGBLOCKCREATORORG	VARCHAR2(512)
CONFIGBLOCKCREATOROU	VARCHAR2(512)

Note:

• Organization (ORG) and organization unit (OU) are driven by identity certificates, which implies that they may be assigned to multiple values. They are captured as a comma separated list in the table's values.
• For identities, the table includes information only about the "Subject" portion of the certificates, not the "Issuer" one.
• The RWSET column contains operations on all chaincodes (in the same ledger) performed during endorsement. As such, you will typically see both lscc read operations and the actual chaincode namespace operations.