Forum Stats

  • 3,872,013 Users
  • 2,266,363 Discussions
  • 7,911,025 Comments

Discussions

“Flashback”/"Versions” of SPFILE

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

What if SPFILE could be accessed as of timestamp?

It could happen if the spfile keep older confgurations or make internal “versions” of configuration.

This way, if you make a mistake scope spfile and is having trouble to startup your database after spfile changes, you can simply make a:

SQL> STARTUP SPFILE[=/LOCATION/OF/SPFILE] AS OF TIMESTAMP SYSDATE-1;

And the database starts with yesterday’s spfile configuration.

Matheus BoesingctriebMathewDBA
3 votes

Active · Last Updated

Comments

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

    Interesting idea.... something similar is creating a pfile with a date in the name, like this:

    > create pfile='name_with_date' from spfile;

    Then you can choose which pfile you want to startup with.

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

    Interesting idea.... something similar is creating a pfile with a date in the name, like this:

    > create pfile='name_with_date' from spfile;

    Then you can choose which pfile you want to startup with.

    That's right... This is actually how I do...
    But I consider it's not so optimal it could be, and you have to 'remmember' to create this backup files, it's not automatically.

    Besides it, this files are not backuped with backup of database (autobackup of spfile) and other features applied to "official" one. I still believe it's a cool idea.

    The same way you can make backups from tables, but having flashback is better.

    Anyway, thanks for your considerations, top.gun!

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

    That's right... This is actually how I do...
    But I consider it's not so optimal it could be, and you have to 'remmember' to create this backup files, it's not automatically.

    Besides it, this files are not backuped with backup of database (autobackup of spfile) and other features applied to "official" one. I still believe it's a cool idea.

    The same way you can make backups from tables, but having flashback is better.

    Anyway, thanks for your considerations, top.gun!

    Starting up like this is not ideal:

    SQL> STARTUP SPFILE[=/LOCATION/OF/SPFILE] AS OF TIMESTAMP SYSDATE-1;

    You need to remember when the last time the spfile was changed.

    If you backup using > create pfile='name_with_date' from spfile; then you know exactly when the change happened because you made a pfile just before you changed it.

  • Matheus Boesing
    Matheus Boesing Member Posts: 46 Blue Ribbon

    Starting up like this is not ideal:

    SQL> STARTUP SPFILE[=/LOCATION/OF/SPFILE] AS OF TIMESTAMP SYSDATE-1;

    You need to remember when the last time the spfile was changed.

    If you backup using > create pfile='name_with_date' from spfile; then you know exactly when the change happened because you made a pfile just before you changed it.

    I agree that the idea has points to be improved. But I still believe it has potential to solve some problems.

    What if we can list the versions from some structure like used to "Flashback Version Query"? It would be an improvement of original idea, enabling to list all versions of spfile.

    It avoids you to have 'different files' to manage the versions/changes, in comparison to actual 'create pfile from spfile' for every change.

    Thanks for your collaboration, top.gun!