Oracle-L: RE: pfile/spfile Sync Problems

From: <John.Hallas_at_vodafone.co.uk>
Date: Tue, 30 Jul 2002 08:18:37 -0800
Message-ID: <F001.004A5E78.20020730081837@fatcity.com>

John,
I have been discussing this with a colleague and our question is - what are you hoping to achieve?
If you are using a spfileSID.ora file then that will be used every time you startup so in what circumstances would you use the pfile?

PS one suggestion re the problem creating the trigger is to create it as user sys rather than another user

John

-----Original Message-----
Sent: 29 July 2002 21:48
To: Multiple recipients of list ORACLE-L

Hi all,

I'm in the process of migrating 8i to 9i over the next couple weeks. One of my outstanding tasks it to find a way to keep the pfile/spfile synchronized as
much as reasonable possible. I figured the easiest way would be to include startup and shutdown triggers to regenerate them from each other and include

similar work in the backup. No, it's not perfect, but should be sufficient to meet the immediate need, after all pfile will eventually go away...maybe. Anyway, I wrote the following segment which works great at the command line:

1 DECLARE

  2  spfile           VARCHAR2(120);
  3  pfile            VARCHAR2(120);
  4  cursor_handle    INTEGER;
  5  return_cd        INTEGER;

6 BEGIN
7 SELECT rtrim(a.value,'bdump')||'pfile/spfile'||b.value||'.ora', 8 rtrim(a.value,'bdump')||'pfile/init'||b.value||'.ora' 9 INTO spfile, pfile
10 FROM v$parameter a, v$parameter b 11 WHERE a.name = 'background_dump_dest' and b.name = 'db_name'; 12 cursor_handle:=DBMS_SQL.OPEN_CURSOR; 13 DBMS_SQL.PARSE(cursor_handle,'CREATE SPFILE='''||spfile||''' FROM /

PFILE='''||pfile||'''',dbms_sql.native); 14 return_cd:=DBMS_SQL.EXECUTE(cursor_handle); 15 DBMS_SQL.CLOSE_CURSOR(cursor_handle); 16 EXCEPTION
17 WHEN OTHERS THEN
18 DBMS_SQL.CLOSE_CURSOR(cursor_handle); 19* END; When I turn it into a trigger:
CREATE OR REPLACE TRIGGER sys.sync_spfile AFTER STARTUP ON DATABASE {everything else the same}

It compiles fine. However it doesn't execute on startup for some reason.

Any ideas?

TIA, John P Weatherman
Database Administrator
Replacements Ltd.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: John Weatherman
INET: john.weatherman_at_replacements.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: John.Hallas_at_vodafone.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------