/*
-- Ever managed to corrupt a SPFILE by entering a value that wasn't good
-- but Oracle didn't stop you and now you can't start your database?
-- This tip helps prevent the frantic scrabbling around for a backup pfile, or having
-- to dig it out of a backup tape, or using strings, or...
--
-- This trigger automatically creates a pfile at startup from the spfile.
-- As the database managed to start (the trigger won't fire otherwise)
-- the spfile that was used to start it is known to be good.
-- Therefore you *always* have a pfile copy of the spfile that last succesfully started
-- the database.
--
-- The trigger comes with two variables for easy configuration:
-- DFM is the date format mask that is embedded into the filename, I prefer an ISO data format.
-- LTCP is the location that the pfile will be created. By leaving it at NULL, the trigger wll
-- create the pfile in an OS dependent location, normally $ORACLE_HOME/dbs for Unix and
-- %ORACLE_HOME%\database for Windows.
--
-- This code has been tested and works as intended on Oracle 9205 EE.
--
-- Credits: The query to determine if the database is using a spfile is from Tom Kyte.
--
-- Get this tip and others, along with useful PL/SQL utilities at
-- www.chrispoole.co.uk
--
*/


CREATE OR REPLACE TRIGGER create_last_known_good_pfile
AFTER STARTUP ON DATABASE
DECLARE

-- change these variables to configure the trigger
dfm VARCHAR2(50) := 'YYYYMMDDHH24MISS'; -- Date Format Mask
ltcp VARCHAR2(256) := NULL; -- Location To Create Pfile

tsas VARCHAR2(50); -- TimeStamp At Startup
spff VARCHAR2(6); -- SPFile Found
sid v$instance.instance_name%TYPE; -- the SID of the instance

BEGIN

-- are we using a spfile?
SELECT DECODE(COUNT(*),1, 'SPFILE','PFILE')
INTO spff
FROM v$spparameter
WHERE ROWNUM= 1 AND
isspecified = 'TRUE';

IF (spff='SPFILE') -- only create a pfile if we are using an spfile
THEN

-- get the name of the instance
SELECT UPPER(instance_name) INTO sid FROM v$instance WHERE ROWNUM = 1;
-- get the date string to embed into the filename
tsas := TO_CHAR(SYSDATE, dfm);
-- create the pfile!
EXECUTE IMMEDIATE 'CREATE PFILE='''||ltcp||'lkg_'||tsas||'_init'||sid||'.ora'' FROM SPFILE';

END IF;

END;
/