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.

Oracle 11.2.0.3 ORACLE_OCM.MGMT_CONFIG_JOB_2_1 errors in database alert log.

You might get following errors in Oracle database alert log:

Errors in file /u01/app/oracle/diag/rdbms/<database_name>/<instance_name>/trace/<instance_name>_j000_7665.trc:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"
ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", line 2436
ORA-06512: at line 1


Reason for these errors are Oracle Configuration Manager (OCM).
It will give these errors once a day because it is trying to write directory that does not exist.

You can fix this error with following ways:

If you don't use OSM then:

Drop whole user:
SQL> drop user ORACLE_OCM cascade;
OCM is a standalone, separate tool and removing won't affect the database functionality.

Or just disable OCM jobs (this prevent it's writing attempts):
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB')
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB')


If you want to use OCM then you need to reconfigure it:

More info about reconfigure and this error from MOS (My Oracle Support) document:  
1453959.1

Oracle Grid Control database configuration save problem.

If you get following errors when trying to save configuration changes into database via Grid Control:
"Saving instance <INSTANCE_NAME> to repository and agent
https://<AGENT_NAME>:3872/emd/main/...-- exception caught when saving the <DB_NAME> to
agent https://<AGENT_NAME>:3872/emd/main/:
oracle.sysman.emSDK.emd.comm.OperationException: Skipping target {<DB_NAME>, rac_database}:
Missing properties - ClusterName ... finished."



This error is coming because Grid Control Agent targets.xml is missing ClusterName properties row.


To fix this problem:

Go into Agent servers and take backup of file: $AGENT_HOME/<nodename>/sysman/emd/targets.xml

And then add following row into file (for example after DBVersion row):
<Property NAME="ClusterName" VALUE="<CLUSTER_NAME>"/>

And in the end reload agent in Agent servers:
$AGENT_HOME/bin/emctl reload 

After these steps configuration should work normally.