Solved

Migratting 11G Data files from 1 disk to another

Posted on 2014-01-21
14
475 Views
Last Modified: 2014-02-10
We have an Oracle server running on Window Server 2008R2.
We have a D:\oradata directory containing the Archive, control, datafiles, online log directories. About 800G

We need to move everything off of this directory to an E: drive.
I dont wish to mess this up and I dont think its as easy as just copying the files over.

Any help would be appreciated.
0
Comment
Question by:GNOVAK
  • 6
  • 5
  • 3
14 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 350 total points
Comment Utility
Actually, it's simpler than you might think.

First: Take a backup!

Get a text version of your controlfile: alter database backup controlfile to trace
Get a text version of your spfile: create pfile='c:\somedir\initdb.ora' from spfile

Shut  your database down.

Edit your pfile to point to where you want your control files to be.
Edit your create controlfile statement to refer to the new datafiles and logfile locations.

Copy over your datafiles.  You don't need the log and controlfiles, they'll be recreated.

(If you're copying everything over you should still have all your original files available on D: if something goes wrong - but still, that's what the backup is for as well - can't have too much insurance).

Do a startup nomount pfile=c:\somedir\initdb.ora

Run the create controlfile script.

Recreate your TEMP tablespace (should be in the script though).

Open your database with resetlogs

Recreate your spfile.

Lastly: Take another backup!

There may be other ways to do it, but I've done it this way in the past and it's worked just fine.

The create controlfile script generated by backup controlfile has 2 copies of the script in it that are subtly different.

Depending on how your current database is set up, assuming you're in archivelog mode, you'll want to be sure to specify ARCHIVELOG RESETLOGS in the create controlfile statement.

If you're not 100% sure, try it out with a test database first (I certainly would, just to make sure I have it all working correctly before I went and played with my production DB).
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 150 total points
Comment Utility
I would not recreate the control file and do a reset logs.  It is not necessary and it will invalidate your previous backups as you cannot recover through a reset logs.

These steps are off the top of my head, so try it somewhere first, but this should be close:

Shut down the database
Copy the files to the new location
STARTUP NOMOUNT
Change control file location in spfile with alter system
ALTER DATABASE MOUNT;
Issue ALTER DATABASE RENAME FILE for every datafile and log file in the system
ALTER DATABASE OPEN;
Verify locations in DBA_LOG_FILES and DBA_DATA_FILES

This assumes that you are not moving the software, only the data files.
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 350 total points
Comment Utility
Did a little digging and found a lovely article on this at oracle-base that covers how to move data files, control files and log files:

http://www.oracle-base.com/articles/misc/renaming-or-moving-oracle-files.php

Johnsone is correct on the invalidating of backups etc.  I was being lazy and going from memory and not fully researching the best way to perform such a move.
0
 

Author Closing Comment

by:GNOVAK
Comment Utility
thanks Everyone
0
 

Author Comment

by:GNOVAK
Comment Utility
Steve /johnsone
Looking at the Oracle article, it seems that Oracle could do alot of the heavy lifting - could someone comment on the below proposed solution:

Steps to move data files

1) Make note of the following:
   CONTROL FILES
         select name from v$controlfile;
         This gives two files:
         D:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJZ.CTL
         D:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJS.CTL
         
   LOG FILES
    SELECT member FROM v$logfile;
    This gives 4 files:
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZNH_.LOG
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_2_8TWJKZNH_.LOG
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_3_8TWJKZNH_.LOG
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZOG_.LOG
   
2) Alter the contol files:
SQL> ALTER SYSTEM SET control_files='E:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJZ.CTL', -
> 'E:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJS.CTL' SCOPE=SPFILE;

3)ShutDown database
 SQL> SHUTDOWN IMMEDIATE
 
4)Use HOST MOVE ON ALL FILES - (the same order as above? Control, then log, then data?)
-- use this to move Control files?
SQL>      HOST MOVE D:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJZ.CTL E:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJZ.CTL
SQL>  HOST MOVE D:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJZ.CTL E:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJS.CTL


