If you don't have access to the original server and all you have is the backup file, then you'll need to restore the headers from the backup file first to see what's inside it.
You can do this by:
create table #headers
(
BackupName nvarchar(128),
BackupDescription nvarchar(255),
BackupType smallint,
ExpirationDate datetime,
Compressed bit,
Position smallint,
DeviceType tinyint,
UserName nvarchar(128),
ServerName nvarchar(128),
DatabaseName nvarchar(128),
DatabaseVersion int,
DatabaseCreationDate datetime,
BackupSize numeric(20,0),
FirstLSN numeric(25,0),
LastLSN numeric(25,0),
CheckpointLSN numeric(25,0),
DatabaseBackupLSN numeric(25,0),
BackupStartDate datetime,
BackupFinishDate datetime,
SortOrder smallint,
[CodePage] smallint,
UnicodeLocaleId int,
UnicodeComparisonStyle int,
CompatibilityLevel tinyint,
SoftwareVendorId int,
SoftwareVersionMajor int,
SoftwareVersionMinor int,
SoftwareVersionBuild int,
MachineName nvarchar(128),
Flags int,
BindingID uniqueidentifier,
RecoveryForkID uniqueidentifier,
Collation nvarchar(128),
FamilyGUID uniqueidentifier,
HasBulkLoggedData bit,
IsSnapshot bit,
IsReadOnly bit,
IsSingleUser bit,
HasBackupChecksums bit,
IsDamaged bit,
BeginsLogChain bit,
HasIncompleteMetaData bit,
IsForceOffline bit,
IsCopyOnly bit,
FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0),
RecoveryModel nvarchar(60),
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
BackupTypeDescription nvarchar(60),
BackupSetGUID uniqueidentifier,
CompressedBackupSize bigint,
Containment tinyint
);
go
insert into #headers exec('restore headeronly from disk=''c:\DEV\UnitTest.bak''');
go
You now have a temporary table(#headers
) which contains details of all the backups contained within that device file.
To find the most recent full database backup we filter for only full db backups (backupType=1
) and then look for the most recent backup in the device, which will be the one with the largest LSN. This gives us the position value, which is used in the RESTORE
command to specify to SQL Server which backup we want to restore:
declare @filepos int;
select top 1 @filepos=Position from #headers where BackupType=1 order by lastlsn desc;
restore database unittest from Disk=N'c:\DEV\UnitTest.bak' with file=@filepos, recovery, replace;
As you say you will still have access to the original server, we can then query the tables in msdb that SQL Server uses to store this information.
The following queries are assuming you are running them in a SQL session on the original server. If you're not allowed to do that, then you'll need to look into using a linked server and 4 part naming.
There are 2 ways you can specify the set of backups you want to look at. One is via the path to the backup file, the other is by referencing the name on the backup device. I've included both in this query, with some notes about which lines to comment out depend on how you're doing it.
This will produce a RESTORE
statement which you can run in a seperate SSMS session. I've also included an example at the bottom of how you could automate this, though you'd want to do some testing beforehand to make sure it's doing what you want.
declare @filepos int, @dbname varchar(50), @devname varchar(50)
/*
dbname is the name of the database you want to restore. Need this, as it's possible for backups of more than one database to be present in the same backup file or device
devname is either the name of the backup device, or the path to the .bak file
*/
select @dbname='Unittest', @devname='c:\DEV\unittest.bak'
select @filepos=max(c.position)
from
msdb.dbo.backupmediafamily a
inner join msdb.dbo.backupmediaset b on a.media_set_id=b.media_set_id
inner join msdb.dbo.backupset c on b.media_set_id=c.media_set_id
where
a.physical_device_name=@devname --use this line if you know the path to the backup
--a.logical_device_name=@devname --this line if you only know a device name.
and c.type='D' and c.database_name=@dbname
--This will build the restore statement if you know the device name
select 'restore database ['+@dbname+'] from '+@devname+' with file='+cast(@filepos as varchar(4))+', recovery, replace'
--This will build the restore statement if you know the backup file path
select 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'
/*
This section only if feeling brave and confident, and you've checked the output previously, as it will execute whatever the generated restore command is, so this could cause a major problem if not checked first, or even overwrite production if run on the wrong box.
*/
declare @restore_cmd varchar(250)
select @restore = 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace'
exec(@restore)
TL;DR: Just supply the tag of the backup you want to restore the database from, for example restore database from tag 'INTERESTING_TAG';
DISCLAIMER
The solution provided here is based solely on my own experience, you use it on your own risk. I'm not liable for any damages (including data loss) caused by using this solution.
Also, do I always need to restore spfile and control file because I
did not create some additional backups (just ran command backup
database
)?
By default RMAN is configured to automatically back up control file and spfile after every successful backup and on every database structural change (for example, adding datafiles) which causes these changes to be reflected in control files. Thus after every successful database backup with backup database
, the spfile and control file will be automatically backed up.
You can determine if autobackup is enabled by issuing show controlfile autobackup;
in RMAN, and enable it saying configure controlfile autobackup on;
in RMAN.
Since you supplied the paths to the backup sets you want to restore your database from, I assume that you obtained them from your current control file using RMAN. It won't hurt to save this information and other information about your existing backups to a plain text file because you will restore one of the previous control files and chances are this information will be lost:
[oracle@oca ~]$ export NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'
[oracle@oca ~]$ rman target=/ log 'list_backup.txt'
RMAN> list backup;
RMAN> exit;
You'll need to restore your database AND control file(s), because the database datafile headers of every datafile should be in sync with control file(s), i. e. they should have the same System Change Number (SCN).
As I already said, you just need to supply the tag name to restore the database from a specific backup. You can determine which tags were assigned to the backups by you or by the system, the completion date and time of the backups, and other information about backups saying list backup
in RMAN (we already saved this info to the text file). Here's the output of list backup
in my sample installation:
RMAN> list backup;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
78 Full 300.26M DISK 00:01:40 10-APR-13 05:10:32
BP Key: 78 Status: AVAILABLE Compressed: YES Tag: TAG20130410T050852
Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/backupset/2013_04_10/o1_mf_nnndf_TAG20130410T050852_8pbc14yv_.bkp
List of Datafiles in backup set 78
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------ ----
1 Full 3985848 10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/system01.dbf
2 Full 3985848 10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/sysaux01.dbf
3 Full 3985848 10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/undotbs01.dbf
4 Full 3985848 10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/users01.dbf
5 Full 3985848 10-APR-13 04:52:40 /u01/app/oracle/oradata/ocaexam/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
79 Full 9.39M DISK 00:00:02 10-APR-13 05:10:39
BP Key: 79 Status: AVAILABLE Compressed: NO Tag: TAG20130410T051037
Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/autobackup/2013_04_10/812350360_8pbk.bkp
SPFILE Included: Modification time: 10-APR-13 04:55:39
SPFILE db_unique_name: OCAEXAM
Control File Included: Ckp SCN: 3985848 Ckp time: 10-APR-13 04:52:40
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
80 Full 300.30M DISK 00:01:39 10-APR-13 05:18:03
BP Key: 80 Status: AVAILABLE Compressed: YES Tag: DELETE_ME
Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/backupset/2013_04_10/o1_mf_nnndf_DELETE_ME_8pbch94j_.bkp
List of Datafiles in backup set 80
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------ ----
1 Full 3986589 10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/system01.dbf
2 Full 3986589 10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/sysaux01.dbf
3 Full 3986589 10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/undotbs01.dbf
4 Full 3986589 10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/users01.dbf
5 Full 3986589 10-APR-13 05:12:33 /u01/app/oracle/oradata/ocaexam/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ------------------
81 Full 9.39M DISK 00:00:01 10-APR-13 05:18:11
BP Key: 81 Status: AVAILABLE Compressed: NO Tag: TAG20130410T051810
Piece Name: /u01/app/oracle/fast_recovery_area/OCAEXAM/autobackup/2013_04_10/812351553_8pbm.bkp
SPFILE Included: Modification time: 10-APR-13 05:13:53
SPFILE db_unique_name: OCAEXAM
Control File Included: Ckp SCN: 3986589 Ckp time: 10-APR-13 05:12:33
From this output, you can see that the first backup was (automatically) assigned the tag TAG20130410T050852
, and that the control file and spfile autobackup follows immediately (check the Completion Time field). You can also see that I performed another database backup, and I manually assigned it the tag DELETE_ME
, and, of course, it's immediately followed by autobackup too. Notice also that the files in every backup have the same SCN, and that SCNs match the SCNs of the control files in the adjacent autobackups.
We will restore the database from the backup tagged TAG20130410T050852
which is older than the other backup tagged DELETE_ME
, and we will restore the control file from the autobackup first.
In order to restore the control file from backup with RMAN, your instance should be in NOMOUNT
state (only spfile is accessed in this state by the instance–control file(s) and datafiles are not accessed):
RMAN> shutdown immediate;
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
database mounted
(Though, you may use abort
clause instead of immediate
, and Oracle won't bother shutting down the database orderly–you'll restore it from the previous backup anyway.)
Restore the controlfile from autobackup:
RMAN> restore controlfile from
2> '/u01/app/oracle/fast_recovery_area/OCAEXAM/autobackup/2013_04_10/812350360_8pbk.bkp';
Starting restore at 10-APR-13 06:01:25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/ocaexam/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ocaexam/control02.ctl
Finished restore at 10-APR-13 06:01:27
In order for RMAN to be able to read backup records in control file (just like it did when we were issuing list backup
), we need to put the database in MOUNT
state:
RMAN> sql 'alter database mount';
We're now ready to restore the database:
RMAN> restore database from tag 'TAG20130410T050852';
The final step is to open the database:
RMAN> sql 'alter database open resetlogs';
We specified resetlogs
clause here because the existing redo log files are no longer usable since they were in use by the previous database, and thus should be reset so that they can be used by the restored database.
Now query for great good!
Best Answer
It depends entirely on the nature of the failure and the need of recovery. The ends-and-outs are too complex to put in a single answer here, but the Database Backup and Recovery User Guide has a whole section of scenarios on "Diagnosing and Responding to Failures". You would do well to study it. And build yourself a private VM under VirtualBox, to practice on.
Recovering a database is the Prime Directive of any DBA.