Restore datafile without backup when in archivelog mode

[haste]

Hi,
would like the steps required to restore and recover a datafile given:
[ul square]
[li] there is no backup of the datafile [/li]
[li] the database is in archivelog mode[/li]
[li] archived logs exist from the creation of the database[/li]
[/ul]
I'm considering the scenario where the dba discovers that a datafile has been deleted on startup of the database.

What beats me after startup in nomount mode is how to recreate the lost datafile.

 

[SantaMufasa]

Haste,

Such a scenario is similar to saying, "I wish to rebuild a house...We have all of the raw materials, but no property or location upon which to build the house." As you can imagine, that I ain't gonna happen.

Oracle's Recovery mechanism depends upon your having a "basis" file (that you have backed up somewhere) upon which to apply the contents of your Archived Redo-Log Files (ARLFs). If you have no basis/baseline data file upon which Oracle can apply your ARLFs, then you are hosed.

Sorry to be the bearer of bad news.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[haste]

Thanks for the reply Santa. The Oracle 9i fundamentals 2 documentation a topic on it "Recovery without a backup" which was the source of my question. I had got stuck on how to recreate the datafile without a backup after restarting the database. Will review it again.

 

[SantaMufasa]

...And, definitely, Haste, if you encounter something "automagical" of which I am not aware (that will "produce gold from straw" in Recovery terms), please, please post it here. Good luck.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[haste]

Hi,
went thro the documentation. Its says recovery is possible without a backup given that:
[ul square]
[li]the datafile lost isnt a system or rollback segment file
[/li]
[li] and all archived logs exist from the time the file was existed.
[/li]
[/ul]

The last point is the one I overlooked. I dont have all archived redo logs coz the database was a new incarnation.

Am curiuos to know if it could still work given the database is a new incarnation.


The procedure involves the following:
[ul square]
[li] U take the datafile that isnt backed up offline if that database is open or take its tablespace offline if the database is open
[/li]

-- if database is closed
alter database datafile 5 offline;

-- if database is open, take its tablespace off line without wtiting to the datafile
alter tablespace example offline immediate;

[li]recreate the datafile[/li]

alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'

[li]Use RECOVER or ALTER DATABASE RECOVER to re-apply the logs[/li]

RECOVER TABLESPACE EXAMPLE;

[li]Bring the tablespace or datafile back online
[/li]

ALTER TABLESPACE EXAMPLE ONLINE;

[/ul]

I havent done this in practice. Have attached my failed attempt:

SQL> show spool
spool ON
SQL> archive log list
ORA-01034: ORACLE not available
SQL> startup mount
ORACLE instance started.

Total System Global Area  126950220 bytes                                       
Fixed Size                   453452 bytes                                       
Variable Size             109051904 bytes                                       
Database Buffers           16777216 bytes                                       
Redo Buffers                 667648 bytes                                       
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\oracle9i\oradata\recvr_db\archive
Oldest online log sequence     25
Next log sequence to archive   26
Current log sequence           26
SQL> -- run workload generator
SQL> -- correction make whole database backup
SQL> -- make online backup of file to lose
SQL> select c.name, a.file#, a.name
  2  from v$tablespace c, v$datafile a , v$recover_file b
  3  where b.file#= a.file#
  4  and c.ts# = a.ts#
  5  /

no rows selected

SQL> ed
Wrote file afiedt.buf

  1  select c.name, a.file#, a.name
  2  from v$tablespace c, v$datafile a 
  3* where c.ts# = a.ts#
SQL> /

NAME                                FILE#                                       
------------------------------ ----------                                       
NAME                                                                            
--------------------------------------------------------------------------------
SYSTEM                                  1                                       
C:\ORACLE9I\ORADATA\RECVR_DB\SYSTEM01.DBF                                       
                                                                                
UNDOTBS1                                2                                       
C:\ORACLE9I\ORADATA\RECVR_DB\UNDOTBS01.DBF                                      
                                                                                
CWMLITE                                 3                                       
C:\ORACLE9I\ORADATA\RECVR_DB\CWMLITE01.DBF                                      
                                                                                

NAME                                FILE#                                       
------------------------------ ----------                                       
NAME                                                                            
--------------------------------------------------------------------------------
DRSYS                                   4                                       
C:\ORACLE9I\ORADATA\RECVR_DB\DRSYS01.DBF                                        
                                                                                
EXAMPLE                                 5                                       
C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF                                      
                                                                                
INDX                                    6                                       
C:\ORACLE9I\ORADATA\RECVR_DB\INDX01.DBF                                         
                                                                                

