自由的風

Oracle Listener on specific ip or all interface

2019-03-26T19:57:00.001-07:00

Oracle listener port listen on which ip address?

Once you start Oracle listener service, you may not know which ip the listener will receive a connection.

if you configure HOSTNAME as host address, the listener will listener on all interface.

if you want to listen on a specific ip address, you need to replace the hostname to an ip address.

Linux找出使用交換空間(swap space) 的進程( process)

2019-03-20T21:31:00.001-07:00

最近資料庫有大量使用cpu / swap的現象，為了查找是否除了 Oracle 資料庫外，還有別的程序使用SWAP，因此在網路上找到這篇文章

https://www.cyberciti.biz/faq/linux-which-process-is-using-swap/

這篇文章有介紹幾個指令文後有一段小的Schell Script，可以用來快速找出佔用SWAP的前幾名

## Get swap space in Linux using bash for loop ##

for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done | sort -k 2 -n -r | less

等下次再發生時，可以再抓取相關資訊

Windows 10 無法執行 SQL Developer (unable launch java virtual machine jvm.dll)

2016-06-11T22:18:00.004-07:00

今天在新安裝的Windows 10中，運行SQL Developer 在下載了SQL Developer bundle JDK

發生了以下的問題==>, 無法找到 jvm.dll

然而在路徑上尋找此檔案是有存在的。

雖然ORACLE官方網站有相關的說明，但是仍無法明暸如何解決，因此到網路上找尋方法。

原來是因為Windows 並無放入 MSVCR100.dll

到 SQLDEVELOPER目錄中 /sqldeveloper/jdk/jre/bin中，將MSVCO100.dll 複製至\windows\system32中，即可以正常運行了。

Oracle emcli setup failed on Windows (無法建立與目前OMS的連線，請查看日誌檔瞭解進一步的詳細資訊)(Error: Connection to the current OMS could not be established. Check the log files for further details.)

2015-04-15T23:41:00.000-07:00

今天嘗試在 Windows 安裝 Cloud Control Agent

1、環境：
OMS

OS: Linux 6.4
Cloud Control: 12c R3

Client
OS: Windows 7
JRE: 1.8

2、步驟：

下載安裝請參考：emcli http://docs.oracle.com/cd/E24628_01/em.121/e17786/deploy.htm#EMCLI117
安裝過程很簡單 ==> 需要先安裝JAVA，再執行以下的安裝步驟
java -jar emclikit.jar -install_dir=c:\Agent
設定OMS連線時，發生無法連線OMS的錯誤

這是遇到了JRE 1.8 Client 與 Oracle Http Server 在SSL 上認證的BUG Doc ID 1675108.1

下載Patch p17555224_111160_Linux-x86-64.zip (記得要選111160)
停止 OMS，opatch apply ，啟動OMS
emctl stop oms -all
opatch all
emctl start oms
再度設定emcli 連線至OMS，就沒有錯誤了。

[DBA] 一句sql可能因為指定的內容，造成Full Table Scan & Index Scan. 20120319

2012-03-18T23:45:00.002-07:00

一句sql可能因為指定的內容，可能造成Full Table Scan or Index Scan.

今天在看sql的資料中，發現了這個有趣現象，供大家討論。

原sql為：

SQL> SELECT MAX (joblog0_.date_created) AS col_0_0_

FROM newsec.job_log joblog0_

WHERE joblog0_.job_name = :1 AND joblog0_.log_type = 'Finish';

其執行計劃：

看來是有用到index，cost也相當低

不過，我在系統的統計中發現一個問題

因此查詢一下此sql的歷史的執行計劃

結果同一個sql，有二種不同的執行結果，一個有採用到Index , 一個卻沒有用到index。

因此我好奇的查了一下table的資料，發現job_name 與 log_type 建立的index 都正常

由於job_name 為動態變數，而log_type 為固定值(Finish)，所以我以job_name的值帶入後發現，若以job_name='JOB_76'帶入，就會造成Full Table Scan。

但若以job_name='JOB_151'帶入，則又是正常的index scan

我們以job_name 來做資料的分類

會發現，job_name = 'JOB_76' 的資料量有高達26386筆，而job_150與job_148也是相當多筆。

故事還沒完

照道理來說，若JOB_76會full table scan的話，那比它多的應該也都是full table scan。

然而實際狀況並非如此。JOB_150有高達8萬多筆，比job_76的2萬多筆多出了三倍，居然是Index Scan。嚇死人了，見鬼了喲。

因此，我們再分析一下二個條件job_name & log_type='Finish'所造成的筆數內容。

JOB_76 JOB_148 JOB_150

13179 0 0

JOB_148 & JOB_150 都是0筆，怎麼會一個是Full Table Scan, 一個是Index Scan。

