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:
- In sqlplus “shutdown abort” the database
- In rman set dbid. One can find the DBID (for instance) in the logging of the backups you make.
- Startup database in nomount
- 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.
- If you are feeling lucky…alter database open resetlogs done
- Or else: restore database
- recover database
- 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 22.214.171.124.0 - Production on Sat Oct 24 14:34:00 2020 Version 126.96.36.199.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 188.8.131.52.0 - Production on Sat Oct 24 15:07:48 2020 Version 184.108.40.206.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 220.127.116.11.0 - Production Version 18.104.22.168.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>