We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Restore Database from Oracle Enterprise manager

PsychoDazey
PsychoDazey asked
on
Medium Priority
1,933 Views
Last Modified: 2013-12-19
Hi experts -
I use Oracle Enterprise Manager DB Control to schedule my backups.  I have 2 backups, one from today and one from yesterday.  I want to recover to a point in time from yesterdays backup.  I was NOT running in Archive Log mode.  
1) How do I restore the database using the previous days backup?  I am not sure what the command is to point to a specific backup (.bak) file is.
2) Can I recover to a point in time if I am not running in Archivelog mode?
Comment
Watch Question

Top Expert 2009

Commented:
>>1) How do I restore the database using the previous days backup?  I am not sure what the command is to point to a specific backup (.bak) file is.

If you are already using Enterprise Manager, go to Availability Tab, then choose Perform Recovery.
The first operation type "Recover to the current time or a previous point-in-time" should select the latest usable backup for you.


>>2) Can I recover to a point in time if I am not running in Archivelog mode?

No. You can only recover to the last consistent backup. You will lose all data since then. If you have data that is exportable, do so prior to doing the restore/recovery.

Author

Commented:
thanks mrjoltcola -
I am not sure what you mean by availability tab.  I have - Home, Performance, Administration and Maintenance.  Also, if I just let Enterprise manager control which file to restore from, wont it always choose the latest?  I am trying to restore to the previous file, not the last.

Appreciate the assist.
Top Expert 2009

Commented:
My apologies! I gave you 11g directions, I was logged onto the wrong instance when I described it.

Use Maintenance Tab -> Perform Recovery

Then the options are the same as I described.

Author

Commented:
I did the restore, but I get this error when trying to open:
ALTER DATABASE open  RESETLOGS
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
Top Expert 2009

Commented:
From the Maintenance tab, click "Manage Current Backups" and tell me what you see listed.

Author

Commented:
I have 4 backups listed; 1 controlfile from 6/29, 1 datafile from 6/29, 1 controlfile from 6/30, 1 datafile from 6/30.
Top Expert 2009

Commented:
I'm not very comfortable using Enterprise Manager for recovery so I am not 100% on the advice.

You can try "Restore all datafiles", but I since you said you are not in archivelog mode, it may require shutdown of the instance.

If you are willing to try through RMAN from the command line I think I can help better.

If this was in fact a cold backup as you claim:

rman target /
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> restore database;
RMAN> alter database open resetlogs;

Author

Commented:
do i need to move the 6/30 files first?  I dont want those restored, just the 6/29 files.

Author

Commented:
it ran but it wouldn't let me do a resetlogs:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

Do I need to recover first?  I tried opening without resetlogs and got this error:
ORA-01113: file 1 needs media recovery
Top Expert 2009

Commented:
Are you SURE you are running in noarchivelog mode?

SQL> select log_mode from v$database;


It sounds like you took a hot backup.

Try:

RMAN> recover database;


Author

Commented:
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

I tried recovering and it keeps looking for an archivelog.  Is it possible archive logging was turned on and then back off at some point and thats what happened?
Top Expert 2009

Commented:
I think so.

1) If you were really in noarchivelog mode, you would NOT have been able to take a hot backup. RMAN / EM will not allow it.

2) If you have cold backups, then you would not have received the ORA-01113 error above and you could have just done "alter database open noresetlogs;"

I am really not sure what your configuration is, it is a bit confusing.

To restore the older files you can either restore from the specific tag or use until time or scn

RMAN> restore database until time to_date('29-JUN-2009 12:00', 'DD-MON-YYYY HH:MI');

But make sure to pick a time that is prior to the JUN 30 backup, or you'll get the latest.

I really need to also see the full messages from RMAN
Top Expert 2009

Commented:
Oops I left out quotes:

RMAN> restore database until time "to_date('29-JUN-2009 12:00', 'DD-MON-YYYY HH:MI')";

Author

Commented:
here are the rman messages.
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/01/2009 02:50:25
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\COGNOS8\DATAFILE\O1_MF_SYSTEM_35CS523C_.DBF'

RMAN> recover database;

