If you have lost the SPFILE (server parameter file), and your Database abnormally shutdown. Then you will try to startup your Database but you got an error regarding spfile not found. You can restore spfile from autobackup using RMAN.
NOTE:- Database must be in archivelog mode and must be configured Controlfile Autobackup on.
Error :
[oracle@node-1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 1 12:50:43 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initORCL.ora'
SQL>
Steps to Restore SPFILE from autobackup :
Step-1 : Since we don’t even have SPFILE, we need to start the instance with default parameters. Start database instance with default parameters by typing the following command at RMAN prompt
[oracle@node-1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat May 1 13:03:44 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> set DBID=1595923691;
executing command: SET DBID
RMAN> startup nomount force;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initORCL.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1073737800 bytes
Fixed Size 8904776 bytes
Variable Size 276824064 bytes
Database Buffers 784334848 bytes
Redo Buffers 3674112 bytes
RMAN>
Step-2 : Now restore SPFILE from autobackup by typing the following command. When we give the below command RMAN will fail with the following error.
RMAN> restore spfile from autobackup;
Starting restore at 01-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210501
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210430
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210429
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210428
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210427
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210426
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210425
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/01/2021 13:08:11
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
RMAN>
To restore SPFILE we need to give the location of the auto backup file. You can view the location and name of the most recent autobackup files by looking into the flash_recovery_area/autobackup location.
In my case auto backup location is defined below.
[oracle@node-1 ~]$ cd /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2021_05_01/
[oracle@node-1 2021_05_01]$
[oracle@node-1 2021_05_01]$ ls -ltrh
total 11M
-rw-r-----. 1 oracle oinstall 11M May 1 12:48 o1_mf_n_1071406089_j8t05lfm_.bkp
[oracle@node-1 2021_05_01]$
[oracle@node-1 2021_05_01]$
RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2021_05_01/o1_mf_n_1071406089_j8t05lfm_.bkp';
Starting restore at 01-MAY-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2021_05_01/o1_mf_n_1071406089_j8t05lfm_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 01-MAY-21
RMAN>Step-3 : After completed recovery of spfile first you need to do shutdown and then you can open the Database :
RMAN> shutdown;
Oracle instance shut down
RMAN> startup;
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 759165480 bytes
Fixed Size 8901160 bytes
Variable Size 285212672 bytes
Database Buffers 461373440 bytes
Redo Buffers 3678208 bytes
RMAN> select name, database_role, open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- --------------------
ORCL PRIMARY READ WRITE
RMAN>
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.