Re-Creating Datafiles When Backups Are Unavailable

I found an old copy of the Oracle 9i User-Managed Backup and Recovery Guide and read the following:

 

If a datafile is damaged and no backup of the file is available, then you can still recover the datafile if:

 

·         All archived log files written after the creation of the original datafile are available

·         The control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database)

 

I decided to try this out in an Oracle 12.1 database. First I checked that the database was in ARCHIVELOG mode:

SQL> select log_mode from v$database

  2  /

 

LOG_MODE

------------

ARCHIVELOG

 

SQL>

I created a tablespace called ANDREW:

SQL> create tablespace andrew

  2  datafile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\ANDREW.DBF'

  3  size 20m

  4  /

 

Tablespace created.

 

SQL>

I created a table in the tablespace:

SQL> l

  1  create table marker

  2  tablespace andrew as

  3  select 'Andrew was here again' message

  4* from dual

SQL> /

 

Table created.

 

SQL>

I closed the database:

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

I deleted the tablespace’s datafile:

C:\app\Administrator\oradata\ORCL1\DATAFILE>dir

Volume in drive C has no label.

Volume Serial Number is 269C-9AD9

 

Directory of C:\app\Administrator\oradata\ORCL1\DATAFILE

 

19/03/2015  18:32    <DIR>          .

19/03/2015  18:32    <DIR>          ..

19/03/2015  18:39        20,979,712 ANDREW.DBF

19/03/2015  18:39     3,722,452,992 O1_MF_SYSAUX_BDGVW9OT_.DBF

19/03/2015  18:39       964,698,112 O1_MF_SYSTEM_BDGVZ93W_.DBF

19/03/2015  11:08     1,906,319,360 O1_MF_TEMP_BDGW88KG_.TMP

19/03/2015  18:39     1,378,885,632 O1_MF_UNDOTBS1_BDGW2MY6_.DBF

19/03/2015  18:39     4,650,442,752 O1_MF_USERS_BDGW2LNZ_.DBF

               6 File(s) 12,643,778,560 bytes

               2 Dir(s)   5,940,617,216 bytes free

 

C:\app\Administrator\oradata\ORCL1\DATAFILE>del andrew.dbf

 

C:\app\Administrator\oradata\ORCL1\DATAFILE>dir

Volume in drive C has no label.

Volume Serial Number is 269C-9AD9

 

Directory of C:\app\Administrator\oradata\ORCL1\DATAFILE

 

19/03/2015  18:42    <DIR>          .

19/03/2015  18:42    <DIR>          ..

19/03/2015  18:39     3,722,452,992 O1_MF_SYSAUX_BDGVW9OT_.DBF

19/03/2015  18:39       964,698,112 O1_MF_SYSTEM_BDGVZ93W_.DBF

19/03/2015  11:08     1,906,319,360 O1_MF_TEMP_BDGW88KG_.TMP

19/03/2015  18:39     1,378,885,632 O1_MF_UNDOTBS1_BDGW2MY6_.DBF

19/03/2015  18:39     4,650,442,752 O1_MF_USERS_BDGW2LNZ_.DBF

               5 File(s) 12,622,798,848 bytes

               2 Dir(s)   5,961,596,928 bytes free

 

C:\app\Administrator\oradata\ORCL1\DATAFILE>

I tried to open the database:

C:\app\Administrator\oradata\ORCL1\DATAFILE>sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 19 18:43:02 2015

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 1720328192 bytes

Fixed Size                  2403496 bytes

Variable Size            1023411032 bytes

Database Buffers          687865856 bytes

Redo Buffers                6647808 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2:

'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\ANDREW.DBF'

 

SQL>

 

I created the missing datafile:

SQL> alter database create datafile

  2  'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\ANDREW.DBF'

  3  /

 

Database altered.

 

SQL>

I recovered the datafile:

SQL> recover datafile 'C:\APP\ADMINISTRATOR\ORADATA\ORCL1\DATAFILE\ANDREW.DBF'

Media recovery complete.

SQL>

I looked for the MARKER table but realized that the database was not open:

SQL> select * from marker

  2  /

select * from marker

              *

ERROR at line 1:

ORA-01219: database or pluggable database not open: queries allowed on fixed

tables or views only

 

SQL>

 

I opened the database:

SQL> alter database open

  2  /

 

Database altered.

 

SQL>

… and the MARKER table was there as before:

SQL> select * from marker

  2  /

 

MESSAGE

---------------------

Andrew was here again

 

SQL> select tablespace_name

  2  from dba_tables

  3  where table_name = 'MARKER'

  4  /

 

TABLESPACE_NAME

------------------------------

ANDREW

 

SQL>

 

The Oracle guide went on to say:

 

Note: You cannot re-create any of the datafiles for the SYSTEM tablespace by using the CREATE DATAFILE clause of the ALTER DATABASE statement because the necessary redo data is not available.

 

I will see what happens if you try to do this in a future post.