RMAN Recovery -Spfile/Controlfie

Steps to recover the spfile using RMAN backup :

Step 1 : Check Database Running Parameter
    [oracle@tes dbs]$ export ORACLE_SID=dev
    [oracle@tes dbs]$ sqlplus / as sysdba

    SQL*Plus: Release 12.2.0.1.0 Production on Sat Feb 24 11:22:55 2024

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


    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    SQL> show parameter spfile;

    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    spfile				     string	 /u01/app/oracle/product/12.2.0
                            /dbhome_1/dbs/spfiledev.ora

Step 2 : Connect RMAN and Backup the Spfile:
    [oracle@tes ~]$ rman target /

    Recovery Manager: Release 12.2.0.1.0 - Production on Sat Feb 24 11:09:32 2024

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

    connected to target database: DEV (DBID=4176617848)

    RMAN> backup spfile;

    Starting backup at 24-FEB-24
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=264 device type=DISK
    channel ORA_DISK_1: starting full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    including current SPFILE in backup set
    channel ORA_DISK_1: starting piece 1 at 24-FEB-24
    channel ORA_DISK_1: finished piece 1 at 24-FEB-24
    piece handle=/u01/app/oracle/fast_recovery_area/dev/DEV/backupset/2024_02_24/
                                o1_mf_nnsnf_TAG20240224T110948_lxm0949p_.bkp tag=TAG20240224T110948 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
    Finished backup at 24-FEB-24

    Starting Control File and SPFILE Autobackup at 24-FEB-24
    piece handle=/u01/app/oracle/fast_recovery_area/dev/DEV/autobackup/2024_02_24/
                                                                   o1_mf_s_1161774589_lxm095mb_.bkp comment=NONE
    Finished Control File and SPFILE Autobackup at 24-FEB-24

Step 3 : Move the Spfile to Backup File
    [oracle@tes ~]$ cd $ORACLE_HOME/dbs
    [oracle@oracle dbs]$ ls
    spfiledev.ora   initdev.ora
    [oracle@tes dbs]$ mv spfiledev.ora spfiledev.ora.bkp [oracle@tes dbs]$]$ ls
    spfiledev.ora   spfiledev.ora.bkp

Step 4 : connect RMAN And set dbid
    [oracle@tes dbs]$  rman target/

    Recovery Manager: Release 12.2.0.1.0 - Production on Sat Feb 24 11:11:42 2024

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

    connected to target database (not started)

    RMAN> set dbid 4176617848

    executing command: SET DBID

Step 5 : Start the database in force option with nomount stage
    RMAN> startup force nomount;

    Oracle instance started

    Total System Global Area     713031680 bytes

    Fixed Size                     8624792 bytes
    Variable Size                583009640 bytes
    Database Buffers             113246208 bytes
    Redo Buffers                   8151040 bytes

Step 6 : Restore the spfile from Auto backup Location
    RMAN> restore spfile  from autobackup;

    Starting restore at 24-FEB-24
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=254 device type=DISK

    recovery area destination: /u01/app/oracle/fast_recovery_area/dev
    database name (or database unique name) used for search: DEV
    channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/dev/DEV/autobackup/2024_02_24/
                                                            o1_mf_s_1161774623_lxm0b7y4_.bkp found in the recovery area
    channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240224
    channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/dev/DEV/autobackup/
                                                                            2024_02_24/o1_mf_s_1161774623_lxm0b7y4_.bkp
    channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
    Finished restore at 24-FEB-24

    RMAN> alter database mount;

    using target database control file instead of recovery catalog
    Statement processed


    [oracle@tes dbs]$ sqlplus / as sysdba

    SQL*Plus: Release 12.2.0.1.0 Production on Sat Feb 24 11:14:24 2024

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


    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

    SQL> select name,open_mode from v$database;

    NAME	  OPEN_MODE
    --------- --------------------
    DEV	  MOUNTED

    SQL> alter database open;

    Database altered.

    SQL> select name,open_mode from v$database;

    NAME	  OPEN_MODE
    --------- --------------------
    DEV	  READ WRITE

Steps to recover the control file using RMAN backup :
Every Oracle Database has a control file as one of its important file. It is a small binary file that records the physical structure of the database.
The control file includes:
  • Database name
  • Names and locations of associated datafiles and redo log files
  • The timestamp of the database creation
  • The current log sequence number
  • Checkpoint information
  • Without the control file, the database cannot be mounted and recovery is difficult. The control file must be available for writing by the Oracle Database server whenever the database is open.

    By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies.

    You should create two or more copies of the control file during database creation.
    Below are some of the recovery scenarios related to control files.

    SCENARIO 1 : AUTOBACKUP OF CONTROL FILE IS ENABLED
    It is highly recommended to enable autobackup of the control file. We had two control files, one at FRA location and another in Non-FRA location. We lost both of the control files
    Control file 1: /u01/app/oracle/oradata/dev/control01.ctl,
    COntrol file 2: /u01/app/oracle/fast_recovery_area/dev/control02.ctl
    Now you can not start the database since the oracle process is unable to locate the control files
        SQL> startup
        ORACLE instance started.
    
        Total System Global Area  713031680 bytes
        Fixed Size		    8624792 bytes
        Variable Size		  595592552 bytes
        Database Buffers	  100663296 bytes
        Redo Buffers		    8151040 bytes
        ORA-00205: error in identifying control file, check alert log for more info
    

    Start the restore process of control file using RMAN
        [oracle@sdbt DEV]$ rman target /
    
        Recovery Manager: Release 12.2.0.1.0 - Production on Sat Mar 2 16:36:36 2024
    
        Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
        connected to target database (not started)
    
        RMAN> startup nomount
    
        Oracle instance started
    
        Total System Global Area     713031680 bytes
    
        Fixed Size                     8624792 bytes
        Variable Size                595592552 bytes
        Database Buffers             100663296 bytes
        Redo Buffers                   8151040 bytes
    

    NOTE: Now since you enabled autobackups of your control file and also used a flash recovery area, you don’t have to explicitly provide RMAN with the name and location of backup files or your target database identifier (DBID).
    This is the simplest method that RMAN provides for restoring a control file.

    RMAN uses the value of your operating system ORACLE_SID variable to look in the default location for control file backups in the flash recovery area
        RMAN> restore controlfile from autobackup;
    
        Starting restore at 02-MAR-24
        using target database control file instead of recovery catalog
        allocated channel: ORA_DISK_1
        channel ORA_DISK_1: SID=256 device type=DISK
    
        recovery area destination: /u01/app/oracle/fast_recovery_area/dev
        database name (or database unique name) used for search: DEV
        channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/dev/DEV/autobackup/2024_03_02/
                                                            o1_mf_s_1162571367_ly61nhvq_.bkp found in the recovery area
        AUTOBACKUP search with format "%F" not attempted because DBID was not set
        channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/dev/DEV/
                                                                 autobackup/2024_03_02/o1_mf_s_1162571367_ly61nhvq_.bkp
        channel ORA_DISK_1: control file restore from AUTOBACKUP complete
        output file name=/u01/app/oracle/oradata/dev/control01.ctl
        output file name=/u01/app/oracle/fast_recovery_area/dev/control02.ctl
        Finished restore at 02-MAR-24
    

    NOTE : As we have restored a control file from a backup, we are required to perform media recovery on entire database and then open it with the open resetlogs command.
    This is true although we didn’t restore any datafiles (because the control file’s SCN is no longer synchronized with the SCNs in the datafiles and online redo log files).
    What will happen if you try to open database at this stage without doing recovery:
        RMAN> alter database open resetlogs;
    
        RMAN-00571: ===========================================================
        RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
        RMAN-00571: ===========================================================
        RMAN-03002: failure of sql statement command at 03/02/2024 16:38:05
        ORA-01507: database not mounted
    
        RMAN> alter database mount;
    
        Statement processed
        released channel: ORA_DISK_1
        RMAN> recover database;
    
        Starting recover at 02-MAR-24
        Starting implicit crosscheck backup at 02-MAR-24
        allocated channel: ORA_DISK_1
        channel ORA_DISK_1: SID=259 device type=DISK
        Crosschecked 9 objects
        Finished implicit crosscheck backup at 02-MAR-24
    
        Starting implicit crosscheck copy at 02-MAR-24
        using channel ORA_DISK_1
        Finished implicit crosscheck copy at 02-MAR-24
    
        searching for all files in the recovery area
        cataloging files...
        cataloging done
    
        List of Cataloged Files
        =======================
        File Name: /u01/app/oracle/fast_recovery_area/dev/DEV/autobackup/2024_03_02/o1_mf_s_1162571367_ly61nhvq_.bkp
    
        using channel ORA_DISK_1
    
        starting media recovery
    
        archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/oradata/dev/redo02.log
        archived log file name=/u01/app/oracle/oradata/dev/redo02.log thread=1 sequence=8
        media recovery complete, elapsed time: 00:00:01
        Finished recover at 02-MAR-24
    
        RMAN> alter database open resetlogs;
    
        Statement processed
    


    (RMAN Recovery - System/Non System)