Solved

Migrating Oracle 7.3.1 to 8i - triggers and stored procedure migration

Posted on 2008-06-23
23
836 Views
Last Modified: 2013-12-23
Hello,
I have an oracle 7.3.1 database (application dependent) that I may need to migrate to oracle 8i.  I was wondering if an Oracle 8i database can be built (or at least tablespaces) from an Oracle 7 export including all triggers and stored procedures.

The o/s is Sun unix.
0
Comment
Question by:DwayneZandbergen
  • 8
  • 8
  • 5
23 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 21852708
not exactly.  You can't create an 8i database out of any version's export.

First you create the 8i database,  then you will import the dump files into your 8i database.
If the export is schema level, you will have to pre-create the users in your new database.

Use the 7.3.1 exp  utility and the 8i imp utility.

also, if you can, you should look into going to an even newer database than 8i.  It's no longer supported and 9i will only be supported for another week.
0
 
LVL 47

Accepted Solution

by:
schwertner earned 500 total points
ID: 21852970
There is only one way - Export/Import.
Export/Import in Oracle is compatible upwards from Oracle 6 up to Oracle 10g.

You have two options, but both of them need a preinstalled Oracle DB
(preferably 10g R2).
1. To make same directory structure on the new installation. This will
help you using FULL export/Import automatically to create the tablespaces and
users. This is not a prereqisite and in many cases is impossible due the
improvements and differences between the installations
2. To make nonsystem schemata Export and Import. In this case you need to precreate the nonsystemusers and
tablespaces.

In both cases a must is to set NLS_LANG environment variable on
both instance in order do not smash the nonEnglish letters.


If you decide to do schema level Export/Import do this:

Install 10g and create the seed DB.

Create the NONSYSTEM tablespaces and users:


CREATE TABLESPACE "TSIMEDICAL"
LOGGING
DATAFILE  'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSIMEDICAL00.DBF'  SIZE 100M
AUTOEXTEND ON    NEXT 100M
MAXSIZE 32768M
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT  MANUAL;


CREATE TABLESPACE "TSDMEDICAL"
LOGGING
DATAFILE  'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSDMEDICAL00.DBF'  SIZE 100M
AUTOEXTEND ON    NEXT 100M
MAXSIZE 32768M
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT  MANUAL;


CREATE TEMPORARY TABLESPACE "TSTEMPORARY"
NOLOGGING
TEMPFILE  'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TSTEMPORARY00.DBF'  SIZE 100M
AUTOEXTEND ON    NEXT
100M   MAXSIZE 32768M
TEMPORARY   ONLINE
EXTENT  MANAGEMENT LOCAL
SEGMENTSPACE MANAGEMENT  MANUAL;


CREATE USER LSMEDICAL  IDENTIFIED BY LSMEDICAL
DEFAULT TABLESPACE TSDMEDICAL  TEMPORARY TABLESPACE   TSTEMPORARY
QUOTA UNLIMITED ON TSDMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSIMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSTEMPORARY;

GRANT create procedure, create session,create table,create type,create view,create synonym TO LSMEDICAL;

After that do an export (full or schemas level) on 8i using Oracle 8i Export Utility.

Transfer the dump file in BINARY mode to the 10g instance machine.

Using 10g Import   the nonsystem schemas.

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21854418
Don't you think a split is in order?
0
 
LVL 1

Author Comment

by:DwayneZandbergen
ID: 21858585
1. Because Schwertner provided more in-depth response to my question with great examples.
2. I don't care as long as I have my answer.
3. Split it if you want.
0
 
LVL 1

Author Comment

by:DwayneZandbergen
ID: 21858642
4. I see Schwertner is in 2nd and needs the boost up the ranks.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21858724
1 I agree schwertner expanded on what I said and deserves points but I think a split is in order because its still esentially the same thing I said.

2 ee works because of the scoring system.

3 we can't but I did request admins to look

