Solved

Migratting 11G Data files from 1 disk to another

Posted on 2014-01-21
14
479 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
ID: 39798099
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
ID: 39798170
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
ID: 39798188
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
ID: 39847230
thanks Everyone
0
 

Author Comment

by:GNOVAK
ID: 39847456
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
ID: 39847564
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
ID: 39847724
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
ID: 39847737
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
ID: 39847768
(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
ID: 39847858
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
ID: 39847865
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
ID: 39847868
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
ID: 39848181
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
ID: 39848245
>>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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
PL/SQL Search for multiple strings 5 39
Schema creation in Oracle12c 6 39
Oracle - SQL Where clause causing Invalid Number Error 4 26
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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

911 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

18 Experts available now in Live!

Get 1:1 Help Now