Oracle Enterprise Manager: Cannot communicate securely with peer: no common encryption algorithm(s). (Error code: ssl_error_no_cypher_overlap)

When attempting to connect to the Oracle Enterprise Manager using  Firefox 15.0.1 or later, the following message is displayed:
Cannot communicate securely with peer: no common encryption algorithm(s).  (Error code: ssl_error_no_cypher_overlap)

Solution
To allow secure connection to the Oracle Enterprise Manager with Firefox 15.0.1 and later, you must ensure that TLS has been enabled.

To do this, complete the following steps:

        In Firefox, click Tools > Options.
        In the Options notebook, click the Advanced tab.
        Click the Encryption tab.
        In the Protocols section, click Use TLS 1.0.
        Click OK.

      






















Attempt to re-connect to the Oracle Enterprise Manager page. An Untrusted Connection message is displayed.
        Click Add Exception. The Add Security Exception window is displayed.
        Click Confirm Security Exception. The Oracle Enterprise Manager page is displayed

Accessing GUI running on AIX using putty

I usually use putty to access my remote AIX server, and then I stat the vnc server on it and connect from my Windows XP laptop using the vnc client to access any GUI like the Oracle runInstaller, etc.

There are time when I would have to connect to a remote AIX machine which does not have vncserver on it, so at that time I would access the GUI from putty using ssh X11 forwarding.

For this the pre-requisite is to have ssh and ssl installed and running on the AIX server. Open SSH and Open SSL can be installed on an AIX 7.1 host following the steps in my earlier post http://shettymayur.blogspot.com/2012/09/open-secure-shellssh-and-open-secure.html

With the help of the instructions on the following links http://mynotes.wordpress.com/2009/12/11/setting-x-display-using-putty-and-xming/ and http://tacomadata.com/node/15 I was able to able to access the GUI (xclock, runinstaller, etc.)

Configuring PuTTY:

Using putty I created an SSH session to access the AIX server.

Configuring Xming:

Install Xming from http://sourceforge.net/projects/xming/

On starting Xming you should see this at the bottom panned on your Windows XP machine.

 

Configure sshd on AIX:

Edit /etc/ssh/sshd_config , uncomment and add the following:

X11Forwarding yes
X11UseLocalhost yes
XauthLocation /usr/bin/X11/xauth

Connect to the AIX host test.ibm.com using the ssh session that was created earlier, and call xclock to test. Xclock should be displayed on the Windows machine.

test> echo $DISPLAY

localhost:10.0

test> xclock

Open Secure Shell(SSH) and Open Secure Socket Layer(SSL) on AIX 7.1

I installed Open Secure Shell(SSH) and Open Secure Socket Layer(SSL) on my AIX 7.1 host, but when I did a sshd -V I got the below error message.

isvp14_ora> /usr/sbin/sshd -V OpenSSL version mismatch. Built against 908070, you have 90812f

Here we see the list of SSH and SSL software that is currently installed on the AIX host.

isvp14_ora> lslpp -l | grep ssh   openssh.base.server     4.7.0.5301  COMMITTED  Open Secure Shell Server isvp14_ora> lslpp -l | grep openssh   openssh.base.server     4.7.0.5301  COMMITTED  Open Secure Shell Server isvp14_ora> lslpp -l | grep ssl   openssl.base            0.9.8.1802  COMMITTED  Open Secure Socket Layer   openssl.license         0.9.8.1802  COMMITTED  Open Secure Socket License   openssl.man.en_US       0.9.8.1802  COMMITTED  Open Secure Socket Layer   openssl.base            0.9.8.1802  COMMITTED  Open Secure Socket Layer isvp14_ora>

I deleted the SSH solftware and started afresh with the compatible versions, by installing
from OpenSSH_5.8.0.6102.tar I got the from
http://www-03.ibm.com/systems/power/software/aix/expansionpack/index.html

On untarring OpenSSH_5.8.0.6102.tar I saw the following files in the directory.

