Recover drop tablespace using flashback


SQL> shutdown immediate 
SQL> startup mount
SQL> alter database archivelog;
 
Database altered.
 
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38713: Flashback Database logging is already turned on.
 
SQL> alter database open;
 
Database altered.
 
SQL> create tablespace tbs datafile '/11g_install/app/sonujha/
oradata/Airtel/temp01.sonu' size 10m;
 
Tablespace created.
 
SQL> create user sujeet identified by sujeet;
 
User created.
 
SQL> grant sysdba to sujeet;
 
Grant succeeded.
 
SQL> alter user sujeet default tablespace tbs;
 
User altered.
 
SQL> create table tbs (id number);
 
Table created.
 
SQL> select current_scn from v$database;
 
CURRENT_SCN
-----------
     744951
 
SQL> drop tablespace tbs including contents and datafile;
Enter value for datafile: 
old   1: drop tablespace tbs including contents and datafile
new   1: drop tablespace tbs including contents
 
 
Tablespace dropped.
 
SQL> desc v$datafile;
 
 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
/11g_install/app/sonujha/oradata/Airtel/system01.dbf
/11g_install/app/sonujha/oradata/Airtel/sysaux01.dbf
/11g_install/app/sonujha/oradata/Airtel/undotbs01.dbf
/11g_install/app/sonujha/oradata/Airtel/users01.dbf
/11g_install/app/sonujha/oradata/Airtel/example01.dbf
 
SQL> shutdown immediate
ORACLE instance shut down.
SQL> startup mount 
SQL> flashback database to scn 744951;
 
Flashback complete.
 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
/11g_install/app/sonujha/oradata/Airtel/system01.dbf
/11g_install/app/sonujha/oradata/Airtel/sysaux01.dbf
/11g_install/app/sonujha/oradata/Airtel/undotbs01.dbf
/11g_install/app/sonujha/oradata/Airtel/users01.dbf
/11g_install/app/sonujha/oradata/Airtel/example01.dbf
/11g_install/app/sonujha/product/11.1.0/db_1/dbs/UNNAMED00006
 
6 rows selected.
 
SQL> select file_id,file_name from dba_data_files;
 
   FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
         4
/11g_install/app/sonujha/oradata/Airtel/users01.dbf
 
         3
/11g_install/app/sonujha/oradata/Airtel/undotbs01.dbf
 
         2
/11g_install/app/sonujha/oradata/Airtel/sysaux01.dbf
 
 
   FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
         1
/11g_install/app/sonujha/oradata/Airtel/system01.dbf
 
         5
/11g_install/app/sonujha/oradata/Airtel/example01.dbf
 
         6
/11g_install/app/sonujha/product/11.1.0/db_1/dbs/UNNAMED00006
 
 
   FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
         7
/11g_install/app/sonujha/oradata/Airtel/sona.dbf
 
 
7 rows selected.
 
SQL> select status from dba_data_files;
 
STATUS
---------
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
AVAILABLE
 
7 rows selected.
 
SQL> create table t1(t number);
 
Table created.
SQL> select file_name,tablespace_name,bytes/1024/1024 from 
dba_data_files where tablespace_name='TBS';
 
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                BYTES/1024/1024
------------------------------ ---------------
/11g_install/app/sonujha/product/11.1.0/db_1/dbs/UNNAMED00006
TBS
 
 
SQL> alter database rename file '/11g_install/app/sonujha/
product/11.1.0/db_1/dbs/UNNAMED00006' to '/11g_install/app/
sonujha/oradata/Airtel/temp01.sonu';
 
Database altered.
 
SQL> alter database create datafile '/11g_install/app/sonujha/
oradata/Airtel/temp01.sonu';
 
Database altered.
SQL> recover datafile 6;
ORA-00279: change 744647 generated at 09/23/2011 14:49:51 
needed for thread 1
ORA-00289: suggestion :
/11g_install/app/sonujha/flash_recovery_area/AIRTEL/archivelog/
2011_09_23/o1_mf_
1_8_77ryhwcf_.arc
ORA-00280: change 744647 for thread 1 is in sequence #8
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
SQL> alter database datafile 6 online;
 
Database altered.
 
SQL> select bytes,TABLESPACE_NAME from dba_data_files;

No comments:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...