A reader, January 14, 2003 - 1:43 pm UTC
Tom,
Isn't it possible(for oracle) to store the hostname or ipaddress and location of pfile.
Thanks.
January 14, 2003 - 8:12 pm UTC
is it possible -- sure, anything is. does it do it? no (and pfiles are obsolete if you ask me -- I don't use them anymore, too much of a hassle)
Vijay'S, January 15, 2003 - 1:00 am UTC
could you please throw some light on your comment "(and pfiles are obsolete if you ask me -- I don't use them anymore, too much of a hassle) I didn't got you.
Thanks in advance as always
January 15, 2003 - 8:12 am UTC
search this site for
spfile
but wait -- you asked about spfiles so you know what they are already?? the replacement for pfiles.
New Feature of Oracle 9i
Sikandar Hayat Awan, January 15, 2003 - 10:35 pm UTC
About "db_create_file_dest" parameter
Sikandar Hayat Awan, March 18, 2003 - 5:26 am UTC
Dear TOM I have installed Oracle 9i Rel 1 and unable to understand the following,
+++++
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------
db_create_file_dest string d:\
SQL>
++++++
SQL> select * from v$spparameter where name = 'db_create_file_dest';
NAME VALUE
------------------------------ -------------
db_create_file_dest d:'
*.db_domain=
db_create_file_dest bahuman'
the values in the v$spparameter is confusing me while in v$parameter2 is ok.
March 18, 2003 - 8:43 am UTC
well, since you didn't cut and paste -- just sort of typed stuff in -- I cannot comment.
Try something like this so you can actually post what is really there and not type it in by hand.
ops$tkyte@ORA9I> column sid format a10
ops$tkyte@ORA9I> column name noprint
ops$tkyte@ORA9I> column value format a20 word_wrapped
ops$tkyte@ORA9I> column update_comment format a20 word_wrapped
ops$tkyte@ORA9I> select * from v$spparameter where name = 'db_create_file_dest';
SID VALUE ISSPEC ORDINAL UPDATE_COMMENT
---------- -------------------- ------ ---------- --------------------
* FALSE 0
Also, try
create pfile from spfile
and inspect the pfile to see if it looks "ok", if not, fix it and create spfile from pfile.
Copy paste
Sikandar Hayat Awan, March 18, 2003 - 11:38 pm UTC
Here is the copy paste,
SQL*Plus: Release 9.0.1.0.1 - Production on Wed Mar 19 09:21:40 2003
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter password: *******
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> column sid format a10
SQL> column name noprint
SQL> column value format a20 word_wrapped
SQL> column update_comment format a20 word_wrapped
SQL> select * from v$spparameter where name='db_create_file_dest';
SID VALUE ISSPEC ORDINAL UPDATE_COMMENT
---------- -------------------- ------ ---------- --------------------
* D:' TRUE 1
*.db_domain=
* bahuman' TRUE 2
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;
create spfile from pfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> connect sys as sysdba
Enter password: *******
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup;
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
SQL> select * from v$spparameter where name='db_create_file_dest';
SID VALUE ISSPEC ORDINAL UPDATE_COMMENT
---------- -------------------- ------ ---------- --------------------
* FALSE 0
SQL> alter system set db_create_file_dest='d:\';
System altered.
SQL> select * from v$spparameter where name='db_create_file_dest';
SID VALUE ISSPEC ORDINAL UPDATE_COMMENT
---------- -------------------- ------ ---------- --------------------
* d:' TRUE 1
*.db_domain=
* bahuman' TRUE 2
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string d:\
SQL>
PFILE output
*.background_dump_dest='C:\oracle\admin\ora9i\bdump'
*.compatible='9.0.0'
*.control_files='C:\oracle\oradata\ora9i\CONTROL01.CTL','C:\oracle\oradata\ora9i\CONTROL02.CTL','C:\oracle\oradata\ora9i\CONTROL03.CTL'
*.core_dump_dest='C:\oracle\admin\ora9i\cdump'
*.db_block_size=4096
*.db_cache_size=33554432
*.db_create_file_dest='d:\'
*.db_domain='bahuman'
*.db_name='ora9i'
*.dispatchers='(PROTOCOL=TCP)(SER=MODOSE)','(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)','(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)'
*.fast_start_mttr_target=300
*.instance_name='ora9i'
*.java_pool_size='33554432'
*.large_pool_size='1048576'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=33554432
*.sort_area_size=524288
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='C:\oracle\admin\ora9i\udump'
March 19, 2003 - 6:40 am UTC
guess it is time for you to open a tar with support -- if the pfile is as you say it is -- and the line breaks are all there correctly AND that is the pfile in $ORACLE_HOME/dba/init$ORACLE_SID.ora
Location of pfile is
Sikandar Hayat Awan, March 19, 2003 - 10:47 am UTC
I will contact Support but I want to tell you the exact locations my window box as path sprcified by you is Unix.
c:\oracle\admin\ora9i\pfile\init.ora
c:\oracle\database\initora9i.ora
c:\oracle\database\spfileora9i.ora
There is no other finding of these files.
self tuning spfile????
Shankar, January 04, 2004 - 12:42 pm UTC
Following is extract from Oracle online doc,
</code>
http://download-west.oracle.com/docs/cd/B10501_01/rac.920/a96597/psimpstg.htm#10178 <code>
<quote>Oracle Corporation recommends that you administer parameters using server parameter files to simplify parameter administration and to take advantage of Oracle's advanced self-tuning capabilities. When Oracle self-tunes, it automatically modifies parameter settings in the server parameter file.......
Note: Avoid modifying the values for self-tuning parameters in the server parameter file; overriding these settings can adversely affect performance.<quote>
Tom, could you please clarify what are self tuning parameters and how oracle self tunes parameters in the spfile? Thanks for your time.
January 04, 2004 - 12:54 pm UTC
In 9i, i must assume they are talking about OEM setting some things.
In 10g -- there are lots of "self tuning" parameters to do with the SGA and such.
Comments in pfiles
A reader, July 20, 2004 - 11:04 pm UTC
I know that spfiles allow centralized management of parameters and remote startup/shutdown without proliferation of pfiles.
One question:
Right now, my pfile is extremely well documented with all the parameters categorized, indented, formatted, etc. Comments to indicate why non-default values are chosen, comments to indicate who/why values were changed (sort of a ChangeLog), etc.
If I move to a spfile, all this valuable information would be lost, right? :(
Any way to get the best of both worlds?
Thanks
July 21, 2004 - 7:33 am UTC
You'd have to maintain a change log, documentation, about your system.
technically the spfile can have comments in it, but it's not practical.
Comments in pfiles
A reader, July 20, 2004 - 11:13 pm UTC
Yes, I just saw the ALTER SYSTEM ... COMMENT '...'
but a) it is just 255 characters b) it is not cumulative i.e. every alter system overwrites the comment
Any other way to preserve all the info in my pfile when I convert to spfile?
July 21, 2004 - 7:33 am UTC
see above.
init.ora comments
A reader, July 21, 2004 - 9:16 am UTC
"You'd have to maintain a change log, documentation, about your system"
Hm, this is a bummer. I like my comments/documentation to stay as close to the code/configuration as possible so they dont get out of sync. Most people are notoriously bad at keeping documentation up to date!
This is almost like saying, no comments are allowed in stored procedures, dba_source will store only the byte-code, you are responsible for documenting your code separately!
When you modify the stored procedure (spfile), you have to remember to go and modify your documentation or risk them getting out of sync! :(
Does anyone else out there have this issue?
Thanks
July 21, 2004 - 9:38 am UTC
it keeps it as up to date as having comments in the code does. Unless there is some rigor applied -- comments in code are out of date faster than you can say "hello world".
with a proliferation of pfiles possible -- you must have some rigor in place already (eg: you have ONE single pfile).
I see no real difference here between maintaining comments in one place or the other. You do have the comment capability -- true, no 'history' but thats what backups are for maybe.
I personally have never kept comments in a pfile (proliferation) and prefer to have pfiles that are teeny tiny (eg: not really in need of commenting)
init.ora comments
A reader, July 21, 2004 - 1:37 pm UTC
I guess we'll just have to disagree then. I usually find the comments/algorithms in the code to be much more accurate and up-to-date than the "official" system documentation. The point is, you want to make it easier for developers and DBAs to keep system documentation up to date.
Right now, my init.ora is well documented, with reasons for changes, non-default values, TAR#s/bug# that made me set "_" parameters, etc. I would lose all this valuable information when I move to an spfile. Similarly, I have tons of comments in my code, as all good code should. Tomorrow if Oracle were to tell me that 10g will make my code faster and has more PL/SQL features, but the price is that I have to leave my comments behind, I would not be happy!
Getting back on topic, the main reason for using a spfile is that I can startup my database remotely using 'startup'. If I were using a pfile, I would need to do 'startup pfile=...' and the pfile would need to be duplicated to that remote client.
But, if my pfile is already in the default $ORACLE_HOME/dbs location on my server, the clients can already do just 'startup', right? So, in this case, there is no advantage in moving to spfiles, right?
Thanks
July 21, 2004 - 2:16 pm UTC
i guess you are saying "i want to use a pfile" -- so, go for it! nothing stopping you at all.
your pfile must be on the CLIENT that does the startup. your spfile does not need be.
You could always create a stored procedure that takes a parameter name, new value and whatever comment you wanted to add -- issue the alter system set x=y scope=...., and add a comment to your own comment table if you like.
I generally find comments in code to be not relevant after about the first modification to the code -- if they exist at all.
Comments in init.ora
A reader, July 21, 2004 - 8:20 pm UTC
Lets take a step back...maybe I was not paying attention in my 9i New Features class
The only reason to use a spfile is if I want to be able to do a remote startup of my database, right? If not, there is no reason to use an spfile?
Thanks
July 21, 2004 - 8:41 pm UTC
i love it for the ability to:
alter system set foo=bar scope=both;
cause I always forget update my init.ora with relevant changes ;)
we just think differently i guess. i look at my init.ora as a place to keep parameters, not a recorded history. and I've been known more than once to do the alter system but forget to update the pfile.
Idealism
Guillermo, July 26, 2004 - 4:01 pm UTC
Answers like "you really don't need to know because you are not the person that startsup/shutsdown the database" are not helpful at all, and grossly oversimplify reality. Just because you *are* the person that "startsup/shutsdown" databases in your organization doesn't necessarily mean you remember where the PFILE is that started up an instance that's been running for months, or was started by an erstwhile colleague that just won the lotto and walked off the job, or is at a brand new client's site that you now have responsibility for. The world is never as simple as "one database, one DBA", and I have never understood why Oracle always chose to keep such a basic item of an instance's history (i.e., the PFILE that was used to configure it) irretrievable. I have yet to hear of a single plausible reason for doing so.
July 26, 2004 - 5:18 pm UTC
did you read the answer?
anyway -- the pfile is relative to the client, so what if the pfile was told to you to be c:\some\where\initpfile.ora? but wait, my server is on unix -- whats up with this c:\ thingy?
spfiles -- in 9i -- solve that issue.
the pfile is 100% a "client thing". If you start Oracle on a mainframe from your linux box with a pfile -- the pfile is on the linux box, not the mainframe.
But then again, if you are running with a mainframe mentality (that of we shall have a bit of control over our environment and the way things are done), you wouldn't be confused as to where the pfile was cause -- well, cause it would be under control.
If you have a situation whereby a colleague winning the lottery and walking off presents a huge logistical nightmare -- well, you have somewhat of a big problem (hey, so were exactly are those backups again? where did they put the archives? how are these archives magically moved from /d01 to /d02 and compressed anyway?)
the list goes on and on and on.
Do parameter changes using alter system generate redo?
Matt, August 03, 2004 - 10:35 pm UTC
9.2.0.4 SE
With regards to propagating initialisation parameter changes from a primary database to a standby database:
Do "alter system set param=123" changes get logged into redo?
set autotrace on output suggests that this does not happen (there *is* no output).
How can I confirm this behaviour?
Is there a better means of transfering parameter changes to the standby rather than manually tracking and applying these?
August 04, 2004 - 9:20 am UTC
in general, the parameters won't be the same. the parameters are either stored in pfiles (no control by us) or spfiles (some control by us but not "logged").
If you change an init parameter in production AND you desire that setting to be made in standby, you must either dynamically change the standby parameter or shutdown standby, change it, bring it backup.
Again--condescension not helpful
Guillermo, August 06, 2004 - 6:00 pm UTC
I, of course, *did* read the answer, several times. It is a cop out to point to the client-side nature of the pfile and pretend that no useful information can therefore be garnered from what Oracle knows about its location at the time of instance startup.
First, the "C:\ thingy", *could* be helpful on its own. Not all clients have the same drive and directory layouts, and one look at the drive/path may clue the DBA to exactly where the pfile is. Very often one can deduce which of one or two client machines the instance was probably started from. Besides, there is a very good chance that it refers to precisely the machine the instance is running on. As anyone who has been a problem solver in IT knows from experience, clues are often the best we have to go on, and are lifesavers. Occasionally they lead us astray, but we like to have them nonetheless. It is especially frustrating to know that a system administration utility was in possession of precisely the piece of information needed to understand what it was doing, but casually discarded it for no good reason.
Second, there is no reason why the server cannot figure out the IP address, or even DNS or NetBIOS name of the machine running the session starting the instance. It is obviously communicating with the client, and therefore has some sort of address. This could also be helpful information in other situations.
Yes, there are many nightmare personnel scenarios that occur every day, and yes, the lists of logistical headaches they generate are enormous. The length of a list of problems is never a good reason to add to it, in my opinion. My preference is for that list to be shorter rather than longer, and any assistance from the tools I use is most welcome.
August 07, 2004 - 9:57 am UTC
I'm not trying to be condescending.
A cop out I guess, is in the eyes of the beholder.
As most people are using GUI's for whatever reason.
As most people use a desktop OS different from their server.
As many people therefore *DO* do remote startups/shutdowns
not a cop out, <b>a fact of life perhaps</b>.
I'm telling you if you have a situation where by your DBA getting hit by the #12 bus on the way back from lunch means you have no one that has a clue where things are -- you have bigger fish to fry.
for c:\ thingy -- It could clue the DBA into the wrong thing as easily. It is a bi-directional street here.
And, if the pfile for you database isn't on a KNOWN FIXED CLIENT -- (eg: george has to start the database, george is the only fellow with the pfile -- oh, what, george just smashed his laptop running up the stairs? uh oh.). Hmm.
I think you are making a slightly large deal out of a non-issue personally.
nuff said, we obviously disagree. I've *never* (and I mean never) been in a situation whereby the location of the pfile/spfile wasn't a well known fact. The only situations I can envision it happening are all situations that frankly, should not exist and there are much large systemic configuration issues going on.
and oh, by the by, if you are connected to the database -- (you must be, you are looking for the pfile location in it), well, you have the ultimate solution at hand don't you -- you don't need to know where that pfile is:
ops$tkyte@ORA9IR2> spool foo.ora
ops$tkyte@ORA9IR2> /
NAME||'='||VALUE
-------------------------------------------------------------------------------
timed_statistics = TRUE
shared_pool_size = 134217728
java_pool_size = 16777216
control_files = /home/ora9ir2/oradata/ora9ir2/control01.ctl, /home/ora9ir2/orad
ata/ora9ir2/control02.ctl, /home/ora9ir2/oradata/ora9ir2/control03.ctl
db_block_size = 8192
db_cache_size = 67108864
compatible = 9.2.0.0.0
log_archive_start = TRUE
log_archive_dest = /tmp/
db_create_file_dest = /home/ora9ir2/oradata/ora9ir2
undo_management = AUTO
undo_tablespace = UNDO_NEWER
undo_retention = 10800
remote_login_passwordfile = EXCLUSIVE
db_domain =
instance_name = ora9ir2
dispatchers = (PROTOCOL=TCP) (SERVICE=ora9ir2XDB)
utl_file_dir = *
job_queue_processes = 0
background_dump_dest = /home/ora9ir2/admin/ora9ir2/bdump
user_dump_dest = /home/ora9ir2/admin/ora9ir2/udump
core_dump_dest = /home/ora9ir2/admin/ora9ir2/cdump
optimizer_features_enable = 9.2.0
audit_trail = FALSE
db_name = ora9ir2
open_cursors = 300
pga_aggregate_target = 251658240
aq_tm_processes = 0
29 rows selected.
ops$tkyte@ORA9IR2> spool off
How to change LOG_ARCHIVE_FORMAT
A reader, September 23, 2004 - 5:09 pm UTC
Hi Tom,
I have a 10.1.0.2 version database and while creating I have left the LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf .
I want to change it to %t_%s_%r.arc as I user .dbf for database files.
Thanks in advance.
September 24, 2004 - 9:43 am UTC
ok,
ops$tkyte@ORA9IR2> alter system set log_archive_format = '%t_%s_%r.arc';
alter system set log_archive_format = '%t_%s_%r.arc'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
<b>you cannot change it in flight, so if you are using an spfile:</b>
ops$tkyte@ORA9IR2> a scope=spfile;
1* alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile
ops$tkyte@ORA9IR2> /
System altered.
<b>and restart, else modify your init.ora pfile and restart</b>
Size of the spfile is greater than pfile
Santosh Kumar, October 21, 2007 - 9:32 am UTC
Tom,
spfile is always bigger than pfile.why?
Are DMLs,DDLs govern the size of spfile?
The size difference between pfile and spfile is not constant.
October 23, 2007 - 6:54 pm UTC
no it is not, but even if it were SO WHAT, who cares???
the files are so tiny, so so so tiny, teeny tiny...
sys%ORA10GR2> !ls -l /home/tkyte/initora10gr2.ora
-rw-rw-r-- 1 tkyte tkyte 9240 Oct 23 18:45 /home/tkyte/initora10gr2.ora
sys%ORA10GR2> startup force pfile=/home/tkyte/initora10gr2.ora
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 92276980 bytes
Database Buffers 71303168 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
sys%ORA10GR2> create spfile from pfile='/home/tkyte/initora10gr2.ora';
File created.
sys%ORA10GR2> !ls -l $ORACLE_HOME/dbs/sp*.ora
-rw-rw---- 1 ora10gr2 ora10gr2 3584 Oct 23 18:46 /home/ora10gr2/oracle/product/10.2.0/db_1/dbs/spfileora10gr2.ora
my pfile had comments, my spfile - won't.
but - who cares, who really cares - these are tiny files. the spfile is a small binary file, it has "stuff in it", the pfile is just a text file - you control the size of it.
Why ?
A reader, January 14, 2008 - 12:51 pm UTC
Why does show parameter not give the full path.
Why does it give ? and @
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile@.ora
January 14, 2008 - 3:50 pm UTC
it defaulted, it was not set with an explicit path, so it is $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
it is a template, if you change the sid or home, it would still find it.
i still like pfiles!
graeme king, May 30, 2008 - 2:50 pm UTC
we have 'a fair number' of oracle applications instances on around 7 unix boxes. we implement a kind of OO approach!
each instance's init.ora file firstly points to (inherits) one of 3 'sizing' (IFILES) called initSMALL.ora, initMEDIUM.ora or initLARGE.ora...these files specify standard values for pga_aggregate_target, sga_target, sessions etc...and are on an NFS volume that all our unix servers can see.
the 3 sizing files point to (inherits) a standard IFILE called initCOMMON.ora that contains the site standard common init parameters such as cursor_sharing, workarea_size_policy...
each instance's init.ora file then defines their instance specific init parameters such as db_name, background_dump_dest...
finally each instance has the ability to override any standard parameter by specifying it immediately after the instance specific parameters in it's own init.ora file.
also we *never* start db's remotely either. ok, yeah, there is a single point of failure...sure but we back it up nightly and if we lost the NFS then a million other things would not work either. also with pfiles there is always a danger of a pfile becoming out of sync but we're pretty good about keeping that under control as well as when we clone there are only a few instance specific parameters to change - no 'exporting' spfile to pfile, changing it and 'importing' it again (at some point your dealing with flat 'init' files)
i think it's a reasonable solution. great for making a global change for example. initialization parameters are one of the very worst, least standardized and poorly documented areas of oracle database administration/development. The IFILE concept is really powerfull - we should really use it creating a standard TNSNAMES.ora also - another 'weak link' in most shops.
finally i'm struggling to find documentation on 'auto tuning' of initialization parameters relating to only spfiles? (i.e you need to use an spfile to take advantage of certain areas of database auto tuning?)
awaiting the wrath of tom on this one :)
May 30, 2008 - 3:21 pm UTC
well, we finally dropped the initsmall/med/large junk a while ago - I cannot imagine "inheriting" my memory parameters like that myself. I've never seen two medium databases that were exactly the same - you'd need subtypes
med-hi-oltp
med-lo-oltp
med-warehouse
med-ods
med-mixed-workload
and so on....
and since I don't believe most dba's should be
a) granted dba
b) allowed to log onto the server
I'd want an spfile :)
even more scathing than i expected
graeme king, May 30, 2008 - 3:44 pm UTC
terrific job there. you ripped that apart beautifully.
i did point out that these can all be 'overridden' in each instances own init.ora - if you'd read that part :) the small, medium, large is a standardized baseline/starting point - 1000 times better than most shops out there i'd imagine.
oracle apps dba's do 'occasionally' need to logon to the server. i find it usually helps when running the ad utilities :)
May 30, 2008 - 4:20 pm UTC
.. oracle apps dba's do 'occasionally' need to logon to the server. ...
I agree, a FEW dba's sometimes - occasionally need to logon to the server
but everyone that needs the ability to restart the server do not...
I'd not like the idea that someone could change a "global" init file that would change ME on the next restart - therefore, I'd override everything - obviating the "inherit" bit.
I don't see what is hard or complex about setting up a handful of parameters for a given instance I guess. I find the ease of the spfile to be - well - the ease of the spfile.
Alexander the ok, May 30, 2008 - 7:52 pm UTC
"I agree, a FEW dba's sometimes - occasionally need to logon to the server "
I found this curious and interesting. We store everything we do as dbas on the node. Scripts for database administrator, scripts and output for changes for auditing, some documentation, things like that.
Do you think that's wrong? We're on the servers everyday almost all day.
May 31, 2008 - 7:01 am UTC
why do the DBA's need to be on the server?
scripts can be shared thousands of ways - there is no need to log into the server itself.
In many cases, that is becoming a "non-option" (sarbanes/hipaa/other policy related issues)
things can be easily accessible to any machine in a network (scripts, documentation, everything)
fun fun fun
graeme, May 31, 2008 - 1:30 am UTC
'We're on the servers everyday almost all day'
that's because the majority of dbas out there, logon to the server every day. i can possibly see a day when we don't - not here yet though - OEM is *still* too clunky and can't quite do everything i need as an apps dba and i don't want to use sqlplus on windows or god forbid dos and the shell - in fact I don't even want to install any oracle s/w on my client - clients are inherently 'unsafe' and hard to manage - servers are 'safe', manageable and restricted to a small bunch of professionals with the relevant access - usually granted to them because having the ability to do something as drastic as remotely restarting a database usually means that they are trusted to logon to a unix box and manage a database and application and their files from there.
anyway, i'm missing the point. back to the old spfile arguement - which by the way could go back and forth for ever :) you say you don't want a global change to affect you - i say you want a global change to affect you next startup - that's why it's deemed global. just like in oo programming - you inherit the standard attributes of a class and override the specific ones.
i will apologise and say that i didn't fully explain my environment - a development shop of apps instances, all at some point cloned from production, (almost) all at the same version seem to benefit from the standardized pfile implementation - the only main differences in each instance being the sizing/resource parameters. of course an enviroment of disparate appliations, db versions, hardware, etc may not fair so well.
thanks for your time.
graeme
p.s i loved the subtypes idea - that is much more flexible and robust :)
May 31, 2008 - 7:06 am UTC
... that's because the majority of dbas out there, logon to the server every day ...
just because you do something doesn't mean it is good.
What can you not do remotely, besides upgrade, what can you *only* do when logged onto the machine. Make it a big list - one thing that you do once a month won't make the point, it would have to be something you do constantly and that all or most of the DBAs have to do constantly.
Else we are back at
a) most dba's don't need to get onto the server.
b) the ones that do, do not need to be on their constantly.
... just like in oo programming - you inherit the
standard attributes of a class and override the specific ones.
......
and in programming/development - a change like that would be regressed - here, it would just magically happen upon the next restart...
graeme, June 02, 2008 - 10:27 am UTC
'just because you do something doesn't mean it is good.'
agreed. of course, thousands of professionals don't just do this because they are geeks and love unix shell scripting, they probably do it because the tools to replace such practices just aren't good enough. the majority of the industry doesn't always just do things if there are better, quicker, more efficient ways of doing them.
besides upgrade? ok, fair point. mind you, when you open the door for someone for an hour as opposed to a day - they are still in your house - you still need the same controls to prevent them from rummaging around in your underwear drawer ,stealing the family silver or burning it down. granted less time in there means it is less likely.
in my case, as an oracle apps dba i do the following pretty regularly:
patch oracle apps
clone oracle apps (mostly scripted but still requires a bit of server time)
run other apps ad utilities
troubleshoot issues in apps file system regularly, granted, some of it `custom¿ code
import/export (it just feels 'cleaner' when i use the server's imp and exp rather than some version i have on my client- of course i should have the exact same version on my client but clients are such a pain to keep in sync)
tkprof trace files (again using the server's executables)
various file related tasks performed using unix shell scripting ¿ yeah some of this falls in the gray area of whether it¿s a DBA or SA task.
it's ok - i agree. alot of this could be technically be done without logging in to the server. The infrastructure and tools would have to be pretty good to allow this.
Could you not technically upgrade a db without logging in byusing a 3rd party tool? Sounds like a pain but for compliance it must be possible?
I understand that Oracle is `machine independent¿ but good working knowledge of the underlying OS is still very helpful ¿ and you only really get that from hands on experience.
'and in programming/development - a change like that would be regressed - here, it would just magically happen upon the next restart... '
i thought i said i was in a development environment? Database changes (such as a init.ora change) should go through the same testing as a change to a object class - why do you think that they wouldn't? is it because most people don't do it?
not much you can do with a rogue developer than overrides attributes to avoid global changes, they are going to write 'junk' whatever.
June 02, 2008 - 12:49 pm UTC
... Database changes (such as
a init.ora change) should go through the same testing as a change to a object
class - why do you think that they wouldn't? is it because most people don't do
it?
...
why don't I think it wouldn't in your case? Because you just "inherit it" - and that change takes place *later*, when someone restarts. It doesn't seem very coordinated is all. I would not like a single change in a single file to affect an unknown number of databases (because you'd have to go to each and every init.ora out there and see what they are overriding!) So, it wouldn't see to be saving much....
graeme, June 03, 2008 - 4:16 pm UTC
i agree. it wouldn't be very coordinated if you had an 'unknown' number of development instances all using that enterprise class, some inheriting and some overriding it. they would only see the change 'later' upon the next build/release.
i am coming around to the spfile though :) i like the idea of scripting a controlled restart of every server from a single remote sql*plus session/script.
say i don't want to use pfiles at all...is this a bug? i *have* to use a pfile to change this.
SQL> ALTER SYSTEM SET DB_DOMAIN=tom.com SCOPE=SPFILE;
ALTER SYSTEM SET DB_DOMAIN=tom.com SCOPE=SPFILE
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
June 03, 2008 - 5:24 pm UTC
ops$tkyte%ORA11GR1> @printtbl8 'select * from v$parameter where name = "db_domain"'
NUM : "1090"
NAME : "db_domain"
TYPE : "2"
VALUE : ""
DISPLAY_VALUE : ""
ISDEFAULT : "FALSE"
ISSES_MODIFIABLE : "FALSE"
ISSYS_MODIFIABLE : "FALSE"<b>
ISINSTANCE_MODIFIABLE : "FALSE"</b>
ISMODIFIED : "FALSE"
ISADJUSTED : "FALSE"
ISDEPRECATED : "FALSE"
ISBASIC : "TRUE"<b>
DESCRIPTION : "directory part of global database name stored
with CREATE DATABASE"</b>
UPDATE_COMMENT : ""
HASH : "2669961844"
-----------------
PL/SQL procedure successfully completed.
that isn't settable.
graeme, June 03, 2008 - 5:38 pm UTC
thanks tom. sorry to waste your time with that.
Setting DB_DOMAIN
Mark A. Williams, June 03, 2008 - 5:58 pm UTC
You can set DB_DOMAIN, but you need to delimit it as a string.
For example, on a 10g XE database:
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 3 17:47:06 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
SQL> alter system set db_domain=tom.com scope=spfile;
alter system set db_domain=tom.com scope=spfile
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set db_domain='tom.com' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1287016 bytes
Variable Size 121638040 bytes
Database Buffers 159383552 bytes
Redo Buffers 2904064 bytes
Database mounted.
Database opened.
SQL> show parameter db_domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string tom.com
SQL>
Did I miss something?
- Mark
June 03, 2008 - 6:26 pm UTC
indeed, thanks!
graeme, June 03, 2008 - 9:51 pm UTC
thanks mark.
i did start to wonder about this. i was able to update other parameters with the ISINSTANCE_MODIFIABLE=FALSE but these were numeric values.
the documentation could be a little clearer on this.
June 04, 2008 - 9:32 am UTC
Yeah, I answered too quick, instance modifiable had nothing to do with it...
graeme, June 04, 2008 - 11:16 am UTC
no worries...actually it's really the error message that is 'misleading'
June 04, 2008 - 11:20 am UTC
I agree - it should "fail" differently :)