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.

work@singapore [29 July 2007 - 4 August 2007]

3.00am and we are still working in our customer’s office. It has been like this since Sunday, July 29^th , the day we arrived from KL.

 

Today is the 6^th day we are working till the next morning. My colleagues are busy fixing program bugs and I am writing this blog. My brain is so jammed up that I can’t think anymore. But there are still issues which we need to settle or fix in our system before we cut over. Incorrect business logic, updating the wrong data, data patching, program/SQL fine tuning, etc.

 

The worse thing was tonight, the live Database (DB) server was not responding. Via telnet and using nmon, I could see that the four processors are idling at 96 to 100%. Strange. The server was not busy but loging in to our system, SQL Tools and Oracle’s Enterprise Manager was taking hours. No errors were thrown.

 

I logged in to Oracle’s SQL Plus and did some queries and it responded well. I restarted Oracle’s services but still the same.

 

It was obvious that it was neither the DB server’s problem nor the Application server’s. Network connection was up. I could ping and telnet to the DB server.

 

A quick call to my colleague back in KL and a few trouble shooting with him on the phone, we managed to find the source of the problem. Oracle’s listener’s (lsnrctl) object was not responding. Even after I stopped and re-started it.

 

The only solution right now was to force the server to reboot. Rebooting will always be our last option.

 

I had to use the command Shutdown -Fr [F - Force; r - reboot] via telnet to reboot the server because it is located at warehouse in Changi and currently there are no IT Administrator there to assist us.

 

After the bootup and starting the listener and Oracle DB, everything was back to normal. Damn! We do not why the listener failed to respond and I bet my Project Manager and customer would like an answer too. This problem we will have to refer to our Oracle Consultants.

 

p/s: If you know what could be the cause for the listener to fail or the path of the log file which I can check, do
let me know. I am not a DBA but just a Java programmer. I am slowly picking up Oracle because our company has now
Oracle DBA.