Wednesday, September 12, 2012

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


Monday, September 10, 2012

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

Saturday, September 8, 2012

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

Thursday, September 6, 2012

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>