Database

computer stuffs…

19/10/09

Posté par Prune dans Dev

Aucun commentaire

The main purpose of this blog is to act as a reminder, a todo. Here are things i’ve just found and needs a second look at, or more, a testing.

  • Human readable Glassfish Log

    • This is a shell (sh) script that should made things easier when having a look or doing a « tail -f » at Glassfish error logs.

  • Rajeshwar’s Weblog

    • This is a blog, lately talking about Glassfish V3 REST api implementation. As far as I know this is (still) not working with GF v2, which already have JMX and, if you’re a registered Sun user, SNMP. But worth having a look.

  • Beet

    • It seems to me that most of developpers are using Spring framework nowadays. This tool should enable them (or force them ?) to have a deeper look of what the framework is actualy doing. Seriously, comments welcome. May be the weapon of choice for sysadmins like me, dealing with developpers who hide behing their framework (like if the framework was an excuse… huhuhu).

  • PandoraFMS

    • Another monitoring software… well… to be tested.

  • Oracle Database-backed iGoogle Gadgets

    • An interesting thing you can do with Google’s API… It should with any database, but hey, a free software working with Oracle is worth beeing mentioned :)

  • Other Oracle related articles
  • Oracle TimesTen In-Memory Database

    • This is a PDF file dealing with TimesTen Database. This is a product newly (2 or 3 years) bought by Oracle, and well integrated. Embed the database close to the application, in the application server. Data are replicated at startup. You can choose which database, which table, which data using a filter, or even do transformations to the data. You select the synchronization frequency and there you go. I had a session OOW 2009 about that and how it was used to break a CDN application in two parts : one for slow data change (read/write to the DB) and one for massive fast queries on the delivery side. Ok, this use a lot of RAM… but RAM is cheap isn’t it ?

    Now we have some cool things to read and test, don’t we ? :)

    Oracle 11g R2 on Linux Fedora

    18/09/09

    Posté par Prune dans Database

    Aucun commentaire

    otn_logo_small
    This is my first attempt at Oracle 11g with Linux. As I’m still waiting for the 11G R2 to be released on Solaris, I managed to have a try on a Linux VM.

    First, get VirtualBox
    Then, get a Fedora 11 image
    You may also need a « z7″ compressor to un-z7 the image. You can get 7za from the Macports

    # port install p7zip
    # 7za e fedora-11-x86.7z

    7-Zip (A) 9.04 beta Copyright (c) 1999-2009 Igor Pavlov 2009-05-30
    p7zip Version 9.04 (locale=utf8,Utf16=on,HugeFiles=on,2 CPUs)

    Processing archive: fedora-11-x86.7z

    Extracting Machine/fedora-11-x86/fedora-11-x86.xml
    Extracting VDI/fedora-11-x86.vdi
    Extracting VDI
    Extracting Machine/fedora-11-x86
    Extracting Machine

    Everything is Ok

    Folders: 3
    Files: 2
    Size: 4740698220
    Compressed: 1148257214

    Then configure and start the VM. I had to add 3 NICs, so I have 4 network interfaces, enough to play. I also set the first one as Bridge instead of the default NAT, so my VM have a real IP.
    I then have to log as root, chance the /etc/sudoers so Wheel users can sudo. Then I added fedora (default user) to Wheel group in /etc/groups.
    Now I can sudo. We are close to be able to install Oracle database. While I’m at it, go to Oracle website and download the 2 install zip files. This is quite huge, around 2.1Gb. Be carefull when you unzip (not yet), as everything lives in the « database » folder…
    You will also need the Grid Infrastructure Software.
    Please note we are installong the 32bits versions, but the 64bits version is the same, only the packages to download are different. Click on the « view all » to get the Grid Infrastructure Software.

    For Oracle 11G R2 to work on linux you need to fulfill some dependencies, starting with some RPM packages. Use ‘yum’ to search for them and install them. Here is a list according to Oracle Linux recommendations :

    binutils-2.17.50.0.6
    compat-libstdc++-33-3.2.3
    compat-libstdc++-33-3.2.3 (32 bit)
    elfutils-libelf-0.125
    elfutils-libelf-devel-0.125
    gcc-4.1.2
    gcc-c++-4.1.2
    glibc-2.5-24
    glibc-2.5-24 (32 bit)
    glibc-common-2.5
    glibc-devel-2.5
    glibc-devel-2.5 (32 bit)
    glibc-headers-2.5
    ksh-20060214
    libaio-0.3.106
    libaio-0.3.106 (32 bit)
    libaio-devel-0.3.106
    libaio-devel-0.3.106 (32 bit)
    libgcc-4.1.2
    libgcc-4.1.2 (32 bit)
    libstdc++-4.1.2
    libstdc++-4.1.2 (32 bit)
    libstdc++-devel 4.1.2
    make-3.81
    sysstat-7.0.2
    unixODBC-2.2.11
    unixODBC-2.2.11 (32 bit)
    unixODBC-devel-2.2.11
    unixODBC-devel-2.2.11 (32 bit)

    11G R2 now comes with a « bundeled NTP server », I mean, Oracle now can sync the time of every node in the cluster. No need of NTPD, and no evictions due to bad Solaris xntpd server. Just disable ntpd or ensure it’s not running before installing Oracle database.
    Also, configure SSHD and kernel parameters, if needed, as Oracle prerequisite.
    Now, let’s go with Oracle. Create an oracle user with :

    adduser oracle
    passwd oracle (give a password)

    As root, create a /opt/oracle folder and give RWX rights to oracle user.
    Create a SSL key for user Oracle, add your personal public key to authorized_keys and log as oracle user. Copy the Oracle install files to the home dir of this user.

    mkdir /opt/oracle
    chown oracle /opt/oracle
    su – oracle
    ssh-keygen -f dsa
    (set empty password and write the key in .ssh folder)
    vi .ssh/authorized_keys
    (copy the pubkey of your admin user)
    unzip linux.x64_11gR2_grid.zip
    unzip linux_11gR2_database_1of2.zip
    unzip linux_11gR2_database_2of2.zip
    cd database

    Log-in again with your oracle user, setting X11 forwarding (use -X -Y if you are using a mac) :

    ssh -X -Y oracle@your_host
    cd grid

    cd database
    ./runInstaller

    Starting Oracle Universal Installer…

    Checking Temp space: must be greater than 80 MB. Actual 9828 MB Passed
    Checking swap space: must be greater than 150 MB. Actual 1023 MB Passed
    Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
    Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-09-18_09-47-50AM. Please wait …[oracle@localhost database]$ Xlib: extension « Generic Event Extension » missing on display « localhost:10.0″.

    Don’t take account for X11 errors, as long as you have the install window.
    First question is giving out your email address for security updates… As you ARE a good DBA/Sysadmin, you won’t need this. Click next :)

    As I don’t have time and I KNOW I will not do better, check there for some more informations on installing Oracle 11G R2 RAC ASM.

    Mysql Reminder : csv export

    28/01/09

    Posté par Prune dans Database

    Aucun commentaire

    logo_mysql_sun_a

    I was asked to export some data in a csv format from a Mysql database. I’m just putting it here as a reminder… Thank you so much my blog to extend my brain capacity so much !

    SELECT * INTO OUTFILE '/tmp/result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM dc_account_key WHERE created_at > '2009-01-09' AND active = 0;

    I won’t explain this, it’s really straitforward. This is just a reminder, after all.

    Oracle 11g ASM install on Solaris 10 with RAW data files

    25/11/08

    Posté par Prune dans Oracle

    8 commentaires

    I was installing a Oracle 11g + ASM server for testing and upgrade purpose when I came across a problem : how do I create RAW « filsystem » for ASM ?

    I found a lot of docs for doing this with Linux, but nothing about Solaris.

    Of course you will quickly find something about ‘lofs’ and ‘lofsadm’. This is the right starting point. But then ?
    It seems ASM wants to have « real » block device for use with ASM. If I’m wrong, please, comment and correct me.

    My solution was to :
    Lire la suite de l’article »

    Oracle FCF, TAF and FAN explained

    3/11/08

    Posté par Prune dans Database

    Aucun commentaire

    Thanks to the Database Journal and Tarry Singh, you have now really good introduction articles on Oracle, TAF, FAN, and many more.

    Just check articles 15 and 16, and spend some time on the others.

    Thank you Tarry.

    Oracle Open World 2008

    21/09/08

    Posté par Prune dans General

    2 commentaires

    Cette année j’ai la chance d’aller a Oracle Open World.

    Pour ceux qui ne savent pas, c’est 5 jours avec plein d’informaticiens qui parlent de bases de données. Enfin, surtout d’une, Oracle.

    Comme moi je suis la pour travailler, je vais laisser la place a mon compère, TrucTruc.
    Lire la suite de l’article »

    Sybase database migration / reduction

    10/04/08

    Posté par Prune dans Database

    2 commentaires

    One of the main problem of sybase (and other database) is the way it handle disk space. You must create « disk devices », which can be RAW or file on a filesystem. Then give some space of it to a database, and perhaps create or extend segments.

    Of course, this may sound a good way to deal with it. Then comes the time when you need some storage for something else, or another database.

    You start realizing that the 15 Gb you added to your database, thinking your data will grow as you reach your 1.000.000.000 subscriber… but your stalled at 150 :)

    Lire la suite de l’article »

    Should we pay for Oracle support ?

    26/03/08

    Posté par Prune dans Database

    Aucun commentaire

    metalink Oracle

    So you pay A LOT for your premium Oracle support ?

    I won’t spend my time complaining on the Metalink web interface, the one you must use to open a F**King SR (Service Request), where you lost half a day each time.
    I won’t complain on the F**King result you get… something nobody can read through and get the information without losing the second half day :)

    No, my problems are somwhere else, and far more important, to me…
    Lire la suite de l’article »

    Building MacPorts Py-Mysql on Leopard

    15/02/08

    Posté par Prune dans Django

    Aucun commentaire

    If you installed over your old Tiger or if you are new user, you may have experienced errors when trying to build py-mysql.Py-mysql is a Python module to connect to Mysql.I’m using it to have Django, the Python Web Framework, to connect to Mysql database. This is what I got when trying to install :   

    # port install py-mysql
--->  Building py-mysql with target build
Error: Target org.macports.build returned: shell command " cd "/opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_python_py-mysql/work/MySQL-python-1.2.2" && /opt/local/bin/python2.4 setup.py build " returned error 1
Command output: running build
running build_py
copying MySQLdb/release.py -> build/lib.macosx-10.3-ppc-2.4/MySQLdb
running build_ext
building '_mysql' extension
/usr/bin/gcc-4.0 -fno-strict-aliasing -Wno-long-double -no-cpp-precomp -mno-fused-madd -fno-common -fno-common -dynamic -DNDEBUG -g -O3 -Wall -Wstrict-prototypes -Dversion_info=(1,2,2,'final',0) -D__version__=1.2.2 -I/opt/local/include/mysql5/mysql -I/opt/local/Library/Frameworks/Python.framework/Versions/2.4/include/python2.4 -c _mysql.c -o build/temp.macosx-10.3-ppc-2.4/_mysql.o
In file included from /opt/local/include/mysql5/mysql/mysql.h:47,
                 from _mysql.c:40:
/usr/include/sys/types.h:92: error: duplicate 'unsigned'
/usr/include/sys/types.h:92: error: two or more data types in declaration specifiers
error: command '/usr/bin/gcc-4.0' failed with exit status 1

Error: Status 1 encountered during processing.

    I tried to upgrade MacPorts… but I already had the latest.

    # port selfupdate

MacPorts base version 1.600 installed

Downloaded MacPorts base version 1.600

The MacPorts installation is not outdated and so was not updated
selfupdate done!

    I finaly found the solution on http://rob.cogit8.org/blog/2007/Nov/14/installing-django-leopard-mac-os-105/

    Get the Page size in Sybase

    26/09/07

    Posté par Prune dans Sybase

    Aucun commentaire

    Strange, I wasn’t able to find a way to get the database page size in a Sybase (12.x) server.

    Digging in sp_helpdb stored procedure gave me some more informations. To do this, or any stored proc, use this SQL command :

    use sybsystemprocs
go
sp_helptext  sp_space_segment
go

    You will get the stored proc as text.

    Finaly, the solution to get the page size is :

    select v.low/1024  from master.dbo.spt_values v  where v.number = 1 and v.type = "E"

    or the page size in octets:

    select v.low/1024  from master.dbo.spt_values v  where v.number = 1 and v.type = "E"

    or how many pages there are in a mega-octet (Mo) :

    select (1048576 / v.low)
from master.dbo.spt_values v
where v.number = 1
and v.type = "E"