Tuesday, February 09, 2010

ALTER SYSTEM SET.. SID='FUN'

Just fun today, I altered to change some parameter on Database by using SID='sid'
Example:
SYS@db1> show parameter pga_aggregate_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M

SYS@db1> select instance_name from v$instance;

INSTANCE_NAME
----------------
db1

1 row selected.
Used upper characters:(Be able to alter, but parameter not change... because wrong SID)
SYS@db1> alter system set pga_aggregate_target=100M sid='DB1';

System altered.

SYS@db1> show parameter pga_aggregate_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M
Used sid='FUN': (Be able to alter, but parameter not change... because wrong SID)
SYS@db1> alter system set pga_aggregate_target=100M sid='FUN';

System altered.

SYS@db1> show parameter pga_aggregate_target;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 200M
Used sid='select instance_name from v$instance': (Be able to alter and parameter changed)
SYS@db1> alter system set pga_aggregate_target=100M sid='db1';

System altered.

SYS@db1> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 100M
So, Checked on v$spparameter and in spfile file -)
SYS@db1> select SID, NAME, VALUE from v$spparameter where name like '%pga%';

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
Reset Wrong SID:
SYS@db1> alter system reset pga_aggregate_target sid='FUN';

System altered.

SYS@db1> alter system reset pga_aggregate_target sid='DB1';

System altered.
This taught me check SID before... before ALTER SYSTEM.. SID='sid'. read more ALTER SYSTEM
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 -)

6 comments:

Martin Berger said...

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).

Surachart Opun said...

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 -)

Babu said...

:-)

Nice post

Babu said...

:-)

Nice post

Marcel DeMaria said...

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.

Surachart Opun said...

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 -)