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.

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

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

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

Unknown said...

Contact to DB Recovery Support to bring out the Oracle Recovery Problem
Assume you execute any order and in the meantime you discovered blunder has happen then what conceivable techniques you are taking care of this issue? Well! In the first place you need to check and screen what sort of database you are utilizing and after that you need to likewise check which kind of order you are executing. When you check these things and as yet confronting a similar issue at that point rapidly contact to Cognegic's Exchange Database Recovery or DB Recovery Services. Here we give viable answer for these issues.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Unknown said...

Restore your Entire Database with Cognegic's Simple DB Recovery Support
Feeling stressed? Due to by mistakenly deletion of your database? If yes, by then consider our extraordinary among other help i.e. DB Recovery Support or Exchange Database Recovery. Here we give complete Backup Recovery of your entire database including MongoDB, Cassandra, MySQL, and MS SQL Server and so on. Here we will help you to examine, screen and check the execution of your database and give high-availability and streamline your database. You can particularly contact to our specific pros and request your inquiries.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Unknown said...

How to Recover SQL Database through DB Recovery Support
Mostly people asked to recover an Oracle database if it permanently deleted by mistake. If these kinds of question are roaming in your mind then you are at correct platform. You can simply recover your data by manually. You can recover database by database mirror, full database backup stored where your admin chose to be stored. But apart from that you can quickly recover your data by DB Recovery Service or Exchange Database Recovery. At Cognegic, we provide Online Database Management Support to solve your recovery related issue.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Unknown said...

MongoDB Crashed? Contact to DB Recovery Support to Recover your MongoDB Data
The MongoDB crashed due to some technical issues, well if you know the trick and tips to recover this crashed data then it’s wonderful but if you do not know the process then we provide best way to get back your important data. Make sure, you can easily recover your crashed or deleted data with the help of Cognegic’s DB Recovery Service or Exchange Database Recovery. Once you get our Online Database Management Support, then you can easily recover entire MongoDB data.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Unknown said...

The most effective method to Recover Dropped Database on MySQL through DB Recovery Support |Cognegic|
Dropping a database is most happening issue which typically asked by the clients. Be that as it may, recuperating this database is some place vital. In more often than not your IT overseer likewise not ready to recuperate it. That is the reason I am instructing you to recoup your dropped database with the assistance of Cognegic's DB Recovery Support or Exchange Database Recovery. We completely comprehend the issues or difficulties looked by you with respect to information recuperation. By remembering these things we give Backup Recovery to a wide range of databases including MongoDB, Cassandra, and MySQL et cetera.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Unknown said...

Unable to Recover Tables when Deleted by Mistakenly in MS SQL Server? Contact to Online MS SQL Server Support
Lamentably in the event that you erased your database tables however the measure of the documents isn't changed and need to recoup your whole tables at that point rapidly contact to Remote Infrastructure Management Support for Microsoft SQL Server or Microsoft SQL Server Support. Our expert specialists utilize basic strategies to get back your erased tables and recoup at the earliest opportunity. Aside from table recuperation you can likewise get back your entire database in the event that it is erased by erroneously.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

QuickBooks Payroll Support said...

Nice Blog We are providing technical support in Quickbooks Support Phone Number +1-800-986-4607. if you are Expand your business to a new hike, with progressive approach. Seeking for the best accounting software? Then, get QuickBooks installed in your system. The software proves to be more profitable to the business.

yanmaneee said...

nike air max 2019
nmd
mbt
russell westbrook shoes
nba jerseys
golden goose sneakers
nike sneakers for women
louboutin shoes
timberland outlet
nike epic react

QuickBooks Payroll Support said...

Nice article QuickBooks Payroll Support Phone Number 1-800-986-4607 has fascinated the world by its advanced & exclusive features. Need any assistance or facing any issue while using QuickBooks? Don’t hesitate to call us, on Quickbooks Payroll Support Phone Number 1800-986-4607.

deraz said...

خدمات الفجيرة – الروضة
أرقام كهربائي منازل الفجيرة
فني كهربائي منازل الفجيرة

BESTSITE said...

Fantastic blog and great design and style. Good write-up, I am regular visitor of one’s website, maintain up the nice operate, and It’s going to be a regular visitor for a long time.

야한소설

MY SITE said...

We like to honor numerous other world wide web websites around the web, even though they aren't linked to us, by linking to them. Beneath are some web pages really worth checking out.

마사지

VISIT ME said...

I’ve been busy writing my reports. Now, I don’t do it anymore thanks to this service. Go to their homepage. The majority of students are overwhelmed with homework. If you are one of them, click here 건전마사지.

안전한놀이터 said...

I finally found what I was looking for! I'm so happy. 안전한놀이터 Your article is what I've been looking for for a long time. I'm happy to find you like this. Could you visit my website if you have time? I'm sure you'll find a post of interest that you'll find interesting.

totosite said...

Your article has answered the question I was wondering about! I would like to write a thesis on this subject, but I would like you to give your opinion once :D totosite