星期四, 8月 25, 2011

Grid Control Agent 10.2.0.5 Fails to Monitor 9.2.0.x Databases with ORA-3113/ORA-7445

I got the error after installed the agent.

WARN vpxoci: Login 0x3549830 failed, error=ORA-03113

OS: Windows 200
DB: Oracle 9.2.0.6
GC Agent 10.2.0.5

Solution:
There are two solutions to this issue:
A. Upgrade the target database to a version which is covered by Premier Support or apply the 9.2.0.8 terminal patch set to the target database. The recommended solution is to upgrade the database.

B. If you cannot upgrade your target database to 9.2.0.8, there is also a fix on the Agent side:
Apply Agent PSU2 Patch 9162498 (Unix) / Patch 9223116 (Windows) on top of the 10.2.0.5 Agent. Please check Note 986539.1 for more details about PSU2.
To activate the fix, add the parameter AvoidOCIPing=1 in $AGENT_HOME/sysman/config/emd.properties and restart the Agent. This parameter is not there by default: it is a toggle to activate the bug fix for this particular issue. The default value is 0, which means that the fix is not activated.
OK, I use Patch 9223116

I use opatch to apply this, second error came to me.

C:\OracleHomes\agent10g\OPatch>opatch
Oracle Home is not set. OPatch cannot proceed!

OPatch failed with error code = 1

It's ORACLE_HOME missing, OK.

set ORACLE_HOME=C:\OracleHomes\agent10g

Java could not be located. OPatch cannot proceed!

OPatch failed with error code = 1

It's mean OPATCH can't find java.

so use this command

opatch lsinventory –jre %ORACLE_HOME%\jdk

Finally, my agent can work perfectly.

MySQL index extract to Oracle Create Index statement!

I am transforming MySQL to Oracle. The data on MySQL had transformed to Oracle, but the index hadn't.

Now, I want to extract index ddl from mysql and not found any tool like navicat for MySQL can do this.

So, I try to use mysqldump to dump whole database in sql script.

I viewed this dump file, the index would be displayed like ..

KEY `adt_bed_basic_idx05` (`station_no`,`bed_status`),
KEY `adt_bed_basic_idx06` (`sex`),
KEY `adt_bed_basic_idx07` (`bed_level`),
KEY `adt_bed_basic_idx08` (`charge_specific`),
KEY `adt_bed_basic_idx09` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1356 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

So, KEY is the index.

Now, I have to figure out come keyword to generate "CREATE INDEX" script

1. Schema Name

2. Index Name

3. Table Name

4. Column Name

============================================
This is my step.

1. Dump Whole MySQL DB

mysqldump --no-data --table --all-databases >>schema.dmp

2. Extract KEY words

egrep -i "CREATE TABLE|KEY|CURRENT" schema.dmp |egrep -v "PRIMARY|FOREIGN|50001|40014" >mysql.sql

3. Generate Create Index DDL

I use shell script. The command is ..

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

while read LineSQL

C1=`echo $LineSQL|cut -d" " -f1 `

C2=`echo $LineSQL|cut -d" " -f2 `

C3=`echo $LineSQL|cut -d" " -f3 `

C4=`echo $LineSQL|cut -d" " -f4 `

C5=`echo $LineSQL|cut -d" " -f5 `

C6=`echo $LineSQL|cut -d" " -f6 `

C7=`echo $LineSQL|cut -d" " -f7 `

#echo "C1 $C1"

#echo "C2 $C2"

#echo "C3 $C3"

#echo "C4 $C4"

#echo "C5 $C5"

#echo "C6 $C6"

#echo "C7 $C7"

if [ $C1 = "--" ];then

#echo "Change User to $C4"

export SCHEMA=$C4

elif [ $C1 == "CREATE" ];then

#echo "Table Name is $C3"

export TB=$C3

elif [ $C1 == "KEY" ];then

#echo "Index Name is $C2"

#echo "Column Name is $C3"

export INDX=$C2

export COLS=$C3

echo "Create Index $SCHEMA.$INDX on $SCHEMA.$TB $COLS"

echo "tablespace HIS_IDX_01;"

#echo "Error Data is $C1,$C2,$C3,$C4,$C5,$C6,$C7"

done

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The Result like this:

Create Index adt.adt_bed_basic_idx01 on adt.adt_bed_basic (bed_no)

tablespace HIS_IDX_01;

OK, I had done.

星期二, 8月 09, 2011

ORACLE IAS WebCache can't startup

CAUSE 1
Web Cache was started by a user or process that is running under a different group which doesn't have sufficient privilege
to change to the group listed in webcache.xml (only root is allowed to change groups).

