Oracle資料表空間正在熱備份時關閉執行個體重啟報錯的重現和解決
最近一個客戶的庫在OPEN時報錯需要恢複,發現原因為當時一個資料表空間正在熱備份-->ALTER TABLESPACE TEST1 BEGIN BACKUP; 然後執行個體異常關閉(可能為ABORT或KILL SMON等進程,這裡據說為儲存直接關閉導致),然後重啟時遇到此錯誤。在Oracle 10.2.0.1及11.2.0.4版本中重現了此錯誤,在這兩個版本中同樣的情況但是報錯資訊不太一樣,具體情況如下:10.2.0.1.0 版本資料表空間正在熱備份時關閉執行個體重啟報錯的重現和解決:SQL> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod查看此時資料檔案的狀態:SQL> select tablespace_name,STATUS from dba_tablespaces;set linesize 200set pagesize 200col file_name for a50select file_name,tablespace_name,status from dba_data_files; TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINEUNDOTBS ONLINESYSAUX ONLINETEMPTS1 ONLINETEMP1 ONLINETEMP2 ONLINEEXAMPLE ONLINEINDX ONLINETOOLS ONLINEUSERS ONLINEOLTP ONLINEREGISTRATION ONLINETEST1 ONLINETEST2 ONLINETEST3 ONLINE15 rows selected.SQL> SQL> SQL> SQL> FILE_NAME TABLESPACE_NAME STATUS-------------------------------------------------- ------------------------------ ---------/u01/app/PROD/disk1/system01.dbf SYSTEM AVAILABLE/u01/app/PROD/disk1/undotbs01.dbf UNDOTBS AVAILABLE/u01/app/PROD/disk1/sysaux01.dbf SYSAUX AVAILABLE/u01/app/PROD/disk1/example.dbf EXAMPLE AVAILABLE/u01/app/PROD/disk1/indx.dbf INDX AVAILABLE/u01/app/PROD/disk1/tools.dbf TOOLS AVAILABLE/u01/app/PROD/disk1/users.dbf USERS AVAILABLE/u01/app/PROD/disk1/oltp.dbf OLTP AVAILABLE/u01/app/PROD/disk1/REGISTRATION.dbf REGISTRATION AVAILABLE/u01/app/PROD/disk1/test1.dbf TEST1 AVAILABLE/u01/app/PROD/disk1/test2.dbf TEST2 AVAILABLE/u01/app/PROD/disk1/test3.dbf TEST3 AVAILABLE12 rows selected.########################################################3-->發出熱備份資料表空間的命令:SQL> ALTER TABLESPACE TEST1 BEGIN BACKUP;Tablespace altered.-->查詢此時資料檔案狀態:SQL> select tablespace_name,STATUS from dba_tablespaces;TABLESPACE_NAME STATUS------------------------------ ---------SYSTEM ONLINEUNDOTBS ONLINESYSAUX ONLINETEMPTS1 ONLINETEMP1 ONLINETEMP2 ONLINEEXAMPLE ONLINEINDX ONLINETOOLS ONLINEUSERS ONLINEOLTP ONLINEREGISTRATION ONLINETEST1 ONLINETEST2 ONLINETEST3 ONLINE15 rows selected.SQL> set linesize 200SQL> set pagesize 200SQL> col file_name for a50SQL> select file_name,file_id,tablespace_name,status from dba_data_files; FILE_NAME FILE_ID TABLESPACE_NAME STATUS-------------------------------------------------- ---------- ------------------------------ ---------/u01/app/PROD/disk1/system01.dbf 1 SYSTEM AVAILABLE/u01/app/PROD/disk1/undotbs01.dbf 2 UNDOTBS AVAILABLE/u01/app/PROD/disk1/sysaux01.dbf 3 SYSAUX AVAILABLE/u01/app/PROD/disk1/example.dbf 4 EXAMPLE AVAILABLE/u01/app/PROD/disk1/indx.dbf 5 INDX AVAILABLE/u01/app/PROD/disk1/tools.dbf 6 TOOLS AVAILABLE/u01/app/PROD/disk1/users.dbf 7 USERS AVAILABLE/u01/app/PROD/disk1/oltp.dbf 8 OLTP AVAILABLE/u01/app/PROD/disk1/REGISTRATION.dbf 9 REGISTRATION AVAILABLE/u01/app/PROD/disk1/test1.dbf 10 TEST1 AVAILABLE/u01/app/PROD/disk1/test2.dbf 11 TEST2 AVAILABLE/u01/app/PROD/disk1/test3.dbf 12 TEST3 AVAILABLE12 rows selected.SQL> select * from v$backup; FILE# STATUS CHANGE# TIME---------- ------------------ ---------- ------------------- 1 NOT ACTIVE 0 2 NOT ACTIVE 0 3 NOT ACTIVE 0 4 NOT ACTIVE 0 5 NOT ACTIVE 0 6 NOT ACTIVE 0 7 NOT ACTIVE 0 8 NOT ACTIVE 0 9 NOT ACTIVE 0 10 ACTIVE 195848 2014/11/11 22:12:07 11 NOT ACTIVE 0 12 NOT ACTIVE 012 rows selected.-->可以看到此時有一個資料檔案處於ACTIVE狀態,結合dba_data_files中資訊,此檔案屬於下在熱備份的TEST1資料表空間。此時,新開一個會話,KILL掉SMON進程,或者使用SHUTDOWN ABORT命令關閉資料庫。shutdown immediate關閉會提示如下:SQL> shutdown immediate;ORA-01149: cannot shutdown - file 10 has online backup setORA-01110: data file 10: '/u01/app/PROD/disk1/test1.dbf'--此時ALERT日誌提示Tue Nov 11 22:50:55 2014Shutting down instance: further logons disabled
更多詳情見請繼續閱讀下一頁的精彩內容:
在CentOS 6.4下安裝Oracle 11gR2(x64)
Oracle 11gR2 在VMWare虛擬機器中安裝步驟
Debian 下 安裝 Oracle 11g XE R2