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

Q: Recently executed SQL statement and user

So I'm reading through my emails and I come across this question:

how do i determine the last executed sql statement and user

It's late and I know the answer to this...but I just can't seem to recall it. I'll post the answer a.s.a.p.

Oracle Statspack

I am always looking for more information about Oracle's statspack and have come across some very good links (some I have read, some I have not)....

Advanced Tuning with Statspack	This article lists and describes the top 10 most common wait events and some possible resolutions. One thing to keep in mind when reading this article is that these are the "most common wait events" and you should look at look at the exhibited behaviors of the system as well as these events.
How To Integrate Statspack with EM 10G	This is a good Metalink article on how to integrate your statspack data into the EM in 10g. If you don't have a Metalink account, you need one.
Gathering a StatsPack snapshot	This note is intended to provide information about the steps required to take a StatsPack snapshot of current database performance levels and how to capture different granularities of performance information at both the instance and session levels. This note also discusses how to automate the process of capturing a StatsPack snapshot. These snapshots can be used as a starting or ending point for generating a performance summary.
FAQ- Statspack Complete Reference	This FAQ is mostly for Oracle releases prior to 10g. It contains lots useful links to more Metalink related articles.

Oracle 9i RAC Training Class

The class...

Last Monday 24-JAN-2005 through Wednesday 26-JAN-2005 I attende an Oracle 9i RAC class in Dallas presented by Rick Pulliam. Rick is an outstanding instructor! I highly recommend taking a class from him if you're planning on taking an Oracle training class. He's very approachable and knowledgable.

This was my first class since starting at Oracle last September. My main areas of interest in Oracle is the performance tuning, high availablity, and architecture. Rick covered all areas and then some. Some of the links under my Oracle bookmark folder came from this class. I have just started posting my favorite Oracle links here so check back and see what I've found.

Rick recommended Optimizing Oracle Performance by Cary Milsap. Which I promptly went out and purchased it. It was the best $35 bucks I've spent in a long time. I've only just started reading it and find it easy to follow. Cary states in the preface that he always wanted to know why a fix worked and would lose sleep trying to figure it out. I am not as obsessive compulsive as Cary, but I am definitely on the "why did it work" side of the line.