Monday, February 02, 2015

Safe browsing using browser in VM

We often searching something desperately clicking to all kinds of links that a search engine brings up. And who knows what scripts or worms are going to be executed in our browser. To make this surfing process safer I've decided to run browser executable in a VM (virtual machine) and enjoy only "picture and sound" here on my workstation. VM can be easily restored from a snapshot if something bad happen and it got corrupted by something evil from The Net.
I use my local browser for browsing safe sites (email, banking etc). For other potentially unsafe browsing I use browser in VM.
How I've made the thing?
I use Linux and I've installed KVM on it so it can run VMs.
I've created a VM with like 2GB RAM and 2 core CPU, it should be enough.
Then some little Linux has been installed - Lubuntu. In that Lubuntu I've installed SSH and Firefox. Done. I can run Firefox in X window via ssh with the command:
ssh -X lubuntu firefox 

It works well, but there is no sound. :(

In Linux, sound can be streamed from one machine to another different ways, but the most convenient one is to use PulseAudio because it is already here. However it does not communicate via TCP by default. It has to be configured. Following modules should be included into your local ~/.pulse/default.pa file at host machine to allow PulseAudio listen on TCP port (4713):

load-module module-native-protocol-tcp
load-module module-x11-publish


then restart your pulseaudio by killing it it will restart automatically then

killall pulseaudio

Check if pulseaudio listens on TCP port 4713

xprop -root | grep PULSE_SERVER

PULSE_SERVER(STRING) = "{32615b6567257d71581bac8751245653}unix:/run/user/1000/pulse/native tcp:ustudio.localhost:4713 tcp6:ustudio.localhost:4713"




Copy file ~/.pulse-cookie to the VM to user's home.

scp ~/.pulse-cookie user@lubuntu:~/

Check now if you can hear file played in VM

ssh -Y user@lubuntu -R 4713:localhost:4713 "PULSE_SERVER=localhost paplay /usr/share/sounds/alsa/Front_Center.wav"

You should hear it, if everything is OK.
Check same way ALSA. (By default ALSA is streamed to PulseAudio. Flash and other programs may use ALSA not PulseAudio).

ssh -Y user@lubuntu -R 4713:localhost:4713 "PULSE_SERVER=localhost aplay /usr/share/sounds/alsa/Front_Center.wav"

If both work, there should not be problems with running a browser.
Lets test.

ssh -Y user@lubuntu -R 4713:localhost:4713 "PULSE_SERVER=localhost firefox http://www.youtube.com"

Play some video there. You should hear its sound.

Please note - the browser works in VM (as X client). Here, on your host machine, it is only its visualization and sound. Well, also the window where it is visualized (X server) gets your mouse and keyboard and sends it to X client - the actual browser that runs in VM. (Please read about X basics if my explanation is not clear :) )
But it looks seamless! The only difference you see - is addition "Mozilla Firefox (on lubuntu)" in header of the window.

You can create little script MySafeBrowser.sh that starts the VM (if it is not yet started) and starts the browser in it.

virsh start Lubuntu
ssh -Y user@lubuntu -R 4713:localhost:4713 "PULSE_SERVER=localhost firefox" 

Done!
Do not forget to take snapshot of the VM.

Monday, January 13, 2014

Oracle 12c. Accessing tables in PDB from CDB or another PDB.

If you are reading this, you should know that Oracle 12c  introduced the new big feature – Pluggable Data Bases. Finally Oracle has made the thing that was available in other RDBMSs for years – now you can have multiple databases run by one instance. This is available from very beginning in RDBMSs such as Sybase, DB2, MSSQL, MySQL… you name it.

In these RDBMSs a user being currently in one database can access tables in another database. For example, in MSSQL you can be in DB01 and select data from DB02 (given that you have permissions):

use DB01
select * from DB02.dbo.ATABLE


It is not that simple in Oracle 12c! You cannot specify PDB before schema. You have to switch or to connect to a PDB to access data in that.

ALTER SESSION SET CONTAINER=PDB02;
select * from ASCHEMA.ATABLE;


Or

CONNECT ASCHEMA@PDB02
select * from ATABLE;


But what if you need to select data from a PDB while you are in CDB or in another PDB?
Oracle documentation states: “The common user cannot query tables or views in a PDB.” 1
“Wait a second! How to query/join data from different PDBs for reports or comparisons?” you may ask.
Relax, it is still possible. The answer is – DATABASE LINK.

To make it possible you should first modify tnsnames.ora file on database server, in Oracle Home of the instance.

Add there TNS entries for PDBs you would like to access.

