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.