Solved

Database Migration Question

Posted on 2006-11-12
23
929 Views
Last Modified: 2007-12-19
Hi Folks,

We have a 3.5TB data warehouse running on 9.2.0.6 and are now planning to migrate to a two node Oracle 10gR2 RAC with ASM (New Storage). The migration strategy we have in mind is:

1) Upgrade current 9i single instance database to 10gR2 single instance database.
2) Migrate 10g single instance database to a 10gR2 dual node RAC with ASM using either
    (i) RMAN duplicate command
    (ii) Data pump.

Now what I would like to ask is can some please highlight the pros and cons of above mentioned migration methods.
0
Comment
Question by:aamermushtaq
  • 9
  • 6
  • 5
  • +2
23 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17928092
I hope that before doing any change on the production, you first do some test on a test instance? !!!
because that is the ONLY way to validate for 99.99% the way you are doing, and that you will not have to pass a sleepless night with this.

now, if you already take into account a data pump, why first update the existing database into a 10g. you could data pump directly from 9i?
0
 
LVL 47

Accepted Solution

by:
schwertner earned 250 total points
ID: 17928659
Data Pump works only on the server side (in contrast to Import/Export).
Also Data Pump works unstable prior 10g R2 - many Experts do not agree, but I had many troubles.
The keypoint is to migrate to 10g R2. This is a hard task.
Export/Import is the most reliable way
but by this volume of the DB will work slowly.
If you delete temporary the indexes there is a chance to
speed up the process.
Altenative way is to use the "Upgrade Assistant" of Oracle (there is a .pdf book
that explains all steps. I fear that this way is risky, so wait for the opinions of
the other Experts that possibly has don upgrade with so big DB.

You have to backup the DB vefore doing such major changes.
You have to keep on your desk a rescue strategy -
your responsibility is extremelly high and have to be prepared
for every case of failure.

Upgrading the 10g to 10g R2 do not forget to upgrade also the Catalog
(catupgr.sql script). This mistake is very common.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 17928893
Hi ,
 i m in process of doing a migration of 1 TB database for a my client. And i m throught with that today...

 The steps i followed ,
 
 1.Upgraded my single instance 9i database to single instance 10r2.( the case is already the database is running in Itanium machine ,So its not a cross platform)
 2.Installed two node cluster in HP-UX 8640,Hardware cluster.
 3.Created ASM instance in both the machine usng DBCA ( Go till 9/15 step in DBCA will create the ASM instance in both the nodes )
 4.Taken a RMAN backup using a catalog database ( catalog databse should be the same version of the auxiallry datase note : i have found a problem with it becaue of version mismatch ).
 5.already you hav created a ASM Instance with diskgroup reated.
 6.What i did is created a single instance database in the first node having the ASM diskgroup as the path of database files and i restored the databse using RMAN.
 
 Go thro the following metalink note : 382669.1 ( it really helped me a lot )

 7.Then i added all the cluster parameters and started both the nodes in cluster .

 Imp steps to be followed :
 
 a.In case if you are getting any error while Crs installed clean it properly using the metalink note : 239998.1( else it will lead to many isses )
b. as you are resotring with a single instance RMAN backup you need to recreate the control file once after the restore gets over in the first node to enable the thread 2 parameter.

c.Then create log groups and undo tbs for the second isntance and make the second isntance to come up.

d.As you are doing the db creation part manually,you have to register the instance and listeners with crs to make it come up when crs comes up.
 
 
0
 
LVL 13

Assisted Solution

by:anand_2000v
anand_2000v earned 250 total points
ID: 17928915
What i did is a Testing for my production.It works out 100%
0
 

Author Comment

by:aamermushtaq
ID: 17936348
Thanks guys for all your suggestions especially Anand. I wanted to have a comparison between Data pump and RMAN particularly keeping performance in mind. Here are some of the things I can think of at the moment:

RMAN Approach:
Pros:
1) Backup and restore operation can be parallelized.
2) Can skip tablespaces while restoring from the backup to minimize the restoration window. These tablespaces could be of indexes and Materialized Views.
3) Production database would be down during the backup window. Once the backup has been taken production database can be brought online for normal operation (batch execution/user access). Carry on with duplicating the database and eventually execute batch for missing days on the newly created RAC database to get both environments in synch. OR create the duplicate database as a standby and then take an incremental backup of the production and apply it on RAC environment to make them consistent.

Cons:
1) Tablespaces will be created with exactly the same names as the production database and since quiet a few tablespaces are not following the naming convention, we would have to use the rename tablespace clause of 10g to put them right one by one.

Data pump Approach:
Pros:

1) Can apply parallelism to export/ import operations but not sure how the performance would be as compared to RMAN.
2) Can skip objects such as indexes and materialized views.
3) Can be selective about schemas and their objects. Junk objects can be left behind and not taken to the new environment.
4) Can create all tablespaces with proper naming convention and use remap_tablespace clause while importing.
5) Can create new datafiles and make sure that database standards are followed. Data fragmentation, which has built over the years can be addressed during this operation as well.

Cons:
1) More manual work would be required.
2) It is anticipated that the performance of the import operation would be considerably slower.

I would appreciate it if everyone can add more points to the above list and/or provide their expert opinion.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 17936414
I prefer RMAN approach would be the better option,But the only point we should come up will be about the intermiediate data's( the time taken during the restore operation).This makes the job very simple to do the migration.If u take the backup in mount state then as you know the database will be restored till the point without any isses as RMAN makes the job quite easier.

 Data pump operation is also a prefered method.But you should be sure with the time it will take for importing and also for export.As you mentioed it involves huge manual work.Where as in RMAN you can use the db_file_convert ,log_file_convert command and you can point out the new locationwhere RMAN makes the job simpler.Also i don feel Renaming the tablespace will not  be a tuff job.As the amount of data is much huge i would recommed you to go with RMAN with a Compressed backup.



0
 
LVL 47

Expert Comment

by:schwertner
ID: 17936478
RMAN is big and complex product. You have to learn it and also have different scenarios for restoring. These scenarios has to be checked and trained - in the other case if a failure occurs you will be in trouble. RMAN can serve many databases, but needs its own database to keep the repository. RMAN makes very compact backups, it also works incremental.

Data Pump needs not so deep knowledge. It works faster in contrast to Export/Import (uses own API in a server package). This is the reason that it works only on the 10g server machine. Works paralel. Need investigation of the instance, more work. But the simplicity leads to simple control and reliability.

We can state that RMAN is good for daily backup/recovery activities and database maintanence. For a farm of instances...
Data Pump is good for migrations, schema extractions, tablespace manipulations. It is operational tool for DBA activities.
0
 

Author Comment

by:aamermushtaq
ID: 17944754
Thanks guys. Anand, you said, you have been tested the migration process in a test environment. We are looking to setup a development environment as well but not sure what should be the hardware specs. Can you please share your testing environment hardware specs which might guide us on certain areas.

If anyone else has a development environment for RAC then please share your hardware specs for my guidance.

Once again, everyone's comments and suggestions are appreciated.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 17944824
Its a two node cluster of hp rx4640  with hp service guard ( this is for my developement purpose) which handles one more CRM database also.For development Storage will be MSA 1000

my production is going to be in three node hp rx8640 with same Hp service guard,RAM size 64 GB,The storage is EVA 5k
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 17944835
Let me know What is the Hardware you are proceuring ,So i can help you some more.

0
 
LVL 47

Expert Comment

by:schwertner
ID: 17945054
There is an excellent article how to build experimental RAC installation:

http://www.oracle.com/technology/pub/articles/hunter_rac10gr2.html

Build Your Own Oracle RAC 10g Release 2 Cluster on Linux and FireWireby Jeffrey Hunter
Learn how to set up and configure an Oracle RAC 10g Release 2 development cluster for less than US$1,800.
Updated April 2006
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.

 

Author Comment

by:aamermushtaq
ID: 17977415
Our prod environment will have couple of Sun Fire E20k servers with EMC storage but we are looking to test the installation and configuration of Oracle 10gR2 RAC on Sun Solaris 10 before having to so it on the big servers. Do you think the following hardware can be used to just test the install and configuration steps:

(i) Two Pentiumn IV intel machines with Sun solaris 10 for x486.These machines will have 2 GB RAM each. 2 X 1GB NICs in both of these machines.
 
(ii) 1 intel machine with Linux OS. It will have 400GB disks with NFS. This will act as a shared storage.(NAS)

(iii) 2 X 1GB Switches for interconnect and connection to NAS.

I am not too sure about concurrent login to the NFS? Will it support that and if not then how can we make it happen. Highlight any problems you can think of with the above mentioned hardware. Remember this is a throw away environment !!!
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17977559
I think the answer is Yes.
The main issue is the Interconnection.
You have very fast NIC cards, faster then Firewire.
This is corner stone in building RAC and NAS.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 17985632
yes the above mentioned configuration will work out
0
 

Author Comment

by:aamermushtaq
ID: 17993792
Thanks guys. There is one more point, which is on my mind and I would like to have a little discussion on that too. The area of concern is ASM diskgroup layout. Since we have a Data warehouse of over 3TB and there are about 7 data marts, which make up for most of this data. Now the question is should we create seperate diskgroups for each of the data marts or should we create one big diskgroup and throw all the data in it. I have been going through some documents/technical papers and the recommendation is to setup as minimum diskgroups as possible and not seperate indexes and MVs into their own diskgroups. I wonder if this recommendation is only true for an OLTP environment? Also consider the case where incase there is a problem with one specific large table within a mart, if we were to put all marts in one diskgroup, wouldnt it have a cascading effect and overall system performance will be degraded?
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 17993950
This is what my concern also,

 Being a dba's point of view,if we have sme 5 to 6 diskgroups it better,so incase if one diskgroups get failed in coming up ,we can go with others.But if we are using a single chunk and throwing all things into it,in case any problem with the diskgroup mounting,what will be the solution  ?
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 17993954
I have been going thro some more documents and will ask oracle recommendations abt this shortly abt this before we do the migration of production .
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17994119
Separation in different disgroups for Datawarehouse application should be the better solution.
This can make the maintenance easier. The possibility to divide the backup in small physical chunks is
very important for you.
Think what happens when the DBA or sysadmin does backup of big volums of data -
all the disks involved in the backup get busy and this will prevent the
normal usage of the DBs.
0
 

Author Comment

by:aamermushtaq
ID: 17996800
Both points are very valid. What about Indexes and MVs. Since DW are heavy on indexes and MVs also take up quiet a bit of space, should we seperate them into their own diskgroups? Wouldnt creating separate diskgroups take away the real benefits of ASM, where it manages I/O across the entire SAN disks.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 18001554
Indexes should be physically placed on separate disk. I mean diveded from the files with tables they are connected.
This means that they have to be placed in different tablespaces.
ASM and SAN are different concepts.

SAN is used as isolated farm of FIBER CHANNEL connected disks. This speeds up the interconnection (because of 2-4 GB transfer speed) and
do not overhelm the LAN when backups of the systems are done.

ASM brings a middletier of physical disks maintanence and isolates the Oracle instances from the events hapen in the
HDD Fearm. E.g. the DBA can change a defect disk without turning off the Oracle instance, files can be moved from disk to disk,
etc.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 18083174
Place the indexes in a seperate chunk not on the same where table located.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20296236
Forced accept.

Computer101
EE Admin
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

Title # Comments Views Activity
Salary Amount Format 13 56
CREATE TABLE syntax 4 45
select query - oracle 16 82
PL/SQL LOOP CURSOR 3 42
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 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

12 Experts available now in Live!

Get 1:1 Help Now