Renaming or moving datafile without database Shutdown.

As we know, we can rename or move datafile by shut down the database. What if we want to do it without shut down the database . This is usually required when we have no space left at mount point where datafiles are. To move or rename datafiles

Steps are as:

1. Suppose I want to move my TEST tablespace datafiles.

SQL> select file_name, tablespace_name from dba_data_Files where tablespace_name like 'TEST';

FILE_NAME TABLESPACE_NAME
/opt/oracle/dwh01/dbs/data/test.dbf TEST

2. We want to move test.dbf from “/opt/oracle/dwh01/dbs/data” to “/opt/oracle/dwh01/dbs”.

First check the status of tablespace.

SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE tablespace_name like 'TEST';

TABLESPACE_NAME STATUS
------------------------------ --------
TEST ONLINE

3. To move datafile first we need to make this tablespace offline using.

SQL> ALTER TABLESPACE TEST OFFLINE;

Tablespace altered.

All the datafile in this tablespace will be offline (not accessible). Here we have only one datafile.

Now check the status of tablespace again.

SQL> SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE tablespace_name like 'TEST';

TABLESPACE_NAME STATUS
------------------------------ ---------
TEST OFFLINE

Here user can’t access the data in this tablespace.

4. Now copy the datafile from source location to destination. Keep in mind only copy don’t move.

[oracle@ ~]$ cd /opt/oracle/dwh01/dbs/data
[oracle@~ ]$ cp test.dbf /opt/oracle/dwh01/dbs

Now compare the size of file at source and file at destination location, if same file is copied.

5. Now execute the following command

SQL> alter database rename file ‘/opt/oracle/dwh01/dbs/data/test.dbf' to '/opt/o
racle/dwh01/dbs/test.dbf';

Database altered.

Check the file path in database is updated.

SQL> select file_name, tablespace_name from dba_data_Files where tablespace_name like 'TEST';

FILE_NAME TABLESPACE_NAME
/opt/oracle/dwh01/dbs/test.dbf TEST

If it is ok.

6. Make the tablespace online using.

SQL> alter tablespace test online;

Tablespace altered.

No comments:

Post a Comment