In this post, we will see how we can move Files related to the database from one location to the other. This activity might be needed, if the database files and application related files are kept in the same directory and we need to separate DB related files from Application files

So, we will be changing the files location to a separate mount point which would be maintained only for DB related files.

This activity needs to be performed in 2 steps.

1.  Online File Movement

2.  Offline File Movement

 

Please make a hard and fast rule, that before performing any activity on your database, take a Full Backup of the concerned db. Once the backup is done, perform the below steps :

ONLINE FILE MOVEMENT

We can move the below files online without shutting down your database :

  • Datafiles 
  • Redo Logs

Datafiles

So let us first move the datafiles

Let us first gather the details of datafiles present in out database :

SQL> SET LINESIZE 100
SQL> set lines 1200 pages 1200
SQL> COLUMN name FORMAT A70

SQL> SELECT file#, name FROM v$datafile ORDER BY file#;

FILE#     NAME
———- ———————————————————————-
1           /opt/oracle/oradata/infra/system01.dbf
2           /opt/oracle/oradata/infra/sysaux01.dbf
3          /opt/oracle/oradata/infra/undotbs01.dbf
4          /opt/oracle/oradata/infra/users01.dbf
5          /opt/oracle/oradata/infra/infra_data01.dbf
6          /opt/oracle/oradata/infra/infra_data02.dbf
7          /opt/oracle/oradata/infra/infraxpertzz01.dbf
8          /opt/oracle/oradata/infra/infra_data03.dbf
9          /opt/oracle/oradata/infra/infra_test01.dbf
10        /opt/oracle/oradata/infra/user_data_01.dbf
11        /opt/oracle/oradata/infra/sysaux02.dbf
12        /opt/oracle/oradata/infra/infra_data04.dbf
13        /opt/oracle/oradata/infra/infra_data05.dbf

Now let us move the datafiles online to another mount point :

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/system01.dbf’ TO ‘/infra_DATA01/oradata/system01.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/sysaux01.dbf’ TO ‘/infra_DATA01/oradata/sysaux01.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/undotbs01.dbf’ TO ‘/infra_DATA01/oradata/undotbs01.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/users01.dbf’ TO ‘/infra_DATA01/oradata/users01.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/infra_data01.dbf’ TO ‘/infra_DATA01/oradata/infra_data01.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/infra_data02.dbf’ TO ‘/infra_DATA02/oradata/infra_data02.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/infraxpertzz01.dbf’ TO ‘/infra_DATA02/oradata/infraxpertzz01.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/infra_data03.dbf’ TO ‘/infra_DATA02/oradata/infra_data03.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/infra_test01.dbf’ TO ‘/infra_DATA02/oradata/infra_test01.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/user_data_01.dbf’ TO ‘/infra_DATA02/oradata/user_data_01.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/sysaux02.dbf’ TO ‘/infra_DATA02/oradata/sysaux02.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/infra_data04.dbf’ TO ‘/infra_DATA02/oradata/infra_data04.dbf’;

SQL> ALTER DATABASE MOVE DATAFILE ‘/opt/oracle/oradata/infra/infra_data05.dbf’ TO ‘/infra_DATA02/oradata/infra_data05.dbf’;

 

Redo Logs :

Now, since our datafiles have been moved to a new location, let us move the redo logs too

SQL> select member from v$logfile;

MEMBER
——————————————————————————–
/opt/oracle/oradata/infra/redo01a.log
/opt_new/oracle/oradata/infra/redo01b.log
/opt/oracle/oradata/infra/redo02a.log
/opt_new/oracle/oradata/infra/redo02b.log
/opt/oracle/oradata/infra/redo03a.log
/opt_new/oracle/oradata/infra/redo03b.log

6 rows selected.

 

SQL> select group#,members from v$log;

GROUP# MEMBERS
———-    ———-
1               2
2               2 
3              2

SQL> ALTER system switch logfile;

SQL> select members,group#,status from v$log;
MEMBERS    GROUP#     STATUS
———-         ———-        ————————————————
2                      1              INACTIVE
2                      2             CURRENT
2                      3             INACTIVE

Now, we can drop the Logfile Groups which are inactive :

SQL> ALTER database drop logfile group 1;

SQL> ALTER database drop logfile group 3;

Remove the logfiles (group 1,3) physically from the location and add the dropped Logfile Groups to a new location :

SQL> ALTER database add logfile group 1 (‘/infra_DATA01/oradata/redo/redo01a.log’,’/infra_DATA02/oradata/redo/redo01b.log’) size 201M;

SQL> ALTER database add logfile group 3 (‘/infra_DATA01/oradata/redo/redo03a.log’,’/infra_DATA02/oradata/redo/redo03b.log’) size 201M;

Repeat the same activity for Logfile Group 2, once the status changes to INACTIVE

So after this we are done with the online activity of moving our Logfiles and Datafiles

 

OFFLINE FILE MOVEMENT

Let us now proceed with Offline File movement. Here offline means, the Application shouldnt connect to the database, since the Database needs to be bounced to make the changes effective

We need to move the below files during this activity :

  • Temp files 
  • Control Files
  • Archive logs

Temp Files

So let us gather the details of temp files and move the same

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;

FILE#      FILE_NAME                                                   STATUS
———- ———————————————                 ———————
1           /opt/oracle/oradata/INFRA/temp01.dbf     ONLINE

SQL> ALTER DATABASE TEMPFILE ‘/opt/oracle/oradata/INFRA/temp01.dbf’ OFFLINE;

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#; –> Verify whether the tempfile is offline or not

$cp -p /opt/oracle/oradata/INFRA/temp01.dbf /INFRA_DATA02/oradata/temp01.dbf

SQL> ALTER DATABASE RENAME FILE ‘/opt/oracle/oradata/INFRA/temp01.dbf’ to ‘/INFRA_DATA02/oradata/temp01.dbf’;

SQL> ALTER DATABASE TEMPFILE ‘/INFRA_DATA02/oradata/temp01.dbf’ online;

SQL> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#; –> Verify whether the tempfile is online or not

Remove the old temp file

$rm -rf /opt/oracle/oradata/INFRA/temp01.dbf

 

Control Files 

Shutdown the database before performing this activity 

Copy the control files from the old mount point to the desired file system and perform the below steps :

$cp /opt/oracle/oradata/INFRA/control01.ctl /INFRA_DATA01/oradata/control/control01.ctl
$cp /opt_new/oracle/oradata/INFRA/control02.ctl /INFRA_DATA02/oradata/control/control02.ctl

SQL> startup nomount

SQL> ALTER system set control_files=’/INFRA_DATA01/oradata/control/control01.ctl’,’/INFRA_DATA02/oradata/control/control02.ctl’ scope=spfile;
SQL> shut immediate
SQL> startup nomount
SQL> show parameter control_files –> to check whether it is reflecting to the new mount point or not
SQL> ALTER database mount;
SQL> ALTER database open;

 

Archive logs

We need to bounce the database to make changes effective for archive log change

 

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/archive/INFRA

 

SQL> create pfile from spfile;

Edit pfile :

$vi /d01/oracle/product/v.12.2.0.1/dbs/initINFRA.ora

change the *.log_archive_dest_1 location from ‘/opt/oracle/archive/INFRA’ to ‘/INFRA_DATA03/archivelog’

SQL> startup

 

Verify whether all files and logs have been moved to the new location before handing over the database to Application Team