Easy recovery from SPFILE/PFILE loss 11g


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

  • New FROM MEMORY clause for CREATE 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 11.0.0.0 or higher

Example excerpt of alert.log parameters are well formatted now

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

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               = "11.2.0.0.0"
  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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s