Amazon books

Wednesday, November 11, 2009

To move datafile from one disk group to another you need to follow these steps

To move datafile from one disk group to another you need to follow these steps:

1-Identify datafiles that will be moved:
SQL>select
file_name
from
dba_data_files;

2-Identify the disk group destination
--Connect to ASM instance
SQL> select
name
from
v$asm_diskgroup;

3-Take the datafile OFFLINE
SQL>alter database datafile 'file_name' offline;

4-Copy the file using either dbms_file_transfer or RMAN:
--DBMS_FILE_TRANSFER
begin
dbms_file_transfer.copy_file(
source_directory_object => 'DIR_ORIG', --This is an object directory created pointing to original datafile location
source_file_name => 'Data_File_Name.dbf',
destination_directory_object => 'DIR_DEST', --This is an object directory pointing to destination of datafile
destination_file_name => 'Data_File_Name_New.dbf');
end;

--RMAN
RMAN>copy datafile '+DG_ORIG/dbname/datafiles/Data_File_Name.dbf' TO '+DB_DEST';

5-Rename the datafile
SQL alter database rename file '+DG_ORIG/dbname/datafiles/Data_File_Name.dbf' to '+DG_DEST/db_name/datafiles/Data_File_Name_New.dbf'

6-Recover the datafile
SQL>recover datafile '+DG_DEST/db_name/datafiles/Data_File_Name_New.dbf';

7-Put the datafile ONLINE
SQL>alter database datafile '+DG_DEST/db_name/datafiles/Data_File_Name_New.dbf' online;

8-Delete the old datafile using ASMCMD
ASMCMD> cd +DG_ORIG/dbname/datafiles/
ASMCMD> rm -rf Data_File_Name.dbf

9-Backup your tablespace or your database


Best Regards,
Paulo Portugal

No comments:

Post a Comment