自由的風: 8月 2011

星期四, 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 中執行

訂閱：文章 (Atom)

搜尋此網誌