TNS-03505: Failed to resolve name

oraclenerd | Sep 2, 2010 21:16 +0000
I've been trying to build an image of OBIEE 11g recently, with not much success.

The process is as follows:
1. Fire up big-ass (to me anyway) Windows 2003/8 Server on Amazon.
2. Go through all the security hassle of Internet Explorer to download the software (I've since put it on it's own volume, and then attach that to my instance, so I don't have to do this 300 times)
3. Install 11gR2 database, software only.
4. DBCA to build database.
5. Net Manager to build the listener.
6. Create the weblogic metadata for OBIEE.
7. Install OBIEE.
8. Scream and yell when the configuration doesn't work.

Rinse and repeat 12 times.

I've gotten quite good at it.

Then I ran into this lovely error...
TNS-03505: Failed to resolve name
Cause: The service name you provided could not be found in TNSNAMES.ORA, an Oracle Names server, or a native naming service.
Action: Verify that you entered the service name correctly. You may need to ensure that the name was entered correctly into the network configuration.

I went through Net Manager and Net Configuration Manager about 1000 times. You know, instead of actually reading the error.

I thought I needed a Loopback Adapter. I didn't.

I started to go through the files by hand, sqlnet.ora, listener.ora, tnsnames.ora (which I'm much more confident with surprisingly)...it had to be something small.
# tnsnames.ora Network Configuration File:
c:\oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TESTING)
(SID = TESTING)
)
)

