Archive

Archive for May, 2011

Nagios: HOWTO convert timestamp values to a human-readable value / logfile

Problem description:

Nagios reports all messages to /usr/local/nagios/var/log/nagios.log. Unfortunately timestamps are stored in a none-human-readable way. If you are analyzing the logfile via tail, less, more, vi, etc. you are not able to determine the actual time of an event.

Problem resolution:

You can convert the timestamp values to a humand-readable string using the following simple perl command:

cat /usr/local/nagios/var/nagios.log | perl -pe 's/(\d+)/localtime($1)/e'

Example:

[root@linux01 ~]# tail -7lf /usr/local/nagios/var/nagios.log | perl -pe 's/(\d+)/localtime($1)/e'
[Wed May 15 12:00:12 2011] Caught SIGHUP, restarting...
[Wed May 15 12:00:12 2011] Nagios 3.2.3 starting... (PID=1234)
[Wed May 15 12:00:12 2011] Local time is Wed May 15 12:00:12 CEST 2011
[Wed May 15 12:00:12 2011] LOG VERSION: 2.0
[Wed May 15 12:14:02 2011] SERVICE ALERT: linux02.mydomain.intern;Status bond0;OK;HARD;4;OK - Bonding Mode: IEEE802.3adDynamic. eth0 up eth1 up
[Wed May 15 12:14:02 2011] SERVICE NOTIFICATION: nagiosadmin;linux02.mydomain.intern;Status bond0;OK;notify-service-by-email;OK - Bonding Mode: IEEE802.3adDynamic. eth0 up eth1 up
[Wed May 15 13:00:12 2011] Auto-save of retention data completed successfully.
Categories: Uncategorized Tags:

Linux/Nagios: sudo: sorry, you must have a tty to run sudo

Problem description:

If you are using Nagios or other monitoring products to check your servers, you might come into the situation running commands which require special permissions (e.g. like running them as the root-user). You have configured sudo permissions and your check runs fine on the terminal, but if the monitoring process/service tries to execute it, you are receiving the following message:

sudo: sorry, you must have a tty to run sudo

Cause:

By default sudo only allows to be executed with a tty:

[root@linux01 ~]# cat /etc/sudoers | grep requiretty
Defaults    requiretty
[root@linux01 ~]#

Problem resolution:

You can either disable requiretty or just disable it for the user you require to use sudo without tty (PREFERRED):

[root@linux01 ~]# cat /etc/sudoers | grep requiretty
Defaults    requiretty
Defaults:myosuser !requiretty
[root@linux01 ~]#
Categories: Uncategorized Tags:

Oracle Database: HOWTO determine the granule size on your release/platform

Problem description:

Upon starting an Oracle Database Instance, memory gets allocated in granule sized memory units. If you set the sga_target parameter to a specific value (e.g. 2500MB), it will be rounded up to the next full granule (e.g. 2512MB on platforms with 16MB granule size).

The granule size depends on the platform you are running Oracle on, which version you are using and your SGA size.

Problem resolution:

To determine the granule size for your Oracle Database instance, just execute the following query:

SELECT * FROM v$sgainfo WHERE name = 'Granule Size';

Example for 11.2.0.2 on AIX:

oracle@aix01 /home/oracle # sqlplus "/ as sysdba"
 
SQL*Plus: Release 11.2.0.2.0 Production ON Mon May 22 13:18:16 2011
 
Copyright (c) 1982, 2010, Oracle.  ALL rights reserved.
 
 
Connected TO:
Oracle DATABASE 11g Release 11.2.0.2.0 - 64bit Production
WITH the REAL Application Clusters AND Automatic Storage Management options
 
SQL> SELECT * FROM v$sgainfo WHERE name = 'Granule Size';
 
NAME                                  BYTES RES
-------------------------------- ---------- ---
Granule SIZE                       16777216 No
 
SQL>
Categories: Uncategorized Tags:

