×

Loading...
Ad by
  • 予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！
Ad by
  • 予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

@Calgary

Spfile and Init.ora Parameter File Startup of an Oracle9i Instance

arthurxu(ArthurXu OCP8,9i)
本文发表在 rolia.net 枫下论坛PURPOSE This note explains which initialization file can be used to start an instance, depending upon if an spfile or init.ora file exists. SCOPE & APPLICATION Intended audience is DBAs. Spfile and Init.ora Parameter File Startup of an Oracle9i Instance: =================================================================== Before Oracle9i, Oracle instances were always started using a text file called an init<SID>.ora. This file is by default located in the "$ORACLE_HOME/dbs" directory. In Oracle9i, Oracle has also introduced the 'SPFILE', which is a binary file stored on the database Server. Changes which are applied to the instance parameters may be persistent accross all startup/shutdown procedures. In Oracle9i, you can startup the instance using either an spfile or an init.ora file. An Oracle9i instance may be started by: - default spfile --> spfile<SID>.ora - default init.ora file --> init<SID>.ora - specified init.ora file - specified spfile The default initialization files must are located as follows: - on Unix ---> $ORACLE_HOME/dbs - on WindowsNT/2000 ---> %ORACLE_HOME%\database The examples below show the different ways an Oracle9i database can be started: 1. Specifying neither file: ======================== sqlplus /nolog SQL> connect / as sysdba SQL> startup Oracle first searches if the spfile<SID>.ora exists. If it does not, Oracle searchs for the spfile.ora parameter file. If neither spfile<SID>.ora nor spfile.ora exist, Oracle will use the init<SID>.ora parameter file. If none of these files exist, you receive the following messages: SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file 'D:\ORA901\DATABASE\INITORA901.ORA' 2. Specifying init.ora file: ========================= This behavior is the same as in previous database versions. SQL> startup pfile=d:\ora901\database\initORA901.ora 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. This option is not available if you are using an spfile. If you try to startup specifying an spfile, Oracle displays the following error message: SQL> startup spfile=d:\ora901\database\spfileORA901.ora SP2-0714: invalid combination of STARTUP options If you startup the database by specifying am init.ora file, the spile parameter is displayed as empty: NAME TYPE VALUE --------------------------------- ----------- ------------------------------ spfile string 3. Specifying spfile: ================== In this case, you must startup with an init.ora file in which you only specify the spfile parameter full name: spfile=d:\ora901\database\spfiletest.ora SQL> startup pfile=d:\ora901\database\inittest.ora ORACLE instance started. Total System Global Area 122449892 bytes Fixed Size 282596 bytes Variable Size 88080384 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened. Now, you can check the usage of an spfile using the following command in SQL*Plus: SQL> show parameter spfile NAME TYPE VALUE ------------------------------ ----------- --------------------------------- spfile string d:\ora901\database\spfiletest.ora You can modify the content of the specified spfile as follows: SQL>alter system set processes = 200 scope=spfile; System altered. SQL> select name, value from v$spparameter where name = 'processes'; NAME VALUE --------------- ----- processes 200更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#808484@0)
Last Updated: 2002-10-20
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / 我现在正在努力学习oracle 9i，碰到一问题,请高手指点： -lilyba(Sunshine); 2002-10-20 {272} (#808401@0)
    我试着创建一个spfile，没有成功。再次startup数据库时，出现下面的问题：
    ora-01078: failure in processing system parameters
    lrm-00109: could not open parameter file ‘c:\oracle\ora92\database\init$SID.ora’
    可是pfile文件就在哪里啊。为什么不能打开呢？
    操作系统：winxp.
    • Did you set the environmental variable for $SID? -ican(apple); 2002-10-20 (#808430@0)
    • 这个问题和你的环境有关系，你好好看看Spfile 和　Pfile的关系吧，还有就是启动顺序，如果你要别人帮忙的话就把尽可能详细的信息发出来的，如果你想自己解决的话就好好看书，反正绝对不是什么BUG之类的东西! -arthurxu(ArthurXu OCP8,9i); 2002-10-20 (#808443@0)
      • 天，我咋敢说是否是bug的问题呢。开始是很好。我就是试验创建一个spfile，没有成功。然后数据库就启动不起来了。 -lilyba(Sunshine); 2002-10-20 {160} (#808455@0)
        从网上查，有的说是因为process太多，有的说是因为机器可能原来装过oracle,别的sid也可能存在。但是，我实际上从8i升级到9i,从注册表删除了原来的数据库。而且用的一个sid.
        • 描述问题要象这样才专业，看看！别没头没脑的问几句，谁也帮不了你 -arthurxu(ArthurXu OCP8,9i); 2002-10-20 {1542} (#808482@0)
          本文发表在 rolia.net 枫下论坛Renamed DB - does not startup with spfile

          I just renamed our databae from logrev9 to logrev. I used the backup control file method to do this with no errors. I used the pfile to start the rename process initially. Once the database was created and open, I did a CREATE SPFILE to generate a new file with the new name. However, when the database uses the spfile to start it fails referencing the old name. I deleted the spfile.ora and recreated it again with no change. The database DOES start with the pfile...

          SQL> startup pfile='/apps/oracle/admin/logrev/pfile/initlogrev.ora';
          ORACLE instance started.

          Total System Global Area 1018136624 bytes
          Fixed Size 731184 bytes
          Variable Size 167772160 bytes
          Database Buffers 838860800 bytes
          Redo Buffers 10772480 bytes
          Database mounted.
          Database opened.
          SQL> CREATE SPFILE='/apps/oracle/9.2.0/dbs/spfilelogrev.ora' FROM pfile='/apps/oracle/admin/logrev/pfile/initlogrev.ora';

          File created.

          SQL> shutdown immediate
          Database closed.
          Database dismounted.
          ORACLE instance shut down.
          SQL> startup
          ORA-01078: failure in processing system parameters
          LRM-00109: could not open parameter file '/apps/oracle/9.2.0/dbs/initlogrev9.ora'

          It still has some reference to the old name. I've check the initlogrev.ora file for the old reference name and it is not there. I've done renames and db moves using this method many times successfully in Oracle 8i. This is my first attempt in 9i R2. Is there something I might be missing in 9i?

          Thanks,
          Dave更多精彩文章及讨论，请光临枫下论坛 rolia.net
    • Spfile and Init.ora Parameter File Startup of an Oracle9i Instance -arthurxu(ArthurXu OCP8,9i); 2002-10-20 {4082} (#808484@0)
      本文发表在 rolia.net 枫下论坛PURPOSE This note explains which initialization file can be used to start an instance, depending upon if an spfile or init.ora file exists. SCOPE & APPLICATION Intended audience is DBAs. Spfile and Init.ora Parameter File Startup of an Oracle9i Instance: =================================================================== Before Oracle9i, Oracle instances were always started using a text file called an init<SID>.ora. This file is by default located in the "$ORACLE_HOME/dbs" directory. In Oracle9i, Oracle has also introduced the 'SPFILE', which is a binary file stored on the database Server. Changes which are applied to the instance parameters may be persistent accross all startup/shutdown procedures. In Oracle9i, you can startup the instance using either an spfile or an init.ora file. An Oracle9i instance may be started by: - default spfile --> spfile<SID>.ora - default init.ora file --> init<SID>.ora - specified init.ora file - specified spfile The default initialization files must are located as follows: - on Unix ---> $ORACLE_HOME/dbs - on WindowsNT/2000 ---> %ORACLE_HOME%\database The examples below show the different ways an Oracle9i database can be started: 1. Specifying neither file: ======================== sqlplus /nolog SQL> connect / as sysdba SQL> startup Oracle first searches if the spfile<SID>.ora exists. If it does not, Oracle searchs for the spfile.ora parameter file. If neither spfile<SID>.ora nor spfile.ora exist, Oracle will use the init<SID>.ora parameter file. If none of these files exist, you receive the following messages: SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file 'D:\ORA901\DATABASE\INITORA901.ORA' 2. Specifying init.ora file: ========================= This behavior is the same as in previous database versions. SQL> startup pfile=d:\ora901\database\initORA901.ora 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. This option is not available if you are using an spfile. If you try to startup specifying an spfile, Oracle displays the following error message: SQL> startup spfile=d:\ora901\database\spfileORA901.ora SP2-0714: invalid combination of STARTUP options If you startup the database by specifying am init.ora file, the spile parameter is displayed as empty: NAME TYPE VALUE --------------------------------- ----------- ------------------------------ spfile string 3. Specifying spfile: ================== In this case, you must startup with an init.ora file in which you only specify the spfile parameter full name: spfile=d:\ora901\database\spfiletest.ora SQL> startup pfile=d:\ora901\database\inittest.ora ORACLE instance started. Total System Global Area 122449892 bytes Fixed Size 282596 bytes Variable Size 88080384 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes Database mounted. Database opened. Now, you can check the usage of an spfile using the following command in SQL*Plus: SQL> show parameter spfile NAME TYPE VALUE ------------------------------ ----------- --------------------------------- spfile string d:\ora901\database\spfiletest.ora You can modify the content of the specified spfile as follows: SQL>alter system set processes = 200 scope=spfile; System altered. SQL> select name, value from v$spparameter where name = 'processes'; NAME VALUE --------------- ----- processes 200更多精彩文章及讨论，请光临枫下论坛 rolia.net
    • Check the environmnet step by step -kew888(西竹); 2002-10-21 {382} (#809362@0)
      Check the name of the init file. Maybe type error or file properties.
      Make sure that there is no read/write privileges been set up incorrect.
      By the way, if your SID is 'TEST', your init file name should be 'initTEST.ora' .
      If you don't know whether there is another Oracle Home been set up in your server, run 'REGEDIT' to check your registry for sure.
      Hope you do good.
      • Thank you very much. I can only try to find the problem after work. Hope tonight I can figure out the problem according to you guys' suggestion. Thanks again. -lilyba(Sunshine); 2002-10-21 (#809385@0)

More Topics

  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
  • 如何在 AWS EC2 实例上构建 WordPress 应用程序?
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论