Home » RDBMS Server » Backup & Recovery » Tablespace OFFLINE - Drop tables + clear UNDO&TEMPORARY - Tablespace ONLINE - HANGS! blocked by (Oracle 10g, 10.2.0.1.0, Windows 2003 Server (64bit))
icon5.gif  Tablespace OFFLINE - Drop tables + clear UNDO&TEMPORARY - Tablespace ONLINE - HANGS! blocked by [message #483925] Thu, 25 November 2010 10:11 Go to next message
mihaiv
Messages: 6
Registered: May 2007
Location: Bucharest
Junior Member
Hello,

I have this strange problem that has caused me some good headaches.

It all started when USERS01 and USERS02 data files got enlarged to the maximum of 32Gb supported by 2003 server, in configuration of 8k per data block, and the system was freezing, due to Oracle trying to enlarge the files.

So i wanted to perform some activities that will free up some space on tablespace USERS.

What i did:

0. SHUTDOWN ABORT;
1. started up the database again and immediately took the tablespace users offline;
2. ALTER TABLESPACE users OFFLINE;
3. cleared the UNDO (34Gb) and TEMPORARY (15GB)
4. prepared scripts based on DBA_SEGMENTS to ALTER TABLE xxx.xxxx MOVE TABLESPACE; (and these scripts i saved so to run them when i will have brought the tablespace online again)
5. I then thought, heck.. let's try to drop some tables, maybe it will do the job (since it's offline and i can easily work on it). So:

DROP TABLE etc. etc.;
DROP INDEX etc. etc.;

6. I then want to bring the tablespace USERS back online:

ALTER TABLESPACE users ONLINE;


Here the tragedy happens:

a) Nothing in the alert.log
b) My session (which launched the #6) is blocked by SMON.

The maximum time i've allowed this command to run is 10 days. After 10 days, still the same picture... by session is blocked by SMON and tablespace is still offline.


I now realize that SMON is trying to recover the tablespace USERS, since it detects that its structure has been affected... but hey, no UNDO, no TEMP, just REDO.
And it keeps running like this day and night, nothing happens.

I even got to the point of replicating this Oracle to another similar server:
- copied control file, datafiles, redo log, pfile started Oracle issued alter tablespace users online --> SAME ISSUE.

What could i try, in order to recover my data?

Database was running in NOARCHIVELOG, and i have no recent full backup, only partial backups (which are quite old) and are not covering my whole data.

Any suggestion is so welcomed.

Thank you,
Mihai
Re: Tablespace OFFLINE - Drop tables + clear UNDO&TEMPORARY - Tablespace ONLINE - HANGS! blocked [message #483947 is a reply to message #483925] Thu, 25 November 2010 15:46 Go to previous messageGo to next message
John Watson
Messages: 8501
Registered: January 2010
Location: Global Village
Senior Member
Hi - as no-one else has tried to answer, I'll have go. But first, you have to provide more information.
In your step (3), you say you "cleared" your undo and temporary space. What did you do?
You say that your session is "blocked by SMON". What do you mean by this? Have you identified a lock or a wait event?
With regard to your backups, are you aware that a "partial" backup is useless in noarchivelog mode? Not just partially useless, but totally useless.
I have just done what you did: dropped a table in an offline tablespace, and brought the tablespace back online. No problem. But I tested on 11.2, in archivelog mode - so perhaps the behaviour is different.
Re: Tablespace OFFLINE - Drop tables + clear UNDO&TEMPORARY - Tablespace ONLINE - HANGS! blocked [message #483977 is a reply to message #483947] Fri, 26 November 2010 02:40 Go to previous messageGo to next message
mihaiv
Messages: 6
Registered: May 2007
Location: Bucharest
Junior Member
Hi John,

Thanks for your input.

1. Cleared UNDO and TEMPORARY. I used the classic method:
a) Created new UNDO tablespace + different datafile (UNDOTBS2)
b) Waited (or killed) pending transactions so no one is using undo;
c) then changed the default undo temporary tablespace). --> default: UNDOTBS2
d) dropped the UNDOTBS including contents and datafiles
e) created again the UNDOTBS
f) changed back default undo to UNDOTBS
g) dropped UNDOTBS2

The same i have applied for temporary tablespace.


2. Blocked by SMON, i will let pictures speak.

See top performance as showed by Enterprise Manager:
http://img19.imageshack.us/img19/9679/smonblocking.png

and also, see blocking sessions:
http://img638.imageshack.us/img638/1447/blockingsessions.png


3. Yes, regarding backups, I agree, you are right ...are useless.

4. I'll be damned... it never crossed my mind to try same scenario, on same database. At least to validate it or not... shoot, i've done it, and it works flawless...
a) create new tablespace (set it to default)
b) create new user
c) create new table using this user
d) bring tablespace offline
e) drop table
f) bring tablespace online
..it works!

I have no clue what really caused my problem... or it now comes to my mind it might be because of the datafiles??
It should throw an error (at least something in alert.log) but nothing.

This subject has been debated also in Oracle forums, but no answer has yet been provided... the problem started with the datafiles being extended by Oracle to a greater size that could 2003 server support (for 8bit/block).
At once, i remember, Oracle threw an error: that datafile is inaccesible (tablespace was online at that moment).

But still... i ran out of ideas. Smile

Any new ideas?
Thanks,
Mihai
Re: Tablespace OFFLINE - Drop tables + clear UNDO&TEMPORARY - Tablespace ONLINE - HANGS! blocked [message #484112 is a reply to message #483977] Sat, 27 November 2010 06:50 Go to previous message
John Watson
Messages: 8501
Registered: January 2010
Location: Global Village
Senior Member
Well, according to these screen shots, SMON isn't blocking anything: your session (number 135) is blocking SMON (which is session 164). You are are also blocking sessions 128 and 161. I'm sure you have looked up the "row cache objects" latch in Metalink, but there isn't a lot there. If you really did try to extend a datafile to beyond 32G, heaven knows what might happen.
Sorry I can't suggest anything, better raise an SR if it is a problem you need to solve.
Previous Topic: RMAN related views
Next Topic: db 10gr2,windows xp
Goto Forum:
  


Current Time: Sun Mar 07 21:51:53 CST 2021