Easy recovery from SPFILE/PFILE loss 11g

This article presents new feature of 11g for pfile/spfile

CREATE PFILE [='pfile_name'] 
FROM { { SPFILE [='spfile_name'] } | MEMORY };
CREATE SPFILE [='pfile_name']
FROM { { PFILE [='spfile_name'] } | MEMORY };
  • Instance parameters written to alert.log are in better format to facilitate cutting and pasting
  • COMPATIBLE must be set to or higher

Example excerpt of alert.log parameters are well formatted now

Starting up:
Oracle Database 11g Enterprise Edition Release - 
64bit Production With the Partitioning, OLAP, Data Mining 
and Real Application Testing options.
Using parameter settings in client-side 

System parameters with non-default values:
  processes                = 150
  nls_language             = "POLISH"
  nls_territory            = "POLAND"
  memory_target            = 2G
  control_files            = "D:APPORACLEORA11GTEMPCONTROL.CTL"
  db_block_size            = 8192
  compatible               = ""
  log_archive_format       = "ARC%S_%R.%T"
  db_create_file_dest      = "+DATA"
  db_recovery_file_dest    = "+BACKUP"
  db_recovery_file_dest_size= 10G
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=ORA11GXDB)"
  audit_file_dest          = "D:APPORACLEADMINORA11GADUMP"
  audit_trail              = "DB"
  db_name                  = "seeddata"
  db_unique_name           = "ORA11G"
  open_cursors             = 300
  diagnostic_dest          = "D:APPORACLE"
Sun Aug 05 20:38:51 2012
PMON started with pid=2, OS id=5108 
Sun Aug 05 20:38:51 2012
PSP0 started with pid=3, OS id=5476

Example SPFILE/PFILE from memory

--change one instance parameter but only in memory
ALTER system SET optimizer_index_cost_adj=10 scope=memory;
--make backup as spfile from memory
CREATE spfile='/tmp/spfile' FROM memory;
--make backup as pfile from memory
CREATE pfile='/tmp/pfile' FROM memory;

Leave a Reply

Your email address will not be published. Required fields are marked *