Migratting 11G Data files from 1 disk to another

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.
GNOVAKAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
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
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
GNOVAKAuthor Commented:
thanks Everyone
0
 
GNOVAKAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
GNOVAKAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
GNOVAKAuthor Commented:
(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
 
Steve WalesSenior Database AdministratorCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
GNOVAKAuthor Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
>>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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.