Recovery scenario

From: Ram Raman <veeeraman_at_gmail.com>
Date: Wed, 30 Jan 2008 17:24:32 -0600
Message-ID: <effc058d0801301524q657816efqb2b50c9f09f21168@mail.gmail.com>

All,

We have an Oracle 10g prod database that is being backed up every night just past midnight using user managed backup method. I am trying to restore the files to a test server and create a new instance in the test server. The full backup started at 1240am at log sequence 11592 ended at 1:23am at sequence 11593. (See attachment of alert.log BELOW)

I recreated the the control file with new names for database, datafiles, redo logs and arch logs, etc on the test server and mounted the database. While trying to open this is what happened:

16:49:56 SQL> select instance_name, status 16:50:02 2 from v$instance;
more..

INSTANCE_NAME STATUS

---------------- ------------
HRDEV            MOUNTED

16:50:06 SQL> recover database using backup controlfile; ORA-00279: change 11805525061 generated at 01/30/2008 00:40:01 needed for thread 1
ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11592_620315068.dbf ORA-00280: change 11805525061 for thread 1 is in sequence #*11592*

16:50:26 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 11805558903 generated at 01/30/2008 01:23:32 needed for thread 1

ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11593_620315068.dbf
ORA-00280: change 11805558903 for thread 1 is in sequence #11593
ORA-00278: log file '/thrundo/oradata/HRDEV/arch/1_*11592_620315068.dbf' no

longer*
needed for this recovery

16:50:41 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel
Media recovery cancelled.
16:50:52 SQL> *alter database open resetlogs;
*alter database open resetlogs
*

ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/thrsys/oradata/HRDEV/system01.dbf'

16:54:26 SQL> *recover database using backup controlfile*; ORA-00279: change 11805558903 generated at 01/30/2008 01:23:32 needed for thread 1
ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11593_620315068.dbf ORA-00280: change 11805558903 for thread 1 is in sequence #11593

16:54:31 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 11805815756 generated at 01/30/2008 05:00:15 needed for thread 1

ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11594_620315068.dbf
ORA-00280: change 11805815756 for thread 1 is in sequence #11594
ORA-00278: log file '/thrundo/oradata/HRDEV/arch/*1_11593_620315068.dbf' no

longer*
needed for this recovery

16:54:35 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: change 11805870839 generated at 01/30/2008 05:32:08 needed for thread 1

ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11595_620315068.dbf
ORA-00280: change 11805870839 for thread 1 is in sequence #11595
ORA-00278: log file '/thrundo/oradata/HRDEV/arch/*1_11594_620315068.dbf' no

longer*
needed for this recovery

16:54:40 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL
Media recovery cancelled.

16:54:49 SQL>
16:54:51 SQL>
16:54:51 SQL> alter database open resetlogs;

alter database open resetlogs
*

ERROR at line 1:
ORA-01113: *file 1 needs media recovery
*ORA-01110: data file 1: '/thrsys/oradata/HRDEV/system01.dbf'

11593 and 11504 are from after 5am in the morning:

total 4076436
-rw-rw---- 1 oracle10 dba 47044608 Jan 30 05:00 1_11593_620315068.dbf
-rw-rw---- 1 oracle10 dba 47047680 Jan 30 05:32 1_11594_620315068.dbf

Then I did this:

16:55:01 SQL> recover database using backup controlfile *until cancel*; *<<-- Remember doing that long ago somewhere
*ORA-00279: change 11805870839 generated at 01/30/2008 05:32:08 needed for
thread 1
ORA-00289: suggestion : /thrundo/oradata/HRDEV/arch/1_11595_620315068.dbf ORA-00280: change 11805870839 for thread 1 is in sequence #11595

17:12:00 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} CANCEL
Media recovery cancelled.

17:12:04 SQL>
17:12:05 SQL>
17:12:05 SQL> alter database open resetlogs;

Database altered.

17:13:37 SQL>
17:13:38 SQL>
17:13:39 SQL>
17:13:39 SQL> select instance_name, status
17:13:44   2  from v$instance;

more..

INSTANCE_NAME STATUS

---------------- ------------
HRDEV            OPEN

Why would oracle ask for a file that is from 5am when the backup finished at 1 30am. Is it the "UNTIL CANCEL" that made the difference? Thanks
Ram.

*From Alert log*:

....
Tue Jan 29 22:02:41 2008
Thread 1 advanced to log sequence 11591
  Current log# 1 seq# 11591 mem# 0: /phrredo/oradata/HRPRD/redo01.log Wed Jan 30 00:00:14 2008
Thread 1 advanced to log sequence 11592
  Current log# 2 seq# 11592 mem# 0: /phrredo/oradata/HRPRD/redo02.log Wed Jan 30 00:40:01 2008
alter tablespace SYSTEM begin backup
Wed Jan 30 00:40:01 2008
Completed: alter tablespace SYSTEM begin backup Wed Jan 30 00:40:01 2008

..
..
..

alter tablespace EOLARGE end backup
Completed: alter tablespace EOLARGE end backup Wed Jan 30 01:23:31 2008
alter tablespace TEMP2 end backup
ORA-3217 signalled during: alter tablespace TEMP2 end backup... Wed Jan 30 01:23:31 2008
alter tablespace TEMP3 end backup
ORA-3217 signalled during: alter tablespace TEMP3 end backup... Wed Jan 30 01:23:32 2008
Thread 1 advanced to log sequence 11593
  Current log# 3 seq# 11593 mem# 0: /phrredo/oradata/HRPRD/redo03.log Wed Jan 30 01:32:44 2008
alter database backup controlfile to trace Wed Jan 30 01:32:44 2008
Completed: alter database backup controlfile to trace Wed Jan 30 05:00:15 2008
Thread 1 advanced to log sequence 11594
  Current log# 1 seq# 11594 mem# 0: /phrredo/oradata/HRPRD/redo01.log Wed Jan 30 05:32:08 2008
...

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 30 2008 - 17:24:32 CST