Posts

Featured Post

Creating trace file, writing to alert.log and retrieve using Amazon RDS for Oracle

Image
Since in Oracle RDS environment, we don't have a direct access to the operating system I have recently been asked to provide a workaround from the database. A procedure that: 1. Write to the alertlog 2. Generate a trace file (no matter its content). 3. Let the user know how to query a. The alertlog b. The trace file Oracle RDS implementation enable us this functionality without a need to create objects or have a special grants. SET SERVEROUTPUT ON VERIFY OFF FEED OFF LINES 300 PAGES 200 DECLARE     full_file_name   VARCHAR2 (512);     file_name        VARCHAR2 (512);     MESSAGE          VARCHAR2 (1024);     ALERT_TABLE      VARCHAR2 (30);     rds_exists       NUMBER;     TABLE_MISSING    EXCEPTION;     PRAGMA EXCEPTION_INIT (TABLE_MISSING, -942);     FUNCTION FIND_VIEW (object_name VARCHAR2)         RETURN NUMBER     IS     BEGIN         EXECUTE IMMEDIATE 'select count(1) from ' || object_name;         RETURN 1;     EXCEPTION         WHEN

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

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

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

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 bindi

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 primary Primary makes update, generates & sends redo for that update to all standbys Active Data Guard session sees the update in redo apply and resumes Preserves ACID properties for ADG session Redirected update only visible to session before commit; visible to all sessions after commit For “Mostly Read, Occasional Updates ” applications – for example, recording user logins for auditing purposes Enabled 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 i

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="&q