Move the datafile into the new location
Posted by Mir Sayeed Hassan on September 27th, 2017
Move the datafiles (System, SYSAUX, UNDOTBS, TEMP, REDO LOGFILES) into the new location:
========
USERS TBS
=========
Note:
It does not require the database to be bonce or in mount stage for USERS tablespace
Get the datafile location exist
SQL> select name from v$datafile; (Old Location of al the datafiles) NAME ------------------------------------------------ /u01/app/oracle/oradata/testdb/system01.dbf /u01/app/oracle/oradata/testdb/sysaux01.dbf /u01/app/oracle/oradata/testdb/undotbs01.dbf /u01/app/oracle/oradata/testdb/users01.dbf /u01/app/oracle/oradata/testdb/example01.dbf
Moving Normal Data Files
To move the datafile associated with the USERS tablespace, follow the below steps:
- Make the users tablespace offline:
SQL> alter tablespace users offline; Tablespace altered.
SQL> exit
Now move or copy the tablespace users into the new location
$ mv /u01/app/oracle/oradata/testdb/users01.dbf /oradata/datafiles/
$ !sq
SQL> alter tablespace USERS rename datafile ‘'/u01/app/oracle/oradata/testdb/users01.dbf ‘ to '/oradata/datafiles/users01.dbf'; Tablespace altered.
SQL> alter tablespace USERS online; Tablespace altered
Verify the new location:
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/testdb/system01.dbf /u01/app/oracle/oradata/testdb/sysaux01.dbf /u01/app/oracle/oradata/testdb/undotbs01.dbf /oradata/datafiles/users01.dbf ----- New location transfer /u01/app/oracle/oradata/testdb/example01.dbf
Others Moving System, SYSAUX, UNDOTBS, TEMP, REDO LOGFILES
— To move system, temporary or rollback tablespaces, the database should be shut down and brought back up into a mount state. This allows the control file to be updated while the datafiles are not in use.
=======
SYSTEM
========
Shutdown the database & startup in mount mode
SQL> shu immediate
SQL> startup mount
[oracle@testdb ~]$ cd /u01/app/oracle/oradata/testdb/
[oracle@testdb testdb]$ ls control01.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf
[oracle@testdb testdb]$ mv system01.dbf /oradata/datafiles/
SQL> alter database rename file '/u01/app/oracle/oradata/testdb/system01.dbf' to '/oradata/datafiles/system01.dbf'; Database altered.
SQL> alter database open; Database altered.
SQL> select status from v$instance; STATUS ------- OPEN
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/datafiles/system01.dbf /u01/app/oracle/oradata/testdb/sysaux01.dbf /u01/app/oracle/oradata/testdb/undotbs01.dbf /oradata/datafiles/users01.dbf /u01/app/oracle/oradata/testdb/example01.dbf
========
SYSAUX
========
SQL> startup mount
[oracle@testdb testdb]$ mv sysaux01.dbf /oradata/datafiles/
[oracle@testdb testdb]$ !sq
SQL> alter database rename file '/u01/app/oracle/oradata/testdb/sysaux01.dbf' to '/oradata/datafiles/sysaux01.dbf'; Database altered.
SQL> alter database open; Database altered.
Verify
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/datafiles/system01.dbf /oradata/datafiles/sysaux01.dbf /u01/app/oracle/oradata/testdb/undotbs01.dbf /oradata/datafiles/users01.dbf /u01/app/oracle/oradata/testdb/example01.dbf
======
UNDO
======
SQL> startup mount
[oracle@testdb testdb]$ mv undotbs01.dbf /oradata/datafiles/
[oracle@testdb testdb]$ !sq
SQL> alter database rename file '/u01/app/oracle/oradata/testdb/undotbs01.dbf' to '/oradata/datafiles/undotbs01.dbf'; Database altered.
SQL> alter database open; Database altered.
Verify
SQL> select name from V$datafile; NAME -------------------------------------------------------------------------------- /oradata/datafiles/system01.dbf /oradata/datafiles/sysaux01.dbf /oradata/datafiles/undotbs01.dbf /oradata/datafiles/users01.dbf /u01/app/oracle/oradata/testdb/example01.dbf
==========
EXAMPLE
==========
SQL> startup mount
[oracle@testdb testdb]$ mv example01.dbf /oradata/datafiles/
[oracle@testdb testdb]$ !sq
SQL> alter database rename file '/u01/app/oracle/oradata/testdb/example01.dbf' to '/oradata/datafiles/example01.dbf'; Database altered.
SQL> alter database open; Database altered.
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/datafiles/system01.dbf /oradata/datafiles/sysaux01.dbf /oradata/datafiles/undotbs01.dbf /oradata/datafiles/users01.dbf /oradata/datafiles/example01.dbf
=======
TEMP
=======
SQL> startup mount
[oracle@testdb testdb]$ mv temp01.dbf /oradata/datafiles/
[oracle@testdb testdb]$ !sq
SQL> alter database rename file '/u01/app/oracle/oradata/testdb/temp01.dbf' to '/oradata/datafiles/temp01.dbf'; Database altered.
SQL> alter database open; Database altered.
Verify:
Below Query
SQL> select * from v$tempfile; FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED ---------- ---------------- --------- ---------- ---------- ------- ---------- BYTES BLOCKS CREATE_BYTES BLOCK_SIZE ---------- ---------- ------------ ---------- NAME -------------------------------------------------------------------------------- 1 925785 20-JUN-17 3 1 ONLINE READ WRITE 30408704 3712 20971520 8192 /oradata/datafiles/temp01.dbf
================
REDOLOG FILES
================
Exiting:
SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/testdb/redo03.log /u01/app/oracle/oradata/testdb/redo02.log /u01/app/oracle/oradata/testdb/redo01.log
Change to the new location
[oracle@testdb testdb]$ mv redo01.log /oradata/datafiles/
[oracle@testdb testdb]$ mv redo02.log /oradata/datafiles/
[oracle@testdb testdb]$ mv redo03.log /oradata/datafiles/
SQL> alter database rename file '/u01/app/oracle/oradata/testdb/redo01.log' to '/oradata/datafiles/redo01.log'; Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/testdb/redo02.log' to '/oradata/datafiles/redo02.log'; Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/testdb/redo03.log' to '/oradata/datafiles/redo03.log'; Database altered.
SQL> alter database open; Database altered.
Verify:
SQ> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /oradata/datafiles/redo03.log /oradata/datafiles/redo02.log /oradata/datafiles/redo01.log
Therefore change the location of the existing datafile location into the new location