--Use this to move Logs?      
SQL>      HOST MOVE D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZNH_.LOG E:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZNH_.LOG
SQL>       HOST MOVE D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_2_8TWJKZNH_.LOG E:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_2_8TWJKZNH_.LOG
SQL>       HOST MOVE D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_3_8TWJKZNH_.LOG E:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_3_8TWJKZNH_.LOG
SQL>       HOST MOVE D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZOG_.LOG E:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZOG_.LOG


--Use this to move ALL data files (user, system, temp, tables, etc)?
SQL>  HOST MOVE d:\ORADATA\<MyDBF>\O1_MF_SYSAUX_8TWJL3CZ_.dbf       E:\ORADATA\<MyDBF>\O1_MF_SYSAUX_8TWJL3CZ_.dbf
SQL>  HOST MOVE d:\ORADATA\<MyDBF>\O1_MF_SYSTEM_8TWJL01F_.DBF       E:\ORADATA\<MyDBF>\O1_MF_SYSTEM_8TWJL01F_.DBF
SQL>  HOST MOVE d:\ORADATA\<MyDBF>\O1_MF_TEMP_8TWJL5WG_.TMP         E:\ORADATA\<MyDBF>\O1_MF_TEMP_8TWJL5WG_.TMP
SQL>  HOST MOVE d:\ORADATA\<MyDBF>\O1_MF_UNDOTBS1_8TWJL5JR_.DBF E:\ORADATA\<MyDBF>\O1_MF_UNDOTBS1_8TWJL5JR_.DBF
SQL>  HOST MOVE d:\ORADATA\<MyDBF>\O1_MF_USERS_8TWJLDB2_.DBF             E:\ORADATA\<MyDBF>\O1_MF_USERS_8TWJLDB2_.DBF
SQL>  HOST MOVE d:\ORADATA\<MyDBF>\<MY_TABLE_FILES_1>                                    E:\ORADATA\<MyDBF>\<MY_TABLE_FILES_1>
SQL>  HOST MOVE d:\ORADATA\<MyDBF>\<MY_TABLE_FILES_2>                               E:\ORADATA\<MyDBF>\<MY_TABLE_FILES_2>

5) STARTUP MOUNT


Is the above correct?
I can use HOST MOVE to move everything?

Do I have to be concerned with the ARCHIVE directory?
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
I am assuming you are talking about running in SQL*Plus.

The HOST command in SQL*Plus (doc here -> http://docs.oracle.com/cd/E16655_01/server.121/e18404/ch_twelve026.htm#i2676105) is nothing more the a call out to a shell that does the move.  Oracle isn't doing anything at all for you here.  SQL*Plus is spawning a shell and executing the move command.  You would still be required to do all the ALTER DATABASE commands to rename the files in the control file.

You would only need to be concerned with the archive directory if you are moving it.
0
 

Author Comment

by:GNOVAK
Comment Utility
OK - so it's probably just as easy to move the subdirectories using the OS.
If I move these files, where/how do I create new ctl files?  Do I even need the old ones?

 So the newly revised process is:



1) Make note of the following:
   CONTROL FILES
         select name from v$controlfile;
         This gives two files:
         D:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJZ.CTL
         D:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJS.CTL
         
   LOG FILES
    SELECT member FROM v$logfile;
    This gives 4 files:
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZNH_.LOG
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_2_8TWJKZNH_.LOG
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_3_8TWJKZNH_.LOG
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZOG_.LOG
   
? 2) Alter the contol files:
SQL> ALTER SYSTEM SET control_files='E:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJZ.CTL', -
> 'E:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJS.CTL' SCOPE=SPFILE;

3)ShutDown database
 SQL> SHUTDOWN IMMEDIATE
 
4)Use OS to move FILES -

MOVE D:\ORADATA\<MyDBF>\ONLINELOG to E:
MOVE d:\ORADATA\<MyDBF>\DATAFILE to E:


5) STARTUP MOUNT

6) for each log file:
 ALTER DATABASE RENAME FILE 'D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZNH_.LOG'  TO 'E:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZNH_.LOG';
 
7) for each data file:
      ALTER DATABASE RENAME FILE 'd:\ORADATA\<MyDBF>\O1_MF_SYSAUX_8TWJL3CZ_.dbf'  TO 'e:\ORADATA\<MyDBF>\O1_MF_SYSAUX_8TWJL3CZ_.dbf';

