1. login as ORACLE_HOME dba 2. command: $ORACLE_HOME/OPatch/opatch lsinventory[dba@dev OPatch]$ cd $ORACLE_HOME/OPatch [dba@dev OPatch]$ ./opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/proddb/proddb/11.2.2 Central Inventory : /etc/oraInventory from : /u01/proddb/proddb/11.2.2/oraInst.loc OPatch version : 11.2.0.3.0 OUI version : 11.2.0.2.0 Log file location : /u01/proddb/proddb/11.2.2/cfgtoollogs/opatch/opatch2014-11-13_11-50-21AM_1.log Lsinventory Output file location : /u01/proddb/proddb/11.2.2/cfgtoollogs/opatch/lsinv/lsinventory2014-11-13_11-50-21AM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.2.0 There are 1 products installed in this Oracle Home. Interim patches (2) : Patch 10160615 : applied on Fri Apr 05 00:09:38 CST 2013 Unique Patch ID: 13348800 Created on 17 Jan 2011, 05:06:03 hrs PST8PDT Bugs fixed: 10160615 Patch 10149223 : applied on Fri Apr 05 00:08:03 CST 2013 Unique Patch ID: 13073567 Created on 25 Oct 2010, 03:35:14 hrs PST8PDT Bugs fixed: 10149223 -------------------------------------------------------------------------------- OPatch succeeded.To know what db patches the DB server has applied，just look up table, registry$history . For example:time action name ver ID COMMENTS BUNDLE_SERIES ------------------------------ ---------- ------- ---------- ---------- ------------------------ --- 17-OCT-09 09.51.47.000000 PM UPGRADE SERVER 10.2.0.1.0 Upgraded from 9.2.0.8.0 18-OCT-09 12.39.34.000000 AM UPGRADE SERVER 10.2.0.4.0 Upgraded from 10.2.0.1.0 25-OCT-09 07.43.22.000000 PM APPLY SERVER 10.2.0.4 2 PSU 10.2.0.4.2 PSU 25-OCT-09 07.59.46.000000 PM CPU 6452863 view recompilation 21-MAR-10 09.42.19.000000 AM APPLY SERVER 10.2.0.4 3 PSU 10.2.0.4.3 PSU 21-MAR-10 09.44.01.000000 AM CPU 6452863 view recompilation 13-JUN-10 10.35.37.000000 AM APPLY SERVER 10.2.0.4 4 PSU 10.2.0.4.4 PSU 13-JUN-10 10.41.06.000000 AM CPU 6452863 view recompilation 04-APR-13 09.36.51.293731 PM VIEW INVAL 8289601 view invalidation IDATE 04-APR-13 09.36.51.781277 PM UPGRADE SERVER 11.2.0.2.0 Upgraded from 10.2.0.4.0 04-APR-13 09.38.39.183463 PM APPLY SERVER 11.2.0.2 0 Patchset 11.2.0.2.0 PSU 11 rows selected.事實上，Oracle 也提供一個非常簡單驗證上 Patch 是否成功的方式，即是檢查 AD_BUGS 這表，檢查方式如下: SELECT * FROM AD_BUGS WHERE BUG_NUMBER = <patch_number> 參考 1. OPatch Utility and Commands, https://docs.oracle.com/cd/B16240_01/doc/em.102/e15294/options.htm
2014年11月12日 星期三
【Oracle 筆記 】Find What Patches Have Been Applied to Oracle Database
【Oracle 筆記 】OPatch Patch 6880880: UnsupportedClassVersionError
現象:
Oracle DB version 11.2.0.2.0， 安裝 OPatch Patch 6880880 for OPatch 11.2.0.3.6 (p6880880_112000_LINUX-x86-64.zip) 後，驗證安裝時，發現下列錯誤:
[mgr@dev OPatch]$ ./opatch version
Exception in thread "main" java.lang.UnsupportedClassVersionError: oracle/opatch/OPatch (Unsupported major.minor version 49.0)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:539)
at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:123)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:251)
at java.net.URLClassLoader.access$100(URLClassLoader.java:55)
at java.net.URLClassLoader$1.run(URLClassLoader.java:194)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:187)
at java.lang.ClassLoader.loadClass(ClassLoader.java:289)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:274)
at java.lang.ClassLoader.loadClass(ClassLoader.java:235)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:302)
OPatch failed with error code 1
Review:
查看 oraparam.ini 的 OUI Version 時 ，發現 OUI_VERSION=11.2.0.2.0，而 Patch 6880880 OPatch 11.2.0.3.6 也支援 OUI v 11.2.*，但為何解壓縮後，無法使用?
$ vi /u01/proddb/proddb/11.2.2/oui/oraparam.ini
以 java -version 查看support 的版本為 build 1.6.0_10-b33，表面看起來此OPatch 11.2.0.3.6 官方文件也說明只需要 JDK / JRE v. 1.5.0 或更高版本，即能運作正常，然而實際上仍然報錯。
這此找向下相容版本 p6880880_111000_LINUX-x86-64.zip，安裝後驗證仍然報錯。問題在那裏?
接著試著從 README 檔案這句話 The OUI version must be 11.2.* 著手，找出目前環境下 OUI 安裝的版本，。發現 OUI Version 為 10.1.0.6.0。所以重新下載，選定 p6880880_111000_LINUX-x86-64.zip 這版本安裝。安裝完後驗證:
[mgr@dev OPatch]$ opatch version
Oracle Interim Patch Installer version 1.0.0.0.64
Copyright (c) 2011 Oracle Corporation. All Rights Reserved..
Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)
OPatch Version: 1.0.0.0.64
結論:
1. p6880880 官方文件有誤導: p6880880_112000_LINUX-x86-64.zip 官方說明支援的 platform 包含 Oracle 11.2.0.2.0 ，但事實並非如此。
2. 造成此現象的原因有可能 Oracle DB 由 10g 升級，而 OUI version 為 10.1.0.6.0
3. 此新版本的 OPatch 由下列 help 中與舊的相對照，可看出提供許多新功能:
注意:
Oracle AP server 與 DB server 各有 OPatch 工具。 Oracle AP server 的 OPatch 是以 AP 帳號登入，位置在 $ORACLE_HOME/OPatch 之下，而 Oracle DB server 的 OPatch 是以 DB 帳號登入，位置同樣也在 $ORACLE_HOME/OPatch 之下。此次原因發生於上 AP sever 的 Patch，而非上 Oracle DB server 的 Patch。
新的 OPatch:
[mgr@dev OPatch]$ opatch -h
Oracle Interim Patch Installer version 1.0.0.0.64
Copyright (c) 2011 Oracle Corporation. All Rights Reserved..
Oracle recommends you to use the latest OPatch version
and read the OPatch documentation available in the OPatch/docs
directory for usage. For information about the latest OPatch and
other support-related issues, refer to document ID 293369.1
available on My Oracle Support (https://myoraclesupport.oracle.com)
Usage: opatch [ -h[elp] ] [ -r[eport] ] [ command ]
command := apply
lsinventory
napply
nrollback
query
rollback
util
version
:= -help Displays the help message for the command.
-report Print the actions without executing.
舊的 OPatch:
[mgr@dev OPatch]$ opatch -h
Oracle Interim Patch Installer version 1.0.0.0.58
Copyright (c) 2007 Oracle Corporation. All Rights Reserved..
We recommend you refer to the OPatch documentation under
OPatch/docs for usage reference. We also recommend using
the latest OPatch version. For the latest OPatch version
and other support related issues, please refer to document
293369.1 which is viewable from metalink.oracle.com
Usage: opatch [ -h[elp] ] [ -r[eport] ] [ command ]
command := apply
lsinventory
query
rollback
version
:= -help Displays the help message for the command.
-report Print the actions without executing.
參考:
1. https://updates.oracle.com/download/6880880.html
2. https://docs.oracle.com/cd/B16240_01/doc/em.102/e15294/options.htm
2014年11月11日 星期二
【Oracle 筆記 】The system has not been taken off maintenance mode completely.
現象 重新啟動 Oracle EBS 時候，系統無法連結，出現下列錯誤訊息: Error Message: The system has not been taken off maintenance mode completely. Please contact your System Administrator事實上，在整個上 patch 的過程中，完全依照下列順序，為何還會有錯誤發生呢? 1. enable Maintenance mode 2. adstpall.sh 3. shut down database 4. apply patch 5. startup database 6. adstrtal.sh 7. disable Maintenance mode 檢查 1. 以 apps 帳號，由 DB 查詢 APPS_MAINTENANCE_MODE 的 status:[dba@tw]$ sqlplus apps/xxxxxx SQL*Plus: Release 11.2.0.2.0 Production on Wed Nov 12 10:59:12 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual; FND_PROFILE.VALUE('APPS_MAINTENANCE_MODE') -------------------------------------------------------------------------------- NORMAL2. 採用 Bounce Apache Server 建議 首先，確認 Maintenance Mode 是 Disabled，之後依序下列順序操作:$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop wait for 30 seconds $COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start/u01/apps/PROD/inst/apps/PROD_xxxx-xxx-erp/admin/scripts/adapcctl.sh
or $INST_TOP/admin/scripts/adapcctl.sh
結論
事實上，在 Bounce Apache Server 之後，連結 Oracle EBS 仍有此錯誤訊息，但重新執行下列作業後，系統才恢復正常運作:
1. enable Maintenance mode ( adadmin )
2. shutdown AP server ( adstpall.sh apps/xxxxxx )
3. startup AP server ( adstrtal.sh apps/xxxxxx )
4. disable Maintenance mode ( adadmin )
參考
1. Oracle Applications Maintenance Procedures
http://download.oracle.com/docs/cd/B53825_03/current/acrobat/121admp.pdf
2. How to Stop and Start (Bounce) the Apache Server [ID 204218.1]
***Checked for relevance on 16-Apr-2013***
goal: How to stop and start (bounce) the apache server
fact: Oracle Application Object Library 11.5.10.2 and later
fact: Oracle HTTP Server Powered by Apache 1.3+
fact: Oracle Human Resources 11.5.10.2 and later
fact: Oracle Self Service Human Resources 11.5.10.2 and later
fix:
Log in to the httpd server
cd $SCRIPTS
type
adapcctl.sh stop
Wait 30 seconds
Type
adapcctl.sh start
Apache Server has been bounced.
2014年11月10日 星期一
【Oracle 筆記 】Oracle Date Format - RR ?
在DB參數設定檔，發現 nls_date_format 被設定為 'DD-MON-RR'，而不是 'DD-MM-YY' 之類。查看 Oracle 官方網站 [1]時，不得不驚訝這是當初千禧年蟲蟲危機所遺留下來的毒害。若日期為 '1-JAN-00' 那代表的是那一年? 1900 年 或是 2000 年? 在這之前 coding 儘量節省欄位，年份只保留後 2 位數字並不為過。然而，沒想到恐龍商業軟體仍然存在 (尤其是 COBOL 商業軟體)，在堪用與好用的考量下，除掉這蟲蟲成了 2000 年前的大事。這也是當年程式設計者始料未及。參考 1. OLAP DML REFERENCE, DATE_FORMAT, https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm#OLADM780
2014年11月9日 星期日
【Oracle 筆記 】語系支援 Language Support
使用者連上DB之後，查看目前的NLS參數設定 不同使用者會因設定不同，而看到不同的內容。SQL> select * from nls_session_parameters PARAMETER VALUE 1 NLS_LANGUAGE AMERICAN 2 NLS_TERRITORY AMERICA 3 NLS_CURRENCY $ 4 NLS_ISO_CURRENCY AMERICA 5 NLS_NUMERIC_CHARACTERS ., 6 NLS_CALENDAR GREGORIAN 7 NLS_DATE_FORMAT DD-MON-RR 8 NLS_DATE_LANGUAGE AMERICAN 9 NLS_SORT BINARY 10 NLS_TIME_FORMAT HH.MI.SSXFF AM 11 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 12 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 13 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 14 NLS_DUAL_CURRENCY $ 15 NLS_COMP BINARY 16 NLS_LENGTH_SEMANTICS BYTE 17 NLS_NCHAR_CONV_EXCP FALSE查看目前 Oracle Instance NLS 參數設定:SQL> select * from nls_instance_parameters PARAMETER VALUE 1 NLS_LANGUAGE AMERICAN 2 NLS_TERRITORY america 3 NLS_SORT binary 4 NLS_DATE_LANGUAGE 5 NLS_DATE_FORMAT DD-MON-RR 6 NLS_CURRENCY 7 NLS_NUMERIC_CHARACTERS ., 8 NLS_ISO_CURRENCY 9 NLS_CALENDAR 10 NLS_TIME_FORMAT 11 NLS_TIMESTAMP_FORMAT 12 NLS_TIME_TZ_FORMAT 13 NLS_TIMESTAMP_TZ_FORMAT 14 NLS_DUAL_CURRENCY 15 NLS_COMP binary 16 NLS_LENGTH_SEMANTICS BYTE 17 NLS_NCHAR_CONV_EXCP FALSE查看目前 Oracle Database NLS 參數設定:SQL> select * from nls_database_parameters PARAMETER VALUE 1 NLS_NCHAR_CHARACTERSET AL16UTF16 2 NLS_LANGUAGE AMERICAN 3 NLS_TERRITORY AMERICA 4 NLS_CURRENCY $ 5 NLS_ISO_CURRENCY AMERICA 6 NLS_NUMERIC_CHARACTERS ., 7 NLS_CHARACTERSET UTF8 8 NLS_CALENDAR GREGORIAN 9 NLS_DATE_FORMAT DD-MON-RR 10 NLS_DATE_LANGUAGE AMERICAN 11 NLS_SORT BINARY 12 NLS_TIME_FORMAT HH.MI.SSXFF AM 13 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 14 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 15 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 16 NLS_DUAL_CURRENCY $ 17 NLS_COMP BINARY 18 NLS_LENGTH_SEMANTICS BYTE 19 NLS_NCHAR_CONV_EXCP FALSE 20 NLS_RDBMS_VERSION 11.2.0.2.0DBA 亦可使用 show parameter nls 指令查看 Database NLS 的設定。 若要修改 NLS 參數設定，則要先查詢 Oracle 提供那些參數的設定值。這些設定值可以從 Data Dictionary中的 v$nls_valid_valuses 這 View 得知。SQL> select * from v$nls_valid_values where value in ('AL16UTF16','AMERICAN','AMERICA','UTF8'); PARAMETER VALUE ISDEPRECATED 1 LANGUAGE AMERICAN FALSE 2 TERRITORY AMERICA FALSE 3 CHARACTERSET UTF8 FALSE 4 CHARACTERSET AL16UTF16 FALSE修改 NLS 參數設定SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'; PARAMETER VALUE 1 NLS_DATE_FORMAT DD-MON-RR修改 nls_data_format 的參數設定:SQL> alter session set nls_date_format = 'MM/DD/YY';確認修改是否成功:SQL> select * from nls_session_parameters; PARAMETER VALUE 1 NLS_LANGUAGE AMERICAN 2 NLS_TERRITORY AMERICA 3 NLS_CURRENCY $ 4 NLS_ISO_CURRENCY AMERICA 5 NLS_NUMERIC_CHARACTERS ., 6 NLS_CALENDAR GREGORIAN 7 NLS_DATE_FORMAT MM/DD/YY 8 NLS_DATE_LANGUAGE AMERICAN 9 NLS_SORT BINARY 10 NLS_TIME_FORMAT HH.MI.SSXFF AM 11 NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 12 NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 13 NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 14 NLS_DUAL_CURRENCY $ 15 NLS_COMP BINARY 16 NLS_LENGTH_SEMANTICS BYTE 17 NLS_NCHAR_CONV_EXCP FALSE
2014年11月2日 星期日
【Oracle 筆記 】Client / DB server connect 設定
Oracle DB 的 client / server connection 的設定是最基本的，兩端設定都要一致才能溝通。例如: 在 Server 端找出設定值:[test@tw-srv-ora admin]$ echo $INST_TOP /u01/apps/TEST/inst/apps/TEST_tw-srv-ora [test@tw-srv-ora admin]$ cd $INST_TOP/ora/10.1.2/network/admin [test@tw-srv-ora admin]$ ls listener.ora tnsnames.ora TEST= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=tw-srv-ora.elvismeng.com.tw)(PORT=1522)) (CONNECT_DATA= (SERVICE_NAME=TEST) (INSTANCE_NAME=TEST) ) )在 client 端，將此設定值 copy 至 tnsnames.ora 即可。此檔案放在 C:\orant\net80\admin 下。 設定後，可用Oracle 附送的工具 PL PLus 80，或是 PL/SQL Developer 做連結測試。測試方式如下:User Name : (Oracle DB 帳號) Password : (Oracle DB 密碼) Host String: (Oracle Instance SID, 例如: TEST)
訂閱：
文章 (Atom)