Oracle Windows OPatch Prerequisite check "CheckActiveFilesAndExecutables" (Following files are active) failed.

When you are running Oracle OPatch patches in Windows environment you might get following error:
Running prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

Following files are active :
C:\oracle\product\10.2.0\db_1\bin\oci.dll 
.
.
.

Patching guide says that before patching you should stop all Oracle services along with the "Distributed Transaction Coordinator" service. But sometimes this is not enough and you get "ActiveFiles" error when trying to run OPatch.

Usually problem is Windows Management Instrumentation (WMI) service. Restart this service from Services tool or you can also end process "WMIPrvSE.exe" from Task Manager (it will restart automatically). Then try to run OPatch again.

From following MOS (My Oracle Support) document you can find more help to this error:
Files in Use errors, when applying patches on Windows [ID 418479.1]

There is also Process Explorer tool which you can use to check which process is using certain ddl's:
http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

MySQL flush logs; gives: ERROR 1105 (HY000): Unknown error

If you trying to run "flush logs;" from mysql or with mysqladmin you'll get following error message:
ERROR 1105 (HY000): Unknown error

There are bugs that might affect like this but usually first thing to do with this is check log paths permissions.

1. First check what log directories your MySQL is using:
mysql> show global variables like '%log%';

You can also use more specific checks like:
This show error log:
mysql> show global variables like '%err%';
or for general log:
mysql> show global variables like '%general%';
or for slow log:
mysql> show global variables like '%slow%';


For example:

mysql> show global variables like '%err%';
+--------------------+----------------------+
| Variable_name      | Value                |
+--------------------+----------------------+
| error_count        | 0                    |
| log_error          | /data/mysql/logs/mysqld_error.log |
| max_connect_errors | 10                   |
| max_error_count    | 64                   |
| slave_skip_errors  | OFF                  |
+--------------------+----------------------+
5 rows in set (0.00 sec)



2. After you know log paths check that mysql user (or user that run mysqld if it is other than mysql.) have permissions to write in those directories (remember to check all log paths you are using).

ls -latr /data00/mysql/

drwxr-xr-x 14 mysql mysql 4096 Mar 26 06:46 logs

If there is not enough permissions then add them and the error is gone. If permissions are correct then you are probably hitting the bug.

Oracle Cluster Verification Utility (cluvfy) tracing.

If you get errors from Cluster Verification Utility (cluvfy) then you might need to get trace log out of it.
For example if you are creating Service Request about cluvfy into My Oracle Support you might add trace log into SR.

How to get trace log from cluvfy (run these as oracle user) :

1. Create directory for log:
mkdir /tmp/cvutrace

2. Set ENV variables:
export CV_TRACELOC=/tmp/cvutrace
export SRVM_TRACE=true
export SRVM_TRACE_LEVEL=1

3. Run wanted cluvfy check:
runcluvfy.sh .....

For example:
/data/install/grid/runcluvfy.sh stage -pre crsinst -n node1,node2 -r 11gR2 -verbose

After the cluvfy run is completed you'll have its trace log in the directory you created.
Copy it and add into the SR.

More info from MOS (My Oracle Support):
MOS ID 986822.1

Oracle shared server processes generates huge trace files in diag > rdbms > trace directory

There is Oracle bug that can cause shared server processes to generate huge trace files in the diag > rdbms > trace directory.

If you are using RAC this problem usually appears only in one node at the time.

Inside trace files you can find following errors (reparse is main thing to watch):
-----------
kksfbc: entering reparse diagnosis mode for xsc:0x7381dcbd0
...

and

 Check cursor failed ctx=(nil) xscflg=110632 ctxflg=0 flg=102
 kksSearchChildList: no suitable child found (hash_match=0)
...

and

Current Cursor Sharing Diagnostics Nodes:
  Child Node: 2  ID=40 reason=Bind mismatch(8) size=4x4
...

-----------

This bug itself is not fatal if you have enough free space in disk where diag directory is.
But if that disk run out of free space then alert log will start to log errors about the trace file writing.

And if the diag directory is in the default place there will be probably other log and files in the same disk which will cause you more problems when the disk is full. For example listener log.

There is fix for this bug in MOS (My Oracle Support) and also more information (MOS ID):
10373013.8   (you can check patches from Patch & Updates > search  10373013)


NOTE! The bug is already fixed in 11.2.0.3 and 12.1 versions. And some patch updates of 11.2.0.2 . Quick workaround for this problem is empty trace files for example this way in Linux:
cat /dev/null > trace_file_name.trc  . If you just delete the file it will not free used space until you restart database. But above emptying will free space immediately. But this is just workaround and error can hit again later. So if you get these often it is better to use bug fix or upgrade the database into new version.

