By William Sescu

Not too often, but sometimes you might run into a situation when you lose everything, your DB Files, your Controlfiles and even your spfile. In situations like that, you need to restore first your spfile, then your controlfile and then the rest.

For restoring the spfile, RMAN has a chicken/egg issue. To be able to restore the spfile, RMAN needs at least a running instance, but how do we start the instance without having the spfile? There are several methods to do it, one of it, is to let RMAN itself create a dummy instance.

But if you think your situation can’t get worse than the following happens. Oracle raises an ORA-04031 error and even the Dummy Instance does not start.

oracle@oel001:/home/oracle/ [rdbms121] export ORACLE_SID=LAX
oracle@oel001:/home/oracle/ [LAX] rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 28 11:45:08 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u00/app/oracle/product/12.1.0.2/dbs/initLAX.ora'

starting Oracle instance without parameter file for retrieval of spfile
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 10/28/2016 11:45:15
RMAN-04014: startup failed: ORA-04031: unable to allocate 111264 bytes of shared memory 
("shared pool","unknown object","sga heap(1,0)","KEWS sesstat values")

 

The error message means, that the SGA allocated for the dummy instance is to small. This is where the environment variable ORA_RMAN_SGA_TARGET comes into play. The environment variable ORA_RMAN_SGA_TARGET sets the SGA to a value in Megabytes which can be used by RMAN to start the Dummy Instance. In the following example to 1024 MB.

oracle@oel001:/home/oracle/ [LAX] export ORA_RMAN_SGA_TARGET=1024
oracle@oel001:/home/oracle/ [LAX]

oracle@oel001:/home/oracle/ [LAX] rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Oct 28 13:12:13 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u00/app/oracle/product/12.1.0.2/dbs/initLAX.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes
Variable Size                293601384 bytes
Database Buffers             771751936 bytes
Redo Buffers                   5455872 bytes

RMAN>

Behind the scenes, Oracle starts a Dummy Instance with the following parameters, and creates all necessary directories in the DIAG destination.

sga_target               = 1G
compatible               = "12.1.0.2.0"
_dummy_instance          = TRUE
remote_login_passwordfile= "EXCLUSIVE"
_diag_adr_trace_dest='/u00/app/oracle/diag/rdbms/dummy/LAX/trace'
core_dump_dest='/u00/app/oracle/diag/rdbms/dummy/LAX/cdump'
db_name='DUMMY' 
  
oracle@oel001:/u00/app/oracle/diag/rdbms/dummy/LAX/ [LAX] pwd
/u00/app/oracle/diag/rdbms/dummy/LAX
oracle@oel001:/u00/app/oracle/diag/rdbms/dummy/LAX/ [LAX] ls
alert  cdump  hm  incident  incpkg  ir  lck  log  metadata  metadata_dgif  metadata_pv  stage  sweep  trace

 

Now it worked and I can retrieve my spfile.

run {
restore spfile to pfile '/tmp/initLAX.ora' for db_unique_name='LAX' from
'+fra/lax/autobackup/2016_10_28/s_894893708.1292.894893713';
}

From now on, I can use the correct parameter file to continue with the other steps. Another option would have been, to search through your alert.log for “System parameters with non-default values”. Whenever you startup your instance, Oracle dumps out the non-default parameter values into the alert.log. Those values can be used to manually create an init.ora file and then the spfile. The drawback of using the values from alert.log is, that the values might be very old. In case the instance was not bounced for several months or longer (not so unusual), then you miss all the new setting since then.

...
...
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /u00/app/oracle/product/12.1.0.2
System name:    Linux
Node name:      oel001
Release:        2.6.32-642.6.1.el6.x86_64
Version:        #1 SMP Tue Oct 4 15:19:03 PDT 2016
Machine:        x86_64
System parameters with non-default values:
  processes                = 300
  _disable_highres_ticks   = TRUE
  event                    = "10720 trace name context forever, level 0x10000000"
  event                    = "10795 trace name context forever, level 2"
  sga_max_size             = 1536M
  use_large_pages          = "ONLY"
  shared_pool_size         = 256M
  _high_priority_processes = "LGWR"
  _highest_priority_processes= "LGWR"
  filesystemio_options     = "SETALL"
  sga_target               = 1536M
  control_files            = "+DATA/LAX/CONTROLFILE/current.265.918392661"
  control_files            = "+FRA/LAX/CONTROLFILE/current.256.918392661"
  control_file_record_keep_time= 32
  db_block_size            = 8192
  compatible               = "12.1.0.2.0"
  log_archive_format       = "%t_%s_%r.dbf"
  db_create_file_dest      = "+DATA"
  db_create_online_log_dest_1= "+DATA"
  db_recovery_file_dest    = "+FRA"
  db_recovery_file_dest_size= 32G
  undo_tablespace          = "UNDOTBS1"
  undo_retention           = 3600
  db_securefile            = "PERMITTED"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=LAXXDB)"
  local_listener           = "LAX_LISTENER"
  session_cached_cursors   = 512
  parallel_max_servers     = 80
  audit_file_dest          = "/u00/app/oracle/admin/LAX/adump"
  audit_trail              = "DB"
  cell_offload_processing  = FALSE
  db_name                  = "LAX"
  open_cursors             = 300
  pga_aggregate_target     = 512M
  _disable_directory_link_check = TRUE
  diagnostic_dest          = "/u00/app/oracle"
...
...

In the end, you have different possibilities to restore the spfile. Either with the RMAN Dummy Instance, or via the alert.log. In case of the Dummy Instance, you might need to play around with the ORA_RMAN_SGA_TARGET environment variable.

By the way … with Oracle 10.2.0.5 your chance to hit the ORA-04031 error during the Dummy Instance startup was much higher, because the default was only 152MB.

oracle@oel001:/home/oracle/ [LAX] rman target /

Recovery Manager: Release 10.2.0.5.0 - Production on Fri Oct 28 11:26:09 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u00/app/oracle/product/10.2.0.5/dbs/initLAX.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     2094736 bytes
Variable Size                 67111280 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6291456 bytes

RMAN>

 

Cheers,
William

 

Post Views: 1,056
Thumbnail [60x60]
by
Oracle Team