Monday, October 8, 2012

How to recover from a loss of the system critical UNDO tablespace on the original location

Here it is another scenario dealing with the loss of a system critical tablespace: today it's UNDO tablespace's turn.

UNDO tablespace stores undo segments, generally used to explicitly (ROLLBACK command) or implicitly (a failed transaction) rollback a transaction, to recreate a read-consistent image and for all kind of recovery purposes.

As well as the SYSTEM tablespace the database must be in the MOUNT state to recover UNDO tablespace.

 Let's begin simulating the loss of the UNDO tablespace: in my case the instance is running and...
[oracle@localhost orcl]$ ps -ef|grep smon
oracle    9247     1  0 07:48 ?        00:00:01 ora_smon_orcl
... I simply remove its UNDO datafile.
[oracle@localhost orcl]$ rm /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
As you can see when I try to insert a row in one table Oracle throws an error stating it's not able to open undotbs01.dbf (data)file.
SQL> insert into  hr.employees (employee_id, last_name, email, hire_date,
job_id)
  2  values (99999, 'pippo', 'pippo@waltdisney.com', sysdate, 'AC_ACCOUNT');
insert into  hr.employees (employee_id, last_name, email, hire_date, job_id)
                *
ERROR at line 1:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
The same information is written in the alert log as well as in a trace file
...
Fri Jul 27 08:09:04 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_9533.trc:
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Fri Jul 27 08:09:04 2012
Checker run found 1 new persistent data failures
...
If you try to recover your UNDO tablespace taking it offline you receive some errors: you cannot take offline that tablespace.
RMAN> sql 'alter tablespace UNDOTBS1 offline immediate';

sql statement: alter tablespace UNDOTBS1 offline immediate
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 07/27/2012 08:36:19
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace
UNDOTBS1 offline immediate
ORA-30042: Cannot offline the undo tablespace
Then it's time to shutdown the instance, but it doesn't work.
RMAN> shutdown immediate;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of shutdown command at 07/27/2012 08:37:01
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
You can at this time kill your instance or issue a SHUTDOWN ABORT command.
RMAN> shutdown abort;

Oracle instance shut down
Your recovery process needs to be started while the database is in MOUNT mode.
RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                390073016 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6008832 bytes
Once the database is mounted you can restore your UNDO tablespace
RMAN> restore tablespace UNDOTBS1;

Starting restore at 27-07-2012 08:38:54
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: restoring datafile 00003
input datafile copy RECID=20 STAMP=789551613 file
name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_undotbs1_81023bm5_.dbf
destination for restore of datafile 00003:
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00003
output file name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf RECID=0
STAMP=0
Finished restore at 27-07-2012 08:38:58
... recover it ...
RMAN> recover tablespace UNDOTBS1;

Starting recover at 27-07-2012 08:39:14
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 27-07-2012 08:39:16
.. and after the recovery process finishes you can finally open your database.
RMAN> alter database open;

database opened
That's all.

17 comments:

Anonymous said...

I’m not that much of a online reader to be honest
but your sites really nice, keep it up! I'll go ahead and bookmark your site to come back later. All the best

Here is my site Bmi Chart Male

Anonymous said...

I’d like to visit your weblog more usually but recently
it seems to be taking ceaselessly to return up. I go to from work, and our connection there is pretty good.
Do you think the problem could possibly be on your finish?


Here is my web site :: how to increase chances of getting pregnant

Anonymous said...

May I just say what a relief to uncover somebody that actually
understands what they're talking about over the internet. You certainly know how to bring a problem to light and make it important. More people must look at this and understand this side of your story. It's surprising you
aren't more popular because you surely possess the gift.

Here is my homepage; how to attract women without spending a fortune

Anonymous said...

Schooling and indoctrination are often confused.
The standard text book response,? might the truth is
be erroneous? but a pupil who can see the error clearly, is marked down like a 'heretic', however a pupil who learns the official right reply just
like a trained parrot, passes the course! Who determines the
'answer'? Hundreds of billions of dollars in corporate profit may possibly be at stake, as with all the erooneous
hypothesis of 'chemical inbalance' in the brain requiring bogus pharmeceutical intervention?
so CAVEAT!

my site: 60 Minute Stamina Torrent Pirate Bay

Anonymous said...

Remedies 1 and 4 are absolutely my preferred ones.

Who knew that eating some of my favorite foods could actually help my
teeth as good as any toothbrush or mouthwash?

Feel free to surf to my webpage; dental pro 7 ingredients

Anonymous said...

My potting shed gets even much more valuable during
our prolonged winters. Post pics of your potting spot! I'd adore to see.

My homepage arrow sheds

Anonymous said...

Aw, this was an incredibly nice post. Taking a few minutes and actual effort to make a
really good article… but what can I say… I hesitate
a whole lot and never seem to get anything done.


Check out my web-site vigrx-plus in korea

Anonymous said...

What, no entry for "cooking oil?" That mystery meat of oils that fees a buck a gallon?
...Actually, I don't wanna know what goes into that. I figure it really is probably the dregs from the vats they make corn, soybean, and canola oil in all mixed together. Ew...

Feel free to visit my webpage - healthy cooking methods meat

Anonymous said...

Today, while I was at work, my cousin stole my iPad and tested to see if
it can survive a 40 foot drop, just so she can be a youtube
sensation. My apple ipad is now broken and she has 83 views.
I know this is totally off topic but I had to share it with someone!


my website :: basyx by hon hvl220

Anonymous said...

Damn, I must have left teh page open to get a while (about 5 hours ) before really posting a comment
lol

Feel free to surf to my web site - herbal premature ejaculation pills review

Anonymous said...

Frequent perception Bravo!!!! Actual food for genuine households just isn't so pricey whenever you contemplate the draw back of the highly processed diet plan. Occasionally the short-sighted solution is not the top on can it be?

my web-site; precision nutrition lean eating for women reviews

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

Malcolm Burrows said...

I have to say that the fix is really convenient and I must say that you have provided a useful guide. If you are interested then follow:- https://applesupportnumber.net/blog/fix-iphone-error-4013/

shysme said...

important site cheap designer bags replica you could check here replica wallets visite site 7a replica bags wholesale