How To Start OSWatcher Black Box (OSWBB) Every System Boot

APPLIES TO:

Reference from Oracle Doc ID 580513.1
Linux OS - Version Oracle Linux 4.6 to Oracle Linux 6.3 [Release OL4U6 to OL6U3]
Oracle VM - Version 2.1 to 3.1.1 [Release OVM21 to OVM31]
Information in this document applies to any platform.

GOAL

Oracle support often recommends that the OSWatcher Black Box(*) tool be run for an extended period.  Should the system reboot during this time, the system administrator must manually restart the OSWatche Black Boxr, and allow it to run until the necessary data have been collected.

* NOTE:

Depending on the version you have, the OSWatcher Black Box tool may also be referred to as simply OSWatcher; the nomenclature has been changed.

To automate this procedure, a simple shell script can be used.  Care must be taken to avoid accidentally overwriting the log data upon a restart.   The script must also ensure that the OSWatcher Black Box tool be run using the correct user privileges.

The osw-service RPM package does not include the OSWatcher Black Box  tool.  OSWatcher Black Box must be downloaded separately.

SOLUTION

The osw-service RPM package provides a script to run the OSWatcher at system boot, and to stop it down gracefully at system shutdown.  It provides an "osw" service that can be controlled using the standard Linux init(1) script controls:

# /sbin/chkconfig oswbb on
# /sbin/service oswbb start

The osw-service RPM package is available as an attachment to this note.  Download and install it as any other RPM package.  A source RPM is provided for completeness.

Before starting the service, first change the settings in the /etc/oswbb.conf configuration file to fit your situation:

# Set OSW_HOME to the directory where your OSWatcher tools are installed
OSW_HOME='/opt/osw'
# Set OSW_INTERVAL to the number of seconds between collections
OSW_INTERVAL='60'
# Set OSW_RETENTION to the number of hours logs are to be retained
OSW_RETENTION='48'
# Set OSW_USER to the owner of the OSWHOME directory
OSW_USER='root'
# Set OSW_COMPRESSION to the desired compression scheme
OSW_COMPRESSION='bzip2'
# Set OSW_ARCHIVE to the location to generate the logs
OSW_ARCHIVE='archive'

Once this is done, the command:

# /sbin/service oswbb start

will start the OSWatcher tool upon every boot.

The OSWatcher logs will be stored in ${OSW_ARCHIVE); the default value is the archive/ subdirectory as in earlier versions

How To Collect an Sosreport on Oracle Linux

APPLIES TO:

Reference from Oracle Doc ID 1500235.1
Linux OS - Version Oracle Linux 4.4 and later
Oracle VM - Version 3.2.7 to 3.2.7 [Release OVM32]
Information in this document applies to any platform.

GOAL

The "sosreport" is a tool to collect troubleshooting data on an Oracle Linux system. It generates a compressed tarball of debugging information that gives an overview of the most important logs and configuration of a Linux system, to be sent to Oracle Support.

Among other things, the sosreport includes information about the installed rpm versions, syslog, network configuration, mounted filesystems, disk partition details, loaded kernel modules and status of all services

It has a plugin-based architecture that enables features to be enabled or disabled, and additional functionality added.

Why support needs sosreport

The sosreport collects system information from an Oracle Linux system by capturing various log files, configuration files and command outputs that helps in diagnosing a problem faster.

Since this collects most of the commonly sort information while troubleshooting problems, collecting a sosreport helps in reducing the number of iterations of data request from the customer.

The logs, configuration files and related command outputs provides a better picture about the system environment and thus it is very helpful for cases about Root cause analysis and on going issues.

The sosreport helps the support to identify configuration errors and make proactive recommendations too.

SOLUTION

Main Content

To run sosreport, the package "sos" must be installed. This is usually installed by default, unless the system was installed with a custom package set. If it is not installed, it can be installed from ULN with up2date, the public Oracle Linux repository or from installation media. It is also a good idea to make sure it is up to date.

To create the sosreport can be as simple as running the command in a terminal, without arguments, as root:

# sosreport

It will ask for some information related to a support case:

# sosreport

sosreport (version 1.7)

This utility will collect some detailed  information about the
hardware and  setup of your  Enterprise Linux  system.
The information is collected and an archive is  packaged under
/tmp, which you can send to a support representative.
This information will be used for diagnostic purposes ONLY
and it will be considered confidential information.

This process may take a while to complete.
No changes will be made to your system.

Press ENTER to continue, or CTRL-C to quit.
Please enter your first initial and last name [testsystem-2]: jdoe
Please enter the case number that you are generating this report for: 3-1234567890

 and will then show a progress bar with estimated time:

 Progress [#                  3%                  ][00:33/19:54]

 On completion, a bzip2-compressed tarball will be created in /tmp, along with a file containing the md5sum so that the file's integrity can be verified by the support representative. The filename will be printed to the terminal:

Progress [###################100%##################][03:15/03:15]

Creating compressed archive...

Your sosreport has been generated and saved in:
  /tmp/sosreport-jdoe.3123456789-182843-6a3d9a.tar.bz2

The md5sum is: 17be8be4266b06efd9726825ff6a3d9a

Please send this file to your support representative.

It is possible to have the sosreport created somewhere other than /tmp by setting the TMPDIR environment variable when running the sosreport command:

# TMPDIR=/home/jdoe sosreport

Your sosreport has been generated and saved in:
  /home/jdoe/sosreport-jdoe.31234567890-904562-30011c.tar.bz2

Additional options:

To list available plugins:

# sosreport -l

sosreport (version 1.7)

The following plugins are currently enabled:

 apache          Apache related information
 autofs          autofs server-related information
 bootloader      Bootloader information
 devicemapper    device-mapper related information (dm, lvm, multipath)
 emc             EMC related information (PowerPath, Solutions Enabler CLI and Navisphere CLI)
 filesys         information on filesystems
 general         basic system information
 hardware        hardware related information
 ipsec           ipsec related information
 kernel          kernel related information
 ldap            LDAP related information
 libraries       information on shared libraries
 mail            mail server related information
 memory          memory usage information
 networking      network related information
 nfsserver       NFS server-related information
 pam             PAM related information
 printing        printing related information (cups)
 process         process information
 rpm             RPM information
 samba           Samba related information
 sendmail        sendmail information
 ssh             ssh-related information
 startup         startup information
 system          core system related information
 systemtap       SystemTap pre-requisites information
 x11             X related information
 xen             Xen related information
 yum             yum information

The following plugins are currently disabled:

 amd               Amd automounter information
 cluster           cluster suite and GFS related information
 ftp               FTP server related information
 initrd            initrd related information
 named             named related information
 openswan          ipsec related information
 radius            radius related information
 rhn               RHN Satellite related information
 s390              s390 related information
 selinux           selinux related information
 squid             squid related information
 xinetd            xinetd information

The following plugin options are available:

 devicemapper.lvmdump  off collect raw metadata from PVs
 general.syslogsize    15 max size (MiB) to collect per syslog file
 kernel.modinfo        on gathers module information on all modules
 kernel.sysrq          off trigger sysrq+[m,p,t] dumps
 networking.traceroute off collects a traceroute to linux.oracle.com
 rpm.rpmq              on queries for package information via rpm -q
 rpm.rpmva             on runs a verify on all packages
 yum.yumlist           off list repositories and packages

If the system has a lot of packages installed, and sosreport takes a long time to complete, support may request that you disable the rpm database verification (verifies all packaged files on the filesystem against the rpm database)

# sosreport -k rpm.rpmva=off

In older versions of sosreport, the kernel.sysrq option was enabled by default. On heavily loaded systems, this can take a long time and can cause problems; particularly on clustered systems where it may cause a node eviction. It may be wise to check if this option is disabled by default on your system, or to manually disable it when running sosreport:

# sosreport -k kernel.sysrq=off

 For further information, see the manual page man sosreport or the help text sosreport --help

Known issues

1. In earlier versions, sosreport could fail with:

#  /usr/sbin/sosreport -k rpm.rpmva=off -n kernel.sysrq
Traceback (most recent call last):
File "/usr/sbin/sosreport", line 31, in <module>
 import sos.policyredhat
ImportError: No module named sos.policyredhat

This usually occurs when a 3rd-party version of python is installed and has been fixed in version 1.7-6 and newer

2. Run sosreport may change the /usr/java/latest symbolic link

sosreport (or rpm -Va, by default part of a sosreport) may change /usr/java/latest symbolic link if it does not point to latest installed release (Doc ID 1597124.1)

REFERENCES

NOTE:1597124.1 - sosreport (or rpm -Va, by default part of a sosreport) may change /usr/java/latest symbolic link if it does not point to latest installed release

Oracle 資料庫轉換字元集工具：csscan

# 主題
CSSCAN (Character Set Scanner) 


# 適用版本
Oracle Database - Enterprise Edition - Version 8.1.7.4 and later 
Oracle Database - Standard Edition - Version 8.1.7.4 and later 
Information in this document applies to any platform. 


# 現象/目的 
在從前建置Oracle DB時，我們會將字元集設定成BIG5，但是網路促成的無國界情境使得種種軟體包含資料庫本身都必須容納多國語系，所以公司內部的資料庫大多早早已轉換成UTF8，但是受限於商業軟體，或者是其他考量，少部分資料庫還是停留在BIG5，倘若有朝一日當資料庫需要做轉換時，如果來源端跟目的端的資料庫字元集不一致時，必須先使用csscan 工具來確認對資料的影響性。


# 解決方式/內容
1. 準備動作
  1.1 確認目前字元集: 
         SQL> select value 
                    from NLS_DATABASE_PARAMETERS 
                    where parameter='NLS_CHARACTERSET'; 

NLS_CHARACTERSET 定義存放在資料庫的是何種字元，而非由 NLS_LANGUAGE 或是 NLS_TERRITORY 決定的 

  1.2 DB Version小於10.2.0.4或是11.1.0.6，impdp會有資料毀損狀況，expdp不受影響，apply patch 5874989可解決此bug。這問題在10.2.0.4 and 11.1.0.7 patch set，或是11.2.0.1 以上版本被解決。

  1.3 清除recyclebin
        $ sqlplus / as sysdba


        SQL> SELECT OWNER, ORIGINAL_NAME, OBJECT_NAME, TYPE 
                   FROM dba_recyclebin ORDER BY 1,2;

        SQL> purge dba_recyclebin;

  1.4 compile invalid objects
        $ sqlplus / as sysdba

        SQL> SELECT owner,object_name,object_type,status
                   FROM dba_objects
                   WHERE status ='INVALID';


        SQL> @?/rdbms/admin/utlrp.sql

  1.5 可以移除sample schema：'HR', 'OE', 'SH', 'PM', 'IX', 'BI' and 'SCOTT'，如果沒有用到APEX / HTML DB也可移除FLOWS_XXX 以及APEX_XXX Users。

2. 參考 Note 745809.1 安裝必要物件 (10g / 11g)
$ export ORACLE_SID=ORCL 
$ sqlplus /nolog 
SQL> conn / as sysdba 
SQL> set termout on 
SQL> set echo on 
SQL> spool csminst.log 
SQL> -- note the drop user 
SQL> drop user csmig cascade; 
SQL> @?/rdbms/admin/csminst.sql 

3. 執行 csscan
$ csscan \"system/password as sysdba\" USER=schema TOCHAR=UTF8 ARRAY=1024000 PROCESS=3 LOG=csscan_log

4. 檢查產出的報表並修正
  4.1  csscan_log.out ==> 執行csscan的過程
  4.2  csscan_log.txt ==> tables / column 有無異常的總表
  4.3  csscan_log.err ==> tables / column 有異常的資料明細
  4.4  通常BIG5轉UTF8欄位大小是除以2乘以3：column_size / 2 * 3 (因為一個中文字在BIG5中佔2個bytes，在UTF8中佔3個bytes)


# 參考文件
Note 225912.1 
Changing Or Choosing the Database Character Set ( NLS_CHARACTERSET ) 

Note 458122.1 
Installing and Configuring Csscan in 8i and 9i (Database Character Set Scanner) 

Note 745809.1 
Installing and configuring Csscan in 10g and 11g (Database Character Set Scanner) 

Note 444701.1 
Csscan output explained

(Doc ID 260192.1)
Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g

ORATOP

前言：
隨著PC Server的規格及速度愈來愈快，大多數的公司摒棄大型主機，進而選擇PC Server的趨勢愈來愈盛，雖然在可靠度上仍然是大型主機占優勢，但是大型主機的維護費用高昂，這也是讓一般公司望之卻步的主要因素。
在目前PC Server的可靠度尚待提升的當下，其實，Virtual Machine的選擇可以彌補PC Server可靠度的不足，目前三大虛擬平台逐漸成形，分別是Vmware、Hyper-V以及Oracle VM。
如果各位使用PC Server，將Oracle Database安裝在PC Server上，大概就只有Linux可以選擇了。Linux上面要即時監控系統狀況，"top" 指令是系統管理員常用的，但是我們使用 "top" 找到了 Top Process之後，往往還需要將Process ID轉換成Database SID，才能找出關鍵性的Session，進而解決效能問題，不過，Oracle最近有一項工具叫做 "oratop"，可以及時監控Linux上的Database Process狀況，讓系統管理員省去不少時間，找出 Top Session。

目的：
oratop是類似 top 的工具，可以針對Oracle Database Performance做全面性的檢視，如果搭配 top 使用，會得到更完整的系統效能資訊。

適用版本：
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.4 [Release 11.2]
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Linux x86-64
Linux x86

使用方式：
1. 使用oracle 使用者將下載的oratop.RDBMS_11.2_LINUX_X64  ftp 到資料庫主機上，如果是RAC環境，選定其中一個node上傳即可。
2. cd 到 oratop 程式所在目錄
3. 更名oratop程式

1. $ mv oratop* oratop

4. 更改oratop權限

1. $ chmod 755 oratop

5. 在.bash_profile 環境變數檔中，設定環境變數(如果尚未設定的話)

1. $ export TERM=xterm  #or vt100
   

   $ export ORACLE_HOME=<11.2 database home>

   $ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

   $ export PATH=$ORACLE_HOME/bin:$PATH

   $ export ORACLE_SID=<local 11.2 database SID to be monitored>  #only needed if connecting to a local database

6. 執行oratop (每10秒更新一次)

1. $ ./oratop -i 10 / as sysdba

7. 針對遠端資料庫執行oratop (每10秒更新一次)

1. $ ./oratop -i 10 system/manager@tns_alias

8. 結束oratop
    按下 "q"，或是 CTRL-C

參考畫面：

指令介紹：
1. 語法

1. $ oratop [Options] [Logon]

2. Options
    a) Help，Displays usage or output information.
        預設: N/A

1. $ oratop -h[elp]  # runtime mode 按下h

    b) Top 5 Wait Events
        預設: 累計
        選項: 即時呈現

