• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to move a datafile from file system to ASM

By admin

Moving a datafile from the file system can be achieved in two ways.
1. While the database is shutdown (in mount stage).
2. While the database is running (with the selected tablespace offline).

While the database is shutdown (in mount stage)

Moving oracle datafile while the database is in mount stage is performed in the following way:

1. Shutdown and mount the database.

$ sqlplus '/as sysdba'
SQL> shutdown immediate;
SQL> startup mount;

2. Ensure you have enough space in the ASM diskgroup to copy the datafile. First identify the size of the datafile you wish to move. In this example we will be moving users01.dbf

SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;

FILE#   NAME                         FILE_SIZE_MB
-----   ---------------------------- --------------
...
4       /oradata/PROD/users01.dbf    2500
...
$ export ORACLE_SID=+ASM
SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

NAME                           STATE       TOTAL_MB   FREE_MB
------------------------------ ----------- ---------- ----------
DGROUP1                        MOUNTED     100        3
DGROUP2                        MOUNTED     4882       4830

3. Connect to RMAN and copy the datafile from the filesystem to the select ASM diskgroup.

$ rman target=/

RMAN> copy datafile 4 to '+DGROUP2';

Starting backup at 2016/09/05 12:14:23
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=31 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/oradata/PROD/users01.dbf
output filename=+DGROUP2/PROD/datafile/users01.258.600351265 tag=TAG20060905T121424 recid=10 stamp=600351264
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:01
Finished backup at 2016/08/05 12:19:24

4. Update the controlfile with the new location of the datafile.

$ rman target /
RMAN> switch datafile 4 to copy;
datafile 4 switched to datafile copy "+DGROUP2/PROD/datafile/users01.258.600351265".

5. The file is now if the new location.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
..
+DGROUP2/PROD/datafile/users01.258.600351265
..

6. The database may now be opened.

While the database is running (with the select tablespace offline)

In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.

1. Identify the tablespace which contains the datafile and offline the tablespace.

SQL> select tablespace_name, file_name from dba_data_files where file_id=4;

TABLESPACE_NAME    FILE_NAME
------------------ ------------------------------
USERS              /oradata/RMAN/users01.dbf
SQL> alter tablespace USERS offline;

3. Now repeat the steps 2 to 5 in the above method 1 to move the datafile to ASM disk group.

6. After you have successfully completed the above steps (2 – 5) place the tablespace online.

SQL> alter tablespace USERS online;

The datafile has now been successfully moved to the ASM diskgroup.

Here is another method to move datafiles from file system to ASM using asmcmd cp command :

How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command

Filed Under: ASM, oracle

Some more articles you might also be interested in …

  1. Empty Directories in the Flash Recovery Area (FRA) are not deleted
  2. Oracle RAC Interview Questions – Coherence and Split-Brain
  3. How to Force ASM to Scan the Multipathed Device First using ASMLIB/oracleasm
  4. How to Move OCR, Vote Disk File, ASM SPILE to new Diskgroup
  5. How to Connect After ORA-20 has Occurred
  6. How to Drop/Truncate Multiple Partitions in Oracle 12C
  7. How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database
  8. ORA-28007: the password cannot be reused
  9. How to Modify an Existing ASM Spfile in a RAC Environment
  10. Script to verify the Oracle DataPump Data Dictionary Catalog

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary