We are going to have a look on the steps which are followed by DBA to recover a loss of a non-system tablespace on the same location while the database is open. When we 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 this 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 datafile
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 18.104.22.168.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 ...
Thank you for giving your valuable time to read the above information.
For More Detail , You can join us follow:
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp