Oracle Databases

Oracle Mar 26, 2001

   This starts out using SuSE 7.1, then I switch to 7.2 and finally to SuSE 7.3. For those of you who visit my SuSE linux pages, you will find most of this repeated... unless I have gotten around to editing it. I skipped Oracle when I installed the SuSE 8.x series but I believe that most of this will apply. Probably will even apply to SuSE 9.0. I am going to try the install of Oracle 8.1.7 on SuSE 9.1 which uses the new kernel... check back later.

   Ok after struggling and reading and struggling... I finally got the thing to read the Oracle CD. Now I'll swear that I had tried this before with no success. The fix was to unset LANG. Some problem with the IBM Java that is on the Oracle disk. Now during this time I also loaded some of the software that I thought I didn't need. Perhaps the two combined together have fixed the problem. At any rate I can now load the new Oracle.

   I still can't get a clean install of the DB. Ran into an httpd problem. It appears that they have a new library version.

Cause:
  The new version of the package gd, needed by both 
  libphp3 and libphp4, wasn't 
  installed during the update. 
Solution:
  Manually update the package gd, found in series d. 
  If the package gdlib is installed,
  you have to deinstall the package before updating gd! 
  The PEARL modules for GD, formerly found in package gd, 
  now reside in the package  perl-GD.
  The package gdlib was renamed to gd. 

   I have a bunch of the SuSE updates and I am going to install them before I go any further. Got those installed and finally got the Oracle DB loaded. Still need to create the DB but all the software is loaded.

   I left myself a note to do some documenting a couple of days ago and time and interruptions did not permit. Now I can't fully remember what I was going to document!! I know it had something to do with Oracle cause I finally got it to load. I can't say it works other than I put in the fixes to use Oracle 8.1.7 on SuSE 7.1. I had trouble creating a DB. It kept telling me that I didn't have permissions. I finally set all the areas that it was complaining about to 755. After that I could store the script that is created by dbassist and then execute it successfully. Don't believe that this was the correct way to do this.

   Also consulted the SuSE Web site to see if there was anything special. In addition to the unset LANG they are also recommending unset LC_TYPE and RC_TYPE. Here is a real strange one .. you must have your NUMLOCK key off or the install buttons don't work!!

   Instead of repeating everything that they said ... I have included a copy of the pages of interest from SuSE.
Oracle 8.1.7    Oracle 8x Common

   Hmmm. Started this so long ago I don't know whether or not I have the OPS option in the Oracle version 8.1.7 that I downloaded. I don't really think so cause I had to look it up. It is "Oracle Parallel Server". It is an option and right now I don't think that I want it.

   According to the Readme in the patch, it doesn't get applied until after the the install. The reason for the patch is because of library differences. glibc-2.1.3 vs glibc-2.2 (The patch is from Oracle. glibc-2.1.3-stubs.tar.gz - go to oracle.com)

   Oh but alas .. there is now an Oracle 9i that probably won't need the patch. Need to read more on the web and see if I'm correct. It will mean a big download .. again. Ok .. I read the docs. They developed the Oracle Linux version using SuSE 7.1. There was a kernel update and it just so happens that the update is in 7.2. So I guess that I am gona have a big down load. Even on cable this takes a long time. ... Well.. I don't have to worry about that. I don't have enough memory to install 9i. Always a day late and a dollar short!! Continuing on with 8.1.7.

   Now I have this book. "Oracle8i for Linux Starter kit". It came with 8.1.6 and I upgraded to 8.1.7. Now I have only been really successful in getting this installed and usable once. Due to time and other problems I haven't accomplished that again. This time I have time.

Oracle Setup

   September 29, 2001
#*#* Special Note: #*#* On installation after installing SuSE 7.3 there are a couple of more quirks.
First one is: You need to install bindutil from SuSE 7.2.
Second one is: During the DB install when it gets to the point where it is doing some links it will complain. Ignore these errors .. 6 or 7 of them. You can let it try to create a DB or not. Either way one will not be created.
Third one: After exiting the DB installer, install the patch. You need to untar it in the oracle home dir and then execute the setup script. This will among other things complete those operations that I told you to ignore.

   First there are some preliminary steps. You must set up an Oracle user and various environment variables. They are all listed in the book. I'm sure that they are also somewhere in the Oracle docs. Once these are setup then you can start the loading of Oracle from the CD. I am signed on as oracle and I have the CD-ROM mounted. Now, theoretically, all I have to do is run ./runinstaller. I placed an unset LANG in a profile.local so that I won't have to worry about it. The other two mentioned are not set in my version of all this.

   Important items during the install: (SuSE has tried to be helpful in creating the oracle user. However, what was created was not correct for the exercises in the book. I removed their user oracle. They also created the dba group. I left it and used it. Also notice that opt is used rather than usr.)

