Monthly Archives: July 2010

Transportable Tablespace from rman backup

Most of oracle’s MAA documentation for transportable tablespaces, seems to recommend that one should put the tablespaces one is transporting, in read-only mode in the source database, before copying the datafiles to the target. This in most cases means application downtime.

In order to minimize the downtime the recommendations seem to be

  • Create a dataguard physical standby database and use this standby database as the source for transport
  • Create a duplicate (aka clone) of your source and use this new duplicate as the source for transport

If you are only transporting a subset of your tablespaces and you want to minimize your downtime, a 3rd good option is to create and use transportable tablespace sets from your already existing rman backups. This process is documented in the Backup and Recovery users guide, Chapter 26 (11gR2 manual).

Since this process uses an existing rman backup you incur no downtime (ie no need to place tablespaces in read-only mode) on your production systems.

Below are the steps to accomplish this (In my example rk01 is the source database and rk02 is the target database).

  • First off, your source database should be running in archivelog mode
  • Take a full database backup from your source database

export ORACLE_SID=rk01
rman target /

run {
allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’;
backup as BACKUPSET tag ‘%TAG’ database;
backup as BACKUPSET tag ‘%TAG’ archivelog all not backed up;
release channel oem_backup_disk1;
run {
allocate channel oem_backup_disk1 type disk format ‘/u01/orarch/rk01/backup/%U’ maxpiecesize 1000 G;
backup as BACKUPSET tag ‘%TAG’ current controlfile;
release channel oem_backup_disk1;


  • Create a transportable tablespace set for the tablespaces you need to transport

export ORACLE_SID=rk01

rman target /

RMAN>  transport tablespace example

2> tablespace destination ‘/u01/orarch/rk01/datafile’

3> auxiliary destination ‘/u01/orarch/rk01/tmp’;

Once the process is complete rman leaves a copy of the datafile (An operating system file, not a backup set file) for the tablespace example , in the directory /u01/orarch/rk01/datafile. It also leaves a export dump file that has the metadata needed for the transport in the same directory /u01/orarch/rk01/datafile.

  • Do endianness conversions on the files, if you need to go cross platform (Use rman convert)
  • Attach the tablespace to your target database

export ORACLE_SID=rk02

sqlplus / as sysdba

create directory tts_dir as ‘/u01/orarch/rk01/datafile’


grant all on directory tts_dir to public



Before you run the next import, make sure that you have created the schema’s (with appropriate privileges) that are in the tablespace you are transporting in the target database rk02, also make sure any roles that are required are created in the target. Eg: The schema HR has objects in the example tablespace. use the create user command to create the HR user with appropriate privileges in the database rk02.

impdp system/manager dumpfile=dmpfile.dmp directory=tts_dir transport_datafiles=/u01/oradata/rk02/example01.dbf logfile=tts_import.log

So As you can see the whole process is executed without shutting down the source database rk01.

Howto Add a new virtual disk to a virtual box, virtual machine

Once i built a oracle enterprise linux virtual machine, using oracle virtual box (opensource destop virtualization software), i wanted to increase capacity by adding a new virtual disk to the configuration. My original configuration was built using only one virtual disk and this was mounted as the / file system. Now i want to add a new file system /u01 on a new virtual disk. Below are the steps i performed to accomplish this.

First create a new virtual disk using virtual box
Applications -> System Tools -> Oracle VM Virtual Box
File -> Virtual Media Manager
You will see the window below

Click on New to create a New disk. The New Disk creation wizard starts up.

Click on Next

Choose Dynamically expanding storage, Click Next

Choose the location and file name for the virtual box, vdi file. I chose the same directory, where my first vdi file was located (/u02/vimage/oel55). Also choose the size of the new disk you need. Click Next.

The Summary Screen displays summary information regarding the new disk you are configuring. click next

Now add the new virtual disk to your operating system image

Applications -> System Tools -> Oracle VM Virtual Box

Click on the image that you want to add the virtual disk to.

On the right hand side click on Storage

Choose the SATA controller and click on the + button to add a new disk

You can edit the new disk it creates by click on the disk Name and editing the disks properties on the right most window. (Use the properties to pick the new disk you created in the previous step, and give it a new name).

Now once you boot up your image you will see a new disk at your operating system level. In my case the original disk i had was /dev/sda. The new disk i added showed up as /dev/sdb. I partitioned the disk, formatted it, mounted it and added an entry to /etc/fstab so that every time i startup the image this file system will be mounted.

fdisk /dev/sdb

mkfs -t ext3 /dev/sdb1

mkdir /u01

Add the following entry into /etc/fstab

/dev/sdb1 /u01 ext3 defaults 0 2

Then run the command “mount” from the prompt and /u01 will be mounted.