Subscribe to get access
Read more of this content when you subscribe today.
Blog
How to Check Network Port Open on Linux
Telnet
$ telnet ractest 1234 Trying 10.3.2.14... telnet: connect to address 10.3.2.14: Connection refused $ telnet ractest 3872 Trying 10.3.2.14... Connected to ractest. Escape character is '^]'. Connection closed by foreign host.
cURL
$ curl -v telnet://ractest:1234 * About to connect() to ractest port 1234 (#0) * Trying 10.3.2.14... Connection refused * couldn't connect to host * Closing connection #0 curl: (7) couldn't connect to host $ curl -v telnet://ractest:3872 * About to connect() to ractest port 3872 (#0) * Trying 10.3.2.14... connected * Connected to ractest (10.3.2.14) port 3872 (#0) * Closing connection #0
Bash
$ cat < /dev/tcp/10.3.2.14/1234 -bash: connect: Connection refused -bash: /dev/tcp/10.3.2.14/1234: Connection refused $ cat < /dev/tcp/10.3.2.14/3872 ^C
Python
$ python
Python 2.6.6 (r266:84292, Sep 4 2013, 07:46:00)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-3)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import socket
>>> clientsocket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
>>> clientsocket.connect(('10.3.2.14',1234))
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "<string>", line 1, in connect
socket.error: [Errno 111] Connection refused
>>>
>>>
>>> import socket
>>> clientsocket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
>>> clientsocket.connect(('10.3.2.14',3872))
>>> clientsocket.send('\n')
1
Perl
$ perl use IO::Socket::INET; $| = 1; my $socket = new IO::Socket::INET(PeerHost => '10.3.2.14', PeerPort =>'1234', Proto => 'tcp', ); die "cannot connect to the server $!\n" unless $socket; print "connected to the server\n"; ^D cannot connect to the server Connection refused $ perl use IO::Socket::INET; $| = 1; my $socket = new IO::Socket::INET(PeerHost => '10.3.2.14', PeerPort =>'3872', Proto => 'tcp', ); die "cannot connect to the server $!\n" unless $socket; print "connected to the server\n"; ^D connected to the server
On server to check port is listening
$ netstat -na|grep 1521|grep -i listen tcp 0 0 10.3.2.14:1521 0.0.0.0:* LISTEN
On server to check connections through listening port
$ lsof -iTCP:1521
$ lsof -i :1521 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME ora_lreg_ 5396 oracle 49u IPv4 935666084 0t0 TCP ractest:23228->ractest-vip:ncube-lm (ESTABLISHED) ... .. .
ssh
$ ssh -vv HOSTNAME -p port_number
$ ssh -vv racnode2 -p 1521 OpenSSH_7.4p1, OpenSSL 1.0.2k-fips 26 Jan 2017 debug1: Reading configuration data /home/grid/.ssh/config debug1: /home/grid/.ssh/config line 1: Applying options for * debug1: Reading configuration data /etc/ssh/ssh_config debug1: /etc/ssh/ssh_config line 58: Applying options for * debug2: resolving "racnode2" port 1521 debug2: ssh_connect_direct: needpriv 0 debug1: Connecting to racnode2 [10.1.2.3] port 1521. debug1: Connection established. ... .. .
ORA-01013: user requested cancel of current operation
Following OEM alerts are received from databases of 11R2 to 12R2:
... .. . Target name=RACTEST Incident creation time=28/11/2017 9:48:17 PM AEDT Last updated time=28/11/2017 9:48:17 PM AEDT Message= ORA-01013: user requested cancel of current operation Severity=Critical Incident ID=294770 ... .. .
CAUSES
SQL ID 4d43by1zzjfna runs slowly until failed with ‘ORA-01013’ error.
SOLUTION
As per “low Performance Oracle Enterprise Manager SQL Metrics SQL statement (SQL ID: 4d43by1zzjfna) Automatically Run (Doc ID 2266608.1)”, the fololowing actions are taken :
(a) Stopping EM agent monitoring the RAC instance(s).
$AGENT_HOME/bin/emctl stop agent
(b) Test the current execution time ( 36 s ), and record the execution plan via SQL PLUS:
SQL> set timing on SQL> set autotrace on SQL> WITH last_run AS ( SELECT all_runs.OWNER , all_runs.JOB_NAME, all_runs.STATUS FROM DBA_SCHEDULER_JOB_RUN_DETAILS all_runs, ( SELECT OWNER , JOB_NAME, MAX(ACTUAL_START_DATE) AS START_DATE FROM DBA_SCHEDULER_JOB_RUN_DETAILS GROUP BY OWNER, JOB_NAME ) latest_runs WHERE all_runs.OWNER = latest_runs.OWNER AND all_runs.JOB_NAME = latest_runs.JOB_NAME AND all_runs.ACTUAL_START_DATE=latest_runs.START_DATE ... .. . SUM(BROKEN) SUM(FAILED) ----------- ----------- 0 0 Elapsed: 00:00:36.49 Execution Plan ---------------------------------------------------------- Plan hash value: 204035645 ... .. . Statistics ---------------------------------------------------------- 338 recursive calls 0 db block gets 58842 consistent gets 0 physical reads 0 redo size 603 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 8671 sorts (memory) 0 sorts (disk) 1 rows processed
c)Purge any job history that is no longer needed to reduce the size of job related tables:
SQL> exec DBMS_SCHEDULER.PURGE_LOG(7,which_log=>'JOB_LOG');
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.95
SQL> exec DBMS_SCHEDULER.PURGE_LOG(7,which_log=>'WINDOW_LOG');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
SQL> exec DBMS_SCHEDULER.PURGE_LOG(log_history => 7, job_name => 'job1, sys.class2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','7');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
d) Regather statistics to ensure that the CBO has the right understanding of cardinality/cost for execution plans involving JOB related tables:
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.
Elapsed: 00:01:26.37
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
Elapsed: 00:01:35.82
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
PL/SQL procedure successfully completed.
Elapsed: 00:10:53.24
e) rerun the same sql_id=’4d43by1zzjfna’. We can see there is a new execution plan. The running time is shorted from 36 seconds from 1 second.
SQL> WITH last_run AS 2 ( 3 SELECT all_runs.OWNER , 4 all_runs.JOB_NAME, 5 all_runs.STATUS 6 FROM DBA_SCHEDULER_JOB_RUN_DETAILS all_runs, 7 ( 8 SELECT OWNER , 9 JOB_NAME, 10 MAX(ACTUAL_START_DATE) AS START_DATE 11 FROM DBA_SCHEDULER_JOB_RUN_DETAILS 12 GROUP BY OWNER, 13 JOB_NAME 14 ) 15 latest_runs 16 WHERE all_runs.OWNER = latest_runs.OWNER 17 AND all_runs.JOB_NAME = latest_runs.JOB_NAME 18 AND all_runs.ACTUAL_START_DATE=latest_runs.START_DATE 19 ) 20 SELECT SUM(broken), 21 SUM(failed) 22 FROM 23 ( 24 SELECT DECODE(broken, 'N', 0, 1) broken, 25 DECODE(NVL(failures,0), 0, 0, 1) failed 26 FROM dba_jobs 27 UNION ALL 28 SELECT DECODE(STATE,'BROKEN',1,0) broken, 29 DECODE(STATUS ,'FAILED',DECODE(STATE,'BROKEN',0,'DISABLED',0,1),0) failed 30 FROM 31 ( 32 SELECT all_jobs.OWNER , 33 all_jobs.JOB_NAME, 34 last_run.STATUS , 35 all_jobs.STATE 36 FROM last_run, 37 DBA_SCHEDULER_JOBS all_jobs 38 WHERE last_run.OWNER =all_jobs.OWNER 39 AND last_run.JOB_NAME=all_jobs.JOB_NAME 40 ) 41 ) 42 / SUM(BROKEN) SUM(FAILED) ----------- ----------- 0 0 Elapsed: 00:00:01.00 Execution Plan ---------------------------------------------------------- Plan hash value: 2148836375 ... .. . Statistics ---------------------------------------------------------- 50 recursive calls 0 db block gets 3585 consistent gets 0 physical reads 0 redo size 603 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 51 sorts (memory) 0 sorts (disk) 1 rows processed
(f) Re-enable EM Agents monitoring the RAC instance(s):
$AGENT_HOME/bin/emctl start agent
How to Make ASM Instance to Use HugePages
The ASM instance is configured in the same way as normal databases instance for using hugepages in Linux 7. But ASM is not using hugepages, while the normal instance is using hugepages. There are enough free hugepages available as per /etc/meminfo.
Let’s configure ASM to use HugePages.
Subscribe to get access
Read more of this content when you subscribe today.
Install the AWS Command Line Interface on Microsoft Windows
There are two ways to install AWS CLI:
MSI Installer
a) Download the appropriate MSI installer 64-bit or 32-bit.
b) Run the downloaded MSI installer, and follows the instructions on screen.
By default, the CLI is installed onto:
C:\Program Files\Amazon\AWSCLI (64-bit) or
C:\Program Files (x86)\Amazon\AWSCLI (32-bit)
c) Test the installation.
C:\>aws --version aws-cli/1.11.84 Python/3.6.2 Windows/7 botocore/1.5.47
d) Updating an MSI Installation.
To update to the latest version, download and run the MSI installer again as detailed above.
e) Uninstall AWS CLI Components.
Control Panel -> All Control Panel Items -> Programs and Features. Select the entry named AWS Command Line Interface and click Uninstall to launch the uninstaller.
Install Python, pip, and the AWS CLI
a) To install Python 3.6 and pip (Windows).
i. Download the Python 3.6 Windows x86-64 executable installer from the downloads page of Python.org.
ii. Run the installer, then click “Install Now” ( C:\Program Files\Python36 )
Tick “Install Launcher for all users ( recommended)
Tick “Add Python 3.6 to PATH”
iii. Until see the below “Setup was successful” screen.
b) To install the AWS CLI with pip (Windows).
i. Open Command Prompt, and verify Python and pip are both installed successfully.
C:\>python --version Python 3.6.3 c:\>pip --version pip 9.0.1 from c:\program files\python36\lib\site-packages (python 3.6)
ii. Install the AWS CLI using pip, Run as Administrator user if not Administrator user.
C:\>pip install awscli Collecting awscli Downloading awscli-1.11.185-py2.py3-none-any.whl (1.2MB) 100% |████████████████████████████████| 1.2MB 930kB/s Collecting docutils>=0.10 (from awscli) Downloading docutils-0.14-py3-none-any.whl (543kB) 100% |████████████████████████████████| 552kB 1.8MB/s Collecting rsa<=3.5.0,>=3.1.2 (from awscli) Downloading rsa-3.4.2-py2.py3-none-any.whl (46kB) 100% |████████████████████████████████| 51kB 6.4MB/s Collecting colorama<=0.3.7,>=0.2.5 (from awscli) Downloading colorama-0.3.7-py2.py3-none-any.whl Collecting botocore==1.7.43 (from awscli) Downloading botocore-1.7.43-py2.py3-none-any.whl (3.7MB) 100% |████████████████████████████████| 3.7MB 286kB/s Collecting PyYAML<=3.12,>=3.10 (from awscli) Downloading PyYAML-3.12.tar.gz (253kB) 100% |████████████████████████████████| 256kB 3.5MB/s Collecting s3transfer<0.2.0,>=0.1.9 (from awscli) Downloading s3transfer-0.1.11-py2.py3-none-any.whl (54kB) 100% |████████████████████████████████| 61kB 6.8MB/s Collecting pyasn1>=0.1.3 (from rsa<=3.5.0,>=3.1.2->awscli) Downloading pyasn1-0.3.7-py2.py3-none-any.whl (63kB) 100% |████████████████████████████████| 71kB 6.0MB/s Collecting python-dateutil<3.0.0,>=2.1 (from botocore==1.7.43->awscli) Downloading python_dateutil-2.6.1-py2.py3-none-any.whl (194kB) 100% |████████████████████████████████| 194kB 3.8MB/s Collecting jmespath<1.0.0,>=0.7.1 (from botocore==1.7.43->awscli) Downloading jmespath-0.9.3-py2.py3-none-any.whl Collecting six>=1.5 (from python-dateutil<3.0.0,>=2.1->botocore==1.7.43->awscli) Downloading six-1.11.0-py2.py3-none-any.whl Installing collected packages: docutils, pyasn1, rsa, colorama, six, python-date util, jmespath, botocore, PyYAML, s3transfer, awscli Running setup.py install for PyYAML ... done Successfully installed PyYAML-3.12 awscli-1.11.185 botocore-1.7.43 colorama-0.3. 7 docutils-0.14 jmespath-0.9.3 pyasn1-0.3.7 python-dateutil-2.6.1 rsa-3.4.2 s3tr ansfer-0.1.11 six-1.11.0
iii. Verify that the AWS CLI is installed correctly.
c:\>aws --version aws-cli/1.11.185 Python/3.6.3 Windows/2012ServerR2 botocore/1.7.43
Vi. To upgrade to the latest version, run the installation command again:
Microsoft Windows [Version 6.3.9600] (c) 2013 Microsoft Corporation. All rights reserved. C:\Windows\system32>pip install --user --upgrade awscli Requirement already up-to-date: awscli in c:\program files\python36\lib\site-pac kages Requirement already up-to-date: rsa<=3.5.0,>=3.1.2 in c:\program files\python36\ lib\site-packages (from awscli) Requirement already up-to-date: colorama<=0.3.7,>=0.2.5 in c:\program files\pyth on36\lib\site-packages (from awscli) Requirement already up-to-date: docutils>=0.10 in c:\program files\python36\lib\ site-packages (from awscli) Requirement already up-to-date: botocore==1.7.43 in c:\program files\python36\li b\site-packages (from awscli) Requirement already up-to-date: s3transfer<0.2.0,>=0.1.9 in c:\program files\pyt hon36\lib\site-packages (from awscli) Requirement already up-to-date: PyYAML<=3.12,>=3.10 in c:\program files\python36 \lib\site-packages (from awscli) Requirement already up-to-date: pyasn1>=0.1.3 in c:\program files\python36\lib\s ite-packages (from rsa<=3.5.0,>=3.1.2->awscli) Requirement already up-to-date: jmespath<1.0.0,>=0.7.1 in c:\program files\pytho n36\lib\site-packages (from botocore==1.7.43->awscli) Requirement already up-to-date: python-dateutil<3.0.0,>=2.1 in c:\program files\ python36\lib\site-packages (from botocore==1.7.43->awscli) Requirement already up-to-date: six>=1.5 in c:\program files\python36\lib\site-p ackages (from python-dateutil<3.0.0,>=2.1->botocore==1.7.43->awscli) C:\Windows\system32>
Adding the AWS CLI Executable to your Command Line Path
After installing with pip, add the aws executable to your OS’s PATH environment variable. With an MSI installation, this should happen automatically, but you may need to set it manually if the aws command is not working.
- Python 3.6 and pip –
%USERPROFILE%\AppData\Local\Programs\Python\Python36\Scripts - MSI installer (64-bit) –
C:\Program Files\Amazon\AWSCLI - MSI installer (32-bit) –
C:\Program Files (x86)\Amazon\AWSCLI
Make DBA Life Easy 