How to Relocate Oracle datafiles


From time to time it is necessary to move the datafile location for Oracle.
SQL> startup mount;
SQL> alter database backup controlfile to trace;
SQL> shutdown immediate;


This will dump the control file to a trace file located in the UDUMP destination directory. The tracefile will contain commands to recreate the control file with or without reset logs. Edit this file to include the new location of the datafiles then move the datafiles to that location. The SQL will look something like:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "IGEN10G" NORESETLOGS  NOARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
    MAXLOGFILES 16
    MAXLOGMEMBERS 2
    MAXDATAFILES 30
    MAXINSTANCES 4
    MAXLOGHISTORY 453
LOGFILE
  GROUP 1 '/log/igen10g/log01.dbf'  SIZE 2000M,
  GROUP 2 '/log/igen10g/log02.dbf'  SIZE 2000M
-- STANDBY LOGFILE
DATAFILE
  '/data1/igen10g/sys01.dbf',
  '/data1/igen10g/undo01.dbf',
  '/data1/igen10g/sysaux.dbf',
  '/data1/igen/data01.dbf',
  '/data2/igen/data02.dbf',
  '/data2/igen/index01.dbf'
CHARACTER SET US7ASCII
;

Now you should be able to start the database as always.

Comments:

Post a Comment:
Comments are closed for this entry.
About

This blog discusses performance topics as running on Sun servers. The main focus is in database performance and architecture but other topics can and will creep in.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
News

No bookmarks in folder

Blogroll

No bookmarks in folder