September 24, 2015

How to move a datafile from filesystem to ASM

We can move the datafile from filesystem to ASM by bringing the corresponding tablespace offline or the only datafile which you needs to be move. We can see these 2 scenarios.

Here we have added the datafile wrongly due to that datafile is not created in a specified location.

SQL> alter tablespace TCB_TSQ01 add datafile 'CRM_DBF01' size 100m autoextend on next 20m maxsize 32g;

Tablespace altered.



 
I) Tablespace offline:
******************

1. Identify the which datafile wants to move to the ASM.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';



FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

------------------------------------------------------------------ ------------------------------ -------------------- --- ----------

/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01                  TCB_TSQ01                                 .09765625 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32



6 rows selected.


2. Take the corresponding tablespace offline. 

SQL> alter tablespace TCB_TSQ01 offline;



Tablespace altered.


3. Copy the datafile to the desired location using RMAN
-bash-4.1$ rman target /




RMAN> copy datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01';



Starting backup at 20-SEP-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2942 instance=CRM2 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00023 name=/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01

output file name=+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477 tag=TAG20150920T123116 RECID=1 STAMP=890915477

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 20-SEP-15



Starting Control File and SPFILE Autobackup at 20-SEP-15

piece handle=+CRM_ARC01/CRM/autobackup/2015_09_20/s_890915478.1003.890915479 comment=NONE

Finished Control File and SPFILE Autobackup at 20-SEP-15



RMAN> exit




Recovery Manager complete.


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



SQL> alter database rename file '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';



Database altered.



SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';



FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

------------------------------------------------------------------ ------------------------------ -------------------- --- ----------

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01



6 rows selected.

5. Bring the tablespace to the online.



SQL> alter tablespace TCB_TSQ01 online;



Tablespace altered.



SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';



FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE

------------------------------------------------------------------ ------------------------------ -------------------- --- ----------

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                TCB_TSQ01                                 .09765625 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32

+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32



6 rows selected.

II) Datafile Offline:
****************
1. Identify the which datafile wants to move to the ASM.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- --- ----------

 /u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01                          TCB_TSQ01                                 .09765625 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

2. Take the datafile offline.


SQL> alter database datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' offline;

Database altered.

3. Copy the datafile to the desired location using RMAN

-bash-4.1$ rman target /


RMAN> copy datafile '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01';


Starting backup at 20-SEP-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2942 instance=CRM2 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00023 name=/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01
output file name=+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477 tag=TAG20150920T123116 RECID=1 STAMP=890915477
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 20-SEP-15

Starting Control File and SPFILE Autobackup at 20-SEP-15
piece handle=+CRM_ARC01/CRM/autobackup/2015_09_20/s_890915478.1003.890915479 comment=NONE
Finished Control File and SPFILE Autobackup at 20-SEP-15

RMAN> exit

Recovery Manager complete.

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

SQL> set pages 200 lines 200
SQL> alter database rename file '/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';


Database altered.

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- --- ----------

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                 TCB_TSQ01                               
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

5. Recover the datafile using RMAN.

RMAN> recover datafile '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477';

6. Bring the datafile to the online.

SQL> alter database datafile '+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477' online;

Tablespace altered.


SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 , AUTOEXTENSIBLE,MAXBYTES/1024/1024/1024 MAXSIZE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TCB_TSQ01';

FILE_NAME                                                          TABLESPACE_NAME                BYTES/1024/1024/1024 AUT    MAXSIZE
------------------------------------------------------------------ ------------------------------ -------------------- -------------

+CRM_DBF01/CRM/datafile/tcb_tsq01.314.890915477                 TCB_TSQ01                                 .09765625 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.317.758819229                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.343.782432985                TCB_TSQ01                                        32 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.349.814014407                TCB_TSQ01                                        20 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.350.814014481                TCB_TSQ01                                      19.5 YES         32
+CRM_DBF01/CRM/datafile/tcb_tsq01.363.846001383                TCB_TSQ01                                9.22265625 YES         32

6 rows selected.

Note:
*****
If your oracle version is 12c you can execute the below statement is enough to move the datafile. No need to perform all the above steps.

SQL>  alter database move datafile
'/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to ' +CRM_DBF01';


Happy Learning :)

No comments:

Post a Comment