Rman Backup Database Continue on Missing File

RMAN Restoring the System Parameter File Spfile

Spfile (System Parameter file and also known as Server Parameter file) is used at the time of the database startup.  Since version 9i, Oracle has been promoting using it more as compared to the traditional parameter files.  To continue encouraging DBAs to use those, Oracle - backup and recovery tool RMAN also includes the spfile in backups, making it a perfect solution to restore the file if it is lost.

It is likely that even if the spfile is lost, there will not be much that would be impacted.  Most of the databases are not rebounced for a long time and parameter change is not something which is supposed to happen at repeated intervals.  Also, the loss of spfile does not impact the normal working of the database.  But still, a loss is a loss and if we need spfile any time and it is not there, it would likely at least cause some trouble if not more major problems.  So it needs to be recovered as well.  Now look at some different ways that we can restore the file.

A Dirty Workaround

So why did we choose this title? Well, we really will be looking at dirty way(s) to get the file back in place.  What is a spfile?  It is nothing but a collection of all the parameters used and maintained by our database, and all of the database related information goes to the alert log file of it as well.  Can we smell the dirty trick coming?!

So if we have lost the spfile, the simplest way to get it back would be to open up our alert log file in our favorite editor, copy the parameter list and save the file as the parameter file. Once we have the parameter file in place, all that needs to be done is create the spfile from it. This can be done by using the following command:

SQL>
create
spfile from pfile='the_location_of_parameter_file';

In the same manner, if we have multiple databases or multiple clones running in our shop or a standby database, we can copy its parameter file, bring it to our machine and from it, we can make the spfile.  Remember to update the db_name and control_files and other parameters which are database specific!

Sure, it is a dirty way to do the task, but it is the simplest way when we do not have any backups of spfile to play around with.  And if we do not have backup, we have broken the very first rule of the backup/recovery playground.

In case we have done the backup of the spfile, either with the whole database or with the control file, we can use RMAN to restore the file as follows:

RMAN> restore spfile from autobackup;

Please note that for this command to be successful, our database must not be started from the spfile by default.  This means we must shut down the database before this command can be used.  If we have not shutdown the database before issuing this command, we would hit RMAN 06564, which essentially means that we cannot have a restore done of the spfile if we have already started it up using spfile.  So what needs to be done is to create a pfile and start our instance from that. Once that is done, use RMAN's restore command to restore the spfile.

The following code listing shows how to backup the spfile. We are not using the recovery catalog here for our database:

$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 21 05:24:26 2015 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORCL (DBID=1223106097)
RMAN> list backup of spfile;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    80.00K     DISK        00:00:01     21-OCT-09
BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag:
TAG20091021T013349
Piece Name:
/u01/app/oracle/flash_recovery_area/ORCL/backupset/
2009_10_21/o1_mf_nnsnf_TAG20091021T013349_5fw5xyw6_.bkp

  spfile Included: Modification time: 21-OCT-09

As our database was already started with the spfile, we are going to restore the spfile to another location and /tmp is the best place for this.

RMAN> restore spfile to '/tmp/spfileorcl.ora' ;
channel ORA_DISK_1: restoring spfile
output filename=/tmp/spfileorcl.ora
...........
...........
Finished restore at 21-OCT-09

If we want to use the autobackup feature of RMAN, there should be a backup of spfile done via autobackup. The following shows how to back up the system tablespace's datafile which would trigger the autobackup of the spfile as well.

RMAN> backup datafile 1;
Starting backup at 21-OCT-09
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 21-OCT-09
channel ORA_DISK_1: finished piece 1 at 21-OCT-09
piece
handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/
2009_10_21/
o1_mf_nnndf_TAG20091021T055510_5fwo78jf_.bkp tag=TAG20091021055510
comment=NONE
Finished backup at 21-OCT-09
Starting Control File and SPFILE Autobackup at 21-OCT-09
piece
handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup
/2009_10_21/
o1_mf_s_700811738_5fwo83co_.bkp comment=NONE
Finished Control File and spfile Autobackup at 21-OCT-09

Once we have ensured the autobackup of the spfile, we can use it later for restoration.   As we are not using the recovery catalog, we must tell Oracle where to find the autobackup of the spfile.  For this, the location of the backup and the database_name (even DBID will do) is required.

RMAN> restore spfile to '/tmp/spfiletemp.ora' from autobackup recovery area ='/u01/app/oracle/flash_recovery_area' db_name=orcl;

Once we have the backup restored to the /tmp folder, all that we need is to copy the file from there to the database specific location, rename it and we are done with the restoration of the spfile.

This process can be used even when we do not have the pfile also. RMAN can jump-start our database instance with a dummy parameter file just for the sake of the restoration of the spfile afterwards.  But after this, things may become complicated depending on whether we are using the recovery catalog.  If we are using recovery catalog, then it is a rather simple process. All we have to do is to issue the following command.

RMAN> restore spfile from autobackup;

Oracle will find the backup of the spfile from the automatic backup and will restore it. We can change the location if we want to, but because we are using the recovery catalog, there will not be any sort of hassles supplying the database identifier (DBID) .  That is a must, and it can be a tricky thing if we are not using the catalog.   If we are not using recovery catalog, then it would be a must to supply explicit DBID before we can continue with the above command.  And that can be difficult if we have not noted the DBID beforehand.

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata? Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just e-mail: and include the URL for the page. Copyright © 1996 -  2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.

carrollhingere.blogspot.com

Source: http://www.dba-oracle.com/t_rman_55_restore_spfile.htm

Share this post