An Oracle blog – Amardeep Sidhu

July 21, 2007

Moving datafiles,control files and log files – Part 1

Filed under: Oracle Tips — Sidhu @ 1:44 pm
Tags: ,

Many times you need to move datafiles from one location to another. The simplest approach for this is to take the tablespace offline, copy the datafiles to new location, rename the files with alter database rename file (Except that you dont have to move the SYSTEM and UNDO tablespace, as you can’t take SYSTEM tablespace offline)

[email protected] AS SYSDBA> alter tablespace system offline;
alter tablespace system offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary

[email protected] AS SYSDBA>

Well lets try moving USERS tablespace.

[email protected] AS SYSDBA> column file_name format a50

[email protected] AS SYSDBA> set lines 100
[email protected] AS SYSDBA> select file_name,tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORCL\USERS01.DBF                         USERS

[email protected] AS SYSDBA>

The current location of the datafile is C:\ORACLE\ORCL\. Suppose I have to move it to c:\oracle\oradata. So first lets take the tablespace offline

[email protected] AS SYSDBA> alter tablespace users offline;

Tablespace altered.

[email protected] AS SYSDBA>

Now copy the datafile to new location [Note the new directory should be already created]

C:\oracle\ORCL>copy USERS01.DBF c:\oracle\oradata
1 file(s) copied.

C:\oracle\ORCL>

Now make database aware of the new location of the datafile using alter database rename file:

[email protected] AS SYSDBA> alter database rename file 'c:\oracle\orcl\users01.dbf' to 'c:\oracle\oradata\users01.dbf';

Database altered.

[email protected] AS SYSDBA>

The last thing is to bring the tablespace online. If everything has gone rightly the message like this will appear and you can view the new location of the datafile.

[email protected] AS SYSDBA> alter tablespace users online;

Tablespace altered.

[email protected] AS SYSDBA> select file_name,tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
C:\ORACLE\ORADATA\USERS01.DBF                      USERS

[email protected] AS SYSDBA>

In next post we will discuss moving datafiles, controlfiles and logfiles.

Theme: Rubric. Get a free blog at WordPress.com

%d bloggers like this: