Archive

Archive for the ‘Enterprise Manager’ Category

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 11.2.0.3.0 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
playground
[root@racn01 ~]#

 

Oracle Grid Control: Agent Collection State Disabled by Upload Manager / How to resync/unblock a Grid Control Agent

Problem description:

Grid Control lists targets for a server as DOWN and agent has an UNKNOWN state. If you are checking the status on the agent, you will see that your agent is running, but has a Collection Status of “Disabled by Upload Manager”:

oracle@racdb01 /u01/app/agent/agent11g/bin # ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /u01/app/agent/agent11g
Agent binaries    : /u01/app/agent/agent11g
Agent Process ID  : 17105112
Parent Process ID : 8192042
Agent URL         : https://racdb01:3872/emd/main/
Repository URL    : https://gridc01.initso.at:4901/em/upload
Started at        : 2011-07-05 10:12:11
Started by user   : oracle
Last Reload       : 2011-07-05 10:12:11
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far :     0.00
Number of XML files pending upload           :      337
Size of XML files pending upload(MB)         :    50.24
Available disk space on upload filesystem    :    24.84%
Collection Status                            : Disabled by Upload Manager
Last attempted heartbeat to OMS              : 2011-07-05 10:17:07
Last successful heartbeat to OMS             : unknown
---------------------------------------------------------------
Agent is Running and Ready
oracle@racdb01 /u01/app/agent/agent11g/bin #

 
Cause:

On the Management Agents page (accessible via Setup -> Agents Subtab) agents will be marked as blocked:

 

If you display the details of the agent, Grid Control will present you the message “The Oracle Management Server(OMS) has blocked this agent because it has either been reinstalled or restored from a filesystem backup. Please click on the Agent Resynchronization button to resync the agent.”:

 


 
Problem resolution:

Click on “Agent Resynchronization” in order to resync the agent and reestablish a normal agent status (leave “Unblock agent” checked):

 

Grid Control will start to resync the agent configuration immediately:

 

Upon end of the resync process you will be redirected to the agents detail page:

 

“emctl status agent” will now point out that the agent is again able to communicate with the Oracle Management Server and upload collected data:

oracle@racdb01 /u01/app/agent/agent11g/bin # ./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /u01/app/agent/agent11g
Agent binaries    : /u01/app/agent/agent11g
Agent Process ID  : 17105112
Parent Process ID : 8192042
Agent URL         : https://racdb01:3872/emd/main/
Repository URL    : https://gridc01.initso.at:4901/em/upload
Started at        : 2011-07-05 10:12:11
Started by user   : oracle
Last Reload       : 2011-07-05 10:28:35
Last successful upload                       : 2011-07-05 10:29:27
Total Megabytes of XML files uploaded so far :     2.89
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    25.00%
Last successful heartbeat to OMS             : 2011-07-05 10:28:42
---------------------------------------------------------------
Agent is Running and Ready
oracle@racdb01 /u01/app/agent/agent11g/bin #

 
After processing by the Oracle Management Server, the status information will also update in the graphical interface:

 

Categories: Enterprise Manager, Grid Control, Oracle Tags:

Oracle Grid Control: “error: invalid compressed data to inflate” when trying do unzip Grid Control installation zip-file

Problem description:

When trying to extract the downloaded file GridControl_11.1.0.1.0_Linux_x86-64_2of3.zip for a Grid Control intallation on Linux x86_64, you are receiving an error:

gc01:/INSTALL # unzip GridControl_11.1.0.1.0_Linux_x86-64_2of3.zip
Archive:  GridControl_11.1.0.1.0_Linux_x86-64_2of3.zip
   creating: oms/Disk1/stage/Components/oracle.sysman.ocamm/
   creating: oms/Disk1/stage/Components/oracle.sysman.ocamm/11.1.0.1.0/
   creating: oms/Disk1/stage/Components/oracle.sysman.ocamm/11.1.0.1.0/1/
   creating: oms/Disk1/stage/Components/oracle.sysman.ocamm/11.1.0.1.0/1/DataFiles/
  inflating: oms/Disk1/stage/Components/oracle.sysman.ocamm/11.1.0.1.0/1/DataFiles/filegroup1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.ocamm/11.1.0.1.0/1/DataFiles/filegroup2.jar
   creating: oms/Disk1/stage/Components/oracle.sysman.paf/
   creating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/
   creating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/
   creating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup5.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup9.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup3.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup2.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup11.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup12.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup7.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup8.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup4.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup10.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup6.jar
   creating: oms/Disk1/stage/Components/oracle.sysman.agent.download/
   creating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/
   creating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/
   creating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup5.1.1.jar
  error:  invalid compressed data to inflate
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup4.1.1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup1.1.1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup2.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup6.1.1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup3.1.1.jar
gc01:/INSTALL # echo $?
2
gc01:/INSTALL #

Cause:

The file seems to be broken on the Oracle server since multiple downloads of the file always lead to a the same wrong checksum “3966614633″.

  • Checksum for downloaded file:

    oracle@gc01:/INSTALL> cksum GridControl_11.1.0.1.0_Linux_x86-64_2of3.zip
    3966614633 1589671704 GridControl_11.1.0.1.0_Linux_x86-64_2of3.zip
    oracle@gc01:/INSTALL>
  • Checksums listed by Oracle:

    GridControl_11.1.0.1.0_Linux_x86-64_1of3.zip (1,430,649,530 bytes) (cksum - 4223002664)
    GridControl_11.1.0.1.0_Linux_x86-64_2of3.zip (1,589,671,704 bytes) (cksum - 535544209)
    GridControl_11.1.0.1.0_Linux_x86-64_3of3.zip (1,408,054,645 bytes) (cksum - 2199662147)

