丢失全部数据文件,控制文件,redo log file(9201)
作者:张大鹏(Lunar)
Email: moonlunar@163.com
MSN: lunar52@hotmail.com
二 使用重建控制文件做(热备)恢复
做热备份前的测试数据
SQL> conn lunar/lunar
Connected.
SQL> truncate table test;
Table truncated.
SQL> insert into test values(15);
1 row created.
SQL> insert into test values(16);
1 row created.
SQL> commit;
Commit complete.
热备数据库
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 1
Next log sequence to archive 1
Current log sequence 1
SQL> @D:/hot_backup/hotbackup_for_win
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
已复制 1 个文件。
SQL>
做热备份后的测试数据
提交部分测试数据(备份后增加的)
SQL> conn lunar/lunar
Connected.
SQL> select * from test;
15
16
2 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>
再开一个窗口,产生检查点
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 Enterprise Edition Release 9.2.0.1.0 - Production
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;
15 (new datas after hot backup)
16
25
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>
删除所有数据库文件
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,802,962,432 可用字节
C:/>
C:/>exit
SQL>
恢复数据库
将最近的热备拷贝回来
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_1972.trc
已复制 13 个文件。
C:/>
C:/>exit
SQL>
编辑并修改备份的控制文件(backup to trace)
CREATE CONTROLFILE REUSE DATABASE "TEST1" RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'E:/ORACLE/ORA92/TEST/REDO01.LOG' SIZE 30M,
GROUP 2 'E:/ORACLE/ORA92/TEST/REDO02.LOG' SIZE 30M,
GROUP 3 'E:/ORACLE/ORA92/TEST/REDO03.LOG' SIZE 30M,
GROUP 4 (
'E:/ORACLE/ORA92/TEST/REDO04A.LOG',
'E:/ORACLE/ORA92/TEST/REDO04B.LOG'
) SIZE 1M
DATAFILE
'E:/ORACLE/ORA92/TEST/SYSTEM01.DBF',
'E:/ORACLE/ORA92/TEST/UNDOTBS01.DBF',
'E:/ORACLE/ORA92/TEST/DRSYS01.DBF',
'E:/ORACLE/ORA92/TEST/INDX01.DBF',
'E:/ORACLE/ORA92/TEST/TOOLS01.DBF',
'E:/ORACLE/ORA92/TEST/USERS01.DBF',
'E:/ORACLE/ORA92/TEST/XDB01.DBF',
'E:/ORACLE/ORA92/TEST/RMAN01.DBF'
CHARACTER SET ZHS16GBK
;
nomount数据库
SQL> conn / as sysdba
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>
重建控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST1" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 5
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 1
6 MAXLOGHISTORY 226
7 LOGFILE
8 GROUP 1 'E:/ORACLE/ORA92/TEST/REDO01.LOG' SIZE 30M,
9 GROUP 2 'E:/ORACLE/ORA92/TEST/REDO02.LOG' SIZE 30M,
10 GROUP 3 'E:/ORACLE/ORA92/TEST/REDO03.LOG' SIZE 30M,
11 GROUP 4 (
12 'E:/ORACLE/ORA92/TEST/REDO04A.LOG',
13 'E:/ORACLE/ORA92/TEST/REDO04B.LOG'
14 ) SIZE 1M
15 DATAFILE
16 'E:/ORACLE/ORA92/TEST/SYSTEM01.DBF',
17 'E:/ORACLE/ORA92/TEST/UNDOTBS01.DBF',
18 'E:/ORACLE/ORA92/TEST/DRSYS01.DBF',
19 'E:/ORACLE/ORA92/TEST/INDX01.DBF',
20 'E:/ORACLE/ORA92/TEST/TOOLS01.DBF',
21 'E:/ORACLE/ORA92/TEST/USERS01.DBF',
22 'E:/ORACLE/ORA92/TEST/XDB01.DBF',
23 'E:/ORACLE/ORA92/TEST/RMAN01.DBF'
24 CHARACTER SET ZHS16GBK
25 ;
Control file created.
SQL>
恢复数据库(USING BACKUP CONTROLFILE)
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE
ORA-00279: change 1087799 generated at 02/05/2002 21:19:44 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_1.ARC
ORA-00280: change 1087799 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1087918 generated at 02/05/2002 21:23:09 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_2.ARC
ORA-00280: change 1087918 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 1088081 generated at 02/05/2002 21:31:09 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_3.ARC
ORA-00280: change 1088081 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> 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 21:49 367,009,792 SYSTEM01.DBF
2002-02-05 21:49 52,436,992 UNDOTBS01.DBF
2002-02-05 21:49 104,865,792 DRSYS01.DBF
2002-02-05 21:49 31,465,472 INDX01.DBF
2002-02-05 21:49 31,465,472 TOOLS01.DBF
2002-02-05 21:49 31,465,472 USERS01.DBF
2002-02-05 21:49 52,436,992 XDB01.DBF
2002-02-05 21:49 52,436,992 RMAN01.DBF
2002-02-05 21:49 1,875,968 CONTROL02.CTL
2002-02-05 21:49 1,875,968 CONTROL03.CTL
10 个文件 727,334,912 字节
2 个目录 2,075,586,560 可用字节
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 1088081 generated at 02/05/2002 21:31:09 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_3.ARC
ORA-00280: change 1088081 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> 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 21:52 31,457,792 REDO01.LOG
2002-02-05 21:52 31,457,792 REDO02.LOG
2002-02-05 21:52 31,457,792 REDO03.LOG
2002-02-05 21:52 1,049,088 REDO04A.LOG
2002-02-05 21:52 1,049,088 REDO04B.LOG
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 21:49 1,875,968 CONTROL02.CTL
2002-02-05 21:49 1,875,968 CONTROL03.CTL
15 个文件 823,806,464 字节
2 个目录 1,979,092,992 可用字节
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;
15
16
25
26
4 rows selected.
SQL>
丢失了部分数据commit的数据,因为那些数据是在redo中的,还没有被归档