Control FileRMAN

When the Controlfile is Missing

When the controlfile is missing, the database can’t mount the controlfile, you need to restore the controlfile before going further. But first of all, you must have at least one copy of the controlfile before restoring it. Otherwise, you will have a big trouble.

The best practice of the backup strategy against the controlfile is to configure controlfile AUTOBACKUP ON, and furthermore, backup the controlfile as a copy regularly in case the failure of the autobackup.

How to Configure AUTOBACKUP ON?

AUTOBACKUP ON in RMAN will enable the database to backup the controlfile to specific path automatically whenever the definition of database is changed.

Check AUTOBACKUP ON

The default configuration of AUTOBACKUP is ON in RMAN. For those databases which are disabled, we can enable it like this:

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Please note that, once AUTOBACKUP is ON, the backup set of BACKUP DATABASE will not include SPFILE and CONTROLFILE except that you explicitly add BACKUP CURRENT CONTROLFILE SPFILE in the run block.

Change AUTOBACKUP Location

To change the backup location, you can do this:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/acfsmounts/acfs1/autobackup/%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/acfsmounts/acfs1/autobackup/%F';
new RMAN configuration parameters are successfully stored

Please note that, the new location must be a shared location for a RAC environment.

In case of any AUTOBACKUP failure, I recommend to backup it as a copy to a specific location regularly.

How to Copy Controlfile?

Since the controlfile is a very busy file on reading and writing when your database is open, you can’t copy the file directly to another place. Instead, you should use SQL or RMAN to snapshot online controlfile out of the database. This will make the file consistent.

By SQL

You can use SQL statement to backup a copy of controlfile to a specific location.

SQL> alter database backup controlfile to '/tmp/primdb1-20181025.ctl';

Database altered.

SQL> !ls -l /tmp/primdb1-20121212-2.ctl
-rw-r----- 1 oracle asmadmin 19709952 Oct 25 16:24 /tmp/primdb1-20181025.ctl

By RMAN

Use the following statement to get a copy of current controlfile.

RMAN> backup as copy current controlfile format '/tmp/primdb1-20121212.ctl';

Starting backup at 12-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 instance=primdb1 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/tmp/primdb1-20121212.ctl tag=TAG20121212T162940 RECID=16 STAMP=801851384
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 12-DEC-12

Starting Control File and SPFILE Autobackup at 12-DEC-12
piece handle=+DATA/primdb/autobackup/2012_12_12/s_801851388.1110.801851393 comment=NONE
Finished Control File and SPFILE Autobackup at 12-DEC-12

Please note that, the above copies can be used directly. No need to restore before using it, just copy it to the destination specified in SPFILE.

Restore Control File

