:

Remove Parameter from SPFile

I have seen the point made several times where an Oracle document says to remove or unset a parameter in the database. Based on what I have seen and tried, here is the way that I would recommend to do it.

I’ll start with an example of how to do it:

ALTER SYSTEM RESET remote_archive_enable SCOPE=SPFILE SID=‘*’;

It is important to put the * in single quotes.
You can use a * or the SID of the database for the SID = value.

I did find a few issues when using it for a database where I was trying to unset an Oracle parameter from the SPFILE, which can be seen below:

SQL> alter system reset remote_archive_enable scope=both sid=’DB1′;
alter system reset remote_archive_enable scope=both sid=’DB1′
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

This time we will try specifying spfile rather than both:

SQL> alter system reset remote_archive_enable scope=both sid=’*’;
alter system reset remote_archive_enable scope=both sid=’*’
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance db1

With this final variation, we use scope=spfile and the sid set to * and we find that it works well.

SQL> alter system reset remote_archive_enable scope=spfile sid=’*’;

System altered.

Like it, share it...

Category: Database Config


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *