Move or rename Oracle database datafiles to another directory

Jephe Wu - http://linuxtechres.blogspot.com

Objective: move/rename Oracle database datafiles to another directory
Environment: Oracle 11g R2 11.2.0.1.0 64bit on Oracle Linux 5.7 64bit


Steps:

1. shutdown Oracle DB and start it in mounted state
sqlplus / as sysdba
sql> shutdown immediate;
sql> startup mount;
sql> exit;

or
rman target /
rman> shutdown immediate;
rman> startup mount;

2. check the existing datafiles
sqlplus / as sysdba
sql> set line 32000
sql> select file#,name from v$datafile;
  1* select file#,name from v$datafile

     FILE# NAME

1 /home/oracle/devdb2/system01.dbf
2 /home/oracle/devdb2/sysaux01.dbf
3 /home/oracle/devdb2/undotbs01.dbf
4 /home/oracle/devdb2/users01.dbf


SQL> select file#,name from v$tempfile;

     FILE# NAME

2 /home/oracle/app/apache/oradata/devdb/temp01.dbf

SQL> select member from v$logfile;

MEMBER
/home/oracle/app/oracle/oradata/devdb/redo03.log
/home/oracle/app/oracle/oradata/devdb/redo02.log
/home/oracle/app/oracle/oradata/devdb/redo01.log

3. create new datafile directory 
$ mkdir /home/oracle/devdb3

4. use rman copy file to new directory

RMAN> copy datafile 1 to '/home/oracle/devdb3/system01.dbf';

Starting backup at 04-JUN-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/devdb2/system01.dbf
output file name=/home/oracle/devdb3/system1.dbf tag=TAG20120604T120342 RECID=1 STAMP=785073858
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 04-JUN-12

RMAN> copy datafile 2 to '/home/oracle/devdb3/sysaux01.dbf';

Starting backup at 04-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/devdb2/sysaux01.dbf
output file name=/home/oracle/devdb3/sysaux01.dbf tag=TAG20120604T120506 RECID=2 STAMP=785073935
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 04-JUN-12

RMAN> copy datafile 3 to '/home/oracle/devdb3/undotbs01.dbf';

Starting backup at 04-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/devdb2/undotbs01.dbf
output file name=/home/oracle/devdb3/undotbs01.dbf tag=TAG20120604T120611 RECID=3 STAMP=785073977
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 04-JUN-12

RMAN> copy datafile 4 to '/home/oracle/devdb3/users01.dbf';

Starting backup at 04-JUN-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/devdb2/users01.dbf
output file name=/home/oracle/devdb3/users01.dbf tag=TAG20120604T120641 RECID=4 STAMP=785074001
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-JUN-12

5. use sqlplus to alter database rename file in controlfile

SQL> alter database rename file '/home/oracle/devdb2/system01.dbf' to '/home/oracle/devdb3/system01.dbf';

Database altered.

SQL> alter database rename file '/home/oracle/devdb2/sysaux01.dbf' to '/home/oracle/devdb3/sysaux01.dbf';

Database altered.

SQL> alter database rename file '/home/oracle/devdb2/undotbs01.dbf' to '/home/oracle/devdb3/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/home/oracle/devdb2/users01.dbf' to '/home/oracle/devdb3/users01.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/oracle/devdb3/system01.dbf
/home/oracle/devdb3/sysaux01.dbf
/home/oracle/devdb3/undotbs01.dbf
/home/oracle/devdb3/users01.dbf

6. move temporary tablespace files
  1* select * from v$tempfile

     FILE# CREATION_CHANGE# CREATION_     TS#     RFILE# STATUS  ENABLED    BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME

2    1876753 04-MAY-12       3  2 ONLINE  READ WRITE 15728640 1920 10485760 8192

/home/oracle/app/apache/oradata/devdb/temp01.dbf

SQL> alter tablespace temp add tempfile '/home/oracle/tempfile/temp01.dbf' size 10m autoextend on next 10m;

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/home/oracle/app/apache/oradata/devdb/temp01.dbf';

Tablespace altered.

SQL> select name from v$tempfile;

NAME
/home/oracle/tempfile/temp01.dbf


7. move/rename redo log file

SQL> select member from v$logfile;

MEMBER
/home/oracle/app/oracle/oradata/devdb/redo03.log
/home/oracle/app/oracle/oradata/devdb/redo02.log
/home/oracle/app/oracle/oradata/devdb/redo01.log

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS ARC STATUS       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1    1      25   52428800   512 1 NO  CURRENT     2026606 04-JUN-12 2.8147E+14
2    1      23   52428800   512 1 YES INACTIVE     2024705 04-JUN-12    2025593 04-JUN-12
3    1      24   52428800   512 1 YES INACTIVE     2025593 04-JUN-12    2026606 04-JUN-12

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '^C

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
2 /home/oracle/app/oracle/oradata/devdb/redo02.log
1 /home/oracle/app/oracle/oradata/devdb/redo01.log

SQL> alter database add logfile group 3 '/home/oracle/redolog/redo03.log' size 10m;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group  2 '/home/oracle/redolog/redo02.log' size 10m;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS ARC STATUS       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1    1      25   52428800   512 1 NO  CURRENT     2026606 04-JUN-12 2.8147E+14
2    1       0   10485760   512 1 YES UNUSED   0  0
3    1       0   10485760   512 1 YES UNUSED   0  0

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance orcl (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/home/oracle/app/oracle/oradata/devdb/redo01.log'


SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS ARC STATUS       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1    1      25   52428800   512 1 YES ACTIVE     2026606 04-JUN-12    2028373 04-JUN-12
2    1      26   10485760   512 1 NO  CURRENT     2028373 04-JUN-12 2.8147E+14
3    1       0   10485760   512 1 YES UNUSED   0  0



keep running 'alter system switch logfile' to make group 1 is not current and active, then drop group 1:

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE  MEMBERS ARC STATUS       FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1    1      28   52428800   512 1 YES INACTIVE     2028420 04-JUN-12    2028430 04-JUN-12
2    1      29   10485760   512 1 NO  CURRENT     2028430 04-JUN-12 2.8147E+14
3    1      27   10485760   512 1 YES INACTIVE     2028394 04-JUN-12    2028420 04-JUN-12


SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '/home/oracle/redolog/redo01.log' size 10m;

Database altered.

SQL> select member from v$logfile;

MEMBER

/home/oracle/redolog/redo03.log
/home/oracle/redolog/redo02.log
/home/oracle/redolog/redo01.log



at 6/04/2012 01:43:00 PM

Labels: ,