Your database instance is started using the server parameter file (SPFILE). You executed a command
to change the value of the LOG_BUFFER initialization parameter:
ALTER SYSTEM SET LOG_BUFFER=32M SCOPE=BOTH;
What would be the outcome of this command?
A.
The command succeeds only if Automatic Memory Management is not enabled.
B.
The command succeeds, but you need to restart the database for changes to take effect.
C.
The command returns an error because the size of the redo log buffer cannot be changed
dynamically.
D.
The parameter value is changed and it comes into effect as soon as space becomes available in
the System Global Area (SGA).
sys @ TEST0910> show parameter log_b
NAME TYPE VALUE
———————————— ———– — —————————
log_buffer integer 19365888
sys @ TEST0910> ALTER SYSTEM SET LOG_BUFFER = 32M SCOPE = BOTH;
ALTER SYSTEM SET LOG_BUFFER = 32M SCOPE = BOTH
*
ERROR at line 1:
ORA-02095: specified initialization parameter can not be modified
sys @ TEST0910> select NAME, ISSES_MODIFIABLE, ISSYS_MODIFIABLE from v $ parameter where name like ‘% log_b%’;
NAME ISSES ISSYS_MOD
——————– —– ———
log_buffer FALSE FALSE
sys @ TEST0910> select distinct ISSYS_MODIFIABLE from v $ parameter;
ISSYS_MOD
———
IMMEDIATE dynamic parameters in the system to directly modify the memory parameters alter system set parameter = value;
FALSE static parameter indicates next time you start to take effect and can not be changed directly into memory alter system set parameter = value scope = spfile;
LOG_BUFFER static parameters, so scope can not both, can only use spfile.
DEFERRED session parameters, alter session parameter = value, effective immediately, after the next start is invalid
sys @ TEST0910> ALTER SYSTEM SET LOG_BUFFER = 32M SCOPE = spfile;
ALTER SYSTEM SET LOG_BUFFER = 32M SCOPE = spfile
*
ERROR at line 1:
ORA-02095: specified initialization parameter can not be modified
sys @ TEST0910> ALTER SYSTEM SET LOG_BUFFER = 32M SCOPE = spfile sid = ‘*’;
ALTER SYSTEM SET LOG_BUFFER = 32M SCOPE = spfile sid = ‘*’
*
ERROR at line 1:
ORA-02095: specified initialization parameter can not be modified
IMMEDIATE dynamic parameters in the system to directly modify the memory parameters alter system set parameter = value;
FALSE static parameter indicates next time you start to take effect and can not be changed directly into memory alter system set parameter = value scope = spfile;
LOG_BUFFER static parameters, so scope can not both, can only use spfile.
DEFERRED session parameters, alter session parameter = value, effective immediately, after the next start is invalid
ALTER SYSTEM SET LOG_BUFFER = 32M SCOPE = spfile sid = ‘*’
*
ERROR at line 1:
ORA-02095: specified initialization parameter can not be modified