Default Port Numbers Used on Exadata: Port Numbers for ILOM Part 2 of 3

network_firewall

The previous post discusses the default port for general use. This post shows the default number for ILOM.

For the following SP Network Ports on db and cell nodes need to be opened. Otherwise opening remote console from ILOM will not work.

<br /><br />Port   Protocol  Application<br />5120   TCP       Oracle ILOM Remote Console: CD<br />5121   TCP       Oracle ILOM Remote Console: Keyboard and Mouse<br />5123   TCP       Oracle ILOM Remote Console: Diskette<br />5555   TCP       Oracle ILOM Remote Console: Encryption<br />5556   TCP       Oracle ILOM Remote Console: Authentication<br />6481   TCP       Oracle ILOM Remote Console: Servicetag Daemon<br />7578   TCP       Oracle ILOM Remote Console: Video<br />7579   TCP       Oracle ILOM Remote Console: Serial<br /><br />

The following are for Common Network Ports.

<br /><br />Port   Protocol       Application<br />22     SSH - TCP      SSH - Secure Shell<br />69     TFTP - UDP     TFTP (outgoing)<br />80     HTTP - TCP     Web<br />123    NTP - UDP      NTP (outgoing)<br />161    SNMP - UDP     SNMP<br />162    IPMI - UDP     IPMI (outgoing)<br />389    LDAP - UDP/TCP LDAP (outgoing)<br />443    HTTPS - TCP    Web<br />514    Syslog - UDP   Syslog - (outgoing)<br />623    IPMI - UDP     IPMI<br />546    DHCP - UDP     DHCP<br />1812   RADIUS - UDP   RADIUS (outgoing)<br /><br />

For more detail about default ports number used in ILOM, you can also check out document – Oracle Integrated Lights Out Manager (ILOM) 3.0 Concepts Guide.

Related Posts:

Default Port Numbers Used on Exadata: Port Numbers for OEM Part 3 of 3

Default Port Numbers Used on Exadata: Port Numbers for General Use Part 1 of 3

Advertisements

Compression Methods on Exadata: Create compression table using ALTER TABLE MOVE – Part 2 of 6

moveData
In the last post, it shows the examples to create compression tables using CREATE TABLE clause. In reality, majority of the time, we have existing tables in place and we just need to change it to a compression table. It is important to know when the compression happens. For example, if uses ALTER TABLE … COMPRESS FOR QUERY HIGH command, there is NO change to the existing data in the current table. Only after you uses MOVE keyword to rebuild the segment, it can really do the compression work. As usual, I shows different ways to use MOVE to compress tables as follows. Of course, in my examples, I do not have a plan to use a dolly to move data. But if you have multiple petabyte or even exabyte of data, dolly from a truck may be the faster way to move compared with slow network transfer via WAN across the country.

BASIC Compression:

WZHOU@dbm1> create table movetab_basic as select * from testcp;

Table created.

Elapsed: 00:00:09.38

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_BASIC';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_BASIC              YES DISABLED

Elapsed: 00:00:00.01

WZHOU@dbm1> ALTER TABLE movetab_basic MOVE COMPRESS;

Table altered.

Elapsed: 00:00:29.78

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables  2  where table_name='MOVETAB_BASIC';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_BASIC              YES ENABLED  BASIC

Elapsed: 00:00:00.01

OLTP Compression:


WZHOU@dbm1> create table movetab_oltp as select * from testcp;

Table created.

Elapsed: 00:00:08.30

WZHOU@dbm1> ALTER TABLE movetab_oltp MOVE COMPRESS FOR oltp;

Table altered.

Elapsed: 00:00:29.03

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
   where table_name='MOVETAB_OLTP';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_OLTP               YES ENABLED  OLTP

Elapsed: 00:00:00.00

Query Low Compression:

WZHOU@dbm1> create table movetab_query_low as select * from testcp;

Table created.

Elapsed: 00:00:08.82

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_QUERY_LOW';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_QUERY_LOW          YES DISABLED

Elapsed: 00:00:00.00

WZHOU@dbm1> ALTER TABLE movetab_query_low MOVE COMPRESS for query low;

