Troubleshooting MySQL Replication with mysqlbinlog

[root@vmxdb01 mysql]# mysqlbinlog mysql-bin.000002


SET TIMESTAMP=1277302367/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
insert into employees values('a','b',1)
/*!*/;
# at 1053
#100623 22:33:07 server id 1  end_log_pos 1154  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1277303587/*!*/;
insert into employees values('b','c',2)
/*!*/;
# at 1154
#100623 22:42:34 server id 1  end_log_pos 1255  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1277304154/*!*/;
insert into employees values('b','c',3)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

[root@vmxdb01 mysql]# mysql -h vmxdb01 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.77-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select from_unixtime(1277304154);
+---------------------------+
| from_unixtime(1277304154) |
+---------------------------+
| 2010-06-23 22:42:34       |
+---------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

LOBSEGMENT defragmentation

Method 1: Shrink space, which is slow
Method 2: "Move" LobSegment

[oracle@vmxdb01 ~]$ sqlplus donghua/ora123

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 8 15:02:14 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set echo on
SQL> @lob_test.sql
SQL> col segment_name for a30
SQL> col index_name for a30
SQL> set lin 90
SQL> col mbytes for 99999999999
SQL> set timing on
SQL>
SQL> drop table tbl_lob_test purge;

Table dropped.

Elapsed: 00:00:01.70
SQL>
SQL> CREATE TABLE tbl_lob_test
  2  (
  3     col1    CHAR (2000),
  4     col2    CHAR (2000),
  5     col3    CHAR (2000),
  6     message   CLOB
  7  )
  8  LOB (message) STORE AS (TABLESPACE users DISABLE STORAGE IN ROW);

Table created.

Elapsed: 00:00:00.21
SQL>
SQL> insert into tbl_lob_test
  2  select owner, object_name,object_name, object_name from dba_objects;

72458 rows created.

Elapsed: 00:00:56.44
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME     from dba_lobs
  2  where table_name='TBL_LOB_TEST' and owner='DONGHUA';

SEGMENT_NAME                   INDEX_NAME
------------------------------ ------------------------------
SYS_LOB0000074592C00004$$      SYS_IL0000074592C00004$$

Elapsed: 00:00:00.14
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$            4194304
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$         603979776

Elapsed: 00:00:02.15
SQL>
SQL> delete from tbl_lob_test;

72458 rows deleted.

Elapsed: 00:01:13.64
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> select segment_name,sum(bytes)     mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$           11534336
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$         603979776

Elapsed: 00:00:02.62
SQL>
SQL> insert into tbl_lob_test
  2  select owner, object_name,object_name, object_name from dba_objects;

72458 rows created.

Elapsed: 00:01:26.81
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes)     mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$           14680064
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$        1207959552

Elapsed: 00:00:01.68
SQL>
SQL> alter table tbl_lob_test modify lob (message) (shrink space);

Table altered.

Elapsed: 00:02:29.18
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$           14680064
TBL_LOB_TEST                      603979776
SYS_LOB0000074592C00004$$         603979776

Elapsed: 00:00:01.09
SQL>
SQL> alter table tbl_lob_test
  2  move lob (message)  STORE AS tbl_lob_test_lob_msg
  3          (TABLESPACE users
  4          index tbl_lob_test_lob_msg_idx (tablespace users) );

Table altered.

Elapsed: 00:01:06.87
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$            4194304
TBL_LOB_TEST                      603979776
TBL_LOB_TEST_LOB_MSG              603979776

Elapsed: 00:00:00.05
SQL>
SQL> truncate table tbl_lob_test;

Table truncated.

Elapsed: 00:00:02.10
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
SYS_IL0000074592C00004$$              65536
TBL_LOB_TEST                          65536
TBL_LOB_TEST_LOB_MSG                  65536

Elapsed: 00:00:00.06
SQL>
SQL> drop table tbl_lob_test purge;

Table dropped.

Elapsed: 00:00:00.49
SQL>
SQL> CREATE TABLE tbl_lob_test
  2  (
  3     col1      CHAR (2000),
  4     col2      CHAR (2000),
  5     col3      CHAR (2000),
  6     MESSAGE   CLOB
  7  )
  8  LOB (
  9     MESSAGE)
 10     STORE AS
 11        tbl_lob_test_lob_msg (
 12     TABLESPACE users
 13     DISABLE STORAGE IN ROW
 14     INDEX tbl_lob_test_lob_msg_idx ( TABLESPACE users ));

Table created.

Elapsed: 00:00:00.18
SQL>
SQL> insert into tbl_lob_test values (1,2,3,4);

1 row created.

Elapsed: 00:00:00.06
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> select segment_name,sum(bytes) mbytes from dba_extents where owner='DONGHUA'
  2  group by segment_name;

