Oracle Database 11g R2: Interactive Quick Reference

Oracle has released "Interactive Quick Reference" for DBA's and Developer's to better understand Oracle Database architecture. It includes

1) List of DBA Views,

2) Database Architecture Diagram, and

3) List of all Database Background Processes by category.

Click here to read more about it and click here to download it.

"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;

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.