Monday, March 2, 2009

Oracle Studies Day 2

Since I'm stuck at home today because of a pretty hefty East Coast snow storm, I figured why not toss up a quick blog about the past 2 days of Oracle studying.

Now, I have never touched Oracle until 2 days ago but I have done some SQL programming, and SQL is basically SQL where ever your using it so that wasn't the problem. Getting Oracle 11g up and running was a bit odd and very different than getting MySQL running and many tutorials available show you how to install Oracle but I couldn't seem to find any to show how to configure it.

Here's a quick tutorial on how to get Oracle 11g up and running in no time:
1. Download your the proper version of Oracle here (Note: you must have a user account with Oracle to download the installer and I'm installing Oracle 11g on a Windows XP Professional machine with SP3)
2. After your download has completed, run the Oracle 11g setup.exe (Be patient, it's Java so it may take a bit to load up)
3. Once the Oracle 11g installer has appeared, untic the "Create Starter Database" checkbox and choose "Advanced Installation", then click next.
4. When the Oracle 11g installer appears again, choose"Custom" and click next
5. Now, Oracle will create a default location with the current Windows logged in user. You can change this directory name and also change the Software location name as well. Click next to proceed.
6. If all checks are successful in the following window, click next to proceed.
7. The new window will show tons of optional packages that you can install. Leave the default selections for now. If you find a need later to install a new package, it's easy to install it using Oracles Universal installer. Click next to proceed.
8. You should now see a window titled "Create Database". Tic the "Install satabase software only" radio button and click continue.
9. Finally, you should now see a summary of all to be installed. Click install and go get a cup of coffee for the reason that it may take sometime to complete the installation.
10. After your installation has completed without any errors I'm hoping. You now need to create a listener.
11. To create a listener, Click Start and find your Oracle installation, and go to listing Configuration and Migration Tools and choose Net Configuration Assistant.
12. Once the Net Configuration Assistant has loaded, leave the default of "Listener configuration" selected and click next.
13. Leave the default of "Add" selected and click next.
14. Here's where you give your new Listener a name. Name it anything you wish and click next.
15. Now select the protocol type of TCP and scoot it over to the selected protocols box and click next.
16. Leave the default port of 1521 and click next.
17. Finally, leave the default of "No" selected and click next.
18. You now have a listener, now you can create a Local Net Service Name by using the "Net Configuration Assistant" once again.
19. Once you have loaded the "Net Configuration Assistant" choose "Local Net Service Name Configuration" and click next.
20. Click "Add" and click next.
21. Give you service name a name and click next.
22. Leave the default of TCP selected and click next.
23. Now enter your hostname by either using localhost,, or your static ip address if thats what your using and click next.
24. Now Click the "Yes, perform a test" radion button and click next.
25. Now, my test came back as a failure but everything is working just fine. Click next or finish to complete the wizard.
26. Now that you have all that is needed to get started. It's now time to try and login to Oracle by creating a new connection using the SQL Developer.
27. Click start and go to your Oracle installation and go to list "Application Development" and select "SQL Developer".
28. Once the SQL Developer has loaded, right click "Connections" and select "New Connection"
29. Where "Connection Name", enter a name for this connection
30. Now you need to enter a username and password. You can either user the "system" user and the password you provided during installation or create a new user. To create a new user open SQL*Plus and enter system as the username and your password to login to SQL*Plus. Enter the following command CREATE USER new_user IDENTIFIED user_password;. Where new_user is your new username and where user_password is the new users password. Hit enter and your new user should be created. Now you need to edit permissions for the new user. Enter the following command GRANT connect, resource TO new_user and hit enter. You should be prompt with a granted line in SQL*Plus.
31. Now back to SQL Developer, enter you new login criteria into the Username and Password fields.
32. Leave the Role set to default
33. Leave the connection type as the default of Basic
34. Enter your hostname localhost,, or a static ip address
35. Leave the default port of 1521
36. Select "Service Name" and enter the service name you created earlier
37. Click "Save" and then click "Test"
38. If your test status is "Successful", you will be able to login to you new connection
39. If a failure occurs, double check all your settings and previous configuations

It may seem like a lot of work but its really not that bad and shouldn't take too long to get Oracle 11g installed and configured. Hmmm, maybe I'll create a tutorial video on this one.

Hope this helps anyone and please feel free to leave me comments.

- Pete

No comments: