Linux from Scratch + Oracle = LinOra

For several years now I've wanted to roll my own linux distro based on the work of Gerard Beekmans and Matthew Burgess of the Linux from Scratch project. Of course, life and work commitments have been getting in the way (not to mention lack of available hardware). However, I now have the time and the hardware available to start the project. While thinking about what I want to do, I decided I want to make a custom linux distro specifically designed for Oracle.

Some may ask why do that when there are already distros readily available that run Oracle without a problem. My answer is simply because I can. I can set all the required (default) kernel parameters up front. I can strip the kernel of any dead weight and make a more efficient system (read as "project goal"). I can use a lighter weight window manager than the two most popular (KDE & GNOME). I can shrink the install footprint significantly by only including the necessary libraries & utilities.

...and so the adventure begins

times they are a changin'

So my current project's contract is ending at the beginning of February and I will no longer be working on-site at the client's location. I will be working from home 24x7. This in itself is very cool (however, it does tend to bring out the inner workaholic). The best part of this change is that I am now in control of where my career goes. Up until now, it has been a constant battle of mapping out where I want to be in the future, the direction I want my career to go, and what my current boss wants. Typically, about every three years or so my desires have diverged significantly from where "the man" wants me to go so I find a new gig and keep going.

For example, at my last gig I was hired on as "the linux expert" because the company was wanting to move their database servers to Oracle / linux (long before Oracle supported linux as their primary dev platform). So I thought, "Sweet! this is exactly what I'm looking for!" Initially it was great. I got to eat, sleep, breath linux/oracle. I got to figure out how to install Oracle on linux 7.0 and make decisions as to which linux distro was best suited for our environment, etc. Well, short version is I eventually became the Citrix admin and spent less and less time doing the work I thought I was hired to do. So three years later I found a new gig. Same ol' same ol'. I figured this is the plight of the techie.

I signed on with my current employer, packed up the family, and moved to Austin. I figured if nothing else, at least we made it to Austin way ahead of schedule and I was working for a company that would pull a lot of weight on the resume if it came to that. I came at the end of the project cycle so it started out very slow. Plenty of time to study outside of work and do stuff I wanted to do. The project was complete and we had nearly 4 months of idle time until the next rev of the project started.

Four months is a long time to sit on the sideline with nothing to do. You begin to wonder about job security, your skills begin to atrophy, and you get really bored. My boss kept telling me that the contract with the client will be up soon and not to worry about my job, there's plenty of work once we're finished with this project. OK, but how much longer until we're out? So the time comes for the suits to renegotiate the contract and we're in for one more rev.

So now the project is coming to a close and my co-worker and I have our team meeting with our manager. Our manager tells us this is it, we've made it through and we're finished with the contract and proceeds to ask us what we want to do. I look at my co-worker, he looks at me and we both are dumb founded. We are being asked what we want to do, not being told what we are going to do. Not only that, but we now work from home, our time is our own, and it is up to us to determine our schedule. HOLY CRAP! That's the coolest thing ever!

I am both excited and scared that I am finally in complete control of my career time. Now granted I am still bound by what my company does, but they do everything that I am interested in! A techie's wet dream come true!!!!

My Linux Laptop

About four months ago I got fed up with my Windows XP Pro laptop image. It kept throwing BSODs much more frequently than I care to admit, IE quit being able to access HTTPS sites, and quite a few more annoyances that made me less than productive for days at a time. I had been thinking about (and wanting to) installing my company's Linux Desktop image. Now I consider myself to be left leaning in the OS realm and had been working with linux in a server enviroment for several years and had been quite proud that the only non-linux system I have is my wife's XP Home pc and my laptop (since every place I had worked at until now only supported Windows images).

When I went to work for my new company a little over a year ago, I found out that they offer a choice in laptop/desktop images, Windows or Red Hat linux. My laptop came imaged with Windows XP Pro and since I needed to get straight to work on my project I kept it. Then (as they always do) my laptop started its downhill slide. So while I was in between projects and my laptop would BSOD at least once a day, I decided to pull the trigger and re-image my laptop with the linux image. At first I was a little sketchy about running linux ("will I be able to access what I need to access on the client's site?" ,"...but what about app, there is no linux equivalent?").