1. $ oratop -d  # runtime mode 按下d

    c) 等待物件
        預設: Event/Latch
        選項: File#:Block#

1. $ oratop -k  # runtime mode 按下k

    d) session行為
        預設: 是Username/Program
        選項: 是Module/Action

1. $ oratop -m  # runtime mode 按下m

    e) SQL mode
        預設: Process mode
        選項: SQL display

1. $ oratop -s  # runtime mode 按下s

    f) Database Service centric mode
        預設: Connection mode
        選項: N/A

1. $ oratop -c  # runtime mode:N/A

    g) Long format, (132 columns)
        預設:  short (80 columns)
        選項:  long format for header & process section.

1. $ oratop -f  # runtime mode: 按下f

    h) Database Process mode
        預設:  Process mode
        選項: process display

1. $ oratop -p  # runtime mode: 按下p

    i) Batch mode operation
        預設: Text-based user interface
        選項: N/A

1. $ oratop -b  # runtime mode: N/A

    j) Maximum Number of iterations.   (requires a number)
        預設: infinite
        選項: the maximum number of iterations, or frames

1. $ oratop -n  # runtime mode: N/A

   k)  Displays tablespace information
        預設: N/A
        選項: tablespace information

1. # runtime mode: 按下t

    l) Displays  ASM diskgroup information.  (no op for non-ASM)
        預設: N/A
        選項: ASM diskgroup information

1. # runtime mode: 按下a

    l) Displays SQL execution plan table. Requires input of the active SQL sql_id value
        預設: 5 seconds
        選項: the delay between update refresh

1. $ oratop -c  # runtime mode: 按下

    m) Interval delay time. ( requires value in seconds)
         預設: Connection mode
         選項: N/A

1. $ oratop -v  # runtime mode: N/A

參考文件：
oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

SQLHC

介紹：
SQLHC (SQL Health Check) 是Database診斷工具之一，目的是快速取得SQL效能診斷資訊，你可以將它視為SQLT (SQLTXPLAIN) 的精簡版，不過和SQLT不同的是，SQLHC不需要安裝，所以也不會異動到Database，但是精簡版的工具當然有所限制，例如它無法在Data Guard的環境之下使用，也無法針對PL/SQL Procedure進行分析等等。

適用環境：
Oracle Database 10.2.0.1以後的版本

設定：
無須任何設定

執行條件：
需要在SQL*Plus執行sqlhc.sql，並且使用SYS，DBA，或是對Data Dictionary views有權限存取的User

執行步驟：
首先，先登入到database Server
# sqlplus / as sysdba
SQL> @sqlhc.sql
接著會出現參數1詢問：
Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
請輸入T
接著會出現參數2詢問：
A valid SQL_ID for the SQL to be analyzed (required)
請輸入SQL_ID

