{"id":122,"date":"2020-10-24T13:30:56","date_gmt":"2020-10-24T13:30:56","guid":{"rendered":"https:\/\/maboc.nl\/?p=122"},"modified":"2020-10-24T13:30:56","modified_gmt":"2020-10-24T13:30:56","slug":"restore-and-recover-a-database-when-the-controlfiles-are-missing","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=122","title":{"rendered":"Restore and recover a database when the controlfiles are missing"},"content":{"rendered":"\n

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….<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

No….ofcourse Oracle thought of that scenario:<\/p>\n\n\n\n

  1. In sqlplus “shutdown abort” the database<\/li>
  2. In rman set dbid. One can find the DBID (for instance) in the logging of the backups you make.<\/li>
  3. Startup database in nomount<\/li>
  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.<\/li>
  5. If you are feeling lucky…alter database open resetlogs done<\/li>
  6. Or else: restore database <\/li>
  7. recover database<\/li>
  8. alter database open resetlogs<\/li><\/ol>\n\n\n\n

    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.<\/p>\n\n\n\n

    Find out the DBID of your database:<\/p>\n\n\n\n

    [oracle@bloemkool log]$ head backup.20201024-143359.CDB01.full.log\r\nThe Oracle base remains unchanged with value \/u01\/app\/oracle\r\n\r\nRecovery Manager: Release 19.0.0.0.0 - Production on Sat Oct 24 14:34:00 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\r\n\r\nconnected to target database: CDB01 (DBID=1590200411)\r\nusing target database control file instead of recovery catalog\r\n\r\n[oracle@bloemkool log]$<\/code><\/pre>\n\n\n\n
    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:<\/p>\n\n\n\n
    [oracle@bloemkool log]$ tail  backup.20201024-143359.CDB01.full.log\r\nFinished backup at 24-OCT-20\r\n\r\nStarting Control File and SPFILE Autobackup at 24-OCT-20\r\npiece handle=\/u04\/backups\/bloemkool\/c-1590200411-20201024-05 comment=NONE\r\nFinished Control File and SPFILE Autobackup at 24-OCT-20\r\n\r\nRMAN>\r\n\r\nRecovery Manager complete.\r\n[oracle@bloemkool log]$<\/code><\/pre>\n\n\n\n
     <\/p>\n\n\n\n
    SQL*Plus: Release 19.0.0.0.0 - Production on Sat Oct 24 15:07:48 2020\r\nVersion 19.3.0.0.0\r\n\r\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\r\n\r\n\r\nConnected to:\r\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\r\nVersion 19.3.0.0.0\r\n\r\r\r\n(2020\/10\/24 15:07:48) SYS@_DUN > shutdown abort;\r\nORACLE instance shut down.\r\n(2020\/10\/24 15:07:58) SYS@_DUN > exit<\/code><\/pre>\n\n\n\n
    RMAN> set DBID=1590200411;\r\n\r\nexecuting command: SET DBID\r\n\r\nRMAN> startup nomount;\r\n\r\nOracle instance started\r\n\r\nTotal System Global Area     620756648 bytes\r\n\r\nFixed Size                     9137832 bytes\r\nVariable Size                339738624 bytes\r\nDatabase Buffers             268435456 bytes\r\nRedo Buffers                   3444736 bytes\r\n\r\nRMAN>RMAN> restore controlfile from '\/u04\/backups\/bloemkool\/c-1590200411-20201024-05';\r\n\r\nStarting restore at 24-OCT-20\r\nusing channel ORA_DISK_1\r\n\r\nchannel ORA_DISK_1: restoring control file\r\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\r\noutput file name=\/u01\/multiplex\/CDB01\/controlfile\/o1_mf_hqwfqvkb_.ctl\r\noutput file name=\/u02\/multiplex\/CDB01\/controlfile\/o1_mf_hqwfqvp8_.ctl\r\noutput file name=\/u03\/multiplex\/CDB01\/controlfile\/o1_mf_hqwfqvtq_.ctl\r\nFinished restore at 24-OCT-20\r\n\r\n\r\nRMAN> alter database mount;\r\n\r\nreleased channel: ORA_DISK_1\r\nStatement processed\r\n\r\nRMAN> restore database;\r\n\r\nStarting restore at 24-OCT-20\r\nStarting implicit crosscheck backup at 24-OCT-20\r\nallocated channel: ORA_DISK_1\r\nchannel ORA_DISK_1: SID=437 device type=DISK\r\nCrosschecked 134 objects\r\nFinished implicit crosscheck backup at 24-OCT-20\r\n\r\nStarting implicit crosscheck copy at 24-OCT-20\r\nusing channel ORA_DISK_1\r\nFinished implicit crosscheck copy at 24-OCT-20\r\n\r\nsearching for all files in the recovery area\r\ncataloging files...\r\nno files cataloged\r\n\r\nusing channel ORA_DISK_1\n\r\nskipping datafile 5; already restored to file \/u02\/datafiles\/CDB01\/pdbseed\/system01.dbf\r\nskipping datafile 6; already restored to file \/u02\/datafiles\/CDB01\/pdbseed\/sysaux01.dbf\r\nskipping datafile 8; already restored to file \/u02\/datafiles\/CDB01\/pdbseed\/undotbs01.dbf\r\nskipping datafile 9; already restored to file \/u02\/datafiles\/CDB01\/PDB01\/system01.dbf\r\nskipping datafile 10; already restored to file \/u02\/datafiles\/CDB01\/PDB01\/sysaux01.dbf\r\nskipping datafile 11; already restored to file \/u02\/datafiles\/CDB01\/PDB01\/undotbs01.dbf\r\nskipping datafile 12; already restored to file \/u02\/datafiles\/CDB01\/PDB01\/users01.dbf\r\nskipping datafile 13; already restored to file \/u02\/datafiles\/CDB01\/PDB01\/martijn01.dnf\r\nchannel ORA_DISK_1: starting datafile backup set restore\r\nchannel ORA_DISK_1: specifying datafile(s) to restore from backup set\r\nchannel ORA_DISK_1: restoring datafile 00001 to \/u02\/datafiles\/CDB01\/system01.dbf\r\nchannel ORA_DISK_1: restoring datafile 00003 to \/u02\/datafiles\/CDB01\/sysaux01.dbf\r\nchannel ORA_DISK_1: restoring datafile 00004 to \/u02\/datafiles\/CDB01\/undotbs01.dbf\r\nchannel ORA_DISK_1: restoring datafile 00007 to \/u02\/datafiles\/CDB01\/users01.dbf\r\nchannel ORA_DISK_1: reading from backup piece \/u04\/backups\/bloemkool\/uevdpcfd_1_1\r\nchannel ORA_DISK_1: piece handle=\/u04\/backups\/bloemkool\/uevdpcfd_1_1 tag=TAG20201024T143421\r\nchannel ORA_DISK_1: restored backup piece 1\r\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:26\r\nFinished restore at 24-OCT-20\r\n\r\nRMAN> recover database;\r\n\r\nStarting recover at 24-OCT-20\r\nusing channel ORA_DISK_1\r\n\r\nstarting media recovery\r\n\r\narchived log for thread 1 with sequence 5 is already on disk as file \/u03\/archiving\/CDB01\/1_5_1054648112.dbf\r\narchived log for thread 1 with sequence 6 is already on disk as file \/u01\/multiplex\/CDB01\/onlinelog\/o1_mf_3_hqwfr6v0_.log\r\narchived log for thread 1 with sequence 7 is already on disk as file \/u01\/multiplex\/CDB01\/onlinelog\/o1_mf_1_hqwfqyf2_.log\r\narchived log for thread 1 with sequence 8 is already on disk as file \/u01\/multiplex\/CDB01\/onlinelog\/o1_mf_2_hqwfqyfx_.log\r\narchived log file name=\/u03\/archiving\/CDB01\/1_5_1054648112.dbf thread=1 sequence=5\r\narchived log file name=\/u01\/multiplex\/CDB01\/onlinelog\/o1_mf_3_hqwfr6v0_.log thread=1 sequence=6\r\narchived log file name=\/u01\/multiplex\/CDB01\/onlinelog\/o1_mf_1_hqwfqyf2_.log thread=1 sequence=7\r\narchived log file name=\/u01\/multiplex\/CDB01\/onlinelog\/o1_mf_2_hqwfqyfx_.log thread=1 sequence=8\r\nmedia recovery complete, elapsed time: 00:00:03\r\nFinished recover at 24-OCT-20\r\n\r\nRMAN>RMAN> alter database open resetlogs;\r\n\r\nStatement processed\r\n\r\nRMAN><\/code><\/pre>\n\n\n\n
    <\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,20],"tags":[24,25,22,26],"class_list":["post-122","post","type-post","status-publish","format-standard","hentry","category-oracle","category-rman","tag-oracle","tag-recover","tag-restore","tag-rman"],"_links":{"self":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/122","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=122"}],"version-history":[{"count":1,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/122\/revisions"}],"predecessor-version":[{"id":123,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/122\/revisions\/123"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=122"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=122"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=122"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}