NAME                                FILE#                                       
------------------------------ ----------                                       
NAME                                                                            
--------------------------------------------------------------------------------
ODM                                     7                                       
C:\ORACLE9I\ORADATA\RECVR_DB\ODM01.DBF                                          
                                                                                
TOOLS                                   8                                       
C:\ORACLE9I\ORADATA\RECVR_DB\TOOLS01.DBF                                        
                                                                                
USERS                                   9                                       
C:\ORACLE9I\ORADATA\RECVR_DB\USERS01.DBF                                        
                                                                                

NAME                                FILE#                                       
------------------------------ ----------                                       
NAME                                                                            
--------------------------------------------------------------------------------
XDB                                    10                                       
C:\ORACLE9I\ORADATA\RECVR_DB\XDB01.DBF                                          
                                                                                

10 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select c.name, a.file#, a.name
  2  from v$tablespace c, v$datafile a 
  3* where c.ts# = a.ts#
SQL> set pages 400
SQL> /

NAME                                FILE#                                       
------------------------------ ----------                                       
NAME                                                                            
--------------------------------------------------------------------------------
SYSTEM                                  1                                       
C:\ORACLE9I\ORADATA\RECVR_DB\SYSTEM01.DBF                                       
                                                                                
UNDOTBS1                                2                                       
C:\ORACLE9I\ORADATA\RECVR_DB\UNDOTBS01.DBF                                      
                                                                                
CWMLITE                                 3                                       
C:\ORACLE9I\ORADATA\RECVR_DB\CWMLITE01.DBF                                      
                                                                                
DRSYS                                   4                                       
C:\ORACLE9I\ORADATA\RECVR_DB\DRSYS01.DBF                                        
                                                                                
EXAMPLE                                 5                                       
C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF                                      
                                                                                
INDX                                    6                                       
C:\ORACLE9I\ORADATA\RECVR_DB\INDX01.DBF                                         
                                                                                
ODM                                     7                                       
C:\ORACLE9I\ORADATA\RECVR_DB\ODM01.DBF                                          
                                                                                
TOOLS                                   8                                       
C:\ORACLE9I\ORADATA\RECVR_DB\TOOLS01.DBF                                        
                                                                                
USERS                                   9                                       
C:\ORACLE9I\ORADATA\RECVR_DB\USERS01.DBF                                        
                                                                                
XDB                                    10                                       
C:\ORACLE9I\ORADATA\RECVR_DB\XDB01.DBF                                          
                                                                                

10 rows selected.

SQL> select * from dba_users
  2  where username ='OE";
ERROR:
ORA-01756: quoted string not properly terminated 


SQL> where username ='OE';
SP2-0734: unknown command beginning "where user..." - rest of line ignored.
SQL> select * from dba_users
  2  where username ='OE';
select * from dba_users
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only 


SQL> alter database open;

Database altered.

SQL> select * from dba_users
  2  where username ='OE';

USERNAME                          USER_ID PASSWORD                              
------------------------------ ---------- ------------------------------        
ACCOUNT_STATUS                   LOCK_DATE EXPIRY_DA                            
-------------------------------- --------- ---------                            
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED           
------------------------------ ------------------------------ ---------         
PROFILE                        INITIAL_RSRC_CONSUMER_GROUP                      
------------------------------ ------------------------------                   
EXTERNAL_NAME                                                                   
--------------------------------------------------------------------------------
OE                                     47 D1A2DFC623FDA40A                      
OPEN                                                                            
EXAMPLE                        TEMP                           12-MAY-02         
DEFAULT                        DEFAULT_CONSUMER_GROUP                           
                                                                                
                                                                                

SQL> select c.name, a.file#, a.name
  2  from v$tablespace c, v$datafile a
  3  where c.ts# = a.ts#
  4  /

NAME                                FILE#                                       
------------------------------ ----------                                       
NAME                                                                            
--------------------------------------------------------------------------------
SYSTEM                                  1                                       
C:\ORACLE9I\ORADATA\RECVR_DB\SYSTEM01.DBF                                       
                                                                                
UNDOTBS1                                2                                       
C:\ORACLE9I\ORADATA\RECVR_DB\UNDOTBS01.DBF                                      
                                                                                
CWMLITE                                 3                                       
C:\ORACLE9I\ORADATA\RECVR_DB\CWMLITE01.DBF                                      
                                                                                
DRSYS                                   4                                       
C:\ORACLE9I\ORADATA\RECVR_DB\DRSYS01.DBF                                        
                                                                                
EXAMPLE                                 5                                       
C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF                                      
                                                                                
INDX                                    6                                       
C:\ORACLE9I\ORADATA\RECVR_DB\INDX01.DBF                                         
                                                                                
