Upgrade from 8.0.5 to 9i : ReDesign [message #59738] |
Fri, 19 December 2003 06:08 |
Vinny75
Messages: 44 Registered: October 2003
|
Member |
|
|
Hi all,
I am in a situation where I had to take over the DBA role to administer an application currently running on 8.0.5. Based on my initial look, I notice a lot of quick fixes and things that were done with no forethought. Since this is a small app, I guess they did not care as long as the DB is up and running. There is only so much one could do in 8.0.5 especially on an application that was homegrown with bad DB design for 4 years. Now we are moving to 9i from 8.0.5 and I am looking at this as a chance to do some house cleaning and start taking advantage on most of the 9i features.
I would like some expert advice on some pressing issues as follows:
1. Going for Locally Managed TBS and quit worrying about extent management for each objects
2. New Backup recovery methods that I can take advantage of
3. How to establish proper failover mechanism
4. Establishing proper audit process. Right now we use custom triggers to track changes to certain tables. Perhaps this is redundant since Logminer does the same.
5. We do have several instances that are cloned periodically from production. Whats new in 9i that will make this more efficient.
6. Anything yall can share based on your experience.
I am going through the features and 9i. But real life experience will be valuable.
Thanks a Bunch
V
|
|
|
|
Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59740 is a reply to message #59739] |
Fri, 19 December 2003 08:09 |
Vinny75
Messages: 44 Registered: October 2003
|
Member |
|
|
Thanks. NO I have not used Workspace management. What does it do?
I have a Sun pizza box sitting where Ive loaded Oracle 9i. If I were to use Database Export from source (8.0.5) and Database Import on the target (9i) for my migration, how do you make sure the objects go to the right place? Meaning Tables into Locally managed Data TBS and indexes into Loally managed INDX tbs?
I remember using INDEXFILE option on import and editing the file to change the tablespace. Will it work in my migration case? Or is there an alternative?
|
|
|
|
Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59742 is a reply to message #59741] |
Fri, 19 December 2003 08:56 |
Vinny75
Messages: 44 Registered: October 2003
|
Member |
|
|
Thanks Mahesh.
Correct me if I am wrong. YOu metioned
"create the user.grant user quotas only on these two tablespaes.run the import "
if I cant create same TBS in the new DB, wont the import fail when it encounters a table that belongs to a tbs that dont exist in the new database?
I am aware that we can avoid the whoe problem if I pre-create identical TBS names.
|
|
|
|
Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59744 is a reply to message #59742] |
Fri, 19 December 2003 09:06 |
Vinny75
Messages: 44 Registered: October 2003
|
Member |
|
|
mahesh
Ignore my last question. I did not realize that if the TBS dont exist, oracle import puts the object in the default tbs.
I will manually move the objects to the right place once I am done with the import
thanks
|
|
|
Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59747 is a reply to message #59743] |
Fri, 19 December 2003 10:01 |
Vinny75
Messages: 44 Registered: October 2003
|
Member |
|
|
Thanks. Sounds like a plan. Suppose you had created a TBS in 9i that is not LMT, can we change it later even though it contains objects? How does oracle handle this? Does it move the content to a temp tbs, re-org and then load again like table rebuild?
|
|
|
|
Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59784 is a reply to message #59748] |
Mon, 22 December 2003 05:19 |
Starter DBA
Messages: 1 Registered: December 2003
|
Junior Member |
|
|
Mahesh
Thanks for the advice. Is it possible for you to mail me the automated shell script wherein you have exported and imported from 8.0.5 to 9i? I am assuming its not just a simple oracle 8.0.5 export and a 9i import into target. I will follow the same suggestions.
Thanks
v
|
|
|
Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59786 is a reply to message #59784] |
Mon, 22 December 2003 05:58 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
it just a straight forward export import process.
1. first export the source ( from source machine ..oracle 805 server.. i export with out indexes)
2. drop the existing tables in target ( an sql script)
3. import to target ( run the import for source . always use lower version of exp/imp executables to do export and import when there is a difference in the version of oracle. else u need to install the views in the lower version of oracle)
4. rebuild the indexes ( i have a seperate script that extracts the ddl of the indexes from the source)
|
|
|