Saturday, March 21, 2015

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.

No comments: