Friday, October 5, 2012

How to recover from a loss of the SYSTEM tablespace on different location

This is another post on a scenario describing how to proceed when you lose the SYSTEM tablespace: as already said this tablespace always contains the data dictionary tables for the entire database.
In particular this example will restore the lost tablespace to another location, just as you have to restore it because a disk controller is no more working and you have to recreate it (recovering) to a different location.

Next it's a short summary about the loss of SYSTEM tablespace, copied from a previous post.
When you lose the SYSTEM tablespace the instance could crash or hang: anyway the instance will alert you as soon as possible.
It's not rare the case when you cannot even shutdown the instance and proceed with a kill or a shutdown abort command.
If you have a good backup you can of course restore it, but the database could not be open untill the recover process finishes.
No problem for your committed transactions because your production database is always running in ARCHIVELOG mode and they will be available again as soon as the database opens.
Unlike recovery of non–system tablespaces that can be recovered with the database in the OPEN state, the database must be in the MOUNT state to recover either the SYSTEM or UNDO tablespace.

Let's begin simulating the loss of the SYSTEM tablespace. In my case the instance was running, so I shutted it down and...
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
... then removed the system datafile.
[oracle@localhost ~]$ rm /home/oracle/app/oracle/oradata/orcl/system01.dbf
Let's connect using RMAN
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jul 27 07:36:55 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)
The instance was not started and I started it in MOUNT mode to begin the restore/recover process.
RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area     456146944 bytes

Fixed Size                     1344840 bytes
Variable Size                385878712 bytes
Database Buffers              62914560 bytes
Redo Buffers                   6008832 bytes
Using the report schema command, you can see RMAN is not able to correctly know the size of SYSTEM datafile.
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf
Now it's time to restore the datafile to a different location (/home/oracle/app/oracle/oradata/orcl/non_default_location/) compared with the original one (looking at the above report schema output it was: /home/oracle/app/oracle/oradata/orcl/).
To switch a datafile to another location you have to use SET NEWNAME command and include it in a run {...} block.
Moreover it's important to include also before issuing the recover command the SWITCH DATAFILE ALL command.
What does it mean and why we have to execute that command ? From Oracle documentation it "specifies that all data files for which a SET NEWNAME FOR DATAFILE command has been issued in this job are switched to their new name": control file will be so updated with the new location of SYSTEM datafile.
RMAN> run {                
2> set newname for datafile 1 to '/home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf';
3> restore tablespace system;
4> switch datafile all;
5> recover tablespace system;
6> alter database open;
7> }

executing command: SET NEWNAME

Starting restore at 27-07-2012 07:40:21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK

channel ORA_DISK_1: restoring datafile 00001
input datafile copy RECID=17 STAMP=789551585 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_81020mvz_.dbf
destination for restore of datafile 00001: /home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00001
output file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf RECID=23 STAMP=789723694
Finished restore at 27-07-2012 07:41:37

datafile 1 switched to datafile copy
input datafile copy RECID=24 STAMP=789723698 file name=/home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf

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

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

Finished recover at 27-07-2012 07:41:48

database opened
After the end of recovery process, you can report the schema info again. The size of SYSTEM tablespace is again well known and the report schema command shows also a new location of the datafile number 1.
RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/non_default_location/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf

Looking into the /home/oracle/app/oracle/oradata/orcl/non_default_location directory we can find a new file. It's the datafile of the SYSTEM tablespace restored, recovered and finally moved to this different location.
[oracle@localhost non_default_location]$ ll -h
total 832M
-rw-rw---- 1 oracle oracle 832M Jul 27 07:44 system01.dbf
That's all.

28 comments:

Anonymous said...

Hi, I ωoulԁ like tо ѕubsсribe for this wеbsitе to take most гесent updates,
so whегe can i ԁo it pleaѕe help.


My web ѕite ... payday loans
my web site: payday loans

Anonymous said...

Thanκs vеry nice blog!

Have a look at my wеb blog ... instant loans

Anonymous said...

Very quickly this web page will be famous among all blogging and site-building people, due to it's good content

my web blog :: Rvtl Anti aging Formula

Anonymous said...

Very good blog you have here but I was curious about if you knew of any forums that cover the same
topics discussed here? I'd really like to be a part of community where I can get suggestions from other knowledgeable individuals that share the same interest. If you have any suggestions, please let me know. Cheers!

