Example:
SYS@db1> show parameter pga_aggregate_target;Used upper characters:(Be able to alter, but parameter not change... because wrong SID)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M
SYS@db1> select instance_name from v$instance;
INSTANCE_NAME
----------------
db1
1 row selected.
SYS@db1> alter system set pga_aggregate_target=100M sid='DB1';Used sid='FUN': (Be able to alter, but parameter not change... because wrong SID)
System altered.
SYS@db1> show parameter pga_aggregate_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M
SYS@db1> alter system set pga_aggregate_target=100M sid='FUN';Used sid='select instance_name from v$instance': (Be able to alter and parameter changed)
System altered.
SYS@db1> show parameter pga_aggregate_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M
SYS@db1> alter system set pga_aggregate_target=100M sid='db1';So, Checked on v$spparameter and in spfile file -)
System altered.
SYS@db1> show parameter pga_aggregate_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 100M
SYS@db1> select SID, NAME, VALUE from v$spparameter where name like '%pga%';Reset Wrong SID:
SID NAME VALUE
----- ------------------------------ ------------------------------
db1 pga_aggregate_target 104857600
FUN pga_aggregate_target 104857600
* pga_aggregate_target 209715200
DB1 pga_aggregate_target 104857600
SYS@db1> create pfile='/tmp/pfile' from spfile;
File created.
$ grep pga_aggregate_target /tmp/pfile
*.pga_aggregate_target=209715200
DB1.pga_aggregate_target=104857600
FUN.pga_aggregate_target=104857600
db1.pga_aggregate_target=104857600
SYS@db1> alter system reset pga_aggregate_target sid='FUN';This taught me check SID before... before ALTER SYSTEM.. SID='sid'. read more ALTER SYSTEM
System altered.
SYS@db1> alter system reset pga_aggregate_target sid='DB1';
System altered.
The SID clause is relevant only in a Real Application Clusters environment. This clause lets you specify the SID of the instance where the value will take effect.
- Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.
- Specify SID = 'sid' if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = '*'.
Just fun -)
Posts
6 comments:
not just fun:
you can use this method to store old or even suggested values directly in the spfile. (maybe with change-reference or other information).
To Martin,
Thank you for your suggestion.
Good Idea to keep old_value and ... in spfile.. Nice -)
But I surprised when I forgot to check SID (upper or lower) before... that made me, Oh! why parameter value not change???
By the way, Hope everything in your life OK -)
:-)
Nice post
:-)
Nice post
Yep a real gotcha - case sensitivity on Linux/Unix - another way to get the sid is from a running instance's processes.
ps -ef |grep pmon will show the sid as it appears in v$instance (case is the same as v$instance and gv$instance - in RAC; also one you can use to set your env $ORACLE_SID to or use in your case an alter system. - It works for ASM as well. It works for getting ht ASM sid as well(by the running processes). Also since on RAC you may want to use gv$instance instead of v$instance. - Congrats on becoming an Oracle ACE - I need to get into that as well.
To Marcel DeMaria
Thank You for comment.
Oracle ACE is good thing in My Oracle Job, Actually I should say 'In My Oracle Community'.And This is the beginning.
Good for case sensitivity, when instance read initialized parameters. But we have to know about SID='', that can use any word -)
Post a Comment