Robert -- Thanks for the question regarding "Automated Database Startup/Shutdown", version 9.2.0
Submitted on 29-Apr-2008 13:28 Central time zone
Last updated 30-Apr-2008 21:07
You Asked
Tom,
I have two questions, please, under the category of automated shutdown/startup of the database (i.e. using 'dbstart' and 'dbshut' in association with /etc/rc.d/init.d/dbora server init file).
(1)
How do you automate changing database init parameters across a database bounce?
I am using SPFILE.
Many init parameters require a bounce to effect into database.
With these init parameters that require a bounce, I must
* 'create pfile from spfile'
* then modify/add the new parameter value to my initxxxxx.ora file.
Now when the database is bounced, I have to start it twice;
1. once with "pfile=initxxxx.ora; create spfile from pfile",
2. then again so it will pick up the new spfile and be running in spfile mode.
This is ok when bouncing manually but when there is a server bounce, and the database is set to shutdown/startup automatically this is a problem.
Would you recommend editing the $ORACLE_HOME/bin/dbstart script to start/stop/start the database to create the new SPFILE?
Your thoughts, please.
(2)
The dbora, dbstart, and dbshut scripts apparently do not support an oracle user '.profile' file which prompts the user for a database name (when more than one database on a server).
I was thinking to add logic in the .profile file which would test and see if the user logging in was a 'root' session which had "su - oracle" (e.g. running the /etc/rc.d/init.d/dbora script) and if so, to default to a particular database instead of waiting for user to enter database value (for which it would wait forever in the case of the root init script).
Do you think this idea is sound?
Surely this is a common problem(?).
Any ideas?
Thanks!
Robert.
and we said...
1) why do you do that?
why not just
alter system set parm = value SCOPE=SPFILE;
2) I've never used dbstart and dbshut, I do not maintain an /etc/oratab file. I've always created a custom script for each database I wanted to auto start or stop - that script would set the correct and proper environment and do the right thing.
What does this mean?
April 30, 2008 - 4pm Central time zone
Reviewer: Robert from Memphis, USA.
Tom,
(1)
But what does this mean?
It appears not to have been set(?).
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- -------
audit_trail string NONE
SQL> alter system set audit_trail=true scope=spfile;
System altered.
SQL> show parameter audit_trail
NAME TYPE VALUE
------------------------------------ ----------- -------
audit_trail string NONE
SQL>
(2)
---------
"...I do not maintain an /etc/oratab file..."
---------
Tom, that sounds "undocumented"!?
Is this how the best DBAs do it?
Do you call your custom scripts from /etc/rc.d/init.d or equivilent?
Will you share a sample of one or two of these scripts?
Thanks!
Robert.
Followup April 30, 2008 - 6pm Central time zone:
1) you did scope = spfile, it is in the spfile, it is not in effect so v$parameter (what show parameter queries) cannot, will not show that.
2) huh? not at all, it is entirely optional, always has been. dbstart and dbshut are very "version 6" if you ask me, I've always rolled my own startup script.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:387218751430
Should have known it
April 30, 2008 - 7pm Central time zone
Reviewer: Robert from Memphis, USA.
Tom,
Thanks for the SPFILE info. (I should have already known this but now things are fine).
And I will check out this link you gave.
But is there a built-in way to tell which SPFILE parameters are 'pending' and will only be effected
at database shutdown/startup?
I know I could compare v$spparameter and v$parameter but there ought to be an easier way?
Thanks,
Robert. Followup April 30, 2008 - 9pm Central time zone:
what is hard about a join? put it into a script and it is "done"
Ok... I'll do it the old-fashioned way....
May 1, 2008 - 9am Central time zone
Reviewer: Robert from Memphis, USA.
Tom,
Thanks for your kind help and patience.
You are among my few top Oracle/Professional mentors.
I just figured something like knowing which init parameters was 'pending' would be a
logical/reasonable thing to know that Oracle would have a 'built-in' way of finding it out.
But a script will do the trick fine.... I'll just need to remember the name of it next time I need
it! :)
Thanks!
Robert.