4 Replies Latest reply: Dec 2, 2012 8:10 PM by ~御帝~ RSS

    ORA-00054: resource busy and acquire with NOWAIT specified

    ~御帝~
      --osinfo
      AIX bomc3_svr1 3 5 00C58E964C00

      --dbinfo
      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
      PL/SQL Release 10.2.0.5.0 - Production
      CORE 10.2.0.5.0 Production
      TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
      NLSRTL Version 10.2.0.5.0 - Production


      --alertlog
      ...
      Current log# 2 seq# 7780 mem# 0: /oradata2/redo30.log
      Current log# 2 seq# 7780 mem# 1: /oradata3/redo34.log
      Thu Nov 29 22:03:01 BEIST 2012
      GATHER_STATS_JOB encountered errors. Check the trace file.
      Thu Nov 29 22:03:01 BEIST 2012
      Errors in file /oracle/admin/bomc/bdump/bomc1_j001_643960.trc:
      ORA-00054: resource busy and acquire with NOWAIT specified
      Thu Nov 29 22:34:51 BEIST 2012
      Thread 1 advanced to log sequence 7781 (LGWR switch)
      Current log# 8 seq# 7781 mem# 0: /oradata3/redo24.log
      Current log# 8 seq# 7781 mem# 1: /oradata1/redo40.log
      Fri Nov 30 00:46:03 BEIST 2012
      Thread 1 advanced to log sequence 7782 (LGWR switch)
      Current log# 5 seq# 7782 mem# 0: /oradata2/redo21.log
      Current log# 5 seq# 7782 mem# 1: /oradata3/redo37.log
      Fri Nov 30 03:21:47 BEIST 2012
      Thread 1 advanced to log sequence 7783 (LGWR switch)
      Current log# 6 seq# 7783 mem# 0: /oradata2/redo22.log
      Current log# 6 seq# 7783 mem# 1: /oradata3/redo38.log
      Fri Nov 30 05:34:12 BEIST 2012
      Thread 1 advanced to log sequence 7784 (LGWR switch)
      Current log# 7 seq# 7784 mem# 0: /oradata2/redo23.log
      Current log# 7 seq# 7784 mem# 1: /oradata3/redo39.log
      Fri Nov 30 07:34:52 BEIST 2012
      Thread 1 advanced to log sequence 7785 (LGWR switch)
      Current log# 1 seq# 7785 mem# 0: /oradata2/redo29.log
      Current log# 1 seq# 7785 mem# 1: /oradata3/redo33.log
      ...

      帖子经 ~御帝~编辑过
        • 1. Re: ORA-00054: resource busy and acquire with NOWAIT specified
          LiuMaclean(刘相兵)
          请把TRACE发给我 /oracle/admin/bomc/bdump/bomc1_j001_643960.trc 邮箱

          有问题请去OTN中文论坛开个帖子 我会回复 地址:http://www.otncn.org
          如果需要发送附件,可以直接发邮件到 liu.maclean@gmail.com
          • 2. Re: ORA-00054: resource busy and acquire with NOWAIT specified
            ~御帝~
            刘大,trace文件已发到您邮箱
            • 3. Re: ORA-00054: resource busy and acquire with NOWAIT specified
              LiuMaclean(刘相兵)
              *** 2012-11-29 22:03:01.015
              ORA-00054: resource busy and acquire with NOWAIT specified
              *** 2012-11-29 22:03:01.015
              GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"WRI$_SEGADV_OBJLIST"','""', ...)
              ORA-00054: resource busy and acquire with NOWAIT specified
              10.2.0.5.0 - 64bit Production + AIX


              请参考文档      Automatic Statistics Job Fails With ORA-00054: Resource Busy And Acquire With Nowait Specified Or Timeout Expired [ID 1312248.1]     To Bottom     

              When running automatic statistics gathering, an: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired error is received.
              
              The maintenance window opens, 
              the resource plan is automatically changed to DEFAULT_MAINTENANCE_PLAN,
              the automatic statistics collection job executes and reports error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.
              
              Thu Apr 07 22:00:00 2011
              Setting Resource Manager plan SCHEDULER[0x2FF7]:DEFAULT_MAINTENANCE_PLAN via scheduler window
              Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
              Thu Apr 07 22:00:00 2011
              Starting background process VKRM
              Thu Apr 07 22:00:00 2011
              VKRM started with pid=56, OS id=835 
              Thu Apr 07 22:01:24 2011
              GATHER_STATS_JOB encountered errors. Check the trace file.
              Errors in file /export/home/oracle/admin/ZDBPFT/diag/rdbms/ZDBPFT/ZDBPFT/trace/ZDBPFT_j001_1347.trc:
              ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
              
              The trace file lists the owner and object name for the object that could not be locked and shows the operation being done (GATHER_STATS_JOB: GATHER_TABLE_STATS).
              
              Trace file /export/home/oracle/admin/ZDBPFT/diag/rdbms/ZDBPFT/ZDBPFT/trace/ZDBPFT_j001_1347.trc
              Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
              ORACLE_HOME = /export/home/app/oracle/product/11.2.0/dbhome_1
              System name: SunOS
              Node name: regdata14
              Release: 5.10
              Version: Generic_144488-04
              Machine: sun4u
              Instance name: ZDBPFT
              Redo thread mounted by this instance: 1
              Oracle process number: 59
              Unix process pid: 1347, image: oracle@regdata14 (J001)
              
              *** 2011-04-07 22:01:24.334
              *** SESSION ID:(516.5639) 2011-04-07 22:01:24.334
              *** CLIENT ID:() 2011-04-07 22:01:24.334
              *** SERVICE NAME:(SYS$USERS) 2011-04-07 22:01:24.334
              *** MODULE NAME:(DBMS_SCHEDULER) 2011-04-07 22:01:24.334
              *** ACTION NAME:(ORA$AT_OS_OPT_SY_1457) 2011-04-07 22:01:24.334
              
              ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
              
              *** 2011-04-07 22:01:24.335
              GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"WRI$_SEGADV_OBJLIST"','""', ...)
              ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
              Changes
              
              The NO_INVALIDATE statistics preference has been set to a non-default value of FALSE.
              
              SQL> select dbms_stats.get_param('NO_INVALIDATE') from dual;
              
              DBMS_STATS.GET_PARAM('NO_INVALIDATE') 
              --------------------------------------------------------------------------------
              FALSE
              Cause
              
              The ORA-0054 error in expected behavior in this case because when parameter NO_INVALIDATE = FALSE the DBMS_STATS procedure makes a NO_WAIT lock request on the object.  If the object is in use and has high concurrency the lock request times out and error ORA-00054 is signaled.
              
              Solution
              
              Use the default setting of NO_INVALIDATE = DBMS_STATS.AUTO_INVALIDATE as this does not require locking the object. 
              
              SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS (pname=> 'NO_INVALIDATE', pvalue=> 'DBMS_STATS.AUTO_INVALIDATE');
              
              PL/SQL procedure successfully completed.
              
              SQL> select dbms_stats.get_param('NO_INVALIDATE') from dual;
              
              DBMS_STATS.GET_PARAM('NO_INVALIDATE')
              --------------------------------------------------------------------------------
              DBMS_STATS.AUTO_INVALIDATE
              
              If you must use NO_INVALIDATE = FALSE then take actions to collect statistics on the object listed in the trace file with the appropriate DBMS_STATS procedure at a time when the object has less concurrent use.
              考虑设置 EXEC DBMS_STATS.SET_GLOBAL_PREFS (pname=> 'NO_INVALIDATE', pvalue=> 'DBMS_STATS.AUTO_INVALIDATE');


              Workaround:

              忽略该问题

              参考


              ORA-00054 资源正忙 “resource busy and acquire with NOWAIT specified”错误
              http://www.askmaclean.com/archives/ora-00054-%E8%B5%84%E6%BA%90%E6%AD%A3%E5%BF%99-resource-busy-and-acquire-with-nowait-specified.html