SEGMENT_NAME                         MBYTES
------------------------------ ------------
TBL_LOB_TEST_LOB_MSG_IDX              65536
TBL_LOB_TEST                          65536
TBL_LOB_TEST_LOB_MSG                  65536

Elapsed: 00:00:00.05
SQL>
SQL> select SEGMENT_NAME,INDEX_NAME     from dba_lobs
  2  where table_name='TBL_LOB_TEST' and owner='DONGHUA';

SEGMENT_NAME                   INDEX_NAME
------------------------------ ------------------------------
TBL_LOB_TEST_LOB_MSG           TBL_LOB_TEST_LOB_MSG_IDX

Elapsed: 00:00:00.04

Useful SQL commands during the database mirroring / log shipping

ALTER DATABASE TESTDB SET
SINGLE_USER WITH Rollback Immediate
Go

/****** Object: Database [TESTDB] Script Date: 05/12/2010 14:21:21 ******/
CREATE DATABASE [TESTDB_SS] ON PRIMARY
( NAME = N'TESTDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SDC\MSSQL\DATA\TESTDB.ss' )
AS SNAPSHOT OF TESTDB
GO

ALTER DATABASE testdb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
GO

Difference with "traceonly explain", "autotrace on" and "traceonly statistics"

Explain plan is supported by default:

SQL> set autotrace traceonly explain

But reporting statistics requires plustrace role.

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> set autotrace traceonly statistics
SP2-0618: Cannot find the Session Identifier.  Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> get ?\sqlplus\admin\plustrce.sql
  1  --
  2  -- Copyright (c) Oracle Corporation 1995, 2002.  All Rights Reserved.
  3  --
  4  -- NAME
  5  --   plustrce.sql
  6  --
  7  -- DESCRIPTION
  8  --   Creates a role with access to Dynamic Performance Tables
  9  --   for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
 10  --   After this script has been run, each user requiring access to
 11  --   the AUTOTRACE feature should be granted the PLUSTRACE role by
 12  --   the DBA.
 13  --
 14  -- USAGE
 15  --   sqlplus "sys/knl_test7 as sysdba" @plustrce
 16  --
 17  --   Catalog.sql must have been run before this file is run.
 18  --   This file must be run while connected to a DBA schema.
 19  set echo on
 20  drop role plustrace;
 21  create role plustrace;
 22  grant select on v_$sesstat to plustrace;
 23  grant select on v_$statname to plustrace;
 24  grant select on v_$mystat to plustrace;
 25  grant plustrace to dba with admin option;
 26* set echo off
 27

Partition by range with Interval example

SQL> select min(time_id) from sh.sales;

MIN(TIME_ID)
--------------------
1998-JAN-01 00:00:00

SQL> create table sales
  2  partition by range (time_id)
  3  interval (numtoyminterval(1,'MONTH'))
  4  (
  5   partition p199801 values less than (to_date('1998-02-01','yyyy-mm-dd'))
  6  )
  7  as select * from sh.sales;

Table created.

SQL> create index sales_n1 on sales(time_id) local;

Index created.

SQL> exec dbms_stats.gather_table_stats('','sales');

PL/SQL procedure successfully completed.

SQL> set lin 120
SQL> col partition for a10
SQL> col high_value for a80
SQL> col num_rows for 99999999
SQL> set pages 999


SQL> select partition_name,high_value,num_rows
  2  from user_tab_partitions
  3  where table_name='SALES';

  PARTITION_ HIGH_VALUE                                                                        NUM_ROWS
