Reporting Server 2005 異機升級至2008

前陣子做了WSS的SQL SERVER搬家兼升級(2005 to 2008_x64)作業，可是不知道為什麼，搞了很久一直沒辦法好好的把reporting server一起弄過去，裡面有幾張報表，不過都是去撈Oracle資料庫的資料。

遇到的主要問題是:
1. 資料庫部分備份出來後，還需要備份加密金鑰，可是還原的時候加密金鑰的密碼一直打不過XD
2. 新主機上明明就裝了oracle client，可是測試報表時一直抱怨無法連接資料庫，用sqlplus又可以連

因為一直搞不定，所以就擱著不理他，沒想到上禮拜心血來潮找到了一篇相關的文章，照著做就成功了!! 可是該做的當初明明都做過XDD 整個莫名其妙~

moving reporting server 2005 to reporting server 2008

1. 分別備份ReportServer與ReportServerTempDB兩個資料庫，用SQL Server Management Studio做就行了，步驟就不描述
2. 開啟"Reporting Services組態管理員"(Microsoft SQL Server 2005>組態工具 下)，點選"加密金鑰"頁籤，將加密金鑰備份出來

3. 到新主機(SQL Server2008)上，利用"SQL Server Configuration Manager"(Microsoft SQL Server 2008>組態工具 下) 將Reporting Service服務停止

4. 用SQL Server Management Studio分別還原ReportServer與ReportServerTempDB兩個資料庫(Restore With Recovery)，步驟略
5. 同步驟3，再將Reporting Service服務開啟
6. 開啟"Reporting Services組態管理員"(Microsoft SQL Server 2008>組態工具 下)將金鑰還原

7. 完工!!

Oracle client的部分真的是沒理他，也沒重開機，自己就忽然可以動了= = 就這樣~

Ref:

迁移SQL server 2005 Reporting Services到SQL server 2008 Reporting Services全程

[Study] Getting Started With the Oracle Server

這一張主要介紹一些圖形管理介面、sqlplus，還有最重要的，password file。

[Study] Oracle Architectural Components

幾個禮拜前讀的，因為present需要，所以做成ppt。
然後又相當自戀的覺得內容很棒XD 所以分享一下。

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