Group IP address counts from a log

A colleague wrote a great little script to do some analysis against an Oracle listener log and to show how many listener connections were being established from each unique IP in the log.

cd /u01/app/oracle/diag/tnslsnr/node_name/listener_qa/trace
grep DB_DEFAULT_SERVICE listener_qa.01-Nov-2018-W44.log | grep -oE "[0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}[.][0-9]{1,3}" | sort | uniq -c

The output looks something like this:

20277 172.0.0.1
37176 172.0.0.4
18    172.1.1.1
2300  172.2.3.10
2300  172.2.3.8
96    172.2.3.4
1012  172.2.3.7

 

 

Advertisements

Deleting files older than X days

A couple of different ways to purge files older than a certain amount of days.

find -type f -mtime +350 -name 'CopyCluster*' -execdir rm -- '{}' \;

If you want to be very precise with the number of days ago you want to delete from then you can find the number of days from now using:

days=$(( ( $(date '+%s') - $(date -d '1 months ago' '+%s') ) / 86400 ))
echo $days

and then use the variable to delete

find /logs/CopyCluster* -mtime +$days -type f -delete

To do this all in one command

find /tmp/*.log -type f \
-mtime "+$(( ( $(date '+%s') - $(date -d '1 months ago' '+%s') ) / 86400 ))" -delete

 

 

Setting up SQL Server vNext CTP 2.0 on Docker

Recently I moved into the brave new world of having a Macbook pro as my main laptop, and one of my first frustrations was the inability to be able to run SQL Server Management Studio locally. Unfortunately Microsoft haven’t come up with a solution for that one, so I’m having to do with a mix of SQLPro Studio and running SSMS inside a VDI machine on the mac.

One thing the Mac has made easier for me is the ability to start testing SQL Server vNext locally and without too much messing around. So when Microsoft released vNext 2.0 on the 19th April 2017, it seemed like a good opportunity to give it a try. (Whats new in SQL Server Linux)

This post should cover what you need to do to get a docker image of SQL Server running on macOS Sierra. The Microsoft documentation is pretty good on this subject, and not convoluted but I always find it good to blog how exactly I’ve done things, as there are always little quirks.

Install Docker for Mac

I downloaded the stable version just to be a bit safer and so that I wouldn’t introduce any unnecessary pinch points.

https://docs.docker.com/docker-for-mac/install/

Once installed, Start the Docker application – it may take a few minutes to initialise, but eventually you will end up with the docker ship icon in the top bar.

There are a few minimum requirements for running docker for the SQL Server image:

  • Docker Engine 1.8+ on any supported Linux distribution or Docker for Mac/Windows.
  • Minimum of 4 GB of disk space
  • Minimum of 4 GB of RAM

If your downloading a recent release of docker then the engine shouldn’t be an issue.

At the time of writing, my version is:

$ docker version
Client:Version	17.03.1-ce
API Version: 1.27
Go Version: go1.7.5

Server:
Version: 17.03.1-ce
API Version: 1.27 (minimum version 1.12)
OS/Arch: linux/amd64

Docker default starts with 2GB of RAM, but this can be easily changed to 4GB from the docker preferences in the advanced section.

Pull down and run the latest SQL Server Docker Image – Step by Step details here – https://docs.microsoft.com/en-gb/sql/linux/sql-server-linux-setup-docker

Quick Steps here:

sudo docker pull microsoft/mssql-server-linux

Note that I have added a –name switch to the docker command below. This is to simplify things once we have everything up and running as the container name is quite critical for more or less every command in docker.

sudo docker run --name SQL2017 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=' -p 1433:1433 -d microsoft/mssql-server-linux

To make sure we have successfully created our container, we can run:

docker ps -a

To familiarise yourself with other docker commands, you can run:

docker --help

Connect to the SQL Server

sudo docker exec -it SQL2017 /bin/bash

The flags are for the following:

-i, –interactive Keep STDIN open even if not attached–privileged Give extended privileges to the command-t, –tty Allocate a pseudo-TTY

This then takes you into the interactive command line for the docker image.

The folder structure by default is /var/opt/mssql for the database files & error logs. Sqlcmd is in /opt/mssql-tools/bin/

Reading the error log

While in exec mode for the SQL2017 container, run the following cat command:

cat /var/opt/mssql/errorlog

There is a very good guide by microsoft on how to troubleshoot SQL Server Linux which can be found here – https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide

Using sqlcmd

Starting with SQL Server vNext CTP 2.0, the SQL Server command-line tools are included in the Docker image. If you attach to the image with an interactive command-prompt (as I have done above), you can run the tools locally.

First step is to add the sqlcmd path to the $PATH environment variable. This step isn’t critical but makes sqlcmd accessible from any location on the docker image

PATH=$PATH:/opt/mssql-tools/binecho 'export PATH="$PATH:/opt/mssql-tools/bin"'
~/.bash_profile 
root@5214e1df3c86:/opt/mssql-tools/bin# echo $PATH
/opt/mssql-tools/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
sqlcmd -S . -U sa -P
1> select name from sys.databases
2> go
name
-----------------------------------------------------------
master
tempdb
model
msdb

Installing SQLCMD on macOS

/usr/bin/ruby -e "$(curl -fsSL <a href="https://raw.githubusercontent.com/Homebrew/install/master/install" data-mce-href="https://raw.githubusercontent.com/Homebrew/install/master/install">https://raw.githubusercontent.com/Homebrew/install/master/install</a>)"
brew tap microsoft/mssql-preview <a href="https://github.com/Microsoft/homebrew-mssql-preview" data-mce-href="https://github.com/Microsoft/homebrew-mssql-preview">https://github.com/Microsoft/homebrew-mssql-preview</a> 
brew update 
brew install mssql-tools #for silent install ACCEPT_EULA=y 
brew install mssql-tools

 

Can be quickly tested locally by opening a terminal window and running:

sqlcmd -S my-remote-server -U test -P test
1&gt; select @@SERVERNAME
2&gt; go
-----------------------------------------------------------------
SQLSERVER/MYNAMEDINSTANCE
(1 rows affected)

Connecting into the docker image from host machine

Get the IP of the container

MCR-AL33450:/mohsin.alipatel$ docker inspect --format "{{ .NetworkSettings.Ports}}" SQL2017
map[1433/tcp:[{0.0.0.0 1433}]]

Then make a sqlcmd connection using the IP and port above

MCR-AL33450:/mohsin.alipatel$ sqlcmd -S 0.0.0.0,1433 -U sa -P
1&gt;Select @@servername
2&gt;go-----------------------------------------------------------------------------------------------
5212e1df3c86
(1 rows affected)

This should provide a basic introduction to both docker and to SQL server on linux.

Retrieve Oracle SPID from unix OS PID

The Unix/Linux PID that you see in ‘top’ is visible as SPID in V$PROCESS. Then, you’d have to join V$PROCESS to V$SESSION to get the SID. (Note : PID in V$PROCESS is the Oracle PID, not the OS PID).

--Replace bind variable with OS PID List
select s.sid, s.serial#, s.username,
       to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,
       p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
       s.program clientprogram, s.module, s.machine, s.osuser,
       s.status, s.last_call_et
from  gv$session s, gv$process p
where p.spid=nvl('&unix_process',' ')
and s.paddr=p.addr
order by s.sidÔ…

Apache Rewrite Rules

I recently worked on Migrating an Application from one oracle database to another whch included transferring over to the new htmldb apex.

I wanted to append a redirect rule onto a web link to add HTMLDB path to send users to the correct application.

Amending Apache Conf:

cd /u01/app/oracle/product/10.1/htmldb/Apache/Apache/conf/includes
more myappconfig

[LIVE]:/u01/app/oracle/product/10.1/htmldb/Apache/Apache/conf/includes : more myappconfig

<VirtualHost *>
        ServerName myapp.gen.mycompany.net
        ServerAlias myapp.live.mycompany.net
        ServerAlias myapp.live.dc1.mycompany.net
        ServerAlias myapp.live.dc2.mycompany.net
        ServerAdmin
webmaster@mycompany.co.uk
        DocumentRoot "/u01/app/oracle/product/10.1/htmldb/Apache/Apache/htdocs"

        RewriteEngine    on
        RewriteLogLevel  0

        RewriteRule ^/pls/htmldb/f   /pls/htmldb2/f?p=152:1 [R=301]
        RewriteRule ^/$ /pls/htmldb2/f?p=152:1 [R=301]
</VirtualHost>

A rewrite rule of

RewriteRule ^/$ /pls/htmldb2/f?p=152:1 [R=301]

Will append the ‘/pls/htmldb2/f?p=152:1’  anyone who goes to any of the server alias

A rewrite rule of

RewriteRule ^/pls/htmldb/f   /pls/htmldb2/f?p=152:1 [R=301]

Will change any addresses which come in with ‘^/pls/htmldb/f’ to change to ‘/pls/htmldb2/f?p=152:1’

To Add logging to the Rewrite config add the following:

       #RewriteLog  /u01/app/oracle/product/10.1/htmldb/Apache/Apache/logs/myapp.rewrite.log
       #ErrorLog    /u01/app/oracle/product/10.1/htmldb/Apache/Apache/logs/myapp.error.log
       #CustomLog   /u01/app/oracle/product/10.1/htmldb/Apache/Apache/logs/myapp.access.log combined

–to see database connectivity for apache and htmldb

/u01/app/oracle/product/10.1/htmldb/Apache/modplsql/conf : more marvel.conf

Backgrounding a linux task

Problem:  Want to make a Oracle mview refresh run in the background so it doesnt timeout after 2 hours of running (our company’s network timeout)

On the Oracle Server

  • Create the file with the SQL code you want to run. Mine is called mv_fp.sql
  • BEGIN
      DBMS_SNAPSHOT.REFRESH(
        LIST => 'SCHEMA.MV_MVIEW'
       ,METHOD => 'C'
       ,PUSH_DEFERRED_RPC => TRUE
       ,REFRESH_AFTER_ERRORS => FALSE
       ,PURGE_OPTION => 1
       ,PARALLELISM => 4
       ,ATOMIC_REFRESH => TRUE
       ,NESTED => FALSE);
    END;
    /
  • Create the file with the shell code you want to run. Mine is called mv_fp.sh
  • export ORACLE_SID=MYDB
    sqlplus schema/password << eof
    @mv_fp.sql
    exit
    eof
    • Grant execute access on the .sh file

    chmod 744 mv_fp.sh
    • Run the following to background the task

    nohup ./mv_fp.sh > nohup_mv_fp.out 2>&1 &

Useful Unix Commands

Remove archive log files older than 2 days:

-- list them first to confirm
find .*.arc -mtime +2 -type f -maxdepth 1 -exec ls -ltr {} \;
-- then delete    
find .*.arc -mtime +2 -type f -maxdepth 1 -exec rm {} \;

Remove trace files – same as above:

-- list them first to confirm
find . -name "*.trc" -mtime +90 -type f                        
-- then delete
find . -name "*.trc" -mtime +90 -type f -exec rm -f '{}' \; 

Different way of doing the above:

-- to find files. Value is number of days
find . -maxdepth 1 -name "*.xml"  -mtime +10 -type f

-- to delete files. value is number of days
find . -maxdepth 1 -name "*.xml" -mtime +10 -type f -exec rm -f '{}' \;

Loop a command:

while true; do crs_stat -t; sleep 5; done

Copy Files from one server to another:

scp <file> <user>@<target>:<path>
eg: scp test.txt oracle@SERVERA:/export/home/oracle

Messages on linux:

var/log/messages

Changing Permissions and Owners:

-- Change owner
chown <owner> <file>
chown oracle CSSfile
-- Change group
chgrp <group> <file>
chgrp dba CSSfile
-- Change permissions
r = 4    w = 2   x = 1
owner|group|all
  421   |  421   |421
  rwx       rwx     rwx
--for read, write, all for all would be:
chmod 777
--for drwxr-xr-x
chmod 755

Tar files:

-- taring a folder
tar -cvfp <tar filename/location> <top level of dir you want to tar>
-- Notes
-- c = create file v = verbose f = files p = preserve permissions
-- Untaring a folder
tar -xvf <name of tar>
-- untaring a folder to location
tar -xvf <name of tar> -C <dir to untar to>

Search and replace:

:%s/<SEARCH_STRING>/<REPLACE_STRING>/g    - /g means global