Oracle 10g 10.2.0.5 Patch

最近客戶一直要求將資料庫由10.2.0.4升級至10.2.0.5

為了讓他了解到升級的風險，因此上網找了升級後出問題的例子：

案例1  post on 11/08/2010

簡述一下內容：此事件的資料庫由10.2.0.1升級至10.2.0.5後(11/10/2010)，原本2分鐘的sql 指令，跑了18小時才完成。

Oracle Support的建議是請他將相容性降至10.2.0.4，才能達到4分鐘(2X時間)完成。

案例二 post on  06/04/2010 相似的bug post on 11/03/2010

這個案例造成hash_Join出錯

案例三 post on 10/26/2010

在 ANSI join時出錯，原文提出的徵況與解法如下：

The pattern that I spotted was:

• in all cases complex views are involved in the queries (outer joins, nested selects, unions...you named it),
• roughly half of the views were written using ANSI join syntax, the other half used old-style join syntax,
• performance problem was reported only on queries that selected data from the views having ANSI join syntax, old-style views performed well
• if the problematic view with ANSI join was rewritten to old-style, it performed well

Workarounds that we found:

• rewrite views containing ANSI joins to use old style join syntax,
• hint the views with /*+ NO_QUERY_TRANSFORMATION */ hint,
• disable CBO query transformations at the session level with logon trigger such as:

查了一下Metalink 有關10.2.0.5的相關問題時發現一個清單(如下)

Issues introduced in 10.2.0.5

This section lists bugs introduced in 10.2.0.5 (if any). Such issues may be either serious or trivial but the aim is to list them all to help customers assess the risk of applying the Patch Set on top of 10.2.0.4

Bug/Doc	Fixed in PSU	Description	Updated
Note:1229669.1*		OERI [kddummy_blkchk] / OERI [5467] for an aborted transaction of allocating extents	18/Nov/2010
10158493		Wrong Results when fix for bug 6082605 is present	08/Nov/2010
10017048		Parallel query / DML does not run in parallel in deep nesting of a DBMS_JOB and PLSQL procedure	09/Sep/2010
10010310		ORA-27300 / ORA-27302 killing a non existing session	15/Nov/2010
9981011		Oracle processes consume large numbers of file descriptors	11/Nov/2010
9980062		Dump [kkocxj] from cost based query transformation	18/Oct/2010
9949948P		Linux: Process spin under ksfdrwat0 if OS Async IO not configured high enough	03/Aug/2010
9928290		The fix for UTL_FILE memory leak bug 7197637 impacts performance	20/Nov/2010
9871430		ORA-31011 / LPX-230 converting CLOB to XMLTYPE	15/Sep/2010
9726739		ORA-997 from SQL with CONNECT BY and UNION	18/Nov/2010
9360157		Wrong results using ORA_ROWSCN pseudocolumn with ANSI join	27/Aug/2010
9020537		DBMS_LOB.SUBSTR truncates LOBs > 8191 in multibyte	02/Sep/2010
7612454		More "direct path read" operations / OERI:kcblasm_1	15/Jul/2010
7361418		ORA-600 querying V$LOGMNR_CONTENTS for logs without supplemental logging	18/Nov/2010
Note:1229669.1*	10.2.0.5.1	ORA-600 [ktsptrn_fix-extmap] during extent allocation caused by bug 8198906	18/Nov/2010

DBA介傳說的偶數版本是較穩定的版本，看來有其道理。

Grid Control 中 Listener 狀態是down的修正

今天安裝了Grid Control for Linux 10.2.0.5

發現了一些問題：

1、找尋target的指令中，沒有listener選項，利用agentca -d 重新找一遍即可還原。

      不過未來在找非預設ip & Port的listener時，可能會比較麻煩。

PS. 我忘了，應該在EM=> TARGET=> ALL TARGET=> AGENT的內去Discover Listener即可

2、原生10.2.0.1的安裝套件中，agent 無法順利安裝

