Oracle 12.1 new features: Online rename or relocate data files

To rename a data file or move a data file before Oracle12.1, you need to close the database or set the tablespace/data file to the offline state before referencing the Oracle modified data file name/Mobile data file summarized earlier. However, in the 12.1 version, you can rename or move data files directly in the data file online state.

To implement this feature, you need to useALTER DATABASE MOVE DATAFILE Statement, the syntax is as follows

ALTER DATABASE MOVE datafile (' filename ' | ' Asm_filename ' | File_number) [To (' filename ' | ' Asm_filename ')] [reuse] [KEEP]

This feature is tested in version 12.2 below

[Email Protected]>select * from v$version; BANNER con_id------------------------------------------------------------------------------------------Oracle Database 12c Enterprise Edition release 12.2.0.1.0-64bit Production 0pl/sql release 12.2.0.1.0-production 0core12.2. 0.1.0Production 0TNS for linux:version 12.2.0.1.0-production 0NLSRTL Version 12.2.0.1.0-production 0

1. Create test table space and data files:

[Email protected]>create tablespace t_move datafile '/home/oracle/t_move.dbf ' size 50m; Tablespace created. [Email protected]>col name for A50[email protected]>select d.name,d.status from V$datafile d,v$tablespace t where T. ts#=d.ts# and T.name= ' T_move '; name STATUS---------------------------------------------------------/home/oracle/t_ MOVE.DBF ONLINE

Now the data file '/home/oracle/t_move.dbf ' is online status

2. Perform a rename operation

[Email protected]>alter database move datafile '/home/oracle/t_move.dbf ' to '/home/oracle/t_move01.dbf ';D atabase Altered.   [Email protected]>select d.name,d.status from V$datafile d,v$tablespace t where t.ts#=d.ts# and t.name= ' T_MOVE '; name STATUS---------------------------------------------------------/home/oracle/t_move01.dbf online[email protected] >!ls-l/home/oracle/t_move01.dbf-rw-r-----1 Oracle oinstall 52436992 Jul 16:07/home/oracle/t_move01.dbf[email PR Otected]>!ls-l/home/oracle/t_move.dbfls:cannot access/home/oracle/t_move.dbf:no such file or directory

You can see that the file name changed from T_MOVE.DBF to T_MOVE01.DBF, and the original file no longer exists.

3. Perform Mobile directory operations

[email protected]>alter database move datafile   '/home/oracle/t_move01.dbf '  to  '/u01/app/oracle/oradata/ora12c/t_move01.dbf ';D atabase  Altered. [Email protected]>!ls -l /u01/app/oracle/oradata/ora12c/t_move01.dbf-rw-r----- 1  Oracle oinstall 52436992 jul 11 16:10 /u01/app/oracle/oradata/ora12c/t_ Move01.dbf[email protected]>!ls -l /home/oracle/t_move01.dbfls: cannot access  /home/oracle/t_move01.dbf: No such file or directory[email protected]> select d.name,d.status from v$datafile d,v$tablespace t where t.ts#=d.ts#  and t.name= ' T_move '; Name   status--------------------------------------------------  -------/u01/app/oracle/oradata/ora12c/t_move01.dbf   online 

From the above results you can see that the data file moved from the '/home/oracle ' directory to the '/u01/app/oracle/oradata/ora12c ' directory.

4. Copy data file to target directory, keep original file

[email protected]>alter database move datafile    '/u01/app/oracle/oradata/ora12c/t_move01.dbf '  to  '/home/oracle/t_move01.dbf '  keep;d Atabase altered. [Email protected]>select d.name,d.status from v$datafile d,v$tablespace t  where t.ts#=d.ts# and t.name= ' T_move ';name    STATUS-------------------------------------------------- -------/home/oracle/t_move01.dbf    online[email protected]>!ls -l /home/oracle/t_move01.dbf-rw-r----- 1 oracle  oinstall 52436992 jul 11 16:15 /home/oracle/t_move01.dbf[email protected] >!ls -l /u01/app/oracle/oradata/ora12c/t_move01.dbf-rw-r----- 1 oracle oinstall  52436992 JUL 11 16:15 /U01/APP/ORACLE/ORADATA/ORA12C/T_MOVE01.DBF 

From the above results you can see the data file changed to/HOME/ORACLE/T_MOVE01.DBF, but the original data file is still retained.

5. Moving data files to ASM storage

--File system to Asmalter DATABASE MOVE datafile '/u01/oracle/rbdb1/user1.dbf ' to ' +dgroup_01/data/orcl/datafile/user1.dbf ';-- ASM to Asmalter DATABASE MOVE datafile ' +dgroup_01/data/orcl/datafile/user1.dbf ' to ' +dgroup_02/data/orcl/datafile/ USER1.DBF ';

Reference: http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837

Oracle 12.1 new features: Online rename or relocate data files