Requirement- When starting up database following occurs:SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file ‘/opt/oracle/product/11.2.0/dbhome_1/dbs/inittest01.ora’
I have faced this problem , while setting up an new image of vmware. I am starting server and i got this error.What does this mean –
Reason-
Database start using spfile (Default). In Unix default path is $ORACLE_HOME/dbs. If spfile is not present it looks for pfile at same path. If pfile is also not present it will give above message.
Implementation- If you have spfie then you can copy default values from spfile to pfile and create pfile.But what if you don’t have spfile.you have to create an pfile
How to create pfile –
When database starts it writes list of non default parameters in alert log files. We can use these values to create a pfile and start the database.
Find you alert log file and open it. This is Database Management Software Oracle 11g, Here you will see entry like this:
System parameters with non-default values:
processes = 150
sga_target = 512M
control_files = “/opt/oracle/test01/dbs/control01.ctl”
control_files = “/opt/oracle/test01/dbs/control02.ctl”
control_files = “/opt/oracle/test01/dbs/control03.ctl”
db_block_size = 8192
compatible = “10.2.0.1.0”
log_archive_dest_1 = “LOCATION=/opt/oracle/test01/archive”
log_archive_dest_state_1 = “ENABLE”
log_archive_format = “%t_%s_%r.dbf”
log_archive_max_processes= 10
log_checkpoint_interval = 9999
log_checkpoint_timeout = 0
db_file_multiblock_read_count= 16
db_recovery_file_dest = “/opt/oracle/test01/flash_recovery_area”
db_recovery_file_dest_size= 2G
undo_management = “AUTO”
undo_tablespace = “UNDOTBS1”
remote_login_passwordfile= “EXCLUSIVE”
db_domain = “agilis.com”
job_queue_processes = 32
core_dump_dest = “/opt/oracle/test01/diag/cdump”
audit_file_dest = “/opt/oracle/test01/adump”
open_links = 10
db_name = “test01”
open_cursors = 500
optimizer_index_cost_adj = 20
optimizer_index_caching = 90
pga_aggregate_target = 128M
diagnostic_dest = “/opt/oracle/test01/diag”
Tue May 31 10:55:29 2011
PMON started with pid=2, OS id=4675
Create pfile using these values:
[[email protected]]$ cd /opt/oracle/product/11.2.0/dbhome_1/dbs/
[[email protected]]$ vi inittest01.ora
Copy non default parameter values from alert log in this file and save it. This is your pfile, Start the database using this pfile.
Start the Database using Pfile:
[[email protected] dbs]$ export ORACLE_SID=test01
[[email protected] dbs]$ sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 24 15:53:16 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup pfile=’$ORACLE_HOME/dbs/inittest01.ora’
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2228200 bytes
Variable Size 163577880 bytes
Database Buffers 360710144 bytes
Redo Buffers 7946240 bytes
Database mounted.
Database opened.
SQL>
Create spfile from pfile:
SQL> create spfile from pfile=’$ORACLE_HOME/dbs/inittest01.ora’;
File created.
Shutdown the database and restart it will use spfile (Default) and problem is solved. I have also published a list of SPFILE Commands.
Happy coding with Vinay Kumar in techartifact….