Allgemein

Downtime Query WinCC OA with RDB-Manager…

Example how to query the downtime of a device which has an alert configuration on the state of the machine (state 0/1):

select element_name, sum(downtime) downtime from (
select e.element_name,
h.ts,
h.VALUE_NUMBER,
h.direction,
case
when h.DIRECTION = 0 then
CAST((h.ts + 0) - ((lag(h.ts, 1, h.ts) OVER(ORDER BY ts)) + 0) AS FLOAT)*24*60*60
else
null
end as downtime
from elements e
join alerthistory h
on e.element_id = h.ELEMENT_ID
where e.element_name like
'Device_%.Status'
and ts between to_date('01.07.2014','DD.MM.YYYY') and to_date('31.07.2014','DD.MM.YYYY')
) group by element_name

Oracle

Oracle sum of intervals …

When timestamps are used in your tables and you subtract two timestamps then you will get as result a value of type “interval”. And it is not possible to sum up intervals. But it can be done by casting to float. The only bad thing is that you will loose the milli seconds…

CAST((ts1 + 0) – (ts2 + 0) AS FLOAT)*24*60*60 /* seconds */

Oracle

CRS-2566 User ‘oracle’ does not have sufficient permissions to operate on resource ‘ora.reco.acfsvol.acfs’…

While installing an Oracle Standard Edition on an Oracle Database Appliance (ODA) on the cluster filesystem (cloudfs) i got the following error:

CRS-2566 User ‘oracle’ does not have sufficient permissions to operate on resource ‘ora.reco.acfsvol.acfs’

I installed the Oracle Standard Edition with the user “oracle” and this user didn’ have the permission to add dependencies to the cluster file system resource.

As root-user add permission for the oracle user:

crsctl getperm resource ora.reco.acfsvol.acfs
crsctl setperm resource ora.reco.acfsvol.acfs -u user:oracle:r-x

Oracle, WinCC OA

Oracle TCP/IP Connection Timeout…

• Set the following registry entry (or create if it does not exist):
KeepAliveTime

Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
Value type: REG_DWORD – Time in milliseconds
Range: 1 – 0xFFFFFFFF
Default value: 7.200.000 (two hours)
For example this value can be set to 180000 (decimal), equals 3 minutes.

• Reboot the computer.

• Oracle SQL*Net settings the tnsnames.ora file:

Add the line “(ENABLE=BROKEN)” as shown below:

TESTDB =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TESTDB)
)
)

This is also useful for WinCC OA and RDB-Manager.

Allgemein

Oracle 12c unified audit trail fills up SYSAUX tablespace (schema AUDSYS)


-- clean audit trail for the current container
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => FALSE,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT );
END;

-- drop purge job
BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB(
AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_PJ');
END;
/

-- create purge job for current container
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
AUDIT_TRAIL_PURGE_INTERVAL => 12,
AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => FALSE,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/
/

Oracle

Oracle query session locks and objects….

SELECT
s.inst_id,
p.spid process_id,
a.oracle_username,
a.os_user_name,
a.locked_mode,
b.object_name,
b.object_type,
s.sid,
s.serial#,
s.program
from
gv$locked_object a, dba_objects b, gv$session s, gv$process p
where
a.object_id = b.object_id AND
a.process = s.process AND
a.SESSION_ID = s.SID AND
p.addr = s.paddr AND
p.inst_id = s.inst_id

In a cluster database use gv$ instead of v$ !


alter system [disconnect|kill] session 'sid,serial#,@inst_id' immediate;

kill locked sessions
SELECT 'alter system kill session '''||b.sid||','||b.SERIAL#||''';'
FROM v$lock a, v$session b, dba_objects o1, dba_objects o2
WHERE a.SID = b.SID
AND a.id1 = o1.object_id (+)
AND a.id2 = o2.object_id (+)
AND b.blocking_session IS NOT NULL
AND username IS NOT NULL

query blocking sessions:
=======================================================
select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2

=======================================================
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l1.id2 = l2.id2 ;

=======================================================
select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=543
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

WinCC OA

WinCC OA Proxy on a separate host …

config file entries:

Host 1 => WinCC OA Server Name: winccoa
# nothing is needed

Host 2 => Proxyhost where the proxy runs: scada

[general]
dataHost = "winccoa"
eventHost = "winccoa"

[proxy]
server = "winccoa:4897"
server = "winccoa:4998"

Host 3 => Client

[general]
data = "winccoa"
event = "winccoa"
mxProxy = "winccoa scada:5678 cert"

if you do not add the [proxy] configuration at the proxy host then you will get following error when you try to connect from the client:

WCCILproxy (1), 2014.05.27 21:09:21.486, SYS, INFO, 222, Accept SSL connection from host ::ffff:192.168.1.125:1485.
WCCILproxy (1), 2014.05.27 21:09:26.078, PARAM,WARNING, 209, Cannot find the host in the list of the allowed-hosts: (winccoa:4897)

Proxy
Source of picture: WinCC OA 3.12 Online Help

Oracle

Oracle Pivot Query (row to column)…

SQL> col PARAMETER format a30
SQL> col "SESSION" format a30
SQL> col DATABASE format a30
SQL> col INSTANCE format a30

SELECT * from
(SELECT 'SESSION' scope, nsp.* FROM nls_session_parameters nsp
union
SELECT 'DATABASE' scope, ndp.* FROM nls_database_parameters ndp
union
SELECT 'INSTANCE' scope, nip.* FROM nls_instance_parameters nip
) a
PIVOT (LISTAGG(VALUE) WITHIN GROUP (ORDER BY scope)
FOR scope
IN ('SESSION' as "SESSION",'INSTANCE' as INSTANCE,'DATABASE' as DATABASE))
/