Move Database Files
In Oracle 12c, a data file can be relocated while it is online. The command updates the control file and dictionary, and moves the file at the OS level. The procedure for relocating temp files, online redo logs, and control files remains unchanged.
-- move data file SQL> alter database move datafile '/u01/oradata/db1/db1/system01.dbf' to '/u01/oradata/db1/system01.dbf';
Here are some noteworthy facts about relocating data files:
– The datafile must be online when this command is issued.
– The file is first copied to the new location, and then deleted from the old; this requires twice the space.
– There is an option to keep the source file; the default is to delete it.
– If the destination file exits, you will get an error, unless the reuse option is specified.
– In Windows, the source file is sometimes not deleted, even if the keep option is not used.
– If you flashback the database, a moved file will not be placed back in its origianl location.
– Moving a datafile on a data guard primary DB does not move it on the standby.
– PDB datafiles cannot be moved from the root container.
– You can move files from/to ASM.
– There are no special considerations for RAC.
Demo
The example illustrates how to move database files. The demo environment consists of an Oracle 12cR1 database running on an Oracle Linux guest, using Virtual Box on Windows. The example below shows how to move data files online. It also shows one procedure for moving other database files.
Example
The online move feature introduced in 12c applies to all data files, inlcuding those belonging to the system tablespace. However, there is no change in the procedure for moving temp files, online redo logs, and control files. Temp files and online redo logs can be added and dropped while the database is running. Moving control files requires a database bounce.
Move all datafiles, including system and undo.
SQL> select file_id,file_name,status from dba_data_files order by file_id; FILE_ID FILE_NAME STATUS ---------- -------------------------------------- --------- 1 /u01/oradata/db1/db1/system01.dbf AVAILABLE 3 /u01/oradata/db1/db1/sysaux01.dbf AVAILABLE 4 /u01/oradata/db1/db1/undotbs01.dbf AVAILABLE 5 /u01/oradata/db1/db1/example01.dbf AVAILABLE 6 /u01/oradata/db1/db1/users01.dbf AVAILABLE SQL> alter database move datafile '/u01/oradata/db1/db1/system01.dbf' to '/u01/oradata/db1/system01.dbf'; Database altered. SQL> alter database move datafile '/u01/oradata/db1/db1/sysaux01.dbf' to '/u01/oradata/db1/sysaux01.dbf'; Database altered. SQL> alter database move datafile '/u01/oradata/db1/db1/undotbs01.dbf' to '/u01/oradata/db1/undotbs01.dbf'; Database altered. SQL> alter database move datafile '/u01/oradata/db1/db1/example01.dbf' to '/u01/oradata/db1/example01.dbf'; Database altered. SQL> alter database move datafile '/u01/oradata/db1/db1/users01.dbf' to '/u01/oradata/db1/users01.dbf'; Database altered.
Verify dictionary has been updated.
SQL> select file_id,file_name,status from dba_data_files order by file_id; FILE_ID FILE_NAME STATUS ---------- -------------------------------- --------- 1 /u01/oradata/db1/system01.dbf AVAILABLE 3 /u01/oradata/db1/sysaux01.dbf AVAILABLE 4 /u01/oradata/db1/undotbs01.dbf AVAILABLE 5 /u01/oradata/db1/example01.dbf AVAILABLE 6 /u01/oradata/db1/users01.dbf AVAILABLE
Verify data files have been relocated to destination directory at OS level.
SQL> !ls /u01/oradata/db1 db1 example01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
… and deleted from source location.
SQL> !ls /u01/oradata/db1/db1 control01.ctl control02.ctl redo01.log redo02.log redo03.log temp01.dbf
Temp files cannot be moved online; to avoid downtime, you can add a new file and delete the old one.
SQL> alter tablespace temp add tempfile '/u01/oradata/db1/temp01.dbf' size 32m autoextend on next 16m maxsize 1024m; Tablespace altered. -- drop temp file; if there are active temp segments in it, you will get an error. SQL> alter database tempfile '/u01/oradata/db1/db1/temp01.dbf' drop including datafiles; Database altered.
Redo logs also cannot be moved online; to avoid downtime, you can add a new file and then delete the old one.
SQL> select f.group#,member,bytes,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1; GROUP# MEMBER BYTES STATUS ---------- ------------------------ ------------ ----------- 1 /u01/oradata/db1/db1/redo01.log 52,428,800 CURRENT 2 /u01/oradata/db1/db1/redo02.log 52,428,800 INACTIVE 3 /u01/oradata/db1/db1/redo03.log 52,428,800 INACTIVE
Drop ORLs in /u01/oradata/db1/db1 and recreate them in /u01/oradata/db1.
Start with group 2 which is inactive.
SQL> alter database drop logfile group 2; Database altered. SQL> alter database add logfile group 2 '/u01/oradata/db1/redo02.log' size 50m reuse; Database altered.
Group 3 is also inactive and can be dropped & recreated.
SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 '/u01/oradata/db1/redo03.log' size 50m reuse; Database altered.
Switch logfile to make group 2 current.
SQL> alter system switch logfile; System altered. SQL> select f.group#,member,bytes,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1; GROUP# MEMBER BYTES STATUS ---------- ---------------------------------- ------------ ---------- 1 /u01/oradata/db1/db1/redo01.log 52,428,800 ACTIVE 2 /u01/oradata/db1/redo02.log 52,428,800 CURRENT 3 /u01/oradata/db1/redo03.log 52,428,800 UNUSED
Group 1 is still active; perform a checkpoint to make it inactive.
SQL> alter system checkpoint;
System altered.
SQL> select f.group#,member,bytes,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1;
GROUP# MEMBER BYTES STATUS
———- ———————————- ———— ———–
1 /u01/oradata/db1/db1/redo01.log 52,428,800 INACTIVE
2 /u01/oradata/db1/redo02.log 52,428,800 CURRENT
3 /u01/oradata/db1/redo03.log 52,428,800 UNUSED
Now group 1 is inactive and can be dropped & recreated.
SQL> alter database drop logfile group 1; Database altered. SQL> alter database add logfile group 1 '/u01/oradata/db1/redo01.log' size 50m reuse; Database altered.
Verify all ORLs are now in /u01/oradata/db1.
SQL> select f.group#,member,bytes,l.status from v$log l,v$logfile f where l.group#=f.group# order by 1; GROUP# MEMBER BYTES STATUS ---------- ----------------------------- ------------ ---------- 1 /u01/oradata/db1/redo01.log 52,428,800 UNUSED 2 /u01/oradata/db1/redo02.log 52,428,800 CURRENT 3 /u01/oradata/db1/redo03.log 52,428,800 UNUSED
Delete ORLs in /u01/oradata/db1/db1.
SQL> !rm /u01/oradata/db1/db1/redo0?.log
Moving control files requires downtime. They are currently in /u01/oradata/db1/db1 and will be moved to /u01/oradata/db1.
SQL> select name from v$controlfile; NAME ----------------------------------- /u01/oradata/db1/db1/control01.ctl /u01/oradata/db1/db1/control02.ctl
Change their location in the spfile to /u01/oradata/db1.
SQL> alter system set control_files='/u01/oradata/db1/control01.ctl','/u01/oradata/db1/control02.ctl' scope=spfile; System altered.
Shut down the database.
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Move the files using OS commands.
SQL> !mv /u01/oradata/db1/db1/control01.ctl /u01/oradata/db1/control01.ctl SQL> !mv /u01/oradata/db1/db1/control02.ctl /u01/oradata/db1/control02.ctl
Startup the database.
SQL> startup ORACLE instance started. Total System Global Area 3607101440 bytes Fixed Size 2930608 bytes Variable Size 419432528 bytes Database Buffers 3170893824 bytes Redo Buffers 13844480 bytes Database mounted. Database opened.
Verify control files are in /u01/oradata/db1/.
SQL> select name from v$controlfile; NAME ----------------------------------- /u01/oradata/db1/control01.ctl /u01/oradata/db1/control02.ctl