The following is a simple example how to use RMAN to backup a running Oracle 11g database and how to fully restore and recover it when all database files are lost or have been destroyed. It assumes that the database is running in Archive Log mode and that the Fast Recovery Area (FRA) has been configured for easier data management. Please see the Oracle documentation for more information.
It all starts with a full backup of the database, including the archived redo logs, database spfile and control files:
[oracle@vm501 ~]$ . oraenv ORACLE_SID = [orcle] ? orcl2 The Oracle base has been set to /u01/app/oracle [oracle@vm501 ~]$ rman target / connected to target database: ORCL2 (DBID=843703910) RMAN> configure controlfile autobackup on; RMAN> backup database plus archivelog delete input;
Note the DBID when connecting with RMAN to the database, which will be required when restoring the database controlfile from autobackup.
A full database backup will always include a copy of the database spfile and control file, however, with RMAN autobackup enabled, the restore process will be easier to manage. The option "delete all input" is not required, but usually a good practice to save disk space, since archived redo logs can build up and occupy a substantial amount of disk space.
The autobackup information will be shown at the the end of the backup.
Starting Control File and SPFILE Autobackup at 10-MAY-15 piece handle=/u03/fast_recovery_area/ORCL2/autobackup/2015_05_10/o1_mf_s_879364591_bnz6yhy5_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 10-MAY-15
Delete all database files:
[oracle@vm501 ~]$ echo "shutdown abort;" | sqlplus / as sysdba [oracle@vm501 ~]$rm -f /u02/oradata/ORCL2/datafile/o1_mf_system_bnz5okkg_.dbf [oracle@vm501 ~]$rm -f /u02/oradata/ORCL2/datafile/o1_mf_sysaux_bnz5okll_.dbf [oracle@vm501 ~]$rm -f /u02/oradata/ORCL2/datafile/o1_mf_undotbs1_bnz5okn4_.dbf [oracle@vm501 ~]$rm -f /u02/oradata/ORCL2/datafile/o1_mf_users_bnz5oknl_.dbf [oracle@vm501 ~]$rm -f /u02/oradata/ORCL2/datafile/o1_mf_example_bnz5okmm_.dbf [oracle@vm501 ~]$rm -f /u02/oradata/ORCL2/datafile/o1_mf_temp_bnz68bpj_.tmp [oracle@vm501 ~]$rm -f /u02/oradata/ORCL2/onlinelog/o1_mf_3_bnz6887c_.log [oracle@vm501 ~]$rm -f /u03/fast_recovery_area/ORCL2/onlinelog/o1_mf_3_bnz688b0_.log [oracle@vm501 ~]$rm -f /u02/oradata/ORCL2/onlinelog/o1_mf_2_bnz687so_.log [oracle@vm501 ~]$rm -f /u03/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_bnz687wt_.log [oracle@vm501 ~]$rm -f /u02/oradata/ORCL2/onlinelog/o1_mf_1_bnz687c6_.log [oracle@vm501 ~]$rm -f /u03/fast_recovery_area/ORCL2/onlinelog/o1_mf_1_bnz687g2_.log
In order for a database instance to know about previous data files, it is necessary to restore the database control file and spfile. Don't forget to delete the previous spfile! Otherwise, using "startup nomount" would use the already existing spfile for the instance and would not allow to restore the spfile from autobackup. When a database instance is started using nomount without a spfile, Oracle will automatically use a dummy pfile.
[oracle@vm501 ~]$rm -f $ORACLE_HOME/dbs/spfileorcl2.ora
To restore any database files, RMAN requires a database instance. A database instance does not require any database files and only provides the process and memory structures that can be used by an Oracle database. Using the "startup nomount" command, the Oracle instance creates the necessary processes and memory structures, but does not access the database control file and has no knowledge about the database data files. Once the spfile has been restored, we can start the database instance with the appropriate parameters and continue with restoring the database control and data files.
After the database has been restored, the database needs to be recovered. The following shows the commands:
[oracle@vm501 ~]$ . oraenv ORACLE_SID = [orcl2] ? orcl2 The Oracle base remains unchanged with value /u01/app/oracle [oracle@vm501 ~]$ rman target / connected to target database (not started) RMAN> set DBID 843703910 RMAN> startup nomount RMAN> restore spfile from autobackup db_recovery_file_dest='/u03/fast_recovery_area' db_name='ORCL2'; RMAN> startup force nomount RMAN> restore controlfile from autobackup; RMAN> startup force mount RMAN> restore database; RMAN> recover database; RMAN-06054: media recovery requesting unknown archived log for thread 1
The RMAN-06054 error in this case is benign. It appears because the recovery procedure continues requesting the next archive log, however, the database has been already been recovered to a consistent state and there are no more archived redo logs to apply. The error could be avoided by using the UNTIL clause and specifying the last database SCN or archivelog sequence that needs to be applied, like it is done when performing a database point in time restore and recovery (PITR), but it is not necessary in this case.
The database needs to be opened with RESETLOGS, which is always necessary when restoring an RMAN backup control file, or when the online redo logs are unavailable or cannot be applied. RESETLOG creates a new database incarnation resetting the log sequence to 1 and either resets or creates new redo log files according to the database init parameter (spfile).
RMAN> alter database open resetlogs; database opened
That's all there is and the database has been restored and recovered. Since the recovery process did not use the online redo logs, this will be called an incomplete recovery and most recent information might be lost.
As outlined earlier, a full backup will always include the spfile file and database control file. If your RMAN configuration did not include "autobackup", you can still restore the spfile and control file. The process in such scenario is more difficult because you have to restore the spfile and control file by specifying the appropriate RMAN backupset. Since this information is stored in the database control file, you cannot access such information prior to restoring the spfile and control file, unless you made an appropriate note or synchronized the RMAN backup repository with an optional RMAN catalog database.
including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 10-MAY-15 channel ORA_DISK_1: finished piece 1 at 10-MAY-15 piece handle=/u03/fast_recovery_area/ORCL2/backupset/2015_05_10/o1_mf_ncsnf_TAG20150510T183149_bnz20tdn_.bkp
RMAN> restore spfile from '/u03/fast_recovery_area/ORCL2/backupset/2015_05_10/o1_mf_ncsnf_TAG20150510T183149_bnz20tdn_.bkp'; RMAN> restore controlfile from '/u03/fast_recovery_area/ORCL2/backupset/2015_05_10/o1_mf_ncsnf_TAG20150510T183149_bnz20tdn_.bkp'