Rman System datafile recovery scenerio

System datafile recovery:

Lets create a scenario where we will drop the system datafile, and we will recover using the rman restore and recover process.Since system datafile is key for database functioning.

Before we start. Lets verify that database is functioning without any problems.

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 20:47:23 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14


Lets drop system datafile now.

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  users01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  undotbs01.dbf

[oracle@linux1 orcl]$ rm system*

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  users01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf
redo01.log     standby_redo02.log  temp01.dbf
redo02.log     standby_redo03.log  undotbs01.dbf

After dropping system datafile. lets query some data from database. We will now see that database is complaining that it can't access system01.dbf

[oracle@linux1 ~]$ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 7 21:18:06 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username from dba_users;
select username from dba_users
                     *
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/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
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/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Now lets start the recover process.Since this is system datafile it is mandatory that you restart the server in mount state. Even if you try to start the
database using startup, it will just mount the database and fail asking for system.dbf file.

SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/datafiles/orcl/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shut abort;
ORACLE instance shut down.


Now login into rman session and restore the system datafile.

[oracle@linux1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jul 7 21:19:20 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1378116623, not open)


RMAN> run {
2> restore datafile 1;
3> recover datafile 1;
4> }

Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 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/datafiles/orcl/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2014_07_07/o1_mf_nnndf_FULL_BACKUP_9vpkw87b_.bkp tag=FULL_BACKUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 07-JUL-14

Starting recover at 07-JUL-14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 65 is already on disk as file /u01/app/oracle/oraarch/orcl_1_65_850335889.arc
archived log for thread 1 with sequence 66 is already on disk as file /u01/app/oracle/oraarch/orcl_1_66_850335889.arc
archived log for thread 1 with sequence 67 is already on disk as file /u01/app/oracle/oraarch/orcl_1_67_850335889.arc
archived log file name=/u01/app/oracle/oraarch/orcl_1_65_850335889.arc thread=1 sequence=65
media recovery complete, elapsed time: 00:00:05
Finished recover at 07-JUL-14

Note: In case you don't know the datafile number to recover you can use below query.
Most of the time system datafile is the #1. You can run this query  when DB is in mount state.

SQL> select file#,Name, status from v$datafile where name='SYSTEM';

     FILE# Name STATUS
---------- ----------------------------------- -------
         1 /u01/app/oracle/datafiles/orcl/system01.dbf SYSTEM
   


verify that system datafile has been restored and recovered.

[oracle@linux1 orcl]$ ls
control01.ctl  redo03.log          standby_redo04.log  undotbs01.dbf
example01.dbf  standby_redo01.log  sysaux01.dbf        users01.dbf
redo01.log     standby_redo02.log  system01.dbf
redo02.log     standby_redo03.log  temp01.dbf


Now open the database and query some random data.

SQL> alter database open;

Database altered.

SQL> select count(username) from dba_users;

COUNT(USERNAME)
---------------
             36