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