This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Apr 16, 2012 8:05 AM by EdStevens RSS

alter system  scope=spfile not taking

EdStevens Guru
Currently Being Moderated
This should have been brain-dead simple but something is happening I've never seen and don't understand.

Oracle SE 10.2.0.4 on Windows Serve 2003

I do an ALTER SYSTEM ... SCOPE=SPFILE, but when the database is restarted, values have reverted back.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      E:\ORACLE\PRODUCT\10.2.0\ROY\D
                                                 ATABASE\SPFILEROYDEV.ORA
SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +SAN/roydev/controlfile/curren
                                                 t.261.686792331, +SAN/roydev/c
                                                 ontrolfile/current.260.6867923
                                                 31
SQL> --
SQL> alter system set control_files='x:\oradata\roydev\controlfile\control01.ctl','y:\oradata\roydev\controlfile\control02.ctl' scope=spfile;

System altered.

SQL> --
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1298192 bytes
Variable Size             167772400 bytes
Database Buffers          440401920 bytes
Redo Buffers                2895872 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      E:\ORACLE\PRODUCT\10.2.0\ROY\D
                                                 ATABASE\SPFILEROYDEV.ORA
SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +SAN/roydev/controlfile/curren
                                                 t.261.686792331, +SAN/roydev/c
                                                 ontrolfile/current.260.6867923
                                                 31
