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