Table altered.

Elapsed: 00:00:15.70

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_QUERY_LOW';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_QUERY_LOW          YES ENABLED  QUERY LOW

Elapsed: 00:00:00.01

Query High Compression:


WZHOU@dbm1> create table movetab_query_high as select * from testcp;

Table created.

Elapsed: 00:00:08.21

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
  2  where table_name='MOVETAB_QUERY_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_QUERY_HIGH         YES DISABLED

Elapsed: 00:00:00.00

WZHOU@dbm1> ALTER TABLE movetab_query_high MOVE COMPRESS for query high;

Table altered.

Elapsed: 00:00:27.60

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
  2  where table_name='MOVETAB_QUERY_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_QUERY_HIGH         YES ENABLED  QUERY HIGH

Elapsed: 00:00:00.00

Archive Low Compression:


WZHOU@dbm1> create table movetab_archive_low as select * from testcp;

Table created.

Elapsed: 00:00:08.74

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
  2  where table_name='MOVETAB_ARCHIVE_LOW';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_ARCHIVE_LOW        YES DISABLED

Elapsed: 00:00:00.00

WZHOU@dbm1> ALTER TABLE movetab_archive_low MOVE COMPRESS for archive low;

Table altered.

Elapsed: 00:00:28.26

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
  2  where table_name='MOVETAB_ARCHIVE_LOW';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_ARCHIVE_LOW        YES ENABLED  ARCHIVE LOW

Elapsed: 00:00:00.00

Archive High Compression:

WZHOU@dbm1> create table movetab_archive_high as select * from testcp;

Table created.

Elapsed: 00:00:08.23

WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_ARCHIVE_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_ARCHIVE_HIGH           YES DISABLED

Elapsed: 00:00:00.00

WZHOU@dbm1> ALTER TABLE movetab_archive_high MOVE COMPRESS for archive high;

Table altered.

Elapsed: 00:02:32.26
WZHOU@dbm1> select table_name,logging,compression,compress_for from dba_tables
    where table_name='MOVETAB_ARCHIVE_HIGH';

TABLE_NAME             LOG COMPRESS COMPRESS_FOR
------------------------------ --- -------- ------------
MOVETAB_ARCHIVE_HIGH           YES ENABLED  ARCHIVE HIGH

Elapsed: 00:00:00.00

Remember from last post, the uncompressed table size is 1,152 MB. You can see the sizes of the tables after compression are quite similar to the approaches using CREATE TABLE in the last post.

WZHOU@dbm1> @table_size
Enter value for owner: WZHOU
Enter value for table_name: MOVE%
Enter value for type:

OWNER   SEGMENT_NAME           TYPE    TOTALSIZE_MEGS TABLESPACE_NAME
------- ------------------------------ ---------- --------------
WZHOU   MOVETAB_ARCHIVE_HIGH   TABLE      48.0 USERS
WZHOU   MOVETAB_ARCHIVE_LOW    TABLE      58.0 USERS
WZHOU   MOVETAB_QUERY_HIGH     TABLE      59.0 USERS
WZHOU   MOVETAB_QUERY_LOW      TABLE     160.0 USERS
WZHOU   MOVETAB_BASIC          TABLE     384.0 USERS
WZHOU   MOVETAB_OLTP           TABLE     424.0 USERS
                                       --------------
sum                                    1,133.0

6 rows selected.

[update 1]
So what’s happened if I need to access this table during the move, can I still do the select or other DML operation against this table during the move? With this question in mind, I did a few tests to see how it behaves during the MOVE operation. Make the Move operation longer, I use ARCHIVE HIGH compression method to see the difference. I also opened 5 more db sessions to do SELECT, INSERT, UPDATE, DELETE and monitor operations.

Session 1:

WZHOU@dbm1> create table movetab_chkop as select * from testcp;
Table created.
Elapsed: 00:00:08.32

Then in the same session, run the compression for archive high. During the compression process, run other operations in different sessions as follows:

ALTER TABLE movetab_chkop MOVE COMPRESS for archive high;

Session 2 (check SELECT):
select id, object_name from movetab_chkop where id = 10;
select count(*) from movetab_chkop;