CAUSE 2
The owner of the Oracle Home does not have the correct unix primary group (GID) setting. There is a mismatch between the information
returned by the unix 'id' command and the information held in the /etc/group file. For

I solved this problem use this command by root privilege.

webcache_setuser.sh setroot oracle

then, webcache can start correctly.

星期一, 8月 08, 2011

Grid Control can't discover target like ias etc..(GC無法自動抓到TARGET)

I installed GC agent at a IAS Server.

But, the agent always couldn't discover Oracle IAS instance.

Even, I use "agentca -f" to reconfig, or "agentca -d " to rediscover. Target was still one host and one agent.

OK, I got some information that I should add oratab file to add Oracle IAS Home.

I did and the rediscover was still missing this target.

Doc ID 789532.1

Finally, the doc help me to solve this problem.

I didn't do any step on the doc.

I check the targets.xml at AS_HOME (IAS_HOME)

The file privilege is ..

-rw-r----- 1 oracle oinstall 6921 Aug 8 15:06 targets.xml

and

Oracle Agent owner privilege is..

uid=502(oragc) gid=502(oragc) groups=502(oragc),501(dba)

So, I understood the agent have enough privilege to access IAS's targets.xml

After I add oragc user to oinstall group the agent can discover this IAS instance.

===============================================

Oracle Grid Control Agent 若要去自動搜尋到相關的target，有二個條件

1、要將目的端的ORACLE_HOME加入到oratab中

2、目的端的ORACLE_HOME/sysman/emd/targets.xml 要能讓Agent 有讀取權限，才能自動抓到。

星期一, 8月 01, 2011

java.sql.SQLException: ORA-00911: invalid character

uild 15366 from 2011-06-14 12.04.35 by buildguy) :
2011/08/02 00:50:10 - Source-MySQL-ier_flow_sheet.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1880)
2011/08/02 00:50:10 - Source-MySQL-ier_flow_sheet.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:220)
2011/08/02 00:50:10 - Source-MySQL-ier_flow_sheet.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:130)
2011/08/02 00:50:10 - Source-MySQL-ier_flow_sheet.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
2011/08/02 00:50:10 - Source-MySQL-ier_flow_sheet.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) : at java.lang.Thread.run(Unknown Source)
2011/08/02 00:50:10 - Source-MySQL-ier_flow_sheet.0 - ERROR (version 4.2.0-RC1, build 15366 from 2011-06-14 12.04.35 by buildguy) : Caused by: java.sql.SQLException: ORA-00911: invalid character

今天在執行一段sql時，發現在sql developer上執行沒問題的語法，到java上執行就出錯了。
原本以為是語法錯誤、殘字、空白…等，到後來才發現不是這樣的原因。

只要把sql語法中的「;」去除，則可以正常執行了。

MySQL 轉 Oracle 一些問題整理

1、Join

MySQL 中的Join, 若其後的 Join 條件有on, 則為Inner Join . 若為where 條件，則為Inner Join

Cross Join with Where == Inner Join with on

SELECT * FROM CITIES CROSS JOIN FLIGHTS
WHERE CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT

SELECT * FROM CITIES INNER JOIN FLIGHTS
ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT

2、Alias

在oracle中，alias 用在column 中，是可以用as xxx 來替代

但如果對Table alias ，則只有一種方式，

表示法為： from Table_name AAA ==> AAA為alias name

as 在table 這個地方有個用途，是用來做為版本查詢

Oracle 在經過設定後，可以保留一定時間的舊資料

若您在查詢某一系統時間時，table的欄位值就可以透過 Table as of timestamp來查詢

範例如下：

SELECT salary FROM employees
WHERE last_name = 'Chung';

SALARY
----------
3800

UPDATE employees SET salary = 4000
WHERE last_name = 'Chung';
1 row updated.

SELECT salary FROM employees
WHERE last_name = 'Chung';

SALARY
----------
4000

To learn what the value was before the update, you can use the following Flashback Query:

SELECT salary FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE) ==>查詢系統一分鐘前的資料
WHERE last_name = 'Chung';

SALARY
----------
3800

因此結論是，若在from 後面的alias 一律把as 拿掉，才能順利在Oracle 中執行

星期日, 7月 24, 2011

MySQL 轉 Oracle Join 的問題

今天遇到一個 MySQL 轉 Oracle Join 的問題

[由原 MySQL COPY 過來的SQL] -->> 不可執行
select xyz.xyz_abc.id AS id,
xyz.xyz_abc.version AS version,
xyz.xyz_abc.chart_no AS chart_no,
xyz.xyz_chrbas.pat_name AS chart_Name,
xyz.xyz_abc.special_code AS special_code,
xyz.xyz_abc.disabled AS disabled
from xyz.xyz_chrbas join xyz.xyz_abc where xyz.xyz_abc.chart_no = xyz.xyz_chrbas.chart_no

[修改後的SQL] 可執行
select xyz.xyz_abc.id AS id,
xyz.xyz_abc.version AS version,
xyz.xyz_abc.chart_no AS chart_no,
xyz.xyz_chrbas.pat_name AS chart_Name,
xyz.xyz_abc.special_code AS special_code,
xyz.xyz_abc.disabled AS disabled
from xyz.xyz_chrbas, xyz.xyz_abc where xyz.xyz_abc.chart_no = xyz.xyz_chrbas.chart_no

此的問題主要在Inner Join vs Cross Join的差異

MySQL join 語法本身有部份隱藏性的功能，在MySQL中，Inner Join 可等於 Cross Join，差別只在你是否有Join Condition "On" or "Using"

在而在Oracle 中，Cross Join必須明確指定

簡單的解決方法，把mysql 中的 join 改為Cross Join 或逗號代替即可。

若本意是要用 Inner Join, 不論是MySQL 或 Oracle ，必須要以" On" or " Using "作為 Join Condition.

若改用逗號，則是由資料庫依Join Condition 自行決定，省去指定的麻煩

星期六, 6月 25, 2011

rman 舊備份記錄清除

最近新接手一套9i rac

因此開始要進行一堆翻修的動作。

當然第一個要做的就是備份。

9i之後的備份，已經不太去進行exp / imp的動作

原因是要花太多的時間，尤其是imp時還要注意許多schema與權限問題。

因此我一律採用Rman的備份。

不過今天要說的這套，因為有"前人"努力過，所以留下了一堆殘缺不全的內容。

在我備份完後，進行清除舊資料時，發現原先用磁帶備份的資料，會產生錯誤造成rman執行不完整。

delete force noprompt obsolete;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 06/26/2011 02:33:41
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

怎麼刪都刪不掉，一直以為是因為之前備份是用tape，所以才備份失敗

所以改用：

configure device type 'sbt_tape' clear;

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' CLEAR;

想來清除與磁帶相關上的記錄

結果還是沒變。

一直到這個指令出現，才是正確解答(的一部份)

RMAN> delete noprompt obsolete device type disk;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=588 devtype=DISK
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 15385 24-JUN-11
Backup Piece 15385 24-JUN-11 /oracle/dbs/c-1189594198-20110624-00
Datafile Copy 412 25-NOV-05 /dev/vx/rdsk/testdg/tools_50
Datafile Copy 413 25-NOV-05 /dev/vx/rdsk/testdg/index-his-30g
Datafile Copy 414 25-NOV-05 /dev/vx/rdsk/testdg/userdb_pacs_02_2048
Datafile Copy 415 25-NOV-05 /dev/vx/rdsk/testdg/userdb_his_10_10240
Datafile Copy 444 29-DEC-05 /dev/vx/rdsk/testdg/userdb_his_03_10240
Datafile Copy 445 29-DEC-05 /dev/vx/rdsk/testdg/userdb_his_04_10240
Datafile Copy 446 29-DEC-05 /dev/vx/rdsk/testdg/userdb_pacs_01_4096
Datafile Copy 447 29-DEC-05 /dev/vx/rdsk/testdg/userdb_his_05_10240
Datafile Copy 448 29-DEC-05 /dev/vx/rdsk/testdg/userdb_his_02_10240
Controlfile Copy 1267 02-MAY-08 /oracle/rscp/pacsctlbk.ctl
deleted backup piece
backup piece handle=/oracle/dbs/c-1189594198-20110624-00 recid=15385 stamp=754671617
deleted controlfile copy
controlfile copy filename=/oracle/rscp/pacsctlbk.ctl recid=1267 stamp=653691555
Deleted 2 objects

RMAN-06207: WARNING: 9 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
List of Mismatched objects
==========================
Object Type Filename/Handle
--------------- ---------------------------------------------------
Datafile Copy /dev/vx/rdsk/testdg/tools_50
Datafile Copy /dev/vx/rdsk/testdg/index-his-30g
Datafile Copy /dev/vx/rdsk/testdg/userdb_pacs_02_2048
Datafile Copy /dev/vx/rdsk/testdg/userdb_his_10_10240
Datafile Copy /dev/vx/rdsk/testdg/userdb_his_03_10240
Datafile Copy /dev/vx/rdsk/testdg/userdb_his_04_10240
Datafile Copy /dev/vx/rdsk/testdg/userdb_pacs_01_4096
Datafile Copy /dev/vx/rdsk/testdg/userdb_his_05_10240
Datafile Copy /dev/vx/rdsk/testdg/userdb_his_02_10240

