Restore Any Lost Datafile with Flashback Database Technology -1

Hi,

I will explain datafile restore with Flashback Database Technology in Oracle in this article.

https://salihdeveci.files.wordpress.com/2019/01/backup-and-recovery-in-oracle-32-638.jpg

 

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.

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *