Relocate datafiles

One of my DB server drivers was not enough any more, and I had to use the index driver to store the table datafile, because the app was loading data to database and the required space was large. and now enough space is added to my table driver, so I want to relocate my files from index driver to table driver.

Here are my steps,

Step 1: list all the datafile,

col tablespace_name format a10
col file_name format a55
Select tablespace_name, file_name FROM dba_data_files

you can get the wrong file name if you have own rules to locate files.

Step 2: backup database,
exp, expdp, or RMAN, backup your database before any big change.

Step 3: shutdown database,
SET oracle_sid=testdb
sqlplus /nolog
conn / AS sysdba
shutdown IMMEDIATE;

Step 4: move datafile using brtools or copy using OS level

Step 5: startup mount database
startup MOUNT

Step 6: execute this command to change controlfile for new location of datafile
Alter DATABASE RENAME FILE 'old_file_name' TO 'new_file_name' ;

–note, full name here.

Step 7. start database
Alter DATABASE OPEN;

and then check dba_data_files to verify your datafile name.

Step 8: backup your database. – we backup database once any large change applied to database.

I also note that there is a way to rename file when the database open, the step should be,

Step 1: Alter TABLESPACE tbs_name OFFLINE NORMAL;
Step 2: Rename the datafiles using the operating system.
Step 3: rename datafile
Alter TABLESPACE tbs_name RENAME DATAFILE 'old_file_full_name' to 'new_file_full_name';
Step 4: Alter TABLESPACE tbs_name ONLINE ;
Step 5: backup your database. – we backup database once any large change applied to database.

发表评论?

0 条评论。

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>