>> Check your datafile location with their diskgroup names.
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;
TABLESPACE_ FILE_NAME
----------- ---------------------------------------------
USERS +DATA/kkprod/datafile/users.267.962995627
UNDOTBS1 +DATA/kkprod/datafile/undotbs1.264.962995823
SYSAUX +DATA/kkprod/datafile/sysaux.265.962995779
SYSTEM +DATA/kkprod/datafile/system.266.962995733
USERS +DATA/kkprod/datafile/users.269.963077837
BABTS +DATA1/kkprod/datafile/babts.268.963657501
>> I want to move babts datafile from +DATA1 to +DATA diskgroup.
SQL> select name from v$asm_diskgroup;
NAME
---------------
DATA1
DATA
>> Check the status of babts datafile which you are moving.
SQL> select name,status from v$datafile where file#='6'
NAME STATUS
----------------------------------------------------------------- -------
+DATA1/kkprod/datafile/babts.268.963657501 ONLINE
>> Offline the datafile by alter command.
SQL> alter database datafile '+DATA1/kkprod/datafile/babts.268.963657501' offline;
Database altered.
>> When you offline the datafile oracle shows recover status.
SQL> select name,status from v$datafile where file#='6';
NAME STATUS
--------------------------------------------- -------
+DATA1/kkprod/datafile/babts.268.963657501 RECOVER
>> Connect RMAN and copy datafile to another diskgroup.
RMAN> connect target /
connected to target database: KKPROD (DBID=1757363720)
RMAN> copy datafile '+DATA1/kkprod/datafile/babts.268.963657501' to '+DATA';
Starting backup at 25-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA1/kkprod/datafile/babts.268.963657501
output file name= +DATA/kkprod/datafile/babts.268.963661839 tag=TAG20171225T115039 RECID=11 STAMP=963661882
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
Finished backup at 25-DEC-17
Starting Control File and SPFILE Autobackup at 25-DEC-17
piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963661884.274.963661885 comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-17
>> Still file location has not changed.
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;
TABLESPACE FILE_NAME
---------- ---------------------------------------------
USERS +DATA/kkprod/datafile/users.267.962995627
UNDOTBS1 +DATA/kkprod/datafile/undotbs1.264.962995823
SYSAUX +DATA/kkprod/datafile/sysaux.265.962995779
SYSTEM +DATA/kkprod/datafile/system.266.962995733
USERS +DATA/kkprod/datafile/users.269.963077837
BABTS +DATA1/kkprod/datafile/babts.268.963657501
6 rows selected.
>>> Copy datafile LOCATION from above COPY command outputfile name (+DATA/kkprod/datafile/babts.268.963661839) for renaming.
SQL> ALTER DATABASE RENAME FILE '+DATA1/kkprod/datafile/babts.268.963657501' to '+DATA/kkprod/datafile/babts.268.963661839';
Database altered.
>> Now Diskgroup location has changed from +DATA1 TO +DATA.
SQL> SELECT TABLESPACE_NAME,FILE_NAME FROM DBA_DATA_FILES;
TABLESPACE FILE_NAME
---------- ---------------------------------------------
USERS +DATA/kkprod/datafile/users.267.962995627
UNDOTBS1 +DATA/kkprod/datafile/undotbs1.264.962995823
SYSAUX +DATA/kkprod/datafile/sysaux.265.962995779
SYSTEM +DATA/kkprod/datafile/system.266.962995733
USERS +DATA/kkprod/datafile/users.269.963077837
BABTS +DATA/kkprod/datafile/babts.268.963661839
6 rows selected.
>> SWITCH command switches the datafile to the most recent copy of the datafile. You can specify the datafile by name or number.
RMAN> switch datafile '+DATA/kkprod/datafile/babts.268.963661839' to copy;
using target database control file instead of recovery catalog
datafile 6 switched to datafile copy "+DATA/kkprod/datafile/babts.268.963661839"
>> Recover the datafile.
RMAN> recover datafile '+DATA/kkprod/datafile/babts.268.963661839';
Starting recover at 25-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=200 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-DEC-17
>> Check the status of datafile.
SQL> select name,status from v$datafile where file#='6';
NAME STATUS
------------------------------------------- -------
+DATA/kkprod/datafile/babts.268.963661839 OFFLINE
>> Make this datafile online.
SQL> alter database datafile '+DATA/kkprod/datafile/babts.268.963661839' online;
Database altered.
SQL> select name,status from v$datafile where file#='6';
NAME STATUS
------------------------------------------- -------
+DATA/kkprod/datafile/babts.268.963661839 ONLINE
>> For testing purpose You can create table in this datafile tablespace.
Enjoy Reading....
No comments:
Post a Comment