Problem resolution:

Download the Enterprise Manager installation medias from edelivery.oracle.com. The zip-files from this installation source are not broken and will output the correct checksums:

oracle@gc01:/INSTALL> cksum V23674-01_2of3.zip
535544209 1589671704 V23674-01_2of3.zip
oracle@gc01:/INSTALL>

Extraction:

oracle@gc01:/INSTALL> unzip V23674-01_2of3.zip
Archive:  V23674-01_2of3.zip
replace oms/Disk1/stage/Components/oracle.sysman.ocamm/11.1.0.1.0/1/DataFiles/filegroup1.jar? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
  inflating: oms/Disk1/stage/Components/oracle.sysman.ocamm/11.1.0.1.0/1/DataFiles/filegroup1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.ocamm/11.1.0.1.0/1/DataFiles/filegroup2.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup5.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup9.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup3.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup2.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup11.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup12.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup7.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup8.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup4.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup10.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.paf/11.1.0.1.0/1/DataFiles/filegroup6.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup5.1.1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup4.1.1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup1.1.1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup2.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup6.1.1.jar
  inflating: oms/Disk1/stage/Components/oracle.sysman.agent.download/11.1.0.1.0/1/DataFiles/filegroup3.1.1.jar
oracle@gc01:/INSTALL> echo $?
0
oracle@gc01:/INSTALL>
Categories: Enterprise Manager, Linux, Oracle 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: Database, Enterprise Manager, Oracle, Tuning Tags:

DB Console fails to start / emagent.trc contains “ERROR ssl: nzos_Handshake failed, ret=29024″

April 14th, 2011 Matthias Pölzinger No comments

Problem description:

Oracle Enterprise Manager DB Console fails during startup without any visible reason:

TZ set to Europe/Madrid
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://myserver.mydomain:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control
.............................................................................................
failed.

The emagent.trc file contains the following messages:

2011-04-14 12:34:43 Thread-4104076192 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://myserver.mydomain:1158/em/upload/: retStatus=-1
2011-04-14 12:34:56 Thread-4130380704 WARN  http: -1,5: nmehl_httpListener: signaled to exit from emctl
2011-04-14 12:39:15 Thread-4135938752 ERROR ssl: nzos_Handshake failed, ret=29024

Cause:
You are using a secure DB Console configuration with an Oracle generated SSL-certificate. These Oracle generated SSL-certificates have a lifetime of 6 months until 10.2.0.4. Starting with 10.2.0.5 the lifetime was extended to 10 years.

If your SSL-certificate expires, you will encounter problems during startup of Enterprise Manager DB Console. Although you might be able to connect with your browser afterwards, agents will not and therefore generate “ERROR ssl: nzos_Handshake failed, ret=29024″ messages.

Problem resolution:
You have to regenerate your SSL-certificate. This can be established by “unsecure” and “secure” commands of emctl.

First stop all running DB Console components:

$ emctl stop dbconsole
TZ set to Europe/Vienna
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
https://myserver.mydomain:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
--- Failed to shutdown DBConsole Gracefully ---
 failed.
 
$ ps -ef | grep oc4j
oracle   18414 18412  7 12:37 pts/1    00:00:13 /u01/app/oracle/product/10.2.0/db_1/jdk/bin/java -server -Xmx256M -XX:MaxPermSize=96m - ....
...
oracle   22708 22837  0 12:40 pts/1    00:00:00 grep oc4j
[oracle@ora1 log]$ kill -9 18414

In the next step “unsecure” and “secure” the DB Console:

$ emctl unsecure dbconsole
TZ set to Europe/Vienna
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
https://myserver.mydomain:1158/em/console/aboutApplication
Configuring DBConsole for HTTP...   Done.
DBCONSOLE already stopped...   Done.
Agent is already stopped...   Done.
Unsecuring dbconsole...   Started.
DBConsole is now unsecured...  Done.
Unsecuring dbconsole...  Sucessful.
$
$ emctl secure dbconsole
TZ set to Europe/Vienna
Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
http://myserver.mydomain:1158/em/console/aboutApplication
Enter Enterprise Manager Root password :
Enter a Hostname for this OMS : myserver.mydomain
 
DBCONSOLE already stopped...   Done.
Agent is already stopped...   Done.
Securing dbconsole...   Started.
Checking Repository...   Done.
Checking Em Key...   Done.
Checking Repository for an existing Enterprise Manager Root Key...   Done.
Fetching Root Certificate from the Repository...   Done.
Updating HTTPS port in emoms.properties file...   Done.
Generating Java Keystore...   Done.
Securing OMS ...   Done.
Generating Oracle Wallet Password for Agent....   Done.
Generating wallet for Agent ...    Done.
Copying the wallet for agent use...    Done.
Storing agent key in repository...   Done.
Storing agent key for agent ...   Done.
Configuring Agent...
Configuring Agent for HTTPS in DBCONSOLE mode...   Done.
EMD_URL set in /u01/app/oracle/product/10.2.0/db_1/myserver_INST/sysman/config/emd.properties
   Done.
Configuring Key store..   Done.
Securing dbconsole...   Sucessful.
$

In an Oracle RAC environment remember to execute the commands on all nodes.

Categories: Enterprise Manager, Oracle Tags: