Solved

Oracle Server data move

Posted on 2014-03-17
19
539 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 34

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 36

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
 

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 34

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 36

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 34

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 34

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 34

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 34

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 34

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

17 Experts available now in Live!

Get 1:1 Help Now