Restore and recover a database when the controlfiles are missing

That’s a little more complicated. The controlfile (nocatalog) is the place where oracle stores all information regarding the backups. So no controlfile …. no restore/recover you might think….

If there is only one thing you take with you from this article it will be: make sure you have the logging of your backups handy. All information you need is in this logging.

No….ofcourse Oracle thought of that scenario:

1. In sqlplus “shutdown abort” the database
2. In rman set dbid. One can find the DBID (for instance) in the logging of the backups you make.
3. Startup database in nomount
4. Restore controlfile from backuppiece. Again you can find which file is the autobackup for controlfile and spfile in the logging of the backups you make.
5. If you are feeling lucky…alter database open resetlogs done
6. Or else: restore database
7. recover database
8. alter database open resetlogs

I think it is advisble to restore to a point in time in cases like this. (You probably want to restore/recover to a point in time you are certain the database was OK.

Find out the DBID of your database:

[oracle@bloemkool log]$ head backup.20201024-143359.CDB01.full.log
The Oracle base remains unchanged with value /u01/app/oracle

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Oct 24 14:34:00 2020
Version 19.3.0.0.0

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

connected to target database: CDB01 (DBID=1590200411)
using target database control file instead of recovery catalog

[oracle@bloemkool log]$

Find out the autobackup file you need. In this case I just needed the last one made. So I tailed the log of the last backup I made:

[oracle@bloemkool log]$ tail  backup.20201024-143359.CDB01.full.log
Finished backup at 24-OCT-20

Starting Control File and SPFILE Autobackup at 24-OCT-20
piece handle=/u04/backups/bloemkool/c-1590200411-20201024-05 comment=NONE
Finished Control File and SPFILE Autobackup at 24-OCT-20

RMAN>

Recovery Manager complete.
[oracle@bloemkool log]$

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 24 15:07:48 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0



(2020/10/24 15:07:48) SYS@_DUN > shutdown abort;
ORACLE instance shut down.
(2020/10/24 15:07:58) SYS@_DUN > exit

RMAN> set DBID=1590200411;

executing command: SET DBID

RMAN> startup nomount;

Oracle instance started

Total System Global Area     620756648 bytes

Fixed Size                     9137832 bytes
Variable Size                339738624 bytes
Database Buffers             268435456 bytes
Redo Buffers                   3444736 bytes

RMAN>RMAN> restore controlfile from '/u04/backups/bloemkool/c-1590200411-20201024-05';

Starting restore at 24-OCT-20
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/multiplex/CDB01/controlfile/o1_mf_hqwfqvkb_.ctl
output file name=/u02/multiplex/CDB01/controlfile/o1_mf_hqwfqvp8_.ctl
output file name=/u03/multiplex/CDB01/controlfile/o1_mf_hqwfqvtq_.ctl
Finished restore at 24-OCT-20


RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> restore database;

Starting restore at 24-OCT-20
Starting implicit crosscheck backup at 24-OCT-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=437 device type=DISK
Crosschecked 134 objects
Finished implicit crosscheck backup at 24-OCT-20

Starting implicit crosscheck copy at 24-OCT-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 24-OCT-20

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

skipping datafile 5; already restored to file /u02/datafiles/CDB01/pdbseed/system01.dbf
skipping datafile 6; already restored to file /u02/datafiles/CDB01/pdbseed/sysaux01.dbf
skipping datafile 8; already restored to file /u02/datafiles/CDB01/pdbseed/undotbs01.dbf
skipping datafile 9; already restored to file /u02/datafiles/CDB01/PDB01/system01.dbf
skipping datafile 10; already restored to file /u02/datafiles/CDB01/PDB01/sysaux01.dbf
skipping datafile 11; already restored to file /u02/datafiles/CDB01/PDB01/undotbs01.dbf
skipping datafile 12; already restored to file /u02/datafiles/CDB01/PDB01/users01.dbf
skipping datafile 13; already restored to file /u02/datafiles/CDB01/PDB01/martijn01.dnf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/datafiles/CDB01/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/datafiles/CDB01/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/datafiles/CDB01/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u02/datafiles/CDB01/users01.dbf
channel ORA_DISK_1: reading from backup piece /u04/backups/bloemkool/uevdpcfd_1_1
channel ORA_DISK_1: piece handle=/u04/backups/bloemkool/uevdpcfd_1_1 tag=TAG20201024T143421
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 24-OCT-20

RMAN> recover database;

Starting recover at 24-OCT-20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u03/archiving/CDB01/1_5_1054648112.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/multiplex/CDB01/onlinelog/o1_mf_3_hqwfr6v0_.log
archived log for thread 1 with sequence 7 is already on disk as file /u01/multiplex/CDB01/onlinelog/o1_mf_1_hqwfqyf2_.log
archived log for thread 1 with sequence 8 is already on disk as file /u01/multiplex/CDB01/onlinelog/o1_mf_2_hqwfqyfx_.log
archived log file name=/u03/archiving/CDB01/1_5_1054648112.dbf thread=1 sequence=5
archived log file name=/u01/multiplex/CDB01/onlinelog/o1_mf_3_hqwfr6v0_.log thread=1 sequence=6
archived log file name=/u01/multiplex/CDB01/onlinelog/o1_mf_1_hqwfqyf2_.log thread=1 sequence=7
archived log file name=/u01/multiplex/CDB01/onlinelog/o1_mf_2_hqwfqyfx_.log thread=1 sequence=8
media recovery complete, elapsed time: 00:00:03
Finished recover at 24-OCT-20

RMAN>RMAN> alter database open resetlogs;

Statement processed

RMAN>