2020-10-27
Oracle, rman
No Comments

Restore and recover a database when everything is gone

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 🙂

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

Find the DBID in the logging of your backups or in filename the AutoBackup file.

Loggin:

[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)
using target database control file instead of recovery catalog

[oracle@bloemkool log]$

 

Or have a look at the name of the autobackup file:

[oracle@bloemkool bloemkool]$ ls -altr | tail -10
-rw-r----- 1 nobody nobody 27623424 Oct 27 11:30 c-1590200411-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-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-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-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-20201027-10
[oracle@bloemkool bloemkool]$

Now we allready see what is the latest Autobackup file :  c-1590200411-20201027-10

To be sure we also take a look at the backup logging:

[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 comment=NONE
Finished Control File and SPFILE Autobackup at 27-OCT-20

RMAN>

Recovery Manager complete.
[oracle@bloemkool log]$

We now have what we need.

Let’s startup the database in nomount mode. Starting up from an (old)  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.

Startup the database in nomount(this can conviniently be done from the RMAN Command Line):

[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>

Set The DBID we found earlier:


RMAN> set dbid=1590200411

executing command: SET DBID

RMAN>

Restore the spfile from the Autobackup file we found:

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>

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)

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>

Restore the controlfile(s) from the Autobackup file:

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>

Now restart the datbase in mount mode. We now have a controlfile, all backup information resides in the controlfiles:

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>

Restore the database:

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>

Recover the database. For brevity a lot of the output is skipped:

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>

 

Let’s open the DB up

RMAN> alter database open resetlogs;

Statement processed

RMAN>

 

Yes…we got it working again!!!!

 

Tags: , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *