Re: How to Change ini.ora filename on Windows?

On May 18, 12:27 pm, EscVector <J..._at_webthere.com> wrote:
> On May 18, 8:49 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
>
>
> > On May 17, 11:10 pm, EscVector <J..._at_webthere.com> wrote:
>
> > > On May 17, 8:31 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > > I am not sure why the registry key did not work for you - maybe a
> > > > reboot is required. The quotes in my previous post were direct from
> > > > the Oracle 8i documentation. A quick search on Google Groups for:
> > > > Oracle 8 ORA_SID_PFILE locates a couple threads related to this
> > > > registry key. SID needs to be replaced with the approproriate SID
> > > > name.
>
> > > > It looks like the registry key may apply to 10g also:http://www.oracle.com/technology/products/oraclenet/pdf/ADsupport_10g...
> > > > (pgs 83, 84, 171)
> > > > "ORA_SID_PFILE This parameter sets the full path to the initialization
> > > > parameter file. If this entry is not present, then oradim will try to
> > > > start the database with an SPFILE or PFILE from ORACLE_HOME\database."
>
> > > > "To start the database, start OracleServiceSID. This automatically
> > > > starts ORADIM and issues the -STARTUP command using the initialization
> > > > parameter file identified by ORA_SID_PFILE."
>
> > > > "ORA_SID_PFILE Specifies full path to initialization parameter file.
> > > > The default value is ORACLE_BASE\admin\DB_NAME\pfile\init.ora"
>
> > > > Charles Hooper
> > > > IT Manager/Oracle DBA
> > > > K&M Machine-Fabricating, Inc.
>
> > > Copied from:
> > > Administering a Database on Windows 5-7
> > > "ORA_SID_PFILE
> > > This parameter sets the full path to the initialization parameter
> > > file. If this entry is
> > > not present, then oradim will try to start the database with an SPFILE
> > > or PFILE
> > > from ORACLE_HOME\database."
>
> > > 10.2.0.3 Test on WinXP.
>
> > > Database running....
>
> > > Removed initBJORK4.ora file from the database directory.
> > > Spfile and initBJORK4.ora file are both in <ORABASE>\admin
> > > \bjork4\pfile directory.
>
> > > Added regstring:
> > > Windows Registry Editor Version 5.00
> > > [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1]
> > > "ORA_BJORK4_PFILE"="C:\\app\\oracle\\product\\10.2.0\\admin\\BJORK4\
> > > \pfile"
>
> > > C:\WORK>net stop oracleservicebjork4
> > > The OracleServiceBJORK4 service is stopping..........
> > > The OracleServiceBJORK4 service was stopped successfully.
>
> > > C:\WORK>net start oracleservicebjork4
> > > The OracleServiceBJORK4 service is starting..
> > > The OracleServiceBJORK4 service was started successfully.
>
> > > Closed and opened new cmd window.
>
> > > C:\WORK>sqlplus /nolog
>
> > > SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 17 22:58:00 2007
>
> > > Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
>
> > > 22:58:00 @> connect / as sysdba
> > > Connected to an idle instance.
> > > 22:58:04 SYS_at_BJORK4> startup
> > > ORA-01078: failure in processing system parameters
> > > LRM-00109: could not open parameter file 'C:\APP\ORACLE\PRODUCT
> > > \10.2.0\DB_1\DATABASE\INITBJORK4.ORA'
> > > 22:58:06 SYS_at_BJORK4>
>
> > > put file back....
>
> > > 22:58:06 SYS_at_BJORK4> startup
> > > ORACLE instance started.
>
> > > removed file again....
>
> > > Database opened.
> > > 23:04:05 SYS_at_BJORK4> shutdown abort;
> > > ORACLE instance shut down.
> > > 23:06:53 SYS_at_BJORK4> startup
> > > ORA-01078: failure in processing system parameters
> > > LRM-00109: could not open parameter file 'C:\APP\ORACLE\PRODUCT
> > > \10.2.0\DB_1\DATABASE\INITBJORK4.ORA'
> > > 23:07:30 SYS_at_BJORK4> exit
> > > Disconnected from Oracle Database 10g Enterprise Edition Release
> > > 10.2.0.3.0 - Production
> > > With the Partitioning, OLAP and Data Mining options
>
> > > C:\WORK>oradim -shutdown -sid bjork4 -shuttype srvc
>
> > > (prompt returned too quickly, so I know the db didn't start....
>
> > > C:\WORK>oradim -startup -sid bjork4 -starttype srvc
>
> > > C:\WORK>oradim -startup -sid bjork4 -starttype inst
> > > ORA-01078: failure in processing system parameters
> > > LRM-00109: could not open parameter file 'C:\APP\ORACLE\PRODUCT
> > > \10.2.0\DB_1\DATABASE\INITBJORK4.ORA'
>
> > > ---------------------
>
> > > I don't think oracle documentation is accurate here. I'd be glad to
> > > see a test that proves me wrong. Anyone?
>
> > Looks like a thorough test. I just performed a couple tests that are
> > not quite as easy to describe (I did not use the command line). I
> > shut down the service using the Service Control Panel app - on Oracle
> > 10.2.0.2, this is the equivalent of shutdown immediate (in 8i, the
> > default is shutdown abort). I then created a new string value
> > registry key (REG_SZ) named ORA_TEST_PFILE and set its value to d:
> > \oracle\initTEST.ora - after renaming the spfile and inittest.ora
> > file in the database folder, I copied the initTEST.ora file to the d:
> > \oracle folder and renamed it back to initTEST.ora. I then started
> > RegMon and FileMon and started the Oracle service. From the FileMon
> > log:
> > 8:24:01 AM oradim.exe:652 OPEN D:\oracle\initTEST.ora SUCCESS
> > 8:24:01 AM oradim.exe:652 READ D:\oracle\initTEST.ora SUCCESS
> > 8:24:01 AM oradim.exe:652 READ D:\oracle\initTEST.ora END OF FILE
> > 8:24:01 AM oradim.exe:652 READ D:\oracle\initTEST.ora END OF FILE
> > 8:24:01 AM oradim.exe:652 CLOSE D:\oracle\initTEST.ora SUCCESS
>
> > >From the RegMon log:
>
> > 8.82039165 oracle.exe:1780 QueryValue HKLM\SOFTWARE\ORACLE
> > \KEY_OraDb10g_home1\ORA_test_CWD NOT FOUND
> > 10.00621128 oracle.exe:1780 QueryValue HKLM\SOFTWARE\ORACLE
> > \KEY_OraDb10g_home1\ORA_test_CWD NOT FOUND
> > 10.00648308 oracle.exe:1780 QueryValue HKLM\SOFTWARE\ORACLE
> > \KEY_OraDb10g_home1\ORA_test_CWD NOT FOUND
> > 9.95929623 oracle.exe:1780 QueryValue HKLM\SOFTWARE\ORACLE
> > \KEY_OraDb10g_home1\ORA_test_LPENABLE NOT FOUND
> > 9.97435474 oracle.exe:1780 QueryValue HKLM\SOFTWARE\ORACLE
> > \KEY_OraDb10g_home1\ORA_test_LPORDER NOT FOUND
> > 9.02463055 oracle.exe:1780 QueryValue HKLM\SOFTWARE\ORACLE
> > \KEY_OraDb10g_home1\ORA_test_MEM_BLKSIZE NOT FOUND
> > 8.89042091 oracle.exe:1780 QueryValue HKLM\SOFTWARE\ORACLE
> > \KEY_OraDb10g_home1\ORA_test_PFILE SUCCESS d:\oracle\initTEST.ora
> > 8.89045238 oracle.exe:1780 QueryValue HKLM\SOFTWARE\ORACLE
> > \KEY_OraDb10g_home1\ORA_test_PFILE SUCCESS d:\oracle\initTEST.ora
> > 9.95917988 oracle.exe:1780 QueryValue HKLM\SOFTWARE\ORACLE
> > \KEY_OraDb10g_home1\ORA_test_PGAPROTECT NOT FOUND
>
> > >From the Alter Log:
>
> > Fri May 18 08:24:02 2007
> > alter database mount exclusive
> > Fri May 18 08:24:06 2007
> > Setting recovery target incarnation to 1
> > Fri May 18 08:24:06 2007
> > Successful mount of redo thread 1, with mount id 1921064994
> > Fri May 18 08:24:06 2007
> > Database mounted in Exclusive Mode
> > Completed: alter database mount exclusive
> > Fri May 18 08:24:06 2007
> > alter database open
> > Fri May 18 08:24:06 2007
> > Thread 1 opened at log sequence 38
> > Current log# 2 seq# 38 mem# 0: D:\ORACLE\...\REDOTEST02.LOG
> > Successful open of redo thread 1
> > Fri May 18 08:24:06 2007
> > SMON: enabling cache recovery
> > Fri May 18 08:24:07 2007
> > Successfully onlined Undo Tablespace 1.
> > Fri May 18 08:24:07 2007
> > SMON: enabling tx recovery
> > Fri May 18 08:24:07 2007
> > Database Characterset is WE8MSWIN1252
> > replication_dependency_tracking turned off (no async multimaster
> > replication found)
> > Starting background process QMNC
> > QMNC started with pid=14, OS id=1404
> > Fri May 18 08:24:10 2007
> > Completed: alter database open
>
> > SQL*PLUS:
> > SQL> connect sys/change_on_install_at_test
> > Connected.
> > SQL>
>
> > The above test was performed on Oracle 10.2.0.2 with Oct 2006 patch.
>
> > Just to be clear, this test is only intended to show that the
> > documentation in this case appears to be correct, and not that one
> > test is right and another is wrong.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> I had posted my latests, but it didn't take, so here's the short
> version.
>
> Looks like reboot changes the search path if regkey value is there on
> start, it works per docs. If no key and only init or spfile in
> datbase directory, it behaves per my first test.

Experienced Oracle Users,

Here's what I found on my 8i instance:

Changing the registry entry HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE \HOME0\ORA_<SID>_PFILE affects the Oracle init file on host restart, probably via the appropriate service. But the pesky file in <drive>: \oracle\<hostname>\database\init.ora contained a single line -- an IFILE reference of the original pathname (in my case 'D:\oracle\admin \erwin\pfile\init.ora'). When I changed this to reference the newly renamed init.ora file (in my case 'initERwin.ora'), I was able to bounce oracle via SQLPlus.

I'm so pleased to find that there is more than one reference which governs the startup of the instance contingent on the manner used to start!

I'd guess samesame behavior for 10g, but mañana será otro día. Received on Mon May 21 2007 - 13:15:59 CDT