3、在升級GC至10.2.0.5後，將Agent 安裝新版的10.2.0.5即可正常使用。

4、預設找到的Listener狀態會是down。修改一下主機名稱中，將最後的一個  "."移除，GC就找得到了。

解決看不到chm檔案的方法

我常在網路上下載電子書來看，有許多的電子書是pdf的，可以直接用pdf程式來讀取，比較沒什麼問題。然而有一些電子檔是屬於chm格式，不需要任何程式即可打開來讀取。

chm文件打不開有時候是因為損毀。但如果像我從不同網站下載後，一樣打不開時，就會懷疑是不是那裏有問題?

其實只要把文件的屬性中，鎖定的項目解決後，即可解決這個問題。

步驟就參考下圖囉。

Database of slogans.

Nice Collect, the Original Post from http://www.textart.ru/database/slogans/computers/software/list.html


LANSA software company
Advertising slogan: LANSA. Advanced software made simple.

Microsoft
Ad slogans: Your Potential. Our Passion.
                  Where do you want to go today?

Apple / iPhoto multimedia software
Advertising slogan: Apple iPhoto. Shoot it. Save it. Share it

Compuware Corporation, software and IT services
Slogans: Compuware. The Leader in IT Value.
              Software Superior by design

Oracle
Taglines: Runs faster. Costs less. And never breaks. (Oracle Grid Computing)
              Enabling the Information Age.
              Oracle. Software Powers the Internet.

SAP business software, Germany
Slogans: The best-run businesses run SAP.
              Have fun with SAP.

SSA Global, USA
Advertising slogan: SSA. Forward Faster.

Siebel Systems, CRM (Customer Relationship Management) software
Slogans: Siebel. It's all about the customer.
              Ensuring Customer Success.

SCO Group, software solutions for small- to medium-sized businesses
Advertising slogan: SCO Grows Your Business.

CA - Computer Associates
Marketing slogan: CA. Software that can think.

Authentex Software
Advertising slogan: After all, it is your information.

FileMaker database software
Taglines: FileMaker. Effortlessly manages all your information.
              What's your problem?

Sage software company, accounting and business management software
Advertising slogan: Sage. Your business in mind.

ACT! – Contact & Customer Management Software
Motto: Make contact. Build relationships. Get results.

Opatch Error Code 74

今天在升級Oracle Database 11gR2的patch 時，出現了以下的錯誤


Following executables are active :
/oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle
ApplySession failed during prerequisite checks: Prerequisite check "CheckActiveFilesAndExecutables" failed.
System intact, OPatch will not attempt to restore the system

OPatch failed with error code 74
[oracle@gc 9165206]$ sqlplus /nolog

後來上網查了一下，是由於相關的程式正在執行中，所以無法繼續。

觀察了一下上面的錯誤，其實裏面就已經告訴我了，是

/oracle/app/oracle/product/11.2.0/dbhome_1/bin/oracle

這隻主程式正在使用中，即資料庫必須關閉才可以使用。

了解後，執行關閉資料庫，再執行opatch apply後，即可正常更新。

安裝Oracle Grid Control 11g on Oracle Linux

為了要survey Oracle Grid Control 11g 這個產品，於是就利用新採購的機器進行安裝測試。

安裝環境： VMWARE Workstation 7
OS: Oracle Enterprise Linux 5 Update 5 64bit
CPU: VM中設定為2CPU
Disk Space:40G

安裝os的部份就略過不提了，接下來就講安裝的過程。

安裝時，參考別人的安裝手冊

安裝過程中，會檢查os環境參數與程式，若遇到沒有設定好的部份，就會產生以下的狀況

可以點選錯誤訊息中所附的連結，得到有用的資訊來進行修正。

ok，來到這次要說的重點了

缺少的package怎麼辦? 安裝手冊中，有教要用yum 來更新，但我試半天都無法更新。

正在絕望中，問了谷歌大神一下，找到一個有用的連結，原來呀，oracle的linux預設沒有yum的server對應，因此必須設定好後才能使用

因此我做了以下的動作：

