Oracle linux database conectivity virtualbox



I´m configuring two Virtualbox machines on my laptop, and i need these two machines had connectivity between them, so i configured both with "Host-only Adapter" on virtual-box network setup as you can see below:


After that i assign an static Ip address on both machines:

[root@wcp12cr2_clone ~]# ifconfig -a
eth2      Link encap:Ethernet  HWaddr 08:00:27:40:8F:EE
          inet addr:192.168.56.100  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe40:8fee/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:4428 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3478 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:375028 (366.2 KiB)  TX bytes:1246289 (1.1 MiB)


[root@wcp12cr2 ~]# ifconfig -a
eth1      Link encap:Ethernet  HWaddr 08:00:27:E0:01:7F
          inet addr:192.168.56.101  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fee0:17f/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1537 errors:0 dropped:0 overruns:0 frame:0
          TX packets:747 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:133660 (130.5 KiB)  TX bytes:103327 (100.9 KiB)
So after that i can successfully ping and ssh between machines

[root@wcp12cr2_clone ~]# ping 192.168.56.101
PING 192.168.56.101 (192.168.56.101) 56(84) bytes of data.
64 bytes from 192.168.56.101: icmp_seq=1 ttl=64 time=1.47 ms
64 bytes from 192.168.56.101: icmp_seq=2 ttl=64 time=0.798 ms
64 bytes from 192.168.56.101: icmp_seq=3 ttl=64 time=0.770 ms
So, i setup entries on tnsnames.ora and listener.ora to connect to oracle through 1521 port:

[oracle@wcp12cr2 admin]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 26-OCT-2017 09:08:11

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                26-OCT-2017 05:31:03
Uptime                    0 days 3 hr. 37 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/db/ohome/network/admin/listener.ora
Listener Log File         /oracle/db/diag/tnslsnr/wcp12cr2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

So listener was started and working ok, tnsnames.ora has correct entries and phisical connection between machines was ok, but....

[oracle@wcp12cr2_clone admin]$  tnsping ORCL

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 26-OCT-2017 09:14:36

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/oracle/db/ohome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
TNS-12543: TNS:destination host unreachable

This was due Linux Iptables firewall is by default enabled on Oracle Linux....


[root@wcp12cr2_clone ~]# service iptables status
Table: nat
Chain PREROUTING (policy ACCEPT)
num  target     prot opt source               destination

Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination

Chain POSTROUTING (policy ACCEPT)
num  target     prot opt source               destination

Table: filter
Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination
1    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
2    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0
3    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0
4    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
5    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:7777
6    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination
1    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination

So i add a rule on table filter port 1521 at the first position by executing this as root user:

[root@wcp12cr2 ~]# iptables -I INPUT 1  -m tcp -p tcp --dport 1521 -j ACCEPT
[root@wcp12cr2 ~]# service iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]
and after that TnsPing works properly:

[oracle@wcp12cr2_clone admin]$  tnsping ORCL

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 26-OCT-2017 09:20:23

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/oracle/db/ohome/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)


You can see more oracle database connection errors on this article



Comments

Post a Comment

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Image
In Oracle 10.2 and above with ASH is very useful to see the number of sessions active and connected to a database over time: You can use this 2 views to obtain this information: v$sysmetric_history --> This is a sort term view, that contains data of last hour by intervals of minutes dba_hist_sysmetric_summary -->This is a long term view, that contains data of last month by intervals of 1 hour. v$sysmetric_history and dba_hist_sysmetric_summary contains information a lot of different metrics information, so the first thing we need to do is to filter metric type. With this query you can select a list of all metric types: set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history group by METRIC_ID, METRIC_NAME ; for example, if you are looking for metrics related with connected sessions to database, you can do: SQL> set linesize 190 col METRIC_NAME for a100 select METRIC_ID, METRIC_NAME from v$sysmetric_history where lo
Read more

Purging and archiving Oracle alert.log and listener.log

As i said in " Purging Oracle traces with ADRCI " ADRCI does not work properly for alert.log and listener.log, so in this article i´m going to study who to do this tasks externally, with unix tools. 1-With logrotate Linux-command. I think this is the best option if you have an Oracle linux because it can be setup easily... Put this lines in a config file but modifing your alert.log and listener.log paths # alert log /oracle/db/diag/rdbms/orcl/orcl/trace/alert_orcl.log { monthly rotate 13 notifempty missingok copytruncate nocreate } # listener log /oracle/db/diag/tnslsnr/wcp12cr2/listener/trace/listener.log { weekly rotate 53 notifempty missingok copytruncate nocreate } i put them into alert_logrotate.conf as you can see: [oracle@wcp12cr2 Documents]$ ls -ltr alert_logrotate.conf -rw-r--r--. 1 oracle oinstall 593 Oct 5 03:08 alert_logrotate.conf [oracle@wcp12cr2 Documents]$ cat alert_logrotate.conf # see "man logrotate" for details # rotate log files
Read more

Check sessions and processes limits in Oracle

Oracle database has a gv$resource_limit view to check if any database resource reach his connection limit: set linesize 190 set pages 200 select * from gv$resource_limit; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE ---------- ------------------------------ ------------------- --------------- --------------- ------------ 1 processes 44 300 300 300 1 sessions 52 215 512 512 1 enqueue_locks 30 295 6480 6480 1 enqueue_resources 24 165 2472 UNLIMITED 1 ges_procs 0 0 0 0 1 ges_ress 0 0
Read more