Oracle Grid Infrastructure on AIX: “gipchaLowerProcessNode: no valid interfaces found”

Problem description:

While upgrading or installing Oracle Grid Infrastructure 11.2.0.2 root.sh/rootupgrade.sh fails on the second node, altough it executed without any issues on the first node:

CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node aix01, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
Timed out waiting for the CRS stack to start.
/u01/app/grid/11.2.0/grid/perl/bin/perl -I/u01/app/grid/11.2.0/grid/perl/lib -I/u01/app/grid/11.2.0/grid/crs/install /u01/app/grid/11.2.0/grid/crs/install/rootcrs.pl execution failed
root@aix02 /tmp #

The Clusterware log file $GRID_HOME/log/$HOSTNAME/alert$HOSTNAME.log contains an error messages that CRSD has failed:

2011-03-13 16:08:57.430
[ohasd(5898406)]CRS-2765:Resource 'ora.crsd' has failed on server 'aix02'.

When analyzing the $GRID_HOME/log/$HOSTNAME/crsd/crsd.log file you are finding “gipchaLowerProcessNode: no valid interfaces found” error messages:

2011-03-13 16:08:59.735: [GIPCHALO][2314] gipchaLowerProcessNode: no valid interfaces found to node for 2781285081 ms, node 112350770 { host 'aix01'
, haName '22b2-2ce9-c25b-03c6', srcLuid 5652a9fe-0e07d4a4, dstLuid 00000000-00000000 numInf 0, contigSeq 0, lastAck 0, lastValidAck 0, sendSeq [0 : 0], c
reateTime 2781285080, flags 0x4 }
2011-03-13 16:09:04.741: [GIPCHALO][2314] gipchaLowerProcessNode: no valid interfaces found to node for 2781290086 ms, node 112350770 { host 'aix01'
, haName '22b2-2ce9-c25b-03c6', srcLuid 5652a9fe-0e07d4a4, dstLuid 00000000-00000000 numInf 0, contigSeq 0, lastAck 0, lastValidAck 0, sendSeq [5 : 5], c
reateTime 2781285080, flags 0x4 }
2011-03-13 16:09:09.750: [GIPCHALO][2314] gipchaLowerProcessNode: no valid interfaces found to node for 2781295095 ms, node 112350770 { host 'aix01'
, haName '22b2-2ce9-c25b-03c6', srcLuid 5652a9fe-0e07d4a4, dstLuid 00000000-00000000 numInf 0, contigSeq 0, lastAck 0, lastValidAck 0, sendSeq [10 : 10],
 createTime 2781285080, flags 0x4 }
2011-03-13 16:09:14.751: [GIPCHALO][2314] gipchaLowerProcessNode: no valid interfaces found to node for 2781300097 ms, node 112350770 { host 'aix01'
, haName '22b2-2ce9-c25b-03c6', srcLuid 5652a9fe-0e07d4a4, dstLuid 00000000-00000000 numInf 0, contigSeq 0, lastAck 0, lastValidAck 0, sendSeq [15 : 15],
 createTime 2781285080, flags 0x4 }
2011-03-13 16:09:19.760: [GIPCHALO][2314] gipchaLowerProcessNode: no valid interfaces found to node for 2781305106 ms, node 112350770 { host 'aix01'
, haName '22b2-2ce9-c25b-03c6', srcLuid 5652a9fe-0e07d4a4, dstLuid 00000000-00000000 numInf 0, contigSeq 0, lastAck 0, lastValidAck 0, sendSeq [20 : 20],
 createTime 2781285080, flags 0x4 }
2011-03-13 16:09:24.770: [GIPCHALO][2314] gipchaLowerProcessNode: no valid interfaces found to node for 2781310115 ms, node 112350770 { host 'aix01'
, haName '22b2-2ce9-c25b-03c6', srcLuid 5652a9fe-0e07d4a4, dstLuid 00000000-00000000 numInf 0, contigSeq 0, lastAck 0, lastValidAck 0, sendSeq [25 : 25],
 createTime 2781285080, flags 0x4 }

Cause:

  • AIX:
    On AIX this problem is caused by an incorrect “udp_sendspace” setting. You can check your current value by executing the following command:

    no -o udp_sendspace

    Example:

    root@aix02 / # no -o udp_sendspace
    udp_sendspace = 65536
    root@aix02 / #

    Additionally you should check the other network related parameters which are required for Oracle Grid Infrastructure 11.2.0.2:

    AIX network parameter recommended/required value
    ipqmaxlen 512
    rfc1323 1
    sb_max 4194304
    tcp_recvspace 65536
    tcp_sendspace 65536
    udp_recvspace 655360
    udp_sendspace 65536

  • Other UNIX-systems:
    On other UNIX-systems you might want to check your netmask for the interconnect interfaces (255.255.255.0).

Problem resolution on AIX:

  1. Check and set the correct values for the required network related parameters on all nodes:

    no -o ipqmaxlen=512
    no -o rfc1323=1
    no -o sb_max=4194304
    no -o tcp_recvspace=65536
    no -o tcp_sendspace=65536
    no -o udp_recvspace=65536
    no -o udp_sendspace=65536
  2. In order to make this changes permanent checkout http://download.oracle.com/docs/cd/E11882_01/install.112/e17210/preaix.htm#CWAIX219

    Example if you are not running in compatibility mode:

    no -o -r ipqmaxlen=512
    no -o -p rfc1323=1
    no -o -p sb_max=4194304
    no -o -p tcp_recvspace=65536
    no -o -p tcp_sendspace=65536
    no -o -p udp_recvspace=65536
    no -o -p udp_sendspace=65536
  3. Stop CRS on all nodes:

    crsctl stop crs -f
    ps -ef |grep d.bin
  4. Restart CRS on the first node:

    crsctl start crs
  5. On the node were root.sh/rootupgrade.sh failed, rerun the script

    Example for reexecuting root.sh:

    root@aix02 /tmp # /u01/app/grid/11.2.0/grid/root.sh
    Running Oracle 11g root script...
     
    The following environment variables are set as:
        ORACLE_OWNER= grid
        ORACLE_HOME=  /u01/app/grid/11.2.0/grid
     
    Enter the full pathname of the local bin directory: [/usr/local/bin]:
    The contents of "dbhome" have not changed. No need to overwrite.
    The contents of "oraenv" have not changed. No need to overwrite.
    The contents of "coraenv" have not changed. No need to overwrite.
     
    Entries will be added to the /etc/oratab file as needed by
    Database Configuration Assistant when a database is created
    Finished running generic part of root script.
    Now product-specific root actions will be performed.
    Using configuration parameter file: /u01/app/grid/11.2.0/grid/crs/install/crsconfig_params
    Adding daemon to inittab
    Configure Oracle Grid Infrastructure for a Cluster ... succeeded
    root@aix02 /tmp #
Categories: Uncategorized Tags:

Oracle Grid Infrastructure on AIX: “0403-006 Execute permission denied.” during installation of binaries.

Problem description:

While installing Grid Infrastructure on AIX, you are receiving an error when Oracle Universal Installer starts to link the binaries. The OUI log file contains the error message “0403-006 Execute permission denied.”:

INFO: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'sdo_on no_opts asm_on rac_on' of makefile '/u01/app/grid/11.2.0/grid/rdbms/lib/ins_rdbms.mk'. See '/u01/app/oraInventory/logs/installActions2011-03-12_11-32-45PM.log' for details.
Exception Severity: 1
INFO: Linking sdo Options
INFO: Linking sdo Options
INFO: The output of this make operation is also available at: '/u01/app/grid/11.2.0/grid/install/make.log'
INFO:
 
INFO: Start output from spawned process:
INFO: ----------------------------------
INFO:
 
INFO:   /bin/ar -X64 cr /u01/app/grid/11.2.0/grid/rdbms/lib/libknlopt.a /u01/app/grid/11.2.0/grid/rdbms/lib/kxmwsd.o
 
INFO: /bin/sh: /u01/app/grid/11.2.0/grid/bin/echodo: 0403-006 Execute permission denied.
 
INFO: make: 1254-004 The error code from the last command is 126.

Cause:
In this case the error message was quite misleading. The problem was not caused by a permission issue. It was introduced by another process using some space on the /tmp file system. Oracle Universal Installer was not able to allocate any further temporary space on the /tmp file system, because it was completely filled up and therefore raised above error message. Even if you would use the environment variables TMPDIR and TEMPDIR, OUI would still use /tmp for some tasks and 1GB of free space would not be enough.

Problem resolution:
Increase the free space on /tmp (2GB of total space should be fine) and press retry in the Oracle Universal Installer or cleanup and restart your installation if you have already cancelled it.

root@aix01 / # df -g /tmp
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd3           2.00      1.99    1%      151     1% /tmp
root@aix01 / #
Categories: Uncategorized Tags:

Oracle: HOWTO run the SQL Tuning Advisor without Enterprise Manager DB Console or Grid Control

Problem description:

You want to run the SQL Tuning Advisor, because of a very resource consuming SQL statement, but no Enterprise Manager component is installed or configured for performance reasons or because of troubles in the past.

