Miladin Modrakovic's Blog: Oraclue

Alert file from standby database got following messages:

ORA-00752: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 8, block# 351475)
ORA-10564: tablespace TD_DATA
ORA-01110: data file 8: ‘+TD_DATA/test/datafile/test01.dbf’
ORA-10561: block type ‘TRANSACTION MANAGED DATA BLOCK’, data object# 105684

and second part

ORA-10877: error signaled in parallel recovery slave PR00
ORA-00752: recovery detected a lost write of a data block
MRP0: Background Media Recovery process shutdown (test1)

The alert file  shows that an ORA-00752 error is raised on the standby database
and the managed recovery is cancelled:

The recommended procedure to recover from such errors is a failover to the physical standby!!!

If you read Oracle Documentation

http://download.oracle.com/docs/cd/B28359_01/server.111/b28294.pdf

Chapter 13.6

During media recovery in a Data Guard configuration, a physical standby database
can be used to detect lost-write data corruption errors on the primary database. This is
done by comparing SCNs of blocks stored in the redo log on the primary database to
SCNs of blocks on the physical standby database. If the SCN of the block on the
primary database is lower than the SCN on the standby database, then there was a
lost-write error on the primary database. Read more of this post

Data Guard Broker (DGMGRL) can connect to database using following options:

-echo
-silent
-xml
-debug
-logfile

So if I need to run dgmgrl command in debug mode I would execute following :

dgmgrl -debug / or

-bash-3.2$ dgmgrl -debug sys/secret@test

DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
[W000 01/05 12:27:03.81] Connecting to database using test.
[W000 01/05 12:27:03.85] Executing query [select dbms_drs.dg_broker_info('VERSION') from dual].
[W000 01/05 12:27:03.86] Query result is '11.2.0.1.0'
Connected.

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. Read more of this post

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;

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: Read more of this post