8) ALTER DATABASE OPEN;
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.

 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
You don't need to create new controlfiles.  You're mixing up instructions from my first reply with the later ones.

You're moving your existing controlfiles so you're not actually recreating anything.

Step 3.5 would be to move your controlfiles at the OS level from the old to the new location.
0
 

Author Comment

by:GNOVAK
Comment Utility
(sorry to be such a pain)
I guess I dont understand where the CTL and spfiles are utilized.
When I look at the CTL files (and the spfile) they refer to "D:\"
If I just move the CTL files and the datafiles, log files to E:\, the control files will be wrong, wont they?
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
When you issue ALTER DATABASE RENAME .... it updates the controlfiles with the location of your new data files and log files.

Controlfiles contains (among other things) the location of your data files and log files.
SPFILE is a binary representation of your init.ora file with your start up parameters.

alter system set control_files updates the SPFILE.

You then shutdown the database.

Issue STARTUP MOUNT.

This reads the spfile for instance initialization parameters (where is my controlfile, what are my memory parameters) - this happens during the first of three phases of Oracle startup.

Second phase is MOUNT.  During database mount, the control file is opened.  It reads the locations of the datafiles but does not try to open them.

Then you tell the database (with the RENAME statements) that your datafiles are now "over there".

Once you have all that done, you issue ALTER DATABASE OPEN and during the third and final stage of database startup, the data files and log files are opened.

That make things clearer ?
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
You need the alter system set control_files command.  That tells Oracle where the files are located.  All of the other file path names are stored within the control file and are changed when you do the alter database rename commands.
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
Overview of instance startup and shutdown is in the documentation here: http://docs.oracle.com/cd/E11882_01/server.112/e40540/startup.htm#CNCPT955
0
 

Author Comment

by:GNOVAK
Comment Utility
Thanks Steve - Excellent  explanation.

The fact that the control files dont point to the right data files to begin with is not a big deal as long as I use the ALTER DATABASE RENAME command followed by the ALTER DATABASE OPEN.  Correct?

So the process should be:
Steps to move data files

1) Make note of the following:
   CONTROL FILES
         select name from v$controlfile;
         This gives two files:
         D:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJZ.CTL
         D:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJS.CTL
         
   LOG FILES
    SELECT member FROM v$logfile;
    This gives 4 files:
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZNH_.LOG
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_2_8TWJKZNH_.LOG
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_3_8TWJKZNH_.LOG
    D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZOG_.LOG
   
 2) Alter the contol files location in the spfile:
SQL> ALTER SYSTEM SET control_files='E:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJZ.CTL', -
> 'E:\ORADATA\<MyDBF>\CONTROLFILE\01_WDGSHSJJJS.CTL' SCOPE=SPFILE;

3)ShutDown database
 SQL> SHUTDOWN IMMEDIATE
 
4)Use OS to move FILES -

MOVE D:\ORADATA\<MyDBF>\ONLINELOG to E:
MOVE d:\ORADATA\<MyDBF>\DATAFILE to E:


5) STARTUP MOUNT

6) for each log file:
 ALTER DATABASE RENAME FILE 'D:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZNH_.LOG'  TO 'E:\ORADATA\<MyDBF>\ONLINELOG\O1_MF_1_8TWJKZNH_.LOG';
 
7) for each data file:
      ALTER DATABASE RENAME FILE 'd:\ORADATA\<MyDBF>\O1_MF_SYSAUX_8TWJL3CZ_.dbf'  TO 'e:\ORADATA\<MyDBF>\O1_MF_SYSAUX_8TWJL3CZ_.dbf';

8) ALTER DATABASE OPEN;
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
>>The fact that the control files dont point to the right data files to begin with is not a big deal
>>as long as I use the ALTER DATABASE RENAME command followed by the ALTER DATABASE
>>OPEN.  Correct?

Correct.  Database does not attempt to open the data / log files until the database is actually opened.

You still seem to be missing the step to move the controlfiles to the new location as step 3.5 - in step 4 you're moving data files and logs, not seeing controlfiles.
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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

763 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

8 Experts available now in Live!

Get 1:1 Help Now