My page: Test Force Muscle Testosterone Booster

Anonymous said...

I was extremely pleased to uncover this page. I need to to thank you for your
time for this particularly wonderful read!! I definitely loved
every little bit of it and i also have you book-marked to check out new things
on your blog.

Here is my website :: Beyond Raspberry Ketone

Anonymous said...

As the admin of this website is working, no doubt very soon it will be famous, due to its quality contents.


Here is my web page: Buy Chronic profits

Anonymous said...

I do not know whether it's just me or if perhaps everybody else encountering issues with your site. It seems like some of the written text on your posts are running off the screen. Can somebody else please comment and let me know if this is happening to them too? This could be a issue with my web browser because I've had
this happen before. Kudos

My web-site - Dermal meds

Anonymous said...

Good way of telling, and pleasant article to take information regarding my presentation
subject matter, which i am going to deliver in institution of higher education.


Here is my page: Warehouse Space

Anonymous said...

Full Article buy propecia cheap online uk - propecia generic australia

Anonymous said...

Lots of grain grass and then lemon or lime blenders
work with by working on this utilizing reamers per augers.
Any individual certainly going to grease veggies in addition to grasses including kale
or a wheatgrass then you may consider a major masticating machine, normally
does a enhanced perform linked with eliminating juice. My
mixer price tag $99.98.

Feel free to surf to my homepage: nutri bullet blender recipes

Anonymous said...

L'Equip A hundred and ten.Five various Small Juicer: The L'Equip Xperia x10 mini is an accomplished condensed centrifugal juicer by means of computerized pulp
removal. Crop, all types of berries, seeds and nuts must mix
together competently hence the the equipment in course
of the amount of food ought to a good deal more in depth additionally efficace inside the operating.
There are additional arguments which weigh
up which substantial juicers around the world might repair machine available to you.
Besides from truly easy to, its powerful and can be
for very many years. Specific Blendtec Over
everything Mixer 621-20 a huge food processor or blender that may possibly do all of it.
Consider an wineglass involving carrot (or a carrot and thus zucchini) beverages
within the morning, and also my fruit juice you will get
pulp which might designed into loaf of bread combine for making brownies,
or even a veggies soups in the future from your morning
,.

My web-site ... buy mixers

Anonymous said...

Immediately have all of your current formulas proper into
a Kenwood vita mixer, donned the type of street bike, depress most of the buttons and see ones own healthy morning drink racing
inside of time! Once you basic goods results in
a very citrus fruit machine, plan much easier to select one which provides most people what you desire after considering acid juicer evaluates.
For the most part machines assist what you would like them comprehensive, delivering malts not to mention rattles.
Circumstance, Waring machines, while the Vita Put together Many hp organization
models that, alongside any number of the Blendtec Commercially made solutions should be marketed
from keepers which are not BPA-Free. Hemorrhoidal inflammation this
really is helpful to ponder should be the
warranty. Per reconditioned Vitamix carries you'll each 5 year assurance.

Also visit my web site - Industrial Blending

Anonymous said...

Hey! I just wanted to ask if you ever have any problems with hackers?

My last blog (wordpress) was hacked and I ended up losing many months of hard work due
to no backup. Do you have any solutions to stop hackers?


Also visit my weblog; Androsolve Review

Anonymous said...

Greetings! Very useful advice in this particular article!

It is the little changes that make the most significant changes.
Thanks for sharing!

Here is my homepage - Christian Louboutin Outlet

santosh tiwary said...

Actually I was searching for tablespace recovery and you found on the top of Google. Thanks for this oracle tips.

Anonymous said...

Piece of writing writing is also a excitement, if you
know then you can write or else it is complicated to write.


Also visit my website payoneer review

Anonymous said...

Hello there! Would you mind if I share your blog with my myspace group?

There's a lot of folks that I think would really
appreciate your content. Please let me know.
Thank you

my webpage: Home Internet Careers review (motomartin.com)

donit k philip said...

hi,
i do have a query on the same.
after we move the datafile to a new location. will the old backup be valid
or do we need to take a new full database backup

Anonymous said...

Wonderful blog! I found it while searching on Yahoo News.
Do you have any tips on how to get listed in Yahoo News?
I've been trying for a while but I never seem to get there!
Many thanks

Here is my blog post - Genki Hentai ()

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...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

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

Amelia Dwi said...

obat pemutih muka alami

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

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

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose