{"id":127,"date":"2020-10-27T15:25:18","date_gmt":"2020-10-27T15:25:18","guid":{"rendered":"https:\/\/maboc.nl\/?p=127"},"modified":"2020-10-27T15:25:18","modified_gmt":"2020-10-27T15:25:18","slug":"restore-and-recover-a-database-when-everything-is-gone","status":"publish","type":"post","link":"https:\/\/maboc.nl\/?p=127","title":{"rendered":"Restore and recover a database when everything is gone"},"content":{"rendered":"\r\n

Today someone deleted everything: datafiles gone, controlfile gone, spfile gone. What to do?…..not to worry….if you have backups (and the logging of those backups) you can get everything back \ud83d\ude42<\/p>\r\n

    \r\n
  1. Lookup your DBID<\/li>\r\n
  2. Lookup the autobackup file you want to use (probably the latest)<\/li>\r\n
  3. Startup database nomount from a init.ora (spfile is also possible but involves extra work)<\/li>\r\n
  4. Restore spfile from autobackup<\/li>\r\n
  5. Restart the database nomount<\/li>\r\n
  6. Restore controlfiles from autobackup<\/li>\r\n
  7. Restart database mount<\/li>\r\n
  8. Restore database<\/li>\r\n
  9. Recover database<\/li>\r\n
  10. Alter database open resetlogs<\/li>\r\n<\/ol>\r\n

    Find the DBID in the logging of your backups or in filename the AutoBackup file.<\/p>\r\n

    Loggin:<\/p>\r\n

    [oracle@bloemkool log]$ head backup.20201027-153002.CDB01.arch.log 
    The Oracle base has been set to \/u01\/app\/oracle

    Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 27 15:30:02 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<\/span>)
    using target database control file instead of recovery catalog

    [oracle@bloemkool log]$<\/pre>\r\n
     <\/p>\r\n
    Or have a look at the name of the autobackup file:<\/p>\r\n
    [oracle@bloemkool bloemkool]$ ls -altr | tail -10
    -rw-r----- 1 nobody nobody 27623424 Oct 27 11:30 c-1590200411<\/span>-20201027-0c
    -rw-r----- 1 nobody nobody 7008768 Oct 27 12:30 7lve12ah_1_1
    -rw-r----- 1 nobody nobody 27623424 Oct 27 12:30 c-1590200411<\/span>-20201027-0d
    -rw-r----- 1 nobody nobody 7062016 Oct 27 13:30 7nve15r3_1_1
    -rw-r----- 1 nobody nobody 27623424 Oct 27 13:30 c-1590200411<\/span>-20201027-0e
    -rw-r----- 1 nobody nobody 7263744 Oct 27 14:30 7pve19bi_1_1
    -rw-r----- 1 nobody nobody 27623424 Oct 27 14:30 c-1590200411<\/span>-20201027-0f
    -rw-r----- 1 nobody nobody 7227904 Oct 27 15:30 7rve1cs4_1_1
    drwxrwxrwx 2 nobody nobody 12288 Oct 27 15:30 .
    -rw-r----- 1 nobody nobody 27623424 Oct 27 15:30 c-1590200411<\/span>-20201027-10
    [oracle@bloemkool bloemkool]$<\/pre>\r\n
    Now we allready see what is the latest Autobackup file :\u00a0 c-1590200411-20201027-10<\/p>\r\n
    To be sure we also take a look at the backup logging:<\/p>\r\n
    [oracle@bloemkool log]$ tail backup.20201027-153002.CDB01.arch.log
    archived log file name=\/u03\/archiving\/CDB01\/1_136_1054737965.dbf RECID=4535 STAMP=1054913412
    Finished backup at 27-OCT-20

    Starting Control File and SPFILE Autobackup at 27-OCT-20
    piece handle=\/u04\/backups\/bloemkool\/c-1590200411-20201027-10<\/span> comment=NONE
    Finished Control File and SPFILE Autobackup at 27-OCT-20

    RMAN>

    Recovery Manager complete.
    [oracle@bloemkool log]$<\/pre>\r\n
    We now have what we need.<\/p>\r\n
    Let’s startup the database in nomount mode. Starting up from an (old)\u00a0 spfile is certainly possible. However…if you startup from an spfile, RMAN will complain that it can not replace a spfile if the database started up from that file. The easiest way is starting from an init.ora which probably jusr resides in your $ORACLE_HOME\/dbs.<\/p>\r\n
    Startup the database in nomount(this can conviniently be done from the RMAN Command Line):<\/p>\r\n
    [oracle@bloemkool dbs]$ rman target \/ nocatalog

    Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 27 15:55:17 2020
    Version 19.3.0.0.0

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

    connected to target database (not started)

    RMAN> startup force nomount;

    Oracle instance started

    Total System Global Area 620756648 bytes

    Fixed Size 9137832 bytes
    Variable Size 281018368 bytes
    Database Buffers 327155712 bytes
    Redo Buffers 3444736 bytes
    RMAN>

    <\/pre>\r\n
    Set The DBID we found earlier:<\/p>\r\n

    RMAN> set dbid=1590200411

    executing command: SET DBID

    RMAN> <\/pre>\r\n
    Restore the spfile from the Autobackup file we found:<\/p>\r\n
    RMAN> restore spfile from '\/u04\/backups\/bloemkool\/c-1590200411-20201027-10';

    Starting restore at 27-OCT-20
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=427 device type=DISK

    channel ORA_DISK_1: restoring spfile from AUTOBACKUP \/u04\/backups\/bloemkool\/c-1590200411-20201027-10
    channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
    Finished restore at 27-OCT-20

    RMAN><\/pre>\r\n
    Startup the database again in nomount (however the spfile is back…so we now have some initialisation done. For example …. the location of the control-files)<\/p>\r\n
    RMAN> startup force nomount;

    Oracle instance started

    Total System Global Area 620756648 bytes

    Fixed Size 9137832 bytes
    Variable Size 184549376 bytes
    Database Buffers 423624704 bytes
    Redo Buffers 3444736 bytes

    RMAN><\/pre>\r\n
    Restore the controlfile(s) from the Autobackup file:<\/p>\r\n
    RMAN> restore controlfile from '\/u04\/backups\/bloemkool\/c-1590200411-20201027-10';

    Starting restore at 27-OCT-20
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=427 device type=DISK

    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 27-OCT-20

    RMAN><\/pre>\r\n
    Now restart the datbase in mount mode. We now have a controlfile, all backup information resides in the controlfiles:<\/p>\r\n
    RMAN> startup force mount;

    Oracle instance started
    database mounted

    Total System Global Area 620756648 bytes

    Fixed Size 9137832 bytes
    Variable Size 192937984 bytes
    Database Buffers 415236096 bytes
    Redo Buffers 3444736 bytes

    RMAN> <\/pre>\r\n
    Restore the database:<\/p>\r\n
    RMAN> restore database;

    Starting restore at 27-OCT-20
    Starting implicit crosscheck backup at 27-OCT-20
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=431 device type=DISK
    Crosschecked 143 objects
    Finished implicit crosscheck backup at 27-OCT-20

    Starting implicit crosscheck copy at 27-OCT-20
    using channel ORA_DISK_1
    Crosschecked 3 objects
    Finished implicit crosscheck copy at 27-OCT-20

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

    using channel ORA_DISK_1

    datafile 5 is already restored to file \/u02\/datafiles\/CDB01\/pdbseed\/system01.dbf
    datafile 6 is already restored to file \/u02\/datafiles\/CDB01\/pdbseed\/sysaux01.dbf
    datafile 8 is already restored to file \/u02\/datafiles\/CDB01\/pdbseed\/undotbs01.dbf
    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\/6ovdvmce_1_1
    channel ORA_DISK_1: piece handle=\/u04\/backups\/bloemkool\/6ovdvmce_1_1 tag=TAG20201027T000014
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
    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 00009 to \/u02\/datafiles\/CDB01\/PDB01\/system01.dbf
    channel ORA_DISK_1: restoring datafile 00010 to \/u02\/datafiles\/CDB01\/PDB01\/sysaux01.dbf
    channel ORA_DISK_1: restoring datafile 00011 to \/u02\/datafiles\/CDB01\/PDB01\/undotbs01.dbf
    channel ORA_DISK_1: restoring datafile 00012 to \/u02\/datafiles\/CDB01\/PDB01\/users01.dbf
    channel ORA_DISK_1: restoring datafile 00013 to \/u02\/datafiles\/CDB01\/PDB01\/martijn01.dnf
    channel ORA_DISK_1: reading from backup piece \/u04\/backups\/bloemkool\/6pvdvme0_1_1
    channel ORA_DISK_1: piece handle=\/u04\/backups\/bloemkool\/6pvdvme0_1_1 tag=TAG20201027T000014
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
    Finished restore at 27-OCT-20

    RMAN><\/pre>\r\n
    Recover the database. For brevity a lot of the output is skipped:<\/p>\r\n
    RMAN> recover database;


    Starting recover at 27-OCT-20
    using channel ORA_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 135 is already on disk as file \/u01\/multiplex\/CDB01_BROCCOLI\/onlinelog\/o1_mf_2_hsc0dxz4_.log
    archived log for thread 1 with sequence 136 is already on disk as file \/u01\/multiplex\/CDB01_BROCCOLI\/onlinelog\/o1_mf_3_hsc0f0hp_.log
    archived log for thread 1 with sequence 137 is already on disk as file \/u01\/multiplex\/CDB01_BROCCOLI\/onlinelog\/o1_mf_1_hsc0dxz4_.log
    channel ORA_DISK_1: starting archived log restore to default destination
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=102
    channel ORA_DISK_1: reading from backup piece \/u04\/backups\/bloemkool\/6rvdvmfd_1_1
    channel ORA_DISK_1: piece handle=\/u04\/backups\/bloemkool\/6rvdvmfd_1_1 tag=TAG20201027T000149
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    ...

    ...

    ...

    media recovery complete, elapsed time: 00:00:02
    Finished recover at 27-OCT-20

    RMAN><\/pre>\r\n
     <\/p>\r\n
    Let’s open the DB up<\/p>\r\n
    RMAN> alter database open resetlogs;

    Statement processed

    RMAN><\/pre>\r\n
     <\/p>\r\n
    Yes…we got it working again!!!!<\/p>\r\n
     <\/p>\r\n","protected":false},"excerpt":{"rendered":"
    Today someone deleted everything: datafiles gone, controlfile gone, spfile gone. What to do?…..not to worry….if you have backups (and the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[5,20],"tags":[24,25,22,26],"class_list":["post-127","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\/127","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=127"}],"version-history":[{"count":11,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/127\/revisions"}],"predecessor-version":[{"id":151,"href":"https:\/\/maboc.nl\/index.php?rest_route=\/wp\/v2\/posts\/127\/revisions\/151"}],"wp:attachment":[{"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/maboc.nl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}