ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf'

How to recover database when system datafile is lost using RMAN backup?
or 
how to resolve ORA-01116: error in opening database file 1  ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf'?

Cause: ORA-01116: error in opening database file 1 means either system datafile is either missing or corrupted
Action: we need to perform recovery as shown below

DB Name: TESTDB
[oracle@avalance_testserv dbf]$   ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf
[oracle@avalance_testserv dbf]$  rm system01.dbf
SQL> select * from sys.aud$;
select * from sys.aud$
                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Note: Since the normal shutdown is not possible in this case we shall use shut abort
SQL> shut abort
ORACLE instance shut down
[oracle@avalance_testserv dbf]$ sqlplus / as sysdba
SQL> startup mount
[oracle@avalance_testserv dbf]$ rman target/
RMAN> restore datafile 1;
Starting restore at 25-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/TESTDB/oradata/dbf/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck
channel ORA_DISK_1: piece handle=/u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck tag=FULL_OPEN_BACKUP_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-SEP-14
RMAN> recover datafile 1;
Starting recover at 25-SEP-14
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc
archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc
archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc
archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_32_859142042.arc
archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_33_859142042.arc
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc thread=1 sequence=22
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc thread=1 sequence=24
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc thread=1 sequence=25
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc thread=1 sequence=26
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc thread=1 sequence=27
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc thread=1 sequence=28
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc thread=1 sequence=29
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc thread=1 sequence=30
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc thread=1 sequence=31
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-SEP-14
sql>alter database open;
It is suggested to take RMAN backup immediately after the activity is over. please follow the link to take RMAN backup: http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html

Recovery of database using RMAN backup when system datafile is lost

How to recover database when system datafile is lost using RMAN backup?

DB Name: TESTDB

[oracle@avalance_testserv dbf]$   ls
redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@avalance_testserv dbf]$  rm system01.dbf


SQL> select * from sys.aud$;
select * from sys.aud$
                  *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Note: Since the normal shutdown is not possible in this case we shall use shut abort

SQL> shut abort
ORACLE instance shut down


[oracle@avalance_testserv dbf]$ sqlplus / as sysdba
SQL> startup mount


[oracle@avalance_testserv dbf]$ rman target/

RMAN> restore datafile 1;

Starting restore at 25-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/TESTDB/oradata/dbf/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck
channel ORA_DISK_1: piece handle=/u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck tag=FULL_OPEN_BACKUP_DISK
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-SEP-14


RMAN> recover datafile 1;

Starting recover at 25-SEP-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc
archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc
archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc
archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc
archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc
archived log for thread 1 with sequence 32 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_32_859142042.arc
archived log for thread 1 with sequence 33 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_33_859142042.arc
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc thread=1 sequence=22
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc thread=1 sequence=24
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_25_859142042.arc thread=1 sequence=25
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_26_859142042.arc thread=1 sequence=26
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_27_859142042.arc thread=1 sequence=27
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_28_859142042.arc thread=1 sequence=28
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_29_859142042.arc thread=1 sequence=29
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_30_859142042.arc thread=1 sequence=30
archived log file name=/u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_31_859142042.arc thread=1 sequence=31
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-SEP-14

sql>alter database open;


It is suggested to take RMAN backup immediately after the activity is over. please follow the link to take RMAN backup: http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html


Cheers…!!!

Recovery using RMAN backup when control file is lost (control file recreation method)

How to recover database using control file recreation method?

There are two ways we can recover the database when control file is lost:
1. Restore the control file and recover the database
Note:Please follow  http://orcl11gdba.blogspot.in/2014/09/recovery-using-rman-backup-when-control.html  for this method
2. Recreate the control file using backup control file and recover

DB Name: TESTDB

Here we shall perform second way to recover the database. To do this we have to make sure that controlfile is backed up to some location.

SQL> alter database backup controlfile to trace as '/tmp/control.sql';
[oracle@avalance_testserv /tmp]$ ls
control.sql 

Note:This file will contain two SQL queries with Resetlogs and noresetlogs option. We have to consider the query resetlogs only.

[oracle@avalance_testserv /tmp]$ cat ctrl.sql
CREATE CONTROLFILE REUSE DATABASE "TESTDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/TESTDB/oradata/dbf/redo01.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/TESTDB/oradata/dbf/redo02.log'  SIZE 100M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/TESTDB/oradata/dbf/redo03.log'  SIZE 100M BLOCKSIZE 512
DATAFILE
  '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf',
  '/u01/app/oracle/TESTDB/oradata/dbf/sysaux01.dbf',
  '/u01/app/oracle/TESTDB/oradata/dbf/undotbs01.dbf',
  '/u01/app/oracle/TESTDB/oradata/dbf/users01.dbf',
  '/u01/app/oracle/TESTDB/oradata/dbf/app_data01.dbf'
CHARACTER SET US7ASCII
;

Observation: This is nothing but structure of database .Now I am going to remove the control file so that we can perform recreation method

[oracle@avalance_testserv oradata]$ ls
control.ctl  dbf  redo
[oracle@avalance_testserv oradata]$
[oracle@avalance_testserv oradata]$ rm control.ctl
[oracle@avalance_testserv oradata]$ ls
dbf  redo

[oracle@avalance_testserv oradata]$ sqlplus / as sysdba
SQL> shut abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  609681408 bytes
Fixed Size                  2215664 bytes
Variable Size             549454096 bytes
Database Buffers           50331648 bytes
Redo Buffers                7680000 bytes
SQL> exit


Observation: we are putting the database in nomount state because we can’t directly mount it without control file.to do that we have to restore it from backup as below.
 


SQL>@control.sql
Control file created.
SQL> alter database mount;
Alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

Observation:Here database will be automatically mounted.

SQL> recover database using backup controlfile;

ORA-00279: change 865927 generated at 09/18/2014 16:14:41 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/archivelog/2014_09_18/o1_mf_1_1_
%u_.arc
ORA-00280: change 865927 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/TESTDB/oradata/dbf/redo01.log
Log applied.
Media recovery complete.
       
Note: Here we can give auto option to apply archived logs and then current redo log file redo1.log

SQL> alter database open resetlogs;
Database altered.


It is suggested to take RMAN backup immediately after the activity is over. please follow the link to take RMAN backup: http://orcl11gdba.blogspot.in/2014/05/taking-database-backup-using-rman-method.html
Cheers…!!!