Solved

Oracle Server data move

Posted on 2014-03-17
19
549 Views
Last Modified: 2014-03-18
An Oracle server is going to be reimaged and I need to store data off the server before reimaging.  looking for best ways to get copies of all data.  Data has to be restored back to the server after the reimaging.
0
Comment
Question by:Nana Hemaa
  • 8
  • 7
  • 2
  • +1
19 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39935069
There are multiple options, each with different advantages and disadvantages.

The simplest option would be to shut down the database, then simply copy all of the Oracle database files: on-line redo logs, controlfiles, init file, and data files (tablespaces) to alternate (maybe networked) disk locations.

Another option (if your database is in archivelog mode, as most production databases should be) is to take a "hot" backup using either Oracle's RMAN utility, or third-party backup software that is Oracle-aware, or manual files copies *IF* you first put each tablespace into "backup" mode.  This option requires less downtime than the first option.  But, it requires the same (or about the same) amount of alternate disk space as the first option.

A third option would be to do a full database export.  This may require much less alternate disk space than either of the two backup options above.  But, this option requires you to do a "create database" after the server re-image based on the Oracle software which must either be re-imaged along with the O/S software or re-installed after the O/S re-image, and you must then run a full import of the exported "dump" file.  This usually takes longer (2x -20X, depending on the number of indexes, foreign keys, PL\SQL objects, etc. in the database) than doing a restore from a backup.  But, it can give you dramatically better performance (after this process is finished) than a restore from a backup.  This depends on whether the application does many deletes or updates of rows that add a lot of bytes per record.

If you choose to do an export, rebuild and import, I recommend that you also do one of the first two options just to be safe.

To summarize:
Option #1 is likely the simplest and cheapest, and requires a medium amount of downtime.

Option #2 will require the least amount of downtime.

Option #3 requires the most downtime, but may give you dramatically-better performance after the import finishes.
0
 

Author Comment

by:Nana Hemaa
ID: 39935350
Thanks..With option 1..can I use RMAN to restore after the reeimaging?  I seem to like option 1.  will copy the actual data files to another server.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39935389
reimaged ????

if a database has to be moved use a physical standby with little downtime
> it can even be converted with rman to a different os and file system while doing that
it's even possible to see if the database is still working after the move the first time.

you could limit the downtime to a few minutes with this, back and forth

http://docs.oracle.com/cd/E11882_01/server.112/e41134/rcmbackp.htm#SBYDB01500
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Nana Hemaa
ID: 39935412
yes reimage??  can you give me step by step instructions  before and after?  want to make sure I do not miss anything
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39935438
"With option 1, can I use RMAN to restore after the re-imaging?".  Yes, if you use RMAN to do the backup.  Option 1 involves shutting the database down first, which is required if your database is *NOT* running in archivelog mode.  If you find RMAN easier (or safer) than just doing manual file copies, yes, you can use RMAN for that.  I find manual file copies to be easy enough.  

To do this manually, you do need to identify all of the files that you need.  And, depending on who did the original database install and setup for you and who may have added data, log or controlfiles later, these could be anywhere on your server now.  You can run queries in Oracle to identify all of these files.

If you've used RMAN before, then using RMAN for this is may be your easiest option.  Just be careful of where RMAN stores its data.  Is that in a separate reposittory database?  Or, is that just in the controlfiles for your current database?  If that is true, you will need to restore the controlfiles first, and that may be easier manually than with RMAN.
0
 

Author Comment

by:Nana Hemaa
ID: 39935441
OS is not changing.  Just adding more physical drives  and splitting data files, archived files etc on different drives.  In the process of copying the files to a different server just not sure how to bring it back . do this in SQl easily by attaching files not sure how in Oracle
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39935457
Go with markgeer's option#1
And . . . DO NOT FORGET the Oracle binaries! Use the instructions on "cloning" the Oracle Home to back it up.
And when you restore the Oracle home you need to "re-link" the binaries.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39935461
PS: if "OS is not changing" then it is NOT called re-imaging. Re-imaging implies the OS binaries change.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 39935580
adding more physical drives ...
technically only a database shutdown is required, shutdown the server, attach the drives
then start the database again

you can add / move tablespaces by taking them offline, moving them at os level, renaming at database level and bringing them back online

a tablespace is a collection for a set of datafiles
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39936855
If you are actually just adding new physical drives, you don't need to shut Oracle down at all.  You can change the location of the archived redo log files for Oracle on-the-fly without doing a database shutdown!  You can also change the location of on-line redo log files without a database shutdown.  And, you can even change the lcoation of Oracle data (tablespace) files without a full Oracle shutdown,  But, you will have to take the affected tablespace offline, and this will likely mean some application downtime.  You cannot however change the location of the SYSTEM tablespace file without doing an Oracle database shutdown.

But, this question seems to indicate that you may not have a very good backup strategy in place for Oracle, or that you are not very familiar with the processes of backing up, recovering or moving Oracle database files.  That may be a bigger issue.
0
 

Author Comment

by:Nana Hemaa
ID: 39937080
to make things clear.  We are reinstalling OS etc on  different drives and we are reinstalling Oracle.  so we have to move data to a different server(server B).  we copied all data to server B already.    Just want to know  how to bring database files back from serverB back to ServerA and get Oracle database operational again.    
FYI--this is currently a dev server which will become a production server later on.  so downtime is not a big issue here.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39937177
The easiest way to get your database running on server B is if you can copy all of the Oracle database files (that is: the on-line redo log files, the control files plus the "data" files which are the tablespace files) to the same disk location they had on server A.  Then if your init file is in the Oracle_home, you simply start the database, and it works.  

If some files are in different locations, you will have to to tell Oracle where those new locations are.  You would have to give us the details of the old and new file names and disk locations if you need us to help you do that.
0
 

Author Comment

by:Nana Hemaa
ID: 39937428
Server A
Currently has 2 physical drives
First physical drive
C  : os  +   Control File 1

2nd Physical Drive is partitioned into E and F

E: has all the .DBF  files, all Redo Logs  + Control File 2 --in Ora Folder (6 redo logs , 5 tablespaces, temp, etc) ,flash recovery area, oracle Install

F:  Archived Logs  , Control File 3

so currently all files on E and F(Server A above)  have been copied to Server B ( a file server)


data will have to be copied  back from file server(Server B) to Server A after more drives are added Raid changes  etc to look like this
C:  Os
E: All .DBF files , Control File1 , Redo logs 1 and  2, Oracle install
F: Archived Logs , Control File 2, Redo Logs 3 and 4
G:  Flash Recovery Area, Control File 3 , Redo Logs 5 and 6


NB.  Also looking at the setup will it be  better to put Oracle install on the C drive or keep it on E.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 39937515
I don't like seeing some on-line redo logs and the archived redo logs on the same disk (F:).  Also, the default for Oracle now is to put the archived redo logs in the Flash Recovery Area.  You don't have to, but that is the default.

Whether the Oracle install is on drive C: or E: likely won't make much difference.  

Will these drives all have the same RAID level?  Different parts of an Oracle database are much better suited to one RAID level than another.

In an ideal world, I would set up a Windows-based Oracle server something like this:

C: RAID1 - O/S (but not the swapfile or temp directory); Oracle undo tablespace, one copy of each on-line redo log, one of the controlfiles
E: RAID5 - Oracle executables, Oracle data files
F: RAID0 or non-RAID - Oracle temp tablespace, Archived Logs, exports, application log files, Windows swap file and Windows temporary directory
G: RAID1 - Flash Recovery Area, other Control Files, the second copy of each on-line redo log
0
 

Author Comment

by:Nana Hemaa
ID: 39937610
thanks I like your distribution of files . We have 16 disks  4 * 146 gigs (for c drive )  and remaining 12  are 900 gigs each so we were going to do
c: Raid 10  
E: Raid 10    
F:Raid 1    
G:Raid 5

but we can change  the setup above if necessary
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39937673
If RAID10 gives you only 50% usable space, I would say that drive E: should be RAID5, Assuming that contains four drives, that would give you 75% usable space.  The Oracle data files and the Oracle executables typically see a lot more reads than writes and RAID5 is much better reads than for writes.  RAID1 or RAID10 are better for writes than reads, so the parts of an Oracle system that I suggested for drive F: work much better on RAID1 than on RAID5.
0
 

Author Comment

by:Nana Hemaa
ID: 39937734
thanks a lot..you have been helpful.  now need to up my knowledge on how to bring the files (back from the file server) to Server A.  I can copy the files to the specified locations as instructed above.  How do I tell Oracle where the files are on the new server
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 39937756
That depends on which files are in a different location on the new server.

For controlfiles, you need to do this in the init (or spfile) before you start the database.

For on-line redo log files, and/or data (tablespace) files you need the Oracle instance started, but without opening the database, then you can do "alter database rename file [old_location] to [new_location]" commands for each file that is in a different location now.  After each file is accounted for, you can then open the database.
0
 

Author Closing Comment

by:Nana Hemaa
ID: 39937945
Thanks Markgeer
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 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