How to move a datafile from ASM to a file system
Can be performed in 2 Ways:
- While the database is in mount stage.
- While the database is running
While the database is in mount stage.
Step 1 Shutdown and mount the database.
SQL> shutdown immediate;
SQL> startup mount;
Step 2 Ensure you have enough space on the file system to copy the datafile.
First identify the size of the datafile you wish to move.
SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;
Step 3 Connect to RMAN and copy the datafile from the ASM diskgroup to the filesystem.
$rman target /
RMAN> copy datafile 4 to ‘/export/home/oracle/users01.dbf’;
Step 4 Update the controlfile with the new location of the datafile.
SQL> alter database rename file ‘+DB_DATA01/INDIAN/datafile/users.258.600450259’ to ‘/export/home/oracle/users01.dbf’;
Step 5 The file is now if the new location.
SQL> select file#, name from v$datafile;
Step 6 The database may now be opened.
While the database is running
In order to move a datafile on a running active database the tablespace where the datafile resides must be placed offline.
Step 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;
SQL> alter tablespace USERS offline;
Step 2 Ensure you have enough space on the file system to copy the datafile.
First identify the size of the datafile you wish to move.
SQL> select file#, name, (bytes/1048576) File_Size_MB from v$datafile;
Step 3 Connect to RMAN and copy the datafile from the ASM diskgroup to the filesystem.
$rman target /
RMAN> copy datafile 4 to ‘/export/home/oracle/users01.dbf’;
Step 4 Update the controlfile with the new location of the datafile.
SQL> alter database rename file ‘+DB_DATA01/INDIAN/datafile/users.258.600450259’ to ‘/export/home/oracle/users01.dbf’;
Step 5 The file is now if the new location.
SQL> select file#, name from v$datafile;
Step 6 Place the tablespace online;
SQL> alter tablespace USERS online;
The datafile has now been successfully moved to the File System.
Responses