Moving Oracle Tablespaces

Our customers are in the midst of reallocating their UAT DB server’s volume group. There are currently two volume groups – rootvg, datavg. And temporarily, the datavg has to be removed.

Inside the datavg volume group are two directories – /oraBackup and /oradata. The oraBackup directory contains dump files and the datafiles are in the /oradata directory.

The files inside the oraBackup directory aren’t very important and they could be copy/paste or moved to another directory.

But, to move the datafiles is not that easy. We have to move the datafiles from /oradata to /oradata2 (rootvg). Below are the steps for the reallocation.

1. Backup
   • Before doing anything to the database, always backup! We used RMAN to backup the database.

    $> rman target /
   Recovery Manager: Release 10.2.0.3.0 - Production on Fri May 30 16:37:24 2008
   Copyright (c) 1982, 2005, Oracle.  All rights reserved.
   connected to target database: PROFIT (DBID=3292098349)
   RMAN> backup database plus archivelog delete input;

2. Non-Oracle Datafiles
   • Bring the tablespace offline

   SQL> alter tablespace USERS offline;

   • Copy the datafile to a new location. Always copy instead of move just in case something happened to the datafile.

   $ cp /oradata/USERS01.dbf /oradata2

   • Rename the datafile

   SQL> alter tablespace USERS
     2  rename datafile '/oradata/USERS01.dbf'
     3  to '/oradata2/USERS02.dbf';

3. Temporary Tablespace

   Create new tablespace in the new directory
   SQL> create temporary tablespace TEMP02
   2  tempfile '/oradata2/TEMP02.dbf'
   3  size 50M
   4  autoextend on 50M maxsize unlimited
   5  extent management local default;

   • Set this temporary tablespace as the default tablespace
     SQL> alter database default temporary tablespace TEMP02;
4. Copy/Move SYSTEM/SYSAUX/UNDOTBS1 Tablespaces
   • Database must be in mount state

   SQL> shutdown immediate;
   Database closed.
   Database dismounted.
   ORACLE instance shut down.
   SQL> startup mount;
   ORACLE instance started.
   
   Total System Global Area  612368384 bytes
   Fixed Size                  1292036 bytes
   Variable Size             348129532 bytes
   Database Buffers          255852544 bytes
   Redo Buffers                7094272 bytes
   Database mounted.

• • Copy all the affected datafiles to the new directory

  $ cp /oradata/SYSTEM01.DBF /oradata2/SYSTEM01.DBF

  • Rename the datafiles

  SQL> alter database
    2  rename file '/oradata/SYSTEM01.DBF'
    3  to '/oradata2/SYSTEM01.DBF';

  • Do the same for the other datafiles
  • Open the database

  SQL> alter database open;
  Database altered.

ORA 00257

While importing an Oracle’s dump file (15Gb) to one of our client’s schemas, I encountered a problem. My import script has the feedback parameter turned on for every 100,000 records. After an hour, my import was stuck. It should have gone through but somehow it didn’t for the last 200,000 thousand records.

I opened up a telnet session and logged into SQLPlus and executed “ALTER SYSTEM SWITCH LOGFILE”. It was stuck as well.

I then fired up TOAD and tried to connect to the database but it failed with ORA-00257.

So I googled it up and people in forums were saying that the disc space where the flash recovery area is is
full. So I did a df -k (Unix server) and checked the volume group still has 20% free space – around 10Gb. The volume group still has space. So, why am I getting ORA-00257?

I login to RMAN to remove obsolete and expired backups. But the imp I executed was still stuck.

The current retention policy was 3, so I changed it to 1 and removed obsolete backups again.

As a final solution, I jarred the archivelog directory and moved it to another directory and was about to execute the command DELETE ARCHIVELOG ALL;” in RMAN when suddenly I read somewhere that it’s not stating the disc space is full, but the lash Recovery Area is full.

This Flash Recovery Area size is set in Oracle’s parameter. I quickly logged into SQLPlus and did a “SHOW PARAMETER recovery;”.

The size set was 30G. So, I went to google again to look for the command to change the size to 35G. “ALTER SYSTEM SET db_recovery_file_dest_size=35G scope=both;”

Then I checked the imp function again and it continued to run. I then quickly started moving other miscellaneous files in that volume group to a different volume group and increasing the db_recovery_file_dest_size bit by bit every time ORA-00257 happened.

After finishing the import the FRA current size was 53Gb. The same error happened again when our users were using the system. Our application couldn’t login to the database. It was throwing out ORA-00257 again. So, the quickest solution then was to increase the db_recovery_file_dest_size to a bigger size and then figure out on a new destination for the flash recovery area so that a full database backup could be done.

What I should have before importing the dump file that night was to do a full backup of the database using RMAN plus the archive log files. This way, the older and obsolete archive logs could be removed to safe space.

Well, the point to this is actually ORA-00257 doesn’t not necessarily means the disc space is full. It could me the recovery destination has reached its maximum space allocated.