SQL> spool off
  • 1. Re: alter system  scope=spfile not taking
    rp0428 Guru
    Currently Being Moderated
    >
    I do an ALTER SYSTEM ... SCOPE=SPFILE, but when the database is restarted, values have reverted back.
    >
    Does the timestamp on the spfile change after the alter?

    Also curious as to why you aren't showing that you actually copied the control files to the new location after the shutdown.

    Can you confirm that other parameter changes get reflected in the spfile properly?

    --- added another confirmation
    Can you confirm that you intentionally use 'x' for one control file and 'y' for the other?
    >
    'x:\oradata\roydev\controlfile\control01.ctl','y:
    >

    Edited by: rp0428 on Apr 14, 2012 8:20 AM
  • 2. Re: alter system  scope=spfile not taking
    Lubiez Jean-Valentin Guru
    Currently Being Moderated
    Hello,


    It seems that you use Oracle Managed Files. I don't have a lot of experience in OMF, but I'm not sure that the parameter CONTROL_FILES behaves as usual in this case.

    I think you may check for the Parameters below first:
    DB_CREATE_FILE_DEST
    DB_CREATE_ONLINE_LOG_DEST_n
    DB_RECOVERY_FILE_DEST
    May be some of them are set and prevails on the Control file location.

    This Note may be interesting:

    - *Configuring file creation in Flash recovery area and order of Precedence [ID 305810.1]*


    Sorry, When I saw the curious extension ( .260.686792331 ) of the control files, I first thought about Oracle Managed Files. But I think the problem is elsewhere.


    Best regards,
    Jean-Valentin

    Edited by: Lubiez Jean-Valentin on Apr 14, 2012 5:48 PM
  • 3. Re: alter system  scope=spfile not taking
    EdStevens Guru
    Currently Being Moderated
    Immediately after posting, I decided to try creating a pfile from the spfile. Examining that file, it shows my control_files parameter changed to what I specified in my ALTER SYSTEM, but also shows
    *.SPFILE='+SAN/ROYDEV/spfileROYDEV.ora'
    HMM. And that spfile spec is NOT what we saw from a SHOW PARAMETER. So it looks like my default spfile references another spfile under ASM, and that file is overriding at least some of the parameters I specified.

    Hmm..
  • 4. Re: alter system  scope=spfile not taking
    Girish Sharma Guru
    Currently Being Moderated
    Ed,

    I am not sure how much below link may be helpful to you, but still posting... if you missed to check it.

    http://zhefeng.wordpress.com/2011/03/02/recreating-spfile-on-asm-storage-from-pfile/

    But, I am curious why in the end of your first post ?
    SQL> spool off
    Regards
    Girish Sharma
  • 5. Re: alter system  scope=spfile not taking
    EdStevens Guru
    Currently Being Moderated
    Ok, here's what I figured out (and I don't have any experience with Oracle Managed files either)...

    The db was using the default spfile, to a point. When I did my ALTER SYSTEM, the timestamp on the default spfile was changing, and that was part of what threw me off. I finally created a pfile from the spfile, and that pfile reflected my changes, but also included a parameter -- spfile=+ASM/... yada, yada. Looking at the contents of the actual spfile (yes, I know it's a binary, but the parms themselves are clear text, so you can see what's going on .. just don't try to edit it). I saw exactly what was in the created pfile.

    Next I renamed my newly created pfile, then did another CREATE PFILE FROM SPFILE, only this time specified the spfile name, pointing to the ASM based file. Sure enough, none of my changes were reflected in that file; further evidence that it was overriding. I did a sanity check on the two pfiles, merging a few things from the first into the one created from the ASM file, the created a new default spfile from that. After that life was good.

    This was a first for me. Of course I've been at this long enough to have used pfiles before there were spfiles. I've seen pfiles that had a single line, 'SPFILE= ..' to point to some non-standard spfile. But I've never seen an spfile itself refer to yet another spfile.

    Oh well. All's well that ends well, and I learned something new.
  • 6. Re: alter system  scope=spfile not taking
    EdStevens Guru
    Currently Being Moderated
    EdStevens wrote:
    Ok, here's what I figured out (and I don't have any experience with Oracle Managed files either)...

    The db was using the default spfile, to a point. When I did my ALTER SYSTEM, the timestamp on the default spfile was changing, and that was part of what threw me off. I finally created a pfile from the spfile, and that pfile reflected my changes, but also included a parameter -- spfile=+ASM/... yada, yada. Looking at the contents of the actual spfile (yes, I know it's a binary, but the parms themselves are clear text, so you can see what's going on .. just don't try to edit it). I saw exactly what was in the created pfile.

    Next I renamed my newly created pfile, then did another CREATE PFILE FROM SPFILE, only this time specified the spfile name, pointing to the ASM based file. Sure enough, none of my changes were reflected in that file; further evidence that it was overriding. I did a sanity check on the two pfiles, merging a few things from the first into the one created from the ASM file, the created a new default spfile from that. After that life was good.

    This was a first for me. Of course I've been at this long enough to have used pfiles before there were spfiles. I've seen pfiles that had a single line, 'SPFILE= ..' to point to some non-standard spfile. But I've never seen an spfile itself refer to yet another spfile. And THAT was the big red herring. The database reported that it was using an spfile, and pointed to the default location and name. It never occurred to me that someone would chain one spfile off of another.

    Oh well. All's well that ends well, and I learned something new.
  • 7. Re: alter system  scope=spfile not taking
    EdStevens Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    I do an ALTER SYSTEM ... SCOPE=SPFILE, but when the database is restarted, values have reverted back.
    >
    Does the timestamp on the spfile change after the alter?

    Also curious as to why you aren't showing that you actually copied the control files to the new location after the shutdown.
    I had copied them. Just figured that part was a given, I guess.
    >
    Can you confirm that other parameter changes get reflected in the spfile properly?
    Actually, I had a couple of other parameters (DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST) that were showing the same behavior.

    >
    --- added another confirmation
    Can you confirm that you intentionally use 'x' for one control file and 'y' for the other?
    Yes, those are the real specs.
    >
    'x:\oradata\roydev\controlfile\control01.ctl','y:
    >

    Edited by: rp0428 on Apr 14, 2012 8:20 AM
  • 8. Re: alter system  scope=spfile not taking
    Lubiez Jean-Valentin Guru
    Currently Being Moderated
    Hello,


    Thank Ed to share us your experience. I'm sure it will be useful.


    Best regards,
    Jean-Valentin
  • 9. Re: alter system  scope=spfile not taking
    EdStevens Guru
    Currently Being Moderated
    Girish Sharma wrote:
    Ed,

    I am not sure how much below link may be helpful to you, but still posting... if you missed to check it.

    http://zhefeng.wordpress.com/2011/03/02/recreating-spfile-on-asm-storage-from-pfile/

    But, I am curious why in the end of your first post ?
    SQL> spool off
    I was spooling the session. Just picked up that line as I was copying from the spool file to this post.
    Regards
    Girish Sharma
  • 10. Re: alter system  scope=spfile not taking
    Girish Sharma Guru
    Currently Being Moderated
    EdStevens wrote:
    Ok, here's what I figured out (and I don't have any experience with Oracle Managed files either)...

    The db was using the default spfile, to a point. When I did my ALTER SYSTEM, the timestamp on the default spfile was changing, and that was part of what threw me off. I finally created a pfile from the spfile, and that pfile reflected my changes, but also included a parameter -- spfile=+ASM/... yada, yada. Looking at the contents of the actual spfile (yes, I know it's a binary, but the parms themselves are clear text, so you can see what's going on .. just don't try to edit it). I saw exactly what was in the created pfile.

    Next I renamed my newly created pfile, then did another CREATE PFILE FROM SPFILE, only this time specified the spfile name, pointing to the ASM based file. Sure enough, none of my changes were reflected in that file; further evidence that it was overriding. I did a sanity check on the two pfiles, merging a few things from the first into the one created from the ASM file, the created a new default spfile from that. After that life was good.

    This was a first for me. Of course I've been at this long enough to have used pfiles before there were spfiles. I've seen pfiles that had a single line, 'SPFILE= ..' to point to some non-standard spfile. But I've never seen an spfile itself refer to yet another spfile.

    Oh well. All's well that ends well, and I learned something new.
    Sorry Ed, but I am still not able to understand your solution because I am bit confused of below text specially :
    only this time specified the spfile name
    what you specified here other than first one's pfile spfile=+ASM/...folder1/folder2 ?
    Sure enough, none of my changes were reflected in that file; further evidence that it was overriding.
    completely failed to understand this line, what happened 2nd time that you did't got changes reflected; while you got in the first one pfile.

    The more cause of my confusion may be poor and bad in English, but I know, I don't have poor wish of oracle learning, so I am asking for removing doubts please.

    Regards
    Girish Sharma
  • 11. Re: alter system  scope=spfile not taking
    734288 Newbie
    Currently Being Moderated
    Here are the steps to perform the change in SPFILE but it's not working with alter system set command.

    login as sys to Oracle

    1. create pfile from spfile;
    2. Stop the database
    3. go to Oracle_Home/dbs directory and rename spfile<sid>.ora to bak file. Make sure there is no other spfile available except pfile for the instance.
    3. edit the pfile with the correct name and location of the control file.
    4. create spfile from spfile before the DB starts
    5. Verify the change what it made in step 3.

    Hope this help you

    Thanks
    Jandial
  • 12. Re: alter system  scope=spfile not taking
    713555 Pro
    Currently Being Moderated
    sounds very similar to this I had last week

    ora-01506 no database name

    in ORACLE_HOME\database , rename the init.ora, init<SID>.ora, spfile<SID>.ora to .bak as we know oracle looks for those default files first (which you say you did) I was starting with a pfile from a different location and "create spfile from pfile" uses the pfile thats in the ORACLE_HOME\init<sid>.ora if one existed unless you specify otherwise.

    If youd ever started that database with an spfile the pfile would have had only the reference to the spfile in it creating the loop.
  • 13. Re: alter system  scope=spfile not taking
    EdStevens Guru
    Currently Being Moderated
    Girish Sharma wrote:
    EdStevens wrote:
    Ok, here's what I figured out (and I don't have any experience with Oracle Managed files either)...

    The db was using the default spfile, to a point. When I did my ALTER SYSTEM, the timestamp on the default spfile was changing, and that was part of what threw me off. I finally created a pfile from the spfile, and that pfile reflected my changes, but also included a parameter -- spfile=+ASM/... yada, yada. Looking at the contents of the actual spfile (yes, I know it's a binary, but the parms themselves are clear text, so you can see what's going on .. just don't try to edit it). I saw exactly what was in the created pfile.

    Next I renamed my newly created pfile, then did another CREATE PFILE FROM SPFILE, only this time specified the spfile name, pointing to the ASM based file. Sure enough, none of my changes were reflected in that file; further evidence that it was overriding. I did a sanity check on the two pfiles, merging a few things from the first into the one created from the ASM file, the created a new default spfile from that. After that life was good.

    This was a first for me. Of course I've been at this long enough to have used pfiles before there were spfiles. I've seen pfiles that had a single line, 'SPFILE= ..' to point to some non-standard spfile. But I've never seen an spfile itself refer to yet another spfile.

    Oh well. All's well that ends well, and I learned something new.
    Sorry Ed, but I am still not able to understand your solution because I am bit confused of below text specially :
    only this time specified the spfile name
    what you specified here other than first one's pfile spfile=+ASM/...folder1/folder2 ?
    Sure enough, none of my changes were reflected in that file; further evidence that it was overriding.
    completely failed to understand this line, what happened 2nd time that you did't got changes reflected; while you got in the first one pfile.

    The more cause of my confusion may be poor and bad in English, but I know, I don't have poor wish of oracle learning, so I am asking for removing doubts please.

    Regards
    Girish Sharma
    Ok, let me see if I can clarify.

    When first approaching the database, I checked to see if it was using a pfile or spfile. A 'show parameter spfile' indicated it was using an spfile, and it was the default name in the default location (%ORACLE_HOME\database\spfile%ORACLE_SID%.ora).

    Next I did a couple of ALTER SYSTEM SET .... SCOPE=SPFILE commands.

    Next I bounced the database and rechecked the parameters I had set, and they were back to their original values.

    After a couple of more iterations to make sure I wasn't doing something really stupid (after all, this should have been dead simple) I opened this thread, then continued to poke at it. So,

    Next I created a default pfile from the default spfile - CREATE PFILE FROM SPFILE. This created %ORACLE_HOME%\database\init%ORACLE_SID%.ora.
    On examining the resulting pfile, I saw my changes reflected in it, but also was surprised to see the parameter *.SPFILE=+ASM/...., so was including an addition spfile that was under ASM. (BTW, the entire purpose of this exercise was to migrate the database off of ASM.) At this point, as a sanity check I opened the default spfile - the one pointed to by the SHOW PARAMTER SPFILE command, the one in ORACLE_HOME\database. It did - as it should have - matched perfectly with the newly created pfile. So it was apparent that my ALTER SYSTEM changes were going to this default spfile but - unbeknownst to me at the outset - were being over-ridden by that second, ASM-based spfile.

    At this point, I renamed the just-created pfile to something like initORCL.sav. Then created a new pfile from the ASM-based spfile - CREATE PFILE FROM SPFILE='+ASM\spfile.ora'. This new pfile was much more extensive than the first, and there were the parameters that were overriding the ones I was trying to change. I hand-modified this pfile, including some other things I found in the first, then created a new default spfile from it. So at this point I had the default spfile without it pulling in a second one that would override what I did.

    Does that clarify?
  • 14. Re: alter system  scope=spfile not taking
    Girish Sharma Guru
    Currently Being Moderated
    EdStevens wrote:
    Girish Sharma wrote:
    EdStevens wrote:
    Ok, here's what I figured out (and I don't have any experience with Oracle Managed files either)...

    The db was using the default spfile, to a point. When I did my ALTER SYSTEM, the timestamp on the default spfile was changing, and that was part of what threw me off. I finally created a pfile from the spfile, and that pfile reflected my changes, but also included a parameter -- spfile=+ASM/... yada, yada. Looking at the contents of the actual spfile (yes, I know it's a binary, but the parms themselves are clear text, so you can see what's going on .. just don't try to edit it). I saw exactly what was in the created pfile.

    Next I renamed my newly created pfile, then did another CREATE PFILE FROM SPFILE, only this time specified the spfile name, pointing to the ASM based file. Sure enough, none of my changes were reflected in that file; further evidence that it was overriding. I did a sanity check on the two pfiles, merging a few things from the first into the one created from the ASM file, the created a new default spfile from that. After that life was good.

    This was a first for me. Of course I've been at this long enough to have used pfiles before there were spfiles. I've seen pfiles that had a single line, 'SPFILE= ..' to point to some non-standard spfile. But I've never seen an spfile itself refer to yet another spfile.

    Oh well. All's well that ends well, and I learned something new.
    Sorry Ed, but I am still not able to understand your solution because I am bit confused of below text specially :
    only this time specified the spfile name
    what you specified here other than first one's pfile spfile=+ASM/...folder1/folder2 ?
    Sure enough, none of my changes were reflected in that file; further evidence that it was overriding.
    completely failed to understand this line, what happened 2nd time that you did't got changes reflected; while you got in the first one pfile.

    The more cause of my confusion may be poor and bad in English, but I know, I don't have poor wish of oracle learning, so I am asking for removing doubts please.

    Regards
    Girish Sharma
    Ok, let me see if I can clarify.

    When first approaching the database, I checked to see if it was using a pfile or spfile. A 'show parameter spfile' indicated it was using an spfile, and it was the default name in the default location (%ORACLE_HOME\database\spfile%ORACLE_SID%.ora).

    Next I did a couple of ALTER SYSTEM SET .... SCOPE=SPFILE commands.

    Next I bounced the database and rechecked the parameters I had set, and they were back to their original values.

    After a couple of more iterations to make sure I wasn't doing something really stupid (after all, this should have been dead simple) I opened this thread, then continued to poke at it. So,

    Next I created a default pfile from the default spfile - CREATE PFILE FROM SPFILE. This created %ORACLE_HOME%\database\init%ORACLE_SID%.ora.
    On examining the resulting pfile, I saw my changes reflected in it, but also was surprised to see the parameter *.SPFILE=+ASM/...., so was including an addition spfile that was under ASM. (BTW, the entire purpose of this exercise was to migrate the database off of ASM.) At this point, as a sanity check I opened the default spfile - the one pointed to by the SHOW PARAMTER SPFILE command, the one in ORACLE_HOME\database. It did - as it should have - matched perfectly with the newly created pfile. So it was apparent that my ALTER SYSTEM changes were going to this default spfile but - unbeknownst to me at the outset - were being over-ridden by that second, ASM-based spfile.

    At this point, I renamed the just-created pfile to something like initORCL.sav. Then created a new pfile from the ASM-based spfile - CREATE PFILE FROM SPFILE='+ASM\spfile.ora'. This new pfile was much more extensive than the first, and there were the parameters that were overriding the ones I was trying to change. I hand-modified this pfile, including some other things I found in the first, then created a new default spfile from it. So at this point I had the default spfile without it pulling in a second one that would override what I did.

    Does that clarify?
    Yes, now its 100% clear to me.
    Thank you.

    Regards
    Girish Sharma
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points