首页 » ORACLE 9i-23ai » Troubleshooting “enq: XR – database force logging” Wait Event

Troubleshooting “enq: XR – database force logging” Wait Event

  • 2023/10/30
  • ORACLE 9i-23ai
  • 31 views
  • Troubleshooting “enq: XR – database force logging” Wait Event已关闭评论

当您尝试在其中一个数据库会话正在执行 NOLOGGING 操作时，尝试将数据库置于 FORCE LOGGING 模式时，将观察到“enq： XR – database force logging”等待事件。这很容易证明。 连接到数据库（例如会话 1）并执行 NOLOGGING 操作：通过从不同的会话（例如会话 2）执行以下 SQL，将数据库置于 FORCE LOGGING 模式：您将观察到 Session-2 不会立即完成，而是等待enq: XR – database force logging。

Top Sessions

  • ‘# Samples Active’ shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity.
  • ‘XIDs’ shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event
Sid, Serial# % Activity Event % Event User Program # Samples Active XIDs
3081,15211 15.91 enq: XR – database force logging 15.91 SYS oracle@dbn04 (CKPT) 7/358 [ 2%] 0
5545,22251 13.64 gc cr block 2-way 13.64 SYS oracle@dbn04 (W02G) 6/358 [ 2%] 0
1177,44925 9.09 CPU + Wait for CPU 9.09 SYS oracle@dbn04 (DIA0) 4/358 [ 1%] 0
1683,19727 9.09 PX Deq: Join ACK 4.55 OPERUSER_MY JDBC Thin Client 2/358 [ 1%] 0
db file sequential read 4.55 2/358 [ 1%] 0
7281,18771 9.09 PX Deq: Join ACK 9.09 OPERUSER_MY JDBC Thin Client 4/358 [ 1%] 0 
SQL> @sed "enq: XR "
Show wait event descriptions matching %enq: XR %..

EVENT# EVENT_NAME                                              WAIT_CLASS           PARAMETER1                PARAMETER2                PARAMETER3                ENQUEUE_NAME                   REQ_REASON                       REQ_DESCRIPTION
------ ------------------------------------------------------- -------------------- ------------------------- ------------------------- ------------------------- ------------------------------ -------------------------------- ----------------------------------------------------------------------------------------------------
  1142 enq: XR - database force logging                        Other                name|mode                 operation                 0                         Quiesce / Force Logging        database force logging           Lock held during database force logging mode
  1141 enq: XR - quiesce database                              Other                name|mode                 operation                 0                         Quiesce / Force Logging        quiesce database                 Lock held during database quiesce

还原问题

# session 1
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; --- not commit; # session 2 SQL> conn /as sysdba
Connected.
SQL> alter database force logging;
--- waiting


# session 3
SQL> @ase
SID_SER_USER            EVENT                              STATUS   STATE
----------------------- ---------------------------------- -------- -------------------
159 -     3 - SYS       enq: XR - database force logging   ACTIVE   WAITING

解决方法
结束Nologging 事务

— over

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：