isvp14_ora> ls .toc                    openssh.msg.Ja_JP       openssh.msg.es_ES OpenSSH_5.8.0.6102.tar  openssh.msg.KO_KR       openssh.msg.fr_FR openssh.base            openssh.msg.PL_PL       openssh.msg.hu_HU openssh.license         openssh.msg.PT_BR       openssh.msg.it_IT openssh.man.en_US       openssh.msg.RU_RU       openssh.msg.ja_JP openssh.msg.CA_ES       openssh.msg.SK_SK       openssh.msg.ko_KR openssh.msg.CS_CZ       openssh.msg.ZH_CN       openssh.msg.pl_PL openssh.msg.DE_DE       openssh.msg.ZH_TW       openssh.msg.pt_BR openssh.msg.EN_US       openssh.msg.Zh_CN       openssh.msg.ru_RU openssh.msg.ES_ES       openssh.msg.Zh_TW       openssh.msg.sk_SK openssh.msg.FR_FR       openssh.msg.ca_ES       openssh.msg.zh_CN openssh.msg.HU_HU       openssh.msg.cs_CZ       openssh.msg.zh_TW openssh.msg.IT_IT       openssh.msg.de_DE       openssl-0.9.8.1802 openssh.msg.JA_JP       openssh.msg.en_US       openssl-0.9.8.1802.tar

I then installed the openssh.base using smitty, here is the new version of ssh on the AIX machine.
NOTE: Remember to accept the license agreement while installing using smitty.

isvp14_ora> lslpp -l | grep ssh   openssh.base.client     5.8.0.6102  COMMITTED  Open Secure Shell Commands   openssh.base.server     5.8.0.6102  COMMITTED  Open Secure Shell Server   openssh.license         5.8.0.6102  COMMITTED  Open Secure Shell License   openssh.man.en_US       5.8.0.6102  COMMITTED  Open Secure Shell   openssh.msg.en_US       5.8.0.6102  COMMITTED  Open Secure Shell Messages -   openssh.base.client     5.8.0.6102  COMMITTED  Open Secure Shell Commands   openssh.base.server     5.8.0.6102  COMMITTED  Open Secure Shell Server isvp14_ora>

isvp14_ora> /usr/sbin/sshd -V sshd: illegal option -- V OpenSSH_5.8p1, OpenSSL 0.9.8r 8 Feb 2011 usage: sshd [-46Ddeiqt] [-b bits] [-f config_file] [-g login_grace_time]                            [-h host_key_file] [-k key_gen_time] [-o option] [-p port] [-u len] isvp14_ora>

To configure passwordless ssh on Oracle RAC nodes here is link to the Oracle docs that talk about it.
http://docs.oracle.com/cd/E11882_01/install.112/e24614/manpreins.htm

Oracle Advanced Compression using Swingbench Order Entry Schema with Scale Factor 100

In this entry I will talk about my experience using Swingbench Version 2.4.0.845 where I used Oracle Advanced Compression while creating the Order Entry Schema with scale factor 100

Here is the Order Entry schema diagram that I reverse engineered using DbWrench

bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 27 09:14:53 2012 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

We can see all the tablespaces that are part of the database.

SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS SOE 6 rows selected.

The Swingbench user is called SOE, so here we look into all the tables owner by that user 

SQL> select table_name from dba_tables where owner='SOE' and partitioned='NO' ; TABLE_NAME ------------------------------ CUSTOMERS WAREHOUSES ORDER_ITEMS ORDERS INVENTORIES PRODUCT_INFORMATION LOGON PRODUCT_DESCRIPTIONS ORDERENTRY_METADATA 9 rows selected.

Below we see the file id and file name that is being used by the tablespace SOE

SQL> select distinct ddf.file_id "File ID", ddf.file_name "File Name" from dba_extents de, dba_data_files ddf where de.file_id = ddf.file_id and ddf.tablespace_name = 'SOE';    File ID ---------- File Name --------------------------------------------------------------------------------          5 +DATA/testdb/datafile/soe.dbf