我猜想這大概跟統計值有關係。果不其然，在JOB_LOG這個table中，發現統計值過期了。

重新analyzed 過後，則都能用到Index 了。

Oracle Group by Without sort after 10g

2012-03-08T01:26:00.002-08:00

Create a temp table to exam the test;

SQL: create table test as select * from v$session;

Now, I Query the test table with group by , and no index add on the table.

SQL: select username,count(1) from test group by username ;

Oracle 9i the Explain is ..

Oracle 11g the Explain is ..

一隻抓取時間的procedure造成cpu拉高

2012-03-06T19:31:00.003-08:00

今天有機會看一個系統的狀況，解決的過程如下：

1、環境：
Oracle Rac 10.2.0.3 on Sun Solaris

2、問題：

＠程式碼：

CREATE OR REPLACE PROCEDURE proc_system_time(Sys_date_timeOUT char) IS
BEGIN
Sys_date_time := TO_CHAR(SYSDATE,'yyyymmddhh24miss');
END;
/

＠症狀：
cpu 升高

3、分析：
經由awr report檢查系統使用率，不論是memory的使用率，hard parse & hit ration都還不錯。

但在sql ordered by execution 中發現執行的次數在2小時間，有1534萬次，實在高得離譜。

懷疑有無窮迴圈產生。

SQL ordered by Executions

Total Executions: 16,500,018
Captured SQL account for 95.5% of Total

Executions	Rows Processed	Rows per Exec	SQL Id	SQL Module	SQL Text
15,344,168	15,337,933	1.00	8u74nmyn35a6f	w3wp.exe	BEGIN proc_system_time...
93,707	93,707	1.00	ftj9uawt4wwzb		select condition from cdef$ wh...
75,927	75,927	1.00	ckxd2nv9y5vyv	ACC.exe	Select count ( rowid ) From pa...
70,812	70,497	1.00	dq5p20xu978dn	ACC.exe	select inp_dec_rec_id from cha...
20,048	344,080	17.16	db78fxqxwxt7r		select /+ rule / bucket, en...
14,841	0	0.00	b2gnxm5z6r51n		lock table sys.col_usage$ in e...
12,845	10,905	0.85	96g93hntrzjtr		select /+ rule / bucket_cnt,...
7,240	1,000	0.14	0h6b2sajwb74n		select privilege#, level from ...
7,159	7,242	1.01	0k8522rmdzg4k		select privilege# from sysauth...
6,428	6,413	1.00	3c1kubcdjnppq		update sys.col_usage$ set eq...

4、解決：請使用者回去找開發人員，是否有異動程式。

一次在Oracle 11g 11.2.0.2 Rac 的patch記錄--Bug：10317487--ontrol file backup operation failed

2012-01-29T08:31:00.000-08:00

一次在Oracle 11g 11.2.0.2的patch記錄

Bug：10317487

Problem:

tag=TAG20120129T220012 comment=NONE

channel ch2: backup set complete, elapsed time: 00:35:59

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on ch4 channel at 01/29/2012 22:33:19

ORA-00245: control file backup operation failed

Action:

1、升級 OPatch

2、Patch

這邊值得一提的是，在有些patch中，是可以online上patch的。這不同我以往的經驗。Readme中寫到：

(2) Installation

-----------------

This section describes the following modes you can use to install the combo patch. Use the one that best suits your requirement.

-       Installing in Offline Mode

-       Installing in Online Mode

這讓我很爽，不用安排時間去停機，也不用一台一台的重開關的。

但，有些問題還是要知道的。

升級的程序中，寫了指令：

-       For RAC Environments:

               $ opatch apply online -connectString [SID_Node1]:[Username_Node1]:[Password_Node1]:[Node1_Name],[SID_Node2]:[Username_Node2]:[Password_Node2]:[Node2_Name],[SID_NodeN]:[Username_NodeN]:[Password_NodeN]:[NodeN_Name] [PATCH_TOP_DIR]/12679138

哇，又是sid，又是username ，又是password，還有一個叫node的。

沒關係我是dba，要什麼我都有。

/oracle/db/OPatch/opatch apply online -connectString MySID1:sys:[MyPassWd]:MyNode1,MySID2:sys:[MyPassWd]: MyNode2 /home/oracle/Patch/12679138

執行結果告訴我語法錯誤。切，我最恨這種狀況了。

查了一下optach apply –help 指令，有一個 –all_nodes可用，因此改了一下語法：

/oracle/db/OPatch/opatch apply online -all_nodes /home/oracle/Patch/12679138

Syntax Error... Unrecognized Option for Apply

○ 歐 ~ ＮＯ～

再試了一次

/oracle/db/OPatch/opatch apply online

The patch should be applied/rolled back in '-all_nodes' mode only.

Converting the RAC mode to '-all_nodes' mode.

Applying interim patch '12679138' to OH '/oracle/db'

Verifying environment and performing prerequisite checks...

Prerequisite check "CheckForInputValues" failed.

The details are:

Patch 12679138 :

Online patching requires -connectString option.

很好，查出這個patch要用-all_nodes這個參數…再往下看。

Oh~ no，還是一定要用-connectString來跑。

看來要騙它還要有點技巧。

不死心，再用一個opatch apply –help看一下參數用法。

-connectString

This option can be used to specify the list of database

instances on which the patch needs to be applied. The

value for this option is specified as per the following

syntax "SID:User:Passwd:Node". The SID is a must, others

can be ignored, OPatch takes default values for it.

Ex: oracle:dba:dba:mymachine,oracle1:::

NOTE: If the system is not part of RAC setup, then to

patch just the local node, provide the node name as

empty string.

看到了，我可以偷吃步了。可以省略username,password,node那真是太好了。

因此我把我的指令改為：

/oracle/db/OPatch/opatch apply online -connectString MySID1:::,MySID2:::

結果：

DB Instance "MySID2" is down or the given SID is invalid.

嘿，第一個ok了，看來第二個要給機器的名稱了。

補上後，就可以運作了。

附上結果

The patch should be applied/rolled back in '-all_nodes' mode only.

Converting the RAC mode to '-all_nodes' mode.

Applying interim patch '12679138' to OH '/oracle/db'

Verifying environment and performing prerequisite checks...

All checks passed.

Provide your email address to be informed of security issues, install and

initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit http://www.oracle.com/support/policies.html for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y

Backing up files...

Patching component oracle.rdbms, 11.2.0.2.0...

The patch will be installed on active database instances.

Installing and enabling the online patch 'bug12679138.pch', on database 'MYSID1'.

Patching in all-node mode.

Updating nodes 'MyNode02'

Apply-related files are:

FP = "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_files.txt"

DP = "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_dirs.txt"

MP = "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/make_cmds.txt"

RC = "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/remote_cmds.txt"

Instantiating the file "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_files.txt" with actual path.

Propagating files to remote nodes...

Instantiating the file "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/oracle/db/.patch_storage/12679138_Jun_21_2011_18_29_05/rac/copy_dirs.txt" with actual path.

Propagating directories to remote nodes...

Installing and enabling the online patch 'bug12679138.pch', on database 'MYSID2' on node 'MyNode02'.

Patch 12679138 successfully applied

Log file location: /oracle/db/cfgtoollogs/opatch/opatch2012-01-29_23-45-54PM.log

OPatch succeeded.

ora-01460 error when extract source code of procedures

2011-11-29T23:45:00.000-08:00

今天測試資料庫連線時，某程式出現以上錯誤

查原因是Client 端與　Server端的資料庫「字元集」不合

若各位未來遇到此問題，請修改Windows 註冊檔中nls_lang的設定

預設是：TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950

在新系統的設定是：AMERICAN_AMERICA.UTF8

修改後，程式重新執行後就可以生效。

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

2011-08-25T20:39:00.000-07:00

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!

2011-08-25T08:30:00.000-07:00

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.

ORACLE IAS WebCache can't startup

2011-08-09T23:00:00.000-07:00

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.

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

2011-08-08T00:45:00.000-07:00

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 有讀取權限，才能自動抓到。

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

2011-08-01T10:05:00.000-07:00

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 一些問題整理

2011-08-01T09:59:00.000-07:00

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 中執行

MySQL 轉 Oracle Join 的問題

2011-07-24T19:16:00.001-07:00

今天遇到一個 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 自行決定，省去指定的麻煩

rman 舊備份記錄清除

2011-06-25T12:00:00.000-07:00

最近新接手一套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

至此，問題終於解決了。

Standby Database for Read only Query problems.

2011-06-18T18:03:00.000-07:00

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都會產生錯誤。

2011-06-09T08:56:00.000-07:00

資料庫調效經驗

2011-06-09T08:48:00.000-07:00

今天的環境是：

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 檔沒用到，就解決了上述問題，下次再試試吧。

Use Pl/SQL to compare 2 tables

2011-05-19T05:49:00.000-07:00

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;
/

Startup Listener with password using Script

2011-05-06T07:58:00.000-07:00

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

Oracle GridControl agent upload error

2011-01-23T23:28:00.000-08:00

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.

Oracle 10g 10.2.0.5 Patch

2010-11-24T06:36:00.000-08:00

最近客戶一直要求將資料庫由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的修正

2010-10-31T08:02:00.000-07:00

今天安裝了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就找得到了。

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`