Here are the steps we solve the problem.

  1. Check the current controlfile name.
  2. [oracle@primary01 ~]$ sqlplus / as sysdba
    ...
    SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;


       INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
    ---------- -------------------- ---------------- --------------------
             1 MOUNTED              PRIMARY          NOT ALLOWED

    SQL> select name from v$controlfile;

    NAME
    --------------------------------------------------------------------------------
    +DATA/primdb/controlfile/current.260.801855881

  3. Check the current backup records.
  4. [oracle@primary01 ~]$ rman target /
    ...
    connected to target database: COMPDB (DBID=841830157, not open)

    RMAN> list backup;

    using target database control file instead of recovery catalog

    List of Backup Sets
    ===================

    ...
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    7       Full    18.64M     DISK        00:00:04     12-DEC-12
            BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20121212T135622
            Piece Name: +DATA/primdb/autobackup/2012_12_12/s_801830930.1089.801842187
      SPFILE Included: Modification time: 12-DEC-12
      SPFILE db_unique_name: PRIMDB
      Control File Included: Ckp SCN: 2948291      Ckp time: 12-DEC-12

    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    8       Full    18.64M     DISK        00:00:07     12-DEC-12
            BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20121212T162948
            Piece Name: +DATA/primdb/autobackup/2012_12_12/s_801851388.1110.801851393
      SPFILE Included: Modification time: 12-DEC-12
      SPFILE db_unique_name: PRIMDB
      Control File Included: Ckp SCN: 2979410      Ckp time: 12-DEC-12

    You can see we have two autobackup sets containing controlfile and spfile, which are backed up by the database automatically. They could be useful when we lose our controlfile.
  5. Shutdown the database for maintenance.
  6. SQL> shutdown immediate;
    ORA-01109: database not open


    Database dismounted.
    ORACLE instance shut down.

  7. Remove the current controlfile to simulate the missing of a controlfile.
  8. ASMCMD> pwd
    +data/primdb/controlfile

    ASMCMD> ls -l
    Type         Redund  Striped  Time             Sys  Name
    CONTROLFILE  HIGH    FINE     DEC 12 17:00:00  Y    current.260.801855881

    ASMCMD> rm current.260.801855881
    ASMCMD> ls -l
    ASMCMD-08002: entry 'controlfile' does not exist in directory '+data/primdb/'

  9. Try to open the database.
  10. SQL> startup open;
    ORACLE instance started.

    Total System Global Area  839282688 bytes
    Fixed Size                  2217992 bytes
    Variable Size             792725496 bytes
    Database Buffers           41943040 bytes
    Redo Buffers                2396160 bytes
    ORA-00205: error in identifying control file, check alert log for more info
    The database startup to nomount and stopped.

  11. Check the alert log.
  12. [oracle@primary01 ~]$ cd /u01/app/oracle/diag/rdbms/primdb/primdb1/trace/
    [oracle@primary01 trace]$ tail -50 aler*
    ...
    ORA-00210: cannot open the specified control file
    ORA-00202: control file: '+DATA/primdb/controlfile/current.260.801855881'
    ORA-17503: ksfdopn:2 Failed to open file +DATA/primdb/controlfile/current.260.801855881
    ORA-15012: ASM file '+DATA/primdb/controlfile/current.260.801855881' does not exist
    ORA-205 signalled during: ALTER DATABASE   MOUNT...

  13. Restore the controlfile
  14. From AUTOBACKUP

    RMAN will pick the latest autobackup to restore control file.

    [oracle@primary01 ~]$ rman target /
    ...
    connected to target database: COMPDB (not mounted)

    RMAN> run {
    2> set dbid 841830157;
    3> set controlfile autobackup format for device type disk to '+data/primdb/autobackup/%F';
    4> restore controlfile from autobackup;
    5> }

    executing command: SET DBID

    executing command: SET CONTROLFILE AUTOBACKUP FORMAT

    Starting restore at 12-DEC-12
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=34 instance=primdb1 device type=DISK

    recovery area destination: +DATA
    database name (or database unique name) used for search: PRIMDB
    channel ORA_DISK_1: AUTOBACKUP +data/PRIMDB/AUTOBACKUP/2012_12_12/s_801851388.1110.801851393 found in the recovery area
    channel ORA_DISK_1: looking for AUTOBACKUP on day: 20121212
    channel ORA_DISK_1: restoring control file from AUTOBACKUP +data/PRIMDB/AUTOBACKUP/2012_12_12/s_801851388.1110.801851393
    channel ORA_DISK_1: control file restore from AUTOBACKUP complete
    output file name=+DATA/primdb/controlfile/current.260.801867061
    Finished restore at 12-DEC-12

    From a Copy

    RMAN> restore controlfile from '/tmp/primdb-20121212.ctl';

    Starting restore at 12-DEC-12
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=34 instance=primdb1 device type=DISK

    channel ORA_DISK_1: copied control file copy
    output file name=+DATA/compdb/controlfile/current.260.761506189
    Finished restore at 12-DEC-12

    From a Backup Piece

    If the backup set contains control file, you can use one of the backup pieces to restore control file.

    RMAN> restore controlfile from '/tmp/rman/backup_0atgl7q2_1_1';
  15. Check the existence of the restored controlfile physically.
  16. ASMCMD> ls -l
    Type         Redund  Striped  Time             Sys  Name
    CONTROLFILE  HIGH    FINE     DEC 12 21:00:00  Y    current.260.801867061

  17. Alter database to mount state for recovering database.
  18. RMAN> alter database mount;

    database mounted
    released channel: ORA_DISK_1

  19. Recover the database under mount state.
  20. RMAN> recover database;

    Starting recover at 12-DEC-12
    Starting implicit crosscheck backup at 12-DEC-12
    allocated channel: ORA_DISK_1
    Crosschecked 7 objects
    Finished implicit crosscheck backup at 12-DEC-12

    Starting implicit crosscheck copy at 12-DEC-12
    using channel ORA_DISK_1
    Crosschecked 6 objects
    Finished implicit crosscheck copy at 12-DEC-12

    searching for all files in the recovery area
    cataloging files...
    cataloging done

    List of Cataloged Files
    =======================
    File Name: +data/PRIMDB/AUTOBACKUP/2012_12_12/s_801851388.1110.801851393
    File Name: +data/PRIMDB/ARCHIVELOG/2012_10_29/thread_1_seq_329.836.797931679
    File Name: +data/PRIMDB/ARCHIVELOG/2012_10_29/thread_1_seq_330.837.797931687


    File Name: +data/PRIMDB/ARCHIVELOG/2012_10_29/thread_1_seq_331.838.797931781
    File Name: +data/PRIMDB/ARCHIVELOG/2012_10_29/thread_2_seq_314.839.797931981
    File Name: +data/PRIMDB/ARCHIVELOG/2012_10_29/thread_2_seq_315.840.797931993

    ...

    using channel ORA_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 89 is already on disk as file +DATA/primdb/onlinelog/group_1.903.797943769
    archived log file name=+DATA/primdb/onlinelog/group_1.903.797943769 thread=1 sequence=89
    media recovery complete, elapsed time: 00:00:01
    Finished recover at 12-DEC-12

  21. Open the database with resetlogs.
  22. Since this kind of recovery is an incomplete one, the database will need to reset logs.
    RMAN> alter database open resetlogs;

    database opened

  23. Check the database status.
  24. SQL> select inst_id, open_mode, database_role, switchover_status from gv$database;

       INST_ID OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
    ---------- -------------------- ---------------- --------------------
             1 READ WRITE           PRIMARY          FAILED DESTINATION


    SQL> select name from v$controlfile;

    NAME
    --------------------------------------------------------------------------------
    +DATA/primdb/controlfile/current.260.801867061
Of course, you can put all the commands together in a run block like this:
run {
set dbid 841830157;
set controlfile autobackup format for device type disk to '%F';
restore controlfile from autobackup;
alter database mount;
recover database;
alter database open resetlogs;
}

More related posts that you may be interested in:

Leave a Reply

Your email address will not be published. Required fields are marked *