Archive for the ‘Oracle’ Category

ORA-00600: internal error code, arguments: [ORA_NPI_ERROR], [600], [ORA-00600: internal error code, arguments: [KFCHK_ERRRET], [kfa.c], [3025]

January 2nd, 2014 Matthias Pölzinger No comments

Problem description:

You are trying to recursively delete an ASM folder in asmcmd on and are receiving the following ORA-00600:

ASMCMD> rm -rf 2013_12_10
ORA-29261: bad argument
ORA-00600: internal error code, arguments: [ORA_NPI_ERROR], [600], [ORA-00600: internal error code, arguments: [KFCHK_ERRRET], [kfa.c], [3025], [], [], [], [], [], [], [], [], []
], [], [], [], [], [], [], [], [], []
ORA-06512: at line 4 (DBD ERROR: OCIStmtExecute)

Problem resolution:

This seems to be a currently unresolved issue in Even applying PSU1 will not fix this issue. Currently it is only possible to workaround this issue by not recursively deleting folders and instead delete the files inside:

ASMCMD> rm -f 2013_12_10/*
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    2013_12_11/
                                        Y    2013_12_12/
                                        Y    2013_12_13/

System generated folders will be deleted automatically by Oracle ASM if no files are stored inside any longer.

Categories: Database, Grid Infrastructure, Oracle Tags:

How To unlock APEX admin without changing the password

August 3rd, 2013 Matthias Pölzinger 1 comment

Problem description:

You have locked your APEX administrator account by mistyping the account’s password too often. As other persons are also using the account, you don’t want to use the “apxchpwd”-script.

Problem resolution:

If you just want to unlock the account instead of completely resetting the password, you can use the following PL/SQL block to establish this task:

    wwv_flow_security.g_security_group_id := <APEX_WORKSPACE_ID>;


  1. Query the id for the “INTERNAL” workspace:

    SQL> SELECT workspace_id FROM apex_workspaces WHERE workspace = 'INTERNAL';


  2. Query the APEX schema’s name for your version:

    SQL> select username from dba_users where username like 'APEX%' order by 1;


  3. Switch your session to the APEX-schema:

    SQL> alter session set current_schema = APEX_040200;                      
    Session altered.


  4. Unlock your ADMIN account with the following code:

    SQL> begin
      2    wwv_flow_security.g_security_group_id := 10;
      3    wwv_flow_fnd_user_api.UNLOCK_ACCOUNT('ADMIN');
      4    commit;
      5  end;
      6  /
    PL/SQL procedure successfully completed.
Categories: APEX, Oracle Tags:

Oracle Clusterware / Grid Infrastructure: How to determine the configured name of your cluster?

Problem description:

You need to know the cluster name defined during the installation of Oracle Clusterware / Oracle Grid Infrastructure. Maybe because you are configuring Oracle Enterprise Manager Database Console for one of your RAC Databases via emca:

[oracle@racn01 ~]$ emca -config dbcontrol db -repos create -cluster
STARTED EMCA at May 27, 2012 10:42:10 AM
EM Configuration Assistant, Version Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.
Enter the following information:
Database unique name: RACDB
Service name: RACDB
Listener port number: 1521
Listener ORACLE_HOME [ /oracle/app/grid/11.2.0/grid ]:
Password for SYS user: 
Password for DBSNMP user: 
Password for SYSMAN user: 
Cluster name:

Problem resolution:

Oracle Clusterware’s command “cemutlo” can be used to determine the name defined for your cluster during the installation:

[root@racn01 ~]# /oracle/app/grid/11.2.0/grid/bin/cemutlo -n
[root@racn01 ~]#


AOUG Experts Forum 13.10.2011: Storage Technologies for Oracle Database Systems and Best Practices

October 14th, 2011 Matthias Pölzinger No comments

InITSo was invited to hold a lecture on Oracle ACFS / Oracle Cloud File System at the Austrian Oracle User Group’s Experts Forum on Storage Technologies for Oracle Database Systems – Best Practices (“AOUG Expertentreff: Storage Technologien Oracle Datenbanksystem – Best Practices“).

If you are interested in this topic, you can download an English or German version of the presentation via the following links:


Oracle: SQL*Plus displays “No errors.”

August 18th, 2011 Matthias Pölzinger No comments

Problem description:

Your are compiling an object, but it fails with an error. If you are trying to check the error message(s) for the compilation, SQL*Plus displays “No errors.”:

Warning: VIEW altered WITH compilation errors.
SQL> SHOW errors
No errors.

Problem resolution:

“show errors” only displays the last error message generated. In some cases (like above) no error messsage will be displayed. In order to force the display of error messages for a specific object, make use of the full functionality of “show errors” and specify the type and name of your object:

-------- -----------------------------------------------------------------
0/0      ORA-01730: invalid NUMBER OF COLUMN names specified

You can also use this method to display errors for other objects and object types:

                VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
Categories: Oracle, SQLPlus, Utilities Tags:

Oracle: How to (comfortably) disable Oracle Scheduler job execution

Problem description:

You want to disable the execution of all Oracle Scheduler jobs by a simple command. With Oracle Jobs (pre-Oracle-Scheduler-era) you where able to disable any job execution by simply setting the job_queue_processes parameters to 0:

* Disabling job execution:
ALTER SYSTEM SET job_queue_processes = 0;
* Enabling job execution (job_queue_processes > 0, e.g. 10):
ALTER SYSTEM SET job_queue_processes = 10;

Oracle Scheduler has no instance parameter which defines the number of job processes.

Problem resolution:

Although there is no entry in the documentation for disabling the Oracle Scheduler, you can stop the Scheduler job execution by using an undocumented attribute called “SCHEDULER_DISABLED” when executing DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE:

  • Disable Oracle Scheduler job execution
    SQL> EXEC dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');
    PL/SQL PROCEDURE successfully completed.


  • Disable Oracle Scheduler job execution
    SQL> EXEC dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');
    PL/SQL PROCEDURE successfully completed.

The current Scheduler job execution state can be checked by querying the Data Dictionary view DBA_SCHEDULER_GLOBAL_ATTRIBUTE:

  • Example for a disabled Scheduler
    SQL> col VALUE FOR a40
    SQL> SELECT * FROM dba_scheduler_global_attribute WHERE attribute_name = 'SCHEDULER_DISABLED';
    ------------------------------ ----------------------------------------


  • Example for an enabled Scheduler
    SQL> col VALUE FOR a40
    SQL> SELECT * FROM dba_scheduler_global_attribute WHERE attribute_name = 'SCHEDULER_DISABLED';
    no ROWS selected


Please be informed that the usage of DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(‘SCHEDULER_DISABLED’, ‘TRUE|FALSE’) is currently undocumented and therefore “SCHEDULER_DISABLED” is not a supported scheduler attribute. It should not have a detrimental effect on your system(s), but just in case, as it is with unsupported/undocumented features, we take no responsibility for any damages incurred from the use of the information contained herein.

Categories: Oracle, Scheduler, Undocumented Tags:

Oracle: How to compile strmmon (Streams Monitoring Utility) on UNIX systems

The Streams Monitoring Utility strmmon is a very useful utility for monitoring current Streams activity. You can use it to obtain the current figures for capture, propagation and apply activity in vmstat-style.

To obtain the latest version of the utility use the following link to download it in a zip-file format:

Compilation on systems prior 10gR2:

  1. Download the latest version
  2. extract the version on your database server in a strmmon directory
  3. make sure that you have set ORACLE_HOME and LD_LIBRARY_PATH correctly
  4. compile the binary inside the directory strmmon using “make -f strmmon”

Compilation on 10gR2 systems:

  1. Download the latest version
  2. extract the version on your database server in a strmmon directory
  3. copy the files strmmon.c and strmmon.html to your $ORACLE_HOME/rdbms/demo directory
  4. cd $ORACLE_HOME/rdbms/demo
  5. make -f strmmon

For Oracle Database systems version 11g onwards, strmmon has been replaced by Oracle Streams performance/diagnostic related dictionary views and the DBMS_STREAMS_ADVISOR_ADM PL/SQL Package.

Example on

oracle@oracle01:~$ mkdir strmmon
oracle@oracle01:~$ cd strmmon/
oracle@oracle01:~/strmmon$ mv /tmp/ .
oracle@oracle01:~/strmmon$ unzip
inflating: strmmon.htm
inflating: strmmon.c
oracle@oracle01:~/strmmon$ cp strmmon.c $ORACLE_HOME/rdbms/demo
oracle@oracle01:~/strmmon$ cp strmmon.htm $ORACLE_HOME/rdbms/demo
oracle@oracle01:~/strmmon$ cd $ORACLE_HOME/rdbms/demo
oracle@oracle01:/u01/app/oracle/product/10.2.0/db_1/rdbms/demo$ make -f strmmon
make -f /u01/app/oracle/product/10.2.0/db_1/rdbms/demo/ build EXE=strmmon OBJS=strmmon.o
make[1]: Entering directory `/u01/app/oracle/product/10.2.0/db_1/rdbms/demo'
/usr/bin/gcc -c -I/u01/app/oracle/product/10.2.0/db_1/rdbms/demo -I/u01/app/oracle/product/10.2.0/db_1/rdbms/public -I/u01/app/oracle/product/10.2.0/db_1/plsql/public -I/u01/app/oracle/product/10.2.0/db_1/network/public -I/u01/app/oracle/product/10.2.0/db_1/precomp/public strmmon.c
/usr/bin/gcc -L/u01/app/oracle/product/10.2.0/db_1/lib/ -L/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/ -o strmmon strmmon.o -lclntsh   `cat /u01/app/oracle/product/10.2.0/db_1/lib/sysliblist` -ldl -lm
make[1]: Leaving directory `/u01/app/oracle/product/10.2.0/db_1/rdbms/demo'

Execution example:

oracle@oracle01:/u01/app/oracle/product/10.2.0/db_1/rdbms/demo$ ./strmmon
Usage: strmmon -interval <seconds> -count <number> [-user <user name>]
[-passw <password>] [-dbname <database name>] [-sysdba]
oracle@oracle01:/u01/app/oracle/product/10.2.0/db_1/rdbms/demo$ ./strmmon -interval 1 -count 5 -user strmadmin -passw SOME_PW -dbname
STREAMS Monitor, v 2.6  Copyright Oracle Corp. 2002, 2005.
Interval = 1, Count=5 ORACLE
Streams Pool Size = 132M
NET: <client bytes per sec> <dblink bytes per sec>
Cxxx: <lcrs captured per sec> <lcrs enqueued per sec> <capture latency>
PRxx: <messages received per sec>
Qx  : <msgs enqueued per sec> <msgs spilled per sec>
PSxx: <lcrs propagated per sec> <bytes propaged per sec>
Axxx: <lcrs applied per sec> <txns applied per sec> <dequeue latency>
<F>: flow control in effect
<B>: potential bottleneck
AR: apply reader
AS(n): n number of apply server
<x%I x%F x%xx>: <idle wait events percentage> <flow control wait events percentage> <other wait event percentage and name>
xx->: database instance name
2009-01-15 16:14:18 || orcl-> |      |
2009-01-15 16:14:19 || orcl-> | NET 16K 0 | <B> C002 0 0 0sec <97%I 0%F ->  |  -  C001 6 0 0sec <97%I 0%F ->  | Q54516 0 0 | PS01 0 0 0 | Q53876 0 0 | PS02 0 0 0
2009-01-15 16:14:20 || orcl-> | NET 13K 0 | <B> C002 0 0 0sec <97%I 0%F ->  |  -  C001 0 0 0sec <97%I 0%F ->  | Q54516 0 0 | PS01 0 0 0 | Q53876 0 0 | PS02 0 0 0
2009-01-15 16:14:21 || orcl-> | NET 13K 0 |  -  C002 6 0 3sec <100%I 0%F ->  |  -  C001 0 0 0sec <97%I 0%F ->  | Q54516 0 0 | PS01 0 0 0 | Q53876 0 0 | PS02 0 0 0
2009-01-15 16:14:23 || orcl-> | NET 6K 0 |  -  C002 0 0 3sec <48%I 0%F ->  |  -  C001 0 0 0sec <48%I 0%F ->  | Q54516 0 0 | PS01 0 0 0 | Q53876 0 0 | PS02 0 0 0
Categories: Oracle, Streams, STRMMON, Utilities Tags:

Oracle: How to easily display the execution plan of a statement located in the shared pool (command line style)

Problem description:

You want to review the execution plan of a statement currently or previously executed by a session and Oracle Enterprise Manager is not use. Querying V$SQL_PLAN would give you the requested information, but not quiet comfortable.

Problem resolution:

As with “EXPLAIN PLAN” generated execution plans, you can use DBMS_XPLAN in order to display the execution plan for a statement in the shared pool. To be more specific, the function DISPLAY_CURSOR will provide you the information by passing the sql_id and cursor child number of the statement in concern:

   sql_id        IN  VARCHAR2  DEFAULT  NULL,
   child_number  IN  NUMBER    DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL');

In addition DBMS_XPLAN.DISPLAY_CURSOR can provide you various plan statistics such as I/O, memory and timing by specifying the format:

  • BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.
  • TYPICAL: This is the default. Displays the most relevant information in the plan (operation id, name and option, #rows, #bytes and optimizer cost). Pruning, parallel and predicate information are only displayed when applicable. Excludes only PROJECTION, ALIAS and REMOTE SQL information (see below).
  • SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
  • ALL: Maximum user level. Includes information displayed with the TYPICAL level with additional information (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

In addition following keywords can be added to above standard formats in order to customize the output. These keywords must be separated by a comma or a space:

  • ROWS – if relevant, shows the number of rows estimated by the optimizer
  • BYTES – if relevant, shows the number of bytes estimated by the optimizer
  • COST – if relevant, shows optimizer cost information
  • PARTITION – if relevant, shows partition pruning information
  • PARALLEL – if relevant, shows PX information (distribution method and table queue information)
  • PREDICATE – if relevant, shows the predicate section
  • PROJECTION -if relevant, shows the projection section
  • ALIAS – if relevant, shows the “Query Block Name / Object Alias” section
  • REMOTE – if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)
  • NOTE – if relevant, shows the note section of the explain plan
  • IOSTATS – assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL), this format will show IO statistics for ALL (or only for the LAST as shown below) executions of the cursor.
  • MEMSTATS – Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators.
  • ALLSTATS – A shortcut for ‘IOSTATS MEMSTATS’
  • LAST – By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last execution.


  • Display the execution for your the last executed statement of your session

    SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor);
    SQL_ID	dt0hqkzcdq5ny, child NUMBER 1
    SELECT * FROM TABLE(dbms_xplan.display_cursor)
    Plan hash VALUE: 3713220770
    | Id  | Operation			  | Name	   | ROWS  | Bytes | Cost (%CPU)| TIME	   |
    |   0 | SELECT STATEMENT		  |		   |	   |	   |	24 (100)|	   |
    |   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |	 4 |	 8 |	24   (0)| 00:00:01 |
       - cardinality feedback used FOR this statement
    17 ROWS selected.


  • Display the execution plan for a specific statement executed by another session:

    SQL> SELECT sid, sql_id, sql_child_number FROM v$session WHERE sid = 1934;
    ---------- ------------- ----------------
          1934 2rk5134pp7s3g		1
    SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('2rk5134pp7s3g', 1));
    SQL_ID	2rk5134pp7s3g, child NUMBER 1
    UPDATE myschema.bycicle SET comment = :comment
    WHERE id = :id
    Plan hash VALUE: 960803932
    | Id  | Operation	   | Name	    | ROWS  | Bytes | Cost (%CPU)| TIME     |
    |   0 | UPDATE STATEMENT   |		    |	    |	    |	  1 (100)|	    |
    |   1 |  UPDATE 	   | BYCICLE	    |	    |	    |		 |	    |
    |*  2 |   INDEX UNIQUE SCAN| BYCICLE#ID     |	  1 |	167 |	  1   (0)| 00:00:01 |
    Predicate Information (IDENTIFIED BY operation id):
       2 - access("ID"=:ID)
    20 ROWS selected.
  • Display execution plan for all children of a specific SQL_ID:

    SELECT * FROM TABLE(dbms_xplan.display_cursor('2rk5134pp7s3g'));


Categories: Oracle, SQLPlus, Tuning Tags:

Oracle ASM: $ORACLE_HOME/rdbms/audit keeps increasing in total size and number of files

Problem description:

The rdbms/audit directory of your Grid Infrastructure is increasing permanently in number of files and total size:

[grid@rac01 ~]$ du -hs $ORACLE_HOME/rdbms/audit
1151M	/u01/app/grid/11.2.0/grid/rdbms/audit
[grid@rac01 ~]$ 
[grid@rac01 ~]$ cd $ORACLE_HOME/rdbms/audit
[grid@rac01 audit]$ 
[grid@rac01 audit]$ ls -l | wc -l
[grid@rac01 audit]$

Permanent increase in number of files and directory size can cause the file system to run out of free space and may also have performance impact on your ASM instance.


Audit files are created by every connection as user sys. In a Real Application Cluster environment with Grid Control in place, this might become a problem (although you might want to store this information for a limited time due to security compliance reasons).

Example of an ASM .aud-file:

[grid@rac01 audit]$ cat +asm1_ora_9981_2.aud
Audit file /u01/app/grid/11.2.0/grid/rdbms/audit/+asm1_ora_9981_2.aud
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
ORACLE_HOME = /u01/app/grid/11.2.0/grid
System name:	Linux
Node name:
Release:	2.6.18-194.el5
Version:	#1 SMP Mon Mar 29 22:10:29 EDT 2010
Machine:	x86_64
Instance name: +ASM1
Redo thread mounted by this instance: 0 <none>
Oracle process number: 32
Unix process pid: 9981, image: (TNS V1-V3)
Tue Jul  5 12:12:06 2011 +02:00
LENGTH : '144'
CLIENT USER:[6] 'oracle'
STATUS:[1] '0'
DBID:[0] ''
[grid@rac01 audit]$


Old files can be deleted without any impact. Each SYS connection creates a new audit file. Old aud-files should not be open by any Oracle ASM process.

In order to cleanup old files you might want to use one or both of the following methods:

  • Manual cleanup

    You can cleanup aud-files manually by running a find command similar to the following:

    [grid@rac01 audit]$ find /u01/app/grid/11.2.0/grid/rdbms/audit -maxdepth 1 -name '*.aud' -mtime +30 -delete -print
    [grid@rac01 audit]$


  • Automatic cleanup after 30 days

    Create a cronjob similar to the following by using “crontab -e” as the Grid Infrastructure user (e.g. grid):

    [grid@rac01 ~]$ crontab -l
    # Daily cleanup job for Oracle ASM aud-files not modified in the last 30 days
    30 0 * * * /usr/bin/find /u01/app/grid/11.2.0/grid/rdbms/audit -maxdepth 1 -name '*.aud' -mtime +30 -delete >/dev/null 2>&1
    [grid@rac01 ~]$

Oracle ACFS: How to check if ACFS is supported with the kernel currently in use or gather other ACFS driver related information


You want to check if the ACFS driver can be used with the kernel currently in use. In addition you might want to gather other ACFS driver related information like if it is installed, loaded or which version is used.


The acfsdriverstate command enables us to gather ACFS driver related information (especially if ACFS is supported with the kernel in use). This binary is part of the Oracle Grid Infrastructure and can be executed as Grid Infrastructure user (e.g. grid) or root.

The following information can be gathered:

  • If ACFS can be used with the kernel currently in use:
    [grid@rac01 bin]$ $ORACLE_HOME/bin/acfsdriverstate -orahome $ORACLE_HOME supported
    ACFS-9200: Supported
    [grid@rac01 bin]$


  • If ACFS driver is installed:
    [grid@rac01 bin]$ $ORACLE_HOME/bin/acfsdriverstate -orahome $ORACLE_HOME installed
    ACFS-9203: true
    [grid@rac01 bin]$


  • If ACFS driver is loaded:
    [grid@rac01 bin]$ $ORACLE_HOME/bin/acfsdriverstate -orahome $ORACLE_HOME loaded
    ACFS-9204: false
    [grid@rac01 bin]$


  • Version of ACFS driver:
    [grid@rac01 bin]$ $ORACLE_HOME/bin/acfsdriverstate -orahome $ORACLE_HOME version
    ACFS-9325:     Driver OS kernel version = 2.6.18-8.el5(x86_64).
    ACFS-9326:     Driver Oracle version = 100804.1.
    [grid@rac01 bin]$