# Fung's DBA World

## Moving Files in Database

March 24, 2016

As a production DBA, we may meet some file moving requirements, such as move a data file from a filesystem to another filesystem, move redo log files, move control files, even move standard file system to ASM. This topic discuss how to relocate those files in Oracle database and how to migrate the whole tablespace in DB2 database.

### 1. Relocating files in Oracle database

The most important file types in Oracle database are: data file, control file and redo log file, I’ll show the most common approach to relocate these types of file.

#### 1.1 Moving datafiles

In oracle database 11g and earlier, moving datafiles will affect relevant applications or the whole database. As of Oracle database 12c, a new feature can let you moving the datafiles while the tablespace and the database keeping online.

• Moving datafiles in 12c

With 12c new feature, you can move any datafiles without any downtime, and can move all dafafiles in all tablespaces, including SYSTEM AND SYSAUX. It’s also works with ASM diskgroup.

Previous post also can be referred in RAC or ASM environments: RAC Datafile in Local Node .

• Moving datafiles in 11g or earlier

There are two approaches can accomplish this task: with ALTER DATABASE command or with ALTER TABLESPACE command. The difference between the two methods is that ALTER DATABASE need to bring the whole database down, and ALTER TABLESPACE only need to offline the relevant tablespaces.

• Moving datafiles with ALTER DATABASE

This approach needs to shutdown the instance, because it needs to shutdown the instance, this method can move all the datafiles in all tablespaces including SYSTEM,SYSAUX etc., the general steps as below:

• Moving datafiles with ALTER TABLESPACE

This approach cannot move SYSTEM,SYSAUX,active undo tablespace and temporary tablespace. With this method, you can keep your database online except the tablespace which datafiles will be moved. The general steps are as follow:

#### 1.2 Moving the redo log files

Actually, you can just simply delete an entire redo log group and add a new redo log group in a different location Manage Redo .This approach can be used if database needs to be kept open. Below approach is how to do it when the database shut down.

#### 1.3 Moving control files

There are two approaches when moving control files depend on what initial files are you using. When using legacy init file instead of spfile, you need to shutdown the database, move the control file physically, and restart the database.
Below method is about how to move the control file when using spfile.

### 2. Migrate the whole tablespace in DB2 database

Depending on your storage configuration, there are different ways can let you move the tablespace to another location. As you know, DB2 V10.1 provide ALTER TABLESPACE… USING STOGROUP can relocate the whole tablespace to another storage group when using AUTOMATIC STORAGE MANAGEMENT. Following examples shows how to migrate the tablespace with different methods in different circumstance.

#### 2.1 Migrate tablespace in non-automatic storage

There are three methods about moving tablespace in non-automatic storage. Below is the original testing environment.

I’d like to move the tablespace “FUNG” from /data/ to /data2/ file system by using different ways showing as below.

• Adding the new containers without rebalance and delete the old containers

First, add new containers to the target file system, I use begin new stripe set, this means no rebalancing occur when adding new containers, if you want to add containers to an exist stripe set, use  ADD TO STRIPE SET clause.

Then, drop old containers, rebalanceing will occur automatically in background.

We can use table function to monitor the rebalance status.

• Performing a db2relocatedb utility

This is a much quickly way than adding new containers, if you have large data in this tablespace, the rebalancing will last very long time. But relocatedb can minimal the downtime via move physical file directly, this method need to restart the database.

• Redirect restoring the tablespace

This is another optional way, backup images and the downtime are required.

By verifying the result, you can find out the container path already changed.

#### 2.2 Migrate tablespace in automatic storage

It’s very simple while relocating the tablespace to another location when using automatic storage, via ALTER TABLESPACE…USING STOGROUP you can simply specify the storage path which you want to move to. You can also use db2relocatedb, but f you via db2relocatedb tool to relocate the containers, it’s not support CONT_PATH, you need to reloate the whole storage group together.

Assumed my file system “/db2/data/newstorage” is encountered file system full issue, I’d like to move the whole tablespace “USERSPACE1” to another file system “/db2/data/db2inst1”, only 2 steps can accomplish that, and with this method, no need to bring down database, the rebalance will start automatically and will move all tablespace data to the new path in the background.

#### 2.3. How to find out whether the tablespace is automatic storage or not

Many ways can find out whether the databaspace is AS or not.

EOF