OEL 5.6: Enable Telnet and FTP

Since I "broke" my last installation and couldn't easily get the latest and greatest Oracle Enterprise Linux running correctly (1, forgot the Desktop components, 2, I can't remember), I went back to OEL 5.6 (Carthage).

I have about 100 files that I need to move over to that server and with my issues with VirtualBox Shared folders in the past, I figured I would just FTP them. Well, FTP isn't on by default...and since this is a sandbox, I don't much care about "real" authorization.

FTP
For FTP, go into /etc/xinited.d/ and open up gssftp

[root@medicaid xinetd.d]# vi gssftp 

# default: off
# description: The kerberized FTP server accepts FTP connections \
#              that can be authenticated with Kerberos 5.
service ftp
{
        flags           = REUSE
        socket_type     = stream
        wait            = no
        user            = root
        server          = /usr/kerberos/sbin/ftpd
        server_args     = -l -a
        log_on_failure  += USERID
        disable         = yes
}

By default, it is disabled.

To enable it, change disable = yes to disable = no. That's not it though. If you try to ftp in, you'll see this:

oraclenerd@oraclenerd:/usr/bin$ ftp medicaid
Connected to medicaid.
220 medicaid FTP server (Version 5.60) ready.
Name (medicaid:oraclenerd): oracle
530 Must perform authentication before identifying USER.
Login failed.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> quit
221 Goodbye.

With the help of this discussion, I removed the server arguments. From what I can tell, those are there for Kerberos authentication, which I don't need or care about. Your final file should look like this:

service ftp
{
        flags           = REUSE
        socket_type     = stream
        wait            = no
        user            = root
        server          = /usr/kerberos/sbin/ftpd
        server_args     =
        log_on_failure  += USERID
        disable         = no
}

Then you can do this:

oraclenerd@oraclenerd:/usr/bin$ ftp medicaid
Connected to medicaid.
220 medicaid FTP server (Version 5.60) ready.
Name (medicaid:oraclenerd): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> quit
221 Goodbye.

Telnet
The process is nearly identical for telnet, here's the config file:

service ftp
{
        flags           = REUSE
        socket_type     = stream
        wait            = no
        user            = root
        server          = /usr/kerberos/sbin/ftpd
        server_args     =
        log_on_failure  += USERID
        disable         = yes
}

For telnet, just change disabled=yes to disabled=no.

oraclenerd@oraclenerd:/usr/bin$ telnet medicaid
Trying 192.168.1.6...
Connected to medicaid.
Escape character is '^]'.

    medicaid (Linux release 2.6.18-194.el5 #1 SMP Mon Mar 29 22:10:29 EDT 2010) (2)

login: oracle
Password: 
Last login: Mon Feb 28 14:38:06 on :0
[oracle@medicaid ~]$

Next time, I won't have to remember, or I'll have blown away so many instances that I'll just remember, either way, this is my record.

Update
So, twitter gave me the following after I posted the link to this post:



My answer, How in the f would I know something like this? I have no fancy training. :)

So, now I'm using ssh and sftp. Much easier, and it just seems to work. No configuration necessary. Yeah for Mr. Norris.

ORACLENERD Naming Convention

I don't think I've ever mentioned this before, but it's probably a good idea. I've hassled friends before about it (Jake, Jeff and others).

The name of the site is oraclenerd. Ideally, small caps. Barring that, all caps or lowercase. Never, ever, ever, camel case. Yuk. I'm not a java guy. :)

Not 2 words either, mostly for the copyright infringement possibilities. I first encountered this a few years ago when trying to create t-shirts with Cafe Press, they rejected Oracle Nerd, probably because of the Oracle part. That's when I made it one word, oraclenerd or just oraclenerd.

Amusingly, I'm currently fighting with Facebook about their allowed name possibilities, no caps and no all lower-case. I already gave in to Katezilla, which annoys me. I wanted it to be all lower-case. For oraclenerd though, I'm attempting to fight the man. We'll see.

Definitely not trying to be a jerk...just never thought I would have to say anything because I never figured I would be referenced any where. I have 3 references now, so I have to control my brand. :p

katezilla and the EBS eBook

Did I mention that John and I published his awesome 52 part series on doing an EBS Vision install?