或是直接輸入參數也可以
SQL> @sqlhc.sql T djkbyr8vkc64h

執行結果：
執行完之後會在Database Server上產生sqlhc_{timestamp}_{sql_id}.zip，解壓縮之後會產出以下檔案：
1_health_check.html
2_diagnostics.html
3_execution_plans.html
4_sql_detail.html
5_sql_monitor.zip
6_10053_trace_from_cursor.trc
8_sqldx.zip
9_log.zip

各位是不是覺得 "7" 怎麼不見了?? 但是產出結果就是如此，不用再去鑽牛角尖，畢竟這不是重點。
第一份的health check會給出 Oracle建議項目，各位可以自身經驗參考，如下圖所示：

第二份的diagnostics會直接從AWR and ASH Reprot 抓出和該SQL相關的數值。
第三份execution plan對各位來說比較有感覺，裡頭會詳述該 SQL 的執行計畫，有經驗的Programer看了執行計畫應該就知道哪個地方該被tuning。
第四份sql detail是以圖形化的方式直接呈現該SQL對各項資源的效能指標。

如果沒有太多時間，前4份文件給的資訊就足以tuning SQL statement，如果可以的話，其他文件會讓你發現對於該SQL相關的其他更微小的細節，

參考文件：
SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

UltraEdit 的15個使用小技巧

轉貼自萬隆小站，http://bruce.idv.tw/forum/viewtopic.php?f=6&t=312

1. UEDIT查找的快捷鍵是Ctrl+F(find)或ALT-F3,替換的快捷鍵是Ctrl+R(replace)，再次查找的快捷鍵是F3。

2. 一般尋找與取代功能：

※在尋找/取代內將「正規化」的√給去除，並分別在尋找/取代的框格內輸入所需的文字，執行尋找/取代即可。 
※如果需要尋找Enter鍵，在尋找框中可輸入^p（注意p是小寫）來代替Enter鍵。

Symbol
Function
^^
尋找 ^ 符號 
^s
巨集執行時尋找選擇的字串(反白的字串)
^c
巨集執行時尋找剪貼簿的內容
^b
尋找分頁點
^p
尋找換行(dos/windows模式)
^r
尋找換行(MAC模式)
^n
尋找換行(unix模式)
^t
尋找tab

選擇所有內文之間的游標位置和搜尋目標，按住Shift鍵，在當時的搜尋是開始尋找下一個按鈕。


