Oracle

Moving ASM Database Files from one Diskgroup to Another

select file_name from dba_data_files order by file_name;

RMAN> copy datafile '+DATA/db/datafile/pvssrdb_values_00100002i.264.817770521' to '+PVSS';
SQL> alter database datafile '+DATA/db/datafile/pvssrdb_values_00100002i.264.817770521' offline;
RMAN> switch datafile '+DATA/db/datafile/pvssrdb_values_00100002i.264.817770521' to copy;

SQL> select file_name from dba_data_files order by file_name;
RMAN> recover datafile '+PVSS/db/datafile/pvssrdb_values_00100002i.258.844976215';
SQL> alter database datafile '+PVSS/db/datafile/pvssrdb_values_00100002i.258.844976215' online;

ASMCMD> rm PVSSRDB_VALUES_00100002I.264.817770521
or
ASMSQL> ALTER DISKGROUP DATA DROP FILE '+DATA/db/datafile/pvssrdb_values_00100002i.264.817770521';

Oracle, WinCC OA

WinCC OA RDB-Manager Config & Database Settings…

WinCC OpenArchitecture using Oracle RDB-Manager…

*) Oracle db_files
*) Oracle logfile size
*) Oracle Password-Lifetime
*) Example WinCC OA config file
*) TCP/IP timeout settings on windows
*) WinCC OA Archive Group Sizing

Oracle db_files
===============
alter system set db_files=65536 scope=both;

Oracle logfile size
===================
For huge load (>1000 values/second) logfile size should be increased!

select * from v$logfile;

alter database add logfile size 512M;
alter database add logfile size 512M;
alter database add logfile size 512M;

remove other/small logfiles. If the logfiles are the current one then you have to switch the logfile and initiate a checkpoint before you are able to remove the old one.

alter system switch logfile;
alter system checkpoint;

alter database drop logfile group

Oracle Password-Life-Time
=========================
To prevent password expiration the default profile should be changed.
alter profile default limit password_life_time unlimited;

WinCC OA Config
===============

[general]
useRDBArchive = 1
useRDBGroups = 1

[data]
sendAlertsToRAIMA=1 # for HDB/RDB Parallel

[ValueArchiveRDB]
DbUser = "PVSSRDB"
DbPass = "xxx"
DbType = "ORACLE"
Db = "DB"
writeWithBulk = 1

[ctrl]
queryRDBdirect = 1
CtrlDLL = "CtrlADO"
CtrlDLL = "CtrlRDBArchive"
CtrlDLL = "CtrlRDBCompr"

[ui]
queryRDBdirect = 1
CtrlDLL = "CtrlADO"
CtrlDLL = "CtrlRDBArchive"
CtrlDLL = "CtrlRDBCompr"

 

Example TNSNAMES.ORA for a cluster with two networks:


DB =
(DESCRIPTION =
  (ENABLE=BROKEN)
  (ADDRESS_LIST=
   (ADDRESS = (PROTOCOL = TCP)(HOST = mainhdr1-vip)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = mainhdr1b-vip)(PORT = 1522))
  )
  (ADDRESS_LIST=
   (ADDRESS = (PROTOCOL = TCP)(HOST = mainhdr2-vip)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = mainhdr2b-vip)(PORT = 1522))
  )
  (CONNECT_DATA =
   (SERVICE_NAME = DB)
   (FAILOVER_MODE =
   (TYPE = SELECT)
   (METHOD = BASIC)
   (RETRIES = 20)
   (DELAY = 5)
  )
 )
)

Windows TCP/IP Timeout
======================
Also the tcp/ip timeout on windows should be decreased:

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.

WinCC OA Archive Group Sizing
=============================
Increase the max archive group size of existing (EVENT, ALERT) and new archives. The default size of 1GB is really damn small. Think about the max. db_files parameter (each archive file switch creates a new db file). Sizes of 20GB+ is no problem for Oracle. But keep in mind that there are file size limits (RDB Manager does not use Oracle Big-File-Tablespaces). For a block size of 8k the max file size is 32GB.

Block Size Maximum Datafile File Size
———– —————————
2k 4194303 * 2k = 8 GB
4k 4194303 * 4k = 16 GB
8k 4194303 * 8k = 32 GB
16k 4194303 * 16k = 64 GB
32k 4194303 * 32k = 128 GB

WinCC OA

WinCC OA Online Panel Language Translator (Google Translate API)…

Gallery

Automatic language translation of WinCC OA Panels with Google-Translate. Service for Message-Catalogs and Datapoint-List will be added (soon?)…

http://translate.rocworks.at

* Upload and import the panel-xml-files as a ZIP.
* Select source language and target language(s).
* Calculate the costs – Google-Translate-API is not for free, as well as this service isn’t – but one Coin/Panel is for free.
* Pay the fee  with the RocWallet (In-App-Purchase)
* Start the translation
* Create Result-ZIP file
* Download ZIP & start your project…

Upload project zip…

Screen Shot 2014-03-23 at 23.32.50 Import, select languages, translate…Screen Shot 2014-03-23 at 23.35.06

Order voucher and pay with mobile phone:Screen Shot 2014-03-23 at 23.35.26

Review translated language texts:

Screen Shot 2014-03-22 at 20.11.27

Result: Chinese Translation

Screen Shot 2014-03-22 at 11.07.45

Result: Russian Translation

Screen Shot 2014-03-22 at 20.04.09

APEX

Oracle Apex Blob as Download-Link…

First of all you have to select the blob column like this:

select project_id, mimetype, filename, dbms_lob.getlength(“OUTPUT”) AS output
from TR_PROJECT t
order by uploadts desc

If not you will get an error message.

AS Number/Date Format use  a string like this:

DOWNLOAD:TR_PROJECT:OUTPUT:PROJECT_ID::MIMETYPE:FILENAME:::Attachment:Download

 

WinCC OA

WinCC OA HowTo get Stock-Quotes from Yahoo (HttpRequest)…

Using Yahoo-Stock-Quote Webservice.

dyn_float getQuote(string symbol)
{
  // http://download.finance.yahoo.com/d/quotes.csv?s=%s&f=l1v"
  string request = "GET /d/quotes.csv?s=%s&f=l1v HTTP/1.1\r\n"
                   "Connection: keep-alive\r\n"
                   "Host: download.finance.yahoo.com\n\n";
  sprintf(request, request, symbol);
  DebugTN("getQuote", symbol);
  string response;
  int socket=tcpOpen("download.finance.yahoo.com", 80);
  tcpWrite(socket, request);
  tcpRead(socket, response, 3);
  tcpClose(socket);
  strreplace(response, "\r", "");
  dyn_string lines = strsplit(response, "\n");
  // print response lines (debug)
  for ( int i=1; i<=dynlen(lines); i++ )
    DebugTN(i, lines[i]);
  // extract the information we want
  if ( dynlen(lines) >= 13 ) {
    dyn_string cols=strsplit(lines[13], ",");
    float f1, f2;
    if ( dynlen(cols) > 0 ) sscanf(cols[1], "%f", f1);
    if ( dynlen(cols) > 1 ) sscanf(cols[2], "%f", f2);
    DebugTN(symbol, f1, f2);
    return makeDynFloat(f1, f2);
  }
}

WinCC OA

WinCC OA 3.12 Proxy Configuration when a Public and Local Network is used…

Local IP/Name: vm1.rocworks-vm1.f1.internal.cloudapp.net
Public IP/Name: rocworks-vm1.cloudapp.net
Client will connect through the Public IP.

Server

[general]
dataHost = "vm1.rocworks-vm1.f1.internal.cloudapp.net"
eventHost = "vm1.rocworks-vm1.f1.internal.cloudapp.net"
[proxy]
server = "vm1.rocworks-vm1.f1.internal.cloudapp.net:4897"
server = "vm1.rocworks-vm1.f1.internal.cloudapp.net:4998"

Client

[general]
dataHost = "vm1.rocworks-vm1.f1.internal.cloudapp.net"
eventHost = "vm1.rocworks-vm1.f1.internal.cloudapp.net"
mxProxy = "vm1.rocworks-vm1.f1.internal.cloudapp.net rocworks-vm1.cloudapp.net:5678 cert"

Note: It is important that the proxy port is set! (even if this port is the default port of the proxy).

Client-Project/config/
host-cert.pem
host-key.pem
root-cert.pem