1、下載 yum的設定檔：

    # cd /etc/yum.repos.d

  # wget http://public-yum.oracle.com/public-yum-el5.repo

2、修改此設定檔中，el5_u5_base及ol5_u5_base的值，將enable=0 改為 enable=1
3、重新執行 yum install sysstat 便成功了。



[root@GC11g yum.repos.d]# yum install sysstat
Loaded plugins: security
el5_u5_base                                              | 1.1 kB     00:00     
el5_u5_base/primary                                      | 944 kB     00:04     
el5_u5_base                                                           4372/4372
ol5_u5_base                                              |  957 B     00:00     
ol5_u5_base/primary                                      |  14 kB     00:00     
ol5_u5_base                                                               53/53
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package sysstat.x86_64 0:7.0.2-3.el5 set to be updated
--> Finished Dependency Resolution


Dependencies Resolved


================================================================================
 Package         Arch           Version               Repository           Size
================================================================================
Installing:
 sysstat         x86_64         7.0.2-3.el5           el5_u5_base         174 k


Transaction Summary
================================================================================
Install       1 Package(s)
Upgrade       0 Package(s)


Total download size: 174 k
Is this ok [y/N]: y
Downloading Packages:
sysstat-7.0.2-3.el5.x86_64.rpm                           | 174 kB     00:00     
warning: rpmts_HdrFromFdno: Header V3 DSA signature: NOKEY, key ID 1e5e0159
el5_u5_base/gpgkey                                       | 1.4 kB     00:00     
Importing GPG key 0x1E5E0159 "Oracle OSS group (Open Source Software group) " from http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : sysstat                                                  1/1 


Installed:
  sysstat.x86_64 0:7.0.2-3.el5                                                  


Complete!

安裝完後…災難還沒結束。這個程式還會檢查hostname的設定，但我不理它，選擇忽略後(紅色部份)，繼續下一步。

才沒走二步，馬上就卡住了，男子漢的熱血又湧上來了。

這裏要選擇Middle Ware的地方…看來這次Oracle不強推他的OAS，但卻要另行安裝，這…

好吧，只好再去慢慢下載Weblogic 囉。

今天先這樣

在dos下，如何使用無窮迴圈來作一個監控程式

在dos下，如何使用無窮迴圈來作一個監控程式

對一個dba來說，在dos 下想使用跟unix相同的工具來監控系統，會是一個很大的困擾。

今天在客戶端，為了進行tnsping的監控，user使用了tnsping {tnsname} 20

這是一個偵測資料庫是否可以連接的指令

不過，在dos下必須一直手動執行，才能繼續監控。

這對我來說是一個很不舒服的感覺。

因此我在網路上找到以下的指令：


for /l %%i in (1 1 50000) do (ping 168.95.1.1 -n 1)


其中的ping 為你想執行的系統指令，1 1 5000，代表，每由1開始，間隔1次執行，共執行至50000時停止。

不過真的執行下去，就馬上一直執行。這個結果還不能滿足我的需求，我還要他中間有暫停時間，暫停完後才再度執行指令。
---20100926補充：dos下有timeout指令，可以代替sleep來使用
可以改寫為：


for /l %i in (1 1 50000) do (
ping 168.95.1.1 -n 1 >>d:\test.log
timeout 2
)


---
然而dos下沒有sleep的指令，因此我把想法轉移到perl上面

對一個oracle dba來說，perl是你最好的朋友，只需要環境變數上，加上小小的路徑，你就可以瞬間提升功力。

這段perl的語法如下：


$num = 1;
while($num++){

print "$num\n";
system("ping 168.95.1.1 -n 1");
    sleep(2);
}

注意，這是一個無窮迴圈，若要限制次數，請修改num值，並將++改為--即可。

利用Wget 來抓整個網站

wget 是一個相當好的下載軟體，不過由於是命令模式介面，相較一般程式比較不易讓人接受。

基本的使用方法： wget url: 

例如：wget http://linuxreviews.org/

