How to Restore spfile from autobackup in ORACLE

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.

Leave a Comment