How to startup the database if you lost both SPFILE and PFILE.

It is very rare but sometimes happened that both spfile and pfile gets deleted and also you have no backup of none of the files.

What to do now?

Solution1: You can take init.ora from another database and change only the parameters such as db_name, instance_name, archive_dest, background_dump, core_dump, user_dump, and control_files.

Solution 2: If you can’t implement the above one then surely you have to create a new pfile from scratch. But the question arises how?

Well, alert log is the answer! Just open the alert log file and extract the below information from it:

  processes                = 150

  timed_statistics         = TRUE

  shared_pool_size         = 50331648

  large_pool_size          = 8388608

  java_pool_size           = 33554432

  control_files            = D:\oracle\oradata\test\CONTROL01.CTL,   D:\oracle\oradata\test\CONTROL02.CTL, D:\oracle\oradata\test\CONTROL03.CTL

  db_block_size            = 8192

  db_cache_size            = 25165824

  compatible               = 9.2.0.0.0

  db_file_multiblock_read_count= 16

  fast_start_mttr_target   = 300

  undo_management          = AUTO

  undo_tablespace          = UNDOTBS1

  undo_retention           = 10800

  remote_login_passwordfile= EXCLUSIVE

  db_domain                =

  instance_name            = test

  dispatchers              = (PROTOCOL=TCP) (SERVICE=testXDB)

  job_queue_processes      = 10

  hash_join_enabled        = TRUE

  background_dump_dest     = D:\oracle\admin\test\bdump

  user_dump_dest           = D:\oracle\admin\test\udump

  core_dump_dest           = D:\oracle\admin\test\cdump

  sort_area_size           = 524288

  db_name                  = test

  open_cursors             = 300

  star_transformation_enabled= FALSE

  query_rewrite_enabled    = FALSE

  pga_aggregate_target     = 25165824

---Now remove the parameter   “db_domain” from it.

---Save the file as <dbname>.ora

---create spfile from pfile=’ <dbname>.ora’;

---startup

Total System Global Area  135338868 bytes

Fixed Size                   453492 bytes

Variable Size             109051904 bytes

Database Buffers           25165824 bytes

Redo Buffers                 667648 bytes

Database mounted.

Database opened.

---Take the backup of both files for future.


Solution 3: If you are using 11g then you also have the below command:


create the pfile='c:\test.ora' from memory

JJ Enjoy.