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:
******************
4. Update the controfile with the new location of the datafile.
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.
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.
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';
'/u01/oracle/product/db/11.2.0.4/dbs/TCB_TSQ01' to ' +CRM_DBF01';
Happy Learning :)
No comments:
Post a Comment