Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Hands-on Oracle, Backup and Recovery Games:            Creating Datafiles

Expert Oracle Database Tips by Donald BurlesonJuly 9, 2015

Hands-on Oracle, Backup and Recovery Games: Creating Datafiles

By Steve Callan

We know that in Oracle, certain things are and can be done at certain times. One of those operations pertains to adding or creating datafiles. One operation where adding datafiles to a database is common is within or during the CREATE DATABASE-related statements. Even if using Oracle managed files and accepting defaults, datafiles will be created. Creating tablespaces also includes a provision for adding datafiles. Yet another add/create datafile event takes place when altering a tablespace for growth-related purposes (altering a tablespace by adding a datafile). In all of these scenarios, there is one thing in common: when the datafile is added, it is associated with a tablespace you have named or identified in the DDL statement. To categorize the commands or operations being used, we can identify them as CREATE or ALTER operations.

Is it possible to add (by creating) a datafile to the database without specifying the tablespace to which it belongs? That is, can you issue an ALTER DATABASE CREATE DATAFILE ?<path/name>?; and expect Oracle to know what to do with this file? If so, does this work for any datafile? And, why would you do this in the first place?

After you alter the structure of a database (e.g., add a new datafile for whatever reason, add a new tablespace, or add another file to an existing tablespace), what is a best practice to follow? That's right, take a backup. The Administrator's Guide (10gR2) states the following no less than five times: After making any structural changes to a database, always perform an immediate and complete backup.

Let's suppose that sometime after having added a datafile (or before having a backup) and data manipulation operations have been applied to objects whose tablespace owns the datafile of interest, you lose the datafile. Media recovery is now required. Can the data be recovered? Don't you need a backup of the datafile to which archived redo logs are applied in order to perform media recovery? Almost every example of media recovery seems to include that part - restore a backed up copy of the datafile and then apply archived redo logs to bring the tablespace to a more current point in time.

This scenario is different - there is no backed up copy of the datafile to start with, so how can recovery be used here? The file existed once and now it does not. The control file still knows about the file, which is why you may or may not be able to open the database, or keep the database in an open state. This is where the ALTER DATABASE CREATE DATAFILE statement comes into play. You do not explicitly state the tablespace to which the datafile belongs because Oracle already knows this bit of information. Your task (one of two) in this scenario is to create a replacement file (same name or rename it, to include using a different path). Do you have to specify the size of the file? No, again, Oracle already knows this. Your other mission (two of two) is to apply archived redo logs against this filler/placeholder file.

To reiterate what must be done: create a new datafile and apply archived redo logs (using RECOVER DATAFILE). Does this work for any datafile? It does not, specifically; you cannot use this technique to recover SYSTEM tablespace datafiles. Does this work for any DML-without-a-backed-up-copy scenario? No, it does not. If the DML was not logged, then there is nothing to recover from the archived redo logs. When is, or when can it be, DML not logged? That's a different topic, so for the point of this scenario, we assume that normal logging has taken place.

Seeing is believing, so let's prove that recovery can take place with an example. Create a test database (one you can afford to trash) and make the files as small as practical (we don't care about the size; it's just the fact that they exist). The database will need to be in archivelog mode. Once the database is open for business, create a new tablespace or add a datafile to an existing tablespace AND have the file location in a place where you can replicate media failure. A flash drive is handy for this; just pull the drive when ready to simulate loss of the datafile. Another way is to shutdown the database and rename the target/now missing file. After adding the datafile, create a table, add some data to it, ensure the redo logs rotate through at least once, and then pull the drive.

In the recovery scenario, pretending that the flash drive location is no longer available, the CREATE DATAFILE statement will use syntax like so:

ALTER DATABASE CREATE DATAFILE 'the old path/name? AS ?use a new path/name?;

The media recovery step is then applied against the new datafile via:

RECOVER DATAFILE 'the new path/name?;

Assuming this all goes well, what should you do when recovery is complete? That's right - take a backup. If you can recover (so easily?) this way, then why is a backup after adding a new file such a big deal? Going back to the NOLOGGING option, what if a table had been created via NOLOGGING? Or an index? Or lots and lots of each? At least with the backed up datafile, you will have captured the structure of those objects. Without the file, you would have to re-create them, which, in a recovery scenario, could add extra time you don't really want to be spending given the possible/potential high degree of visibility or scrutiny you may be experiencing (ignoring the obvious question about why there wasn't a backed up copy of the file in the first place).

The figures and pictures below show the scenario of losing a datafile (I did a shutdown and deleted the file after the service was stopped).

Starting Files:
 FILE_NAME                                                MB 
 ----------------------------------------------------- -----

Add a datafile"
 SQL> alter tablespace users
   2  add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\USERS02.DBF'
   3  size 5M;
 Tablespace altered.

User DML

SQL> alter tablespace users
SQL> conn scott/tiger@demo


SQL> create table lost
2  as select * from all_objects;
Table created.

SQL> delete from lost;
40768 rows deleted.

SQL> commit;
Commit complete.

SQL> insert into lost select * from all_objects;
40768 rows created.

SQL> commit;
Commit complete.

Switch logfiles:

SQL> conn system/oracle@demo


SQL> alter system switch logfile;
System altered.
 QL> /
system altered.
SQL> /
System altered.

Shutdown, and startup. Check the alert log.

Alert Log - file is missing

 Errors in file c:\oracle\product\10.2.0\admin\demo\bdump\demo_dbw0_3512.trc:
 ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
 ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\USERS02.DBF'
 ORA-27041: unable to open file
 OSD-04002: unable to open file
 O/S-Error: (OS 2) The system cannot find the file specified.

Perform Recovery

SQL> conn / as sysdba


SQL> select status from v$instance;
SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\USERS02.DBF';
Database altered.
SQL> recover datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DEMO\USERS02.DBF';
ORA-00279: change 548985 generated at 07/01/2008 21:52:32 needed for thread 1
ORA-00289: suggestion :
ORA-00280: change 548985 for thread 1 is in sequence #2
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.

Back to normal

SQL> conn scott/tiger@demo
SQL> select count(*) from lost;

In Closing

This is actually pretty easy to practice and demonstrate, and it offers a little twist on the usual ?how and when? you add datafiles to the database operation. As an alternative demonstration, create a table using the NOLOGGING option (can you identify one way how?) and then apply DML, rotate the logs, and induce media failure. Without re-creating the table, can you recover now?


Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster