丢失全部数据文件，控制文件，redo log file（9201）——处理方法一
来源：互联网 发布：程序员修炼之道 豆瓣 编辑：程序博客网 时间：2024/04/28 19:05
丢失全部数据文件，控制文件，redo log file（9201）
作者：张大鹏（Lunar）
Email: moonlunar@163.com
MSN: lunar52@hotmail.com
一 使用备份的控制文件（热备）恢复
做热备份前的测试数据
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> conn lunar/lunar
Connected.
SQL> select * from test;
15
16
25
26
4 rows selected.
SQL> truncate table test;
Table truncated.
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL>
热备数据库
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> @D:/hot_backup/hotbackup_for_win
SQL> set termout off
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
SQL>
SQL> host copy E:/oracle/oradata/test/archive/*.* D:/hot_backup/archive
E:/oracle/oradata/test/archive/1_1.ARC
已复制 1 个文件。
SQL> host dir D:/hot_backup/archive
驱动器 D 中的卷是 PROGRAMS
卷的序列号是 A06D-D424
D:/hot_backup/archive 的目录
2002-02-04 13:59 <DIR> .
2002-02-04 13:59 <DIR> ..
2002-02-05 22:07 180,224 1_1.ARC
1 个文件 180,224 字节
2 个目录 1,623,019,520 可用字节
SQL>
制作热备份后的测试数据
提交部分测试数据（备份后增加的）
SQL> conn lunar/lunar
Connected.
SQL> select * from test;
no rows selected
SQL> insert into test values(25);
1 row created.
SQL> insert into test values(26);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into test values(27);
1 row created.
SQL> select * from test;
25
26
27
3 rows selected.
SQL>
再开一个窗口，产生检查点
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
C:/>set nls_lang=AMERICAN_AMERICA.US7ASCII
C:/>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 21:28:47 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> alter system checkpoint;
System altered.
SQL>
回到原来的窗口，继续添加数据
SQL> insert into test values(28);
1 row created.
SQL>
回到as sysdba的窗口，switch logfile
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL>
回到原来的窗口，commit后，继续添加数据
SQL> commit;
Commit complete.
SQL> insert into test values(29);
1 row created.
SQL>
回到as sysdba的窗口，产生检查点
SQL> alter system checkpoint;
System altered.
SQL>
回到原来的窗口，不commit继续添加数据
SQL> insert into test values(30);
1 row created.
SQL> select * from test;
25 (new datas after hot backup)
26
( commit )
27
(checkpoint )
28
( switch logfile )
(commit )
29
(checkpoint )
30
( shutdown abort)
8 rows selected.
SQL>
只能恢复到switch logfile前全部commit的数据，即：26
回到as sysdba的窗口，模拟数据库crash
SQL> shutdown abort
ORACLE instance shut down.
SQL>
删除所有数据库文件（数据文件，控制文件，redo log file）
SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
C:/>del E:/oracle/ora92/test/*.*
E:/oracle/ora92/test/*.*, 是否确认 (Y/N) ? y
C:/>dir E:/oracle/ora92/test
驱动器 E 中的卷是 ORACLE
卷的序列号是 707A-C50C
E:/oracle/ora92/test 的目录
2002-01-13 21:59 <DIR> .
2002-01-13 21:59 <DIR> ..
0 个文件 0 字节
2 个目录 2,803,036,160 可用字节
C:/>exit
SQL> startup
ORA-12571: TNS:packet writer failure
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
SQL> shutdown abort
ORACLE instance shut down.
SQL>
恢复数据库
将最近的热备拷贝过来
SQL> host
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
C:/>copy D:/hot_backup/*.* E:/oracle/ora92/test
D:/hot_backup/hotbackup_for_win.sql
D:/hot_backup/hotbackup.sql
D:/hot_backup/hotbackup.log
D:/hot_backup/SYSTEM01.DBF
D:/hot_backup/UNDOTBS01.DBF
D:/hot_backup/DRSYS01.DBF
D:/hot_backup/INDX01.DBF
D:/hot_backup/TOOLS01.DBF
D:/hot_backup/USERS01.DBF
D:/hot_backup/XDB01.DBF
D:/hot_backup/RMAN01.DBF
D:/hot_backup/CONTROL02.CTL
D:/hot_backup/test_ora_1024.trc
已复制 13 个文件。
C:/>dir E:/oracle/ora92/test
驱动器 E 中的卷是 ORACLE
卷的序列号是 707A-C50C
E:/oracle/ora92/test 的目录
2002-01-13 21:59 <DIR> .
2002-01-13 21:59 <DIR> ..
2002-02-05 21:52 367,009,792 SYSTEM01.DBF
2002-02-05 21:52 52,436,992 UNDOTBS01.DBF
2002-02-05 21:52 104,865,792 DRSYS01.DBF
2002-02-05 21:52 31,465,472 INDX01.DBF
2002-02-05 21:52 31,465,472 TOOLS01.DBF
2002-02-05 21:52 31,465,472 USERS01.DBF
2002-02-05 21:52 52,436,992 XDB01.DBF
2002-02-05 21:52 52,436,992 RMAN01.DBF
2002-02-05 22:07 1,875,968 CONTROL02.CTL
2002-02-05 22:07 4,905 test_ora_1024.trc
10 个文件 725,463,849 字节
2 个目录 2,077,544,448 可用字节
C:/>copy E:/oracle/ora92/test/CONTROL02.CTL E:/oracle/ora92/test/CONTROL03.CTL
已复制 1 个文件。
C:/>dir E:/oracle/ora92/test
驱动器 E 中的卷是 ORACLE
卷的序列号是 707A-C50C
E:/oracle/ora92/test 的目录
2002-01-13 21:59 <DIR> .
2002-01-13 21:59 <DIR> ..
2002-02-05 22:07 1,875,968 CONTROL03.CTL
2002-02-05 21:52 367,009,792 SYSTEM01.DBF
2002-02-05 21:52 52,436,992 UNDOTBS01.DBF
2002-02-05 21:52 104,865,792 DRSYS01.DBF
2002-02-05 21:52 31,465,472 INDX01.DBF
2002-02-05 21:52 31,465,472 TOOLS01.DBF
2002-02-05 21:52 31,465,472 USERS01.DBF
2002-02-05 21:52 52,436,992 XDB01.DBF
2002-02-05 21:52 52,436,992 RMAN01.DBF
2002-02-05 22:07 1,875,968 CONTROL02.CTL
2002-02-05 22:07 4,905 test_ora_1024.trc
11 个文件 727,339,817 字节
2 个目录 2,075,668,480 可用字节
C:/>
装载但不打开数据库
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
C:/>set nls_lang=AMERICAN_AMERICA.US7ASCII
C:/>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Feb 5 20:58:25 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount;
Database altered.
SQL>
使用using backup controlfile恢复数据库
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SQL> recover database using backup controlfile;
ORA-00279: change 1088428 generated at 02/05/2002 22:03:48 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_1.ARC
ORA-00280: change 1088428 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1088540 generated at 02/05/2002 22:07:14 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_2.ARC
ORA-00280: change 1088540 for thread 1 is in sequence #2
ORA-00278: log file 'E:/ORACLE/ORADATA/TEST/ARCHIVE/1_1.ARC' no longer needed
for this recovery
ORA-00279: change 1088612 generated at 02/05/2002 22:10:14 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_3.ARC
ORA-00280: change 1088612 for thread 1 is in sequence #3
ORA-00278: log file 'E:/ORACLE/ORADATA/TEST/ARCHIVE/1_2.ARC' no longer needed
for this recovery
ORA-00308: cannot open archived log 'E:/ORACLE/ORADATA/TEST/ARCHIVE/1_3.ARC'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
SQL>
使用until cancel using backup controlfile再次恢复
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: 'E:/ORACLE/ORA92/TEST/SYSTEM01.DBF'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1088612 generated at 02/05/2002 22:10:14 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_3.ARC
ORA-00280: change 1088612 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
使用resetlogs打开数据库
SQL> alter database open resetlogs;
Database altered.
SQL>
请注意此时的数据库文件的更新时间等状态：
SQL> host dir E:/oracle/ora92/test
驱动器 E 中的卷是 ORACLE
卷的序列号是 707A-C50C
E:/oracle/ora92/test 的目录
2002-01-13 21:59 <DIR> .
2002-01-13 21:59 <DIR> ..
2002-02-05 22:07 1,875,968 CONTROL03.CTL
2002-02-05 22:25 31,457,792 REDO01.LOG
2002-02-05 22:25 31,457,792 REDO02.LOG
2002-02-05 22:25 31,457,792 REDO03.LOG
2002-02-05 22:25 1,049,088 REDO04A.LOG
2002-02-05 22:25 1,049,088 REDO04B.LOG
2002-02-05 22:25 367,009,792 SYSTEM01.DBF
2002-02-05 22:25 52,436,992 UNDOTBS01.DBF
2002-02-05 22:25 104,865,792 DRSYS01.DBF
2002-02-05 22:25 31,465,472 INDX01.DBF
2002-02-05 22:25 31,465,472 TOOLS01.DBF
2002-02-05 22:25 31,465,472 USERS01.DBF
2002-02-05 22:25 52,436,992 XDB01.DBF
2002-02-05 22:25 52,436,992 RMAN01.DBF
2002-02-05 22:07 1,875,968 CONTROL02.CTL
2002-02-05 22:07 4,905 test_ora_1024.trc
16 个文件 823,811,369 字节
2 个目录 1,979,166,720 可用字节
SQL>
为临时表空间创建临时文件
SQL> alter tablespace temp
2 add tempfile 'E:/ORACLE/ORA92/TEST/temp01.DBF' SIZE 20M ;
Tablespace altered.
SQL> host dir E:/oracle/ora92/test
驱动器 E 中的卷是 ORACLE
卷的序列号是 707A-C50C
E:/oracle/ora92/test 的目录
2002-01-13 21:59 <DIR> .
2002-01-13 21:59 <DIR> ..
2002-02-05 22:07 1,875,968 CONTROL03.CTL
2002-02-05 22:25 31,457,792 REDO01.LOG
2002-02-05 22:25 31,457,792 REDO02.LOG
2002-02-05 22:25 31,457,792 REDO03.LOG
2002-02-05 22:25 1,049,088 REDO04A.LOG
2002-02-05 22:25 1,049,088 REDO04B.LOG
2002-02-05 22:25 367,009,792 SYSTEM01.DBF
2002-02-05 22:25 52,436,992 UNDOTBS01.DBF
2002-02-05 22:25 104,865,792 DRSYS01.DBF
2002-02-05 22:25 31,465,472 INDX01.DBF
2002-02-05 22:25 31,465,472 TOOLS01.DBF
2002-02-05 22:25 31,465,472 USERS01.DBF
2002-02-05 22:25 52,436,992 XDB01.DBF
2002-02-05 22:25 52,436,992 RMAN01.DBF
2002-02-05 22:07 1,875,968 CONTROL02.CTL
2002-02-05 22:07 4,905 test_ora_1024.trc
2002-02-05 22:27 20,979,712 TEMP01.DBF
17 个文件 844,791,081 字节
2 个目录 1,958,187,008 可用字节
SQL>
验证恢复
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> conn lunar/lunar
Connected.
SQL> select * from test;
F
----------
25
26
SQL>
丢失了部分数据commit的数据，因为那些数据是在redo中的，还没有被归档
- 丢失全部数据文件，控制文件，redo log file（9201）——处理方法一
- 丢失全部数据文件，控制文件，redo log file（9201）——处理方法二
- 丢失全部数据文件，控制文件，redo log file（9201）——处理方法三
- 丢失全部数据文件，控制文件，redo log file（9201）——处理方法四
- 试验之——丢失数据文件，全部控制文件，全部redo log file（原创）
- shutdown immediate的情况，丢失全部控制文件和数据文件（不包括redo），方法1
- shutdown immediate的情况，丢失全部控制文件和数据文件（不包括redo），方法2
- 同时丢失参数文件、控制文件及redo log file的不完全恢复（有数据文件的热备和归档）
- redo文件丢失处理方法
- shutdown abort的情况，恢复全部控制文件（不包括数据文件和redo）
- shutdown abort的情况，恢复全部控制文件和数据文件（不包括redo）
- oracle数据文件丢失处理方法一（测试）
- 重建控制文件后，各文件（datafile、control file、redo log）中scn的关系
- 丢失全部数据文件的恢复(不包括control和redo)
- 数据库一致关闭后REDO LOG全部丢失的解决办法
- Redo Log File（inactive、active）损坏，处理恢复对策
- 丢失控制文件和数据文件，但是有冷备份和全部归档文件的恢复
- 【翻译自mos文章】在unix/linux中使用文件描述符（File Descriptors）来找回被删掉的文件（数据文件or redo log）
- 設計模式之Chain of Responsibility(職責鏈)
- 設計模式之Command
- 新买了一块硬盘
- 点歌
- 浅谈关于redo log file的故障处理
- 丢失全部数据文件，控制文件，redo log file（9201）——处理方法一
- 丢失全部数据文件，控制文件，redo log file（9201）——处理方法二
- 丢失全部数据文件，控制文件，redo log file（9201）——处理方法三
- 丢失全部数据文件，控制文件，redo log file（9201）——处理方法四
- (转）DataGrid资料
- Introduction to RSS
- Writing is discovery!!
- 杯罩ABC的真正意思
- 说海交大内部网络上泄露的东东(转贴)
