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

完畢!!

Use batch file to backup(copy) files by date

利用Oracle 11g 做了每周系統全備份與每日export檔、設定archive的丟出。丟出來的檔案自己依日期分別放在一個一個資料夾內，為了做異地備份，需要每天、每周去copy出這些檔案，而且是copy到異地硬碟內，不是本機其他硬碟上(特別強調是因為會影響到寫法與執行成功與否)。所以寫了一個batch file，並且設定排程來執行工作。

首先在要放batch file的同層目錄下放一個文字檔(copy_msg.txt)，用來記錄copy過程訊息用。

batch file內容如下:

   1:  rem //rem 表示註解，command line會忽略此行

   2:  rem "echo.": 表示印出一行空白

   3:  rem ">>"   : 將echo的內容以"附加"方式寫入copy_msg.txt檔內

   4:  rem 執行batch file第一件事: 顯示日期時間

   5:  echo. >> copy_msg.txt

   6:  echo start copy ARCHIVELOG at %date% %time% >> copy_msg.txt

   7:   

   8:  rem 切換到copy_msg.txt所在目錄下

   9:  cd \

  10:  e:

  11:   

  12:  rem 先將日期format為oracle丟出來的格式: yyyy_mm_dd，並且存在today變數內

  13:  set today=%date:~0,4%_%date:~5,2%_%date:~8,2%

  14:  echo folder name = %today% >> copy_msg.txt

  15:   

  16:  rem 印出開始做copy的訊息

  17:  echo start copying... >> copy_msg.txt

  18:   

  19:  rem 利用xcopy做複製，帶I參數，表示若目的資料夾不存在，自動產生

  20:  rem 同時將copy訊息都寫入copy_msg.txt

  21:  xcopy X:\<source_path>\%today%\* \\<target_server>\<target_path>\%today% /I >> copy_msg.txt

  22:   

  23:  rem 完成後也印出提示訊息

  24:  echo task end at %date% %time% >> copy_msg.txt

  25:  echo **************************************************************** >> copy_msg.txt

  26:  exit

完成!! 若執行過一次，可以去看copy_msg.txt內容大概長這樣:

start copy ARCHIVELOG at 2009/09/09 星期三 23:59:00.30
folder name = 2009_09_09
start copying...
X:\<source_path>\2009_09_09\O1_MF_1_3689_5BF3H8FR_.ARC
X:\<source_path>\2009_09_09\O1_MF_1_3690_5BF3NLXR_.ARC
X:\<source_path>\2009_09_09\O1_MF_1_3691_5BFQT2CF_.ARC
複製 3 個檔案
task end at 2009/09/09 星期三 23:59:09.61
****************************************************************

這樣就可以達到每天自動複製不同資料夾內的檔案之工程了~

What is Oracle RAC?

Oracle RAC(Real Application Clusters)

DEFINITION - Real Application Cluster (RAC) is a component of the Oracle 9i database product that allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing a clustered database.

According to Oracle, RAC's shared disk method of clustering databases:

increases scalability: because servers can easily be added or subtracted to meet current needs

lowers costs: because companies don't have to buy high-end servers

improves availability: because if one server fails, another can assume its workload.

In a non-RAC Oracle database, a single instance accesses a single database. Where the "database" consists of a collection of data files, control files, and redo logs located on disk; the "instance" comprises the collection of Oracle-related memory and operating system processes that run on a computer system.

In an Oracle RAC environment, two or more computers (each with an instance) concurrently access a single database. This allows an application or user to connect to either computer and have access to a single coordinated set of data.

RAC's shared disk architecture is an unusual approach to database clustering. Most competing database products (such as Microsoft's SQL Server and IBM's DB2 for Windows and Unix environments) use the alternative, which is known as "shared nothing" architecture. Shared nothing architecture partitions data and only gives each server access to its own disk subsystem, while shared disk architecture gives all servers access to the entire database. This adds failover capacity to the database, because all servers have access to the whole database.

Cluster

A computer cluster is a group of linked computers, working together closely so that in many respects they form a single computer. The components of a cluster are commonly, but not always, connected to each other through fast local area networks. Clusters are usually deployed to improve performance and/or availability over that provided by a single computer, while typically being much more cost-effective than single computers of comparable speed or availability.

Ref:

1. SearchOracle.com Definitions - Real Application Cluster
2. WIKIPEDIA - Oracle RAC
3. WIKIPEDIA - Cluster

欠

前陣子(從把資料庫搞掰咖之後，又剛好遇到SQL資料庫要搬家)諸事不順，陸續看了一堆文件都沒空document一下。先記著，以後"有機會、有心力"再補XD。

1. Windows SharePoint Service(WSS3.0)之資料庫搬家順便升級(2005=>2008)
2. Oracle 9i: drop one datafile from tablespace
3. Oracle 9i: control file
4. Oracle 9i: autorun offline backup script
5. Oracle 9i: OS授權?
6. PowerBuilder: cross table, printpreview

What is CULV?

看到這個詞也有一陣子時間了，之前還特地去查過到底是什麼鬼，但是該看的看過一遍，還是無法具體一點講出到底何為CULV?! (我想跟個人表達能力也有點關係= =a) 今天又在癮科技看到這個詞，都有打算換台筆電了，也該好好來認識他一下。

以下剪剪貼貼一些文章片段:

CULV(Consumer Ultra Low Voltage)

CULV處理器嚴格來說不是全新的技術，早在多年前Pentium、Celeron時期就已有LV(Low Voltage)低電壓版處理器、以及ULV(Ultra Low Voltage)超低電壓版處理器。

ULV超低電壓版處理器除了用在上述輕薄筆電(Ultra Slim NB)外、也大量運用於嵌入式系統和手持裝置如UMPC超行動電腦。ULV處理器其實是Intel專為刀鋒伺服器、嵌入式系統和輕薄筆電打造的處理器，是專為體積狹小、散熱空間不足等嚴苛環境所設計。

ULV處理器作法能減輕體積厚度，讓筆電體積更薄，再加上散熱佳、以及低功耗的省電訴求，理所當然受到輕薄筆電愛戴。為了提振常規筆電的買氣，英特爾（Intel）將過往僅用於嵌入式系統、超薄型（Ultra Slim）筆電的超低電壓版（Ultra Low Voltage；ULV）處理器，以較低價格供應筆電業者，並允許用於消費性（Consumer）筆電上，此被稱為CULV NB。

CULV NB的效能優於使用凌動（Atom）處理器的小筆電，且相關規格比照常規筆電，如11、12吋以上的顯示器尺寸，具備光碟機、ExpressCard槽等，但價位、功耗卻低於常規筆電，此成為筆電業者於2009年第2季開始的主攻產品。

以上是一些簡單介紹，若要知道其他與Atom比較的小消息或是CULV定位等，請見reference。

<(_ _)>

Ref:

1. CULV處理器完全解讀
2. CULV筆電仍不可過度期望

ORA-12560: TNS:protocol adapter error

昨天在測試資料庫上要針對一個user做import。

直接在command line裡下>imp後，接著打帳號密碼，就遇上這個問題。

大概知道是什麼問題，還是找個文件來看一下:

Cause:

Oracle classify this as a ‘generic protocol adapter error’. In my experience it indicates that Oracle client does not know what instance to connect to or what TNS alias to use.

因為這台server上裝了三個資料庫，下imp前需先指定是針對哪個資料庫做的操作:

>set oracle_sie=oracle_sid

就這樣，搞定。