Operating system:
	groupadd sysdba
	groupadd sysoper
	groupadd oinstall
	userdel -r oracle
	useradd oracle -d /opt/oracle -g oinstall -G sysdba,sysoper,dba -m
	passwd oracle
	chmod 755 /opt/oracle
	cd /opt
	mkdir oraInventory
	chown oracle.oinstall oraInventory
Environment: (At this point it is primarily for the Oracle user.)
	export DISPLAY=localhost:0.0;
	export ORACLE_BASE=/opt/oraInventory;
	export ORACLE_HOME=/opt/oracle;
	export ORACLE_SID=oralin;
	export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/buildtools/bin;
	export TNS_ADMIN=$ORACLE_HOME/network/admin;
	export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data;
	export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/classes111.zip;
	export LD_LIBRARY_PATH=$ORACLE_HOME/lib;
Make sure you have a /usr/local/java directory.

   Sign on as user oracle if you are not there already.

   Got the latest updates as they suggested. Now I need to do it. I fired it off and answered the questions as it was installing. It got to a point where it was creating a database which, according to the instructions from the patch file, I was not supposed to allow it. Well, it was in progress so I let it go. However, it came up and said it was having trouble. I clicked on abort and it abandoned the DB creation. I will create it after the patch is installed... like they wanted.

   Well, guess what?? It didn't work. Got the same errors. Now the last time I tried this I had to set all the dirs involved to 755. I'll check and do that now. Hmmm they all have 755 or higher. Ok let's try one more time.... How 'bout that??!! It worked. These computers can be so #@%* frustrating at times...!

Database creation completed.
        Database Information:
          global database name:  oralin.trcomputing
          database system identifier (SID):  oralin
          SYS account password:  change_on_install
          SYSTEM account password:  manager

   trcomputing is my own domain on my personal computer. Naturally you will have to use one of your own.
   The database is created using dbassist from the bin dir of /usr/oracle.

The following were/are primarily for my SuSE 7.3
  • Create a database. (only choice if this is first time)
  • Typical or Custom. (I chose typical)
  • You are given choice of copying from the CD or creating a new one. (I chose create a new one.)
  • You are given the choice of database types: I chose Multipurpose.
    Other choices: OnLine Transaction Processing and Data Warehousing.
  • Now you are asked for the number of users. If you selected OLTP and now select more than 20 users, Oracle multi-threaded server will be enabled.
  • Next is the options you want to use. I chose the defaults.
  • Next you are asked for a DB name. Remember that name you tried to used during DB installation? It didn't get created so you can use it now.
  • Now we get to a create now or later. I have had trouble here before. This is where it says it can't save the script and some other files. This is where I set the areas concerning the DB to 755 permisions. Ok I'm going to try it first. I'm going to do the create and not save the script. You are warned that it will take some time. That's ok as long as it creates one.
  • Well it failed on the first operation. Couldn't find orapwd.
  • Since it took me back to create or save script I decided to save the script. It lets you select the dir and name to save it in and I picked /opt/oracle/DBscript.sh But it came back and told me it was gona save it as /opt/oracle/assistants/dbca/DBscript.sh ... so why did they ask in the first place??
  • Hmmmm. Didn't save it and it complained about orapwd again. Appears to be something concerning the oracle working directory.
  • Well the reason it couldn't fine orapwd is: It is named orapwdO for some reason. I also saw a bunch of other file in the /opt/oracle/bin dir that had a 'O' on the end. Some but not all have a counter part without the 'O'. Well, I'm gona take a chance and link the 'O' names to ones without so then I will have both.
  • Well, it got further but had a problem with the DB not being open. I aborted. However, some of the previous DB got created cause during the create is said the files existed. I said delete them. Which is what I will do no my next try after I find out what went wrong this time.
  • Ran it again and this time I had it save the script. That way I don't have to go through all those questions again and again. Or at least I think it will work that way.
  • Well it is getting segmentation faults when it tries to execute svrmngrl. Now I didn't link this one. It already had a counter part. Wonder if I should anyway?? Hmmmm.. I'm gona.
  • This time in the start of dbassist I was given the option to delete.. I did.
  • Well that was worse, so I am putting svrmgrl back the way it was. In fact I put them all back the way they were except orapwd.
  • Nope but it got farther. Failed on the listener. So I will link lsnrctlO to lsnrctl.
  • Allllrighty... the only two I had to change were orapwd and listener.

   Now then, the database has been created and loaded... But .. it is not running. In fact the only way you can even try to do anything is if you sign on as the oracle user. I tried to use sqlplus from my area and it didn't recognize the command. Now, signed on as oracle it at least understands the sqlplus command. However, I received a message stating that "Oracle was not available". A CTL-D will get you out of sqlplus at this point and back to the command line. Normally in sqlplus you would enter EXIT or QUIT to exit.

   Note for 7.3: In this one sqlplus was/is sqlplusO. I did the link to get sqlplus.
Ok now, we need to start the DB. First sign on as the oracle user and then enter sqlplus. At the "Enter user-name:" enter sys/oracle as sysdba. This will get you into an SQL session. Now if you setup everything as mentioned above, just enter:
   STARTUP OPEN PFILE=$ORACLE_HOME/dbs/initoralin.ora;
Well, I guess that everything wasn't done correctly for 7.3. It didn't create the control files. I'll have to investigate that now. Well part of the reason was .. evidently some things got started cause I found a lot of oracle stuff running. Killed it all and just for good measure did a re-boot.

Running dbassist again. During this run I got a question that I'll swear I didn't get before. Wants me to select the mode in which I want the server to run. Choices are: Dedicated Server mode and Shared Server mode. Because I am the only user I am specifying Dedicated.
Another new screen: After the options select screen it asked for the DB name and SID. It has done this before, but this time it told me what the name of the initialization file will be. Hmmmmm.
Well, now .. another new screen. This one is talking about the control files and giving their names!!
But yet another new screen: This one is letting me review the system table space and allowing me to make any neccessary changes. I'm making none. This screen has tabs to various pieces of info.
Must be creating a DB this time. I got another new screen. This one lists the redo log files.
Another one. Talks about the Checkpoint.
Another one. Talks about the SGA.
I think I must have made a wrong selection somewhere.. but maybe not. Another screen: this one lists the trace files.
Finally the 'Create database now?'.... But if failed on 'interMedia' again. But I got a new message. It was telling me that it couldn't open the DB. Now it is saying 'Oracle not available.' So I aborted the creation again. However, this last one has shown me a lot.
Ok. I'm gona save the script again and run it separately. I get more messages and info when I run it.
Well, I got a segmentation fault on svrmrgl. This is one that has both types. Just for grins I am gona save the one and link the other.
Well, that made a difference. Before It zipped thru the creation the they had said was gona take a few minutes. I thought it was fast cause of my new P4 machine. Not the case. Just didn't have the correct execution files. I stopped the process cause it looked like it was hung up in 'Add Advanced Rep'. Also wanted to try to time the operation. That was a mistake. Now I have the DB all screwed up!! Shut everything down and re-booted. I was able to delete the old DB.
Oky-Doky. It finished and took about an hour + or - 10 to 15 minutes. I got to doing something else and didn't see when it finished.

   Now then we should have a DB running. If not repeat the step up above.

   If you did everything correctly you will have a DB up and running. In order to shut it down, you again must be in sqlplus and enter:
   SHUTDOWN
You can use NORMAL, IMMEDIATE, TRANSACTIONAL or ABORT as an argument to the SHUTDOWN command if so desired. Normal is the default.
There is one more part in starting Oracle. The TNS. You need to start the listener. It does not automatically start with Oracle. This command is executed from the command line. This command is:
   lsnrctl START
STOP and STATUS are two more args that you can use for this.

   MultiThreaded Server:
