Home » RDBMS Server » Server Administration » Upgrade from 8.0.5 to 9i : ReDesign
Upgrade from 8.0.5 to 9i : ReDesign [message #59738] Fri, 19 December 2003 06:08 Go to next message
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 #59739 is a reply to message #59738] Fri, 19 December 2003 06:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
1. yes go for LMT
2. RMAN is my choice.
   if you have more space, take an export.
3. failover ? u mean application failoover or hardware failover?
   depending on how much u want to spend $$ u can go for. 
   ORACLE RAC is ideal choice.
4. did u get a chance to look into workspacen management?
5. Cloning or DUPLICATION is more powerful using RMAN.
   we have automated this procss to happen every week.

Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59740 is a reply to message #59739] Fri, 19 December 2003 08:09 Go to previous messageGo to next message
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 #59741 is a reply to message #59740] Fri, 19 December 2003 08:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
for workspace management please have a look [url= http://www.orafaq.net/cgi-bin/search/highlight?q=workspace%20thiru&url=http://www.orafaq.net/msgboard/server/messages/21211.htm] here [/url]

indexfile option has nothing to do with this.
if specified indexfile=somefile.sql , the ddl of the tables and indexes in the dump file is extrcated and written to the file . thats it.

export from 805 to 9i will work.
how to  make sure, the tables and objects are in right places?

precreate the tablespaces for data and indx in target db ( like the same in source..if not possible to have the same name NO problem).
create the user.
grant user quotas only on these two tablespaes.
run the import .
check the obnjects manually ( whether they exist in right places).
sometimes,if there is a primary key in table,
the index might be created in the data tablespace itself instead of index tablespace.
You may move those indexes later to appropriate tablespace.

a simple script might show u where the tables and indexes are created...

dbadmin@republic_lawp1 > @tb_lawp

TABLES and INDEXEs owned by LAWP_LAWSON

TABLESPACE_NAME     tables
--------------- ----------
DATA                  1825

TABLESPACE_NAME    indexes
--------------- ----------
INDX                  5212

dbadmin@republic_lawp1 > get tb_lawp
  1  column tablespace_name format a15
  2  prompt
  3  prompt TABLES and INDEXEs owned by LAWP_LAWSON
  4  select tablespace_name,count(*) "tables" from dba_tables
  5  where owner=upper('lawson')
  6  group by tablespace_name;
  7  select tablespace_name,count(*) "indexes" from dba_indexes
  8  where owner=upper('lawson')
  9* group by tablespace_name;
 10

Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59742 is a reply to message #59741] Fri, 19 December 2003 08:56 Go to previous messageGo to next message
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 #59743 is a reply to message #59742] Fri, 19 December 2003 09:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
u can avaoid that
case: export objetcts by scott and import into to another schema in another database
source_db
       username=scott
       pasword=tiger
       default tablespace= scott_data
       and indexes are in = scott_index
target_db
       username=magellan
       pasword= something
       default tablespace= magellan_data
       and indexes are in = magellan_indx

now during import do the import schema by shema

import dba/pass@target_db file=somefile.dmp fromuser=scott touser=magellan 

----------------------------------------------------------------------

all the tables in scott_data will be created in magellan_data ( becuase magellan_data is default tablespace for magellan)
if there are tables in other tablespcaes, they also will be created in magellan_data ( since it is defualt.  IF u have give resource role, then it will be created in the tablespace_with_same_name as in source. But since u have quota only in those two tablepsaces u can avoid it)
all the pk indexes are also created in magellan_data.
later we can move them to whereever we want.
using the script, if the tables are scattered everywhere, we can move them later.

 

Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59744 is a reply to message #59742] Fri, 19 December 2003 09:06 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #59748 is a reply to message #59747] Fri, 19 December 2003 10:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
by default, tablespace is LMT in oracle 9i.
so unless u specificly create  an DMT, u will not be having an dictionary managed one.
with export/ import u deal ( ie export or import ) only data and objects.
exp/imp tools will not see this as migration.
if the target db has an LMT it will dump data there.
the issues when moving data from dictionary manageed to local managed arises when u go through the 'migration' path using DBMA /ODMA or running manual scripts ( in that case, first u should upgrade to 8.0.6 and then to 9i. 8.0.5 is desupported by oracle)
As per ur first posting 
"Since this is a small app, I guess they did not care as long as "

the database is not complicated ( likewith oracle text/ spatial etc).
considering this.
install new 9i binaries. ( dont create the seed database).
createa customized database
create users and tablespaces ( like the source)
export source and import into target.
this works good ( as long as the db is not complicated. we have completely automated this 'process' of moving data from one our legacy databases (8.0.5 HP-UX) to (9i RAC,sun ) through export/import.)

Re: Upgrade from 8.0.5 to 9i : ReDesign [message #59784 is a reply to message #59748] Mon, 22 December 2003 05:19 Go to previous messageGo to next message
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 Go to previous message
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)
 

Previous Topic: Moving Index Organized Table in 8.0.5
Next Topic: table partition
Goto Forum:
  


Current Time: Mon Jan 06 18:37:16 CST 2025