Thursday, January 23, 2014

How to move or rename a datafile of a tablespace in oracle database

Datafile movement 12c Database Code

ALTER DATABASE MOVE DATAFILE '+DATA/orcl/datafile/orcl_custom.702.836915343' TO '+DATA_BKP/orcl/datafile/orcl_custom.702.836915343';


In 12c Database you need not have to make the datafile offline to do the changes. You can do it online. It is a new feature. 

For other version you need to follow the below steps.

step1: Bring the datafile offline
ALTER DATABASE DATAFILE '+DATA/orcl/datafile/orcl_custom.702.836915343' OFFLINE;
step 2: Nove the datafile to new location
rman target /
COPY DATAFILE '+DATA/orcl/datafile/orcl_custom.702.836915343' TO '+DATA_BKP';
step 3: Rename the datafile with new details with alter database  
ALTER DATABASE RENAME FILE '+DATA/orcl/datafile/orcl_custom.702.836915343'
TO '+DATA_BKP/orcl/datafile/';
step 4: Make a copy of datafile
rman target /
SWITCH DATAFILE '+DATA_BKP/orcl/datafile/' TO COPY;
step 5: Recover the datafile
RECOVER DATAFILE '+DATA_BKP/orcl/datafile/';
step 6: Bring the datafile online
ALTER DATABASE DATAFILE '+DATA_BKP/orcl/datafile/' ONLINE;

No comments:

Post a Comment

Troubleshooting ACFS-07981: Metadata Validation Errors

  Troubleshooting ACFS-07981: Metadata Validation Errors Introduction The ACFS-07981 error indicates that an attempt to run an online file ...