Posts

Featured Post

Connect to Oracle Database server through ssh tunnel

Image
Last week I needed to connect a database from remote using VPN.
The VPN is connected to a secured network, so I couldn’t connect directly to the database.
Since I am using Toad for Oracle which is based on oracle client – I needed a transparent solution for that connection.



At the past I wrote a post about Using ssh X11 tunnel through a bastion host to connect to a database server , at that post I described how to forward SSH and X11.
Now I will describe a way to connect to the database using SQL*Net (usually port 1521) on Windows.

Download plink from https://www.putty.org , you can download putty package or just standalone plink.
Run the following command line:
plink -N -L localport:dbserver:dbport getwayuser@getwayserver Do this on both machines (REMOTE PC Windows machine and the server you have access to - Gateway) to chain the SSH tunnels.

Example:

Gateway server (assuming Linux):
ssh -N -L1521:dbserver:1521 dbserveruser@dbserver Your PC:
plink -N -L 1521:getwayserver:1521 getwayus…

Using ssh X11 tunnel through a bastion host to connect to a database server

Image
Hi,

I succeeded running Oracle dbca, xclock & virt-viewer using Bastion over VPN (with root) 😊

Theoretical Steps: First, connect to machine B and forward [localPort] to C:22 through B
A$ ssh -L [localPort]:C:22 B Next, connect to C from A through this newly-created tunnel using [localPort], forwarding X11
A$ ssh -X -p [localPort] localhost Now we can run X11 programs on C and have them display on A
C$ xclock [localPort] can be any port that you are not already listening to on A, I often use 2222 for simplicity.
X11Forwarding should be enabled on server C
AllowTcpForwarding should be enabled on server B
Actual Steps First, connect to machine B and forward [localPort] to C:22 through B
A$ ssh -L [localPort]:C:22 B Next, connect to C from A through this newly-created tunnel using [localPort], forwarding X11
From A using putty to localhost using [localPort] enable X11 forwarding to localhost:0.0
Now we can run X11 programs on C and have them display on A
C using putty:
C$ export DISP…

How to install Oracle Linux in kvm without a need to X server

Image
Hi,
I managed to install Oracle Linux on kvm guest without a need to X console (no GUI, vnc or X11)
1.We can see the PXE menu
2.We can select image to install
3.We need to add console=ttyS0 to the PXE (cobbler is supporting this feature the same way as grub enable you to add parameter to the boot)

Disclaimer:
I tested this on Oracle Linux 7.5 using libvirt 1.5

# virt-install --hvm --connect qemu:///system --network=bridge:virbr0 --pxe --graphics none --name Oracle_Linux_7.4-x86_64 --ram=756 --vcpus=1 --os-type=linux --os-variant=rhel7 --disk path=/tmp/rhel7-machine.img,size=5 --console pty,target_type=serial --boot 'menu=on,useserial=on'
Starting install...
Allocating 'rhel7-machine.img'                                                                                                                       | 5.0 GB  00:00:00   
Connected to domain Oracle_Linux_7.4-x86_64
Escape character is ^]
Google, Inc.
Serial Graphics Adapter 04/30/14
SGABIOS $Id: sgabios.S 8 2010-0…

rlwrap and auto completion in sqlplus

Image
It is a while I am following the blog of Mariami Kupatadze, it seems that our background and daily work is around the same area and interest as mine.
This week she posted a simple Linux adjustment (sqlplus backspace – ^H, delete – [[D^, add command history) for better way of using sqlplus.
Since I am also a geek of environment improvements that can ease daily work, I want to add a nice variation.

An example of basic rlwrap usage:
rlwrap sqlplus / as sysdba or use sqlplus as alias with rlwrap:
alias sqlplus='rlwrap sqlplus' After some usage you will notice that default history file is created here ~/.sqlplus_history
I usually copy this file to ~/sqlplus.wordlist and edit it with wanted completion word.

My personal file looks this way:
abort
active
affirm
all_roles
alter
alternate
applied
applied_seq#
applied_thread#
apply
apply_finish
apply_lag
archived
archived_seq#
archived_thread#
archiver
async_blocks
attribute
availability
begin
binding
block#
blocks
blocksize
both
bytes

Oracle 19c Updates on Active Data Guard using DML Redirection

Image
Previously, you could only update Global Temp Tables on Active Data Guard standby database. From Oracle 19c, you can also update regular tables.
Updates on Active Data Guard:Update will be redirected to the primaryPrimary makes update, generates & sends redo for that update to all standbysActive Data Guard session sees the update in redo apply and resumesPreserves ACID properties for ADG sessionRedirected update only visible to session before commit; visible to all sessions after commitFor “Mostly Read, Occasional Updates” applications – for example, recording user logins for auditing purposesEnabled by ADG_REDIRECT_DML at system or session level The following setup is based on Active Data guard configuration is MaxAvailability, but the behavior is the same when configuring MaxPerformance

A step by step guide to demonstrate this feature:
Current configuration Basic setup  The setup is primary database sends its redo logfiles through a far sync instance to a remote standby database…

Oracle Database Auto Discovery

Image
Hi,
I was asked by our development team to provide the best way to identify database parameters from database host, I was surprised to find so many options. Identifying all instances on the current machine Option 1:  $ ps -ef |grep smon | grep -v grep
oracle    3025  1  0  2016 ?   00:00:48 asm_smon_+ASM
oracle   11459  1  0 17:24 ?  00:00:00 ora_smon_fdb oracle SID is fdb and process id is 11459
Option 2:  $ pgrep  -lf _pmon_
3025 asm_pmon_+asm
11459 ora_pmon_fdb oracle SID is fdb and process id is 11459 Option 3:  cleaner way for sid:
$ ps -ef |grep 'ora_smon_.*$' | grep -v grep | awk -F_ '/ora_smon/{print $NF}'
fdb oracle SID is fdb 
Option 4:  When we already know ASM home (grid infrastructure) we can use the cluster commands:
$ /oracle/product/12.1.0.2/grid/bin/crsctl stat res -t -w "TYPE = ora.database.type"|awk '/^ora./ {l=$0;} !/^ora./ { if ( l > "" ) l=l " " $0; print l;l="";}'|grep  ${HOSTNAME%%.*}
 ora.pdb.db…

Creating SNMP Plug-In for Enterprise Manager Cloud Control

Image
Putting it In my previous blog (Enterprise Manager Cloud Control, SNMP, and Plug-Ins –
The Background), I shared my frustration in trying to create
a plug-in in OEM. Here are the technical steps for a very simple plug-in.
Preparing Plug-in
To build your own plug-in, you need to download a development kit:
To download EDK using UI, from the Setup menu, selectExtensibility, and then select Development Kit.OR
To download EDK using EMCLI, run the following steps:Log in to OEM:$ /oracle/em/middleware/bin/emcli login -username=sysmanEnter password :

Login successful Download the EDK zip archive:$ emcli get_ext_dev_kitDownloading edk.zipFile saved as edk.zipFile(s) downloaded successfully Unpack the downloaded EDK ZIP archive to a directory on your local
system. For example:
$ unzip 13.3.0.0.0_edk_partner.zip There are several restrictions and guidelines you should know before you create your plug-in.
Most of them can be found at the following link: About Plug-in Metadata
Restrictions …