Did I mention that all proceeds go to katezilla?

I don't have to link to just the stuff I post here, she has her own Facebook page now (she's classified as a comedian, appropriately), with a vanity url: facebook.com/katezilla.rockstar

That page comes complete with the oh so popular Like Box

Go ahead, Like katezilla. It's easy.

Did I mention that all proceeds go to kate?

I probably did. Technically, they don't go to her directly. They help us to pay for the care she needs. Currently, that consists of Behavioral Therapy (aka PT) which is about 4-5 hours a week. We'd like to do more of course as we believe her therapist has done wonders. We're trying to talk her into spending more time, closer to 10 hours per week. Have I mentioned that the therapists rates would make most of us blush, and we're in IT. Of course she is worth a heck of a lot more, I'm not complaining...I realize the importance of her work in comparison with mine. Her work will last a lifetime, if I'm lucky, mine will last a decade. Even if it did last a decade, it still couldn't even come close to matching the importance of what she does.

Then there's Kate's caretaker/nurselady (aka CT). What can I say, she's just awesome. We have the same situation with her...we would love to have her here more often, but it's near impossible. Perhaps when the house is paid for or the cars (close, very close). Or perhaps we win the lottery. I guess I have to play for that to happen though.

We've had tons of help from my parents, and to a lesser extent other friends and family, financially over the past 6 years. Nothing makes me tear up faster than someone offering to help like that. That people would offer up help, just because, is an amazing act of generosity and kindness. Someone recently complained that we didn't allow customers to order multiple copies of the eBook. I didn't get it at first...why would you want multiple copies of a PDF? Just make a copy.

Oh wait...you want to donate...OK, now I get it. I'm slow sometimes.

We've sold 12 books so far, which I consider a success. John is now a professional author and I am now a professional editor (well...). We just need to sell about 200,000 books to cover Kate's total bills over 6 years. I think we can do it. Lots of people need the EBS Vision install to make themselves a better worker, right?

Buttons would be helpful. :)

Ubuntu Tip #1 - Cleaning the Filesystem

More of a reminder to myself than anything...

It all started when I couldn't run JDeveloper; the launcher didn't work, clicking on the icon in the /bin directory didn't work...WTF? I opened up a terminal and tried to open it there when I received a "hey, you've got no more room on your main drive (partition, whatever)"

OK.