LISTENER=(Address=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))
I name all my sandbox databases "testing." Always have, probably always will. From the command line I issue:
SQLPLUS SYS/TESTING@TESTING AS SYSDBA
(Yes, I know I don't need to specify the user/password@service_name).

Spot it yet?

Could it be that my alias says: ORACLR_CONNECTION_DATA?

Yup, foiled again. ID10T in the house.


OBIEE 11g Install – Thoughts

oraclenerd | Sep 1, 2010 00:29 +0000
This post is almost a direct copy of my reply to the OBIEE EMG mailing list started by Mark Rittman about Initial Opinions on OBIEE 11g?.

I can't say I've had any success installing the software. So far I've tried on XP Pro (VM on laptop) and Server 2008 (AWS instance). I've chosen the Enterprise install each time.

The laptop I can understand, sort of, as it is severely underpowered. 3 GB RAM total for the machine. I understand the integration with WebLogic/Fusion, but it makes it that much more difficult for those starting out to get it up and running on their personal machines. I can still install the database on my laptop; in fact I have 3 or 4 versions (in VMs) on my machine right now. That's nice.

A couple of days ago, I fired up a cloud Windows Server 2008 server with 18 GB RAM. Installed the database. Created the BI metadata (rcu). Finally began the OBIEE install (Enterprise). It failed finally on one of the configuration steps (12 of 13), I believe it was one of the opmnctl processes.

That said, I haven't read the install documents from front to back yet. I have, however, installed a lot of Oracle software over the years, including EM, OAS (10g), etc. I can usually get the software up and running. This is a different beast though and is not for the weak.

I'll eventually read the docs, sooner rather than later.

On the bright side, installing the latest 11g forces people to learn a bit more about the interactions of the database, EM, WebLogic, etc. That's a good thing.

SQL Developer: Turn Off "Autogenerate GROUP BY"

oraclenerd | Aug 30, 2010 18:01 +0000
This is more for me since I seem to install it quite regularly.

I've been snagging the SQL from OBIEE query logs (nqquery.log), which doesn't come out too pretty.

So that I don't have to manually format 400 lines of SQL, I created a formatting template. Ctl - F7 and voila!

I do go back in and make small changes, which is why I am writing this. As I scroll down through the file and indent or change code, the GROUP BY clause is auto-generated, which is annoying to me.

To turn it off is easy (if you can remember, which I can't, which is why I write this).

Go to Tools - Preferences then look for Code Editor:

Code Editor

Expand that group and then go to Completion Insight:

completion insight

Uncheck the box next to the arrow and you're done.

My hope is that in the next version of SQL Developer, we'll be able to call the SQL Beautifuler from the command line. That would be pure awesome.

OBIEE 11g Install – Part I

oraclenerd | Aug 25, 2010 00:12 +0000
As you may know, OBIEE 11g was released not too long ago.

I downloaded it the day it was available, and have been trying every since to 1, find the time to install it and 2, not break the install process.

Environment
Guest: Windows XP Professional (an old image I had lying around)
VirtualBox 3.2.8
Host: Ubuntu Karmic Koala
Memory: 1.5 GB

In short, my laptop.

I started with the database, 11gR2 32 bit for Windows. No problems there.

As is my MO, I didn't read the installation instructions the first time I fired up the OBIEE installation. I was quickly met with the database connect string. Easy enough, machine:port:service_name.

What's this? You want a specific user? Bollocks.

I let it rest for a couple of days.

Meanwhile, a colleague asked a question about which database he could use. I asked if a version was specified and then quickly scanned the docs. No specific mention of a version...so I said Oracle XE, no 11gR2, no, XE, no, 11gR2. 11gR2, yeah, that's it.

That's when I finally decided to read the installation instructions...and found out I had missed one of the downloads.



Oh, you mean that one?

The Repository Creation Utility. Interesting.

I had heard that some of the new version would be database driven, I had just assumed (yes, I know about assuming) it was more RPD related (and it still may be).

Anyway, that took all of 15 seconds to install.

Back to the installer...and I don't learn any lessons, I tried to install it from my virtual folder, instead of from the "actual" disk. So, it took 4 times before I finally figured that out.

I copied the zip files to the disk, unzipped them and installed the sucker. By install I mean I am in step 12 of 13, Configuring BI Domain. That's the furthest I have gotten so I'm calling it a success.

Now to wipe the disk and start from scratch.

OBIEE 11g Released

oraclenerd | Aug 23, 2010 21:46 +0000
I'm not really out in front of this one, but I'll do the obligatory post.

11g was announced back in early July in the UK, followed shortly by an announcement here in the States. Every since then, I'll go to OTN, downloads, OBIEE to see if was generally available. I've done this every day for the past month. On Friday, I did the same thing and 10.1.3.4 was still there. I then got an email from my boss that evening saying it had been released. I guess I can't first for everything.

To download the latest and greatest, go here. The Overview is here. And finally the documentation.

I downloaded it on Friday evening (nerd) but haven't installed it just yet. I'm hoping to get to it this week. I wouldn't say I'm excited...I'm still in my learning phase...so it just means a bit more work. I'm sure it will be fun though.

Mr. Rittman, I believe, has written a book on 11g, which means he's had it for quite some time. Get on over there to see a host of articles pertaining to the new release. I'll start with the New Features section, the install, and probably head on over there.

Fun with SQL – Part 59

oraclenerd | Aug 23, 2010 10:29 +0000
I got an email today titled, "Your Favorite Movie."

It claims to successfully calculate your favorite movie by picking a number, from 1 to 9, then doing some calculations and applying the result to a list of movies.

Years ago...my first boss, got a similar email and began working through it via SQL. I was very impressed, both by the idea of using SQL and that he could do it in SQL. He was (is) a smart, creative guy.

I'm sure you've seen this one before:
Your Favorite Movie

Try this test. Scroll down and do the quiz as it instructs and find out what movie is your favorite. This amazing math quiz can likely predict which of 18 films you would enjoy the most. Don't ask me how but it really works!

Pick a number from 1-9.

Multiply by 3.

Add 3.

Multiply by 3 again.

Now add the two digits together to find your predicted favorite movie in the list of 18 movies below.

Mine was "Gone with the Wind" - exactly right! So be honest, and do it before you scroll down to see the list below. It's easy and it works.
How do you do this in SQL? It's not that hard really. First, get 1-9. SELECT from DUAL will accomplish that.
SELECT rownum
FROM dual
CONNECT BY LEVEL <= 9
Then you just need to apply the calculations. First go, I put the calcs in the outer query:
SELECT rn, ( ( ( rn * 3 ) + 3 ) * 3 ) calc_#
FROM
(
SELECT rownum rn
FROM dual
CONNECT BY LEVEL <= 9
)
/

RN CALC_#
------ ----------
1 18
2 27
3 36
4 45
5 54
6 63
7 72
8 81
9 90
A pattern jumps out at me immediately...

So I move it to the inner query and use SUBSTR to get each number and then add them together. I am sure there are better ways...this was quick and dirty.
SELECT
rn,
SUBSTR( t, 1, 1 ) + SUBSTR( t, 2, 1 ) total
FROM
(
SELECT
rownum rn,
( ( ( rownum * 3 ) + 3 ) * 3 ) t
FROM dual
CONNECT BY LEVEL <= 9
)
/
The results reveal the pattern rather quickly.
    RN      TOTAL
------ ----------
1 9
2 9
3 9
4 9
5 9
6 9
7 9
8 9
9 9
Of course #9 is the movie that you would never pick. It's funny though:

1. Gone With The Wind
2. E.T.
3. Beverly Hills Cop
4. Star Wars
5. Forrest Gump
6. The Good, The Bad, and the Ugly
7. Jaws
8. Grease
9. The Joy of Anal Sex With A Goat
10. Casablanca
11. Jurassic Park
12. Shrek
13. Pirates of the Caribbean
14. Titanic
15. Raiders Of The Lost Ark
16. Home Alone
17. Mrs. Doubtfire
18. Toy Story

COLLABORATE: Oracle ACE Dinner

Uncategorized
oraclenerd | Apr 19, 2010 14:31 +0000

Twitter to the rescue again.

I was at the OAUG 20th Anniversary Party where I consumed my usual 2 beers (albeit larger than normal beers), when I noticed this tweet by Brian “Bex” Huff.

With the help of a couple of very nice conference attendees, I was able to locate the establishment. They warned me that the restaurant might not allow me in as I was in my travel attire (flip flops), but they did. Pretty casual everywhere around here.

Anyway, this was my very first Oracle ACE gathering. I got to meet, among others, Iggy Fernandez, Sunil Ranka and Bex. I also met Vicki who is the Oracle representative who runs the program. She actually went to high school near me in Denver.

Although I may or may not have had too much to drink, I had a good time. Floyd Teter has introduced me to just about everyone in the world and I’m struggling to remember everyone’s name. If my phone or camera would work, I could probably take a picture and quickly tag it with their name. I need a system.

Anyway, I had the steak which was delicious.

Looking at that picture though…the presentation doesn’t look too appealing…I can’t think of a couple of disgusting things that it looks like. But I was plyed with beer and didn’t care much. Like I said, it was delicious.

I also got to meet Arup Nanda, who’s New Features guides/tutorials I’ve used a million times to learn something new. Not really sure I belong with that crowd…but I’ll take full advantage of the opportunity. :)