Friday, May 27, 2011

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.

No comments: