Oracle - Alert log

今天讀到Monitor Diagnostic Files這塊。忽然想起來，之前把資料庫搞掰咖之後，一直沒發現，就是因為我從排程器裡看他都回報備份有正常結束(0x0)。

真正detail的訊息還是得靠oracle自己的trace file們。果然，再回去找alert log，果然就看到一些相關訊息。

先來介紹一下trace file。

Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file.

The alert file, or alert log, is a special trace file. The alert file of a database is a chronological log of messages and errors, which includes the following:

1. All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur
   
2. Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP, SHUTDOWN, and ARCHIVELOG statements
3. Several messages and errors relating to the functions of shared server and dispatcher processes
   
4. Errors occurring during the automatic refresh of a materialized view
   
5. The values of all initialization parameters at the time the database and instance start

Oracle uses the alert file to keep a log of these special operations as an alternative to displaying such information on an operator’s console (although many systems display information on the console). If an operation is successful, a "completed" message is written in the alert file, along with a timestamp.

這些trace files的位置分別記錄在兩個parameter裡:

BACKGROUND_DUMP_DEST: trace file for background processed, alert file

USER_DUMP_DEST: trace file for server process

要看這些parameter的值可用slqplus下> show parameter <parameter_name>

好! 所以我就去把alert file挖出來，找到事發當時的時間，觀察一下系統在某個datafile offline的狀態下還要做hot backup(alter  tablespace begin backup...) 時揪竟會怎樣。真相來了:

alter tablespace XXX begin backup
ORA-1128 signalled during: alter tablespace AP begin backup... 
alter tablespace XXX end backup
ORA-1142 signalled during: alter tablespace AP end backup...

果然就是有問題啦。順便查了這兩個錯誤訊息，分別如下:

ORA-1128
Description: cannot start online backup - file string is offline
Cause: An attempt to start an online backup found that one of the files is offline

ORA-1142
Description: cannot end online backup - none of the files are in backup
Cause: None of the files were found to be in online backup when attempting to end an online backup

完畢!!