Right,
For anyone else who might come across this post - I am just adding this information for the sake of completeness.
It took me a long time to get this sorted. I booted off of a SystemRescueCD and ran testdisk. It managed to recover my LVM partitions, which I couldn't mount due to damage to the disks. I tried numerous file recovery tools. R-Studio was probably the most helpful, it managed to recover file fragments.
I also ran Scalpel version 2.0. This version allows for specification of minimum file size, which was important in my case since I knew how big the files were. Scalpel took roughly 3 days, but we managed to recover our 5 data files. However without any of the other files (controlfiles, archived redo logs etc) recovery was still a challenge. I managed to create new controlfiles and rebuild the spfile from a previous copy of the pfile. I ended up starting the database with resetlogs. So no far we haven't had any reports of missing data.
We were incredibly lucky. While I would say that even in the worst imaginable scenario there might be hope for recovery, it is in no way an excuse to eschew prescribed backup methods. I cannot overstate how lucky we were that we managed to salvage our data, after having to deal with a malfunctioning RAID controller, corrupt partitions and missing files. My employers have now taken a more serious and pragmatic view towards offsite backups. but I wouldn't want to see anyone in this position. Like they say - prevention is better than cure.
For reference :
Oracle Data File Headers in Hex : 00 A2 00 00 FF C0 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? 7A 7B 7C 7D
Oracle Data File Footer : 00 00 00 01
The question marks represent characters which will be different for every data file. Scalpel allows you to specify wildcards.
R-Studio also allows defining a specific file type and searching for that file type, but I didn't try it.
Thanks to everyone who took time to share their advice. I am grateful to you all.
Regards
Alex
-- create a new UNDO tablespace named UNDOTBS2
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/01/oradata/dbname/undotbs201.dbf'
SIZE 50M AUTOEXTEND ON NEXT 5M;
Tablespace created.
-- Switch the database to the new UNDO tablespace.
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
-- Drop the old one
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
-- If Undo is still in use
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
I would try this out first on a test database:
(Following from dead link) from original answer:
SELECT a.name,b.status
FROM v$rollname a,v$rollstat b
WHERE a.usn = b.usn
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS1'
);
The above query shows the name of the UNDO segment in the UNDOTBS1 tablespace and its status. See which users/sessions are running this pending transaction:
SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE a.usn = b.usn
AND a.usn = c.xidusn
AND c.ses_addr = d.saddr
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS1'
);
You can then kill the session; the following assumes a SID of 147 and a SERIAL# of 4:
alter system kill session '147,4' immediate;
Best Answer
I will move all pdb data files,undo and create temp file into new directory.You can skip redo log files as they are fixed ,in Oracle Developer Day VM total size of Redo group files is 600M.
To move data files online execute sql command