Well, as usual I did what the book said and it didn't work. This was the section on enabling MTS which is MultiThreaded Server. I will investigate that more later. Could be because the book is designed for 8.1.6 and I am using 8.1.7 but I really doubt it. (Think I didn't have enough users and didn't specify the DB creation correctly on the last install. On this one I noticed the different modes.)

   Now as for the startup, you can set parameters that will cause Oracle to start running at system startup. However, I don't really want that. I do, however, want it to shutdown automatically. So I altered the script so that it won't start it but will stop it. There is a file in /etc called oratab that contains a line $ORACLE_SID:$ORACLE_HOME:(Y|N). It really doesn't look like that. The values are naturally translated into what you are using on your system. The final value on that line states whether you want automatic startup or not. I believe that it has to be yes even if you only want auto shutdown like I do. By the way, you can have more than one DB running and if you want autostart on all, they all have to be listed in oratab. (oracle table)

   The script to execute is called dbora. You will be basically signing on as Oracle and starting the DB and TNS operations. Info is in the book. Once this script is written you will have to create the appropriate symbolic links in the /etc/rc.d directory.

   Well, as usual I am changing what I did. The above was good for learning but I am using the files that came in orarun8i.rpm for SuSE. There are really only some minor changes.
One of the changes is oracle.sh in /etc/profile.d that will set the Oracle environment vars at boot time. Not sure if I like their startup but for now I will use it. I commented out the startup anyway. All I want is the auto shutdown. (dbora vs oracle script). linked as K10oracle and S40oracle in /etc/rc.d/rc2.d and /etc/rc.d/rc3.d
Another change was to put /usr/local/bin/oraenv in .bashrc. This will set up the environment and ask which SID you want to use. (I'm using oralin.)

   Ok. The DB is installed. Now in continuing, the book wants you to use this user scott. Well, I want to be myself. So I jumped ahead and found out how to create a user. Then when I couldn't do anything I had to jump further and find out how to grant myself privileges. I believe that I can now finish the lessons as myself. If not I'll have to find out why ... which is what I am doing anyway.

   Well, I have a problem right off the bat. The first thing to do was run a script that is specific to this chapter (chap 4). I did that and then decided that I wanted to be me. So I was in and out of the DB while I was figuring out how to make myself a user. I tried to do a select on the DB for the orders and it said it was not there. Think that I was supposed to stay in till the lesson was finished. I don't think that the SQL script did a commit. Hmmm. Bad guess they did do some commits... but the main thing was that I had to stay in. The commits that they made must not have been what I thought. At any rate I can list the items in the order table after running the chap04.sql script again.

   I went through the exercises for chapter 4 and played around with the SQL. Got out of the DB, shutdown the 'puter and went to bed. I fire it up today and the tables for chapter 4 are not there. Now I need to find out why.

Toad & Tora

   Spent a lot of time yesterday investigating the above and getting some other tools. I now have a tool similar to Toad on Windows. It is called Tora. I also got some scripts that will help show items when all you have is sql.
I had a bit of a problem installing Tora. It kept complaining about needing 4 libs. Two of them were associated with the client and the other two were just Linux libs. I looked and they were there... but rpm install couldn't find them. I just did a --nodeps and the package installed AND worked. Go figure.

   Now in all of this I did find that the tables from the chap04 exercises are still there. I just have to bring them in the correct way. Ok, ok. Guess that I don't know as much as I thought I did. At any rate, the script for chap04 created a USER named PRATICE04 with a PASSWORD of password!! I was trying to look at it all wrong. Well, it was something learned ... and on my own. The other good thing about this is, I can now access the practice04 tables from Toad in Windows. Coolness . I now have a functioning DB system.

   Alrighty now. I have created a database, a user chuck, roundaboutly a user practice04 and some tables via practice04. Now I need to find out how to give chuck access to those tables. I believe that I will have to be system and grant the accesses. Hmmmm. Well, I granted access to chuck but I had to be practice04 in order to do it. Or at least so it seemed. Possibly could have been cause I didn't specify "practice04.orders". I'll test/check it again during chap05. Also found that there is a set command. Used it to make the display 132 columns wide so the output would quit wrapping and getting hard to read. (set linesize 132)

   Now in exercise 5.6, they have you enter a new item in the parts list. I did this and looked at the DB via Toad in Windows. The new part didn't show. Didn't think it would cause I had not performed a commit. I went back to Linux SQL and performed the commit. I could then bring it up in Toad in Windows.

   Well, I said in the beginning that I skipped the version 8.x's. That is not totally true. My first experiences in switching to SuSE version 8.0 were just trying the Update and Oracle worked fine. The only real problem I had was with VMware which involves Windows which also usually spells trouble. In this case the trouble was ... hang on to your seat ... to much memory!! Arrrggg! You can read about my trials and tribulations with SuSE 8.0 in my other WebPages.

   

Valid HTML 4.01! Click here to validate current page. Best viewed with ANY browser! Valid CSS! Click here to validate current CSS.


Copyright © 2004-2004 All rights reserved.