I opened up DiskAnalyzer, started to just delete things I knew were gone (yeah, recipe for disaster, especially me). I used Synaptic to remove old or obsolete packages (Complete Removal). I removed a gigantic 3.3 GB log file from my /home directory. Yet System Monitor told me I had even less space now (no, you can't have less than zero, but I had gotten it up to a few hundred MBs of space).

Emptied the trash.

What about the root trash?

oraclenerd@oraclenerd:~$ su root
Password: 
root@oraclenerd:/home/oraclenerd# 

root@oraclenerd:/home/oraclenerd# cd ~
root@oraclenerd:~# cd .local/share/Trash/files/
root@oraclenerd:~/.local/share/Trash/files# rm *
root@oraclenerd:~/.local/share/Trash/files# ls
root@oraclenerd:~/.local/share/Trash/files#

Apparently that thing was quite full, I went from 100% "Used" to 45% used just like that. Yeah for me.

Funny Story #1

If you've ever met me, I love to tell stories. In fact, I think the whole point of our existence is to tell stories. Many of them cannot be shared in a public forum, most are best told over beers.

As I was getting my son settled for bed this evening, a Bio on Animal House was on. Perhaps this isn't really my story, but who cares?

I "played" baseball at the University of Florida from 1992 through 1993 (JUCO prior to that). Our announcer/PA dude was a local radio personality, can't remember (or find) his name. He played all the usual baseball songs:

- Centerfield by John Fogerty
- Glory Days by Bruce Springsteen
- Take Me Out To The Ballgame by Jack Norworth

So on and so forth.

He also had a little fun. One of the pieces he did depended on a very specific situation. Going into the bottom of the 9th (which meant we were losing) in a close game (usually a run or 2). He would play the scene from Animal House, the one were Bluto (John Belushi) gives his speech on rally the troops.

Bluto: Hey! What's all this laying around stuff? Why are you all still laying around here for?
Stork: What the hell are we supposed to do, ya moron? We're all expelled. There's nothing to fight for anymore.
D-Day: [to Bluto] Let it go. War's over, man. Wormer dropped the big one.
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? Hell no!
Otter: [to Boon] Germans?
Boon: Forget it, he's rolling.
Bluto: And it ain't over now. 'Cause when the goin' gets tough...
[thinks hard of something to say]
Bluto: The tough get goin'! Who's with me? Let's go!

Here's the video as well...

I'm pretty sure the curse words were removed, but it was funny and definitely got the crowd fired up.

ORA-01578: ORACLE data block corrupted (file # 7, block # 42061)

I've been loading the raw data into my sandbox for my little side project (Florida Medicaid reporting database).

Since much of this is throw away, I am not terribly concerned with repeatable processes, just get the data in there.

I have about 100 files to load, ~250 GBs worth. I've created a spreadsheet with the file names, the associated external table, whether it has been loaded or not and the data loaded. This is just for my own sanity, as I do this when time permits and I don't want to lose track of my progress.

Using the external table name and file name, I've created a formula to build my ALTER statements (ALTER TABLE <table_name> LOCATION ( '<file_name>' );). Each file takes 3 to 5 minutes to load.

The other morning, around 3 AM, I got to the 10th file and received a communication error with the server. Being so late, I didn't bother with researching the issue, I just retired.

The next day, I attempted to look into the issue, first I wanted to see which files were loaded.

SELECT COUNT(*)
FROM orig_claim;

SELECT COUNT(*) FROM orig_claim
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 42061)
ORA-01110: data file 7: '/u01/app/oracle/oradata/TESTING/medicaid_02.dbf'

Uh oh.

As I usually do, I posted that error message to Twitter.

Twitter = Awesomesauce Part 983



Here are some answers to the questions above:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>SELECT 
  instance_name, 
  host_name, 
  version, 
  status, 
  archiver 
FROM v$instance;

INSTANCE_NAME    HOST_NAME  VERSION           STATUS       ARCHIVE
---------------- ---------- ----------------- ------------ -------
TESTING          oracle     11.2.0.1.0        OPEN         STOPPED

SQL> SELECT 
  dbid, 
  name, 
  log_mode 
FROM v$database;

      DBID NAME      LOG_MODE
---------- --------- ------------
  86530622 TESTING   NOARCHIVELOG

Also, since I just started loading data, I had not performed a backup yet.

Other possibly pertinent information:
- Using COMPRESS BASIC
- Just before I had the communication error, I had enabled PARALLEL DML for the session and set it to 8 as a hint in the SQL statement
- INSERT /*+ APPEND */ from an external table into a staging table
- Oracle Enterprise Linux 5.5 (Carthage)
- 6 GB of RAM allocated to Oracle
- 4 CPUs allocated to this VirtualBox virtual machine

SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         4     100939          1                  0 CHECKSUM
         7      42061          1                  0 CHECKSUM

I tried using RMAN (BLOCKRECOVER DATAFILE 7 BLOCK 42061;), but naturally it had nothing to recover from. I had pondered using DBMS_REPAIR, but I was exactly sure of the implications, i.e. would I have to reload specific data files.

---

From Martin Berger:
An answer to his question below, what is the corrupted object?

SELECT 
  tablespace_name, 
  segment_type,
  owner, 
  segment_name 
FROM dba_extents 
WHERE file_id = 7 
  AND 42061 BETWEEN block_id AND block_id + blocks - 1;

It's a table. The ORIG_CLAIM table to be exact. That's the only core table I have begun to load.

Dropping and recreating it won't be terribly difficult, way better than rebuilding the entire VM/DB.

---

After identifying that a specific table had a corrupt block, I dropped it per Martin's guidance.

Since I wanted to keep the original (raw) data along with the, dimensional model, I decided to create a separate tablespace to house the raw data. I did that and began to load the data.

That's when my new joy happened:

ORA-01578: ORACLE data block corrupted (file # 8, block # 26031)

At first, at I was just going to drop and recreate the database. Issued the SHUTDOWN command then started it back up in RESTRICTED mode, STARTUP NOMOUNT EXCLUSIVE RESTRICTED, then dropped the thing. After a bit more thinking, I just decided to rebuild the entire VM.

The good news there, I get to mess around with Oracle Enterprise Linux 6. The bad news, more time.

Design Documentation

I got bit recently. I didn't ask for requirements and I didn't create a design document. Bad Chet.

It made for a difficult time getting that first cut done...and I missed a few very important items.

Talking with a friend today about it, and he kind of hammered the point home. Do the design document and you spend fewer cycles spinning your wheels. As an example he use a project we both worked on last summer. At first, he didn't do a design document and "struggled" for a couple of weeks. When it came time to do it again (it had been dropped from that first release), he started with the design document and it only took a week.

Forced him to think about how to do it, the possible roadblocks and the ability to raise those roadblocks to the appropriate people.

For whatever reason, I had never thought of the design document in terms of OBIEE. I don't know why, it just was. For OLTP type applications, I would always, at a minimum, create a Visio diagram. That would allow me to quickly and easily spot any problems. That world is much more intuitive to me, so I can visualize it easier. OBIEE, not as easy.

So here's my pledge to, no matter how much I loathe formal design documents, to go forth and create formal design documents. After all, this is development, just a different tool (and a layer of abstraction). No more ad-hoc development for me.

EBS Install Guide - The eBook

A mutual friend of John and I suggested we do an eBook. The very first thing I thought of was John's EBS Install Guide (Part I, Part 2, Part 3, and Part 4). If you've read here with any frequency, you'll know that John pretty much took over my site with his guide, occupying many of the top spots...all time. Jerk. This is my 675th post, John has 4. John has 4 of the top 30 spots over the 40+ months I have been blogging in just under 14 months. Jerk. To illustrate:



OK, so 2 of the top 5 spots (not including the root directory). Yikes. Or is it jerk?

Of course John is not a jerk, as much as I would like to paint him as such. He's a great guy and now real-life friend (he didn't kill me after OOW after 4 days). John's guide not only proved useful to me, but has proven useful to many others in the community as well.

Aforementioned friend also suggested that this should be a "for Kate" kind of thing too. You know, to guilt those suckers who may have gotten hold of it without paying into paying.

What's in it?

Well, it's a PDF. It has bookmarks.



It was created using OpenOffice (if it matters). It is 49 pages long and includes a foreword from both John and I.

All 8 posts are contained within this one single document. The 4 hosted here and the 4 hosted on John's site. One stop shopping.

So, if you are about to venture down the (crazy) path of installing EBS for fun (super crazy) or for education (somewhat crazy), this is a great place to start. I know the guide works because even I could install the entire thing, even if it did take me over a month.

Finally, the super cool PayPal buttons:

If there is enough interest, we may port it over to the Kindle, Apple or Lulu. For now, once your order is placed, your "success" page will be the PDF.

OBIEE 11g: Instrumentation

I had this great post planned out only to realize I had been beaten to the punch.

Instrumenting OBIEE for tracing Oracle DB calls

Mr. Moffatt [ LinkedIn | Twitter ] describes a way to go about finding out exactly what was being run by using DBMS_APPLICATION_INFO.

If you are working with OBIEE, any version, I would encourage you to go read his article.

What's amusing to me, is that we both tried (well, I tried, he did) to do the same after reading Cary Millsap's excellent paper, Mastering Performance with Extended SQL Trace.

I heckled Mr. Moffatt a little bit privately for stealing my thunder, but after reflection, I was thankful...I didn't have to do all that work.

OBIEE 11g: Admin Tool Now Available...

...as a separate download. Timing couldn't be worse, personally. But I'm glad it's finally out.

Twitter = Discovery - Part 531



Found in Mr. Rittman's stream.

I've complained a lot about the 11g version of OBIEE, perhaps not here a lot, but trust me, I have complained. I don't mind the integration with WebLogic, I understand that move. It's just so darn...well, it's just painful.

This move rectifies part of it, the install process. Before this, you had to install the entire stack and then disable the services...just to get the Admin tool.

Find the download page here. README is here.

SQL Developer + MR Trace

After last week's presentation on Advanced Oracle Troubleshooting, my interest in knowing and understanding performance related matters has been rekindled. Well, I've always been interested, I just don't know what I'm doing.

In that regard, I'm trying out some tools. Today it is MR Trace, or Mister Trace as the folks at Method-R like to call it.

They have a nice little SQL Developer plugin so I don't have to think a whole lot, which is good. I need to save those cycles for more important things.

What is MR Trace?

...is our company’s extension for the Oracle SQL Developer interactive development environment. It is a zero-click Oracle trace file collector. Every time you execute SQL or PL/SQL from your SQL Developer worksheet, MR Trace (we call it “Mister Trace”) automatically copies a carefully scoped extended SQL trace file to your SQL Developer workstation, where you can tag it for easy lookup later. The main panel lets you manipulate all the trace files you’ve collected. You can click to open a trace file with any application you choose, like the Method R Profiler or your favorite text editor.

Now, I would encourage you to watch this short video featuring Mr. Millsap.

Installation
Installation is a snap. I'll run through it real quick here.

First, check for updates.



Check the Third Party SQL Developer Extensions



Scroll down or do a search for trace.



Running It
Dead simple appears to be their mantra, and we don't get far from that.

I didn't set up anything before hand, so here is what happens when you first run a SQL (or PL/SQL) statement:



You are prompted to choose how you want to do this. Since this is my sandbox, I just went for public to make things easier. Doing so requires SYS access of course.

After that you get a splash page from Method-R.

So here's what I ran:



That first row highlighted in the bottom pane, that's my trace file.

I open it up using the SQL Developer Viewer which looks like this:



A closer look...



Now, I don't have much of a clue about what all these means. That's the next step.

I like that this really is "dead simple," especially when it comes to us developers. Simple is good and usually appreciated.

What's Next?


Martin suggested checking out Trace Analyzer or TRCANLZR, which can be found here (MOS account required). Looks pretty neat, but again, I should kinda learn what the heck these things are telling me before I get too far along.

If you have a suggestion for a tool to check out, leave it in the comments. Hopefully I'll be able to get to it soon.

I would prefer something that just gives me red, yellow and green lights...:)

