Forum Stats

  • 3,874,709 Users
  • 2,266,767 Discussions
  • 7,911,957 Comments

Discussions

Tool to Compare PFILE/SPFILE Against Memory

Matheus Boesing
Matheus Boesing Member Posts: 46 Blue Ribbon
edited Jul 2, 2016 1:30PM in Database Ideas - Ideas

Now days, when I supporting a new/unknown database and have to restart it, I always create a ‘pfile from memory’ in case changes would be made with scope spfile/pfile before me and there is any “time bomb” prepared for me… So, in emergency cases, I always have this "backup" pfile from memory to "save me" and simply put database online until I find the problem and solve it definitively, with another restart.

But, what if I could compare the memory parameters configuration against a file (pfile or spfile) and see the difference?

It should help me to prevent a lot of misconfigurations that causes the "cant't startup" situation.

I think it’s not so hard to implement and very useful for DBA.

Matheus Boesingctrieb
5 votes

Active · Last Updated

Comments

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    Non-default instance level parameters come from :

    sql> SELECT name, value FROM V$SYSTEM_PARAMETER2 where isdefault='FALSE';

    Non-default SPFILE parameters are from:

    sql> SELECT name, value FROM v$spparameter where value is not null;

    Now you can do a quick comparison.

    Matheus BoesingGbenga AjakayeDilshanD
  • Matheus Boesing
    Matheus Boesing Member Posts: 46 Blue Ribbon
    edited Jul 3, 2016 12:17PM

    Non-default instance level parameters come from :

    sql> SELECT name, value FROM V$SYSTEM_PARAMETER2 where isdefault='FALSE';

    Non-default SPFILE parameters are from:

    sql> SELECT name, value FROM v$spparameter where value is not null;

    Now you can do a quick comparison.

    If you make a change scope=spfile and have no restarted database yet, this view will have this information?

    In case of yes, great! In case not, I believe it stills a good idea...


    But you gave a good tip!

    If exists a view like 'v$changes_scope_file' having parameters changes only scope spfile/pfile after last startup (so, it will be applied next restart), it could solve my problem, using the views you mentioned as comparison.

  • top.gun
    top.gun Member Posts: 3,666 Gold Crown

    If you make a change scope=spfile and have no restarted database yet, this view will have this information?

    In case of yes, great! In case not, I believe it stills a good idea...


    But you gave a good tip!

    If exists a view like 'v$changes_scope_file' having parameters changes only scope spfile/pfile after last startup (so, it will be applied next restart), it could solve my problem, using the views you mentioned as comparison.

    Yes, I just tried v$spparameter and the view displays parameters changed with just scope=spfile.

    I'm using 12.1.0.2.

  • Matheus Boesing
    Matheus Boesing Member Posts: 46 Blue Ribbon
    edited Jul 8, 2016 11:39AM

    Thanks, top.gun!

    This way, this problem could be solved by something like:

    SELECT name, value FROM v$spparameter where value is not null

    minus

    SELECT name, value FROM V$SYSTEM_PARAMETER2 where isdefault='FALSE';

    This is a good post about: http://exploreoracle.blogspot.com.br/2009/01/difference-between-vparameter-and_07.html

    I didn't thinked about these views, but they really solve my original problem. Thanks!