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

Products

• Data Loss Prevention Enterprise Suite
• Data Loss Prevention
• Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

You are looking to migrate the Oracle database 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.

 

To update the control files, the above might not work for 19c.  For this you can follow the below process to move control files to new location.

How to move control files:

 

1. connect to sqlplus as sysdba
2. Shutdown the instance.  Type "shutdown" in sqlplus
3. Backup the database folder under %ORACLE_HOME%\database.  For example: 'C:\Oracle\Product\19.3.0.0\db_1\database.'
4. Create pfile from existing spfile.  Type "create pfile from spfile;" in sqlplus
5. Modify and update control location from '%ORACLE_HOME%\database\INITPROTECT.ORA'
6. Save INITPROTECT.ORA with modifications.
7. Create spfile from updated pfile.  type "create spfile from pfile;" in sqlplus
8. Startup db in no mount mode and verify control file location.
   
   1. "startup nomount;"
   2. "show parameter control_files;"  <-- verify spfile location and make sure its updated.
   3. "alter database mount;"
   4. "alter database open;"

You should have a working db at this time with control files in new location.