Restoring Oracle’s Initial Database Settings
I messed up my local Oracle database settings while tweaking its SGA memory and the instance would not start. TNS Listener would run, and SQLPLUS connects, but no instance. I have XE installed and programs trying to connect to the XE SID would fail.
Tried connecting to SQLPLUS / AS SYSDBA works but running 'startup' gave me a weird
ORA-44412: XE edition memory parameter invalid or not specified
It was then I realized something went awfully wrong with the new memory settings but I couldn't find an easy way to undo it. Luckily, there's a way to start a db instance using the default settings that came with a fresh Oracle install. Here are the sequence of commands:
Try recreating it from the template init.ora (in the %ORACLE_HOME%\config\scripts directory). So connect to sqlplus as sysdba then run:
startup pfile = $ORACLE_HOME/config/scripts/init.ora
If the database comes up without errors then recreate the spfile with:
create spfile from pfile='$ORACLE_HOME/config/scripts/init.ora';
March 15th, 2009 - 22:24
i try “startup pfile = $ORACLE_HOME/config/scripts/init.ora”,but it gave me a weird
LRM-00109: could not open parameter file ‘$ORACLE_HOME/config/scripts/init.ora’
ORA-01078: failure in processing system parameters
how can i do?
March 18th, 2009 - 11:14
Seems your ORACLE_HOME might not be set.
July 14th, 2009 - 05:22
Thanks,
That was really helpful. I also messed up my configuration. The next time I will backup the entire database before trying to change the sga parameters.
To set environment variables when connecting locally, enter one of the following commands in a terminal session.
For Bourne, Korn, or Bash shell:
source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh
For C shell:
source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.csh
for more informacion:
http://download.oracle.com/docs/cd/B25329_01/doc/admin.102/b25107/startup.htm#BABEIJHA