very helpful
Chandan Singh, November 30, 2002 - 11:07 pm UTC
It's Quite easy way to know from which file instance has started.
Thanks
The sequence of pfiles
Sikandar Hayat, July 22, 2003 - 2:42 am UTC
I have read the sequence of files used during startup,
1 spfile<instance_name>.ora
2 spfile.ora
3 init.ora
To test it I have just renamed the spfile and getting the following error,
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\ORA9I\DATABASE\INITORA9I.ORA'
Although it will be successful if I use
SQL> startup pfile='D:\ora9i\admin\ora9i\pfile\init.ora'
It seems the parameter file selection is not auto.
OS Win2000 and Oracle 9i Rel 2
July 22, 2003 - 8:14 am UTC
there is no way.
To check the current pfile
Sikandar Hayat, July 22, 2003 - 2:44 am UTC
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string %ORACLE_HOME%\DATABASE\SPFILE%
ORACLE_SID%.ORA
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='D:\ora9i\admin\ora9i\pfile\init.ora';
ORACLE instance started.
Total System Global Area 143727516 bytes
Fixed Size 453532 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 143727516 bytes
Fixed Size 453532 bytes
Variable Size 109051904 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string %ORACLE_HOME%\DATABASE\SPFILE%
ORACLE_SID%.ORA
SQL>
Note: Dear TOM I am waiting for the response to the above post.
mys spfile is corrupt
Ahmad, December 24, 2003 - 10:34 am UTC
My spfile is corrupt and now I cannot start my database running on my laptop. Is there a way to build spfile again? I followed your new book Chapter 4 but could not get any success. How can I create spfile using pfile ?
Thanks
Ahmad
December 24, 2003 - 10:39 am UTC
if you are on unix,
$ cd $ORACLE_HOME/dbs
$ strings spfilename temp_pfile.ora
edit the temp_pfile.ora, clean it up if there is anything "wrong" with it.
then
SQL> startup pfile=temp_pfile.ora
SQL> create spfile from pfile;
SQL> shutdown
SQL> startup
On windows -- just try editing the spfile, create a pfile from it. save it, and do the same.
I edited spfile
Ahmad, December 24, 2003 - 10:53 am UTC
Thanks for quick response. I have win2000 with 9i Rel2. Thats what I did, in order to install OWB , I chnaged some parameter in spfile and then it got corrupted. I can use NT services and it works OK, but when I try to conenct to db , I get Oracle not available error. Since, I donot have my laptop right now, I cannot give you exact error but it was related with "Oracle not available"?
Then what I did is that I copied the spfile from different machine which has same data , same directory structure and tried to run but still no success.
Do you think if Oracle is not available I can still create pfile the way you described.
December 24, 2003 - 11:08 am UTC
yes, you can startup the db from the command line using sqlplus
create a pfile, do a manual startup (start the oracle service, then use sqlplus to start the database)
SPFILE question
Arun Gupta, December 24, 2003 - 11:31 am UTC
Tom
I am going through your book Effective Oracle by Design. It says that one of the reasons to use spfile is if I use OEM/OMS to start/stop database, the PFILE needs to be on the machine where OMS is installed or PFILE should be accessible to the machine running OMS. I have run into this problem in the past. One copy of PFILE on OMS server and one copy on the database server. Keeping these copies in sync was difficult.
If I use SPFILE, would I still need one copy of SPFILE on the OMS server? How would the OMS know where to find the SPFILE?
Thanks.
December 24, 2003 - 11:37 am UTC
the spfile will ALWAYS be on the database server -- the spfile SOLVES this problem of here a pfile there a pfile everywhere a pfile.
Arun Gupta, December 24, 2003 - 12:13 pm UTC
We have several databases running on one server. We have a common.ora, which contains parameters which are common for every database. Then we have database specific parameter file. In the PFILE, using ifile=... we refer to both these files and some parameters are in the SPFILE also. Is this a good scheme to have parameters in three files? Would SPFILE support this scheme?
Thanks.
December 24, 2003 - 1:37 pm UTC
I would fix the first sentence :)
A server should have no more then one database.
So, I have no best practices in this area since it is something I would not do. Sorry.
Jeff Hunter, December 24, 2003 - 2:28 pm UTC
OK, then how about this:
I run several Oracle Apps instances on several machines (one per machine). Since we want our Oracle Apps environments to have the same behaviour (relatively), we use a common init.ora file for several of the optimizer paramters. Each init.ora file includes an entry like "ifile=/usr/local/oracle_config/apps_optimizer.par" with entries like optimizer_max_permutations=2000, _complex_view_merging=true, _sort_elimination_cost_ratio=5, etc. How would these instances (on seperate boxes) share the common ifile, but still be able to have seperate parameters like db_name, log_archive_dest, etc. with an SPFILE?
December 24, 2003 - 2:31 pm UTC
they would not -- i don't have any best practices for sharing an init.ora amongst many instances cause
a) i would not run them on a single machine
b) i would not use network drives as that gives me a nasty single point of failure so sharing with ifile is still not "relevant"
I've just never had the need (or desire) to share config files between multiple instances in my experience.
no luck
Suhail, December 24, 2003 - 7:09 pm UTC
Tom,
As you mentioned, I did but I have no luck. I am running 9iRel2 on win2000 and my services are all running but could not connect. My spfile is broken but could not fix. Here are what I tried:
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\9IDB\DATABASE\INITSA1.ORA'
SQL> shutdown
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL> startup pfile=init.ora
LRM-00109: could not open parameter file 'init.ora'
ORA-01078: failure in processing system parameters
SQL> startup pfile='D:\9IDB\DATABASE\INIT.ORA'
LRM-00109: could not open parameter file 'D:\9IDB\DATABASE\INIT.ORA'
ORA-01078: failure in processing system parameters
SQL> create spfile from pfile
2 ;
create spfile from pfile
*
ERROR at line 1:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'D:\9IDB\DATABASE\INITSA1.ORA'
SQL> create pfile from spfile='SPFILESA1';
create pfile from spfile='SPFILESA1'
*
ERROR at line 1:
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> create pfile from spfile='SPFILESA1';
create pfile from spfile='SPFILESA1'
*
ERROR at line 1:
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> create pfile from spfile='SPFILESA1';
create pfile from spfile='SPFILESA1'
*
ERROR at line 1:
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Any help greatly appreciated as always.
Suhail
December 25, 2003 - 8:21 am UTC
you didn't create your pfile -- that is why.
the error seems obvious to me? your init.ora parameter file just quite simply "isn't there".
put it there -- create it.
Interesting Scenario on 9i...
denni50, March 15, 2004 - 1:57 pm UTC
Hi Tom
I mentioned, these past weeks, about not having the
tkprof tool in version 9.0.1.1.1.
Been trying to determine cause of slow performance,found
documentation regarding optimizing 9i on NT and performed
all the procedures outlined for efficient use of system
resources....configuring NT to be an App Server rather
than file server....etc
I found the Tuning/SQL Analyzer Tool that came with the 9.0.1.1.1 installation so installed that instead.
It's a pretty neat gui tool that does trace/tkprof and
statistics all in one.
I came upon a curious situation when running a sql statement through the analyzer tool.
The optimizer parameter in the pfile is set to RULE...
however in the SQL Analyzer it showed optimizer mode
chosen was "CHOOSE First Rows"?
I am not using a spfile:
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> select isspecified, count(*) from v$spparameter group by isspecified;
ISSPEC COUNT(*)
------ ----------
FALSE 250
SQL>
I prefer to stick with pfile so I can modify parameters
as needed.
Why is the SQL Analyzer choosing an optimizer mode that is
not specified in the pfile.
downloaded 9ir2 haven't had the chance to upgrade.
thanks
March 15, 2004 - 3:09 pm UTC
the sql analyzer is reporting WHAT HAPPENED. it (sql analyzer) did not choose the optimizer mode.
sessions can change their optimizer goal -- but, i'm not sure what "CHOOSE First Rows" means really as CHOOSE is a mode and FIRST_ROWS is a mode but choose first rows isn't...
Not sure of the context there.
thanks Tom...
denni50, March 15, 2004 - 3:36 pm UTC
for always catching my blunders...
I meant "COST First Rows"....got so many parameters
flowing through my synapses I took the first "C" that
triggered an electrical impulse.
As I fiddled with the Analyzer I saw the menu where you
choose the optimizer mode you want explained...i.e.:
Choose
First_Rows
All_Rows
Rule......etc...so that explains the First_Rows scenario.
which brings me to another tangent....
the optimizer_index_cost_adj has a default value of 100.
I understand this value can be just as expensive as a full
table scan and should be lowered.
Is there a ball park value that I should try and test?
I read 10g does away with RULE Optimizer altogether....
it'll be "light years" before we ever upgrade to 10g.
hey Tom...was going through some of your archived stuff
and found the picture of the galaxy with the database at
the center...like a "black-hole"(blackbox)...maybe
Hubble will send back some snapshots!!(chuckle).
March 15, 2004 - 3:52 pm UTC
if you have my book "Effective Oracle by Design", I go over optimizer_index_cost_adjust/caching and what they do. There is an excellent white paper as well </code>
http://www.evdbt.com/SearchIntelligenceCBO.doc <code>out there
forgot to mention...
denni50, March 15, 2004 - 3:51 pm UTC
I know the optimizer_index_cost_adj only works with CBO..
I plan to change the Optimizer to CHOOSE since I've
Analyzed my tables and Indexes.
see Tom i'm learning this stuff!
Wow!!!!
denni50, March 15, 2004 - 4:22 pm UTC
thanks Tom....
printed out the CBO.doc link you provided.
Clearly and simply illustrated and explained.
Anyone wanting to learn and understand the differences
between RBO and CBO should most definitely read this paper.
yoganand from hyderabad
yoganand, April 20, 2004 - 7:22 am UTC
This is very useful for me in real time.Thanks to Tom
yoganand
yoga, April 20, 2004 - 7:30 am UTC
Tom,
thanks for ur good service.
I have one doubt. from one system how to startup the other databse which in remote area(means in WAN/MAN).I would like to know how to give the remote area's database pfile path from local system.
SQL> STARTUP PFILE = <PFILE PATH>?
April 20, 2004 - 8:49 am UTC
use spfiles in 9i
otherwise, you -- the client -- needs to have the pfile on your machine. tools like OEM keep pfiles on their host for this purpose (leading to a proliferation of pfiles all out of sync)
spfiles in 9i are the way to go to solve this problem once and for all.
Some clarification
Matt, May 04, 2004 - 10:23 pm UTC
I just spoke to a DBA who expressed a preference not to use SPFILES (on 9.2). He expressed some concern that when the database is down he is then unable to identify the configuration of the database. For example in a recovery situation he would be unable to identify where the DB controlfiles should be (and other configuration information)
Is this a legitimate concern?
May 05, 2004 - 7:34 am UTC
No, it is not.
first of all -
[tkyte@tkyte-pc dbs]$ strings -a spfileora10g.ora
*.background_dump_dest='/home/ora10g/admin/ora10g/bdump'
*.compatible='10.1.0.2.0'
*.control_files='/home/ora10g/oradata/ora10g/control01.ctl','/home/ora10g/oradata/ora10g/control02.ctl','/home/ora10g/oradata/ora10g/control03.ctl'
*.core_dump_dest='/home/ora10g/admin/ora10g/cdump'
....
on unix would show you everything you need. and even on windows
E:\oracle\ora92\database>write SPFILEORA9IR2W.ORA
throws me into a pretty gui to read the file. And even if you don't like either of those, there is always:
idle> startup nomount
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 167772160 bytes
Database Buffers 67108864 bytes
Redo Buffers 667648 bytes
idle> show parameter control
NAME TYPE
------------------------------------ -----------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/home/ora9ir2/oradata/ora9ir2/
control01.ctl, /home/ora9ir2/o
radata/ora9ir2/control02.ctl,
/home/ora9ir2/oradata/ora9ir2/
control03.ctl
idle>
so, ask them to try again.
ORA-01507: database not mounted
A reader, September 17, 2004 - 6:17 pm UTC
tom,
what should i do here for 8.1.7?
when i say alter database open;
it gives
ORA-01507: database not mounted
database is started, but not letting queries run
September 17, 2004 - 8:15 pm UTC
alter database mount;
perhaps?
Duplicated instance
Vishal, December 02, 2004 - 4:27 pm UTC
Hi Tom,
I have Oracle 10 running on Solaris 9. I can see that Oracle instance is up - all processes are there and I can connect to it if I use sqlnet. The problem that when I connect like 'sqlplus "/ as sysdba"' from command line I get message "Connected to an idle instance." I tried to issue "startup nomount" and verified that it tries to bring up second copy of the already running instance (same spfile used). Could you please advise what could cause it and how to fix this issue?
Thanks a lot
December 02, 2004 - 7:26 pm UTC
your oracle_home and oracle_sid are not set properly.
[tkyte@tkyte-pc-isdn tkyte]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Dec 2 19:23:55 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
sys@ORA9IR2> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[tkyte@tkyte-pc-isdn tkyte]$ export ORACLE_SID=bogus
[tkyte@tkyte-pc-isdn tkyte]$ !sql
sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Thu Dec 2 19:24:10 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
idle>
Dupliacated instances
Vishal, December 03, 2004 - 11:40 pm UTC
Dear Tom,
Please see below evidence that both instances use same SPFILE/Oracle home:
/export/home/oracle/dae/rman> sqlplus "sys/password@prod as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Sat Dec 4 10:22:44 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.1.0
/db_1/dbs/spfilePROD.ora
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
/export/home/oracle/dae/rman> sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Sat Dec 4 10:23:23 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 3841982464 bytes
Fixed Size 1305392 bytes
Variable Size 910955728 bytes
Database Buffers 2919235584 bytes
Redo Buffers 10485760 bytes
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.1.0
/db_1/dbs/spfilePROD.ora
SQL>
Could you think of any other reasons?
Thank you.
December 04, 2004 - 10:56 am UTC
I have no idea what you are trying to show here.
@prod could be a database on server 5,000 miles away.
Duplicated instances
Vishal, December 05, 2004 - 5:38 am UTC
Dear Tom,
@prod is on the same box :
/usr/export/prod> sqlplus "sys/*********@prod as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Sun Dec 5 21:29:39 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
SQL> select INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
1 PROD
prod
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
/usr/export/prod> sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Sun Dec 5 21:33:10 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 3841982464 bytes
Fixed Size 1305392 bytes
Variable Size 910955728 bytes
Database Buffers 2919235584 bytes
Redo Buffers 10485760 bytes
SQL> select INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
1 PROD
prod
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning and Data Mining options
/usr/export/prod> hostname
prod
/usr/export/prod>
You need to be believe me that this is the same "prod". So back to my question, can you please advise what could cause it?
Thank you very much.
December 05, 2004 - 7:36 am UTC
do you see two instances running. ps -whatever | grep pmon
Duplicated instances
Vishal, December 05, 2004 - 3:13 pm UTC
Dear Tom,
Yes I see two instances:
oracle 10366 14833 0 07:10:40 pts/3 0:00 grep pmon
oracle 26498 1 0 Nov 29 ? 0:12 ora_pmon_PROD
oracle 10345 1 0 07:10:28 ? 0:00 ora_pmon_PROD
Thank you very much.
December 05, 2004 - 7:05 pm UTC
don't do that, you've got a problem there.
You probably started them with different oracle homes. suggest you shutdown abort both of them - figure out why you have used different oracle homes (eg: symbolic links will do that for example -- the oracle_home/oracle_sid is unique but only because you have a symbolic link to the oracle homes)
check your backups, you might be needing them.
Duplicated instances
Vishal, December 06, 2004 - 1:18 am UTC
Dear Tom,
Thank you for your prompt responses.
I understand implications of opening same database twice, that's why I was using "startup nomount" to bring second instance up.
I think you are quite right and for some reason Oracle software thinks that instances were started from different $ORACLE_HOME. I did not understand your comment about symbolic links, could you please explain?
Is there any way to find which $ORACLE_HOME was used to bring instance up?
Thank you very much.
December 06, 2004 - 11:41 am UTC
ln -s /home/oracle /foobar
export ORACLE_HOME=/foobar
just use a symbolic link.
finding your environment variables is os dependent, on linix (and solaris with /usr/ucb/ps)
$ ps -auxeww | grep pmon
ora10gr1 2379 0.0 2.1 243872 11012 ? S 09:15 0:06 ora_pmon_ora10gr1 SSH_AGENT_PID=2208 HOSTNAME=localhost.localdomain SHELL=/bin/bash TERM=xterm HISTSIZE=1000 GTK_RC_FILES=/etc/gtk/gtkrc:/home/ora10gr1/.gtkrc-1.2-gnome2 WINDOWID=27263051 QTDIR=/usr/lib/qt-3.1 USER=ora10gr1 LD_LIBRARY_PATH=/home/ora10gr1/OraHome_3/lib LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35: ORACLE_SID=ora10gr1 SSH_AUTH_SOCK=/tmp/ssh-PLFk2157/agent.2157 SESSION_MANAGER=local/localhost.localdomain:/tmp/.ICE-unix/2157 MAIL=/var/spool/mail/ora10gr1 PATH= INPUTRC=/etc/inputrc PWD=/home/ora10gr1 XMODIFIERS=@im=none LANG=en_US.UTF-8 LAMHELPFILE=/etc/lam/lam-helpfile SQLPATH=/mnt/hgfs/local/sqlstuff/ GDMSESSION=Default SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass HOME=/home/ora10gr1 SHLVL=2 GNOME_DESKTOP_SESSION_ID=Default LOGNAME=ora10gr1 ORACLE_PROC_MAKEFILE=/home/ora10gr1/OraHome_3/precomp/demo/proc/demo_proc.mk CLASSPATH=/home/ora10gr1/OraHome_3/jdbc/lib/classes12.zip:. LESSOPEN=|/usr/bin/lesspipe.sh %s DISPLAY=:0.0 ORACLE_HOME=/home/ora10gr1/OraHome_3 G_BROKEN_FILENAMES=1 COLORTERM=gnome-terminal XAUTHORITY=/home/ora10gr1/.Xauthority _=/home/ora10gr1/OraHome_3/bin/sqlplus ORA_NET2_DESC=7,10 ORACLE_SPAWNED_PROCESS=1
Duplicated instances
Vishal, December 06, 2004 - 6:12 pm UTC
Dear Tom,
Thank you very much for your help, the problem was that for whatever reason one $ORACLE_HOME value had "/" at the end and another did not. Problem is now solved.
Have a nice day.
Amazing
Vamsi Mudumba, March 15, 2005 - 8:51 pm UTC
Never knew that a single human being could pack so much knowledge in his grey cells. Just outstanding !!!!
Parameter file entry
Richard, April 08, 2005 - 12:04 pm UTC
How can an existing parameter be *dropped* ?
e.g. I recently set DB_CREATE_FILE_DEST to /ora/myTempDir, did a bit of testing, and would now like to nuke the parameter entry DB_CREATE_FILE_DEST. I have tried setting it to NULL, but that's a no-no.
Do I have to shut down the Database; startup mount; change the PFILE; destroy the SPFILE and then create the SPFILE from the PFILE?
I realise that the answer must be in the documentation, somewhere, but I've not found it so far.
April 08, 2005 - 12:21 pm UTC
ops$tkyte@ORA9IR2> alter system RESET utl_file_dir scope=spfile sid='*';
System altered.
Great!
Richard, April 08, 2005 - 12:39 pm UTC
Pfile or SPFile", version 9.0.1
Aby Singh, January 01, 2006 - 9:04 am UTC
Hi
The reviews by you are first one in the google search. But really could not work with the command "startup nomount spfile=<path>". Donot know the reason. It worked with pfile though.
January 01, 2006 - 11:13 am UTC
well, the sqlplus guide has the documentation for the startup command, spfile= is not a valid option.
</code>
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12045.htm#i2699631 <code>
to use a different that default spfile, you would create a pfile (on your client machine) that has "spfile=....." in it.
STARTUP
Syntax
STARTUP options | upgrade_options
where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] | [ OPEN [open_options] [dbname] ] | NOMOUNT ]
where open_options has the following syntax:
READ {ONLY | WRITE [RECOVER]} | RECOVER
and where upgrade_options has the following syntax:
[PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]
Starts an Oracle Database instance with several options, including mounting and opening a database.
not able to change parameters while in spfile mode
San, November 13, 2006 - 6:11 pm UTC
Tom
We are on 9i Rel2 , HP-Ux 11i.
SQL> select isspecified, count(*) from v$spparameter group
2 by isspecified;
ISSPEC COUNT(*)
------ ----------
FALSE 230
TRUE 31
SQL> select name, value from v$spparameter where lower(name) like '%spfile%';
NAME VALUE
----------------------------
spfile
When the spfile is being used, why doesn't it show up in v$spparameter ?
Thanks
San
pfile ... spfile ( dynamic/static initialization parameters
Krishna Rao, December 26, 2007 - 7:58 am UTC
Tom,
How to check which parameters can be changed dynamically and which can not? while using spfile
thanks./ TVK Rao
December 26, 2007 - 9:31 am UTC
ows selected.
ops$tkyte%ORA11GR1> edit
Wrote file afiedt.buf
1* select name, isses_modifiable, issys_modifiable, isinstance_modifiable from v$parameter order by name
ops$tkyte%ORA11GR1> /
NAME ISSES ISSYS_MOD ISINS
------------------------------ ----- --------- -----
O7_DICTIONARY_ACCESSIBILITY FALSE FALSE FALSE
active_instance_count FALSE FALSE FALSE
aq_tm_processes FALSE IMMEDIATE TRUE
archive_lag_target FALSE IMMEDIATE TRUE
asm_diskgroups FALSE IMMEDIATE TRUE
asm_diskstring FALSE IMMEDIATE TRUE
...
v$parameter tells you that.
pfile + sid + instance + oracle_home
alan, July 04, 2009 - 5:24 pm UTC
hi tom,
i understand that oracle_home + oracle_sid = unique identifier for an instance.
and i understand that pfile is use for starting an instance.
q1) is there any value in the pfile(other thant its name) that indicates the oracle_sid that we are starting ?
so when creating an instance (where does oracle take the sid from ? from the pfile ? or the environment variable ?)
q2) lets say i am connected as sys and i have 2 pfile (initdb1 and initdb2)
and i issue a startup pfile = intidb2 and open the database.
However my ORACLE_SID env variable is set to db1.
with this, i am unable to identify the instance and connect to it, am i right ?
q3) i am in a window environment -
under computer management -> service -> i have saw the need to start the oracleserviceSID before i can connect to the instance.
I have also read that in a window environment, i would use oradim to create a service and a instance.
what is the relationship between a service and a instance then ?
q4) in general, what are the relationships between the 4 item above
oracle_home + oracle_sid = instance
pfile = paratemeters to startup instance (does it register the sid as part of the hashing process? )
am i right?
Hope to hear from you soon
Best Regards
July 06, 2009 - 8:11 pm UTC
q1) it does not even need that (the oracle sid) to be in the name. the pfile could be named ANYTHING and you just use startup pfile=that_name.
No, there is nothing in the name or the file to indicate the sid for sure.
q2) that is sort of like asking "I'm driving my car see. What time will flight 141 from LAX to IAD land". I don't see the connection.
You wrote:
lets say i am connected as sys and i have 2 pfile (initdb1 and initdb2)
and i issue a startup pfile = intidb2 and open the database.
However my ORACLE_SID env variable is set to db1.
with this, i am unable to identify the instance and connect to it, am i right ?
well, you said "I am connected ..... I am unable to ... connect to it". You are connected so no, you will be able to connect to it BY DEFINITION.
ops$tkyte%ORA10GR2> select instance_name from v$instance;
INSTANCE_NAME
----------------
ora10gr2
that is your sid.
to connect, you need either
a) the oracle home + oracle sid
b) the tnsconnect string to connect over the network.
q3) that server business is a windows'ism. You need the service in the background to have the database run in the background. It is just a windows thing - if you want to learn more about windows "services" read windows documentation... It is an OS thing, not really our doing, something we have to do to run in the background.
q4) pfile is parameters and nothing to do with the sid. the pfile doesn't even have to be on the database machine itself. The sid is the sid, just a string, like a variable - it is that column in v$instance.
pfile + sid + instance + oracle_home
alan, July 07, 2009 - 12:56 am UTC
hi tom,
thanks for the explaination.
can i ask,
so do you mean that
ORACLE_HOME + ORACLE_SID = key to instance.
that ORACLE_SID is what i specified in the environment variable.
So when i startup pfile = xyz.ora
it will go read the ORACLE_SID value from the environment variable and hash it with ORACLE_HOME as a key to the instance?
what i meant is there must be a way to identified between 2 created instances running on a machine
since pfile and SID are not related.
can i said that i can use the same pfile to start 2 different instances ?
if that is the case, i know i have to differentiate them by SID, but when is the SID value determined (when the instance is startedup so that the SID is attached/bind to the instance)
hope i am not confusing things.
thanks alot in advanced!
July 07, 2009 - 1:16 am UTC
under Unix, hash(oracle_home+oracle_sid) is *literally* the key to the shared memory segment (windows doesn't have shared memory in the way unix does). In Unix, it literally is the key.
Suffice to say - oracle_home+oracle_sid is the "key" to the instance, if you set it in the environment and you are "local" (not over the network, not using sqlnet) you can login to that instance with a valid user/password.
can you use the same pfile to start two different instances? absolutely - under RAC (real application clusters) that is somewhat commonplace. Happens all of the time.
The sid is something that pre-exists the instance. Prior to an instance coming into being, someone has set the sid in the environment.
pfile + sid + instance + oracle_home
alan, July 07, 2009 - 6:01 am UTC
hi tom,
thanks for the prompt reply!
as you said
he sid is something that pre-exists the instance. Prior to an instance coming into being, someone has set the sid in the environment.
the ORACLE_SID is read and
1) used at instance creation to be used/hash as part of a key to identify the instance.
2) used at connection to an instance. by specifying the oracle_home + oracle_sid to correctly identify an instance
am i right ?
July 07, 2009 - 6:18 pm UTC
#2 should be "might be used", since you can of course connect to an instance using a service name over sqlnet without the oracle_home/oracle_sid being known
alan, July 08, 2009 - 5:13 am UTC
thanks alot tom!
hope you enjoy urself on ur visit in singapore !
pfile
raj verma, March 09, 2010 - 11:36 pm UTC
sir database is mounted by the pfile,spfile.can we use another file to mount the database.i wanna search on this topic.
pls tell soon
March 10, 2010 - 8:41 am UTC
you can use whatever you like to start an instance and then mount the database, you can use a pfile once, an spfile the next time, whatever.
SPFILE
Navanth K Gorantla, March 22, 2010 - 9:56 am UTC
Hi Tom,
This IS Navanth K Gorantla,I'm Going With Your Questions && Answers They Are Very Useful To Me.
Tom I HAve One Question,My Database Is Running With Two Parameters(SPFILE && PFILE),Do To Some Reasons My PFile Is Corrupted,Then That Time Can I Use My SPFile To Start My Database With Out Any Restores..
That Time What Abt My Database Status Its Goes To HAng Or Its Continiously On Running Only...
Pls Give ME Your Guidence To Follow This Query
Thanks&Rgrds,
Navanth K Gorantla
March 22, 2010 - 10:14 am UTC
unless your pfile references your spfile, you are using either an spfile OR a pfile
and you can use either one to startup, you pick. read the startup command description in the sqlplus guide.
A reader, February 26, 2011 - 1:33 am UTC
who to find pfile used to start up
Tom, September 11, 2012 - 9:49 pm UTC
Hi Tom and all
Thanks for this site, it has been a life saver.
Today I noticed that one of my ASM instances is not using spfile, and funny enough I did a bit of search to see if there is v$ view, but could not find any as you mentioned, however I searched through alert log file and managed to find which pfile was used.
regards