Hi,
I will explain datafile restore with Flashback Database Technology in Oracle in this article.
Read previous article before this. If you don’t know What is the Flashback and its architecture.
You can restore any lost or dropped datafile with flashback and rman after a tablespace is dropped.
1-)First of all, create a tablespace, user a table and insert sample data.
[oracle@MehmetSalih ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 25 11:31:49 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 ts identified by Deveci; User created. SQL> grant dba to ts; Grant succeeded. SQL> alter user ts default tablespace ts; User altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@MehmetSalih ~]$ sqlplus ts/Deveci SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 25 11:33:13 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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/11.2.0.4/db/dbs/aydem_data.dbf /u01/app/oracle/product/11.2.0.4/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 ts(id number); Table created. SQL> insert into ts(id) values(10); 1 row created. SQL> commit; Commit complete. SQL> select * from ts; ID ---------- 10 SQL> exit
2-) Check and specify Current SCN, we will return back to this SCN.
[oracle@MehmetSalih ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 25 11:34:04 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select current_scn from v$database; CURRENT_SCN ----------- 1859609
3-) Then drop tablespace and datafile for testing purposes.
SQL> drop tablespace ts including contents and datafiles; Tablespace dropped.
4-) 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.
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/11.2.0.4/db/dbs/aydem_data.dbf
/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00007
/u01/app/oracle/oradata/deneme/tb_01.dbf
/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00009
9 rows selected.
5-) Change datafile name to his old name again because named of dropped datafile has changed.
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00009' as '/u01/app/oracle/oradata/deneme/ts_01.dbf'; Database altered.
6-) Datafile has been restored , but can not query the tables in this datafile.
SQL> select * from ts.ts; select * from ts.ts * 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/11.2.0.4/db/dbs/aydem_data.dbf /u01/app/oracle/product/11.2.0.4/db/dbs/UNNAMED00007 /u01/app/oracle/oradata/deneme/tb_01.dbf /u01/app/oracle/oradata/deneme/ts_01.dbf 9 rows selected.
I will continue to explain restore datafile with flashback database feature in the next post.
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/