有關wget 使用手冊還是要靠各位自行去研讀。在網路漫遊的工具上，建議看看這二個手冊 wget (manual page) + less (manual page)。

wget 之所以強大，是在於你能對下載的網站，不斷的深入挖掘，這代表著你能挖光所有的頁面與所有的圖片。

例如： wget -r http://linuxreviews.org/

不過有的網站並不讓你下載全部的內容。這此網站是利用檢查你的流覽器( IE, FireFox,...) 版本。若他發現你不是用網頁流覽器來下載的話，會出現以下的錯誤訊息：

Sorry, but the download manager you are using to view this site is not supported. We do not support use of such download managers as flashget, go!zilla, or getright

--我們不支援下載管理器，如flashget, getright等--

Wget 可以非常方便的利用 -U 參數來對應這些網站. 使用 -U My-browser 來回應適當的版本資訊:

  wget  -r -p -U Mozilla http://www.stupidsite.com/restricedplace.html

其它重要的選項還有： --limit-rate= and --wait=. 你可以加入 --wait=20 來指定每個下載間的暫停時間(20秒), 這可以防止你被列入黑名單中而無法下載。加入 --limit-rate 預設值為 bytes, 加上 K 則為 KB/s. 

例如:

wget --wait=20 --limit-rate=20K -r -p -U Mozilla http://www.stupidsite.com/restricedplace.html

假如你只是簡單的這麼執行 wget http://foo.bar 指令, 網站管理者或許會覺得很沮喪。 而且如果你有限制下載的速度與下載的間隔時間，管理者更將不會查覺。

使用 --no-parent

--no-parent 是一個很方便的選項，這會讓wget 不會下載指定目錄外的其它檔案。

8/2 SQL 進階課程的建置檔

drop user Aha;
drop user Andy;
drop user Ann;
drop user Brian;
drop user Charles;
drop user Cloud;
drop user Clover;
drop user Danniel;
drop user Dennis;
drop user Dylan;
drop user Edgar;
drop user Edward;
drop user Eric;
drop user Frank;
drop user Howard;
drop user James;
drop user Jay;
drop user Jimmy;
drop user John;
drop user Joseph;
drop user Ken;
drop user kidd;
drop user Leon;
drop user Niles;
drop user Rainmo;
drop user Ray;
drop user Richie;
drop user Steven;
drop user Vincent;
drop user Walter;
drop user Xiandong;
drop user Yh;
drop user Yihhann;
drop user Apple;
drop user Banana;
drop user Cobby;
drop user Emily;
drop user Franky;
drop user Gimmy;
drop user Hocky;
drop user Ivy;
drop user Jacky;
drop user Kenny;
drop user Lemon;


create user Aha identified by Aha123 quota 100m on users;
create user Andy identified by Andy123 quota 100m on users;
create user Ann identified by Ann123 quota 100m on users;
create user Brian identified by Brian123 quota 100m on users;
create user Charles identified by Charles123 quota 100m on users;
create user Cloud identified by Cloud123 quota 100m on users;
create user Clover identified by Clover123 quota 100m on users;
create user Danniel identified by Danniel123 quota 100m on users;
create user Dennis identified by Dennis123 quota 100m on users;
create user Dylan identified by Dylan123 quota 100m on users;
create user Edgar identified by Edgar123 quota 100m on users;
create user Edward identified by Edward123 quota 100m on users;
create user Eric identified by Eric123 quota 100m on users;
create user Frank identified by Frank123 quota 100m on users;
create user Howard identified by Howard123 quota 100m on users;
create user James identified by James123 quota 100m on users;
create user Jay identified by Jay123 quota 100m on users;
create user Jimmy identified by Jimmy123 quota 100m on users;
create user John identified by John123 quota 100m on users;
create user Joseph identified by Joseph123 quota 100m on users;
create user Ken identified by Ken123 quota 100m on users;
create user kidd identified by kidd123 quota 100m on users;
create user Leon identified by Leon123 quota 100m on users;
create user Niles identified by Niles123 quota 100m on users;
create user Rainmo identified by Rainmo123 quota 100m on users;
create user Ray identified by Ray123 quota 100m on users;
create user Richie identified by Richie123 quota 100m on users;
create user Steven identified by Steven123 quota 100m on users;
create user Vincent identified by Vincent123 quota 100m on users;
create user Walter identified by Walter123 quota 100m on users;
create user Xiandong identified by Xiandong123 quota 100m on users;
create user Yh identified by Yh123 quota 100m on users;
create user Yihhann identified by Yihhann123 quota 100m on users;
create user Apple identified by Apple123 quota 100m on users;
create user Banana identified by Banana123 quota 100m on users;
create user Cobby identified by Cobby123 quota 100m on users;
create user Emily identified by Emily123 quota 100m on users;
create user Franky identified by Franky123 quota 100m on users;
create user Gimmy identified by Gimmy123 quota 100m on users;
create user Hocky identified by Hocky123 quota 100m on users;
create user Ivy identified by Ivy123 quota 100m on users;
create user Jacky identified by Jacky123 quota 100m on users;
create user Kenny identified by Kenny123 quota 100m on users;
create user Lemon identified by Lemon123 quota 100m on users;