Now we check to see if Compression has been enabled, and the kink of compression we are using.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM dba_tables where owner='SOE'; TABLE_NAME                     COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ CUSTOMERS                      ENABLED  OLTP WAREHOUSES                     ENABLED  OLTP ORDER_ITEMS                    ENABLED  OLTP ORDERS                         ENABLED  OLTP INVENTORIES                    ENABLED  OLTP PRODUCT_INFORMATION            ENABLED  OLTP LOGON                          ENABLED  OLTP PRODUCT_DESCRIPTIONS           ENABLED  OLTP ORDERENTRY_METADATA            DISABLED 9 rows selected.

Below we see all the indxes associated with each of the table created for the Order Entry Schema

SQL> select TABLE_NAME, INDEX_NAME from dba_indexes where OWNER='SOE'; TABLE_NAME                     INDEX_NAME ------------------------------ ------------------------------ CUSTOMERS                      CUSTOMERS_PK CUSTOMERS                      CUST_UPPER_NAME_IX CUSTOMERS                      CUST_ACCOUNT_MANAGER_IX CUSTOMERS                      CUST_LNAME_IX CUSTOMERS                      CUST_EMAIL_IX INVENTORIES                    INVENTORY_PK INVENTORIES                    INV_PRODUCT_IX INVENTORIES                    INV_WAREHOUSE_IX ORDERS                         ORDER_PK ORDERS                         ORD_CUSTOMER_IX ORDERS                         ORD_SALES_REP_IX TABLE_NAME                     INDEX_NAME ------------------------------ ------------------------------ ORDERS                         ORD_ORDER_DATE_IX ORDERS                         ORD_WAREHOUSE_IX ORDER_ITEMS                    ORDER_ITEMS_PK ORDER_ITEMS                    ITEM_ORDER_IX ORDER_ITEMS                    ITEM_PRODUCT_IX PRODUCT_DESCRIPTIONS           PRD_DESC_PK PRODUCT_DESCRIPTIONS           PROD_NAME_IX PRODUCT_INFORMATION            PRODUCT_INFORMATION_PK PRODUCT_INFORMATION            PROD_CATEGORY_IX PRODUCT_INFORMATION            PROD_SUPPLIER_IX WAREHOUSES                     WAREHOUSES_PK TABLE_NAME                     INDEX_NAME ------------------------------ ------------------------------ WAREHOUSES                     WHS_LOCATION_IX 23 rows selected.

Here we check to see if compression has been enabled for each of the indexes associated with the table.

SQL> select INDEX_NAME , TABLE_NAME, COMPRESSION from dba_indexes where OWNER='SOE'; INDEX_NAME                     TABLE_NAME                     COMPRESS ------------------------------ ------------------------------ -------- CUSTOMERS_PK                   CUSTOMERS                      DISABLED CUST_UPPER_NAME_IX             CUSTOMERS                      DISABLED CUST_ACCOUNT_MANAGER_IX        CUSTOMERS                      DISABLED CUST_LNAME_IX                  CUSTOMERS                      DISABLED CUST_EMAIL_IX                  CUSTOMERS                      DISABLED INVENTORY_PK                   INVENTORIES                    DISABLED INV_PRODUCT_IX                 INVENTORIES                    DISABLED INV_WAREHOUSE_IX               INVENTORIES                    DISABLED ORDER_PK                       ORDERS                         DISABLED ORD_CUSTOMER_IX                ORDERS                         DISABLED ORD_SALES_REP_IX               ORDERS                         DISABLED INDEX_NAME                     TABLE_NAME                     COMPRESS ------------------------------ ------------------------------ -------- ORD_ORDER_DATE_IX              ORDERS                         DISABLED ORD_WAREHOUSE_IX               ORDERS                         DISABLED ORDER_ITEMS_PK                 ORDER_ITEMS                    DISABLED ITEM_ORDER_IX                  ORDER_ITEMS                    DISABLED ITEM_PRODUCT_IX                ORDER_ITEMS                    DISABLED PRD_DESC_PK                    PRODUCT_DESCRIPTIONS           DISABLED PROD_NAME_IX                   PRODUCT_DESCRIPTIONS           DISABLED PRODUCT_INFORMATION_PK         PRODUCT_INFORMATION            DISABLED PROD_CATEGORY_IX               PRODUCT_INFORMATION            DISABLED PROD_SUPPLIER_IX               PRODUCT_INFORMATION            DISABLED WAREHOUSES_PK                  WAREHOUSES                     DISABLED INDEX_NAME                     TABLE_NAME                     COMPRESS ------------------------------ ------------------------------ -------- WHS_LOCATION_IX                WAREHOUSES                     DISABLED 23 rows selected.

