This is possible only if all the redo is still available in the online redo logs. !
Do not attempt this if the online redo logs have suffered enough switches that the log that was current during backup and the subsequent transaction(s) has been overwritten.
First, I create a new tablespace :
SQL> connect / as sysdba
Connected.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 295
Current log sequence 297
SQL>
SQL> create tablespace TBS_RECOVERY ;
Tablespace created.
SQL> select file_name from dba_data_files where tablespace_name = 'TBS_RECOVERY';
FILE_NAME
--------------------------------------------------------------------------------------------------------------
/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
SQL>
SQL> -- startup in MOUNT mode for an RMAN Backup
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 213911176 bytes
Database Buffers 314572800 bytes
Redo Buffers 5840896 bytes
Database mounted.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 295
Current log sequence 297
SQL> pause Run an RMAN Backup of the tablespace
Run an RMAN Backup of the tablespace
Then I run an RMAN backup :
RMAN> backup tablespace TBS_RECOVERY;
Starting backup at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=21 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00018 name=/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
channel ORA_DISK_1: starting piece 1 at 05-NOV-11
channel ORA_DISK_1: finished piece 1 at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T113924_7c9czg54_.bkp tag=TAG20111105T113924 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-NOV-11
Starting Control File and SPFILE Autobackup at 05-NOV-11
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_05/o1_mf_s_766409773_7c9czltz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-NOV-11
RMAN>
Now, I proceed to put some data into the tablespace :
SQL> alter database open;
Database altered.
SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- get current SCN
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5482811
SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;
TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:41
SQL>
SQL> -- run regular insert
SQL> create table INSERT_TBL tablespace TBS_RECOVERY
2 as select * from dba_objects where 1=2;
Table created.
SQL> insert into INSERT_TBL select * from dba_objects;
76684 rows created.
SQL> -- run CTAS
SQL> create table CTAS_TBL tablespace TBS_RECOVERY as select * from dba_objects;
Table created.
SQL>
SQL> select segment_name, segment_type, bytes from user_segments
2 where tablespace_name = 'TBS_RECOVERY';
SEGMENT_NAME SEGMENT_TYPE BYTES
------------------------------ ------------------ ----------
INSERT_TBL TABLE 9437184
CTAS_TBL TABLE 9437184
SQL>
SQL> -- check unrecoverable stamps on the datafile
SQL> select unrecoverable_change#, to_char(unrecoverable_time,'DD-MON HH24:MI')
2 from v$datafile, dba_data_files
3 where file#=file_id
4 and tablespace_name = 'TBS_RECOVERY'
5 /
UNRECOVERABLE_CHANGE# TO_CHAR(UNRECOVERABLE
--------------------- ---------------------
0
SQL>
SQL> -- get current SCN
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5482969
SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;
TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:41
SQL>
SQL> connect / as sysdba
Connected.
SQL> -- set tablespace offline
SQL> alter tablespace TBS_RECOVERY offline;
Tablespace altered.
SQL>
SQL> pause Remove datafile and restore and recover datafile
Remove datafile and restore and recover datafile
I remove the datafile :
[root@localhost ~]# ls -l /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
-rw-rw---- 1 oracle oracle 104865792 Nov 5 11:41 /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
[root@localhost ~]# rm /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
rm: remove regular file `/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf'? yes
[root@localhost ~]# ls -l /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
ls: /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf: No such file or directory
[root@localhost ~]#
I then RESTORE and RECOVER the tablespace :
RMAN> restore tablespace TBS_RECOVERY;
Starting restore at 05-NOV-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=20 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 00018 to /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9crk23_.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T113924_7c9czg54_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_05/o1_mf_nnndf_TAG20111105T113924_7c9czg54_.bkp tag=TAG20111105T113924
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 05-NOV-11
RMAN> recover tablespace TBS_RECOVERY;
Starting recover at 05-NOV-11
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 05-NOV-11
RMAN> sql 'alter tablespace TBS_RECOVERY online';
sql statement: alter tablespace TBS_RECOVERY online
RMAN>
I now verify the data in the two tables :
SQL>
SQL> -- get current SCN
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5483260
SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;
TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:47
SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- query the table
SQL> select count(*) from CTAS_TBL;
select count(*) from CTAS_TBL
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 18, block # 1283)
ORA-01110: data file 18: '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9dc5mw_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SQL>
SQL> select count(*) from INSERT_TBL;
COUNT(*)
----------
76684
SQL>
The table that was created as a CTAS operation is not readable as the rows were inserted as a NOLOGGING operation. (See my previous blog post : "CTAS in a NOARCHIVELOG database is a NOLOGGING operation").
However, the table that has data inserted by regular DML, using a normal INSERT has been recovered !
The RECOVER was able to read from the Online Redo Logs.
This case also shows how I did not have to do a Full Database Backup or a Full Database Restore+Recover. The single Tablespace was recovered to a consistent point in time with the rest of the database as evidenced below :
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5483376
SQL> select to_char(sysdate,'DD-MON HH24:MI') from dual;
TO_CHAR(SYSDATE,'DD-M
---------------------
05-NOV 11:51
SQL> alter system checkpoint;
System altered.
SQL> select file#, last_change#, checkpoint_change#, to_char(checkpoint_time,'DD-MON HH24:MI')
2 from v$datafile order by 1;
FILE# LAST_CHANGE# CHECKPOINT_CHANGE# TO_CHAR(CHECKPOINT_TI
---------- ------------ ------------------ ---------------------
1 5483383 05-NOV 11:51
2 5483383 05-NOV 11:51
3 5483383 05-NOV 11:51
4 5483383 05-NOV 11:51
5 5483383 05-NOV 11:51
6 5483383 05-NOV 11:51
7 5483383 05-NOV 11:51
8 5483383 05-NOV 11:51
9 5483383 05-NOV 11:51
10 5483383 05-NOV 11:51
11 5483383 05-NOV 11:51
12 5483383 05-NOV 11:51
13 5483383 05-NOV 11:51
14 5483383 05-NOV 11:51
15 5483383 05-NOV 11:51
16 5483383 05-NOV 11:51
17 5483383 05-NOV 11:51
18 5483383 05-NOV 11:51
18 rows selected.
SQL>
These are the Log Sequence Numbers (selected lines from the alert.log):
Sat Nov 05 11:22:02 2011
ALTER DATABASE OPEN
Thread 1 opened at log sequence 297
Current log# 3 seq# 297 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Successful open of redo thread 1
...
Sat Nov 05 11:22:14 2011
QMNC started with pid=20, OS id=2378
Completed: ALTER DATABASE OPEN
...
Sat Nov 05 11:35:45 2011
create tablespace TBS_RECOVERY
Sat Nov 05 11:35:55 2011
Completed: create tablespace TBS_RECOVERY
...
Sat Nov 05 11:37:12 2011
ALTER DATABASE MOUNT
Successful mount of redo thread 1, with mount id 1294202344
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
...
Sat Nov 05 11:41:13 2011
alter database open
Sat Nov 05 11:41:13 2011
Thread 1 opened at log sequence 297
Current log# 3 seq# 297 mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
...
Sat Nov 05 11:41:59 2011
alter tablespace TBS_RECOVERY offline
Completed: alter tablespace TBS_RECOVERY offline
...
Sat Nov 05 11:45:46 2011
Full restore complete of datafile 18 /home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_tbs_reco_7c9dc5mw_.dbf. Elapsed time: 0:00:05
checkpoint is 5482691
Sat Nov 05 11:46:04 2011
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
tablespace TBS_RECOVERY
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 297 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo03.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
tablespace TBS_RECOVERY
Sat Nov 05 11:46:23 2011
alter tablespace TBS_RECOVERY online
Completed: alter tablespace TBS_RECOVERY online
Therefore it is possible to recover a Tablespace in a NOARCHIVELOG database in a very limited frame -- enough redo being available in the Online Redo Logs.
.
.
.