DBA needs to rename or move oracle file from one filesystem or ASM DISKGroup to another.
- Move Datafiles Using ALTER DATABASE MOVE DATAFILE command – 12c and onward
- Moving Datafile Using RMAN with offline option – 10g or 11g
Prerequisites: Database should be configured archive log mode.
Move Datafiles Using ALTER DATABASE MOVE DATAFILE command – 12c and onward
From 12c, can move any Datafiles in online by using ALTER DATABASE MOVE DATAFILE command. I will show how to move all datafiles from +DATA01 to +DATA DISKGroup. Follow below steps.
Step 1: Stop all applications and you may stop listener to make sure there is no new connection from any users.
Step 2: Make sure you have database backup (Full Backup + Archive Log Backup or Incremental Backup).
Step 3: Find DataFile list.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES; FILE_NAME -------------------------------------------------------------------------------- +DATA01/ORCL/DATAFILE/system.266.1044638517 +DATA01/ORCL/DATAFILE/sysaux.262.1044636479 +DATA01/ORCL/DATAFILE/undotbs1.263.1044637461 +DATA01/ORCL/DATAFILE/users.261.1044630801 +DATA01/ORCL/DATAFILE/undotbs2.265.1044638477 +DATA01/ORCL/DATAFILE/tbs_01.259.1044630753 +DATA01/ORCL/DATAFILE/tbs_02.256.1044232727 +DATA01/ORCL/DATAFILE/ts_data.260.1044630755 +DATA01/ORCL/DATAFILE/ts_data01.258.1044294097 9 rows selected.
Step 4: Identify the target DiskGroup to move:
SQL> SELECT NAME FROM V$ASM_DISKGROUP; NAME ------------------------------ DATA01 OCR FRA DATA
Find DiskGroup details using ASMCMD:
[oracle@racdr1 ~]$ . oraenv ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@racdr1 ~]$ asmcmd ASMCMD> lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 4096 1048576 15342 14530 0 14530 0 N DATA/ MOUNTED EXTERN N 512 4096 1048576 15342 4096 0 4096 0 N DATA01/ MOUNTED EXTERN N 512 4096 1048576 10228 10129 0 10129 0 N FRA/ MOUNTED EXTERN N 512 4096 1048576 9201 4713 0 4713 0 Y OCR/
Step 5: Prepare script like below for all datafile or particular data file (which files want to move)
ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/system.266.1044638517' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/sysaux.262.1044636479' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/undotbs1.263.1044637461' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/users.261.1044630801' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/undotbs2.265.1044638477' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/tbs_02.256.1044232727' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/ts_data01.258.1044294097' TO '+DATA';
Execute the command:
[oracle@racdr1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 1 17:55:43 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/system.266.1044638517' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/sysaux.262.1044636479' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/undotbs1.263.1044637461' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/users.261.1044630801' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/undotbs2.265.1044638477' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/tbs_02.256.1044232727' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' TO '+DATA'; ALTER DATABASE MOVE DATAFILE '+DATA01/ORCL/DATAFILE/ts_data01.258.1044294097' TO '+DATA'; Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered. SQL> Database altered.
Step 6: Verify the datafile: All Files moved to +DATA DiskGroup
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES; FILE_NAME -------------------------------------------------------------------------------- +DATA/ORCL/DATAFILE/system.258.1044640545 +DATA/ORCL/DATAFILE/sysaux.268.1044640561 +DATA/ORCL/DATAFILE/undotbs1.260.1044640581 +DATA/ORCL/DATAFILE/users.257.1044640585 +DATA/ORCL/DATAFILE/undotbs2.277.1044640587 +DATA/ORCL/DATAFILE/tbs_01.278.1044640589 +DATA/ORCL/DATAFILE/tbs_02.279.1044640593 +DATA/ORCL/DATAFILE/ts_data.280.1044640595 +DATA/ORCL/DATAFILE/ts_data01.281.1044640657 9 rows selected.
Step 7: Take database full backup.
Moving Datafile Using RMAN with offline option – 10g or 11g
Above option – (ALTER DATABASE MOVE) will not support for 10g or 11g. I will discuss how can perform datafile movement or renaming on 10g or 11g.
Step 1: Stop all applications and you may stop listener to make sure there is no new connection from any users.
Step 2: Make sure you have database backup (Full Backup + Archive Log Backup or Incremental Backup).
Step 3: Find DataFile list.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES; FILE_NAME -------------------------------------------------------------------------------- +DATA01/ORCL/DATAFILE/system.266.1044638517 +DATA01/ORCL/DATAFILE/sysaux.262.1044636479 +DATA01/ORCL/DATAFILE/undotbs1.263.1044637461 +DATA01/ORCL/DATAFILE/users.261.1044630801 +DATA01/ORCL/DATAFILE/undotbs2.265.1044638477 +DATA01/ORCL/DATAFILE/tbs_01.259.1044630753 +DATA01/ORCL/DATAFILE/tbs_02.256.1044232727 +DATA01/ORCL/DATAFILE/ts_data.260.1044630755 +DATA01/ORCL/DATAFILE/ts_data01.258.1044294097 9 rows selected.
Step 4: Identify the target DiskGroup to move:
SQL> SELECT NAME FROM V$ASM_DISKGROUP; NAME ------------------------------ DATA01 OCR FRA DATA
Find DiskGroup details using ASMCMD:
[oracle@racdr1 ~]$ . oraenv ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle [oracle@racdr1 ~]$ asmcmd ASMCMD> lsdg State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name MOUNTED EXTERN N 512 4096 1048576 15342 14530 0 14530 0 N DATA/ MOUNTED EXTERN N 512 4096 1048576 15342 4096 0 4096 0 N DATA01/ MOUNTED EXTERN N 512 4096 1048576 10228 10129 0 10129 0 N FRA/ MOUNTED EXTERN N 512 4096 1048576 9201 4713 0 4713 0 Y OCR/
Step 5: Take offline the data file which want to move
alter database datafile '+DATA/ORCL/DATAFILE/tbs_01.274.1044232703' offline; alter database datafile '+DATA/ORCL/DATAFILE/ts_data.275.1044294089' offline; alter database datafile '+DATA/ORCL/DATAFILE/users.259.1043890029' offline;
Step 6: Copy the datafile to new location (New DISKGroup)
Note::: Save these log and it will help to identify the new file name specially for ASM.
[oracle@racdr1 ~]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jul 1 15:12:11 2020 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1571670809) RMAN> copy datafile '+DATA/ORCL/DATAFILE/tbs_01.274.1044232703' to '+DATA01'; Starting backup at 01-JUL-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=79 instance=ORCL1 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=+DATA/ORCL/DATAFILE/tbs_01.274.1044232703 output file name=+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753 tag=TAG20200701T151233 RECID=1 STAMP=1044630753 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 01-JUL-20 RMAN> copy datafile '+DATA/ORCL/DATAFILE/ts_data.275.1044294089' to '+DATA01'; Starting backup at 01-JUL-20 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+DATA/ORCL/DATAFILE/ts_data.275.1044294089 output file name=+DATA01/ORCL/DATAFILE/ts_data.260.1044630755 tag=TAG20200701T151235 RECID=2 STAMP=1044630792 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 Finished backup at 01-JUL-20 RMAN> copy datafile '+DATA/ORCL/DATAFILE/users.259.1043890029' to '+DATA01'; Starting backup at 01-JUL-20 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.259.1043890029 output file name=+DATA01/ORCL/DATAFILE/users.261.1044630801 tag=TAG20200701T151321 RECID=3 STAMP=1044630801 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 01-JUL-20
Step 7: Rename the datafile
Note::: New datafile name (generated auto file name for ASM) will get from Step 6 (RMAN output).
SQL> alter database rename file '+DATA/ORCL/DATAFILE/tbs_01.274.1044232703' to '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753'; alter database rename file '+DATA/ORCL/DATAFILE/ts_data.275.1044294089' to '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755'; alter database rename file '+DATA/ORCL/DATAFILE/users.259.1043890029' to '+DATA01/ORCL/DATAFILE/users.261.1044630801'; Database altered. SQL> SQL> Database altered. SQL> SQL> Database altered.
Step 8: Switch Datafile using RMAN
switch datafile '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' to copy; switch datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' to copy; switch datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801' to copy;
[oracle@racdr1 ~]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Jul 1 15:22:07 2020 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1571670809) RMAN> switch datafile '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' to copy; using target database control file instead of recovery catalog datafile 7 switched to datafile copy "+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753" RMAN> switch datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' to copy; datafile 2 switched to datafile copy "+DATA01/ORCL/DATAFILE/ts_data.260.1044630755" RMAN> switch datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801' to copy; datafile 6 switched to datafile copy "+DATA01/ORCL/DATAFILE/users.261.1044630801"
Step 9: Recovery the new datafile using RMAN
recover datafile '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753'; recover datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755'; recover datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801';
RMAN> recover datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755'; Starting recover at 01-JUL-20 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 01-JUL-20 RMAN> recover datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801'; Starting recover at 01-JUL-20 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 01-JUL-20
Step 10: Bring back datafile online
RMAN> alter database datafile '+DATA01/ORCL/DATAFILE/tbs_01.259.1044630753' online; Statement processed RMAN> alter database datafile '+DATA01/ORCL/DATAFILE/ts_data.260.1044630755' online; Statement processed RMAN> alter database datafile '+DATA01/ORCL/DATAFILE/users.261.1044630801' online; Statement processed
Step 11: Take database full backup.
