恢复2：

最新推荐文章于 2025-03-12 21:45:02 发布

原创最新推荐文章于 2025-03-12 21:45:02 发布 · 664 阅读

0 ·

CC 4.0 BY-SA版权

备份恢复专栏收录该内容

40 篇文章

订阅专栏

前提：

所有归档文件及日志文件正常，丢失所有数据文件和控制文件，但有以前老的控制文件和数据文件备份，但老的数据文件备份不包括新增加的数据文件，（即数据文件是不全的）

SQL> startup;
ORACLE instance started.

Total System Global Area 171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
ORA-01991: ??????? 'd:\oracle\product\10.1.0\db_1\DATABASE\PWDmyoracle.ORA'

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-38760: ???????????????

SQL> alter session set nls_language=american;

Session altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database

Alertlog中：

Errors in file d:\oracle\product\10.1.0\admin\myoracle\bdump\myoracle_rvwr_1228.trc:
ORA-38739: Flashback log file is more recent than control file.
ORA-38701: Flashback database log 49 seq 125 thread 1: "D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\FLASHBACK\O1_MF_53LT4BBM_.FLB"

Tue Jul 14 14:26:37 2009
Database mounted in Exclusive Mode.
ORA-1991 signalled during: ALTER DATABASE MOUNT...
Tue Jul 14 14:32:18 2009
alter database open
Tue Jul 14 14:32:19 2009
ORA-38760 signalled during: alter database open...
Tue Jul 14 14:32:40 2009
alter database open
Tue Jul 14 14:32:40 2009
ORA-38760 signalled during: alter database open...
Tue Jul 14 14:34:05 2009

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-38760: This database instance failed to turn on flashback database

alertlog中：

ALTER DATABASE RECOVER database using backup controlfile until cancel
Tue Jul 14 14:34:05 2009
Media Recovery Start
Media Recovery failed with error 38760
ORA-283 signalled during: ALTER DATABASE RECOVER database using backup cont...
Tue Jul 14 14:35:23 2009

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     66
Next log sequence to archive   70
Current log sequence           70
SQL> select flashback_on from v$database;

FLA
---
YES

SQL> alter database flashback off;

Database altered.

alter database flashback off
Tue Jul 14 14:35:23 2009
Deleted Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\FLASHBACK\O1_MF_53JZNV4B_.FLB
Deleted Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\FLASHBACK\O1_MF_53L1MDGD_.FLB
。。。。。。。。。。。。。
Deleted Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\FLASHBACK\O1_MF_53MGNS5F_.FLB
Deleted Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\FLASHBACK\O1_MF_53MGYQB7_.FLB
Flashback Database Disabled
Starting control autobackup
db_recovery_file_dest_size of 6144 MB is 34.20% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Created Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\AUTOBACKUP\2009_07_14\O1_MF_S_689945766_55R9SM2M_.BKP
Control autobackup written to DISK device
handle 'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\AUTOBACKUP\2009_07_14\O1_MF_S_689945766_55R9SM2M_.BKP'
Completed: alter database flashback off
Tue Jul 14 14:35:42 2009
ALTER DATABASE RECOVER database using backup controlfile until cancel
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
attempting to start a parallel recovery with 2 processes
parallel recovery started with 2 processes
Starting datafile 1 with incarnation depth 0 in thread 1 sequence 551
Datafile 1: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\SYSTEM01.DBF'
Starting datafile 2 with incarnation depth 0 in thread 1 sequence 551
Datafile 2: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\UNDOTBS01.DBF'
Starting datafile 3 with incarnation depth 0 in thread 1 sequence 551
Datafile 3: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\SYSAUX01.DBF'
Starting datafile 4 with incarnation depth 0 in thread 1 sequence 551
Datafile 4: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\USERS01.DBF'
Starting datafile 5 with incarnation depth 0 in thread 1 sequence 551
Datafile 5: 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\DUREX.ORA'
Starting datafile 6 with incarnation depth 0 in thread 1 sequence 551
Datafile 6: 'D:\TEST\TEST.DBF'
Starting datafile 7 with incarnation depth 0 in thread 1 sequence 551
Datafile 7: 'D:\TEST\JERRY_UNDO.DBF'
Media Recovery Log
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup cont...
Tue Jul 14 14:36:34 2009
ALTER DATABASE RECOVER    LOGFILE 'auto;'
Tue Jul 14 14:36:34 2009
Media Recovery Log auto;
Errors with log auto;
ORA-308 signalled during: ALTER DATABASE RECOVER    LOGFILE 'auto;' ...
Tue Jul 14 14:36:53 2009
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Jul 14 14:36:54 2009
WARNING: Cannot delete Oracle managed file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_28\O1_MF_1_2845_51V37T7S_.ARC
Tue Jul 14 14:36:54 2009
Errors in file d:\oracle\product\10.1.0\admin\myoracle\udump\myoracle_ora_1372.trc:
ORA-01265: Unable to delete ARCHIVED LOG D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_05_28\O1_MF_1_2845_51V37T7S_.ARC
ORA-27056: could not delete file
OSD-04029: 无法获取文件属性
O/S-Error: (OS 3) 系统找不到指定的路径。

SQL> recover database using backup controlfile until cancel;--使用老的控制文件进行恢复
ORA-00279: change 14779437 generated at 06/19/2009 11:00:26 needed for threa
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\

F_1_70_%U_.ARC
ORA-00280: change 14779437 for thread 1 is in sequence #70

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto;
ORA-00308: cannot open archived log 'auto;'
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

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

auto;

ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1

F_1_303_%U_.ARC
ORA-00280: change 15789408 for thread 1 is in sequence #303
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_06_30\O

MF_1_302_54M43C3B_.ARC' no longer needed for this recovery

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 8: 'D:\TEST\TEST0630.DBF'

ORA-01112: media recovery not started

SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\USERS01.DBF
D:\ORACLE\PRODUCT\10.1.0\ORADATA\MYORACLE\DUREX.ORA
D:\TEST\TEST.DBF
D:\TEST\JERRY_UNDO.DBF
C:\WINDOWS\SYSTEM32\UNNAMED00008

8 rows selected.

由于使用的是备份的控制文件进行恢复，该文件中不包含后增加的文件，恢复过程中会缺省的赋予一个文件名（本例子为UNNAMED00008)

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database create datafile 'C:\WINDOWS\SYSTEM32\UNNAMED00008' as 'd:\te
st\test0630.dbf'; --重新创建后来丢失的数据文件

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

8 rows selected.

继续恢复

D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_M

F_1_533_%U_.ARC
ORA-00280: change 16696992 for thread 1 is in sequence #533
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_09\O1_

MF_1_532_55BNC0MQ_.ARC' no longer needed for this recovery

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 8: 'D:\TEST\BLOCK.DBF'

ORA-01112: media recovery not started

SQL> alter database create datafile 'C:\WINDOWS\SYSTEM32\UNNAMED00008' as 'd:\te
st\block.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

8 rows selected.

ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_M

F_1_551_%U_.ARC
ORA-00280: change 16802584 for thread 1 is in sequence #551
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_10\O1_

MF_1_550_55F14JPL_.ARC' no longer needed for this recovery

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 8: 'D:\TEST01.DBF'

ORA-01112: media recovery not started

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: 'C:\WINDOWS\SYSTEM32\UNNAMED00008'
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: 'C:\WINDOWS\SYSTEM32\UNNAMED00008'

ORA-00279: change 17215679 generated at 07/14/2009 11:01:04 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_M

F_1_659_%U_.ARC
ORA-00280: change 17215679 for thread 1 is in sequence #659
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_

MF_1_658_55QX7LK8_.ARC' no longer needed for this recovery

ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 9: 'D:\TEST\TEST02.DBF'

ORA-01112: media recovery not started

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: 'C:\WINDOWS\SYSTEM32\UNNAMED00009'
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01111: name for data file 9 is unknown - rename to correct file
ORA-01110: data file 9: 'C:\WINDOWS\SYSTEM32\UNNAMED00009'

SQL> alter database create datafile 'C:\WINDOWS\SYSTEM32\UNNAMED00009' as 'd:\te
st\test02.dbf';

Database altered.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 17218050 generated at 07/14/2009 11:45:55 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_M

F_1_659_%U_.ARC
ORA-00280: change 17218050 for thread 1 is in sequence #659

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 17218557 generated at 07/14/2009 11:52:16 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_M

F_1_660_%U_.ARC
ORA-00280: change 17218557 for thread 1 is in sequence #660
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_

MF_1_659_55R07L2D_.ARC' no longer needed for this recovery

ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_

MF_1_660_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 17218557 generated at 07/14/2009 11:52:16 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_M

F_1_660_%U_.ARC
ORA-00280: change 17218557 for thread 1 is in sequence #660

SQL> Alter session set nls_language=american;

Session altered.

F_1_660_%U_.ARC
ORA-00280: change 17218557 for thread 1 is in sequence #660

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\MYORACLE\ARCHIVELOG\2009_07_14\O1_

MF_1_660_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

恢复完成。