Q

Moving Oracle folder to external hard drive

I have PeopleSoft installed locally on my laptop and it uses Oracle 9i as its database (also local on the C drive). I want to move the oradata folder to my external hard drive to free up space and leave everything else. How can I accomplish this?

I have PeopleSoft installed locally on my laptop and it uses Oracle 9i as its database (also local on the C drive). I understand that all data is under c:\oracle\oradata\FSDMO but I want to be able to move this data (or folder) to my external hard drive (H) to free up space. I'm very sure that a simple cut and paste will not work since there must be some pointer files somewhere that point to this C:\...\FSDMO location. To further clarify, if I navigate to C:\oracle, I find three folders: admin, ora92 and oradata. I just want to move this oradata folder to my external hard drive and leave everything else. How can I accomplish this? Thanks in advance.
The "pointers" you are referring to are the database's control files. When an Oracle database is started, it first reads the database's parameter file. In Windows, the service used to start the database has the information for the location of this parameter file. The parameter file contains a parameter called CONTROL_FILES which points to all of the database's control files. The control files contain "pointers" to all of the online redo logs and the database datafiles. You cannot simply move the files and expect everything to work. You have to let Oracle know where everything is moved to. Follow this step-by-step guide to move your control files, online redo logs and database datafiles.

Gather information:

  1. Query the Data Dictionary to determine the current locations of your control files:
         SELECT name FROM v$controlfile;
    
  2. Query the Data Dictionary to determine the current locations of your data files:
         SELECT name FROM v$datafile;
    
  3. Query the Data Dictionary to determine the current locations of your online redo logs:
        SELECT member FROM v$logfile;
    
  4. Keep the information from above as it will be important in later steps.

Move the files:

  1. Modify the parameter file with the new location of your control files.
    • If you are using the old style PFILE, simply modify the CONTROL_FILES parameter with a text editor.
    • If you are using the new sytle SPFILE, modify the CONTROL_FILES parameter with this command:
            ALTER SYSTEM SET control_files=
            ('/new_dir/control01.ctl','/new_dir/control02.ctl',....) 
            SCOPE=spfile; 
      
  2. Perform a clean shutdown of the database (SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE).
  3. Copy the control files, online redo logs and data files with OS commands. In Unix/Linux, we typically use the 'cp' command. In Windows, you can use the 'copy' command or Windows Explorer.
  4. Start up the database in MOUNT mode:
        STARTUP MOUNT
    
  5. Tell the control files of the new online redo log file locations:
        ALTER DATABASE RENAME FILE '/old_dir/redo01a.log' 
        TO '/new_dir/redo01a.log';
    
  6. Repeat the previous step for each and every online redo log that is moved.
  7. Tell the control files of the new data file locations:
        ALTER DATABASE RENAME FILE
        '/old_dir/file01.dbf' TO '/new_dir/file01.dbf';
    
  8. Repeat the previous step for each and every data file that is moved.

Finish up:

  1. Open the database.
         ALTER DATABASE OPEN;
    
  2. Shut down the database and take a backup after all of your hard work.

Step 1 under "Move the files" above changes the pointer of the control file locations so that the control files can be found in step 3. The remaining steps change the pointers in the control files to the new online redo log and datafile locations. Once all of the pointers have been changed, the database can be opened. I cannot stress strongly enough that a backup should be taken at this point. It will be very, very difficult to recover through this operation.

One other note -- sometimes, a DBA wants to change the file's name but not necessarily the location. In Oracle's world, the file's name is the combination of the directory path and the actual file name. So '/dir1/file1.dbf' and '/dir2/file1.dbf' are two distinctly different files. So the process to change the file's name is similar to the above. The ALTER DATABASE RENAME file is used to change the location and the name.

This was first published in July 2006

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close