{"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 logging of those backups) you can get everything back \ud83d\ude42 Lookup your DBID Lookup the autobackup file you want to use (probably the latest) Startup database nomount from a init.ora (spfile is also possible but […]<\/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-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}]}}