Wednesday, September 26, 2012

How to recover from a loss of a non-system tablespace

The following scenario will describe how to proceed when you lose a non-system tablespace, I mean not the SYSTEM and UNDO tablespace. In particular this example will restore the lost tablespace to its original location. When you lose a non-system tablespace you cannot access and query only objects that were created on their datafiles;
meanwhile users can continue to query and use all the others objects in the database and you can restore it while the database is open.
Moreover because your database is running in ARCHIVELOG mode any committed transactions don't need to be inserted again.
Let's simulate a loss of the EXAMPLE tablespace, in my case formed by only one datafile:
[oracle@localhost ~]$ ll /home/oracle/app/oracle/oradata/orcl/example01.dbf*
-rw-rw---- 1 oracle oracle 85991424 Jul 26 06:34 /home/oracle/app/oracle/oradata/orcl/example01.dbf
[oracle@localhost ~]$ mv /home/oracle/app/oracle/oradata/orcl/example01.dbf /home/oracle/app/oracle/oradata/orcl/example01.dbf.bck
The database is still open and I query for the very first time an object located on the EXAMPLE tablespace. I receive an error stating the instance was not able to open the example01.dbf (data)file
SQL> select count(*) from ix.ORDERS_QUEUETABLE;
select count(*) from ix.ORDERS_QUEUETABLE
  *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
If you look at the alert log the same error and a trace file are generated
...
Thu Jul 26 06:49:51 2012
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_6858.trc:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Thu Jul 26 06:49:53 2012
Checker run found 1 new persistent data failures
...
As you can see the objects located on EXAMPLE tablespace are no more available. So it's time to recover our tablespace: I'd like to remind you that all the following steps are executed while the database is OPEN as you can see even from the screen log of rman console (connected to target database: ORCL (DBID=1229390655)):
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Sep 25 23:09:23 2012

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

connected to target database: ORCL (DBID=1229390655)
First thing to do is to put the lost tablespace OFFLINE.
RMAN> sql 'alter tablespace example offline immediate';

sql statement: alter tablespace example offline immediate
Second step is to issue the restore command providing the name of your lost tablespace
RMAN> restore tablespace example;

Starting restore at 26-07-2012 06:54:15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=22 STAMP=789633285 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_812kvfk3_.dbf
destination for restore of datafile 00005: /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf RECID=0 STAMP=0
Finished restore at 26-07-2012 06:54:19
After your tablespace was restored from your backup pieces it's time to execute the recover command.
RMAN> recover tablespace example;

Starting recover at 26-07-2012 06:54:27
using channel ORA_DISK_1

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

Finished recover at 26-07-2012 06:54:29
The tablespace is now recovered and you can put it again online and available for your users.
RMAN> sql 'alter tablespace example online';

sql statement: alter tablespace example online
An extract of the alert log taken during the restore and recover process...
...
Thu Jul 26 06:53:38 2012
alter tablespace example offline immediate
Completed: alter tablespace example offline immediate
Thu Jul 26 06:54:16 2012
Checker run found 2 new persistent data failures
Thu Jul 26 06:54:18 2012
Restore of datafile copy /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_812kvfk3_.dbf complete to datafile 5 /home/oracle/app/oracle/oradata/orcl/example01.dbf
checkpoint is 13657207
Thu Jul 26 06:54:28 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace EXAMPLE
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
tablespace EXAMPLE
Thu Jul 26 06:54:40 2012
alter tablespace example online
Completed: alter tablespace example online
...
That's all.

212 comments:

«Oldest   ‹Older   201 – 212 of 212
sophieturner said...

Thanks for sharing such important information which is very useful .I really appreciate your way of work.
123.hp.com/setup | HP Printer Assistant Software | 123.hp.com/Laserjet | 123.hp.com/envy5540 | 123.hp.com/oj4650

Printer Customer Service said...
This comment has been removed by the author.
ava said...

Thank you so much for this wonderful Post and all the best for your future.
HP Printer in error state |
HP printer offline windows 10 |
HP officejet 4650 not printing |
How to fix HP Printer not responding |
HP Printer not printing |

Printer Support said...

In this digital world, everyone is using printers no matter whether their business is big or small. Lexmark provides versatile printers which embedded with latest and advanced technology. Lexmark manufactures high speed printers which have several options to print any document. Regardless having excellent features, users sometimes face technical glitches. In that case contact our lexmark printer support team for assistance from team of experts.

John Belar said...

Trouble Because Of Inability To Sign In On Binance In Canada

Call 1833-464-7652 To sign in to the Binance account, users need to fill in the accurate credentials and open the account. If you are encountering any error while doing this, you can always contact the team of skilled professionals who are there to assist you. You can call on Binance helpline number which is always functional and the team is ready to assist you at every step where you can ask for solutions in steps so that you don’t get any error while executing them.

PETER123 said...

SEO analysts are experts at presenting our website to major search engines. They prepare your website for indexing and improve its rankings by poring over the wealth of data available through analytical tools.

primevideocommytv said...

Simple Steps to Register Your Amazon Prime Video Account
The least difficult approach to interface a gadget to your Amazon account is to download an Amazon application, similar to Prime Music or Prime Video, and sign in to your current Amazon Prime record.
Contingent upon your security settings, you may need to affirm your personality by composing in a code sent to the email or telephone number related to your record. Amazon Prime Video provides customer support, they are fix issues instantly and provide step by step solution. If you have any problem your device contact us primevideo.com/mytv. Our expert team always available for customer assistance.
Read more…

zelenadumas said...

Thanks for posting a very sweet article. So much needed data I'll get and keep in my file.Thank u so muc
office.com/setup
AOL mail login

John Smith said...

It is safe to say that you are Chase Cardholder and looking for the Chase Card Verify.
This post will direct you on How to Verify Chase Debit Card
Charge card? Chase credit card is a well-known choice
Since they are anything but difficult to utilize and oversee.
Read More…

Hotmail Nederland said...

Very informative content. Our company leading in the best Microsoft Hotmail helpdesk services in Nederland. If you get rid of your Hotmail issues and errors then you can contact us by visiting our official website.

Contact Met Hotmail

Hotmail Account Herstellen Lukt Niet

Hotmail Password Vergeten

Demi said...

Fildena 100 Espana
Cenforce 100 France
Vilitra 20 France
Spedra 50 Italia
Stendra US

CStafford Blog said...


Remember Cialis to incorporate healthy habits and prioritize Cialis Vs Viagra for your overall well-being for a fulfilling happier life.
Want to know more biznas.com?

«Oldest ‹Older   201 – 212 of 212   Newer› Newest»