Starting recover at 01-JUL-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2917 is already on disk as file I:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA
54OKWVSP_.ARC
unable to find archive log
archive log thread=1 sequence=2908
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/01/2009 02:50:35
RMAN-06054: media recovery requesting unknown log: thread 1 seq 2908 lowscn 70537852
Top Expert 2009

Commented:
What happened to your archive logs?

Why would you have switched your database to noarchivelog mode and then tried to restore like this?

I really do not understand the situation. It is highly possible your only option may be to force the database open, which will de-support your database and require a complete rebuild.

Do you have an export file, or do you have a later cold backup?

If you are running in NOARCHIVELOG mode you MUST keep cold backups.
If you are running IN ARCHIVELOG mode, you MUST keep your archive logs.

Do you have active Oracle support?

Author

Commented:
Ugh, it just shut down to do a backup again.  I am wondering if someone changed the database I am restoring from to enable archivelog, then when I applied the backup file it got confused because the backup is noarchivelog?  At any rate, I need to get some sleep, its 3 am here.  I appreciate your help, I think after a few hours sleep I will figure this out.

Author

Commented:
I wasnt the one that did this, I just have to clean up the mess.  Someone that isn't in IT got a hold of the sys password and tried to "fix" a problem they were having.

What will happen if I force open?  if it totally hoses the database can I shut it down and restore again?
I do have backups going back about 2 months.  I have the database at a point where it opens right now, but there are data and schema changes that don't belong in there.

Thanks again.
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Top Expert 2009

Commented:
PS: I can relate to fixing other folks messes, like you are doing now; no sweat mate, that's part of the job. Change that sys password and tell the guy to keep his grubby paws off your DB. :)
Top Expert 2009

Commented:
Here are some snippets to help you figure out whether you have an archivelog gap.

-- Find out when the database was opened with resetlogs.
select distinct resetlogs_time from v$archived_log;

-- List your archive log history in order
select sequence#, first_change#, name from v$archived_log order by first_change#;

Then compare that list to the archive directory see whether you have gaps or not. You may have to recover prior to the gap, if there is any, you surely cannot recover through a log gap, so you'll either have to pick a hot backup on either side of the gap to restore from to get what you want.

Author

Commented:
Thanks for all the input.  Here is what I have when I runn the 2 select statements above:
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> select distinct resetlogs_time from v$archived_log;

no rows selected

SQL> select sequence#, first_change#, name from v$archived_log order by first_change#;

no rows selected

SQL>

I think I am going to try restoring to an older backup.  Not sure what else I can do at this point aside from force open.  I am not too concerned about data loss.
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Did you restore the control file as well as the backup?  That could be your issue.  If the control file is out of sync with the data files, it can think that you data files need recovery because the control file is newer.

Author

Commented:
no, the last round I did not restore the control file.  What is the command in RMAN to do that?  I have to say, RMAN is alot easier to use than the enterprise manager interface.
Top Expert 2009

Commented:
>>I have to say, RMAN is alot easier to use than the enterprise manager interface.

Thank you! I've been telling people that for a long time.


The fact that you show no archivelog history in the controlfile tells me that controlfile was either re-created, or it came from a database not in archivelog mode.

The problem here in telling you what to do is I've no idea what your situation is, even after last night's fun. If you want to go the RMAN route, then lets see the backups you have available.

Lets see what we see in RMAN

RMAN> list incarnation;

RMAN> list backup;

Author

Commented:
Some people are scared of the whole dos prompt thing.  That UI is real cumbersome to me!

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       COGNOS8  1271381767       PARENT  1          29-OCT-05
2       2       COGNOS8  1271381767       CURRENT 518852     24-MAY-07

Author

Commented:
My 6/29 backup is gone now, but I have it on tape.  I also have a backup from 6/10 that doesn't show up in my list but I can see it...I was planning on restoring from there.



List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
403     Full    184.89M    DISK        00:00:48     30-JUN-09
        BP Key: 403   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_COGNOS8_000_063009010013
        Piece Name: I:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\COGNOS8\BACKUPSET\2009_06_30\O1_MF_NNNDF_BACKUP_COGNOS8_000_0_54M71K0Y_.BKP
  List of Datafiles in backup set 403
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 70718666   30-JUN-09 E:\ORACLE\PRODUCT\10.2.0\ORADATA\COGNOS8\DATAFILE\O1_MF_SYSTEM_35CS523C_.DBF
  2       Full 70718666   30-JUN-09 E:\ORACLE\PRODUCT\10.2.0\ORADATA\COGNOS8\DATAFILE\O1_MF_UNDOTBS1_35CS5259_.DBF
  3       Full 70718666   30-JUN-09 E:\ORACLE\PRODUCT\10.2.0\ORADATA\COGNOS8\DATAFILE\O1_MF_SYSAUX_35CS523T_.DBF
  4       Full 70718666   30-JUN-09 E:\ORACLE\PRODUCT\10.2.0\ORADATA\COGNOS8\DATAFILE\O1_MF_USERS_35CS5259_.DBF
  5       Full 70718666   30-JUN-09 H:\COGNOS8\DATAFILE\SYSAUX02.DBF
  6       Full 70718666   30-JUN-09 H:\COGNOS8\DATAFILE\SYSTEM02.DBF
  7       Full 70718666   30-JUN-09 H:\COGNOS8\DATAFILE\USERS02.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
404     Full    1.11M      DISK        00:00:02     30-JUN-09
        BP Key: 404   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_COGNOS8_000_063009010013
        Piece Name: I:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\COGNOS8\BACKUPSET\2009_06_30\O1_MF_NCSNF_BACKUP_COGNOS8_000_0_54M739Y6_.BKP
  Control File Included: Ckp SCN: 70718666     Ckp time: 30-JUN-09
  SPFILE Included: Modification time: 30-JUN-09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
405     Full    1.11M      DISK        00:00:02     01-JUL-09
        BP Key: 405   Status: AVAILABLE  Compressed: YES  Tag: BACKUP_COGNOS8_000_070109025803
        Piece Name: I:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\COGNOS8\BACKUPSET\2009_07_01\O1_MF_NCSNF_BACKUP_COGNOS8_000_0_54P5S2NC_.BKP
  Control File Included: Ckp SCN: 70781506     Ckp time: 01-JUL-09
  SPFILE Included: Modification time: 01-JUL-09
Top Expert 2009

Commented:
Sounds good.

A couple of more closing recommendations:

Set your retention level high. The backupsize is only 185m, you have space for many days of backup I hope. Let RMAN manage your backup "deletion" by using the retention period.

To see the RMAN config, issue:

RMAN> SHOW ALL;


On many of my databases, I want ensured 15-30 backups at least, so I use REDUNDANCY option, but you can also use the RECOVERY WINDOW option to specify in days.,

This will ensure RMAN will preserve 15 backups.

RMAN> configure retention policy to redundancy 15;

OR for 30 days of window, regardless of number of backups:

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;


Author

Commented:
to restore using the older files do I run:
RMAN> restore controlfile from 'path\name'
RMAN> restore database from 'path\name'?
Top Expert 2009

Commented:
Its probably best to use the "UNTIL TIME" syntax

RMAN> restore controlfile from autobackup until time 'sysdate - 7';

OR

use the to_date() syntax I showed you above.

This will let Oracle choose the best one based on your time criteria. The UNTIL TIME syntax actually means "get the newest one that is at least as old as the time specificied" and it is usually used for point-in-time recovery.

Same goes for the restore database command.

Author

Commented:
sorry, i meant to close this out last week.  I appreciate the help, I got the database restored.  
I ended up having to copy all of the backups after the one I wanted to use to a temp directory so RMAN didnt see them anymore.  Then I ran:
RMAN> restore controlfile from 'path\name'
RMAN> restore database

I have some cleaning up to do in my backups it appears!  I have to figure out how to use RMAN for automatic backups instead of OEM!
Top Expert 2009

Commented:
>> I have to figure out how to use RMAN for automatic backups instead of OEM!

OEM actually uses RMAN to do the backups. The scheduling portion is done via the OEM / DBMS scheduler, but the backup script is an RMAN script. You can see it by drilling down in OEM.

Good luck.

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.