Solved

Restore Database from Oracle Enterprise manager

Posted on 2009-06-30
31
1,116 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?
0
Comment
Question by:PsychoDazey
  • 15
  • 15
31 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24751463
>>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.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24751479
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.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24751587
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.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24751677
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:
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24751777
From the Maintenance tab, click "Manage Current Backups" and tell me what you see listed.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24751787
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.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24751857
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;
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24751904
do i need to move the 6/30 files first?  I dont want those restored, just the 6/29 files.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24751919
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
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24751929
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;


0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24751952
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?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24751976
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
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24751988
Oops I left out quotes:

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

Author Comment

by:PsychoDazey
ID: 24752018
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
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24752046
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:PsychoDazey
ID: 24752050
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.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24752072
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.
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24752088
1) I would disable the autojob until you figure out what is going on.
2) make sure to keep it in ARCHIVELOG mode for the near future
3) Take a FULL backup of the database as it stands now, if it is open
4) Then consider doing point in time recovery to a clone to use exports to recover data

OR if you really don't care about any data you have today or yesterday, you can try restore/recover to a point in the past using the commands I gave you but with times of say sysdate - 7, etc. Trial and error.

But again, make sure to keep archivelog mode on, you don't want any archive gaps, and make sure to take an export and a full RMAN backup prior to any more work.

Good luck, I hope I am around in the morning to help. :)
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24752097
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. :)
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24752108
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.

0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24754098
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.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 24754338
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.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24755357
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.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24755430
>>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;

0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24755496
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
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24755564
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
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24755649
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;


0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24755985
to restore using the older files do I run:
RMAN> restore controlfile from 'path\name'
RMAN> restore database from 'path\name'?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24756032
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.
0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 24782516
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!
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24782519
>> 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.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now