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.

6 comments:

Zheng junxai5 said...

zhengjx20160428
kd 8
jordan retro 8
kate spade outlet
coach factory outlet online
coach outlet store online clearances
toms outlet
nfl jerseys
hollister clothing store
michael kors outlet clearance
true religion outlet
ray bans
air jordan homme
nike outlet store
ray ban outlet
burberry handbags
hollister clothing store
asics running shoes
montblanc pen
oakley canada
cheap nfl jerseys
coach outlet
michael kors outlet online
nike store outlet
coach factory outlet
nike air max 90
coach factory outlet
adidas stan smith
cheap jordan shoes
abercrombie and fitch
polo ralph lauren outlet
louis vuitton outlet stores
louis vuitton bags
louis vuitton outlet
polo ralph lauren outlet
oakley vault
cheap jerseys
gucci outlet online
coach outlet
lebron james shoes 13

Zheng junxai5 said...

zhengjx20160428
kd 8
jordan retro 8
kate spade outlet
coach factory outlet online
coach outlet store online clearances
toms outlet
nfl jerseys
hollister clothing store
michael kors outlet clearance
true religion outlet
ray bans
air jordan homme
nike outlet store
ray ban outlet
burberry handbags
hollister clothing store
asics running shoes
montblanc pen
oakley canada
cheap nfl jerseys
coach outlet
michael kors outlet online
nike store outlet
coach factory outlet
nike air max 90
coach factory outlet
adidas stan smith
cheap jordan shoes
abercrombie and fitch
polo ralph lauren outlet
louis vuitton outlet stores
louis vuitton bags
louis vuitton outlet
polo ralph lauren outlet
oakley vault
cheap jerseys
gucci outlet online
coach outlet
lebron james shoes 13

oakleyses said...

cheap oakley sunglasses, nike air max, jordan shoes, polo outlet, longchamp outlet, louis vuitton, ray ban sunglasses, tory burch outlet, longchamp pas cher, oakley sunglasses, replica watches, longchamp outlet, nike free run, louis vuitton outlet, nike air max, polo ralph lauren, christian louboutin outlet, nike outlet, oakley sunglasses, burberry pas cher, oakley sunglasses, tiffany jewelry, louis vuitton, prada outlet, replica watches, ray ban sunglasses, jordan pas cher, sac longchamp pas cher, chanel handbags, tiffany and co, ugg boots, oakley sunglasses wholesale, longchamp outlet, uggs on sale, polo ralph lauren outlet online, christian louboutin uk, michael kors pas cher, gucci handbags, christian louboutin, christian louboutin shoes, prada handbags, nike roshe, nike free, air max, kate spade outlet, ray ban sunglasses, ugg boots, louboutin pas cher

oakleyses said...

michael kors outlet, hollister pas cher, ralph lauren uk, michael kors, north face uk, coach outlet store online, true religion outlet, mulberry uk, abercrombie and fitch uk, guess pas cher, true religion outlet, lululemon canada, ray ban pas cher, michael kors, uggs outlet, vans pas cher, nike free uk, michael kors outlet, michael kors outlet online, replica handbags, nike tn, michael kors outlet, oakley pas cher, burberry outlet, polo lacoste, nike air max uk, nike blazer pas cher, hollister uk, michael kors outlet online, sac hermes, uggs outlet, michael kors outlet online, burberry handbags, new balance, coach purses, true religion jeans, north face, kate spade, ray ban uk, michael kors outlet online, true religion outlet, nike air max, converse pas cher, nike air force, nike air max uk, nike roshe run uk, sac vanessa bruno

oakleyses said...

ghd hair, hollister clothing, ray ban, iphone cases, celine handbags, oakley, insanity workout, converse, abercrombie and fitch, instyler, gucci, lululemon, ferragamo shoes, baseball bats, nike huaraches, beats by dre, babyliss, louboutin, new balance shoes, mac cosmetics, hollister, herve leger, hermes belt, giuseppe zanotti outlet, timberland boots, vans, valentino shoes, nike trainers uk, asics running shoes, p90x workout, nfl jerseys, nike air max, vans outlet, reebok outlet, soccer shoes, north face outlet, soccer jerseys, converse outlet, longchamp uk, nike roshe run, wedding dresses, ralph lauren, hollister, bottega veneta, north face outlet, mont blanc pens, nike air max, jimmy choo outlet, mcm handbags, chi flat iron

oakleyses said...

canada goose outlet, canada goose, toms shoes, links of london, karen millen uk, doudoune moncler, pandora jewelry, louis vuitton, marc jacobs, montre pas cher, canada goose outlet, moncler uk, moncler, pandora charms, juicy couture outlet, louis vuitton, thomas sabo, replica watches, canada goose jackets, ugg uk, ugg, canada goose, ugg,uggs,uggs canada, louis vuitton, wedding dresses, juicy couture outlet, canada goose, swarovski, moncler, canada goose outlet, supra shoes, moncler outlet, lancel, coach outlet, canada goose uk, louis vuitton, moncler outlet, moncler, louis vuitton, swarovski crystal, ugg pas cher, barbour, pandora uk, barbour uk, ugg,ugg australia,ugg italia, moncler, hollister, pandora jewelry