How to migrate oracle to a new directory structure when moving to a new server

Products

• Data Loss Prevention Enterprise Suite

Issue/Introduction

You are looking to migrate oracle to a new directory structure when moving oracle to a new server.

Resolution

When you are moving the database files but to a different folder structure, you need to first trace where the files are located and where they are going.  For this example, let's assume the following:

Old server db path: C:\Oracle\oradata\PROTECT

New server db path: E:\Oracle\oradata\PROTECT 

 

So here we are just looking to move the database but on the E drive instead of the C drive on the new server.  

The process to move the files is easy as you first stop all Oracle services on both servers and then copy the files from the old server onto the new server in the new location.  In this case the E drive.

Next you need to first run the following and find out where the files are pointing to.

1. On the new Oracle server after the db as been copied over.  Start the Oracle services on the new server.  The service should start but db will not be mounted.

2. Login to sqlplus as sysdba.  ('sqlplus /nolog' and then 'connect sys as sysdba')

3. Next we need to figure out exactly where each file is pointing to.  Run the below commands one at a time and take note of each file location.  So copy the output into notepad to make this easier.

• • • SELECT name FROM v$datafile;
    • SELECT member FROM v$logfile;
    • SELECT name from v$controlfile;
    • SELECT name FROM v$tempfile;

 

4. The above output should still show the files being pointed to the C drive even though the files actually live on the E drive.

5. Next we need to update this path for each file using the below command.  You will need to update this command according to your environment.

• • • ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PROTECT\SYSTEM01.DBF' TO 'E:\ORACLE\ORADATA\PROTECT\SYSTEM01.DBF';

 

6. Repeat step 5 until all files have been moved from C drive to E drive.  Once completed run the steps from step 3 again and verify they all look good.

7. Once complete just mount the database or restart oracle services.