Thursday, December 15, 2011

Reset/Unset Oracle parameters


A parameter value can be reset to the default using the following command

Note: Do not try to change the value of a parameter with” (or) (‘) or (null) to reset it to default
Below example shows, how to reset a oracle parameter. I am running this test on oracle 11gR2(11.2.0.2.0)


SQL> show parameter convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
db_file_name_convert                 string   +DATA1,+DG_DATA1
log_file_name_convert                string   +LOG,+DG_LOG


SQL> alter system reset db_file_name_convert scope=spfile sid='*';

System altered.

SQL> alter system reset log_file_name_convert scope=spfile sid='*';

System altered.

Resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported. If you proceeding with the parameter change, you will get an error message as below


SQL> alter system reset db_file_name_convert scope=both sid='*';
alter system reset db_file_name_convert scope=both sid='*'
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported


I tried to change a dynamic parameter which can changed during the run time, even those parameter are not supported in “RESET” with memory or both option

SQL> alter system set log_archive_dest_state_3=DEFER scope=both sid='*';

System altered.

SQL> show parameter log_archive_dest_state_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3             string      DEFER

SQL> alter system reset log_archive_dest_state_3 scope=both sid='*';
alter system reset log_archive_dest_state_3 scope=both sid='*'
*
ERROR at line 1:
ORA-32029: resetting with SCOPE=MEMORY or SCOPE=BOTH is currently not supported


SQL> alter system set log_archive_dest_state_3=ENABLE scope=both sid='*';

System altered.

I am able to change the parameter with out bouncing the database.

SQL> show parameter log_archive_dest_state_3

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3             string      ENABLE

1 comment:

  1. So how to unset the parameter for log_archive_dest_state_3

    ReplyDelete