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@alpha datafile]$ ll /u01/app/oracle/oradata/TEST/datafile/example.dbf
-rw-r----- 1 oracle oinstall 10493952 Mar 13 13:46 /u01/app/oracle/oradata/TEST/datafile/example.dbf
[oracle@alpha datafile]$ mv /u01/app/oracle/oradata/TEST/datafile/example.dbf /u01/app/oracle/oradata/TEST/datafile/example.dbf.back
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
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: TEST (DBID=2300947395)
)
Put the tablespace offline
Restore tablespace
RMAN> restore tablespace example;
Starting restore at 13-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/TEST/datafile/example.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp tag=TAG20190313T134524
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-MAR-19
Recover tablespace
Put tablespace online.
Tablespace got recovered and we can see that in original location
Content in alert log
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@alpha datafile]$ ll /u01/app/oracle/oradata/TEST/datafile/example.dbf
-rw-r----- 1 oracle oinstall 10493952 Mar 13 13:46 /u01/app/oracle/oradata/TEST/datafile/example.dbf
[oracle@alpha datafile]$ mv /u01/app/oracle/oradata/TEST/datafile/example.dbf /u01/app/oracle/oradata/TEST/datafile/example.dbf.back
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
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: TEST (DBID=2300947395)
)
Put the tablespace offline
Restore tablespace
RMAN> restore tablespace example;
Starting restore at 13-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/TEST/datafile/example.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2019_03_13/o1_mf_nnndf_TAG20190313T134524_g8lmy49o_.bkp tag=TAG20190313T134524
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 13-MAR-19
Recover tablespace
Put tablespace online.
Tablespace got recovered and we can see that in original location
Content in alert log
No comments:
Post a Comment