Oracle/RMAN 備份時出現 ORA-00235 error
今天檢視客戶的備份狀態是發現了 ORA-00235 錯誤
錯誤訊息如下:
RMAN> run{
2> backup
3> incremental level 1 database
4> filesperset=10
5> format '/user/oracle/oradata2/backup/rman/level1_%t_%s_%p';
6> backup archivelog all delete all input
7> format '/user/oracle/oradata2/backup/rman/arch_%t_%s_%p';
8> }
9>
Starting backup at 16-NOV-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=26 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=362 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=163 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/16/2010 01:06:38
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
先查原廠的Solution
ORA-00235 controlfile fixed table inconsistent due to concurrent update
Cause: Concurrent update activity on a control file caused a query on a control file fixed table to read inconsistent information.
Action: Retry the operation.
什麼鳥答案丫, 檢查上週及上上週的備份也都是一樣, 根本就不是再做一次的問題
Starting backup at 09-NOV-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=790 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=761 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=612 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/09/2010 01:06:25
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
請教 Google 大神, 找不到特別有用的資料, 但發現這個錯誤並不只是在 RMAN 中會發生
再檢查其它天的備份倒很正常, 比較 level 0 的備份 script
$more /user/oracle/script/rman_level0.cmdfile
run{
backup
incremental level 0 database
filesperset=2format '/user/oracle/oradata2/backup/rman/level0_%t_%s_%p';
backup archivelog all delete all input
format '/user/oracle/oradata2/backup/rman/arch_%t_%s_%p';
delete noprompt obsolete ;
}
$more /user/oracle/script/rman_level1.cmdfile
run{
backup
incremental level 1 database
filesperset=10format '/user/oracle/oradata2/backup/rman/level1_%t_%s_%p';
backup archivelog all delete all input
format '/user/oracle/oradata2/backup/rman/arch_%t_%s_%p';
}
看到差異點了, 當初太貪心了, 想說 level 異動的 block 不多, 所以同時多備份幾個, 可能就是這個出問題, 改為 filesperset = 2 試看看
RMAN> run{
2> backup
3> incremental level 1 database
4> filesperset=2
5> format '/user/oracle/oradata2/backup/rman/level1_%t_%s_%p';
6> backup archivelog all delete all input
7> format '/user/oracle/oradata2/backup/rman/arch_%t_%s_%p';
8> }
9>
Starting backup at 2010-11-16 10:44:43
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=125 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=345 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=861 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00018 name=/user/oracle/oradata/webdb/TAINDX.dbf
input datafile fno=00037 name=/user/oracle/oradata/webdb/FR.dbf
channel ORA_DISK_1: starting piece 1 at 2010-11-16 10:44:47
channel ORA_DISK_2: starting incremental level 1 datafile backupset
........................................................
WARNING: archive log not deleted, not yet applied by standby
archive log filename=/user/oracle/oradata2/archive/webdb/1_58488.dbf thread=1 sequence=58488
Finished backup at 2010-11-16 10:53:19
Starting Control File and SPFILE Autobackup at 2010-11-16 10:53:19
piece handle=/user/oracle/oradata2/backup/c-3059653443-20101116-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2010-11-16 10:53:21
Recovery Manager complete.
2010-12-30 補註
昨天收到 mail alert : 空間又快用完了, 今天到 user site 檢查
發現 RMAN 備份又失敗, 繼續往回找, 分別是 12/21 星期二, 12/24 星期六, 12/28 星期二
備份也同樣的錯誤訊息如下
12/21
RMAN-03002: failure of backup command at 12/21/2010 01:06:10
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
12/25
RMAN-03002: failure of backup command at 12/25/2010 01:07:48
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
12/28
RMAN-03002: failure of backup command at 12/28/2010 01:06:48
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
查看 RMAN 備份的排程, 得知星期六跑的是 Level 0 備份, 而星期二跑的是 Level 1 備份
#------------------------------------------
0 1 * * 6 /user/oracle/script/rman_level0
0 1 * * 0,1,3,4,5 /user/oracle/script/rman_level2
0 1 * * 2 /user/oracle/script/rman_level1
因緣際會剛好查到了 v$archived_log , 摘錄相關時間點的 log
FIRST_TIME BLOCKS*BLOCK_SIZE
------------------- -----------------
2010-12-21 01:03:22 104856576
2010-12-21 01:03:56 104856576
2010-12-21 01:04:30 104856576
2010-12-21 01:05:00 104856576
2010-12-21 01:05:31 104856576
2010-12-21 01:06:01 66984448
2010-12-25 01:04:50 104856576
2010-12-25 01:05:56 104856576
2010-12-25 01:06:34 104856576
2010-12-25 01:07:17 104856576
2010-12-25 01:07:49 104856576
2010-12-25 01:09:15 62071808
2010-12-28 01:03:45 104856576
2010-12-28 01:04:16 104856576
2010-12-28 01:04:49 104856576
2010-12-28 01:05:20 104856576
2010-12-28 01:05:53 104856576
2010-12-28 01:06:24 62151680
詢問使用者得知他們排程 1:00am 開始做 replication
而 1:00am 剛好是 RMAN 開始備份的時間, 所以合理的推斷, 在執行備份的時間,
同時因為大量的異動造成短時間內的多次 log switch 而發生
ORA-00235: controlfile fixed table inconsistent due to concurrent update
因而讓 RMAN 備份失敗, 因此修改 cron table 如下
0 3 * * 6 /user/oracle/script/rman_level0
0 3 * * 0,1,3,4,5 /user/oracle/script/rman_level2
0 3 * * 2 /user/oracle/script/rman_level1
錯誤訊息如下:
RMAN> run{
2> backup
3> incremental level 1 database
4> filesperset=10
5> format '/user/oracle/oradata2/backup/rman/level1_%t_%s_%p';
6> backup archivelog all delete all input
7> format '/user/oracle/oradata2/backup/rman/arch_%t_%s_%p';
8> }
9>
Starting backup at 16-NOV-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=26 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=362 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=163 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/16/2010 01:06:38
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
先查原廠的Solution
ORA-00235 controlfile fixed table inconsistent due to concurrent update
Cause: Concurrent update activity on a control file caused a query on a control file fixed table to read inconsistent information.
Action: Retry the operation.
什麼鳥答案丫, 檢查上週及上上週的備份也都是一樣, 根本就不是再做一次的問題
Starting backup at 09-NOV-10
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=790 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=761 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=612 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 11/09/2010 01:06:25
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
請教 Google 大神, 找不到特別有用的資料, 但發現這個錯誤並不只是在 RMAN 中會發生
再檢查其它天的備份倒很正常, 比較 level 0 的備份 script
$more /user/oracle/script/rman_level0.cmdfile
run{
backup
incremental level 0 database
filesperset=2format '/user/oracle/oradata2/backup/rman/level0_%t_%s_%p';
backup archivelog all delete all input
format '/user/oracle/oradata2/backup/rman/arch_%t_%s_%p';
delete noprompt obsolete ;
}
$more /user/oracle/script/rman_level1.cmdfile
run{
backup
incremental level 1 database
filesperset=10format '/user/oracle/oradata2/backup/rman/level1_%t_%s_%p';
backup archivelog all delete all input
format '/user/oracle/oradata2/backup/rman/arch_%t_%s_%p';
}
看到差異點了, 當初太貪心了, 想說 level 異動的 block 不多, 所以同時多備份幾個, 可能就是這個出問題, 改為 filesperset = 2 試看看
RMAN> run{
2> backup
3> incremental level 1 database
4> filesperset=2
5> format '/user/oracle/oradata2/backup/rman/level1_%t_%s_%p';
6> backup archivelog all delete all input
7> format '/user/oracle/oradata2/backup/rman/arch_%t_%s_%p';
8> }
9>
Starting backup at 2010-11-16 10:44:43
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=125 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=345 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=861 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00018 name=/user/oracle/oradata/webdb/TAINDX.dbf
input datafile fno=00037 name=/user/oracle/oradata/webdb/FR.dbf
channel ORA_DISK_1: starting piece 1 at 2010-11-16 10:44:47
channel ORA_DISK_2: starting incremental level 1 datafile backupset
........................................................
WARNING: archive log not deleted, not yet applied by standby
archive log filename=/user/oracle/oradata2/archive/webdb/1_58488.dbf thread=1 sequence=58488
Finished backup at 2010-11-16 10:53:19
Starting Control File and SPFILE Autobackup at 2010-11-16 10:53:19
piece handle=/user/oracle/oradata2/backup/c-3059653443-20101116-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2010-11-16 10:53:21
Recovery Manager complete.
2010-12-30 補註
昨天收到 mail alert : 空間又快用完了, 今天到 user site 檢查
發現 RMAN 備份又失敗, 繼續往回找, 分別是 12/21 星期二, 12/24 星期六, 12/28 星期二
備份也同樣的錯誤訊息如下
12/21
RMAN-03002: failure of backup command at 12/21/2010 01:06:10
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
12/25
RMAN-03002: failure of backup command at 12/25/2010 01:07:48
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
12/28
RMAN-03002: failure of backup command at 12/28/2010 01:06:48
ORA-00235: controlfile fixed table inconsistent due to concurrent update
RMAN-06031: could not translate database keyword
查看 RMAN 備份的排程, 得知星期六跑的是 Level 0 備份, 而星期二跑的是 Level 1 備份
#------------------------------------------
0 1 * * 6 /user/oracle/script/rman_level0
0 1 * * 0,1,3,4,5 /user/oracle/script/rman_level2
0 1 * * 2 /user/oracle/script/rman_level1
因緣際會剛好查到了 v$archived_log , 摘錄相關時間點的 log
FIRST_TIME BLOCKS*BLOCK_SIZE
------------------- -----------------
2010-12-21 01:03:22 104856576
2010-12-21 01:03:56 104856576
2010-12-21 01:04:30 104856576
2010-12-21 01:05:00 104856576
2010-12-21 01:05:31 104856576
2010-12-21 01:06:01 66984448
2010-12-25 01:04:50 104856576
2010-12-25 01:05:56 104856576
2010-12-25 01:06:34 104856576
2010-12-25 01:07:17 104856576
2010-12-25 01:07:49 104856576
2010-12-25 01:09:15 62071808
2010-12-28 01:03:45 104856576
2010-12-28 01:04:16 104856576
2010-12-28 01:04:49 104856576
2010-12-28 01:05:20 104856576
2010-12-28 01:05:53 104856576
2010-12-28 01:06:24 62151680
詢問使用者得知他們排程 1:00am 開始做 replication
而 1:00am 剛好是 RMAN 開始備份的時間, 所以合理的推斷, 在執行備份的時間,
同時因為大量的異動造成短時間內的多次 log switch 而發生
ORA-00235: controlfile fixed table inconsistent due to concurrent update
因而讓 RMAN 備份失敗, 因此修改 cron table 如下
0 3 * * 6 /user/oracle/script/rman_level0
0 3 * * 0,1,3,4,5 /user/oracle/script/rman_level2
0 3 * * 2 /user/oracle/script/rman_level1
留言
張貼留言