Forum Stats

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

Discussions

Tool to Edit SPFILE Offline

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

Actually the only way to edit a SPFILE is through an online database.

But if you made a mistake scope SPFILE, get the database down and then database is not starting up?

You actually need to find an old spfile or a “parallel” pfile (or build one) to get database up again, once you cannot simply “edit” the spfile to fix your mistake.

Why not?

Matheus BoesingctriebMathewDBA
4 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    I thought the whole idea is that you are not able to edit it offline.

    If you want offline editability, use PFILEs instead.

    And CREATE PFILE FROM SPFILE seems not so hard a command...

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

    The contents of a pfile can be as simple as this:

    DB_NAME=<name_of_the_database>

    You can then add the other parameters to the pfile from the spfile, as recorded in the alert log.

    Once you have all the parameters correct you create an spfile from the nomount state.

    sql> create spfile from memory;

    Matheus Boesing
  • Matheus Boesing
    Matheus Boesing Member Posts: 46 Blue Ribbon

    Yes, I know this options. I got your points. But try to understand the situation:

    I passed by a situation that we changed some parameters scope spfile and have no backups or pfile to startup the instance again, so we had to mannually create a pfile to startup.

    It's our problem/mistake? Yes, I agree. But if we could change the spfile offline to startup the database again it would be great and reduced some effort.

    Your points is good too. The main reason to have a SPFILE is to NOT editing it.

    So, what if we can CREATE PFILE FROM SPFILE OFFLINE instead of my original idea?

    It would solve my problem and I think it will be better too...

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

    Yes, I know this options. I got your points. But try to understand the situation:

    I passed by a situation that we changed some parameters scope spfile and have no backups or pfile to startup the instance again, so we had to mannually create a pfile to startup.

    It's our problem/mistake? Yes, I agree. But if we could change the spfile offline to startup the database again it would be great and reduced some effort.

    Your points is good too. The main reason to have a SPFILE is to NOT editing it.

    So, what if we can CREATE PFILE FROM SPFILE OFFLINE instead of my original idea?

    It would solve my problem and I think it will be better too...

    You can do CREATE PFILE FROM SPFILE while the instance is shutdown.

    There is not need to start the instance in nomount mode.

    Matheus Boesing
  • Matheus Boesing
    Matheus Boesing Member Posts: 46 Blue Ribbon

    Oh! That's right!

    Problems solved, so.