Problem resolution:
It is not necessary to run Enterprise Manager in order to make use of the SQL Tuning Advisor. Here is a short how to for running the SQL Tuning Advisor on a SQL statement currently using a lot of resources after updating the schema statistics.

  • First of all create a tuning task:

    DECLARE
      l_sql_tune_task_id  VARCHAR2(100);
    BEGIN
      l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                              sql_id      => '&&my_sql_id',
                              scope       => DBMS_SQLTUNE.scope_comprehensive,
                              time_limit  => 60,
                              task_name   => 'sql_tuning_task_&&my_sql_id',
                              description => 'Tuning task for statement &&my_sql_id.');
      DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
    END;
    /

    Output:

    SQL> SET serveroutput ON
    SQL> 
    SQL> DECLARE
      2   l_sql_tune_task_id  VARCHAR2(100);
      3  BEGIN
      4    l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
      5                            sql_id      => '&&my_sql_id',
      6                            scope       => DBMS_SQLTUNE.scope_comprehensive,
      7                            time_limit  => 60,
      8                            task_name   => 'sql_tuning_task_&&my_sql_id',
      9                            description => 'Tuning task for statement &&my_sql_id.');
     10    DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
     11  END;
     12  /
     
    Enter VALUE FOR my_sql_id: 2vbmgruzfy58y
    old   5:			   sql_id      => '&&my_sql_id',
    NEW   5:			   sql_id      => '2vbmgruzfy58y',
    old   8:			   task_name   => 'sql_tuning_task_&&my_sql_id',
    NEW   8:			   task_name   => 'sql_tuning_task_2vbmgruzfy58y',
    old   9:			   description => 'Tuning task for statement &&my_sql_id.');
    NEW   9:			   description => 'Tuning task for statement 2vbmgruzfy58y');
    l_sql_tune_task_id: sql_tuning_task_2vbmgruzfy58y
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>

  • If you want, you can check the status of your newly created tuning task by querying dba_advisor_log:

    SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

    Output:

    SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
    OLD   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
    NEW   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2vbmgruzfy58y'
     
    TASK_NAME		       STATUS
    ------------------------------ -----------
    sql_tuning_task_2vbmgruzfy58y  INITIAL
     
    SQL>

  • Next step is to actually execute your SQL Tuning task:

    EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

    Output:

    SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>

  • Status will change from INITIAL to COMPLETED:

    SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

    Output:

    SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';
    OLD   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'
    NEW   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_2vbmgruzfy58y'
     
    TASK_NAME		       STATUS
    ------------------------------ -----------
    sql_tuning_task_2vbmgruzfy58y  COMPLETED
     
    SQL>

  • Now you can review the recommendations generated by the SQL Tuning Advisor:

    SET LINES 150
    SET pages 50000
    SET long 5000000
    SET longc 5000000
     
    SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;

    Output:

    SQL> SET LINES 150
    SQL> SET pages 50000
    SQL> SET long 5000000
    SQL> SET longc 5000000
    SQL> 
    SQL> SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;
    OLD   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual
    NEW   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_2vbmgruzfy58y') AS recommendations FROM dual
     
    RECOMMENDATIONS
    ------------------------------------------------------------------------------------------------------------------------------------------------------
    GENERAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    Tuning Task Name   : sql_tuning_task_2vbmgruzfy58y
    Tuning Task Owner  : SYS
    Workload TYPE	   : Single SQL Statement
    Scope		   : COMPREHENSIVE
    TIME LIMIT(seconds): 60
    Completion STATUS  : COMPLETED
    Started at	   : 05/20/2011 09:44:13
    Completed at	   : 05/20/2011 09:44:33
     
    -------------------------------------------------------------------------------
    Schema Name: SYS
    SQL ID	   : 2vbmgruzfy58y
    SQL Text   : SELECT COUNT(*) FROM dba_tables, dba_users
     
    -------------------------------------------------------------------------------
    FINDINGS SECTION (3 findings)
    -------------------------------------------------------------------------------
     
    1- Restructure SQL finding (see plan 1 IN EXPLAIN plans SECTION)
    ----------------------------------------------------------------
      An expensive cartesian product operation was found at line ID 24 OF the
      execution plan.
     
      Recommendation
      --------------
      - Consider removing the disconnected TABLE OR VIEW FROM this statement OR
        ADD a JOIN condition which refers TO it.
     
    2- Restructure SQL finding (see plan 1 IN EXPLAIN plans SECTION)
    ----------------------------------------------------------------
      An expensive cartesian product operation was found at line ID 17 OF the
      execution plan.
     
      Recommendation
      --------------
      - Consider removing the disconnected TABLE OR VIEW FROM this statement OR
        ADD a JOIN condition which refers TO it.
     
    3- Restructure SQL finding (see plan 1 IN EXPLAIN plans SECTION)
    ----------------------------------------------------------------
      An expensive cartesian product operation was found at line ID 15 OF the
      execution plan.
     
      Recommendation
      --------------
      - Consider removing the disconnected TABLE OR VIEW FROM this statement OR
        ADD a JOIN condition which refers TO it.
     
    -------------------------------------------------------------------------------
    EXPLAIN PLANS SECTION
    -------------------------------------------------------------------------------
     
    1- Original
    -----------
    Plan hash VALUE: 674672025
     
    ------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation				     | Name		       | ROWS  | Bytes | Cost (%CPU)| TIME     |
    ------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT			     |			       |     1 |   227 |   380	 (3)| 00:00:05 |
    |   1 |  SORT AGGREGATE 			     |			       |     1 |   227 |	    |	       |
    |*  2 |   HASH JOIN				     |			       | 15530 |  3442K|   380	 (3)| 00:00:05 |
    |   3 |    TABLE ACCESS FULL			     | TS$		       |     7 |    21 |     4	 (0)| 00:00:01 |
    |*  4 |    HASH JOIN RIGHT OUTER		     |			       | 15530 |  3397K|   376	 (3)| 00:00:05 |
    |   5 |     INDEX FULL SCAN			     | I_USER2		       |    45 |   180 |     1	 (0)| 00:00:01 |
    |*  6 |     HASH JOIN RIGHT OUTER		     |			       | 15530 |  3336K|   374	 (3)| 00:00:05 |
    |   7 |      INDEX FAST FULL SCAN		     | I_OBJ1		       | 15494 |   121K|    16	 (0)| 00:00:01 |
    |*  8 |      HASH JOIN RIGHT OUTER		     |			       | 15530 |  3215K|   357	 (2)| 00:00:05 |
    |   9 |       INDEX FAST FULL SCAN		     | I_OBJ1		       | 15494 | 77470 |    16	 (0)| 00:00:01 |
    |* 10 |       HASH JOIN RIGHT OUTER		     |			       | 15530 |  3139K|   341	 (3)| 00:00:05 |
    |  11 |        TABLE ACCESS FULL		     | SEG$		       |  4081 | 44891 |    34	 (0)| 00:00:01 |
    |* 12 |        HASH JOIN			     |			       |  9971 |  1908K|   306	 (2)| 00:00:04 |
    |* 13 | 	TABLE ACCESS FULL		     | TAB$		       |  1200 | 33600 |   223	 (0)| 00:00:03 |
    |* 14 | 	HASH JOIN			     |			       |   128K|    20M|    82	 (7)| 00:00:01 |
    |  15 | 	 MERGE JOIN CARTESIAN		     |			       |   374 | 58718 |    28	(11)| 00:00:01 |
    |* 16 | 	  HASH JOIN			     |			       |     8 |  1224 |    24	(13)| 00:00:01 |
    |  17 | 	   MERGE JOIN CARTESIAN 	     |			       |     8 |  1120 |    24	(13)| 00:00:01 |
    |* 18 | 	    HASH JOIN			     |			       |     8 |   680 |    24	(13)| 00:00:01 |
    |* 19 | 	     HASH JOIN			     |			       |     8 |   656 |    19	(11)| 00:00:01 |
    |* 20 | 	      HASH JOIN 		     |			       |     8 |   632 |    15	(14)| 00:00:01 |
    |* 21 | 	       HASH JOIN OUTER		     |			       |     8 |   608 |    12	 (9)| 00:00:01 |
    |* 22 | 		HASH JOIN		     |			       |     8 |   376 |    10	(10)| 00:00:01 |
    |  23 | 		 NESTED LOOPS		     |			       |     8 |   360 |     7	 (0)| 00:00:01 |
    |  24 | 		  MERGE JOIN CARTESIAN	     |			       |     1 |    16 |     4	 (0)| 00:00:01 |
    |* 25 | 		   TABLE ACCESS FULL	     | PROFILE$ 	       |     1 |     8 |     2	 (0)| 00:00:01 |
    |  26 | 		   BUFFER SORT		     |			       |     1 |     8 |     2	 (0)| 00:00:01 |
    |* 27 | 		    TABLE ACCESS FULL	     | PROFILE$ 	       |     1 |     8 |     2	 (0)| 00:00:01 |
    |* 28 | 		  TABLE ACCESS BY INDEX ROWID| USER$		       |    12 |   348 |     3	 (0)| 00:00:01 |
    |* 29 | 		   INDEX SKIP SCAN	     | I_USER2		       |    12 |       |     1	 (0)| 00:00:01 |
    |  30 | 		 TABLE ACCESS FULL	     | PROFNAME$	       |     1 |     2 |     2	 (0)| 00:00:01 |
    |* 31 | 		TABLE ACCESS FULL	     | RESOURCE_GROUP_MAPPING$ |     1 |    29 |     2	 (0)| 00:00:01 |
    |  32 | 	       TABLE ACCESS FULL	     | USER_ASTATUS_MAP        |     9 |    27 |     2	 (0)| 00:00:01 |
    |  33 | 	      TABLE ACCESS FULL 	     | TS$		       |     7 |    21 |     4	 (0)| 00:00:01 |
    |  34 | 	     TABLE ACCESS FULL		     | TS$		       |     7 |    21 |     4	 (0)| 00:00:01 |
    |  35 | 	    BUFFER SORT 		     |			       |     1 |    55 |    20	(15)| 00:00:01 |
    |* 36 | 	     FIXED TABLE FULL		     | X$KSPPI		       |     1 |    55 |     0	 (0)| 00:00:01 |
    |  37 | 	   FIXED TABLE FULL		     | X$KSPPCV 	       |   100 |  1300 |     0	 (0)| 00:00:01 |
    |  38 | 	  BUFFER SORT			     |			       |    45 |   180 |    28	(11)| 00:00:01 |
    |  39 | 	   INDEX FAST FULL SCAN 	     | I_USER2		       |    45 |   180 |     1	 (0)| 00:00:01 |
    |* 40 | 	 TABLE ACCESS FULL		     | OBJ$		       | 15494 |   166K|    52	 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (IDENTIFIED BY operation id):
    ---------------------------------------------------
     
       2 - access("T"."TS#"="TS"."TS#")
       4 - access("CX"."OWNER#"="CU"."USER#"(+))
       6 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
       8 - access("T"."BOBJ#"="CO"."OBJ#"(+))
      10 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND "T"."TS#"="S"."TS#"(+))
      12 - access("O"."OBJ#"="T"."OBJ#")
      13 - FILTER(BITAND("T"."PROPERTY",1)=0)
      14 - access("O"."OWNER#"="U"."USER#")
      16 - access("KSPPI"."INDX"="KSPPCV"."INDX")
      18 - access("U"."TEMPTS#"="TTS"."TS#")
      19 - access("U"."DATATS#"="DTS"."TS#")
      20 - access("U"."ASTATUS"="M"."STATUS#")
      21 - access("CGM"."VALUE"(+)="U"."NAME")
      22 - access("U"."RESOURCE$"="P"."PROFILE#")
      25 - FILTER("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1 AND "DP"."PROFILE#"=0)
      27 - FILTER("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
      28 - FILTER("U"."RESOURCE$"="PR"."PROFILE#")
      29 - access("U"."TYPE#"=1)
           FILTER("U"."TYPE#"=1)
      31 - FILTER("CGM"."ATTRIBUTE"(+)='ORACLE_USER' AND "CGM"."STATUS"(+)='ACTIVE')
      36 - FILTER("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
      40 - FILTER(BITAND("O"."FLAGS",128)=0)
     
    -------------------------------------------------------------------------------
     
     
    SQL>

  • If you have reviewed your recommendations, you can remove the task by simply calling drop_tuning_task:

    BEGIN
      DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
    END;
    /

    Output:

    SQL> BEGIN
      2    DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
      3  END;
      4  /
     
    old   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');
    NEW   2:   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_2vbmgruzfy58y');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
Categories: Uncategorized Tags:

PRKP-1030 / CRS-2632 / CRS-0223 when trying to start database service

Problem description:

You are trying to start your database service(s) after some maintenance work and the following error stack is reported even though your database instances are already up & running:

[oracle@linux01 bin]$ srvctl start service -d mydb
PRKP-1030 : Failed to start the service myappsrv.
CRS-2632: There are no more servers to try to place resource 'ora.mydb.myappsrv.cs' on that would satisfy its placement policy
CRS-0223: Resource 'ora.mydb.myappsrv.cs' has placement error.
[oracle@linux01 bin]$

Cause:
Your database instance(s) are still in RESTRICTED mode.

Problem resolution:
Disable restricted session mode and retry to start your service(s):

  • Disable restricted session mode:
    SQL> ALTER system disable restricted SESSION;
     
    System altered.
     
    SQL>
  • Start your service(s):
    [oracle@linux01 bin]$ srvctl start service -d mydb
    [oracle@linux01 bin]$
Categories: Uncategorized Tags:

ORA-00600 [kfk_load_by_idx_in_pga9] / ORA-15186 when using sqlplus as none-oracle OS user

Problem description:

When executing sqlplus as none-oracle OS user and connecting to the Oracle database using the bequeath protocol (e.g. sys as sysdba), you are receiving the following error messages:

Tue May 02 19:27:06 2011
ERROR: asm_init(): asm_erc:-5 msg:Driver not installed pid:39333
Errors in file /u01/app/oracle/diag/rdbms/mydb/mydb1/trace/mydb1_ora_39333.trc  (incident=13810):
ORA-00600: internal error code, arguments: [kfk_load_by_idx_in_pga9], [1], [0], [], [], [], [], [], [], [], [], []
ORA-15186: ASMLIB error function = [asm_init],  error = [18446744073709551611],  mesg = [Driver not installed]
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 11900
Tue May 02 19:27:09 2011
Sweep Incident[13810]: completed

Cause:
You are running your database on Grid Infrastructure/ASM. When connecting for instance as root user and sqlplus/your server process tries to access the datafiles, you will receive above error messages.

Problem resolution:

  • Use sqlplus as the oracle OS user you are running the database
  • Connect via SQL*Net
  • If it’s really necessary to run sqlpus via bequeath protocol with this specific OS user, add him to the OS group configured as asmdba group (e.g. asmdba)

Example connect as root-user after adding the account to the asmadmin group:

[root@linux01 ~]# $ORACLE_HOME/bin/sqlplus "sys as sysdba"
 
SQL*Plus: Release 11.1.0.7.0 - Production on Tue May 2 23:50:10 2011
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
Enter password:
 
Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
With the Real Application Clusters option
 
SQL>
SQL> select count(*) from dba_segments;
 
  COUNT(*)
----------
     39496
 
SQL> exit
Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
With the Real Application Clusters option
[root@linux01 ~]#
Categories: Uncategorized Tags:

Navisphere: hostname.domain: is not a Managed Host, unable to retrieve Remote Agent Configuration.

Problem description:

You have installed the Navisphere Host Agent and configured the agent appropriately. Navisphere on storage side still has problems to detect the host and displays messages like “hostname.domain: is not a Managed Host, unable to retrieve Remote Agent Configuration.”:



Cause:
Navisphere has problems to contact the host agent. This can have several causes like:

  • Blocked firewall ports
  • Multiple NICs

Problem resolution:
In my case, the problem was caused by multiple installed network interfaces. In this scenario Navisphere Host Agent does not work properly without creating a special configuration file.

If you have multiple NICs, you have to create the agentID.txt file containing the hostname and IP-address to use. Example:

[root@linux01 /]# cat /agentID.txt
linux01.initso.local
192.168.100.101
[root@linux01 /]#

Afterwards restart the Navisphere Host Agent:

[root@linux01 bin]# service naviagent restart
Shutting down Naviagent:                                   [  OK  ]
Starting Navisphere agent:                                 [  OK  ]
[root@linux01 bin]#
Categories: Uncategorized Tags:

Grid Infrastructure: [INS-30060] Check for group existance failed. during installation

Problem description:

When trying to install Grid Infrastructure 11.2.0.2 Oracle Universal Installer raises the error message “[INS-30060] Check for group existance failed.” during the validation of the chosen OS-ASM-groups:



Cause:
The OUI module to check the requirements on 11gR2 creates a CVU-directory under /tmp (e.g. during GI installation /tmp/CVU_11.2.0.2.0_grid). If this directory already exists, it is reused.

If the directory already exists (because of a canceled installation) and permissions are not set correctly, then you will experience an INS-30060.

In addition OUI will also raise this error if HPUX-Patch “PHCO_40381″ is missing.

Problem resolution:
Change the permissions of the directory accordingly for your installation user (e.g. grid:oinstall):

chown -R grid:oinstall /tmp/CVU_11.2.0.2.0_grid
Categories: Uncategorized Tags: