If you haven’t heard Red Hat released Red Hat Enterprise Virtualization (RHEV). It has a lot of advantages over proprietary virtualization solutions.
November 11, 2009
October 29, 2009
Find waiter with Oradebug 11gR2
There is small addition to oradebug unit_test command in 11g R2 .
oradebug unit_test per_session_find_one_waiter
This command has four mandatory arguments and one optional.
First parameter is find_waiters_for which can have two different values : current_sess or all_local_sess .Self descriptive.
Second parameter is wait_event.This is name of the wait event which search is done.Third and fourth parameters are related to time and rusults.
For my simple test I will test this command on event enq TX -row lock contention for my own session.
Session 1:
create table test(a int primary key);
insert into test select rownum from dba_tables where rownum <9;
commit;
update test set a=9 where a=1;
update test set a=10 where a=2;
Session 2:
update test set a=11 where a=3;
update test set a=12 where a=4;
SQL> /
PID SPID SID USERNAME SERIAL# SQL_HASH_VALUE
———- ———————— ———- —————————— ———- ————–
22 1030 190 SYS 1115 3889092034
Session 1 again:
SQL> /
PID SPID SID USERNAME SERIAL# SQL_HASH_VALUE
———- ———————— ———- —————————— ———- ————–
21 31360 156 SYS 59123 3889092034
update test set a=25 where a=8;
update test set a=13 where a=3;
Now I got event to test oradebug command:
oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=300 min_results=1
SQL> oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=1 min_results=1
Here is output:
WAITERS_FOUND_BEGIN
(inst=1, sid=190, osid=1030) is blocking (inst=1, sid=156) for at least 74 secs
WAITERS_FOUND_END
if I run same command again
SQL> oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=1 min_results=1
WAITERS_FOUND_BEGIN
(inst=1, sid=190, osid=1030) is blocking (inst=1, sid=156) for at least 221 secs
WAITERS_FOUND_END
wait time will increase.
Let’s increase waiter_min_secs_blkd=300 and run command:
SQL> oradebug unit_test per_session_find_one_waiter find_waiters_for=current_sess wait_event=”enq: TX – row lock contention” waiter_min_secs_blkd=300 min_results=1
WAITERS_FOUND_BEGIN
(inst=1, sid=190, osid=1030) is blocking (inst=1, sid=156) for at least 301 secs
WAITERS_FOUND_END
and I got output after aprox 301 secs..
October 27, 2009
ORION (Oracle I/O Calibration Tool) included in 11g R2
Apparently Oracle included this tool into 11g release 2 database.Orion binary is located under $ORACLE_HOME/bin .
Quick run with help command will give very detail explanation how to use this tool.Also
Kevin Closson’s Oracle blog got post about Orion:
http://kevinclosson.wordpress.com/2006/12/11/a-tip-about-the-orion-io-generator-tool/
-bash-3.2$ orion -help
ORION: ORacle IO Numbers — Version 11.2.0.1.0
ORION runs IO performance tests that model Oracle RDBMS IO workloads.
It measures the performance of small (2-32K) IOs and large (128K+) IOs
at various load levels.
(more…)
October 20, 2009
Uncommitted transactions
Let say someone have an update,insert or delete statement that runs but has not been commited yet.So question is how to show uncommitted transactions ?
First some background about transaction management in Oracle.
A transaction is a logical unit of work that contains one or more SQL statements.
A transaction ends when any of the following occurs:
A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements,
Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
A user disconnects from Oracle. The current transaction is committed.
A user process terminates abnormally. The current transaction is rolled back.
When a transaction is committed, the following occurs:
1 The internal transaction table for the associated undo tablespace that the transaction has committed,
and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.
This can be seen using v$transaction view.To get more info I normally join v$transaction and v$session views.
e.g.
select t.start_time,s.sid,s.serial#,s.username,s.status
from v$transaction t, v$session s
where s.saddr = t.ses_addr
2. The log writer process (LGWR) writes redo log entries in the SGA’s redo log buffers to the redo log file.
It also writes the transaction’s SCN to the redo log file. (more…)
October 15, 2009
Blocking locks history
In my previous blogs I have already wrote script for monitoring blocking locks in real time.
http://oraclue.com/2009/05/18/procedure-to-kill-blocking-session-in-rac-11g/
But what if I am accessing database next day or week later and trying to find out who caused blocking lock? I know there are trace files etc but this time I will use v$views.
Let me start with familiar scenario to produce blocking lock ( I have use same code for deadlock scenario in previous blogs):
Session 1:
create table test(a int primary key);
insert into test select rownum from dba_tables where rownum <9;
commit;
update test set a=9 where a=1;
update test set a=10 where a=2;
Table created.
8 rows created.
Commit complete.
1 row updated.
1 row updated.
Session 2:
update test set a=11 where a=3;
update test set a=12 where a=4;
1 row updated.
1 row updated.
again session 1:
update test set a=25 where a=8;
update test set a=13 where a=3;
| So to detect blocking lock that occurred in the past I use active session history or to be precise column blocking_session from view V$ACTIVE_SESSION_HISTORY. Note that blocking_session column is populated only when the session was waiting for enqueues or a “buffer busy” wait. |
For mutex related waits, v$session.blocking_session is not populated in 10.2.
Use Metalink Note 786507.1
v$session.blocking_session is populated in 11g .
My simple script looks like: (more…)
October 13, 2009
Deffered Segment Creation
Oracle 11g release 2 introduced new initialization parameter DEFERRED_SEGMENT_CREATION .
This parameter specifies the semantics of deferred segment creation. If set to true ( which is DEFAULT ) then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.
According to Oracle documentation when you create a table with deferred segment creation (the default), the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.
There is a new SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS that can be used to verify deferred segment creation.
However I do not trust any new feature until I test it. I will use simple example to test it.
I am running Oracle 11g version 2
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
and connected as SYS user
SQL> show user
USER is “SYS”
Check that tablespace is LOCALLY managed:
SQL> select TABLESPACE_NAME ,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN SEGMEN
—————————— ———- ——
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
USERS LOCAL AUTO
and I will create table test with only one column and stored in tablespace users:
SQL> create table test ( a number) tablespace users;
Table created.
Now according to Oracle documentation this table should be listed in _TABLES views but not in _SEGMENTS views!
SQL> SELECT segment_created
FROM user_tables
WHERE table_name = ‘TEST’;
SEG
—
YES
but segment_created= YES? (more…)
August 28, 2009
Bugman ( RMAN ) validate restore “bug”
If you are using RMAN (BUGMAN) like I do then you probably used VALIDATE command.Validate command is used to verify integrity of backups
without performing backups etc.
There are some “issues” with validate command.Actually Oracle intentionally did it but nevertheless it can be potential problem if you do not pay attention to it.
Let me start with two validate command:
restore database validate and restore database validate check logical
First command validate physical backups while second command check logical corruption only.
If you are validating your backups using these commands then you are missing files needed for database recovery or archivelogs.
Restore database validate command only validates that all DATAFILES that can be restored.ARCHIVELOGS ARE NOT INCLUDED.
Archivelog files are used for recovery and this command does not validate them. (more…)
August 20, 2009
Tracking DDL changes in 11g
As you know Oracle provides DDL triggers to track changes to the database.Data from these triggers are used for auditing, change controls etc.
But Oracle 11g also provides specific event for tracking DDL changes.It uses target called SQL_DDL.
These events can be enabled on session or system level.This is shortest command:
SQL> alter session set events ‘trace[SQL_DDL]‘;
and to turn off:
SQL> alter session set events ‘trace[SQL_DDL] off’;
System level:
SQL> alter system set events ‘trace[SQL_DDL]‘;
System altered.
SQL> alter system set events ‘trace[SQL_DDL] off’;
This command can be extended to trace disk , memory or use different resolution etc.List is below:
[disk=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]
[memory=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]
[get_time=DISABLE | DEFAULT | SEQ | HIGHRES | SEQ_HIGHRES ]
[get_stack=DISABLE | DEFAULT | ENABLED ]
For my test I have created simple logon trigger whic will trace only my userid after logon on database:
CREATE OR REPLACE TRIGGER SYS.trace_ddl
after logon on database
begin
if user like ‘MILADIN’ then
execute immediate ‘alter session set timed_statistics=true’;
execute immediate ‘alter session set max_dump_file_size=unlimited’;
execute immediate ‘alter session set tracefile_identifier=”MILADIN”’;
execute immediate ‘alter session set events ”trace [SQL_DDL]”’;
end if;
end;
/
Here are few examples.
First trace is coming from create statement.These traces can be used not only to track DDL changes but they are also very
educational especially if you interested into Oracle internals. (more…)
August 13, 2009
Issues with upgrade to 11g
I was doing manual upgrade from version 10.2.0.3 to 11.1.0.7 .Really did not expect any big issues since I did it many times.So after doing all pre-upgrade steps ( or I taught I did all of them)
I started database in upgrade mode and fired catupgrd.sql :
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 6413680640 bytes
Fixed Size 2160112 bytes
Variable Size 1946159632 bytes
Database Buffers 4429185024 bytes
Redo Buffers 36175872 bytes
Database mounted.
Database opened.
SQL> @catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint (<constraint_name>) violated
DOC>#
FROM registry$database
*
ERROR at line 2:
ORA-00942: table or view does not exist
Upgrade failed! Since I hit same error in the past I knew where is the problem and expected to fix it quickly.
This error is related to timezone file which must be version 4 for Oracle version 11g.If timezone is not version 4 than patch needs to be applied.
Query to check timezone file is:
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 4
so I pointed database to old oracle home and started it:
-sh-3.2$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Tue Aug 11 11:12:41 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 6442450944 bytes
Fixed Size 2082376 bytes
Variable Size 1577060792 bytes
Database Buffers 4848615424 bytes
Redo Buffers 14692352 bytes
ORA-00201: control file version 11.0.0.0.0 incompatible with ORACLE version
10.2.0.3.0
ORA-00202: control file:
‘/oracle/demo/control/copy_1/control01.ctl’ (more…)
July 28, 2009
Identifying PID/SPID for killed session in 11g
Many times I killed session with alter system kill session command ( or Toad )
and then session would just hang without dying.Then when I tried to find which OS process is related to killed session I could not identify it directly by using v$session and v$process view and joining them using addr column.According to Oracle this is expected.Here is explanation:
When a session is killed, the session state object(and all the child state objects under the session state object) move out from under the original parent process state object, and are placed under the pseudo process state object (which is expected, given the parent/child process mechanism on Unix). PMON will clean up all the state objects found under the pseudo process state object. That explains why PADDR changes in V$SESSION when a session is killed. New PADDR you are seeing in v$SESSION is the address of the pseudo process state object. This shows up in system state under PSEUDO PROCESS for group DEFAULT: V$PROCESS still maintains the record of the original parent process.
So to finally get rid of that session I had to use different workarounds.One of the workaround was to eliminate PSEUDO processes.
select spid, program
from v$process where program != ‘PSEUDO’
and addr not in
(select paddr from v$session); and of course avoid killing background processes
select spid, program from v$process
where program!= ‘PSEUDO’
and addr not in (select paddr from v$session)
and addr not in (select paddr from v$bgprocess);
Thanks to 11g there are two new columns in v$session view :
CREATOR_ADDR - state object address of creating process
CREATOR_SERIAL# - serial number of creating process
CREATOR_ADDR is the column that can be joined with the PADDR column in V$PROCESS to uniquely identify
the killed process corresponding to the former session.
So new query would look like:
select * from v$process where addr=(select creator_addr from v$session where sid=< sid used in alter system kill session command >);
In addition to these columns there are views
V$DETACHED_SESSION
V$PROCESS_GROUP
which can help in resolving this issue.