Friday, January 30, 2015

How to move datafile from one ASM diskgroup to another

Follow the below steps to move datafile from one ASM diskgroup to another.

Case 1: Make the tablespace offline and move the datafile
1. Check the file which you need to move.
2. Make the tablespace offline
3. Connect to rman 
4. issue the copy command from the rman prompt
5. login to the sql prompt and issue rename datafile command
6. Make the tablespace online

Case 2: Make the datafile offline and move the datafile
1. Check the file which you need to move.
2. Make the datafile offline
3. Connect to rman 
4. issue the copy command from the rman prompt
5. login to the sql prompt and issue rename datafile command
6. Recover the datafile (In case you make datafile offline you need to recover the datafile)
7. Make the datafile online

Note: Be very cautious while executing the rename file command as it will remove the original file from the ASM.(tested in 11.2.0.4)

Case 1:
SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                            ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1            +DATA_DG01/orcl/datafile/test1.1117.870325481        ONLINE

SQL> alter tablespace test1 offline;
Tablespace altered.

SQL>  select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                            ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1            +DATA_DG01/orcl/datafile/test1.1117.870325481        OFFLINE


[oracle@linux01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 30 05:24:02 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=408657265)

RMAN> copy datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481' to '+FRA_DG01';

Starting backup at 30-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA_DG01/orcl/datafile/test1.1117.870325481
output file name=+FRA_DG01/orcl/datafile/test1.263233.870326681 tag=TAG20150130T052441 RECID=7 STAMP=870326681
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15


SQL> alter database rename file '+DATA_DG01/orcl/datafile/test1.1117.870325481' to '+FRA_DG01/orcl/datafile/test1.263233.870326681';
Database altered.

SQL>  select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                            ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1            +FRA_DG01/orcl/datafile/test1.263233.870326681       OFFLINE

SQL> alter tablespace test1 online;
Tablespace altered.

SQL> select tablespace_name, file_name, online_status from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                            ONLINE_STATUS
---------------- ---------------------------------------------------- -------------
TEST1            +FRA_DG01/orcl/datafile/test1.263233.870326681       ONLINE


====================================================================

Case 2:
SQL> select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                        ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1            +FRA_DG01/orcl/datafile/test1.256846.868340771   ONLINE


SQL> alter database  datafile '+FRA_DG01/orcl/datafile/test1.256846.868340771' offline;
Database altered.

SQL>  select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                        ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1            +FRA_DG01/orcl/datafile/test1.256846.868340771   RECOVER

[oracle@linux01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jan 30 05:03:51 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=408657265)

RMAN> copy datafile '+FRA_DG01/orcl/datafile/test1.256846.868340771' to '+DATA_DG01';

Starting backup at 30-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=245 instance=RL3DB151 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+FRA_DG01/orcl/datafile/test1.256846.868340771
output file name=+DATA_DG01/orcl/datafile/test1.1117.870325481 tag=TAG20150130T050439 RECID=6 STAMP=870325480
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15

SQL> alter database rename file '+FRA_DG01/orcl/datafile/test1.256846.868340771' to '+DATA_DG01/orcl/datafile/test1.1117.870325481';
Database altered.

SQL> recover datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481';
Media recovery complete.

SQL>  select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                        ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1            +DATA_DG01/orcl/datafile/test1.1117.870325481    OFFLINE

SQL> alter database datafile '+DATA_DG01/orcl/datafile/test1.1117.870325481' online;
Database altered.

SQL>  select tablespace_name, file_name, ONLINE_STATUS from dba_data_files;
TABLESPACE_NAME  FILE_NAME                                        ONLINE_STATUS
---------------- ------------------------------------------------ -------------
TEST1            +DATA_DG01/orcl/datafile/test1.1117.870325481    ONLINE

No comments:

Post a Comment