3. 沒有註冊碼，如何有效使用Ultraedit呢？
很多人立即想到使用crack版，可實際追究起來，那是違法的事情。
實際上，Ultraedit並沒有把路子完全堵死。我們可以使用一些小技巧來躲掉這個問題。
修改ultraedit的快捷方式，將命令行改為：E:\uedit9\UEDIT32.EXE p:，
其中p:必須是你的機器上不存在的磁碟分割，這樣，就不會有試用時間結束的問題了。

4. 如何去掉所編輯文本中包含特定字串的行?　
這則技巧是在UltraEdit的幫助文件裡提到．CTRL+R 叫出取代(Replace)視窗,
選取"正規化";然後用尋找 %*你的字串*^p 替換成空內容即可.
(UltraEdit的正規化引擎，進階->搜尋->正規化引擎->UltraEdit樣式)
如，我現在有個文件，需要去掉所有包含 http://www.baidu.net/ 這個字串的行，
尋找　%*http://www.baidu.net/*^p　替換成空即可．
注意,^p 是 DOS 文件類型的換行．如果是 Unix 類型文件，則用 ^n.

5. 如何在行末加入特定字元，比如逗號? 
有了上面的經驗，CTRL+R 叫出取代(Replace)視窗,
選取"正規化";然後可以尋找 ^p(或者^n,如果是Unix 文件),
用 ,^p(或者,^n)進行"全部取代"即可.補充一點，如果是 MAC(Apple) 類型文件,則換行符號為 ^r .

6. 如何刪除空行?
參考上面兩個例子,查找 ^p$ 然後替換為空即可．

7. 編輯文件如何加入時間戳 ?
F7　快捷鍵即可．你試試看?


8. 為何 複製(Copy)/貼上(Paste)功能不能用了?
UltraEdit有10個剪貼簿(clipboard),分別用Ctrl+0 - Ctrl+9　切換
Ctrl+0 是Windows 的，其他則為用戶自定義的．我在使用的過程中錯調用了 CTRL+n, 
結果內容就有問題了．你遇到過沒?

9. 即使是打開小文件也有遲延?
每次打開文件的時候總有幾秒鐘的耽擱．我的電腦效能可不算差．怎麼回事? 
網絡印表機搞得鬼! 
打開"進階"->"組態"->"編輯器"->"進階",
看看是不是選中了"讀取/還原印表機設定"?
如果是的話，去掉．

10. 刪除當前行內容 CTRL+e

11. 你按一下快捷鍵ALT+c試試，變為列編輯模式，可以一次輸入多行內容，只要你選好行範圍，編輯整齊的表格非常方便。

12. 游標位於某一行，按Ctrl+F2試試，有了標籤了，多設幾個，按住F2，它會往你設定的標籤處跳來跳去，很好玩(Alt+F2回前一個標籤)。

13. 發現沒有，有時候很多文件類型在Ultraedit裡沒有語法高亮度顯示，很麻煩不是。
不要急，從UE的網站上下一些方法的模組，找到你要的文件類型的語法文件，
將其內容追加到wordfile.txt裡頭，注意序號不要重複，一定要在1－20之間。網址是：
http://www.ultraedit.com/index.php?name ... age&pid=40

14.ctrl+b
寫程式的時候，括號一般要一一對應的，但是如果嵌套太多，看花眼了，怎麼辦？
你把游標放在括號開始的地方，按ctrl+b，UE 會幫你找到相對應的括號結尾的地方。
你還可以試試連著多按幾次ctrl+b。 

15.自定義快捷鍵
UE很多功能都有快捷鍵，但不是所有的都有。
UE 11.00有一個Text2html的功能。我工作中遇到了要對大量源始碼進行這種操作的情況。
我就想自己定一個快捷鍵。advanced(進階)/configuration(組態)/key mapping(快速鍵)
在commands(指令)裡面找到你要用的command.
我這裡是HTMLConvertSpecialChars(HTML-轉換特殊字符),然後點 Press new key(新鍵)下面的輸入框，
設置一個自己覺得爽，不衝突的快捷鍵。比如ctrl+alt+s.然後點ok.這樣用常用的功能，可以成倍提高效率。