Session 3 (check INSERT):
insert into movetab_chkop select * from testcp where id = 1;

Session 4 (check UPDATE):
update movetab_chkop set id = 1001 where id = 10000;

Session 5 (check DELETE):
delete from movetab_chkop where id = 5000;

Session 6 (Monitor the other sessions)
run as.sql script to check out the active session.
as.sql

The script to check who is blocking who

select l1.sid, ‘ IS BLOCKING ‘, l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

The result are listed as follows:

Session 1 (Do archive high compression)

WZHOU@dbm1> ALTER TABLE movetab_chkop MOVE COMPRESS for archive high;
Table altered.
Elapsed: 00:02:32.15

Session 2 (check SELECT)

WZHOU@dbm1> select id, object_name from movetab_chkop where id = 10;

	ID OBJECT_NAME
---------- ------------------------------
	10 I_OBJ5

Elapsed: 00:00:00.70

WZHOU@dbm1> select count(*) from movetab_chkop;

  COUNT(*)
----------
  10000000

Elapsed: 00:00:00.65

WZHOU@dbm1> select id, object_name from movetab_chkop where id = 10000;

	ID OBJECT_NAME
---------- ------------------------------
     10000 KU$_M_VIEW_SCM_T

Elapsed: 00:00:00.04

WZHOU@dbm1> select id, object_name from movetab_chkop where id = 5000;

	ID OBJECT_NAME
---------- ------------------------------
      5000 UTL_RECOMP_ALL_OBJECTS
Elapsed: 00:00:00.02

Session 3 (check INSERT)

WZHOU@dbm1> insert into movetab_chkop select * from testcp where id = 1;

1 row created.
Elapsed: 00:02:09.70

Session 4 (check UPDATE)

WZHOU@dbm1> update movetab_chkop set id = 1001 where id = 10000;

1 row updated.
Elapsed: 00:02:04.50

Session 5 (check DELETE)

WZHOU@dbm1> delete from movetab_chkop where id = 5000;

1 row deleted.
Elapsed: 00:01:59.17

Session 6 (Monitor the other sessions)

WZHOU@dbm1> @as

  SID	 SERIAL# PROG	    SQL_ID	   CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME OFF SQL_TEXT
----- ---------- ---------- ------------- ------ --------------- ---------- ----------- --- -----------------------------------------
 1167	     587 sqlplus@en 1vz2hfh9wccwd      0      3566611749	  0	    .00 No  insert into movetab_chkop select * from t
 1945	      15 sqlplus@en 8856nfxk46wv8      0       900447529	  0	    .00 No  delete from movetab_chkop where id = 5000
  976	     669 sqlplus@en 9qta3dvp1jp5f      0       143915756	  0	  67.22 Yes ALTER TABLE movetab_chkop MOVE COMPRESS f
 1751	      15 sqlplus@en b2qff928t8ca7      1      2858995896	  8	    .35 No  select sid, serial#,substr(program,1,19)
 1557	     341 sqlplus@en f23grns86fvmn      0      3466372318	  0	    .01 No  update movetab_chkop set id = 1001 where

Elapsed: 00:00:00.45

WZHOU@dbm1> select l1.sid, ' IS BLOCKING ', l2.sid
  from v$lock l1, v$lock l2
    where l1.block =1 and l2.request > 0
    and l1.id1=l2.id1
        and l1.id2=l2.id2;

  SID 'ISBLOCKING'    SID
----- ------------- -----
  976  IS BLOCKING   1167
  976  IS BLOCKING   1557
  976  IS BLOCKING   1945

Elapsed: 00:00:00.08

After the compression completes, all DML operations are unblocked.

WZHOU@dbm1> @as

  SID	 SERIAL# PROG	    SQL_ID	   CHILD PLAN_HASH_VALUE      EXECS   AVG_ETIME OFF SQL_TEXT
----- ---------- ---------- ------------- ------ --------------- ---------- ----------- --- -----------------------------------------
 1751	      15 sqlplus@en b2qff928t8ca7      1      2858995896	  9	    .36 No  select sid, serial#,substr(program,1,19)

Elapsed: 00:00:00.44

WZHOU@dbm1> select l1.sid, ' IS BLOCKING ', l2.sid
  from v$lock l1, v$lock l2
    where l1.block =1 and l2.request > 0
    and l1.id1=l2.id1
        and l1.id2=l2.id2;

no rows selected

From the above results, we can see the MOVE operation is blocking all DML operations while select operation is not blocked. So if you have a big table to be compressed, there will be a period all DML operations will be blocked during the move.

Default Port Numbers Used on Exadata: Port Numbers for General Use Part 1 of 3

network_port

There are many ports that need to be opened in firewall to allow certain types of communication on Exadata. Here is the list of the port numbers. Note: all ports below are on the management work with the only exception of port number 1521, which is on client or public network.

Source   Target               Protocol     Port  Application
         Database management  SSH - TCP    22    SSH
         DB, Cell, IB IlOMs   SSH - TCP    22    SSH
         KVM                  SSH - TCP    22    SSH
         Storage management   SSH - TCP    22    SSH
         KVM                  Telnet - TCP 23    Telnet
Cell     E-mail server        SMTP         25    SMTP
                                          465 if using SSL
DB, Cell
IB ILOMs                      TFTP - UDP   69    Outgoing TFTP
         DB,Cell, IB ILOMs    HTTP - TCP   80    Web
         KVM                  HTTP - TCP   80    Avocent video viewer
                                                 download for Java applet
         PDU                  HTTP - TCP   80    Web
DB, Cell                      NTP - UDP   123    Outgoing NTP
IB ILOMs

ASR Mgr  ASR asset            SNMP (get)  161    FMA enrichment
         DB, Cell, IB IlOMs   SNMP - UDP  161    SNMP
         KVM                  SNMP - UDP  161    SNMP
         PDU                  SNMP - UDP  161    SNMP

Cell     SNMP subscriber such SNMP        162    SNMP version 1
         as OEM or SNMP Mgr                      outgoing traps

DB, Cell ASR Manager          SNMP        162    Telemetry messages sent
ILOMs                                            to ASR Manager

DB, Cell                      IPMI - UDP  162    Outgoing IPMI
IB ILOMs
KVM                           SNMP - UDP  162    Outgoing SNMPv2 traps
PDU                           SNMP - UDP  162    Outgoing SNMPv2 traps
         DB, Cell, IB ILOMs   LDAP - UDP/TCP 389 Outgoing LDAP

ASR Mgr  ASR backend          HTTPS       443    Telemetry messages sent
                                                 to ASR backend
         DB, Cell, IB ILOMs   HTTPS - TCP 443    Web
         KVM                  HTTPS - TCP 443    Browser interface for
                                                 MergePoint Utility switch
                                                 and KVM sessions
         PDU                  HTTPS - TCP 443    Browser interface

DB, Cell                      Syslog - UDP 514   Outgoing Syslog
IB ILOMs
KVM, PDU

DB, Cell                      DHCP - UDP  546    client DHCP
IB ILOMs
KVM, PDU
         DB, Cell, IB ILOMs   IPMI - UDP  623    IPMI
OEM                           TCP        1159    OEM HTTPS upload port
         DB                   TCP        1521    Database listener
DB, Cell                      RADIUS - UDP 1812  Outgoing RADIUS
IB ILOMs
         KVM                  TCP        2068    KVM session data
OEM                           TCP        4889    OEM HTTP upload port
         DB                   TCP        7777    OEM HTTP console port
         DB                   TCP        7799    OEM HTTPS console port
         Cell                 TCP        7799    OEM HTTPS console port

The above port list is based on the document Default Service Ports for Exadata and Cloud Control /Enterprise Manager Configurations (Doc ID 1532838.1).

For me, it is not easy to figure out which port should be open on which hardware component. So I use Excel spreadsheet to create a matrix to show ports used on Exadata.

PortList_exadata

In the future, I will have another two posts discussing ports used by ILOM and OEM.

Related Posts:

Default Port Numbers Used on Exadata: Port Numbers for ILOM Part 2 of 3

Default Port Numbers Used on Exadata: Port Numbers for OEM Part 3 of 3