EXPERT RESPONSE
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:
- Query the Data Dictionary to determine the current locations of your control files:
SELECT name FROM v$controlfile;
- Query the Data Dictionary to determine the current locations of your data files:
SELECT name FROM v$datafile;
- Query the Data Dictionary to determine the current locations of your online redo logs:
SELECT member FROM v$logfile;
- Keep the information from above as it will be important in later steps.
Move the files:
- Modify the parameter file with the new location of your control files.
- Perform a clean shutdown of the database (SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE).
- 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.
- Start up the database in MOUNT mode:
STARTUP MOUNT
- 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';
- Repeat the previous step for each and every online redo log that is moved.
- Tell the control files of the new data file locations:
ALTER DATABASE RENAME FILE
'/old_dir/file01.dbf' TO '/new_dir/file01.dbf';
- Repeat the previous step for each and every data file that is moved.
Finish up:
- Open the database.
ALTER DATABASE OPEN;
- 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.
|
