Forwarding events from WinCC OA to OpenTSDB, which is based on HBase. Based on OpenTSDB Grafan can be used to build cool dashboards in a very fast way.
Live Example (my photovoltaic data) : http://82.149.127.81/grafana
On my test environment (i7 with 32gb RAM and 8 threads) i was able to push about 20000(!) values per second from my WinCC OA system to my Hadoop/HBase cluster. WinCC OA and the Hadoop Cluster are running on Hyper-V.
By monitoring the CPU load of my Oracle Database and the RDB-Buffer-Blocks i was able to detect a relationship … after some time the RDB Manager is not able to write data anymore and restarts… seems to be an Oracle Problem… i should add more WinCC OA metrics…
DDE: Problem Key ‘ORA 600 [peshmgel: Table size]’ was completely flood controlled (0x6)
Further messages for this problem key will be suppressed for up to 10 minutes
Errors in file /app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_50519.trc (incident=223091) (PDBNAME=PVSS):
ORA-00600: internal error code, arguments: [26599], [1], [150], [], [], [], [], [], [], [], [], []
Incident details in: /app/oracle/diag/rdbms/cdb/cdb/incident/incdir_223091/cdb_ora_50519_i223091.trc
Use ADRCI or Support Workbench to package the incident.
With the new WinCC OA version 3.13 it is very easy to use webservices. A simple example is to send alerts to an app (Android, iOS, Windows) by push notifications (powered by Windows Azure Cloud).
All you need is:
* Download a panel: http://rocworks.at/share/rocworks.pnl
* Download an Android-App: http://rocworks.at/share/rocworks.apk
* Android Barcode Scanner: https://play.google.com/store/apps/details?id=com.google.zxing.client.android
* Start the panel and scan the QR-Code with the App (top right menu button “scan”)
* You can create your own distribution channel with a click on “create new QR code” in the panel and scan it with your phone(s).
This is just a demo. It is not safe and not secure.
You can use and adapt the panel as you want.
No guarantee about the availability of the cloud service.
The sdb.ctl is a client library which can be used to access relational databases from the WinCC OA client without the need to install any database client software at the client. The connection to the relational database is handled by the WinCC OA server. The client communicates with the WinCC OA server only by data points. The SQL-Statements are transferred by data points to a WinCC OA control, this control executes the SQL-Statements and sends back the result to the client by data points.
Howto: sdb
With a little adaptor in the RDB-Manager it is easy to forward values to an Java EE Server like Glassfish (free).
And from a Java-Bean values can be forwarded to any kind of data sink.
As an example i forwared values to Oracle Event Processing
http://www.oracle.com/us/products/middleware/soa/overview/complex-event-processing-ds-066411.pdf
Enabling Fast Data and the Internet of Things.
Calculate a moving average in WinCC OA? Quite simple with CQL (Continous Query Language, this is not SQL):
select el, avg(val) as val from channel1 [RANGE 1 MINUTE SLIDE 5 SECONDS] group by el
Or use the power of Microsoft Machine Learing: http://azure.microsoft.com/en-gb/services/machine-learning/
Machine learning – mining historical data with computer systems to predict future trends or behaviour – touches more and more lives every day. Search engines, online recommendations, ad targeting, virtual assistants, demand forecasting, fraud detection, spam filters – machine learning powers all these modern services. But these uses barely scratch the surface of what’s possible.
The sdb.ctl is a client library which can be used to access relational databases from the WinCC OA client without the need to install any database client software at the client. The connection to the relational database is handled by the WinCC OA server. The client communicates with the WinCC OA server only by data points. The SQL-Statements are transferred by data points to a WinCC OA control, this control executes the SQL-Statements and sends back the result to the client by data points.
It is possible to use the RDBSyncForward feature to replicate history data to a second Oracle database.
WinCC OA primarily writes the history data to the first database. Values are replicated from the first database to the second database by Oracle-Packages (part of WinCC OA). When the first database goes down then WinCC OA will continue to write history data to the second database. When the first database gets up and running again then WinCC OA will switch back to the first database and the history data which was written to the second database will be replicated from the second database to the first database.
The bi-directional replication is done asynchronously (periodically)! If one database crashes some history data may be lost.
Prepare two RDB configuration files
RDB_config_DB1.sql and RDB_config_DB2.sql.
Use RDB_config_template.sql: C:\Siemens\Automation\WinCC_OA\3.12\data\RDBSetup\ora\RDB_config_template.sql
Example configuration file with ASM is shown at the end.
The following lines are important for the data replication.
RDB_config_DB1.sql:
define whatpackage = 'fwd'
define connect_first = '&connect_identifier'
define connect_second = 'DB2'
define whatinstall = '3'
define syncjob_intval = 1
RDB_config_DB2.sql:
define whatpackage = 'fwd'
define connect_first = '&connect_identifier'
define connect_second = 'DB1'
define whatinstall = '3'
define syncjob_intval = 1
Prepare the TNSNAMES.ORA
It must contain both databases and must be deployed to the WinCC OA server(s) and to the database servers!
An TNSNAMES.ORA example you will find below.
Change to RDBSetup directory
C:\Siemens\Automation\WinCC_OA\3.12\data\RDBSetup\ora
Execute the RDB-Manager-Setup-Script for the first Oracle-Database.
> win_install.bat/unix_intall.sh _DB1
Execute the RDB-Manager-Setup-Scriptfor the second Oracle-Database.
> win_install.bat/unix_intall.sh _DB2
Change to sync subdirectory C:\Siemens\Automation\WinCC_OA\3.12\data\RDBSetup\ora\sync
Execute the sync setup for database 1
> sync_setup _DB1 ..\
Execute the sync setup for database 2
> sync_setup _DB2 ..\
Check arc_log table for (error)messages
select * from arc_log order by arc_log_id desc
Adapt WinCC OA config file
Important for the data replication is the following configuration:
[ValueArchiveRDB]
Db = “DB1,DB2”
RDB archive group configuration
In WinCC OA go to system management / database / RDB archive groups and set the check box “forwarding” for the archive groups you wanna sync.
WinCC OA config file
[general]
useRDBArchive = 1
useRDBGroups = 1
[ValueArchiveRDB]
DbUser = "RDBSYNC"
DbPass = "manager"
DbType = "ORACLE"
Db = "DB1,DB2"
writeWithBulk = 1
[ctrl]
queryRDBdirect = 1
CtrlDLL = "CtrlADO"
CtrlDLL = "CtrlRDBArchive"
CtrlDLL = "CtrlRDBCompr"
[ui]
queryRDBdirect = 1
CtrlDLL = "CtrlADO"
CtrlDLL = "CtrlRDBArchive"
CtrlDLL = "CtrlRDBCompr"
TNSNAMES.ORA
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux-DB-01)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = DB1))
)
DB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux-DB-02)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = DB2))
)
RDB_config_DB1.sql
define connect_identifier = 'DB1'
define sysdba_user = 'SYS'
define yesno_newuser = 'yes'
define schema_user = 'RDBSYNC'
define app_user = 'RDBSYNCAPP'
define use_rman = 'rman'
define os_sys = 'unix'
define zip_backup = 'no'
define sequence_start = 100000
define sequence_maxvalue = 199999
define path_dbfile = '+DATA/WINCCOA/RDBSYNC/'
define path_tempdbfile = '+DATA/WINCCOA/RDBSYNC/'
define path_oraclebin = '/u01/app/oracle/product/12.1.0/dbhome_1/bin/'
define instance_name = 'DB1'
define host_name = 'linux-db-01'
define path_backup = '/u01/app/oracle/fast_recovery_area/DB1/winccoa/'
define path_alert = '+DATA/WINCCOA/RDBSYNC/'
define path_event = '+DATA/WINCCOA/RDBSYNC/'
define mytimezone = 'Europe/Vienna'
define asm_instance = '+ASM'
define service_name = 'DB1'
define number_db_storage = 1
define whatpackage = 'fwd'
define connect_first = '&connect_identifier'
define connect_second = 'DB2'
define whatinstall = '3'
define syncjob_intval = 1
RDB_config_DB2.sql
define connect_identifier = 'DB2'
define sysdba_user = 'SYS'
define yesno_newuser = 'yes'
define schema_user = 'RDBSYNC'
define app_user = 'RDBSYNCAPP'
define use_rman = 'rman'
define os_sys = 'unix'
define zip_backup = 'no'
define sequence_start = 100000
define sequence_maxvalue = 199999
define path_dbfile = '+DATA/WINCCOA/RDBSYNC/'
define path_tempdbfile = '+DATA/WINCCOA/RDBSYNC/'
define path_oraclebin = '/u01/app/oracle/product/12.1.0/dbhome_1/bin/'
define instance_name = 'DB2'
define host_name = 'linux-db-02'
define path_backup = '/u01/app/oracle/fast_recovery_area/DB1/winccoa/'
define path_alert = '+DATA/WINCCOA/RDBSYNC/'
define path_event = '+DATA/WINCCOA/RDBSYNC/'
define mytimezone = 'Europe/Vienna'
define asm_instance = '+ASM'
define service_name = 'DB2'
define number_db_storage = 1
define whatpackage = 'fwd'
define connect_first = '&connect_identifier'
define connect_second = 'DB1'
define whatinstall = '3'
define syncjob_intval = 1
This example combines the WinCC OA audit trail datapoint elements to a single row for one audit trail entry.
select “TS”,”TIME”,”USERNAME”,”UINUM”,”BATCHID”,”ITEM”,”ITEMTYPE”,”ACTION”,”OLDVAL”,”NEWVAL”,”REASON”,”HOST” from (
select h.ts, e.ELEMENT_NAME, NVL(to_char(h.VALUE_NUMBER),h.VALUE_STRING) val
from eventhistory h
join elements e on e.ELEMENT_ID = h.ELEMENT_ID
where e.ELEMENT_NAME in (
‘System1:_AuditTrail.time’,
‘System1:_AuditTrail.username’,
‘System1:_AuditTrail.uinum’,
‘System1:_AuditTrail.batchid’,
‘System1:_AuditTrail.item’,
‘System1:_AuditTrail.itemtype’,
‘System1:_AuditTrail.action’,
‘System1:_AuditTrail.oldval’,
‘System1:_AuditTrail.newval’,
‘System1:_AuditTrail.reason’,
‘System1:_AuditTrail.host’
)
) a
pivot (listagg(val) within group (order by element_name)
for element_name in (
‘System1:_AuditTrail.time’ as time,
‘System1:_AuditTrail.username’ as username,
‘System1:_AuditTrail.uinum’ as uinum,
‘System1:_AuditTrail.batchid’ as batchid,
‘System1:_AuditTrail.item’ as item,
‘System1:_AuditTrail.itemtype’ as itemtype,
‘System1:_AuditTrail.action’ as action,
‘System1:_AuditTrail.oldval’ as oldval,
‘System1:_AuditTrail.newval’ as newval,
‘System1:_AuditTrail.reason’ as reason,
‘System1:_AuditTrail.host’ as host
));
A very simple Windows-Phone-App to receive push notifications from WinCC OA (Alerts, Events, …) based on Windows-Azure-Mobile.
