All posts by vogler

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

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;

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