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