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

The Geek Diary

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

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

by admin

Question : A datafile was wrongly added on file system instead of ASM diskgroup. How to move it to the ASM disk group again?

Answer :
1) Make sure the datafile to be moved is OFFLINE before proceeding :

SQL> alter system switch logfile;
System altered.
SQL> select file_name, file_id from dba_data_files;
FILE_NAME FILE_ID
---------------------
/u01/oracle/oradata/test1.dbf 6
SQL> alter database datafile 6 offline;
Database altered.
SQL> select file_name, file_id, online_status from dba_data_files where file_id=6;
FILE_NAME FILE_ID ONLINE_STATUS
---------- -------
/u01/oracle/oradata/test1.dbf 6 RECOVER

2. Use ASMCMD to copy the file from filesystem to the diskgroup

ASMCMD> cp /u01/oracle/oradata/test1.dbf +DATA/LONDON/DATAFILE/test.dbf
copying /u01/oracle/oradata/test1.dbf -> +DATA/LONDON/DATAFILE/test.dbf

Verify the movement :

ASMCMD> cd +DATA/ASM/DATAFILE
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
  N test.dbf => +DATA/ASM/DATAFILE/test.dbf.286.833718815

3. Once the file is copied, rename the datafile.

SQL> alter database rename file '/u01/oracle/oradata/test1.dbf' to '+DATA/LONDON/DATAFILE/test.dbf';
Database altered.

4. Recover the datafile and bring it ONLINE.

SQL> alter database recover datafile 6;
Database altered.
SQL> alter database datafile 6 online;
Database altered.

5. Confirm correct name and location:

SQL> select file_name, file_id, online_status from dba_data_files where file_id=6;
FILE_NAME   FILE_ID   ONLINE_STATUS
----------  -------   -------------
+DATA/london/datafile/test.dbf  6  ONLINE
How to move a datafile from file system to ASM

Filed Under: ASM, oracle

Some more articles you might also be interested in …

  1. Automatic Stop of Oracle Database (dbshut) not working in CentOS/RHEL 7 with systemd
  2. Common Init.ora Parameters and Unix, Linux Kernel Parameters and Relationship Between Them
  3. How to Disable Oracle Net Tracing without stopping server process
  4. Oracle Database 12c New Feature – Move a Datafile Online
  5. Oracle Database Environment Variables and Their Functions
  6. Oracle Database 18c New Feature – Memoptimized Rowstore
  7. How to rename Oracle-Managed Files (OMFs)
  8. How To Create “A CRS Managed” ACFS FileSystem On Oracle RAC Cluster (ASM/ACFS 11.2)
  9. How to do a Synchronous Refresh with Staging Logs in Oracle 12c
  10. How to Create Undo Tablespace for a Newly Added RAC Instance (ORA-30012)

You May Also Like

Primary Sidebar

Recent Posts

  • What are /dev/zero and /dev/null files in Linux
  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright