作業系統 OS 版本
$ uname -a
Linux erp.xxx.com 2.6.18-53.el5 #1 SMP Sat Nov 10 19:37:22 EST 2007 x86_64 x86_64 x86_64 GNU/Linux
查看 OS version: cat /proc/version
查看內核 version : uname -r
資料庫DB 版本
SQL> select * from v$version
BANNER
1 Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
2 PL/SQL Release 11.2.0.2.0 - Production
3 "CORE 11.2.0.2.0 Production"
4 TNS for Linux: Version 11.2.0.2.0 - Production
5 NLSRTL Version 11.2.0.2.0 - Production
在此 11.2.0.2.0 表示 主要發佈版本 + 主要發佈維護 + 應用服務器版本號 + 構件特定版本號 + 平台特定版本號
查看共享 memory: # df -k /dev/shm
查看 disk 容量: # df -k /tmp
查看 RAM SIZE: # grep MemTotal /proc/meminfo
查看 SWAP SIZE: # grep SwapTotal /proc/meminfo
查看系統剩餘 RAM 及 SWAP: # free
查看安裝甚麼軟體: # rpm -q package_name
2015年5月29日 星期五
【Oracle ERP Note 】Oracle 系統版本資訊
【Oracle ERP Note 】Oracle Database 用戶模式解開 Lock 步驟
1. 以 system 連接 db SQL> connect system /password 2. 解鎖用戶帳號，並修改其 login 密碼 SQL> alter user hr account unlock; SQL> alter user hr identified by hr; 3. 現可連接到 HR db，查看模式所含 table SQL> connect hr/hr SQL> select table_name from user_tables;
【Oracle ERP Note 】Restart Oracle EBS
Problem Oracle EBS 意外 shutdown 時，例如: 誤按 Ctl + Alt + Del 鍵，造成系統重新 Reboot Solution 1. 以 dba 帳號登入，下 SQL> startup ，啟動 DB 2. 以 dba 帳號登入，下 lsnrctl start ，啟動 DB Listener 3. 以 ap manager 帳號登入，下 adstrtall.sh ，啟動 AP 4. 以 ap manager 帳號登入，至 /usr/japps/apache.../bin ， 下 catalina.sh start ，啟動 Tomcat 5. 以 dba 帳號登入，下 emctl start dbconsole ， 啟動 OEM 6. 以 ap manager 帳號登入，下 adcmctl.sh start ，啟動 Concurrent Manager (AD Manger) 以上為非正常 shutdown 的啟動順序，若正常啟動時，因為之前是正常 shutdown，所以會先 enable maintenance mode，所以開機最後要記的將此 mode 改為 disable maintenance mode。
2015年5月28日 星期四
【Oracle ERP Note 】-bash: reboot: command not found
Problem 下指令更正 v$datafile 中 Table 的位置成功，但更正 v$tempfile 失敗 (尚有 v$logfile 也未更正)，系統出現下列錯誤訊息: # ls ls: reading directory .: Input/output error # reboot -bash: reboot: command not found Study 以 df -h 指令檢查硬碟空間，狀況正常。但，無法以 reboot 重啟伺服器。最後強迫以開關電源重開機時，系統出現 kernel panic - not syncing attempted to kill init 的錯誤訊息，經查看捲動訊息，發現有 Bad Sector 現象。 Solution 硬碟狀況不良，檢測硬碟壞軌位置並試著修復。修復後 Ghost 硬碟，之後換顆新硬碟。
2015年5月26日 星期二
【Oracle ERP Note 】Oracle Discoverer 不要顯示中文日期
Problem User 在Oracle Discoverer 上跑報表查詢時，日期顯示中文，User 期望改成顯示英文 Study User 在安裝 Oracle Discoveer 時，語系選擇繁體中文，所以 Discoverer 的 UI 是中文介面，而以 regedit 探詢機碼時，其設定如下: NLS_LANG = TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950 這機碼的顯示路徑為: HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > Oracle > KEY_OH1295xxx Solution 在這路徑下，新增下列機碼: NLS_DATE_LANGUAGE = ENGLISH 之後重新啟動 Discoverer，選取 資料 選單後，在彈跳的新視窗中，可看到新增的Tab 日期 頁面，可更改日期顯示的格式 Date Format。 機碼 NLS_LANG 用來顯示 Discoverer 的操作介面為中文、英文、或其他語系，而機碼 NLS_DATE_LANGUAGE 則用來設定顯示日期的語系。
2015年5月20日 星期三
【Oracle ERP Note 】Is you database is running in archive log mode ?
以 sqlplus，在 SQL> 環境下達指令 archive log list 即可確認 DB 的啟動是否是 Archive Log Mode。 SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/proddb/proddata/archive Oldest online log sequence 2355 Next log sequence to archive 2356 Current log sequence 2356 SQL> Reference 此解決方法完全參考 [1] 1. https://support.ca.com/cadocs/0/CA%20ARCserve%20%20Backup%20r16-ENU/Bookshelf_Files/HTML/oraclewn/index.htm?toc.htm?ow_check_archivelog_mode.htm
2015年5月18日 星期一
Kill Apache server and get error, "httpd dead but subsys locked"
Problem 當使用 apachectl stop 去 shutdown 此 Apache server 時，再以 service httpd status 檢視時，系統出現錯誤訊息: httpd dead but subsys locked Study 剛開始研判，由網路搜尋解決方法時，會誤判 Disk 容量不夠，需清理。但， /u01 (或 /ora03) 仍有 103M ，應該足夠# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 38G 4.5G 32G 13% / /dev/sda1 99M 16M 79M 17% /boot tmpfs 4.0G 0 4.0G 0% /dev/shm /dev/mapper/VolGroup00-LogVol02 40G 22G 16G 58% /ora01 /dev/mapper/VolGroup00-LogVol03 5.0G 631M 4.1G 14% /misap /dev/mapper/VolGroup00-LogVol05 9.9G 1.4G 8.0G 15% /var /dev/mapper/VolGroup01-LogVol05 352G 337G 103M 100% /ora03 /dev/mapper/VolGroup01-LogVol05 352G 337G 103M 100% /u01但，事實上參考 [1] 時，問題可獲解決。 Solution 1. 更改 /etc/httpd/conf/httpd.conf 檔案# cd /etc/httpd/conf # vi httpd.conf # # PidFile: The file in which the server should record its process # identification number when it starts. # #PidFile run/httpd.pid PidFile /var/run/httpd.pid <== 修改後2. 更改 /etc/sysconfig/httpd 檔案# cd /etc/sysconfig/ # vi httpd # Configuration file for the httpd service. # # The default processing model (MPM) is the process-based # 'prefork' model. A thread-based model, 'worker', is also # available, but does not work with some modules (such as PHP). # The service must be stopped before changing this variable. # #HTTPD=/usr/sbin/httpd.worker # # To pass additional options (for instance, -D definitions) to the # httpd binary at startup, set OPTIONS here. # #OPTIONS= # # By default, the httpd process is started in the C locale; to # change the locale in which the server runs, the HTTPD_LANG # variable can be set. # #HTTPD_LANG=C PIDFILE=/var/run/httpd.pid <== 新增3. 確認所有 Apache Server 都清除# killall -9 httpd httpd: no process killed4. 將被鎖住的檔案 httpd 刪除# cd /var/lock/subsys # ls acpid cups hpssd.py network smartd xinetd atd gpm httpd nfslock smb yum-updatesd auditd haldaemon kudzu pcscd sm-client autofs hcid local portmap sshd avahi-daemon hidd messagebus rpcidmapd syslog bluetooth hpiod microcode_ctl sdpd winbindd crond hplip netfs sendmail xfs # rm httpd rm: remove regular empty file `httpd'? y5. 重啟 Apache server# service httpd restart Stopping httpd: [FAILED] Starting httpd: [ OK ]6. 確認 Apache server 是否啟動# service httpd status httpd (pid 5328 5327 5326 5325 5324 5323 5322 5321 5319) is running...Reference 1. http://serverfault.com/questions/560282/still-httpd-dead-but-subsys-locked-even-following-the-fix-of-some-source (1) Had to change httpd.config in /etc/httpd/conf/ from: PidFile run/httpd.pid to: PidFile /var/run/httpd.pid (2) Also, changed httpd file in /etc/sysconfig/ from: PIDFILE=/var/run/httpd/httpd.pid to: PIDFILE=/var/run/httpd.pid (3) Run the following comand: killall -9 httpd (4) then, to remove httpd lock: rm -f /var/lock/subsys/httpd (5) And restarted Apache: service httpd restart 2. http://www.tecmint.com/how-to-check-disk-space-in-linux/
2015年5月14日 星期四
【Oracle ERP Note 】查詢系統已上那些 Patch
除了使用類似指令 select * from ad_bugs where bug_number = "<patch_number>" 外，Oracle EBS 亦提供工具查詢系統已上甚麼 Patch。 首先以 Super User 登入 Oracle EBS，然後點選下列路徑 System Administration > Oracle Application Manager > Patching and Utilities ，此時系統出現下列畫面:輸入查詢條件，點選 Go 執行後，系統會列出搜尋結果:在搜尋結果的每列最後有 Detail 按鈕，提供此 Patch 更詳細的資訊；當然，搜尋方式也可使用 Advanced Search 此按鈕來限制搜尋的範圍:
【Oracle ERP Note 】查詢系統平台版本
以 SELECT * FROM V$VERSION 查詢Oracle DB 版本 / PL/SQL 版本 / TNS 版本 等如下:SQL> SELECT * FROM V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production只查看 DB Release:SQL> show release release 1102000200待續
2015年5月13日 星期三
【Oracle ERP Note 】ARC3: Error 19502 Closing archive log file
Problem 較早之前運作正常的測試區，突然 User 反映無法連線。 Study 查看 alert_TEST.log 時，發現有 Thread 1 cannot allocate new log, sequence 2320 這錯誤訊息，所以可先合理懷疑磁碟空間是否不足。ARC3: Error 19502 Closing archive log file '/u01/proddb/proddata/archive/1_2318_852393435.dbf' ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance TEST - Archival Error ORA-16038: log 9 sequence# 2318 cannot be archived ORA-19502: write error on file "", block number (block size=) ORA-00312: online log 9 thread 1: '/u01/proddb/proddata/redo/log09b.dbf' Tue May 12 22:06:21 2015 ARCH: Archival stopped, error occurred. Will continue retrying ORACLE Instance TEST - Archival Error ORA-16014: log 9 sequence# 2318 not archived, no available destinations ORA-00312: online log 9 thread 1: '/u01/proddb/proddata/redo/log09b.dbf' Tue May 12 22:06:56 2015 Thread 1 cannot allocate new log, sequence 2320 Checkpoint not complete Current log# 8 seq# 2319 mem# 0: /u01/proddb/proddata/redo/log08b.dbf經以 df -h 指令查看硬碟空間，發現 Oracle EBS 安裝所在的 /ora03 沒有空間，所以我們可清除此空間。 (事實上， /ora03 與 /u01 指向同一個硬碟空間)# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 38G 4.5G 32G 13% / /dev/sda1 99M 16M 79M 17% /boot tmpfs 4.0G 3.3M 3.9G 1% /dev/shm /dev/mapper/VolGroup00-LogVol02 40G 21G 17G 55% /ora01 /dev/mapper/VolGroup00-LogVol03 5.0G 631M 4.1G 14% /misap /dev/mapper/VolGroup00-LogVol05 9.9G 1.4G 8.0G 15% /var /dev/mapper/VolGroup01-LogVol05 352G 338G 31M 100% /ora03 /dev/mapper/VolGroup01-LogVol05 352G 338G 31M 100% /u01Solution 我們清除 /ora03 的硬碟空間後，查看清除結果如下，之後重新以 IE Client 連線登入，此時系統恢復正常。# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 38G 4.5G 32G 13% / /dev/sda1 99M 16M 79M 17% /boot tmpfs 4.0G 3.3M 3.9G 1% /dev/shm /dev/mapper/VolGroup00-LogVol02 40G 21G 17G 56% /ora01 /dev/mapper/VolGroup00-LogVol0ˇ 5.0G 631M 4.1G 14% /misap /dev/mapper/VolGroup00-LogVol05 9.9G 1.4G 8.0G 15% /var /dev/mapper/VolGroup01-LogVol05 352G 337G 357M 100% /ora03 /dev/mapper/VolGroup01-LogVol05 352G 337G 357M 100% /u01
2015年5月11日 星期一
2015年5月10日 星期日
【Oracle ERP Note 】查看 alert_PROD.log 時候，出現錯誤訊息
Problem
查看 alert_PROD.log 時候，出現下列錯誤訊息:
E325: ATTENTION
Found a swap file by the name ".alert_PROD.log.swp"
owned by: proddba dated: Mon May 11 09:21:40 2015
file name: /u01/app/diag/rdbms/prod/PROD/trace/alert_PROD.log
modified: no
user name: proddba host name: erp
process ID: 28769
While opening file "alert_PROD.log"
dated: Mon May 11 11:14:27 2015
NEWER than swap file!
(1) Another program may be editing the same file.
If this is the case, be careful not to end up with two
different instances of the same file when making changes.
Quit, or continue with caution.
(2) An edit session for this file crashed.
If this is the case, use ":recover" or "vim -r alert_PROD.log"
to recover the changes (see ":help recovery").
If you did this already, delete the swap file ".alert_PROD.log.swp"
to avoid this message.
Swap file ".alert_PROD.log.swp" already exists!
[O]pen Read-Only, (E)dit anyway, (R)ecover, (D)elete it, (Q)uit, (A)bort:
Study
此現象與用 vi alert_PROD.log 時，未結束此 Processs 就關機，而發生此異常現象。
Solution
若只是查看，直接刪除此 swap 檔案 .alert_PROD.log.swp 即可。
2015年5月8日 星期五
【Oracle ERP Note 】WARNING: ORA-12541: TNS:no listener
Problem 使用 emca -config dbcontrol db 命令來建立 Database Control 時，出現 WARNING: ORA-12541: TNS:no listener 訊息 Study 以 lsnrctl status 命令檢查 TNS Listener 狀態，事實上此 listner 並沒被啟動。[11.2.2]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-MAY-2015 17:57:31 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refusedSolution 很簡單，只要下 lsnrctl start 指令啟動此 TNS Listener 即可。[11.2.2]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 08-MAY-2015 17:57:51 Copyright (c) 1991, 2010, Oracle. All rights reserved. Starting /u01/proddb/proddb/11.2.2/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.2.0 - Production System parameter file is /u01/proddb/proddb/11.2.2/network/admin/TEST_srv/listener.ora Log messages written to /u01/proddb/proddb/11.2.2/log/diag/tnslsnr/srv/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv.xxx.com)(PORT=1521))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production Start Date 08-MAY-2015 17:57:52 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/proddb/proddb/11.2.2/network/admin/TEST_srv/listener.ora Listener Log File /u01/proddb/proddb/11.2.2/log/diag/tnslsnr/srv/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv.xxx.com)(PORT=1521))) The listener supports no services The command completed successfullyReference 1. Oracle listener lsnrctl tips, http://www.dba-oracle.com/tips_oracle_lsnrctl.htm
2015年5月6日 星期三
【Oracle ERP Note 】Tablespace [SYSTEM] is [91.45 percent] full
Problem The tablespace, SYSTEM, at the production node is almost 91.45 percent full. How could I clean up the redundant data? Fig. 1:Fig. 2:Study 此類表 SYSTEM 與 SYSAUX [Oracle 10.g 版本後支援] 作為 DBMS 管理使用，勿手動刪除其表單中任一 Record。但，對應此 Tablespace 的實體檔案儲存容量快不足夠時，又該如何處理? 依據 Hussein Sawwan 的說法，他提到: 1. If there is no more free space available in this specific datafile and you want it to stop growing. Then you have to turn autoextend off. 2. If autoextend is off on one or more tablespace(s) and it is on for one datafiel that belongs to that specific tablespace, the database will write data to that datafile. Solution 可選擇下列其一來解決，選擇任一並無 Performance Issue。Option1: ALTER DATABASE DATAFILE 'Reference 1. ALTER TABLESPACE, http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3002.htm#SQLRF01002 2. Tablespace check script for Oracle E-Business Suite R11&R12, http://heliosguneserol.com/2010/06/11/tablespace-check-script-for-oracle-e-business-suite-r11r12/ 3. OEM Tablespace Space Used % Alerts and the AUTOEXTEND effect, http://gavinsoorma.com/2011/11/oem-tablespace-space-used-alerts-and-the-autoextend-effect/' RESIZE [K|M|G]; Option2: ALTER TABLESPACE ... ADD DATAFILE;
2015年5月4日 星期一
【Oracle ERP Note 】Oracle ERP Cleint 連線時，畫面呈現空白
現象: Oracle ERP Client 連線時，畫面呈現空白。檢查連線，Session Connection仍存在。 調查: 1. 找出 Alert Log 1.1以 proddba 登入 1.2 $ cd $ORACLE_HOME/dbs 1.3 $ locate alert_PROD.log 找出 Alert Log 路徑為/u01/app/diag/rdbms/prod/PROD/trace/alert_PROD.log 2. 檢查 Alert Log 輸入 $ tail -f alert_PROD.log 此時系統告知 ICX Table 容量不足，需擴充ORA-1654: unable to extend index ICX.ICX_SESSIONS_N1 by 16 in tablespace ICXX ORA-1654: unable to extend index ICX.ICX_SESSIONS_N1 by 16 in tablespace ICXX ORA-1654: unable to extend index ICX.ICX_SESSIONS_N1 by 16 in tablespace ICXX Mon May 04 14:54:16 2015 ORA-1654: unable to extend index ICX.ICX_SESSIONS_N1 by 16 in tablespace ICXX解決方式: 以Oracle OEM 擴充 ICX Table容量如下圖所示:或直接以下列指令擴充 ICX 容量: ALTER TABLESPACE "ICXX" ADD DATAFILE '/u01/proddb/proddata/icxx02.dbf' SIZE 100M 檢討: 1. 此現象發生而在調查原因時，曾發生shutdown AP server後，本機可連線，但其他 User 仍然無法連線，畫面空白，而懷疑 Http Server 有問題 2. 事實上，AP Shutdown / Startup 其 script 會自動 shutdown / startup Apache Server，此 Server 的路徑與 Red Hat Linux 安裝路徑無關 (Oracle ERP 自行定義包裝 Apache，有其自訂路徑) 3. 當初以 Oracle OEM 查看 ICX 容量並有 FULL 狀況，造成誤判，而未再查看 Alert Log，找出原因 後續: 1. ICX Table 紀錄 User Connection的連線紀錄，需定期清除 2. 檢查 Oracle Table 使用現況，預防後續類似事件發生 進階參考 1. What is the Relationship Between the ICX_SESSIONS Table and the FND_LOGINS Table? (Doc ID 358823.1), https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=238337641632646&id=358823.1&_afrWindowMode=0&_adf.ctrl-state=1warpixej_4 2. Overview Of ORA-01654: Unable To Extend Index %s.%s By %s In Tablespace %s (Doc ID 146595.1), Overview Of ORA-01654: Unable To Extend Index %s.%s By %s In Tablespace %s (Doc ID 146595.1)
訂閱：
文章 (Atom)