SRVCTL command

1. For single database in ASM:
# srvctl status database -d <db_unique_name>
# srvctl status listener

# srvctl stop database -d <db_unique_name> -o immediate
# srvctl stop listener
# srvctl start listener
# srvctl start database -d <db_unique_name>

# srvctl status database -d <db_unique_name>
# srvctl status listener

2. For RAC database in Filesystem or ASM
If service name is PROD, instance name is PROD1 (hostname is HOST1) and PROD2 (hostname is HOST2)

# srvctl status database -d PROD
# srvctl status listener

# srvctl stop database -d PROD -o immediate
or
# srvctl stop instance -d PROD -i PROD1,PROD2 -o immediate

# srvctl stop listener
or
# srvctl stop listener -n HOST1

# srvctl start listener
or
# srvctl start listener -n HOST1

# srvctl start database -d PROD
or
# srvctl start instance -d PROD -i PROD1,PROD2

# srvctl status database -d <db_unique_name>
# srvctl status listener

Reference:
https://docs.oracle.com/html/E25494_01/restart005.htm#srvstartdb1

如何刪除HPUX的未使用裝置

例如找出 Tape 未連接裝置
# ioscan -fnC tape
Class     I  H/W Path       Driver   S/W State   H/W Type     Description
==================================================
tape      1  0/4/1/0/4/0.2.0  stape    NO_HW       DEVICE       IBM     DDS Gen5
         /dev/rmt/1m            /dev/rmt/1mn           /dev/rmt/c2t2d0BEST    /dev/rmt/c2t2d0BESTn
         /dev/rmt/1mb           /dev/rmt/1mnb          /dev/rmt/c2t2d0BESTb   /dev/rmt/c2t2d0BESTnb
tape      0  0/4/1/0/4/1.0.0  stape    CLAIMED     DEVICE       HP      C7438A
         /dev/rmt/0m            /dev/rmt/0mnb          /dev/rmt/c3t0d0BESTn   /dev/rmt/c3t0d0DDSb
         /dev/rmt/0mb           /dev/rmt/c3t0d0BEST    /dev/rmt/c3t0d0BESTnb  /dev/rmt/c3t0d0DDSn
         /dev/rmt/0mn           /dev/rmt/c3t0d0BESTb   /dev/rmt/c3t0d0DDS     /dev/rmt/c3t0d0DDSnb

找出顯示 NO_HW 的 H/W Path，再使用 rmsf 指令刪除
# rmsf -H 0/4/1/0/4/0.2.0

或是直接執行以下指令：
# ioscan -nfC tape|grep NO_HW|awk '{print $3}'|xargs -i -t rmsf -H {}

或是重新 reboot 也可以

最後再重新搜尋一次
# ioscan -fnC tape
Class     I  H/W Path       Driver   S/W State   H/W Type     Description
==================================================
tape      0  0/4/1/0/4/1.0.0  stape    CLAIMED     DEVICE       HP      C7438A
         /dev/rmt/0m            /dev/rmt/0mnb          /dev/rmt/c3t0d0BESTn   /dev/rmt/c3t0d0DDSb
         /dev/rmt/0mb           /dev/rmt/c3t0d0BEST    /dev/rmt/c3t0d0BESTnb  /dev/rmt/c3t0d0DDSn
         /dev/rmt/0mn           /dev/rmt/c3t0d0BESTb   /dev/rmt/c3t0d0DDS     /dev/rmt/c3t0d0DDSnb

Oracle ERP Daily Check

-- DB
invalid object
blocking lock
unusable index
enable but invalid trigger
top 5 event
long running session
session count
tablespace free status
tablespace space compare with the previous day
segment space compare with the previous day
-- AP
concurrent program enable run alone
concurrent program enable trace
diable User Has Pending concurrent request
concurrent request running but completed
concurrent request running
concurrent manager queue status

SAP Basis Daily Check

Skip to end of metadata參考網址

Reference:

http://wiki.scn.sap.com/wiki/display/Basis/SAP+Basis+Daily+Tasks?original_fqdn=wiki.sdn.sap.com

• SAP System R/3 System Status Check:

Logon Test

The availability of the SAP system is a pre-requisite for using the SAP system. If  you to establish connection to the SAP system the system must be up and running.

如果logon沒問題，表示系統是正常的

• Backup Management:

DB12

It is recommended that backup of the SAP system daily. Success or failure of the backup run has to be monitored daily. Ensure that backups are done properly so that you can recover the system state when it’s required. When a backup run fails, you should immediately resolve the problem and possibly perform an “emergency” backup.

養成習慣，每天確認備份是否成功

• Application Servers Status Check:

SM51

Application servers used for load balancing, hence, the need for their availability. The application server represents the runtime environment the SAP system. Use transaction SM51 to display the status of the instances of your SAP system.

• Work processes Status Check:

SM51

Work processes are essential for the effective functioning of the SAP system. It is important to ensure that all configured work processes possess their correct status at any point in time. The SAP administrator should be able to know when to add or redistribute work process based on usage analysis.

確認CI and DI 每個節點的WP都是正常運作

• Failed Updates Monitoring:

SM13

Failed updates are transaction that is not committed in the database. As administrator you needs to critical review such updates. Examine the reason and reprocess the failed update if required.

檢查是否有update error，也可以說是lock

• System Log Review:

SM21

The SAP system has its own system log. The system log contains error, warning and problem messages. The application server records events and problems in the system log and has a log that contains the messages output.

檢察系統 log

• Jobs Monitoring:

SM37/SM35

In order to optimize resources and increase performance of SAP systems, some operations are performed at the background, defined or standard jobs. Background job as it were, is supposed to perform assigned task. Review the status of jobs for failure or success.

檢查系統管理相關的 job 是否正常執行

"先 outer join 再 filter " 的結果不一定等於 "先 filter 再 outer join "

這是個很弔詭的問題，直覺上我們會覺得先join 再filter 跟先filter 再 join 的結果是相同的，的確，這情況會發生在 inner join 身上，所以想當然爾，我們也會認為outer join 的結果也會相同，但是事實卻不是如此。

舉例來說，如果是先 left outer join 再 filter 的話，right table會因為 filter 的欄位而缺少配對的數值，換個角度看，因為缺少配對的數值，所以應該要存在的值就變成是 NULL，所以這也是為何加上 ..... OR column is NULL  會變成正確的原因。
老實說，這很難理解，但是我們如果先 filter 再做 left outer join 的話，問題就會變的簡單多了
先 filter column = 'Y' 是我們想要的第一層結果，再做 left outer join 是我們想要的第二層結果，讓 left table 全數呈現。

結論是，今後遇到 outer join 加上 filter 的問題，先使用 sub-query 做 filter ，再做 outer join。

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