難得的waiting for snapshot control file enqueue提示...

warehouse發表於2011-09-18
AIENQ

rman備份db時出現了：waiting for snapshot control file enqueue提示，很少看到這個提示，記錄一下。

[@more@]

RMAN> run{
2> backup database;
3> delete noprompt obsolete;
4> backup archivelog all;
5> }

Starting backup at 2011-09-30 15:31:00
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=G:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF
input datafile fno=00003 name=G:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF
input datafile fno=00004 name=G:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF
input datafile fno=00005 name=G:ORACLEPRODUCT10.2.0ORADATAORCLTEST.DBF
input datafile fno=00006 name=G:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS02.DBF
channel ORA_DISK_1: starting piece 1 at 2011-09-30 15:31:05
channel ORA_DISK_1: finished piece 1 at 2011-09-30 15:31:40
piece handle=G:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLBACKUPSET2011_09_30O1_MF_NNNDF_TAG20110930T153101_78BW1SH4_.BKP tag=TAG20110930T153101 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
waiting for snapshot control file enqueue
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2011-09-30 15:32:25
channel ORA_DISK_1: finished piece 1 at 2011-09-30 15:32:26
piece handle=G:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLBACKUPSET2011_09_30O1_MF_NCSNF_TAG20110930T153101_78BW49QG_.BKP tag=TAG20110930T153101 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 2011-09-30 15:32:26

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
using channel ORA_DISK_1
no obsolete backups found

Starting backup at 2011-09-30 15:32:30
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=71 recid=101 stamp=762189937
input archive log thread=1 sequence=72 recid=102 stamp=763227110
input archive log thread=1 sequence=73 recid=103 stamp=763227151
channel ORA_DISK_1: starting piece 1 at 2011-09-30 15:32:32
channel ORA_DISK_1: finished piece 1 at 2011-09-30 15:32:33
piece handle=G:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREAORCLBACKUPSET2011_09_30O1_MF_ANNNN_TAG20110930T153231_78BW4JPG_.BKP tag=TAG20110930T153231 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2011-09-30 15:32:33

RMAN>
--===================================

搜了一下doc的解釋是：

Backup Fails Because of Control File Enqueue: Scenario

In this scenario, a backup job fails because RMAN cannot make a snapshot control file. The message stack is as follows:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 08/30/2001 22:48:44
ORA-00230: operation disallowed: snapshot control file enqueue unavailable

Backup Fails Because of Control File Enqueue: Diagnosis

When RMAN needs to back up or resynchronize from the control file, it first creates a snapshot or consistent image of the control file. If one RMAN job is already backing up the control file while another needs to create a new snapshot control file, then you may see the following message:

waiting for snapshot control file enqueue

Under normal circumstances, a job that must wait for the control file enqueue waits for a brief interval and then successfully obtains the enqueue. RMAN makes up to five attempts to get the enqueue and then fails the job. The conflict is usually caused when two jobs are both backing up the control file, and the job that first starts backing up the control file waits for service from the media manager.

To determine which job is holding the conflicting enqueue:

After you see the first message stating "RMAN-08512: waiting for snapshot control file enqueue", start a new SQL*Plus session on the target database:

% sqlplus 'SYS/oracle@trgt AS SYSDBA'

Execute the following query to determine which job is causing the wait:

SELECT s.SID, USERNAME AS "User", PROGRAM, MODULE, 
       ACTION, LOGON_TIME "Logon", l.* 
FROM V$SESSION s, V$ENQUEUE_LOCK l
WHERE l.SID = s.SID
AND l.TYPE = 'CF'
AND l.ID1 = 0
AND l.ID2 = 2;

You should see output similar to the following (the output in this example has been truncated):

SID User Program              Module                    Action           Logon
--- ---- -------------------- ------------------- ---------------- ---------
9 SYS rman@h13 (TNS V1-V3) backup full datafile: c10000210 STARTED 21-JUN-01

Backup Fails Because of Control File Enqueue: Solution

Commonly, enqueue situations occur when a job is writing to a tape drive, but the tape drive is waiting for new tape to be inserted. If you start a new job in this situation, then you will probably receive the enqueue message because the first job cannot complete until the new tape is loaded.

After you have determined which job is creating the enqueue, you can do one of the following:

  • Wait until the job holding the enqueue completes

  • Cancel the current job and restart it after the job holding the enqueue completes

  • Cancel the job creating the enqueue

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1055168/，如需轉載，請註明出處，否則將追究法律責任。

相關文章