IOPS check from Oracle 11 server

If you want to check Oracle database server IOPS (Input/Output Operations Per Second) usage you can do it with following SQL:

SQL> break on report
SQL> compute sum label TOTAL_IOPS: of IOPS on report
SQL> select METRIC_NAME,avg(AVERAGE) as "IOPS" 
from dba_hist_sysmetric_summary 
where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec') 
group by METRIC_NAME;

For example:
SQL> break on report
SQL> compute sum label TOTAL_IOPS: of IOPS on report
SQL> select METRIC_NAME,avg(AVERAGE) as "IOPS"
  2     from dba_hist_sysmetric_summary
  3     where METRIC_NAME in ('Physical Read Total IO Requests Per Sec','Physical Write Total IO Requests Per Sec')
  4     group by METRIC_NAME;

METRIC_NAME                                                            IOPS
---------------------------------------------------------------- ----------
Physical Write Total IO Requests Per Sec                                  32.8448519
Physical Read Total IO Requests Per Sec                                  13.3675773
                                                                                                 ----------
TOTAL_IOPS:                                                                         46.2124292


NOTE! DBA_HIST_SYSMETRIC_SUMMARY contains snapshots of V$SYSMETRIC_SUMMARY view. V$SYSMETRIC_SUMMARY displays a summary of all system Metric values for the long-duration system metrics. The average, maximum value, minimum value, and the value of one standard deviation for the last hour are displayed for each metric item. And because of this if you are using V$SYSMETRIC_SUMMARY instead of DBA_HIST_SYSMETRIC_SUMMARY you will get different results .

Oracle export table data to CSV format.

There are many ways to export Oracle table data to CSV format.

Easiest way is simple SQL spool clause:

With following sql spool you can get txt file that contains data in CSV format (with strings (columns 2,3 and 5) in single quotation marks):
SQL> spool testdata.csv
SQL> SELECT column1||',"'||column2||'","'||column3||'",'||column4||',"'||column5||'"'FROM testtable1;
SQL> spool off;


Example:
SQL> desc test_schema.test_table_1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2                                               VARCHAR2(20)





SQL> spool test.csv
SQL> SELECT col1||','''||col2||'''' FROM test_schema.test_table_1
SQL> spool off;


You can also use Oracle UTL_FILE package to get data in CSV format more info about that from asktom.oracle.com:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059

Oracle Grid Control Agent configuration/communication problem.

If your suddenly get following errors in Grid Control console:

If trying to configure database error will appear:
"Error occurred while getting information for the specified target. Possible reasons include: the target doesn't exist; connection to the target failed."

If trying to configure agent this error will appear:
"Communication between the Oracle Management Service host to the Agent host is unavailable. Any functions or displayed information requiring this communication link will be unavailable. For example: deleting/configuring/adding targets, uploading metric data, or displaying Agent home page information such as Agent to Management Service Response Time (ms)."

And when going into database page in grid control following error:
"java.lang.Exception: java.io.IOException: javax.net.ssl.SSLPeerUnverifiedException: peer not authenticated"


These are coming because of some kind network connectivity issues with secured agents.

Workaround for these errors is:

Check oms detailed status (this asks SYSMAN password)
$OMS_HOME/bin/emctl status oms -details

If Agent upload and OMS Console is locked then run following:
(To remove the restriction for HTTPS uploads from the Management Agents, repeat the preceding procedure, but replace the emctl secure lock command with the following command: (this asks SYSMAN and agent securing password.)
$OMS_HOME/bin/emctl secure unlock 

$OMS_HOME/bin/emctl stop oms -all
$OMS_HOME/bin/emctl start oms
 
After unlock run following for each of problematic Grid Control Agents:
$AGENT_HOME/bin/emctl unsecure agent
$AGENT_HOME/bin/emctl upload agent
$AGENT_HOME/bin/emctl status agent

 After this errors are gone. 



If want to use secure OMS and Agents try to find problem of HTTPS connections between your OMS server and Agent servers. And after that run : 
$OMS_HOME/bin/emctl secure lock 

$OMS_HOME/bin/emctl stop oms -all
$OMS_HOME/bin/emctl start oms

And then secure agents with:
$AGENT_HOME/bin/emctl secure agent
$AGENT_HOME/bin/emctl upload agent
$AGENT_HOME/bin/emctl status agent

 
Sometimes simple unsecure and secure can fix the problem.