Wednesday, October 13, 2010

"enq: XR - database force logging" Wait Event

“enq: XR - database force logging” wait event is observed when you try to place the database in FORCE LOGGING mode while one of the database sessions is executing a NOLOGGING operation. This can be easily demonstrated.

Connect to database (say session 1) and perform a NOLOGGING operation:

SQL> conn test/test
Connected.
SQL>
SQL> 
SQL> create table t1 (id number, name varchar2(200)) NOLOGGING;
Table created.
SQL>
SQL> insert /*+ append */into t1 select level, rpad('*', 200, '*') from dual connect by level <= 5000000;

Place the database in the FORCE LOGGING mode by executing the following SQL from a different session (say session 2):

SQL> conn /as sysdba
Connected.
SQL>
SQL> 
SQL> alter database force logging;

You will observe that Session-2 does not complete immediately but rather waits. Let us see what session-2 is waiting on by connecting to the database (say session – 3):
SQL>
SQL> @wait

SID_SER_USER            EVENT                              STATUS   STATE
----------------------- ---------------------------------- -------- -------------------
159 -     3 - SYS       enq: XR - database force logging   ACTIVE   WAITING
146 -    82 - TEST      control file sequential read       ACTIVE   WAITED SHORT TIME

SQL> 

Session – 2 is actually waiting on “enq: XR - database force logging” wait event for Session – 1 to complete the NOLOGGING operation. As soon as Session – 1 completes the transaction, Session – 2 completes.

Here is the “wait.sql” used above to identify the wait events:

set line 10000
set pagesize 500

column Sid_Ser_User format a23
column event format a34

select
sid || ' - ' || lpad(serial#, 5, ' ') || ' - ' || username Sid_Ser_User,
event,
status,
state
from gv$session
where 1=1
and wait_class# <> '6'
and sid <> sys_context('USERENV', 'SID')
order by username;

Monday, October 11, 2010

AIOUG Webcast: RMAN Backup and Recovery

I will be talking on RMAN Recovery Procedures on 13 Oct, 2010 during my AIOUG Webcast. The webcast is scheduled at 05:00 PM IST.

One of the most important responsibilities of a DBA is to ensure that a database can be quickly and completely recovered from any type of failure. The aim of this session is to provide a platform to help DBAs to be able to restore databse under different failure scenarios.

In this session, I will discuss various database failure scenario's followed by a detailed recovery solution providing screenshots.

This presentation should be of a great value to the Novoice & Intermediate DBAs and a good refresher for an Advanced DBA.

See you at the webcast.

Friday, October 08, 2010

Oracle 11g Release 2 Patchset - 1 Available for Download

As you all know Oracle 11g Release 2 Patchset - 1 (11.2.02) is available for download for most of the platforms. The Patchset release has been blogged by many Oracle Enthusiasts and I am the last one to post it on my blog.

I should admit that I did not read body of any of the blog posts. I thought, the patch is out and everyone's blogging. Now, when I trackback and read Kevin Closson's Oracle Blog and Surachart Opun's Blog, I find an interested piece of information and here it is:

"In past releases, Oracle Database patch sets consisted of a set of files that replaced files in an existing Oracle home. Beginning with Oracle Database 11g Release 2, patch sets are full installations that replace existing installations."

If I had paid attention to this I wouldn't have gone through the pain of downloading Oracle 11gR2 base release and Oracle 11gR2 Patch-1.

Happy patching!!!