---------- -------------------------------------------------------------------------------- ---------
P199801    TO_DATE(' 1998-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     15132
SYS_P69    TO_DATE(' 1998-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14307
SYS_P70    TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14248
SYS_P71    TO_DATE(' 1998-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     11818
SYS_P72    TO_DATE(' 1998-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     12309
SYS_P73    TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     11631
SYS_P74    TO_DATE(' 1998-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     16257
SYS_P75    TO_DATE(' 1998-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17199
SYS_P76    TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17059
SYS_P77    TO_DATE(' 1998-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19878
SYS_P79    TO_DATE(' 1998-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14612
SYS_P78    TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14384
SYS_P80    TO_DATE(' 1999-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20437
SYS_P81    TO_DATE(' 1999-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     24122
SYS_P82    TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19627
SYS_P83    TO_DATE(' 1999-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17004
SYS_P84    TO_DATE(' 1999-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19213
SYS_P85    TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     18016
SYS_P86    TO_DATE(' 1999-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21889
SYS_P87    TO_DATE(' 1999-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22225
SYS_P88    TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     23024
SYS_P89    TO_DATE(' 1999-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22256
SYS_P91    TO_DATE(' 1999-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21259
SYS_P90    TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     18873
SYS_P92    TO_DATE(' 2000-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22135
SYS_P93    TO_DATE(' 2000-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20609
SYS_P94    TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19453
SYS_P95    TO_DATE(' 2000-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17481
SYS_P96    TO_DATE(' 2000-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20046
SYS_P97    TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     17988
SYS_P98    TO_DATE(' 2000-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     18534
SYS_P99    TO_DATE(' 2000-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20369
SYS_P100   TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20047
SYS_P101   TO_DATE(' 2000-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21542
SYS_P102   TO_DATE(' 2000-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19709
SYS_P103   TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     14733
SYS_P104   TO_DATE(' 2001-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20739
SYS_P105   TO_DATE(' 2001-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19185
SYS_P106   TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20684
SYS_P107   TO_DATE(' 2001-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21667
SYS_P108   TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     19188
SYS_P109   TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22437
SYS_P110   TO_DATE(' 2001-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     21860
SYS_P112   TO_DATE(' 2001-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     23330
SYS_P111   TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     20579
SYS_P115   TO_DATE(' 2001-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     24537
SYS_P113   TO_DATE(' 2001-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22403
SYS_P114   TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA     22809

48 rows selected.

Using "nonunique index to enforce uniqueness" is a good way?

Quote from Oracle® Database Performance Tuning Guide:

14.1.9 Using Nonunique Indexes to Enforce Uniqueness

You can use an existing nonunique index on a table to enforce uniqueness, either for UNIQUE constraints or the unique aspect of a PRIMARY KEY constraint. The advantage of this approach is that the index remains available and valid when the constraint is disabled. Therefore, enabling a disabled UNIQUE or PRIMARY KEY constraint does not require rebuilding the unique index associated with the constraint. This can yield significant time savings on enable operations for large tables.

Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint. You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the UNIQUE key; otherwise, Oracle Database creates an additional unique index to enforce the constraint.

But i still think using unique indexes to support uniqueness and primary key constraint is a better choice. And uniqueness is affecting oracle optimizer.

SQL> select constraint_name,status,index_name from user_constraints;

CONSTRAINT_NAME                STATUS   INDEX_NAME
------------------------------ -------- ------------------------------
SYS_C009770                    ENABLED
SYS_C009771                    ENABLED
SYS_C009772                    ENABLED
SYS_C009773                    ENABLED
EMPLOYEES_PK                   ENABLED  EMPLOYEE_N1

SQL> select index_name,uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMPLOYEE_N1                    NONUNIQUE

SQL> set autotrace traceonly explain statistics
SQL> select * from employees where employee_id=107;


Execution Plan
----------------------------------------------------------
Plan hash value: 2553029091

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   133 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |   133 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPLOYEE_N1 |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=107)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1083  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter table employees disable constraint employees_pk;

Table altered.

SQL> drop index employee_n1;

Index dropped.

SQL> create unique index employee_n1 on employees(employee_id);

Index created.

SQL> alter table employees enable constraint employees_pk;

Table altered.


SQL> select index_name,uniqueness from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMPLOYEE_N1                    UNIQUE

SQL> select constraint_name,status,index_name from user_constraints;

CONSTRAINT_NAME                STATUS   INDEX_NAME
------------------------------ -------- ------------------------------
SYS_C009770                    ENABLED
SYS_C009771                    ENABLED
SYS_C009772                    ENABLED
SYS_C009773                    ENABLED
EMPLOYEES_PK                   ENABLED  EMPLOYEE_N1


SQL> select * from employees where employee_id=107;


Execution Plan
----------------------------------------------------------
Plan hash value: 1716760386

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   133 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     1 |   133 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMPLOYEE_N1 |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=107)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        987  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Sample iptables configuration to cater Oracle Listener

With RHEL default configuration:

[root@vmxdb02 ~]# cat /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT

WithOUT RHEL default configuration:

[root@vmxdb02 ~]# cat /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -i lo -j ACCEPT
-A INPUT -p icmp --icmp-type any -j ACCEPT
-A INPUT -p 50 -j ACCEPT
-A INPUT -p 51 -j ACCEPT
-A INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A INPUT -p udp -m udp --dport 631 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT

[root@vmxdb02 ~]# service iptables restart
Flushing firewall rules:                                   [  OK  ]
Setting chains to policy ACCEPT: filter                    [  OK  ]
Unloading iptables modules:                                [  OK  ]
Applying iptables firewall rules:                          [  OK  ]
Loading additional iptables modules: ip_conntrack_netbios_n[  OK  ]
[root@vmxdb02 ~]# iptables -L -n
Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0
ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0           icmp type 255
ACCEPT     esp  --  0.0.0.0/0            0.0.0.0/0
ACCEPT     ah   --  0.0.0.0/0            0.0.0.0/0
ACCEPT     udp  --  0.0.0.0/0            224.0.0.251         udp dpt:5353
ACCEPT     udp  --  0.0.0.0/0            0.0.0.0/0           udp dpt:631
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:631
ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:1521
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination