What does "show parameter spfile" return? If it returns a value then most probably you have an spfile instead pfile (init.ora).
SQL> show spfile
SP2-0158: unknown SHOW option "spfile"
Sorry, it's "show parameter spfile".
No worries. I didnot think.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Please check the alert.log file to verify. You may also bounce the database to make sure it's reading from the correct init.ora file.
1 person found this helpful
The PFILE and SPFILE-files are meant to control database parameters, but depending on the database version, and depending on actual values of different parameters, simple or more complex logic is going on, which populates/changes/.. specific parameters during database start up. The complex ones are comparing different parameters, and for example fill one parameter if another one is also filled, and stuff like that.
While this is true for a lot of parameters, some are only literally read in the PFILE or SPFILE, and are never parsed/derived/auto-filled..
One question, I was trying to change the comparable to 11.2.0 (another instance migrated by Oracle was like that, its db version is 220.127.116.11) from 18.104.22.168. But the database didnot start saying that the control file not comparable or something like that. For my next migration, should I set it to 11.2.0 in the init.ora or 22.214.171.124.
For my next migration, should I set it to 11.2.0 in the init.ora or 126.96.36.199.
1 person found this helpful
I presume you mean the "compatible" parameter.
Here's my understanding : you CAN set this parameter to a value that is LOWER than the actual database version, but then - for specific functionality - uses the version that you put in the variable. In other words, it's not using the latest version. I can understand why that may be helpful, but for a production system, I do not see the point of NOT using the latest version. Otherwise, why did one upgrade ? If there is an issue -> Oracle Support
But, the setting of this variable is a bit special, as it performs some kind of upgrade, when you set it. And, you cannot lower it again.
The documentation would detail that, but basically, you should change the parameter AT A LATER POINT in time, to distinct the main upgrade, and this action, in case of issue.
1. upgrade database
2. validate environment
3. take backup
4. change parameter (compatible)
5. validate again
Lastly, any 11.2 database should have its value set to exactly "11.2.0" (without quotes), to my understanding.
To that can be added :
- PFILE is the old style of parameter file, is a text file that can be edited easily, but is ONLY read on start up of database
It used to be the only way of working, but these days, there's only 1 reason left to use this file : when doing database activity, like upgrade, big changes, etc., it's much easier to manipulate ANY variable. In that context, the database will need to be bounced anyway
- SPFILE : allows changing of SOME parameters online, but more importantly, it kicks off some kind of auto-tuning of memory configuration, which gets better the longer the database is up and running (with SPFILE startup). The file itself is binary, and can NOT be edited (it can be viewed though, but output is unreliable)
Bottom line : these days (11g) any database should be running with SPFILE, but PFILE usage is much easier during maintenance
I guess documentation should be consulted for complete and better understanding
Upgrade guide should be read many times if first reading is not giving a clear picture
It details many points regarding upgrade
after all its specifically created for upgrading oracle database so it should not be missing important points
Reading makes a full man.......
Yes I hope nobody is doing upgrade with only the textual information in this forum, the info here should complement the official documentation, like the Doc ID's (also mentioned here)
But back to the initial question ... one can have both a PFILE and an SPFILE in the Oracle Home. And, you can be sure, they WILL have different values for at least 1 parameter, there's a law that dictates that !
shows there is no default value for this, may be you are checking wrong initzzz.ora
what is the output of
grep -i db_domain initzzz.ora
select * from V$PARAMETER where upper(name) like '%DB_D%'