哈，只刪了一個Control file，不過還好，至少沒有錯誤訊息了。

由於這個Device type disk 的提示，我後來就進行Crosscheck copy的找出已經失效檔案，再進行刪除記錄：

RMAN> delete noprompt obsolete device type disk;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=588 devtype=DISK
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Datafile Copy 412 25-NOV-05 /dev/vx/rdsk/testdg/tools_50
Datafile Copy 413 25-NOV-05 /dev/vx/rdsk/testdg/index-his-30g
Datafile Copy 414 25-NOV-05 /dev/vx/rdsk/testdg/userdb_pacs_02_2048
Datafile Copy 415 25-NOV-05 /dev/vx/rdsk/testdg/userdb_his_10_10240
Datafile Copy 444 29-DEC-05 /dev/vx/rdsk/testdg/userdb_his_03_10240
Datafile Copy 445 29-DEC-05 /dev/vx/rdsk/testdg/userdb_his_04_10240
Datafile Copy 446 29-DEC-05 /dev/vx/rdsk/testdg/userdb_pacs_01_4096
Datafile Copy 447 29-DEC-05 /dev/vx/rdsk/testdg/userdb_his_05_10240
Datafile Copy 448 29-DEC-05 /dev/vx/rdsk/testdg/userdb_his_02_10240
deleted datafile copy
datafile copy filename=/dev/vx/rdsk/testdg/tools_50 recid=412 stamp=575303261
deleted datafile copy
datafile copy filename=/dev/vx/rdsk/testdg/index-his-30g recid=413 stamp=575307628
deleted datafile copy
datafile copy filename=/dev/vx/rdsk/testdg/userdb_pacs_02_2048 recid=414 stamp=575313295
deleted datafile copy
datafile copy filename=/dev/vx/rdsk/testdg/userdb_his_10_10240 recid=415 stamp=575317535
deleted datafile copy
datafile copy filename=/dev/vx/rdsk/testdg/userdb_his_03_10240 recid=444 stamp=578337094
deleted datafile copy
datafile copy filename=/dev/vx/rdsk/testdg/userdb_his_04_10240 recid=445 stamp=578339270
deleted datafile copy
datafile copy filename=/dev/vx/rdsk/testdg/userdb_pacs_01_4096 recid=446 stamp=578340124
deleted datafile copy
datafile copy filename=/dev/vx/rdsk/testdg/userdb_his_05_10240 recid=447 stamp=578340816
deleted datafile copy
datafile copy filename=/dev/vx/rdsk/testdg/userdb_his_02_10240 recid=448 stamp=578342100
Deleted 9 objects

至此，問題終於解決了。

星期六, 6月 18, 2011

Standby Database for Read only Query problems.

I wanna compare two production databases data, so I created two standby databases for comparing data.

After opening database read only, I use minus function to compare the same table on each database.

There were some error raised:

ORA-16000: database open for read-only access

It was a strange error, because not all of these tables had this error.

After some testing, I found the root cause.

The standby database leak temp tablespace. It could not be created during rman recovery process.

So, I add temp file to temp tablespace. It worked.

ps. Since the database was read only, you can add file only, create or drop temp tablespace would

be failed.

有一次進行資料比對作業的測試，目標是二個standby database。

在資料庫開到read only mode後，利用minus 的函數去進行資料比對。

結果發生以下錯誤：

ORA-16000: database open for read-only access

在經過一番測試後，發現是temp tablespace在利用rman建置時遺漏了，因此在新增temp file後，問題就解決了。

注意：由於資料庫在READ ONLY的狀況下，建立或刪除temp tablespace都會產生錯誤。

星期四, 6月 09, 2011

資料庫調效經驗

今天的環境是：

Solaris 8 and Oracle 9.2.0.6 export / import to Linux 4 and Oracle 9.2.0.6

為一異質平台的轉換。

由於Oracle 9i 尚未支援Transportable Tablespace，因此只能依賴傳統的export / import。

問題反應：

1、某些資料查詢過於緩慢，較舊系統緩慢約3~4倍時間。

2、大量Procedure & Function 無法使用

分析過程：

1、利用Toad 的Monitor session的功能，在監控查詢時卡住的程序，並判斷是否有使到到Index

2、若有使用到Index的查詢時，系能仍緩慢，則開啟Trace來監控。

3、檢視procedure & Function 的Error messages

分析結果：

1、由於tables & indexes 沒有統計值，因此造成大量的Full Table Scan. 重新analyze 後，即完成。

2、有用到Index的查詢仍舊緩慢，經檢查後，發現使用到錯誤的indexes。重新將table與index同時analyze後就恢復正常。

3、由於客戶有自行定義Function, 而這function又建立在其它的schema中，再加上原先有的public synonyms並未一併匯入進去(這點很怪)，因此在重新建立 Synonyms後即完成任務。

另外的問題：

1、客戶某個table中，日期的存放格式為Month Varchar2(5), 以10005代表100年5月。在某支查詢中，會出現1722的錯誤 invalid number。

查看sql語法為to_number(month)>= :argument_1。因此去查詢table中的資料，發現有一筆的資料內容並非是數字，

因此造成字元轉換的錯誤。但…怪異的事，相同的語法在舊系統上仍可以查詢出來，真是怪哉呀。

最後把資料刪除後，程式即運作正常。

2、exp / imp 一般而言會有統計值，除非你在import 或 export 過程中，指定不匯出/ 匯入統計值，才會發生今天的問題。

3、Trace 檔沒用到，就解決了上述問題，下次再試試吧。

星期四, 5月 19, 2011

Use Pl/SQL to compare 2 tables

This Script is used to generate Dynamic SQL.

The SQL is used to compare 2 tables using minus function

CREATE OR REPLACE procedure SelTab2Minus
( name_in IN varchar2,table_in IN varchar2 )
IS
stmt varchar2(2000);
col_name VARCHAR2(30);

cursor c1 is
select column_name
from dba_tab_cols
where owner= name_in and table_name = table_in;

BEGIN

open c1;
LOOP
fetch c1 into col_name;
-- DBMS_OUTPUT.PUT_LINE(stmt);
stmt := stmt || col_name ;
EXIT WHEN c1%NOTFOUND;
stmt := stmt ||',';
END LOOP;
close c1;
--DBMS_OUTPUT.PUT_LINE(stmt );
DBMS_OUTPUT.PUT_LINE('select ' || stmt ||' from ' || name_in||'.'||table_in);
--DBMS_OUTPUT.PUT_LINE(' from ' || name_in||'.'||table_in);
DBMS_OUTPUT.PUT_LINE(' minus ');
DBMS_OUTPUT.PUT_LINE('select ' || stmt ||' from ' || name_in||'.'||table_in||'@VASMD_46');

END;
/

星期五, 5月 06, 2011

Startup Listener with password using Script

If the listener.ora set with password, you can't stop listener like "lsnrctl stop".

You will receive the error message like below :

[oracle@localhost ~]$ lsnrctl stop

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 06-MAY-2011 07:48:15

Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
TNS-01169: The listener has not recognized the password

I wrote a script to stop listener with password

======== Script Start Hear ===========
#!/usr/bash

export PATH
unset USERNAME
export ORACLE_HOME=/home/oracle/OraHome1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=ABC
export JRE_HOME=/usr/java/jre1.6.0_24

lsnrctl< Current Listener is LISTENER
LSNRCTL> The command completed successfully
LSNRCTL> Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
The command completed successfully

星期日, 1月 23, 2011

Oracle GridControl agent upload error

My notebook has installed oracle grid control agent.

Someday, I flashback the system, then GC agent stop upload and sync with GC server.

After I execute "emctl upload" on cmd console. It's return:

D:\oracle\agent10g\bin>emctl upload
Oracle Enterprise Manager 10g Release 5 Grid Control 10.2.0.5.0.
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

I try so many ways to solve this issue, but no one was workable.

Today, I open GC web page, found a page about the agent status.

It's blocked!

I try to unblock it. But, It was still block couple seconds later.

After I read the red words, I can see what I should do.

I clicked the Agent Resynchronization Buttom.

Wait a moment, the agent status is fine.

星期三, 11月 24, 2010

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介傳說的偶數版本是較穩定的版本，看來有其道理。

星期日, 10月 31, 2010

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就找得到了。

星期六, 10月 23, 2010

解決看不到chm檔案的方法

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

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

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

步驟就參考下圖囉。

星期二, 10月 19, 2010

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.

星期三, 10月 13, 2010

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後，即可正常更新。

星期三, 10月 06, 2010

安裝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 囉。

今天先這樣

訂閱：文章 (Atom)

自由的風

搜尋此網誌