We do essentially the same thing nightly from a dbms
job. Works fine that way. Don't know why yours does
not work. We create the job and run it as "sys as
sysdba". Perhaps it is a permissions thingy.
--- John Weatherman <[EMAIL PROTECTED]>
wrote:
> 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Pass
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