pdb01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb01)
    )
  )


Second, create DB link.

connect system/password
create public database link PDB01 using 'PDB01';
select * from ASCHEMA.ATABLE@PDB01;


Done!

In this case the DB link was created in CDB, so common users can access data in PDB01.
If you need to allow a local user to access another PDB you should create DB link in the PDB of the local user.

You can play with public or user’s DB links to build security you need, main idea is to access PDBs via DB links.

References:
1.           Oracle® Database Administrator's Guide 12c Release 1 (12.1) , Chapter 40 Administering a CDB with SQL*Plus http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#ADMIN13616 )

Thursday, December 19, 2013

Poor export performance of SAP database and how to speed it up.

During an export (regular exp not datapump) of SAP database with CONSISTENT=YES I've found that performance was suboptimal. There was 100% CPU consumption, and even empty tables were exported one per 2-3 sec!
The export took so long time that finally caused it crash with "Snapshoot too old".

Investigation showed that the exp called "SYS"."DBA_CAPTURE_PREPARED_TABLES" view for each table. And, you may be know, there can be 80-90 thousands of tables in SAP schema.
In my DB there were 3 SAP schemas so total number of tables was quite big.

I've got an execution plan of the offensive query that exp called:

SELECT NVL(MAX(CO.SCN), 0) FROM ALL_CAPTURE_PREPARED_TABLES CO WHERE CO.TABLE_OWNER = :B2 AND CO.TABLE_NAME = :B1;

And the plan shows full scan of SYS.CDEF$ table.

ALL_CAPTURE_PREPARED_TABLES calls DBA_CAPTURE_PREPARED_TABLES.
The query of DBA_CAPTURE_PREPARED_TABLES view in my Oracle 10g is:

 select u.name, o.name, co.ignore_scn, co.timestamp,
       decode(bitand(cd.flags, 1), 1,
              decode(bitand(co.flags, 1), 1, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(cd.flags, 2), 2,
              decode(bitand(co.flags, 2), 2, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(cd.flags, 4), 4,
              decode(bitand(co.flags, 4), 4, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(cd.flags, 8), 8,
              decode(bitand(co.flags, 8), 8, 'IMPLICIT', 'EXPLICIT'), 'NO')
  from obj$ o, user$ u, streams$_prepare_object co,
       (select obj#, sum(DECODE(type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)) flags
          from sys.cdef$ group by obj#) cd

  where o.obj# = co.obj# and o.owner# = u.user# and co.obj# = cd.obj#(+);


Please note highlighted text. The combination of aggregating inline view cd and its left outer join caused Oracle to full scan whole CDEF$ table, aggregate it to calculate "FLAGS" for each table and then hash join it to just one row from streams$_prepare_object to get cd.FLAGS.
Not good.

To speed up the query I tried all legitimate ways - gathered stats, set performance hints like FIRST_ROWS(1) PUSH_PRED(cd) USE_NL_WITH_INDEX(cd I_CDEF2) and so on.
But nothing convinced the optimizer to push OBJ#= predicate into the cd inline view.

So I've just ended up with total rewrite the view's query as:

CREATE OR REPLACE FORCE VIEW "SYS"."DBA_CAPTURE_PREPARED_TABLES" ("TABLE_OWNER", "TABLE_NAME", "SCN", "TIMESTAMP", "SUPPLEMENTAL_LOG_DATA_PK", "SUPPLEMENTAL_LOG_DATA_UI", "SUPPLEMENTAL_LOG_DATA_FK", "SUPPLEMENTAL_LOG_DATA_ALL") AS
  /* made by MM to speedup export */

  select
       u.name, o.name, co.ignore_scn, co.timestamp,
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 1), 1,
              decode(bitand(co.flags, 1), 1, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 2), 2,
              decode(bitand(co.flags, 2), 2, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 4), 4,
              decode(bitand(co.flags, 4), 4, 'IMPLICIT', 'EXPLICIT'), 'NO'),
       decode(bitand(sum(DECODE(cd.type#, 14, 1, 15, 2, 16, 4, 17, 8, 0)), 8), 8,
              decode(bitand(co.flags, 8), 8, 'IMPLICIT', 'EXPLICIT'), 'NO')
  from streams$_prepare_object co
  join obj$ o on o.obj# = co.obj# 
  join user$ u on o.owner# = u.user# 
  left outer join sys.cdef$ cd on co.obj# = cd.obj#
  group by u.name, o.name, co.ignore_scn, co.timestamp, co.flags;


that produces same results, but 5000 times faster.
I know, it is not good to modify dictionary objects. But what else to do when you need the thing done fast?
Export of empty tables become lightning fast, and full export has been finally taken.
If you are going to do this trick too, do not forget to restore the original view's SQL, that is provided by Oracle.

Saturday, February 23, 2013

Setup Subversion 1.7 into JDeveloper 11R2

I do not know for what reason Oracle JDeveloper R2 still does not support Subversion 1.7, but I've managed to make it to support it. I've done it n Linux, but you can apply it for Windows too.
So.
That is what I have:
- Ubuntu 11.20 Linux 3.5.0-24-generic SMP x86_64
- JDeveloper Studio Edition 11.1.2.3.0

1. Check what Subversion is installed in JDeveloper.
Start JDeveloper and open menu Tools -> Preferences, select Versioning/Subversion: see
SVNKit/1.3.5 Client installed. JNA disabled.

2. Close JDeveloper.

3. This step is optional. Install linsvn-java package if you think you will need some fancy ways to login to Subversion using system security.

sudo apt-get install libsvn-java

4. Download SVNKit Standalone Version 1.7 from http://svnkit.com/download.php
cd /download/svn/
wget http://www.svnkit.com/org.tmatesoft.svn_1.7.8.standalone.zip
unzip  org.tmatesoft.svn_1.7.8.standalone.zip


/download/svn/svnkit-1.7.8 directory is created

5. Download Subclipse 1.8 from http://subclipse.tigris.org
cd /download/subclipse/
wget http://subclipse.tigris.org/files/documents/906/49260/site-1.8.18.zip
unzip site-1.8.18.zip 

later we will need only files from /download/subclipse/plugins

5. Go to JDeveloper extension directory (my JDev is installed in /opt/oracle/) and do following:
cd /opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/extensions
mv oracle.jdeveloper.subversion oracle.jdeveloper.subversion-1.3
mkdir oracle.jdeveloper.subversion-1.7
ln -s oracle.jdeveloper.subversion-1.7 oracle.jdeveloper.subversion

6. Copy required SVNKit files here and link them as "versionless" names
cd oracle.jdeveloper.subversion
cp /download/svn/svnkit-1.7.8/lib/antlr-runtime-3.4.jar .
cp /download/svn/svnkit-1.7.8/lib/svnkit-1.7.8.jar .
cp /download/svn/svnkit-1.7.8/lib/sqljet-1.1.6.jar .
cp /download/svn/svnkit-1.7.8/lib/sequence-library-1.0.2.jar .
cp /download/svn/svnkit-1.7.8/svnkit-javahl16-1.7.8.jar .
cp /download/svn/svnkit-1.7.8/trilead-ssh2-1.0.0-build215.jar .

ln -s antlr-runtime-3.4.jar antlr-runtime.jar
ln -s svnkit-1.7.8.jar svnkit.jar
ln -s sqljet-1.1.6.jar sqljet.jar
ln -s sequence-library-1.0.2.jar sequence-library.jar
ln -s svnkit-javahl16-1.7.8.jar svnkit-javahl.jar
ln -s trilead-ssh2-1.0.0-build215.jar trilead.jar


7. Copy required Subclipse files here and link them as "versionless" names

cp /download/subclipse/plugins/net.java.dev.jna_3.4.0.t20120117_1605.jar .
cp /download/subclipse/plugins/org.tigris.subversion.clientadapter_1.8.3.jar .

cp /download/subclipse/plugins/org.tigris.subversion.clientadapter.javahl_1.7.8.1.jar .
cp /download/subclipse/plugins /org.tigris.subversion.clientadapter.svnkit_1.7.8.1.jar . 

ln -s net.java.dev.jna_3.4.0.t20120117_1605.jar jna.jar
ln -s org.tigris.subversion.clientadapter_1.8.3.jar svnClientAdapter.jar 
ln -s org.tigris.subversion.clientadapter.javahl_1.7.8.1.jar svnjavahl.jar
ln -s org.tigris.subversion.clientadapter.svnkit_1.7.8.1.jar casvnkit.jar

5. Go to JDeveloper jdev/bin directory and open jdev.conf file in your favorite text editor:
cd /opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/bin
gedit jdev.conf

6. Add following to the end
#
# Needed for Subversion 1.7
#
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/antlr-runtime.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnkit.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/casvnkit.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/sqljet.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnClientAdapter.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnjavahl.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/trilead.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnkit-javahl.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/sequence-library.jar

# If you want to use JNA
#AddNativeCodePath /usr/lib/x86_64-linux-gnu/jni/libsvnjavahl-1.so
#AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/jna.jar

# for debug
#AddVMOption -Djava.util.logging.config.file=../extensions/oracle.jdeveloper.subversion/svnkit-logging.conf
#AddVMOption -verbose


7. Check what Subversion is now installed in JDeveloper.
Start JDeveloper and open menu Tools -> Preferences, select Versioning/Subversion: see
SVNKit/1.7.8 Client installed. JNA Disabled.

Done!
Now you can connect to your Subversion 1.7 repository and use Versioning Navigator, commit files in Application Navigator etc.


Some additional notes!

I had issues when I wanted to add a project or an application to Versioning (using JDK 1.6 (i386) and 1.7 (amd64) ). SVNKit returned exception
 java.lang.NullPointerException at org.tmatesoft.svn.core.internal.wc.SVNClassLoader.loadProperties(SVNClassLoader.java:193)

I've filed the bug to SVNKit issues tracker http://issues.tmatesoft.com/issue/SVNKIT-352 .
However if you do not want to wait for official update that includes the fix, you can fix it yourself.
1. unzip sources
cd /download/svn/svnkit-1.7.8-nojna/src
mkdir svnkit
cd svnkit
unzip ../svnkit-1.7.8-sources.jar 

2. unzip classes
cd /opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/extensions/oracle.jdeveloper.subversion
mkdir svnkit
cd svnkit
unzip ../svnkit.jar

3. copy source to classes
cd org/tmatesoft/svn/core/internal/wc/ 
cp /download/svn/svnkit-1.7.8-nojna/src/svnkit/org/tmatesoft/svn/core/internal/wc/SVNClassLoader.java .


4. modify code to fix the bug
Open for edit SVNClassLoader.java
go to line 193

resourceStream = SVNClassLoader.class.getClassLoader().getResourceAsStream(svnkitPropertiesResource);
delete it

insert here the following

ClassLoader cl = SVNClassLoader.class.getClassLoader();
if (cl == null) cl = ClassLoader.getSystemClassLoader();
resourceStream = cl.getResourceAsStream(svnkitPropertiesResource);

save, exit from editor


5. compile the file
/opt/oracle/jdk1.7.0_15-amd64/bin/javac -g:source,lines,vars -source 1.5 -target 1.5 -cp /opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/extensions/oracle.jdeveloper.subversion/sequence-library.jar:/opt/oracle/middleware-11.1.2.3/jdeveloper/jdev/extensions/oracle.jdeveloper.subversion/svnkit.jar SVNClassLoader.java

6. modify jdev.conf to use svnkit dir instead of svnkit.jar
AddVMOption -Xbootclasspath/a:../extensions/oracle.jdeveloper.subversion/svnkit

If you need that SVNKit to generate a detailed output create file
../extensions/oracle.jdeveloper.subversion/svnkit-logging.conf and populate it with something like
svnkit.level=FINEST
svnkit-network.level=FINEST
svnkit-fsfs.level=FINEST
svnkit-wc.level=FINEST
svnkit-cli.level=FINEST


----------
Note. By some reason History in JDeveloper does not work with SVN. Though you can see change log in Version Navigator or other ways.











Tuesday, February 19, 2013

Setup of Alsa on EMU0404 PCI

If you have no idea what is Alsa, Jack and emu10k1 you can stop reading right here.

I've built a new PC based on ASUS mobo P8Z77-M and installed Ubuntu Studio 12.10 on it. Also I've plugged emu0404 PCI (not PCIe) sound card into it. Though the card is supported by Soundblaster/Emu only for Windows, there are Alsa drivers for Linux too. I've successfully installed alsa-base and other alsa packages that include emu10k1 driver and firmware for it. However there were some issues.

Issue #1. Initially output to emu was broken. aplay repeated 1 sec fragment of sound many times. The issue is appeared to be in poor interrupts distribution by BIOS, or by inability this card to share an interrupt with other devices, in my case it was USB host controller. I could not plug the card into another slot, because there is only one legacy PCI slot on mobo. I tried to boot with kernel parameters acpi=off noapic and other acpi and pci options. Sometimes it worked, but often not.
The issue has been fixed by updating BIOS to 1806.

Issue #2. Playback to emu is perfect but there is no input capture. I've turned on all switches in alsamixer, maxed up all shaders. Still no capture. Somehow I've googled this thread: https://ardour.org/node/4061 . I started to set all alsamixer settings according to what linuxdsp user has recommended. And Ta-Daa! Sound got captured into Alsa. The important thing that was not described anywhere is to set internal routing in emu0404.
Specifically, in alsamixer on Capture tab set [DSP 0] to [0202 ADC Left] and [DSP 1] to [0202 ADC Right] accordingly.

Monday, December 17, 2012

PIOB started

I was looking for a tool to measure I/O from Oracle to disks. The only tools were available ORION and SLOB. These are very good tools and they do the job, however these have some limitations.
First of all they are OS dependable. Even SLOB that is mostly implemented in PL/SQL. It also uses Bash and one binary component for inter-process communication. It also uses AWR that should be licensed additionally.
I've decided I'll create something similar but without these limitations.
Couple of days of work and it is done.
It is named PIOB - PL/SQL Input/Output Benchmark (tool).

It generates different kinds of I/O using Oracle database means and measures it.

As it said, the PIOBs idea is same as SLOB (Silly Little Oracle Benchmark) - PIOB benchmarks disk I/O using Oracle.
You can use PIOB for similar tasks - tuning of disk subsystem on a machine where you have your Oracle.

How it works

   When you call PIOB_TOOL.RUN* it will create several Oracle Scheduler jobs for
   workers that you specified with *_WORKERS parameter(s).
   These jobs will simultaneously execute workers.
   Oracle I/O metrics will be saved just before workers start generating
   actual I/O and right after that.
   When you run PIOB_TOOL.REPORT the saved metrics will be used to calculate
   more commonly used metrics: IO/s for short random I/O and MB/s for long sequential I/O.

It can create any combination of 5 types of workload:
- Short Read - random reads by one DB block - index access.

- Long Read - random reads by multiple DB blocks - full table scans.
- Light Write - modification of one column in random table row: index access, one block modification, little redo.
- Heavy Write - modification of all columns in random range of table 256 rows: index range scan, multiple block modification, large redo.
- Redo Write - modification of all column in one table row: index range scan, same one block modification, large redo.

Other features of PIOB are:
 - it is OS and platform independent because it is implemented purely in
   Oracle SQL*Plus, SQL and PL/SQL;
 - it does not use Oracle AWR (AWR requires additional licensing);
 - it takes snapshots of only relevant performance metrics;
 - it is instrumented for monitoring its progress;
 - SEED table is tuned to have 1 row per 8K block for more precise calculation
   of random I/O;
 - it uses RECYCLE buffer of minimal size to reduce caching (can be turned off);
 - it keeps some configuration parameters in its repository;
 - its work can be limited by amount of work cycles or by timer;
 - it can be stopped anytime.

Of course it is free, GPLv3.

You can download it from PIOB SF project page https://sourceforge.net/projects/piob/?source=directory

Enjoy!

Wednesday, November 07, 2012

How to use Cygwin svn and TortoiseSVN both, or fixing " Unable to open an ra_local session to URL" svn error

I have SVN repo located in C:/SVNrepo. And I have a working copy created with TortoiseSVN.
Sometimes I need to access my machine remotely with just command line. I use Cygwin SSH for this.
Sometimes in Cygwin bash session I need to use svn command, for example to update or commit my project working copy.
Well. It did not work. :(
The cause is that Subversion stores path to repository in working copy directory .svn. Inside this svn repo path is set by a tool that created the working copy. If the working copy was created by TortoiseSVN, the repo path will be DOS/Windows path - C:/SVNrepo. Or whatever path you have, important is that it is in DOS/Windows convention, it starts with a drive letter followed by colon.
Cygwin in general can honor such pathes. However its svn utility cannot.

For example it will fail if you issue:

$ svn ls file:///C:/SVNrepo
svn: E180001: Unable to connect to a repository at URL 'file:///C:/SVNrepo'
svn: E180001: Unable to open an ra_local session to URL
svn: E180001: Unable to open repository 'file:///C:/SVNrepo'


Cygwin's svn honors only Cygwin's path convention - /cygdrive/c/SVNrepo.

$ svn ls file:///cygdrive/c/SVNrepo
 project1/
 project2/

As you can see, the repo URL has three slashes in prefix - file:///
First two belong to the prefix itself - file://. The last slash means filesystem root.
It means that when ra_local library parses the URL 'file:///C:/SVNrepo' it will take out "file://" part, and try to find '/C:/SVNrepo' directory. And, of course, it fails! - there is no such path in my system.
Despite there is C:/SVNrepo, there is no /C:/SVNrepo.

What to do?!

To create a link!

ln -s /cygdrive/c /C:

check it is created

ls -l /
...
lrwxrwxrwx   1 mark           None     11 Nov  7 14:25 C: -> /cygdrive/c
...


ensure svn likes it

$ svn ls file:///C:/SVNrepo
 project1/
 project2/

It does!
It works now!