使用创建数据文件的方法修复数据库(9201)
作者:张大鹏(Lunar)
Email: moonlunar@163.com
MSN: lunar52@hotmail.com
前言
当丢失某个数据文件的时候,可以使用重建数据文件的方法快速的恢复数据,这样做的前题是,必须具有当前的控制文件(或者最新的BACKUP TO TRACE的控制文件,一边重建控制文件)和所有的归档日志,以及当前的联机日志文件(即,使用从该文件创建一来到当前时间的所有归档日志和联机日志,应用自文件创建一来的所有重做记录来前滚恢复该数据文件)。
所以,在改变数据库结构后,应当立刻备份数据库,如果条件不允许备份数据库,那么至少应当备份控制文件。
这种恢复方法尤其试用于当文件损坏时,还没有备份这个数据文件或者,备份的数据文件不可用的情况
一 使用当前控制文件
制作测试数据
查看当前数据
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 e:/oracle/oradata/test/archive
最早的概要日志序列 1
下一个存档日志序列 2
当前日志序列 2
SQL> conn lunar/lunar
已连接。
SQL> select * from test;
F
----------
15
16
25
26
创建新的表空间和数据文件
SQL> conn / as sysdba
已连接。
SQL> CREATE TABLESPACE test LOGGING DATAFILE
2 'E:/oracle/ora92/test/test01.dbf' SIZE 2M
3 AUTOEXTEND ON
4 NEXT 64K
5 MAXSIZE UNLIMITED
6 EXTENT MANAGEMENT LOCAL;
表空间已创建。
SQL> alter user lunar quota unlimited on test;
用户已更改。
SQL>
备份控制文件
SQL> ALTER DATABASE
BACKUP CONTROLFILE TO 'D:/cold_backup/CONTROL_BK.CTL';
Database altered.
SQL> alter system set user_dump_dest='D:/cold_backup';
系统已更改。
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
数据库已更改。
SQL> alter system set user_dump_dest='e:/oracle/admin/test/udump';
系统已更改。
SQL> host dir D:/cold_backup
驱动器 D 中的卷是 PROGRAMS
卷的序列号是 A06D-D424
D:/cold_backup 的目录
2002-02-02 21:07 <DIR> .
2002-02-02 21:07 <DIR> ..
2003-02-06 14:34 <DIR> archive
2003-02-06 14:44 1,875,968 CONTROL_BK.CTL
2003-02-06 14:44 4,578 test_ora_1844.trc
2 个文件 1,880,874 字节
3 个目录 2,367,717,376 可用字节
SQL>
在新的数据文件中插入测试数据
SQL> conn lunar/lunar
已连接。
SQL> create table lunar(aa number) TABLESPACE TEST;
表已创建。
SQL> select TABLE_NAME,TABLESPACE_NAME
2 from user_tables
3 where TABLE_NAME='LUNAR';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LUNAR TEST
SQL>
SQL> insert into lunar values(1);
已创建 1 行。
SQL> insert into lunar values(2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from lunar;
AA
----------
1
2
SQL> insert into lunar values(3);
已创建 1 行。
SQL>
再打开一个AS SYSDBA的窗口,产生checkpoin
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 Thu Feb 6 14:48:11 2003
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> show user
USER 为"LUNAR"
SQL> insert into lunar values(4);
已创建 1 行。
SQL> insert into lunar values(5);
已创建 1 行。
SQL>
回到AS SYSDBA的窗口,SWITCH LOGFILE
SQL> alter system switch logfile;
System altered.
SQL>
回到原来的窗口,继续增加数据,然后commit
SQL> show user
USER 为"LUNAR"
SQL> insert into lunar values(6);
已创建 1 行。
SQL> commit;
提交完成。
SQL>
回到AS SYSDBA的窗口,CHECKPOINT
SQL> alter system checkpoint;
System altered.
SQL>
回到原来的窗口,继续增加数据
SQL> insert into lunar values(7);
已创建 1 行。
SQL> select * from lunar;
AA
----------
1
2
( commit )
3
( checkpoint )
4
5
( switch logfile )
6
( commit,checkpoint )
7
( crash )
已选择7行。
应该可以恢复到所有commit的数据,即6
回到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/test01.dbf
C:/>exit
SQL>
启动数据库,报错
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
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'E:/ORACLE/ORA92/TEST/TEST01.DBF'
SQL>
恢复数据库
重建损坏的数据文件
SQL> alter database create datafile 'E:/oracle/ora92/test/test01.dbf';
Database altered.
SQL>
恢复损坏的数据文件(前滚数据)
SQL> recover datafile 'E:/oracle/ora92/test/test01.dbf';
Media recovery complete.
SQL>
打开数据库
SQL> alter database open;
Database altered.
SQL>
验证恢复
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 2
Next log sequence to archive 5
Current log sequence 5
SQL> conn lunar/lunar
Connected.
SQL> select * from lunar;
AA
----------
1
2
3
4
5
6
6 rows selected.
SQL> select TABLE_NAME,TABLESPACE_NAME
2 from user_tables
3 where TABLE_NAME='LUNAR';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LUNAR TEST
SQL>
二 使用备份控制文件(TO FILE)
制作测试数据
查看当前数据
SQL> conn / as sysdba
Connected.
SQL> drop tablespace test including contents;
Tablespace dropped.
SQL> host del E:/oracle/ora92/test/test01.dbf
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:/oracle/oradata/test/archive
Oldest online log sequence 7
Next log sequence to archive 10
Current log sequence 10
SQL>
创建新的表空间和数据文件
SQL> CREATE TABLESPACE test LOGGING DATAFILE
2 'E:/oracle/ora92/test/test01.dbf' SIZE 2M
3 AUTOEXTEND ON
4 NEXT 64K
5 MAXSIZE UNLIMITED
6 EXTENT MANAGEMENT LOCAL;
表空间已创建。
SQL> alter user lunar quota unlimited on test;
用户已更改。
SQL>
备份控制文件
SQL> ALTER DATABASE BACKUP CONTROLFILE
TO 'D:/cold_backup/CONTROL_BK.CTL';
Database altered.
SQL> alter system set user_dump_dest='D:/cold_backup';
System altered.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Database altered.
SQL> alter system set user_dump_dest='e:/oracle/admin/test/udump';
System altered.
SQL> host dir D:/cold_backup
驱动器 D 中的卷是 PROGRAMS
卷的序列号是 A06D-D424
D:/cold_backup 的目录
2002-02-02 21:07 <DIR> .
2002-02-02 21:07 <DIR> ..
2003-02-06 14:34 <DIR> archive
2003-02-06 16:25 1,875,968 CONTROL_BK.CTL
2003-02-06 16:25 4,982 test_ora_1868.trc
2 个文件 1,880,950 字节
3 个目录 2,365,833,216 可用字节
SQL>
在新的数据文件中插入测试数据
SQL> conn lunar/lunar
已连接。
SQL> create table lunar(aa number) TABLESPACE TEST;
表已创建。
SQL> select TABLE_NAME,TABLESPACE_NAME
2 from user_tables
3 where TABLE_NAME='LUNAR';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LUNAR TEST
SQL>
SQL> insert into lunar values(1);
已创建 1 行。
SQL> insert into lunar values(2);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from lunar;
AA
----------
1
2
SQL> insert into lunar values(3);
已创建 1 行。
SQL>
再打开一个AS SYSDBA的窗口,产生checkpoin
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.
C:/>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 6 14:48:11 2003
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;
系统已更改。
SQL>
回到原来的窗口,继续增加数据
SQL> show user
USER 为"LUNAR"
SQL> insert into lunar values(4);
已创建 1 行。
SQL> insert into lunar values(5);
已创建 1 行。
SQL>
回到AS SYSDBA的窗口,SWITCH LOGFILE
SQL> alter system switch logfile;
System altered.
SQL>
回到原来的窗口,继续增加数据,然后commit
SQL> show user
USER 为"LUNAR"
SQL> insert into lunar values(6);
已创建 1 行。
SQL> commit;
提交完成。
SQL>
回到AS SYSDBA的窗口,CHECKPOINT
SQL> alter system checkpoint;
System altered.
SQL>
回到原来的窗口,继续增加数据
SQL> insert into lunar values(7);
已创建 1 行。
SQL> select * from lunar;
AA
----------
1
2
( commit )
3
( checkpoint )
4
5
( switch logfile )
6
( commit,checkpoint )
7
( crash )
已选择7行。
应该可以恢复到所有commit的数据,即6
回到AS SYSDBA的窗口,模拟数据库crash
SQL> shutdown abort
ORACLE instance shut down.
SQL>
删除新增的数据文件
C:/>del E:/oracle/ora92/test/test01.dbf
C:/>del E:/oracle/ora92/test/control*.*
C:/>dir E:/oracle/ora92/test/control*.* E:/oracle/ora92/test/test01.dbf
驱动器 E 中的卷是 ORACLE
卷的序列号是 707A-C50C
E:/oracle/ora92/test 的目录
E:/oracle/ora92/test 的目录
找不到文件
C:/>
启动数据库,报错
C:/>set nls_lang=AMERICAN_AMERICA.US7ASCII
C:/>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Feb 6 15:30:13 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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>
恢复数据库
把备份的控制文件拷贝回来
C:/>copy D:/cold_backup/CONTROL_BK.CTL E:/oracle/ora92/test/control02.ctl
已复制 1 个文件。
C:/>copy D:/cold_backup/CONTROL_BK.CTL E:/oracle/ora92/test/control03.ctl
已复制 1 个文件。
C:/>
装载但不打开数据库
SQL> startup mount;
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
Database mounted.
SQL>
重建损坏的数据文件(在OS上建立该文件)
SQL> alter database create datafile 'E:/oracle/ora92/test/test01.dbf';
Database altered.
SQL>
恢复数据库(using backup controlfile)
SQL> recover database using backup controlfile;
ORA-00279: change 1156110 generated at 02/06/2003 16:25:09 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_10.ARC
ORA-00280: change 1156110 for thread 1 is in sequence #10
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
Auto (已经应用了最后一个归档日志)
ORA-00279: change 1156200 generated at 02/06/2003 16:28:05 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_11.ARC
ORA-00280: change 1156200 for thread 1 is in sequence #11
ORA-00278: log file 'E:/ORACLE/ORADATA/TEST/ARCHIVE/1_10.ARC' no longer needed
for this recovery
ORA-00308: cannot open archived log 'E:/ORACLE/ORADATA/TEST/ARCHIVE/1_11.ARC'
ORA-27041: unable to open file
OSD-04002: ??????
O/S-Error: (OS 2) ???????????
SQL>
再次恢复(until cancel using backup controlfile)
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1156200 generated at 02/06/2003 16:28:05 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_11.ARC
ORA-00280: change 1156200 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:/ORACLE/ORA92/TEST/SYSTEM01.DBF'
ORA-01112: media recovery not started
说明需要当前日志文件来恢复数据库
使用当前日志文件来恢复数据库(前滚)
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1156200 generated at 02/06/2003 16:28:05 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_11.ARC
ORA-00280: change 1156200 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:/oracle/ora92/test/REDO01.LOG
ORA-00310: archived log contains sequence 10; sequence 11 required
ORA-00334: archived log: 'E:/ORACLE/ORA92/TEST/REDO01.LOG'
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:/ORACLE/ORA92/TEST/SYSTEM01.DBF'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1156200 generated at 02/06/2003 16:28:05 needed for thread 1
ORA-00289: suggestion : E:/ORACLE/ORADATA/TEST/ARCHIVE/1_11.ARC
ORA-00280: change 1156200 for thread 1 is in sequence #11
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
E:/oracle/ora92/test/REDO02.LOG
Log applied.
Media recovery complete.
SQL>
resetlogs打开数据库
SQL> alter database open resetlogs;
Database altered.
SQL>
验证恢复
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>
SQL> conn lunar/lunar
Connected.
SQL> select * from lunar;
AA
----------
1
2
3
4
5
6
6 rows selected.
SQL> select TABLE_NAME,TABLESPACE_NAME
2 from user_tables
3 where TABLE_NAME='LUNAR';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LUNAR TEST
SQL>