RECOVER THE LOST DATAFILE USNIG FLASHBACK SCN CONCEPT
In this blog we are going to learn about how to recover the lost data file by using flashback database in SCN method
You can restore any lost or dropped datafile with flashback and rman after a tablespace is dropped.
First of all, create a tablespace, user a table and insert data as your wish.
CONNECT TO THE DATABASE AND CREATE NEW TABLESPACE AND USER WITH ASSIGNED TO NEW TABLESPACE
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.2.0 Production on Sun Oct 18 11:31:49 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create tablespace ts datafile ‘/u01/app/oracle/oradata/deneme/ts_01.dbf’ size 100m;
Tablespace created.
SQL> create user shan identified by shan ;
User created.
SQL> grant dba to shan;
Grant succeeded.
SQL> alter user shan default tablespace tbs1;
User altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle ~]$ sqlplus shan/shan
SQL*Plus: Release 12.2.0.2.0 Production on Sun Oct 18 11:33:13 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to
Oracle Database 12c Enterprise Edition Release 12.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u01/app/oracle/oradata/deneme/system01.dbf
/u01/app/oracle/oradata/deneme/sysaux01.dbf
/u01/app/oracle/oradata/deneme/undotbs01.dbf
/u01/app/oracle/oradata/deneme/users01.dbf
/u01/app/oracle/oradata/deneme/example01.dbf
/u01/app/oracle/product/12.2.0.2/db/dbs/aydem_data.dbf
/u01/app/oracle/product/12.2.0.2/db/dbs/UNNAMED00007
/u01/app/oracle/oradata/deneme/tb_01.dbf
/u01/app/oracle/oradata/deneme/ts_01.dbf
9 rows selected.
SQL> create table tb1(id number);
Table created.
SQL> insert into tb1(id) values(10);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tb1;
ID
———-
10
SQL> exit
Check and specify Current SCN, we will return back to this SCN.
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.2.0 Production on Sun Oct 18 11:34:04 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select current_scn from v$database;
CURRENT_SCN
———–
1859609
Then drop tablespace and datafile.
SQL> drop tablespace tbs1 including contents and datafiles;
Tablespace dropped.
Now try to restore our datafile using flashback database.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1419685888 bytes
Fixed Size 2253224 bytes
Variable Size 855641688 bytes
Database Buffers 553648128 bytes
Redo Buffers 8142848 bytes
Database mounted.
With the flashback database feature, we will return database to a previous time or to SCN.
SQL> flashback database to scn 1859609;
Flashback complete.
Now let’s go to open database with resetlogs option.
SQL> alter database open resetlogs;
Database altered.
We want Cross-Check all datafiles, our datafiles has been restored with unnamed name like following
SQL> select name from v$datafile
NAME
——————————————————————————–
/u01/app/oracle/oradata/deneme/system01.dbf
/u01/app/oracle/oradata/deneme/sysaux01.dbf
/u01/app/oracle/oradata/deneme/undotbs01.dbf
/u01/app/oracle/oradata/deneme/users01.dbf
/u01/app/oracle/oradata/deneme/example01.dbf
/u01/app/oracle/product/12.2.0.2/db/dbs/aydem_data.dbf
/u01/app/oracle/product/12.2.0.2/db/dbs/UN_NAMED7
/u01/app/oracle/oradata/deneme/tb_01.dbf
/u01/app/oracle/product/12.2.0.2/db/dbs/UN_NAMED9
9 rows selected.
We Should Change data-file name to his old name again because named of dropped data-file has changed.
SQL>alter database create datafile ‘/u01/app/oracle/product/12.2.0.2/db/dbs/UN_NAMED9’ as ‘/u01/app/oracle/oradata/deneme/ts_01.dbf’;
Database altered.
Datafile has been restored , but can not query the tables in this datafile.
SQL> select * from tb1.tbs1
select * from tb1.tbs1
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: ‘/u01/app/oracle/oradata/deneme/ts_01.dbf’
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u01/app/oracle/oradata/deneme/system01.dbf
/u01/app/oracle/oradata/deneme/sysaux01.dbf
/u01/app/oracle/oradata/deneme/undotbs01.dbf
/u01/app/oracle/oradata/deneme/users01.dbf
/u01/app/oracle/oradata/deneme/example01.dbf
/u01/app/oracle/product/12.2.0.2/db/dbs/aydem_data.dbf
/u01/app/oracle/product/12.2.0.2/db/dbs/UNNAMED00007
/u01/app/oracle/oradata/deneme/tb_01.dbf
/u01/app/oracle/oradata/deneme/ts_01.dbf
9 rows selected.
