Restore Any Lost Datafile with Flashback Database Technology -1

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.

Oracle Flashback 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/