Tuesday, January 30, 2018

Recovery Scenarios | Loss Of SPFILE

SPFILE is a small but very important file and if we lose the spfile, we cannot start the database even if all the other database files are present and intact.You might need to restore the spfile for one of several reasons:
• You accidentally deleted your server parameter file.
• You want to view an old copy of the spfile.
• You can’t start your instance with the current spfile.

If you’re using an spfile, you can have it automatically backed up for you by enabling the autobackup of the control file.By default, automatic control file backups are disabled. Even when the auto backup feature is disabled, RMAN will back up the current control file and the server parameter file whenever any backup command includes data file 1 from the data files that belong to the target database. In an Oracle database, data file 1 is always part of the system tablespace, which contains the data dictionary. You can configure RMAN to automatically back up the control file
following every backup and any database structural change by using the configure command.

When does the SPFILE gat backed up with AUTOBACKUP tuned on?
Once you configure automatic control file backup, RMAN will automatically back up your target database control file, as well as the current server parameter file, when any of the following events occurs:
• Successful completion of either a backup or the copy command
• After a create catalog command from the RMAN prompt is successfully completed
• Any structural changes to the database modify the contents of the control file

After a backup or copy command completes and the recovery catalog—if you are using one—is successfully updated, RMAN will then back up the control file to its own backup piece. In addition, any changes to the physical structure of your database, even if they are made through SQL*Plus, will trigger a control file auto backup. (For example, the following actions will trigger an auto backup of the control file: adding a tablespace or data file, dropping a data file, placing a tablespace offline or online, adding an online redo log, and renaming a data file.)

When automatic backup is triggered by a structural change, an Oracle server process (not an RMAN process) will automatically create the auto backup of your control file.

Check Autobackup Status 

Enable Autobackup of controlfile

Where does the SPFILE autobackup goes?
You’ve just enabled the auto backup of the control file feature, but you don’t know where the files are physically being written. You want to ensure that these critical backups are being written to a location you know about so that you can maintain and monitor that location.

1. If you have enabled a fast area as well as the auto backup of the control file, then RMAN will write the backup to the directory defined for the fast area. By default, RMAN creates these files as Oracle managed files.

2. If you haven’t enabled a fast recovery area, RMAN will create the auto backups in an operating system–specific location ($ORACLE_HOME/dbs on Unix and %ORACLE_HOME%\database on Windows).

3. You can also configure the auto backup to back up the control file to an automatic storage management (ASM) disk group, as shown in the following example:
RMAN> configure controlfile autobackup format for device type disk to '+DATA/%F';

4. You can override where RMAN will write the auto backup control file and its name using the configure command.

Note : The format variable %F yields a unique combination of the database ID, day, month, year, and sequence.

5. To set the directory and file format back to the default value, run this command:
RMAN> configure controlfile autobackup format for device type disk clear;

6. To list backup of spfile use command  : "list backup of spfile;"

Recovery scenarios involving loss of SPFILE

Case 1 : Using a Recovery Catalog
If you’re using a recovery catalog, then restoring the spfile is fairly straightforward.

$ rman target / catalog rman/xxxx@racdb
RMAN> startup nomount;
RMAN> restore spfile;
RMAN> startup force; # startup using restored spfile

If you receive an error such as this when running the restore command:
RMAN-20001: target database not found in recovery catalog

Then first set the DBID
RMAN> set dbid 961393455;

Case 2 : Not Using a Recovery Catalog, RMAN Auto Backup in Default Location
In this case we assume that auto backups of the spfile are going to the default location.
The default location depends on your operating system. For Linux/Unix, the default location is ORACLE_HOME/dbs.On Windows systems, it’s usually ORACLE_HOME\database.

$ rman target /
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> set dbid 961393455;
RMAN> restore spfile from autobackup;
RMAN> startup force; # startup using restored spfile

Case 3 : Not Using a Recovery Catalog, RMAN Auto Backup Not in Default Location

If you’re either using an FRA or have the auto backup of your control file configured to a nondefault location, then the spfile will not be backed up to what Oracle calls the default location. In these situations, you have to specifically tell RMAN where to retrieve the backup from.

If you’re using an FRA, your spfiles will be backed up in an auto backup directory in the FRA. You’ll have to find that directory and backup piece name before you can restore your spfile.You’ll also need to know your database identifier before you can proceed.

$ rman target /
RMAN> set dbid 961393455;
RMAN> startup force nomount; # start instance for retrieval of spfile
RMAN> restore spfile from '/u01/';
RMAN> startup force; # startup using restored spfile

Suppose you are not sure which backup file contains the SPFILE backup. So to work around this, we tried to fool RMAN by creating the directory structure when using a FRA. We create the directory structure RACDB/autobackup/2018_01_30 under the to level location /u01/ and copy the backup pieces to this location.

$ cd /u01/
$mkdir -p RACDB/autobackup/2018_01_30
$ mv /u01/* /u01/

Now we can restore spfile using command :-
restore spfile from autobackup db_recovery_file_dest='/u01/' db_name='RACDB';

1 comment:

  1. I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. ανακτηση δεδομενων