Recreating SPFILE

We received a helpdesk email from a user attempting to time travel on a local development server.
The system date was set to a value in the past. They were attempting to bring the date to now.
The way which this is done is by procedure which launches dynamic SQL and modifies the SPFILE and memory through script similar to the following:

if paramater_date = 'NONE' then
--Remove the entry from SPFILE
execute immediate 'alter system reset fixed_date scope=spfile sid='||''''||'*'||'''';
--Reset value inside memory
execute immediate 'alter system set fixed_date='NONE' scope=memory';
else
--Set value to supplied parameter
execute immediate 'alter system set fixed_date='||''''||parameter_date||'''';
end if;

The user was receiving the following error:

Line Pos Text
202 1 ORA-32001: write to SPFILE requested but no SPFILE is in use
ORA-06512: at "ADMIN.SET_SYSDATE", line 6
ORA-06512: at line 2

I logged into the development database and queried the spfile parameter to see if an SPFILE had been set since the last database refresh:

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

SQL>

No value had been specified.

So when procedure fires, it attempts to reset a value in a file not specified.
The solution to this error is to create an spfile from the last pfile successfully used to refresh,load or restore the database.

By default, pfiles and spfiles can be found within the $ORACLE_HOME/dbs directory.

server01:~ $ ll $ORACLE_HOME/dbs/initorcl*
-rw-r--r-- 1 oracle oinstall 1342 Sep 7 10:45 /app/oracle/product/11.2.0/dbs/initorcl.ora
server01:~ $

For this instance, I choose to restore an spfile from the pfile in the location
/app/oracle/product/11.2.0/dbs/initorcl.ora

We do this by running

SQL> CREATE SPFILE FROM PFILE='/app/oracle/product/11.2.0/dbs/initorcl.ora'
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Now, when the user makes a request to the proc, the proc can now complete the command it originally set out to execute!

exec ops$admin.set_sysdate('NONE');
select sysdate from dual;

ORA-32010: cannot find entry to delete in SPFILE
ORA-06512: at "ADMIN.SET_SYSDATE", line 6
ORA-06512: at line 2

Okay not quite. Whats happening here is that the command:

execute immediate 'alter system reset fixed_date scope=spfile sid='||''''||'*'||'''';

results in Oracle searching the spfile for the entry *.fixed_date
There is a chance the the spfile no longer has the parameter *.fixed_date.
This could have happened in 2 ways:
First, the user may have executed the proc with NONE as the parameter, which would have removed the the parameter in the first transaction successfully.
THEN the next or same person comes along and executes the same proc AGAIN at which time, the parameter no longer exists resulting in the error.

Second, the pfile may have never HAD the parameter in it, therefore, the solution would be to either alter the pfile and include
the paramater then create SPFILE from this, or to create the parameter at run time by firing the following cmd:

alter system set fixed_date='NONE' scope=both

This would create the parameter in the spfile and memory and so if the proc is fired, the parameter will successfully be removed