An External Table Definition

Yes, I know these examples are everywhere...but this is:
1. The first time I had used fixed-width column specifications
2. NULLIF

I'll trim it down for the post, but you can find the file here.

I may have mentioned before, but I'm working on putting together a dimensional model for an old Florida Medicaid database. The goal is to provide this to researchers so that they can, umm, research, Autism. Of course this one is near-and-dear to my heart.

So here we go...

CREATE TABLE ext_claim
(
  recipient_id VARCHAR2(12),
  month_of VARCHAR2(4),
  bucket VARCHAR2(2),
  date_of_service DATE,
  date_of_payment DATE,
  claim_count INTEGER,
  units INTEGER,
  ...snip...
  ndc_code VARCHAR2(11),
  record_type VARCHAR2(1),
  county VARCHAR2(2)
)
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ag_file_dir
    ACCESS PARAMETERS
    (
      RECORDS DELIMITED BY NEWLINE
      FIELDS 
      (
        recipient_id (1:12) CHAR(12),
        month_of (13:16) CHAR(4),
        bucket (17:18) CHAR(2),
        date_of_service (19:26) CHAR(8) DATE_FORMAT DATE mask "YYYYMMDD",
        date_of_payment (27:34) CHAR(8) DATE_FORMAT DATE mask "YYYYMMDD",
        claim_count (35:35) CHAR(1),
        units (36:40) CHAR(5) NULLIF( units = '*****' ),
        ...snip...
        ndc_code (143:153) CHAR(11),
        record_type (154:154) CHAR(1),
        county (155:156) CHAR(2)
      )
    )
  LOCATION ('CLAIM_200811.txt')
  );

As to the fix-width columns, that was relatively easy to figure out, I just followed the docs.

After creating the definition, I would issue a SELECT COUNT(*) FROM ext_claim to see how many records came back (and to see if any of them didn't match my specification). At first I just changed all the INTEGER, DATE and NUMBER fields to VARCHAR2 data types, it was easier. Of course that's cheating and I have about 100 of these files to load.

At first I wondered if I could include an Oracle function like REPLACE, but searching through the docs didn't return anything, nor did The Google Machine. I looked at the list of Reserved Words though, and found NULLIF. Could it be that easy?

Sure enough it was. After the column specification I just added

NULLIF( units = '*****' )

and it worked. Too easy.