create role rl_index;
grant connect,resource to rl_index;

create public synonym COUNTRIES for hr.COUNTRIES;
create public synonym  DEPARTMENTS for hr.DEPARTMENTS;
create public synonym EMPLOYEES for hr.EMPLOYEES;
create public synonym JOBS for hr.JOBS;
create public synonym  JOB_HISTORY for hr.JOB_HISTORY;
create public synonym LOCATIONS for hr.LOCATIONS;
create public synonym REGIONS for hr.REGIONS;


grant select on hr.COUNTRIES to rl_index;
grant select on hr.DEPARTMENTS to rl_index;
grant select on hr.EMPLOYEES to rl_index;
grant select on hr.JOBS to rl_index;
grant select on hr.JOB_HISTORY to rl_index;
grant select on hr.LOCATIONS to rl_index;
grant select on hr.REGIONS to rl_index;

grant rl_index to Aha ;
grant rl_index to Andy ;
grant rl_index to Ann ;
grant rl_index to Brian ;
grant rl_index to Charles ;
grant rl_index to Cloud ;
grant rl_index to Clover ;
grant rl_index to Danniel ;
grant rl_index to Dennis ;
grant rl_index to Dylan ;
grant rl_index to Edgar ;
grant rl_index to Edward ;
grant rl_index to Eric ;
grant rl_index to Frank ;
grant rl_index to Howard ;
grant rl_index to James ;
grant rl_index to Jay ;
grant rl_index to Jimmy ;
grant rl_index to John ;
grant rl_index to Joseph ;
grant rl_index to Ken ;
grant rl_index to kidd ;
grant rl_index to Leon ;
grant rl_index to Niles ;
grant rl_index to Rainmo ;
grant rl_index to Ray ;
grant rl_index to Richie ;
grant rl_index to Steven ;
grant rl_index to Vincent ;
grant rl_index to Walter ;
grant rl_index to Xiandong ;
grant rl_index to Yh ;
grant rl_index to Yihhann ;
grant rl_index to Apple ;
grant rl_index to Banana ;
grant rl_index to Cobby ;
grant rl_index to Emily ;
grant rl_index to Franky ;
grant rl_index to Gimmy ;
grant rl_index to Hocky ;
grant rl_index to Ivy ;
grant rl_index to Jacky ;
grant rl_index to Kenny ;
grant rl_index to Lemon ;

alter user scott identified by systex7720 account unlock;


create public synonym emp for scott.emp;
create public synonym dept for scott.dept;
create public synonym bonus for scott.dept;
create public synonym SALGRADE for scott.SALGRADE;

grant select on scott.emp to rl_index;
grant select on scott.dept to rl_index;
grant select on scott.dept to rl_index;
grant select on scott.SALGRADE to rl_index;