4 our rankings shouldn't factor in to the point award but even if they did, schwertner is by far the leader in all things oracle.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21863326
Gentlemen,
in April I asked Mr. Stuber to pay attention on his behaviour here at the Experts-Exchange.
Now I am asking this for a second time.
I work for Experts Exchange since January, 2001 but for first time I meet
such agressive behavior from an Expert.

Mr. Stuber,
if you use the search machine of Experts Exchange you will see that I gave this answer
possibly between 50 and 80 times. This doesn't mean that I have the monopoly at this topic.
This only means that I am more experienced as you - answering many times I now what
the asker wants and also I can display my opinion in a good form.

If you analyze my answer and compare it with yours you will see that additionally I gave much details
that only experienced Oracle professional can give:
1. How to achieve automatic tablespace creation
2. How to use NLS_LANG variable. Small detail? No! in 2004 I did my first upgrade in germany and when I went
back the customer encounters that all Umlauts were smashed - a '?' appears instead Umlauts.
3. The details of user and tablespace precreation are very important.

Mr. Stuber, the fact that you have given an opinion doesn't means that you have preferences
in the score system. Also please, be informed that there were many times the Asker has accepted unappropriate and even wrong opinion. I have never react. The asker is owner of the question.

Mr. modus_operandi,
I kindly ask you to pay more attention on Mr. Stuber attitude on Experts Exchange. As I said before this manners are unacceptable. Please also take in account that this wastes my time. I have to work and also to try some issues. The feelings of Mr. Stuber are his personal issue and I am too busy to consider them.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21864744
schwertner, I don't understand this attitude at all.

I do nothing at all on Experts Exchange except try to be helpful, I think the point and scoring system of Experts Exchange is ingenious and the key to keeping the system working and as such I do my best to work within in and promote it.

I didn't do or say anything disrespectful to you or the asker in my above comments or in my request for review.
I know you have posted negative things about me in multiple questions, I have not sought sanction against you for this though.
I thought, from our offline conversations that we had,  we were more or less on the same "team" trying to help everyone but I guess not.
For whatever reason, you seem to take my best efforts as a personal attack on you.
I don't know why you do this.  Yes, I will correct you if you make a mistake, but, if you look over our posts I also support your answers
when they are correct and have been challenged by others.  If you do a search on my posts you'll also see that I have requested
points to be taken AWAY from me and given to others when I thought the scoring wasn't reflective of the answers and the EE scoring system.

I'm simply trying to follow the scoring rules.  Yes, the asker owns the question, but an incorrect answer "should" be changed as it influences the future searches.
Plus, in many cases, incorrect or incomplete acceptance is simply an accident or oversight on the part of the asker.
If you look at some of my other requests for attention (there are only a few, I don't challenge lightly) you'll see this has happened multiple times.
Not so for this particular question it seems but,  in this case while your post was perfectly correct, so is what I posted.  The rules of scoring say to accept the first answer.    If you had a longer delay behind my post for answering, or if you had never posted at all, or if the asker had logged back in to EE to check on this question just one minute before you go to it, I assume my question would have been accepted as it is essentially the same answer you gave.  Worst case scenario, if my answer wasn't satisfactory the asker could have given me a B for incomplete or simply asked for more details and I would have been happy to supply all of the above information as well as links to supporting documentation and sample exp/imp par files.  Given the asker had already asked about export (implying knowledge of its use) and ranked himself "intermediate"  instead of beginner. I didn't see a need to innundate with extra details.  I highlighted the ones that I thought were the most important, first no, a db creation can't be done simply with an export,  suggesting extra upgrade, admittedly brief description of steps to take and noting the importance of using the proper version of each utility on the respective source and target systems.

Your answer isn't different from mine, but it DOES have useful extensions to my answer (I never said or even implied that it didn't), and in the spirit of promoting Experts Exchange I suggested to not change the accepted answer from you to me, but instead to split the points.   What is possibly wrong with that?

modus_operandi (and any other advisors or moderators) please do investigate my posting trends.  I think you'll see I do nothing but try to be quick and correct , as well as respectful of both askers and the other volunteers.    When I do post counter answers I do not insult other posters nor have I attacked them for trying to help, and, I "think/hope", every time I've posted a counter to someone else's answer I explained why unless it was self-evident from the post itself.   Nor have I ever tried to elevate myself or my answers over anybody else by claiming superior education, experience, duration of EE involvement, certifications, etc.
0
 
LVL 1

Author Comment

by:DwayneZandbergen
ID: 21864840
Gents,

In order to best apply points going forward, could you please both post your resume in the knowledge base that I may review accordingly and deem who is more deserving based on your credentials for situations when there is no clear weiner, I mean winner.
0
 
LVL 1

Author Comment

by:DwayneZandbergen
ID: 21864943
I think we're losing sight of the Modus operandi.  I've copied an excerpt from this very site about the benefits of being an 'Expert'.  Pay special attention to number 5 (the last one).

- Access more than 2.2 million solutions
- Ask the brilliant Experts unlimited questions
- Browse our site ad-free
- Create your own knowledgebase of useful solutions to use throughout your career
- Build lasting relationships and exchange knowledge with worldwide technology Experts like you
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 21865018
My Resume in a short form is published in my Expert Exchange profile.

My answer is posted on this page. As everybody can see it is provided with more technicall details
based on the experience I have in this field. My intention was to prevent the Asker to fall in that troubles
and traps I fall when I tried to do similar task.

But I see that I forget to include a very important detail and it is:

If the Import fails for some reason, before doing next Import drop and recreate the users:

DROP USER LSMEDICAL CASCADE;

CREATE USER LSMEDICAL  IDENTIFIED BY LSMEDICAL
DEFAULT TABLESPACE TSDMEDICAL  TEMPORARY TABLESPACE   TSTEMPORARY
QUOTA UNLIMITED ON TSDMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSIMEDICAL;
ALTER USER LSMEDICAL QUOTA UNLIMITED ON TSTEMPORARY;

GRANT create procedure, create session,create table,create type,create view,create synonym TO LSMEDICAL;


Thinking about the procedures at Experts Exchange I am not sure that the moderator has the right to change the award for the question. If he does so this will be an obvious and pure act of manipulation ....
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21865532
I think (hope) the request for resume was a joke.  :)

As for the EE goals,  I think I've gotten along quite well with everybody on EE except schwertner.  I've received thanks and kudos from numerous members and given them as well.  I have no idea why he keeps slandering my efforts.  As I mentioned before,  we've emailed privately a few times and I thought we had resolved our differences.  We (obviously) don't agree on everything but I thought he had gotten over his animosity, I thought wrong.

I admire and respect his dedication and time spent on Experts Exchange.  He's shown a great deal of knowledge about the Oracle database and Forms/Reports and above all, a willingness to share his knowledge.  The only thing I don't care for is the condescending attitude towards others and hostility towards me.  Previously, I've assumed the attitude problem as a language issue and he probably doesn't mean some of the things he says.  However, given his repeated hostility towards me, maybe I'm wrong about that too.

So I guess I'll have to settle for a positive relationship with 87979 out of 87980 volunteers on Experts Exchange.  I hope I can count  DwayneZandbergen in the "plus" column too.


modus_operandi, I withdraw my request for a split.  A few extra points are definitely not worth all this fuss.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21865961
Sean,

many times I gave the best and most relevant answer (I know this for sure - I have 3 Oracle certificates)
BUT the asker had decided to award the points to somebody else. Not fair but thats the life. I have never posted complaints. Internally I establish a "black list" if such guys, but soon I forget top use this. Experts Exchange is only an Exchange .. Points are  .. a play.

You know our story with Kevin Sherman in Ohio - we were good professionals, but were kicked out ... we were not ... read Kevin's article in CNN. Not fair ... especially I expected more fairness in USA. But on Saturday I have to go to the Church and burn a candle in Kevin's memory... Sad ...

You see ... let us look what unite us, not what devide us.
0
 
LVL 1

Author Comment

