Local (non-RMAN) Backups of your pfile, spfile, and control file

Recently I was working an outage on a RAC database. During the event I was asked to increase memory_target by the Product Engineering group. I decided to go ahead and do that, but when I did I forgot to set memory_max_target. Then, I shutdown down the database rather than one instance (this was early morning at this point if I recall correctly). When I tried to start it up, it wouldn’t, because memory_target was greater than memory_max_target. Personally I think this is kinda stupid, but oh well, I now had a down system.
Unfortunately I did not have a backup of the old pfile or spfile (initialization parameter files) … great. To make a long story short, after a bit the RAC was back and running, but it sure would have been easier if I’d had a backup of the pfile or spfile (whether in ASM or on a local file system.) Unfortunately, in the middle of the night I didn’t think to try a recovery from RMAN (restore spfile from autobackup).
As a result, I decided to add the following to my RMAN backup script, to ensure I always had recent pfile and spfile backups on the local filesystem where I can get to them easily. Another item I like to have is an ASCII controlfile, so I added that too.
Here is the code I added:

Some important notes:

  • I create the pfile first. If you don’t have one or it isn’t valid you’ll end up with an invalid spfile. I do this also because I like to have both for safety and editing.
  • The naming of the files is designed so that I always have a backup of each for each day for the last seven days.
 set echo off
 set feedback off
 set termout off
 set heading off
 declare
   v_count   number;
   v_baknum  number;
   v_sid     varchar2(20);
   v_pfile   varchar2(20);
   v_spfile  varchar2(20);
   v_stmt    varchar2(200);
 begin
   v_baknum:=mod(to_number(to_char(sysdate,'d')),7);
   select name into v_sid from v\$database;
   v_pfile:='init' || v_sid || '.bak.' || v_baknum;
   v_spfile:='spfile' || v_sid || '.bak.' || v_baknum;
   --does spfile exist, if only pfile then no backup made
   select count(1) into v_count from v\$parameter where name = 'spfile' and value is not null;
   if v_count = 1 then
     -- create a pfile from the "current" spfile
     v_stmt:='create pfile=''' || v_pfile || ''' from spfile';
     execute immediate v_stmt;
     -- crate a spfile from the newly created pfile (otherwise will try to use standard location which might not exist)
     v_stmt:='create spfile=''' || v_spfile || ''' from pfile=''' || v_pfile || '''';
     execute immediate v_stmt;
   end if;
end;
/
alter database backup controlfile to trace;

You might also want some code in the calling language to rename and/or move the files as you’d like them.

You should, of course, have an RMAN backup of it and you could create copies in ASM. I still like having them on a regular filesystem though.

Advertisements

3 responses to “Local (non-RMAN) Backups of your pfile, spfile, and control file

  1. Pingback: Make sure you have a good backup of your pfile, spfile, and and ASCII control file | Jed's

  2. Thanks man. If you don’t have any problems, Can i paste your Blog Link in my Blog?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s