Oracle 11g RAC環境下的ORA - 19606問題

標籤：definition   oracle   

在剛剛接手Oracle時，這個問題困擾了我一段時間，現在將問題的解決過程分享一下

Oracle版本：11gR2

OS環境：Centos6.4

問題重現：

1. 接手資料庫是寫了一個備份指令碼，指令碼內容如下：

-----------------------------------------------------------------------------------------------------------------------------------

# !/bin/bash
# Name: rmanbk_level0.sh
# Write by: Datura at 2014/11/11 v1.0
# Description: The script is used to make the zero level backup for the orcl Library

# The definition of the variable
lock_file=/tmp/rmanbk.lock
oracleid=`cat /etc/passwd|grep oracle|awk -F: ‘{print $3}‘`

# Check the script to run or not
if [ -f $lock_file ];then
        pid=`cat $lock_file`
        ps $pid &> /dev/null
        [ $? -eq 0 ] && echo "Script is running..." && exit 1
fi

# Create process lock
echo $$ > $lock_file

# Only allows the oracle to run
[ $UID -ne $oracleid ] && echo "Please run as oracle !!" && exit 4

# To set environment variables
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl1
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

echo on
rman target /  msglog=/storage/script/log/rmanbk_level0_`date +%Y‘-‘%m‘-‘%d‘-‘%H‘:‘%M‘:‘%S`.log <<EOF
RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup as compressed backupset incremental level 0 database format ‘/storage/arch/rman/rmanbk_level0_%d_%I_%s_%p_%T.bkp‘;
crosscheck archivelog all;
backup archivelog all format ‘/storage/arch/rman/rmanbk_archivelog_%d_%I_%s_%p_%T.bkp‘;
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backupset;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
exit
EOF
echo off

find /storage/arch/rman/ -mtime -0.5 -type f -exec zip /storage/arch/rman/rmanbk_level0`date +%F`.zip {} \;
scp /storage/arch/rman/rmanbk_level0`date +%F`.zip backup.demon.com:/home/oracle/orabackup
rm -rf /storage/arch/rman/rmanbk_level0`date +%F`.zip
find /storage/script/log -mtime +7 -exec rm -rf {} \;

2. 有一天在備份日誌裡出現了下面的報錯資訊

--------------------------------------------------------------------------------------------------------------------------------------------------

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: =========================================================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   +DATA/orcl/snapc_orcl.f

3. 報錯資訊處理過程

----------------------------------------------------------------------------------------------------------------------------------------------------

RMAN> crosscheck backupset;

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 2 days
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     5      12-DEC-14          +DATA/orcl/snapc_orcl.f

RMAN> delete noprompt obsolete;

RMAN-00571: ====================================================
RMAN-00569: ===============ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03009: failure of delete command on c2 channel at 11/20/2014 09:03:14
ORA-19606: Cannot copy or restore to snapshot control file

RMAN> show snapshot controlfile name;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f‘; # default

RMAN> configure snapshot controlfile name to ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f_bak‘;

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f_bak‘;
new RMAN configuration parameters are successfully stored

RMAN> crosscheck controlfilecopy  ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f‘;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 instance=orcl1 device type=DISK
validation failed for control file copy
control file copy file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f RECID=2 STAMP=863884566
Crosschecked 1 objects

RMAN> delete expired controlfilecopy ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f‘;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=80 instance=orcl1 device type=DISK
List of Control File Copies
=========================================================================================
Key     S Completion Time Ckp SCN    Ckp Time
------- - --------------- ---------- ---------------
2       X 17-NOV-14       67553950   17-NOV-14
        Name: /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f
        Tag: TAG20141117T155602

Do you really want to delete the above objects (enter YES or NO)? yes
deleted control file copy
control file copy file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f RECID=2 STAMP=863884566
Deleted 1 EXPIRED objects

RMAN> configure snapshot controlfile name to ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f‘;

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f_bak‘;
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f‘;
new RMAN configuration parameters are successfully stored

RMAN> configure snapshot controlfile name clear;

old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl1.f‘;
RMAN configuration parameters are successfully reset to default value

4. 通過上面的常規操作暫時解決了問題，但沒幾天就會再次出現相同的問題

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

RMAN-00571: ==========================================================
RMAN-00569: ================= ERROR MESSAGE STACK FOLLOWS ===================
RMAN-00571: ==========================================================
RMAN-03009: failure of Control File and SPFILE Autobackup command on c1 channel at 12/12/2014 01:05:19
ORA-00245: control file backup failed; target is likely on a local file system

5. 上面的常規操作只能暫時解決問題，不能解決根本問題，於是翻閱官方文檔得到以下資訊

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

從11gR2開始，在備份控制檔案時不再需要鎖住controlfile enqueue
對於非RAC環境的資料庫沒有任何的改變
但是對於RAC環境，因為控制檔案備份機制的改變
叢集中的所有節點都必須能夠訪問快照控制檔案，所以快照控制檔案要對所有執行個體可見
如果快照控制檔案沒有放到共用裝置上，當rman備份快照控制檔案時就會出現以上的錯誤

6. 根據得到的資訊做出如下調整

------------------------------------------------------------------------------------------------------------------------------------------

RMAN> show snapshot controlfile name;

RMAN> configure snapshot controlfile name to ‘/storage/snap_control/snapcf_%d_%I_%s_%p_%T.f‘;

RMAN> configure snapshot controlfile name to ‘+DATA/orcl/snapcf_orcl.f‘;   --也可以指定到對應的ASM磁碟組（磁碟組不支援萬用字元命名）

將控制檔案快照放到共用儲存之後就沒有再出現上面的錯誤了

在出現問題時我們都習慣用自己的經驗採用常規的手法去解決問題

但有時候翻閱官檔是個不錯的選擇

雖然官檔不能告訴你具體的操作步驟

卻可以給予解決根本問題的正確指引

-------------------------------------------------------以上為個人觀點闡述，如有不妥，歡迎指點-----------------------------------------------------------------------

本文出自 “營運管理” 部落格，請務必保留此出處http://datura.blog.51cto.com/8962530/1612949

Oracle 11g RAC環境下的ORA - 19606問題