Category Archives: Oracle

Oracle

Oracle Inmemory Database (and materialized view)…

# turn on the inmemory option (it’s part of the SGA, so get sure that the SGA is big enought)

alter system set inmemory_size=1G scope=spfile;

One gigabyte seems to be less, but inmemory tables are stored column oriented. so, Oracle uses also compression .. for example i loaded a table with 1.2 Mio Rows (but only 5 columns) and i just needed ~30MB (in)memory.

# create a materialized view which is refreshd every 5 minutes

create materialized view sma_ext_5min_snp
refresh complete start with (sysdate) next (sysdate+5/24/60) with rowid
as select * from sma_ext_5min;

# put the materialized view table into memory
alter table sma_ext_5min_snp inmemory;

# check the inmemory state of the materialized view (table)

SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate FROM user_tables WHERE table_name = ‘SMA_EXT_5MIN_SNP’

# access the view so that it will be loaded into memory (default is load on use)

select count(*) from sma_ext_5min_snp;

# check if it is loaded into memory:

select * from v$im_segments

Oracle, WinCC OA

How to replicate values to data centers, big-data, SOA, … with WinCC OA…

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.

Rest-Forward

Oracle

Oracle Listener disable connect logging…

Disable logging of every connect request:

listener.ora:
LOGGING_ = OFF

otherwise the listener will log every connect and fill up the listener.log

[oracle@Database trace]$ ls -la
total 30696
drwxr-xr-x 2 oracle oinstall 4096 Aug 24 20:19 .
drwxr-xr-x 14 oracle oinstall 4096 May 4 09:18 ..
-rw-r—– 1 oracle oinstall 31393482 Aug 25 17:28 listener.log

Oracle

Flashback Database ….

It is pretty cool to create a restore point for the database before doing some upgrades. If upgrade fails the database can be set back to the restore point by a single statement.

CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

FLASHBACK DATABASE TO RESTORE POINT before_upgrade;

DROP RESTORE POINT before_upgrade;

Oracle, WinCC OA

WinCC OA pivot query to combine history values with same timestamp in one row…

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
));

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.