Problem statement:
- How to move data files from one location to another on same storage.
- How to move data files from one storage to another.
- How to rename data files to make data file name standardized
Environment / Scenario:
- You have a database where you have to move your data files from old slower storage to new faster storage
- You want to organize your data files to one location from various locations
- You want to standardized data file name so that you can relate your data files with tablespace just by seeing data files names.
- You have one db on one or multiple storage system where data files are scattered across storage.
Approach:
In any of the above scenario, we are just doing re-organization of data files on both database and Operating System level.
How it works:
To reorganize data files of tablespaces, we first have to change either its path (i.e. move to new location) or its name (i.e. Rename) or both on O/S level by using cp or mv command. Once O/S level reorganization is completed, we then have to reorganize this on database level. Both steps are required and has to be performed in sequence.
To perform these steps tablespace or data files has to be taken offline during this period. As a result, user may face some error like “” if they hit any object which belongs to the same tablespace or data files.
Steps to Perform:
- Login to Database with SYS user or any DBA user
[oracle@TESTServer ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 4 12:53:30 2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
MYDB2
SQL>
- Generate the list of data files for each tablespace(s ) which has to be re-organize. List highlighted in blue color is candidate for re-organization in our case.
SQL> r
1* select tablespace_name, file_name from dba_data_files order by 1 desc
TABLESPACE_NAME FILE_NAME
——————- ————————————————————
UNDOTBS /u03/oracle/oradata/mydb2/undo.dbf
TEST_IND_04 /u03/oracle/oradata/mydb2/TTS/TEST_IND_04_01.dbf
TEST_IND_03 /u03/oracle/oradata/mydb2/TTS/TEST_IND_03_01.dbf
TEST_IND_02 /u03/oracle/oradata/mydb2/TTS/TEST_IND_02_01.dbf
TEST_IND_01 /u03/oracle/oradata/mydb2/TTS/TEST_IND_01_01.dbf
TEST_DATA_04 /u03/oracle/oradata/mydb2/TTS/TEST_DATA_04_01.dbf
TEST_DATA_03 /u03/oracle/oradata/mydb2/TTS/TEST_DATA_03_01.dbf
TEST_DATA_02 /u03/oracle/oradata/mydb2/TTS/TEST_DATA_02_01.dbf
TEST_DATA_01 /u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_01.dbf
TEST_DATA_01 /u03/oracle/oradata/mydb2/t4590.dbf
TEST_DATA_01 /u03/oracle/oradata/mydb2/r546789.dbf
TEST_DATA_01 /u03/oracle/oradata/mydb2/t1_01.dbf
TEST_DATA_01 /u03/oracle/oradata/mydb2/z1_01.dbf
SYSTEM /u03/oracle/oradata/mydb2/sys.dbf
SYSAUX /u03/oracle/oradata/mydb2/sysaux.dbf
15 rows selected.
- We will be considering these 4 data files because two reasons.
- Path of these data files does not matching up. It should include TTS folder it its path
- Naming convention is not correct.
- Hence, Seeing the above list we will be doing both action here i.e. Moving and Renaming.
- Take one of tablespace in offline mode. We can take even one data file offline and perform all the steps required. When you take the tablespace or a data file offline, any operation on those tablespace or that particular data file will not be possible. Hence, perform this step as per your permissible outage.
- We are taking tablespace READ ONLY instead of each data files.
SQL> ALTER TABLESPACE TEST_DATA_01 OFFLINE;
Tablespace altered.
SQL>
- Open another terminal to perform Operating system level activities
- Copy/rename data files of this tablespace to the desired location with desired name.
- Copying files from current location the desired location for re-organization
[oracle@TESTServer mydb2]$cp t1_01.dbf TTS/.
[oracle@TESTServer mydb2]$cp z1_01.dbf TTS/.
[oracle@TESTServer mydb2]$cp t4590.dbf TTS/.
[oracle@TESTServer mydb2]$cp r546789.dbf TTS/.
- Rename file name to make it standarized.
[oracle@TESTServer TTS]$ mv t1_01.dbf TEST_DATA_01_02.dbf
[oracle@TESTServer TTS]$ mv z1_01.dbf TEST_DATA_01_03.dbf
[oracle@TESTServer TTS]$ mv t4590.dbf TEST_DATA_01_04.dbf
[oracle@TESTServer TTS]$ mv r546789.dbf TEST_DATA_01_05.dbf
- We can merge above two steps (cp & mv) into one single step using mv as well. Above two steps mentioned was just for clarity.
- Switch old data files with new one for this tablespace in database using SYS login.
SQL> r
1* alter database
rename file ‘/u03/oracle/oradata/mydb2/t1_01.dbf’
to ‘/u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_02.dbf’
Database altered.
SQL> alter database
rename file ‘/u03/oracle/oradata/mydb2/z1_01.dbf’
to ‘/u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_03.dbf’;
Database altered.
SQL> alter database
rename file ‘/u03/oracle/oradata/mydb2/t4590.dbf’
to ‘/u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_04.dbf’;
Database altered.
SQL> alter database
rename file ‘/u03/oracle/oradata/mydb2/r546789.dbf’
to ‘/u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_05.dbf’;
Database altered.
- Once switching for all data files are done, take the same tablespace online.
SQL> alter tablespace TEST_DATA_01 online;
Tablespace altered.
SQL>
- Perform these steps for all tablespaces one by one.
We can take individual data files offline copy/rename to the desired destination, switching old to new data files in database, and bring the same data file online. Here are example of working with individual data files.
- Take the list of data files for data files or tablespaces to be organize. List in blue color is candidate in our case.
SQL> r
1* select tablespace_name,
file_id,
file_name
from dba_data_files
where tablespace_name=’TEST_DATA_01′
TABLESPACE_NAME FILE_ID FILE_NAME
————— ——- ————————————————–
TEST_DATA_01 4 /u03/oracle/oradata/mydb2/TEST_DATA_01_01.dbf
TEST_DATA_01 12 /u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_02.dbf
TEST_DATA_01 13 /u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_03.dbf
TEST_DATA_01 14 /u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_04.dbf
TEST_DATA_01 15 /u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_05.dbf
SQL>
- Taking individual data files offline. Since file id is 4 for this file, we have used file id 4 here.
SQL> alter database datafile 4 offline;
Database altered.
SQL>
- Copying or Renaming individual data files from O/S. We have used mv here.
[oracle@TESTServer TTS]$ mv /u03/oracle/oradata/mydb2/TEST_DATA_01_01.dbf
/u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_01.dbf
- Switching data files from database.
SQL> alter database
rename file ‘/u03/oracle/oradata/mydb2/TEST_DATA_01_01.dbf’
to ‘/u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_01.dbf’;
Database altered.
SQL>
- Bringing individual data files online. While bringing online a data file we need a media recovery first. Before media recovery, if we try to bring the data file online, it will raise the requirement of media recovery.
SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/u03/oracle/oradata/mydb2/TTS/TEST_DATA_01_01.dbf’
SQL>
- Recovering media.
SQL> recover datafile 4;
Media recovery complete.
SQL>
- Bringing data file online.
SQL> alter database datafile 4 online;
Database altered.
SQL>