Move datafiles

Sometimes, when all your datafiles are on the same volume, you can have a lack of space and risks fulling it. In that case, you should move some of your DB datafiles (*.dbf) to another volume.

To do so, you have a few steps to follow:

On the primary

1. tablespace with the files to move must be offline
2. move the datafile(s) with the OS command
3. rename the datafile with SQL
4. put the tablespace online

For the example, we'll move files from /volume_01 to /volume_02, which are within the DATA tablespace.

1. Put the tablespace offline:

SQL> ALTER TABLESPACE DATA OFFLINE NORMAL;

2. Move the file(s)

[oracle@lab01 ~]$ mv /volume_01/datafile/data_01.dbf /volume_02/datafile/data_01.dbf

[oracle@lab01 ~]$ mv /volume_01/datafile/data_02.dbf /volume_02/datafile/data_02.dbf

3. SQL rename

SQL>  ALTER TABLESPACE DATA_XX_REPORT RENAME DATAFILE

      '/volume_01/datafile/data_01.dbf', '/volume_01/datafile/data_02.dbf'

      TO

      '/volume_02/datafile/data_01.dbf', '/volume_02/datafile/data_02.dbf';

4. Tablespace back online

SQL> ALTER TABLESPACE DATA_XX_REPORT ONLINE;

On the standby

The datafiles are supposed to be renamed automatically thanks to the parameter STANDBY_FILE_MANAGEMENT='AUTO', but it doesn't... To fix this, it has to be done on the standby as well.
The steps are:

1. Switch off managed recovery
2. Shutdown immediate
3. move the affected datafiles
4. startup mount
5. alter the database standby file management to manual
6. alter database rename files to new location.
7. alter database standby file management to auto.
8. switch on managed recovery.

1. Switch off managed recovery

[oracle@lab01 ~]$ dgmgrl /

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production



Copyright (c) 2000, 2005, Oracle. All rights reserved.



Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> disable configuration

Disable.

 stb file management

SQL> ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;

2. Shutdown immediate

SQL> shutdown immediate

ORA-01109: database not open



Database dismounted.

ORACLE instance shut down.

3.  move the files (OS)
See primary commands

4. Startup mount

SQL> startup mount

ORACLE instance started.



Total System Global Area 8.5899E+10 bytes

Fixed Size      2245688 bytes

Variable Size   1711279048 bytes

Database Buffers  8.4171E+10 bytes

Redo Buffers     14528512 bytes

Database mounted.

6. SQL rename files
As the standby is not open, the alter tablespace command does not work. To do it, use the below command

SQL> alter database rename file

     '/volume_01/datafile/data_01.dbf', '/volume_01/datafile/data_02.dbf'

     TO

     '/volume_02/datafile/data_01.dbf', '/volume_02/datafile/data_02.dbf';

7. AUTO standby file management

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;

8. switch on managed recovery
On the primary:

[oracle@lab01 ~]$ dgmgrl /

DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production



Copyright (c) 2000, 2005, Oracle. All rights reserved.



Welcome to DGMGRL, type "help" for information.

Connected.

DGMGRL> show configuration



Configuration

  Name:                db1

  Enabled:             NO

  Protection Mode:     MaxPerformance

  Fast-Start Failover: DISABLED

  Databases:

    db1_lab02 - Primary database

    db1_lab01 - Physical standby database



Current status for "db1":

DISABLED



DGMGRL> enable configuration;

Enabled.

DGMGRL> show configuration



Configuration

  Name:                db1

  Enabled:             YES

  Protection Mode:     MaxPerformance

  Fast-Start Failover: DISABLED

  Databases:

    db1_lab02 - Primary database

    db1_lab01 - Physical standby database



Current status for "db1":

SUCCESS