by:DwayneZandbergen
ID: 21866440
Who is Kevin Sherman of Ohio?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21866953
I've also had good answers not accepted, and worse I've seen completely wrong answers get accepted and then later see a new question posted looking for a retry.  I let those go at first, but after awhile I started challenging them and I think in all but maybe one case my requests for attention have resulted in changes.  So, in general, it seems the EE admins agree have agreed with with my assessments of  what constitutes proper use of the EE system including grades and choice of acceptance.

In particular, it's important to get the right answer (or answers) accepted because the search features of EE are only relevant if you can find a question and use the accepted answer.  I challenge reponses not for my own points, (again, check and see I've requested points taken away from me) but so the question is left in the database with the most useful information.    I accumulate points by answering questions and helping people.  A few requests for review here and there are not going to significantly alter my totals or anyone else's.  The only significant thing that will happen is the best answer or answers will be accepted and graded as such.

In this particular case,  your answer and mine are essentially the same,  we each added something the other didn't and you added much more than I did.  So any search that turns up this question will have two answers to choose from and your answer is fine (superior even.)  Hence, I withdrew my request, I believe I was in the right as far as the spirit of the EE rules go; but given our already unfortunate relationship there is no reason for me to take a few points from your pile and put them on my pile when there will be no gain to the EE community as a whole.

Plus, I don't need the points anymore, my race is over.  At the start of the year I wanted to see if I could be the fastest to ever reach Genius in the Oracle zone and get a million plus this year; but I simply don't have the time anymore.  I'll continue to contribute and will likely come close but I don't realistically have a shot at it anymore unless my real job cuts back drastically.  That's fine,  I'll get there eventually.  At the rate I've been going lately, I'll hit genius in 10.x before I do in Oracle parent zone!  For whatever reason I seem to be gettting a lot more sub-zone questions lately.

on a side note,  I don't think you do this intentionally to be offensive, but you might want to consider avoiding some of the things you post.
Something like "(I know this for sure - I have 3 Oracle certificates)"  comes off as pretentious and condescending.  There is no reason to ever site one's experience, education, certifications, etc to try to defend an answer.  If two people posted the exact same answer to some random question would one of thos answers be more correct or more complete because one of the people was an OCP and the other was not, or because one said they were an OCP and the other never mentioned it?  No, of course not.  They would both be equally right or equally wrong.

The only possible place where a self-reference might be relevant would be a question that can only be answered by experience.  Something like "how often does XXXX occur?"  or "how many people use XXXXX?" or "what kind of issues have you seen with XXXXX?"  
Questions like these aren't technical questions, they are experience questions and a multi-year veteran is in much better position to answer those than a neophyte.

For technical questions, trying to justify or support an answer by citing personal characteristics is silly and often offensive.   For that very reason I have specifically NOT posted my resume on my profile.  I've put my EE timeline in it, but that's just a reiteration of public information.  My points and/or ranks are embedded on every web page I post to, so there's no hiding it.   And in the grand scheme of things, I take greater pride in my EE rankings than my education, experience, degree and certifications.  Experience is just time while paying attention,  the other things are just pieces of paper that say I passed some tests.  My EE points indicate that I actually helped people and that's much more worthwhile.  So I don't know mind tooting my own horn about those.  But, even though I'm proud of those points,  I have never, nor will I ever use my rank in any particular zone to suggest that my answers are in some way more correct than somebody else that might be ranked lower.   I haven't seen you use ee-rank/point comparisons, but you've certainly been guilty of the other self-grandisement.   I don't think it's intentional but I don't know why you do that.  

I'm certainly insulting your accomplishments, you've obvously done a lot and know a lot; but it's simply not relevant as a means of explaining why an answer may or may not be correct/complete.  Nothing you or I have done in the past proves anything we say today is right.  "Hopefully" our personal accomplishments will imply we are right more often than we are wrong, but that isn't a justification either.   As such personal accomplishments in the past for either of us (or any other volunteer) have no place in a technical discussion.

I think that's enough of a dissertation for today.  :)
I've got to go do some SQL Server work now.

Dwayne,  sorry your question had to be the forum for another rant session between us.
Schwertner,  I'm really not out to get you.  I want the best for EE and you're part of that, keep up the good work!

Have a great day everyone!
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21872150
I used to work in the U.S.A. 9 years ago.
One of my friend was Kevin Sherman, American citezen,
graduated Ohio State University. We both worked
as Oracle developers.
It is very curious that Kevin states in an article in
the American Press that I rescued him professionally.
An alien rescued an American in the heart of USA ...
In fact we were in the same company and have worked
close together with him to survive.
Here you can see what happens with Kevin:
http://edition.cnn.com/2003/TECH/biztech/08/11/training.replacements.ap/
I will say that this was really so.
Will not explain what happens with me ...
Kevin died on June 28, 2007 in Columbus, OH

0
 
LVL 47

Expert Comment

by:schwertner
ID: 21872186
Mr. Stuber, dear Sean!

I am an old computer dinosaur from the punchtape era ... before punched cards.
I have very bitter days in my career. I have also done many mistakes in my life
Please believe me.
I also was too emotional, I also would like to make impression.
Life is a long thing and emotianal erruptions are not the best thing.
I needed 4 and half yers to be "Genius" at Expert Exchange.
To be Genius for 1 year you have to be very brutal, to sit on the
screen 16 hours a day and this simply is meaningles.


I think you are one of the Experts here that will help Experts Exchange not only right now,
but also in the future. Also keep in mind that we have to unite and to work togheter.

So let us see more optimistic on the life.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21876633
I'm sorry, but I'm not sure what you're trying at the beginning of your last post.

To get 1,000,000 Oracle points in one year doesn't (or shouldn't) require 16 hours a day.  I'll probably get 700,000 or so just this year, and you likely get 800,000 at the pace you're going.  I'm not putting in anywhere near 16 hours a day.  Maybe if I get some more free time I'll be able to ramp up my efforts and get 800,000-900,000 but still that only requires maybe a few hours a day.  The time is spread out a few minutes at a time, I'm just not able to fit those few minutes in as much as I was at the beginning of the year.

Thanks, I hope to help a lot.  It's the biggest reason I come to EE.
I'm not sure what more uniting I can do.  I try to avoid working contrary to anybody else.  I try my best to support the other volunteers.  I cooroborate on good answers when they are challenged.  I correct their mistakes when I can and thank everybody that corrects my own.  So far, you're the only person that has insulted me for trying to help and you're the only person I've seen insult other volunteers and askers if they disagagree with you.  I'm really trying to believe you're one of the "good guys" so I'm continually taken aback by the attitude you take with me and others sometimes (and, it is only sometimes.)    I would like to get along with everybody here, including you. I'm not sure why I and others, but mostly myself, are the target of your animosity.

I'm quite optimistic about life.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21881303
Sean,

everything is OK!   On Experts Exchange there are many factors
that can influence the activity - the Askers, the Experts, even the mood.
Nobody will hurt you, nobody has bad feeling to you.
You have helped so many people.
You can be proud by yourself for the job you do.

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21895928
Well, I certainly do "hope" all is well.

This was never about the points, if it was, it would be a ludicrous way to accumulate them as "challenges" are an insignificant contribution to my overall total.
My request for attention was simply about the worthiness of my post.  I still stay it was correct and, while schwertner certainly added good information, his was still
basically just a "followup".

In this special case, his followup was superior (not completely superceding, but superior) to my post so I have no problems keeping his post as the EE "permant record"
of how to address this particular question.

The rest of the rant-fest, was about the bad attitude toward me.  I do apologize for that whole thing taking place in the context of somebody else's question.
I was not aware (probably should have been, but I wasn't) of the possibility of private threads.  If something like this comes up again, I'll be sure to take advantage of that.  Thanks!
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21897068
I kindly ask all my private posts to be scratched.
I am very sorry for this.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now