After I got it installed, it was like I was reunited with an old friend. I had been weaning myself from closed-source/Windows-only apps for quite sometime. All my laptops and/or pc's always had Cygwin installed, I had been using Open Office as my only business apps for many years, and my main text editor/IDE is (G)Vim. So I was much more comfortable than I had imagined I would be. The only stumbling block was that I had only been using GNOME for my window manager and our linux image was built on KDE. No big deal after all.

The thing that annoyed me the most about KDE in the beginning was the differences in how it handles the XTerm setup. I had really become accustomed to XTerm in GNOME's keyboard shortcuts being similar to Firefox's.

technorati tags: linux, kde, lobi, red hat

Non-RAC ASM v LVM Benchmarking

In a recent Linux Journal article titled Optimizing Oracle 10g on Linux: Non-RAC ASM vs. LVM by Brent Scalzo. Overall it was an interesting read, but not a true "apples-to-apples" comparison. Which (in his defense) he acknowledges. His conclusion is that LVM is faster than ASM on non-RAC databases.

For a follow up and counter argument to Brent's article, see Wim Coekaert's blog. Both Brent's and Wim's articles are very interesting.

LGWR SYNC vs ASYNC

An interesting set of questions came across a distribution list I am subscribed to recently. Mostly the person wanted to know the differences between SYNC & ANSYC attributes of the LGWR attribute. His set of questions were specific to Data Guard and the (not so) subtle differences between the various database modes [Max Protection, Max Performance, and Max Availability].

1. What is the difference between LGWR SYNC and ASYNC in Oracle DataGuard configuraton?
2. What happens if the network between the primary and standby [database] is lost with LGWR SYNC and ASYNC?
3. What happens if the standby database is shutdown with LGWR SYNC and ASYNC?
4. If LGWR SYNC and ASYNC is deployed, what processes bring the standby [database] back into sync with the primary [database] if the network is lost and is then restored? How does it do it?

 My biggest question is, when the network to the standby
 is lost with SYNC or ASYNC, where is the information queued
 and how is it retransmitted once the network has been
 re-istablished?

So I gave it a shot and here are my answers...

1.  What is the difference between LGWR SYNC and ASYNC in Oracle DataGuard configuraton?

LGWR is an attribute of the LOG_ARCHIVE_DEST_n parameter which is used to specify the network transmission mode. Specifying the SYNC attribute (which is the default), tells the LGWR process to synchronously archive to the local online redo log files at the same time it transmits redo data to archival destinations. Specifically, the SYNC atrribute performs all network I/O synchornously in conjunction with each write operation to the online redo log file. Transactions are not committed on the primary database until the redo data necessary to recover the transactions is received by the destination.

The ASYNC attribute perfoms all network I/O asynchronously and control is returned to the executing application or user immediately. When this attribute is specified, the LGWR process archives to the local online redo log file and submits the network I/O request to the network server (LNSn process for that destination, and the LGWR process continues processing the next request without waiting for the network I/O to complete.

2.  What happens if the network between the Primary and Standby [database] is lost with LGWR SYNC and ASYNC?

This is dependent upon the database mode you have set. If you have set Maximum Protection, you have chosen a configuration that guarantees that no data loss will occur. You have set this up by specifying the LWGR, SYNC, and AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least one standby database. This mode provides the highest level of data protection possible and to achieve this the redo data needed to reocver each transaction must be written to both the local online redo log and the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault (such as the network going down) prevents it from writing its redo stream to at least one remote standby redo log.

If you have set the Maximum Availability mode, you have chosen a configuration that provides the highest level of data protection that is possible without compromising the availablity of the primary database. Like the maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to at least one remote standby redo log. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availabitly mode. This guarantees that no data loss will occur if the primary database fails, but only if a second fault does not complete set of redo data being sent from the primary database to at least one standby database.

If you have set the Maximum Performance mode (the default), you have chosen a mode that provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover the transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, bu that the redo stream is written asynchronously with respect to the commitment of the transactions that create the redo data.

The maximum performance mode enables you to either set the LGWR and AYSNC attributes, or set the ARCH attribute on the LOG_ARCHIVE_DEST_n parameter for the standby database destination. If the primary database fails, you can reduce the amount of data that is not received on the standby destination by setting the LGWR and ASYNC attributes.

3.  What happens if the standby database is shutdown with LGWR SYNC and ASYNC?

This goes back to what mode you have chosen. See the answer to question 2 for the details

4.  If LGWR SYNC or ASYNC is deployed, what process(es) bring(s) the standby database back into sync with the primary [database] if the network is lost and is then restored? How does it do it?

Again, this is dependent upon the mode you have chosen for you database. The LGWR process (and possibly the LNSn process if you have multiple standby databases) is responsible for closing the gap.

My biggest question is, when the network to the standby is lost with SYNC or ASYNC, where is the information queued and how is it retransmitted once the network has been re-established?

This implies that your database has been set to either maximum availability or maximum performance mode. You cannot use the ASYNC attribute with maximum protection mode. The information is queued in the local online redo log and the LGWR (and the LNSn) process will transmit the data to the standby database's online redo log file to close the gap once the network connectivity has been re-established

Gap recovery is handled through the polling mechanism. For physical and logical standby databases, Oracle Change Data Capture, and Oracle Streams, Data Guard performs gap detection and resolution by automatically retrieving missing archived redo log files from the primary database. No extra configuration settings are required to poll the standby database(s) to detect any gaps or to resolve the gaps.

The important consideration here is that automatic gap recovery is contigent upon the availablity of the primary database. If the primary database is not available and you have a configuration with mulitple physical standby databases, you can set up additional initialization parameters so that the Redo Apply can resolve archive gaps from another standby database.

It is possible to manually determine if a gap exists and to resolve those archive gaps. To manually determine if a gap exists, query the V$ARCHIVE_GAP view on your physical standby database. If a gap is found, you will then need to locate the archived log files on your primary database, copy them to your standby database, and register them.

Fun Metalink Search

If you're bored (or just curious) and have a Metalink account, try searching for LGWR...you'll be surprised and impressed with what comes back. I know I was. There's a ton of information out there and some of it you might just find useful.

Installing OCFS on RHEL4

So I realized that I wanted to use OCFS as my filesystem of choice for this install, but when I went to install the rpms available from here I kept getting script errors. The rpms were designed for RHEL2.1 and/or RHEL3 so they would not install completely. I could 'rpm -qa |grep 'ocfs' ' and all the packages I installed would be there, but I could not get 'ocfstool' to start. So I went hunting for a file to hack. I found '/proc/sys/kernel/ostype & /proc/sys/kernel/osrelease', but these are not writeable files (even by root). So I tried using 'sysctl', but I still could not update these parameters. I'm still trying to find a way to get around this.

Until the OCFS rpm is updated for RHEL4, installing it is an exercise in futility. You will need to install OCFS and OCFS Tools from source. This is also not an easy process. So I have now spent most of my morning hacking all kinds of files trying to get OCFSv1 or v2 installed on RHEL4 and it just will not co-operate. I'm throwing in the towel on this one and waiting for a code refresh of OCFS to work with RHEL4.

A: Recently executed sql statement and user

well, i did a quick google search because my brain just would not pull the answer out of the recesses...here's the first hit my search came back with: this came from Ask Tom
this is the link to the forum discussion "How can I track the execution of PL/SQL and SQL?", version 8.1.6

---------------- showsql.sql --------------------------
column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, process, status
from v$session
where username is not null
/

column username format a20
column sql_text format a55 word_wrapped

set serveroutput on size 1000000
declare
    x number;
begin
    for x in
    ( select username||'('||sid||','||serial#||
                ') ospid = ' ||  process ||
                ' program = ' || program username,
             to_char(LOGON_TIME,' Day HH24:MI') logon_time,
             to_char(sysdate,' Day HH24:MI') current_time,
             sql_address, LAST_CALL_ET
        from v$session
       where status = 'ACTIVE'
         and rawtohex(sql_address) <> '00'
         and username is not null order by last_call_et )
    loop
        for y in ( select max(decode(piece,0,sql_text,null)) ||
                          max(decode(piece,1,sql_text,null)) ||
                          max(decode(piece,2,sql_text,null)) ||
                          max(decode(piece,3,sql_text,null))
                               sql_text
                     from v$sqltext_with_newlines
                    where address = x.sql_address
                      and piece < 4)
        loop
            if ( y.sql_text not like '%listener.get_cmd%' and
                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
            then
                dbms_output.put_line( '--------------------' );
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ' ' ||
                                      x.current_time||
                                      ' last et = ' ||
                                      x.LAST_CALL_ET);
                dbms_output.put_line(
                          substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;
    end loop;
end;
/

column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username,
       module,
       action,
       client_info
from v$session
where module||action||client_info is not null;

there were plenty of comments and follow-ups on this discussion...i recommend reading the entire thread...you will find tons of valuable information