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

Oracle, WinCC OA

How to copy WinCC OA RDB-Manager schema and data to another database…

// create all tablespaces
create tablespace PVSSRDB_ALERT_00100000 datafile '+PVSS';
create tablespace PVSSRDB_ALERT_00100000I datafile '+PVSS';
create tablespace PVSSRDB_EVENT10_00100003 datafile '+PVSS';
create tablespace PVSSRDB_EVENT10_00100003I datafile '+PVSS';
create tablespace PVSSRDB_EVENT_00100001 datafile '+PVSS';
create tablespace PVSSRDB_EVENT_00100001I datafile '+PVSS';
create tablespace PVSSRDB_EXTERN_00100011 datafile '+PVSS';
create tablespace PVSSRDB_VALUES_00100002 datafile '+PVSS';
create tablespace PVSSRDB_VALUES_00100002I datafile '+PVSS';
create tablespace PVSSRDB_VALUES_00100006 datafile '+PVSS';
create tablespace TS_PVSSRDB datafile '+PVSS';

// create roles
create role R_APP_PVSSRDB;

// create database link to old database
create database link solaris connect to system identified by manager using 'solaris/db';

// create directory (in 12c with pluggable database the default data_pump_dir directory does not work)
create directory data_pump as '/app/oracle/admin/CDB/dpdump';

// import data with database link
impdp system/manager@database/pvss schemas=(PVSSRDB,PVSSAPP) network_link=solaris directory=data_pump parallel=2

// grant execute on sys.dbms_lock to pvssrdb
grant execute on dbms_lock to pvssrdb;

APEX

Export Oracle APEX Applications of Workspace

How to export all applications of an Oracle APEX Workspace:

export CLASSPATH=.:/app/oracle/product/11.2.0/dbhome_1/jdbc/lib/ojdbc5.jar:/app/apex/utilities

java oracle.apex.APEXExport -db localhost:1521:ORCL -user system -password -workspaceid 2097002597188478

/app/apex ==> Oracle APEX Installation-Directory

Oracle

Oracle 12c Global Temporary Tables & Undo & Redo…

Oracle-Magazin: http://www.oracle.com/technetwork/issue-archive/2013/13-nov/o63asktom-2034271.html

Starting in Oracle Database 12c, temporary undo can be stored in the temporary tablespace and undo for permanent objects can be stored in the undo tablespace. What this effectively means is that operations on temporary tables will no longer generate redo.

How undo is generated in Oracle Database 12c for global temporary tables is controlled by a new init.ora parameter: temp_undo_enabled. It has two settings: TRUE and FALSE. By default, this parameter is set to FALSE and undo will be generated in the same fashion it was in the past. For example:

SQL> alter session
set temp_undo_enabled = false;

Session altered.

SQL> insert into gtt
2 select *
3 from all_objects;
87310 rows created.

Statistics
———————————————————————————
…
566304 redo size
…

SQ> update gtt
2 set object_name =
lower(object_name);
87310 rows updated.

Statistics
————————————————————————————
…
8243680 redo size
…

As you can see, the INSERT generates about half a megabyte of redo (566,304 bytes) while the UPDATE generates upwards of 8 MB of redo (8,243,680 bytes). If I enable temporary undo, however:

SQL> alter session
set temp_undo_enabled = true;

Session altered.

SQL> insert into gtt
2 select *
3 from all_objects;
87310 rows created.

Statistics
———————————————————————————————
…
280 redo size
…

SQL> update gtt
2 set object_name =
lower(object_name);
87310 rows updated.
Statistics
———————————————————————————————
…
0 redo size
…

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