ODM                                     7                                       
C:\ORACLE9I\ORADATA\RECVR_DB\ODM01.DBF                                          
                                                                                
TOOLS                                   8                                       
C:\ORACLE9I\ORADATA\RECVR_DB\TOOLS01.DBF                                        
                                                                                
USERS                                   9                                       
C:\ORACLE9I\ORADATA\RECVR_DB\USERS01.DBF                                        
                                                                                
XDB                                    10                                       
C:\ORACLE9I\ORADATA\RECVR_DB\XDB01.DBF                                          
                                                                                

10 rows selected.

SQL> -- will backup the Example tablespace
SQL> alter tablespace Example begin backup;

Tablespace altered.

SQL> SELECT STATUS FROM V$TABLESPACE
  2  ;
SELECT STATUS FROM V$TABLESPACE
       *
ERROR at line 1:
ORA-00904: "STATUS": invalid identifier 


SQL> DESC V$TABLESPACE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TS#                                                NUMBER
 NAME                                               VARCHAR2(30)
 INCLUDED_IN_DATABASE_BACKUP                        VARCHAR2(3)

SQL> alter tablespace Example END backup;

Tablespace altered.

SQL> @C:\loci\TEMP\wkldgen\build_workload_generator.sql
Connected.

Synonym dropped.

Connected.

Package created.

No errors.

Package body created.

No errors.
Connected.

Synonym created.


Grant succeeded.

SQL> SHOW USER
USER is "SYSTEM"
SQL> CONN OE/OR@RECVR
ERROR:
ORA-01017: invalid username/password; logon denied 


Warning: You are no longer connected to ORACLE.
SQL> CONN OE/OE@RECVR
Connected.
SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME                                                                      
------------------------------                                                  
CUSTOMERS                                                                       
INVENTORIES                                                                     
ORDERS                                                                          
ORDERS_L                                                                        
ORDER_ITEMS                                                                     
ORDER_ITEMS_L                                                                   
PRODUCT_DESCRIPTIONS                                                            
PRODUCT_INFORMATION                                                             
PRODUCT_REF_LIST_NESTEDTAB                                                      
SUBCATEGORY_REF_LIST_NESTEDTAB                                                  
WAREHOUSES                                                                      

11 rows selected.

SQL> CREATE TABLE CUSTOMERS_BAK AS
  2  SELECT * FROM CUSTOMERS;

Table created.

SQL> ARCHIVE LOG LIST
ORA-01031: insufficient privileges
SQL> show spool
spool ON
SQL> -- actual recovery of datafile without a backup
SQL> -- datafile examples will be deleted after shutdown
SQL> -- An online backup has been taken
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn sys/manager@recvr as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  126950220 bytes                                       
Fixed Size                   453452 bytes                                       
Variable Size             109051904 bytes                                       
Database Buffers           16777216 bytes                                       
Redo Buffers                 667648 bytes                                       
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file 
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' 


SQL> select a.file#,  a.error from v$recover_file a;

     FILE# ERROR                                                                
---------- -----------------------------------------------------------------    
         5 FILE NOT FOUND                                                       

SQL> select b.name, a.ts#, a.status, a.file#, a.name
  2  from v$tablespace b, v$datafile a
  3  where b.ts# = a.ts#
  4  /

NAME                                  TS# STATUS       FILE#                    
------------------------------ ---------- ------- ----------                    
NAME                                                                            
--------------------------------------------------------------------------------
SYSTEM                                  0 SYSTEM           1                    
C:\ORACLE9I\ORADATA\RECVR_DB\SYSTEM01.DBF                                       
                                                                                
UNDOTBS1                                1 ONLINE           2                    
C:\ORACLE9I\ORADATA\RECVR_DB\UNDOTBS01.DBF                                      
                                                                                
CWMLITE                                 3 ONLINE           3                    
C:\ORACLE9I\ORADATA\RECVR_DB\CWMLITE01.DBF                                      
                                                                                
DRSYS                                   4 ONLINE           4                    
C:\ORACLE9I\ORADATA\RECVR_DB\DRSYS01.DBF                                        
                                                                                
EXAMPLE                                 5 ONLINE           5                    
C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF                                      
                                                                                
INDX                                    6 ONLINE           6                    
C:\ORACLE9I\ORADATA\RECVR_DB\INDX01.DBF                                         
                                                                                
ODM                                     7 ONLINE           7                    
C:\ORACLE9I\ORADATA\RECVR_DB\ODM01.DBF                                          
                                                                                
TOOLS                                   8 ONLINE           8                    
C:\ORACLE9I\ORADATA\RECVR_DB\TOOLS01.DBF                                        
                                                                                
USERS                                   9 ONLINE           9                    
C:\ORACLE9I\ORADATA\RECVR_DB\USERS01.DBF                                        
                                                                                
XDB                                    10 ONLINE          10                    
C:\ORACLE9I\ORADATA\RECVR_DB\XDB01.DBF                                          
                                                                                

10 rows selected.

SQL> -- EXAMPLE tablespace lost
SQL> alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as ''C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF';
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as ''C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
                                                                                 *
ERROR at line 1:
ORA-00933: SQL command not properly ended 


SQL> ed
Wrote file afiedt.buf

  1* alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
SQL> /
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate 
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' 


SQL> alter tablespace EXAMPLE offline immediate;
alter tablespace EXAMPLE offline immediate
*
ERROR at line 1:
ORA-01109: database not open 


SQL> alter database datafile 5 offline;

Database altered.

SQL> alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as ''C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF';
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as ''C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
                                                                                 *
ERROR at line 1:
ORA-00933: SQL command not properly ended 


SQL> ed
Wrote file afiedt.buf

  1* alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
SQL> /
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate 
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' 


SQL> select status from v$instance;

STATUS                                                                          
------------                                                                    
MOUNTED                                                                         

SQL> alter database open;

Database altered.

SQL> alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF';
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate 
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' 


SQL> alter tablespace example offline immediate;

Tablespace altered.

SQL> alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF';
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate 
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' 


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> spool off

 

[SantaMufasa]

Haste,

In your sample attempt, above, what lines indicate that you need to recover your database (i.e., that File 5 is corrupt or missing)? Secondly, what lines indicate that you experienced "Failure to Launch" the recovery that I can't see that you needed?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[haste]

I omitted the errors on startup.Am not sure if it answers the second question. Please rephrase it if it doesnt.

Below is the omitted section:

SQL> startup
ORACLE instance started.

Total System Global Area  126950220 bytes                                       
Fixed Size                   453452 bytes                                       
Variable Size             109051904 bytes                                       
Database Buffers           16777216 bytes                                       
Redo Buffers                 667648 bytes                                       
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file 
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' 


SQL> select a.file#,  a.error from v$recover_file a;

     FILE# ERROR                                                                
---------- -----------------------------------------------------------------    
         5 FILE NOT FOUND                                                       

SQL> select b.name, a.ts#, a.status, a.file#, a.name
  2  from v$tablespace b, v$datafile a
  3  where b.ts# = a.ts#
  4  /

NAME                                  TS# STATUS       FILE#                    
------------------------------ ---------- ------- ----------                    
NAME                                                                            
--------------------------------------------------------------------------------
SYSTEM                                  0 SYSTEM           1                    
C:\ORACLE9I\ORADATA\RECVR_DB\SYSTEM01.DBF                                       
                                                                                
UNDOTBS1                                1 ONLINE           2                    
C:\ORACLE9I\ORADATA\RECVR_DB\UNDOTBS01.DBF                                      
                                                                                
CWMLITE                                 3 ONLINE           3                    
C:\ORACLE9I\ORADATA\RECVR_DB\CWMLITE01.DBF                                      
                                                                                
DRSYS                                   4 ONLINE           4                    
C:\ORACLE9I\ORADATA\RECVR_DB\DRSYS01.DBF                                        
                                                                                
EXAMPLE                                 5 ONLINE           5                    
C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF                                      
                                                                                
INDX                                    6 ONLINE           6                    
C:\ORACLE9I\ORADATA\RECVR_DB\INDX01.DBF                                         
                                                                                
ODM                                     7 ONLINE           7                    
C:\ORACLE9I\ORADATA\RECVR_DB\ODM01.DBF                                          
                                                                                
TOOLS                                   8 ONLINE           8                    
C:\ORACLE9I\ORADATA\RECVR_DB\TOOLS01.DBF                                        
                                                                                
USERS                                   9 ONLINE           9                    
C:\ORACLE9I\ORADATA\RECVR_DB\USERS01.DBF                                        
                                                                                
XDB                                    10 ONLINE          10                    
C:\ORACLE9I\ORADATA\RECVR_DB\XDB01.DBF                                          
                                                                                

10 rows selected.

 

[Scunningham99]

hi

because you are using a new incarnation of the db all previous archives associated with the previous instance are no longer valid for the current database (new incarnation). So recovery will never work.



Sy UK

 

[haste]

Thanks Scunningham99 , I got that recovery is not possible. Previously, I thought having all the backups of the new incarnation would suffice.