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

今天在新安裝的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.)

今天嘗試在 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

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

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

一句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

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拉高

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

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

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

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

一次在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

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

查原因是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

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

do

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"

fi

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

opmnctl status



Processes in Instance: XXX
-------------------+--------------------+---------+---------
ias-component      | process-type       |     pid | status  
-------------------+--------------------+---------+---------
DSA                | DSA                |     N/A | Down    
HTTP_Server        | HTTP_Server        |   11939 | Alive   
LogLoader          | logloaderd         |     N/A | Down    
dcm-daemon         | dcm-daemon         |   13132 | Alive   
OC4J               | home               |   11940 | Alive   
OC4J               | aaOC4J            |   11950 | Alive   
OC4J               | bbOC4J            |   11951 | Alive   
OC4J               | ccOC4J            |   11952 | Alive   
OC4J               | ddOC4J             |   11955 | Alive   
OC4J               | eeOC4J            |   11962 | Alive   
OC4J               | ffOC4J            |   11963 | Alive   
OC4J               | ggOC4J            |   11965 | Alive   
OC4J               | hhOC4J            |   11978 | Alive   
WebCache           | WebCache           |     N/A | Down    
WebCache           | WebCacheAdmin      |     N/A | Down   


$ORACLE_HOME/webcacahe/logs/event_log
..
[alert 9526] [ecid: -] Permission denied when setting group ID ( oinstall )
[alert 13003] [ecid: -] Configuration error. Stopping admin or cache server.


At Doc ID 464198.1

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)

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

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