Friday, January 24, 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

Command to do active duplicate for Oracle Database

1. First login to target server 2. Validate tns connectivity between Source DB and Target DB 3. Prepare and validate space availability 4. S...