The below sql statement gives us more information on the number of rows in each of the tables, and the number of blocks used by the table.

SQL> SELECT table_name, NUM_ROWS, blocks as BLOCKS_USED, empty_blocks FROM dba_tables WHERE owner='SOE'; TABLE_NAME                       NUM_ROWS BLOCKS_USED EMPTY_BLOCKS ------------------------------ ---------- ----------- ------------ CUSTOMERS                       400000000     3906292            0 WAREHOUSES                           1000          60            0 ORDER_ITEMS                    1350084849     3972147            0 ORDERS                          450000000     3233681            0 INVENTORIES                        899915       20941            0 PRODUCT_INFORMATION                  1000          60            0 LOGON                           100000000      176240            0 PRODUCT_DESCRIPTIONS                 1000          60            0 ORDERENTRY_METADATA                     4          60            0 9 rows selected. SQL>

Here we look to see the size of each of the table in KB. 
As per this excellent article by Burleson Consulting  you need to ask yourself exactly which Oracle table size, you wish to query:

• Do you want only the row space consumed? ( select avg_row_len*num_rows from dba_tables)
• Do you want to include allocated file space for the table? (select . . . from dba_segments)
• Do you want to include un-used extent space? (select . . . from dba_data_files, dba_extents . . )
• Do you want to include un-used space up to the high water mark? This may over-estimate the real Oracle table size.
• Do you want table sizes for Oracle growth monitoring?

The query below gives us the size in terms of number of row space consumed
 

SQL> select TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) SIZE_KB from dba_tables WHERE owner='SOE'; TABLE_NAME                        SIZE_KB ------------------------------ ---------- CUSTOMERS                        30859375 WAREHOUSES                          26.37 ORDER_ITEMS                    27687286.9 ORDERS                         21972656.3 INVENTORIES                      12303.53 PRODUCT_INFORMATION                172.85 LOGON                          1269531.25 PRODUCT_DESCRIPTIONS               210.94 ORDERENTRY_METADATA                    .1 9 rows selected. SQL>

The query below gives the table size that includes the unused extents space

SQL>  select segment_name table_name, sum(bytes)/(1024) table_size_kb from user_extents where segment_type='TABLE' and segment_name = 'CUSTOMERS' group by segment_name; TABLE_NAME -------------------------------------------------------------------------------- TABLE_SIZE_KB ------------- CUSTOMERS      31885312

Using the function written Anantha Narayanana 

SQL>  CREATE OR REPLACE FUNCTION get_table_size   2  (t_table_name VARCHAR2)RETURN NUMBER IS   3  l_size NUMBER;   4  BEGIN   5  SELECT sum(bytes)/(1024) INTO l_size  6   7  FROM user_extents   8  WHERE segment_type='TABLE'   9  AND segment_name = t_table_name;  10  11  RETURN l_size;  12  EXCEPTION  13  WHEN OTHERS THEN  14  RETURN NULL;  15  END;  16  / Function created. SQL> SELECT get_table_size('CUSTOMERS') Table_Size from dual; TABLE_SIZE ----------   31885312 SQL>