Enterprise Manager 13c - Database Usage Tracking Report Bug with FTP destination

Posted by Dirk Nachbar on Thursday, April 04, 2019
Within Oracle Enterprise Manager 13c you have a nice Feature under the BI Publisher Reports to generate Database Usage Tracking Reports and to deliver them to a FTP Server, called BurstToFTP.


Unfortunatelly if you follow the very good official step-by-step documentation on how to setup and to configure the Database Usage Tracking Report you will not be able to get your Database Usage Tracking Reports under your FTP Server :-(


After you are done with the above setup and configuration steps and you perform an execution of the Database Usage Tracking Report, you will receive following error:

[INSTANCE_ID=.1515472353767] [DELIVERY_ID=1075]Error deliver document to FTP::Exception happened when calling deliver API::Error deliver document to FTP::FILE=[::Exception happened when calling deliver API::oracle.xdo.delivery.DeliveryException: oracle.xdo.delivery.DeliveryException: SFTP Client cannot be openedoracle.xdo.service.delivery.DeliveryException: oracle.xdo.delivery.DeliveryException: oracle.xdo.delivery.DeliveryException: SFTP Client cannot be op

If you are checking the corresponding bipublisher.log under gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/bipublisher you will find following error messages:

[2019-02-01T09:24:22.148+01:00] [BIP] [WARNING] [] [oracle.xdo] [tid: 168606] [userId: BISystemUser] [ecid: cdc36ce1-e46c-486e-93a8-cee32db72219-00000004,0:1070] [APP: bipublisher#11.1.1]  !!!!!!! GenericDeliveryListener.onMessage :: DeliveryProcessor.processDelivery exception::[INSTANCE_JOB_ID=1529]::[JOB_ID=1583]::[OUTPUT_ID=1402]::[DELIVERY_ID=1391]::[STATUS=FAILED_DELIVERY]::Error deliver document to FTP::Exception happened when calling deliver API::Error deliver document to FTP::FILE=[::Exception happened when calling deliver API::oracle.xdo.delivery.DeliveryException: oracle.xdo.delivery.DeliveryException: SFTP Client cannot be openedoracle.xdo.service.delivery.DeliveryException: oracle.xdo.delivery.DeliveryException: oracle.xdo.delivery.DeliveryException: SFTP Client cannot be opened[[
        at oracle.xdo.service.delivery.impl.DeliveryServiceImpl.deliverToSFTP(DeliveryServiceImpl.java:810)
        at oracle.xdo.enterpriseScheduler.delivery.FTPProcessor.deliver(FTPProcessor.java:255)
        at oracle.xdo.enterpriseScheduler.delivery.FTPProcessor.processDelivery(FTPProcessor.java:123)
        at oracle.xdo.enterpriseScheduler.util.GenericDeliveryListener.onMessage(GenericDeliveryListener.java:120)
        at oracle.xdo.enterpriseScheduler.util.CheckpointEnabledListener.onMessage(CheckpointEnabledListener.java:25)
        at oracle.xdo.enterpriseScheduler.jmswrapper.client.JMSMessageConsumer.processMessage(JMSMessageConsumer.java:231)
        at oracle.xdo.enterpriseScheduler.jmswrapper.client.ThreadedMessageDispatcher$DispatchWorker.run(ThreadedMessageDispatcher.java:157)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:745)
Caused by: oracle.xdo.delivery.DeliveryException: oracle.xdo.delivery.DeliveryException: SFTP Client cannot be opened
        at oracle.xdo.delivery.ssh2.SFTPDeliveryRequestHandler.submitRequest(SFTPDeliveryRequestHandler.java:528)
        at oracle.xdo.delivery.AbstractDeliveryRequest.submit(AbstractDeliveryRequest.java:1513)
        at oracle.xdo.service.delivery.impl.DeliveryServiceImpl.deliverToSFTP(DeliveryServiceImpl.java:804)
        ... 9 more

The Problem occurs under Oracle Enterprise Linux and Redhat Enterprise Linux.

This is a know bug and luckily there has been a Patch released for this issue.

You will need to download the Patch 23233792: FTP DELIVERY FAILS WITH ORACLE.XDO.DELIVERY.SSH2.SSHEXCEPTION for 12.1.3.0




After you applied the Patch to your Oracle Cloud Control 13c, the BurstToFTP Feature will work without problems.