Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I just started working with oracle and I have some issue. My database is NONARCHIVELOG mode. I made two backups: they are at locations:

/opt/oracle/flash_recovery_area/CTGINST1/backupset/ol_mf_____20130408.bkp

/opt/oracle/flash_recovery_area/CTGINST1/backupset/ol_mf_____20130407.bkp

I want to restore second file from 7th April. I read some guide on: http://orafusion.com/art_rman3.htm

but I can not find command how to specify restore command with specific backup files from these two (or more). It does not say on this link and on other links I read. I want to use it with RMAN commands. what will be the commands to restore my older backup?

I found

RMAN> restore database;

but how it will know what file to restore? Also does it always need to restore spfile and control file because I did not create some additional backups (just ran command backup database)

Thank you!!

share|improve this question
    
Can you star you database? Can you connect to it with RMAN? What show controlfile autobackup; outputs when you connect to the database with RMAN? –  Yasir Arsanukaev Apr 10 '13 at 5:32

1 Answer 1

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!

share|improve this answer
    
I follow your steps but when I tried to open the database with resetlogs I got this error ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: 'F:\ORACLE\ORADATA\SYSTEM01.DBF' Thanks for your help UH –  www Hhh Oct 30 at 1:54
    
Probably your database is in ARCHIVELOG mode and the backups you're restoring from was taken online (hot backup), and there are archive logs which need to be applied to the data files you restored to make them consistent with your current control file. This is called the roll forward phase. Or you have differential database backups which should also be applied to the restored data files. In either case, you need to say RECOVER DATABASE just after RESTORE DATABASE, to apply all the changes made to the database since the last backup, only then you can open with RESETLOGS. –  Yasir Arsanukaev Oct 31 at 0:12
    
Also notice that in my example we made sure that the control file is sufficiently old, otherwise Oracle would attempt to recover the data files up to and including the latest change in database which is probably not what you want. So first, restore the sufficiently old control file (possibly from the controlfile autobackup), then place your database, archive log and differential backup files to Recovery Area on disk and say CATALOG RECOVERY AREA, so that backups are added to RMAN repository (control file), and, finally, RESTORE DATABASE, RECOVER DATABASE, ALTER DATABASE OPEN RESETLOGS. –  Yasir Arsanukaev Oct 31 at 0:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.