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

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

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

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 (covered here),
so for the point of this scenario, we assume that normal logging has taken

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.
 SQL> /
 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?


See All Articles by Columnist
Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles