Recovery scenario
- From: "Ram Raman" <veeeraman@xxxxxxxxx>
- To: "Oracle List" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Jan 2008 17:24:32 -0600
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
...
Other related posts:
