10 Replies Latest reply: Jan 27, 2013 1:25 AM by alz2real RSS

    Spfile access issue

    alz2real
      Hello all. I run an oracle database 11gR2 on a windows 7 x64 system. I recently decided to remove permissions on my server parameter file since my enterprise manager reported a policy violation. So i navigated to my spfile, got to it's properties and then clicked on the security tab. There, i deselected all the permissions available. Apparently that was taking it to far. I then did a clean shutdown and tried starting up my database again, but my spfile can't be accessed by oracle again. Even when i try to open it with a test editor, i get an 'Access Denied' error message. When i try to startup my instance, it says 'could not open parameter file.....', and specifies the spfile directory. Can someone help me out here? I don't want to have to create a new database coz of this. I want to solve this for the purpose of learning.
        • 1. Re: Spfile access issue
          sb92075
          alz2real wrote:
          Hello all. I run an oracle database 11gR2 on a windows 7 x64 system. I recently decided to remove permissions on my server parameter file since my enterprise manager reported a policy violation. So i navigated to my spfile, got to it's properties and then clicked on the security tab. There, i deselected all the permissions available. Apparently that was taking it to far. I then did a clean shutdown and tried starting up my database again, but my spfile can't be accessed by oracle again. Even when i try to open it with a test editor, i get an 'Access Denied' error message. When i try to startup my instance, it says 'could not open parameter file.....', and specifies the spfile directory. Can someone help me out here? I don't want to have to create a new database coz of this. I want to solve this for the purpose of learning.
          You can create pfile using values from alert_SID.log file during last successful DB restart
          • 2. Re: Spfile access issue
            EdStevens
            alz2real wrote:
            Hello all. I run an oracle database 11gR2 on a windows 7 x64 system. I recently decided to remove permissions on my server parameter file since my enterprise manager reported a policy violation.
            What policy violation did it report that caused you to remove even the ability to read the file, much less allow oracle to update it?

            So i navigated to my spfile, got to it's properties and then clicked on the security tab. There, i deselected all the permissions available. Apparently that was taking it to far. I then did a clean shutdown and tried starting up my database again, but my spfile can't be accessed by oracle again. Even when i try to open it with a test editor, i get an 'Access Denied' error message. When i try to startup my instance, it says 'could not open parameter file.....', and specifies the spfile directory. Can someone help me out here? I don't want to have to create a new database coz of this. I want to solve this for the purpose of learning.
            Why don't you just "navigate to your spfile, got to it's properties and then clicked on the security tab. There, select all the permissions available"

            Edited by: EdStevens on Jan 25, 2013 6:31 PM
            • 3. Re: Spfile access issue
              alz2real
              Hello Stevens. Yep, silly mistake i made, i agree. But i already tried selecting all that i deselected. It just won't let me select again. The area for selecting is greyed out. I'm hoping there's some other way out. I no there must be, i just don't know how at the moment.
              • 4. Re: Spfile access issue
                alz2real
                How exactly do i create a pfile from the alert_SID.log. I'm aware of creating a pfile from the spfile and also creating an spfile from the pfile using the command CREATE PFILE FROM SPFILE and CREATE SPFILE FROM PFILE. But using the alert log, I'm a little lost there. Really need some help.
                • 5. Re: Spfile access issue
                  moreajays
                  hi,

                  By any chance if you have RMAN backup configured with catalog option you can restore spfile from autobackup ..
                  RMAN> restore spfile to 'sptest.ora' from autobackup;
                  else recheck again if $ORACLE_HOME/dbs contains spfile/pfile for any backup parameter files that you can re-use

                  Also check if permission/owner/group of the file has got changed if spfile is not stored in ASM

                  From alert log you may find something below .. post startup of datbase done in past to rebuild/re-create the pfile

                  ORACLE_HOME = /u01/app/ora11g/product/11.2.0/dbhome_1
                  System name:    Linux
                  Node name:      remedy-ebu-test-db1
                  Release:        2.6.32-220.el6.x86_64
                  Version:        #1 SMP Wed Nov 9 08:03:13 EST 2011
                  Machine:        x86_64
                  VM name:        VMWare Version: 6
                  Using parameter settings in server-side spfile /u01/app/ora11g/product/11.2.0/dbhome_1/dbs/spfileREMTST.ora
                  System parameters with non-default values:
                    processes                = 2000
                    sessions                 = 3022
                    resource_limit           = TRUE
                    filesystemio_options     = "ASYNCH"
                    sga_target               = 624M
                    memory_target            = 1648M
                    memory_max_target        = 1648M
                    control_files            = "+DG01/remtst/controlfile/current.260.797379301"
                    db_block_size            = 8192
                    db_writer_processes      = 2
                    compatible               = "11.2.0.0.0"
                    log_archive_dest_1       = "location=/u01/arc_dest"
                    log_archive_start        = TRUE
                  .
                  .
                  .
                  Thanks,
                  Ajay More
                  http://www.moreajays.com
                  • 6. Re: Spfile access issue
                    EdStevens
                    alz2real wrote:
                    How exactly do i create a pfile from the alert_SID.log. I'm aware of creating a pfile from the spfile and also creating an spfile from the pfile using the command CREATE PFILE FROM SPFILE and CREATE SPFILE FROM PFILE. But using the alert log, I'm a little lost there. Really need some help.
                    pfile is just a simple text file. The notification of all non-default parameters in your alert file is just some lines of text ....

                    Um, so open your alert file in a text editor, copy then lines, and save them in another file named 'init<mysid>.ora

                    Ok, details.

                    Locate your alert log. Copy it to a file named 'init<sid>.ora' (where you replace '<sid>' with the name of your database. Save it into the same directory where your currenty hosed up spfile is located.

                    Open the newly saved init<sid>.ora file in a text editor. You never stated your OS (please read the forum FAQ) but it's painfully obvious that it is Windows, so your default text editor would be notepad.

                    Next, find the messages from the last startup. Just a few lines after the startup begins, it should be obvious which lines are showing you the non-default parameters. Delete everything but those lines and save the file, but keep it open.

                    Every line is of the forma <some parameter> = <some value>. Remove all the spaces surrounding the "=". Save the file.

                    Some lines may be broken with line wrap. Fix it. Save the file.

                    Now you are ready to start your database with
                    startup pfile='init<sid>.ora' 
                    you may get some errors due to some bad formatting, which you will need to address on a case-by-case basis.

                    Once you are able to get a clean startup with this new pfile, create a new spfile with the command
                    create spflile from pfile='init<sid>.ora'
                    The Achilles heel here may be that you are not even allowed to overwrite or delete the existing pfile. If that's the case .... you just learned a hard lesson.

                    Edited by: EdStevens on Jan 26, 2013 8:14 AM
                    • 7. Re: Spfile access issue
                      alz2real
                      Thanks moreajays, I'll try this out ASAP.
                      • 8. Re: Spfile access issue
                        alz2real
                        Thumbs up Edstevens!!! My database is up!! Your solution worked perfectly. I did as u said and only had to do a little editing of the pfile. Thanks a lot pal.
                        • 9. Re: Spfile access issue
                          EdStevens
                          alz2real wrote:
                          Thumbs up Edstevens!!! My database is up!! Your solution worked perfectly. I did as u said and only had to do a little editing of the pfile. Thanks a lot pal.
                          Glad it worked. One last thing .. now that you have your spfile created, restart your database without the 'pfile=' option, so that the database will startup with the spfile. Confirm it did so by the sqlplus command
                          show parameter spfile
                          • 10. Re: Spfile access issue
                            alz2real
                            Hello again Edstevens. Yep, i already did that. Didn't want to take any chances. I ran the command CREATE SPFILE FROM PFILE as soon as my database was opened with the pfile, did a clean shutdown and then restarted without the pfile parameter. I really do appreciate your help. I look forward to helping others like you do.