Category Archives: Oracle

Oracle query session locks and objects….

p.spid process_id,
gv$locked_object a, dba_objects b, gv$session s, gv$process p
a.object_id = b.object_id AND
a.process = s.process 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
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 ;

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
SELECT 'DATABASE' scope, ndp.* FROM nls_database_parameters ndp
SELECT 'INSTANCE' scope, nip.* FROM nls_instance_parameters nip
) a
FOR scope

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;

Oracle 12c Global Temporary Tables & Undo & Redo…


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.


566304 redo size

SQ> update gtt
2 set object_name =
87310 rows updated.


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.


280 redo size

SQL> update gtt
2 set object_name =
87310 rows updated.

0 redo size

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
ASMSQL> ALTER DISKGROUP DATA DROP FILE '+DATA/db/datafile/pvssrdb_values_00100002i.264.817770521';

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

useRDBArchive = 1
useRDBGroups = 1

sendAlertsToRAIMA=1 # for HDB/RDB Parallel

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

queryRDBdirect = 1
CtrlDLL = "CtrlADO"
CtrlDLL = "CtrlRDBArchive"
CtrlDLL = "CtrlRDBCompr"

queryRDBdirect = 1
CtrlDLL = "CtrlADO"
CtrlDLL = "CtrlRDBArchive"
CtrlDLL = "CtrlRDBCompr"


Example TNSNAMES.ORA for a cluster with two networks:

DB =
   (ADDRESS = (PROTOCOL = TCP)(HOST = mainhdr1-vip)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = mainhdr1b-vip)(PORT = 1522))
   (ADDRESS = (PROTOCOL = TCP)(HOST = mainhdr2-vip)(PORT = 1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = mainhdr2b-vip)(PORT = 1522))
   (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

VMWare tools Oracle-Linux-UEK and ‘The path “” is not a valid path to the kernel’

when you get the following message while building the vmware tools:

Searching for a valid kernel header path…
The path “” is not a valid path to the 3.8.13-16.3.1.el6uek.x86_64 kernel
Would you like to change it? [yes]

you first have to install the kernel-devel and kernel-header package:

> yum install kernel-uek-devel kernel-uek-headers

but there is still a version-file missing… a link can help:

ln -s /usr/src/kernels/3.8.13-16.3.1.el6uek.x86_64/include/generated/uapi/linux/version.h /usr/src/kernels/3.8.13-16.3.1.el6uek.x86_64/include/linux/version.h

Register 11.2 database to 12.1 grid infrastructure…

some permission have changed between 11 and 12, before creating a 11 database in an 12 grid environment some permission have to be changed:

./crsctl modify type ora.database.type -attr “ATTRIBUTE=TYPE_ACL, DEFAULT_VALUE=’owner:root:rwx,pgrp:dba:rwx,other::r–,group:dba:r-x,user:oracle:rwx'”

./crsctl modify type ora.service.type  -attr “ATTRIBUTE=TYPE_ACL, DEFAULT_VALUE=’owner:root:rwx,pgrp:dba:rwx,other::r–,group:dba:r-x,user:oracle:rwx'”

otherwise you will get an permission error when the database will be added to the grid.

if you already got the error while creating the database with dbca then you can add the database manually to the grid, but you have to use srvctl from the 11 home directory:

/home/app/product/11.2.0/dbhome/bin/srvctl add database -d <DB> -o /home/app/product/11.2.0/dbhome