马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
本帖最后由 dbdream 于 2014-8-11 09:44 编辑
本文演示非核心数据文件丢失的恢复方法,测试环境使用OEL5.7 X86_64操作系统,数据库版本10.2.0.4.0。 对于开启归档且创建数据文件之后的归档都在的情况下,即使没有备份,丢失数据文件也是可以找回的,并且不会有数据丢失。如果是非归档模式,没有备份的情况下丢失了数据文件,即使数据库打开,丢失数据也是必然的。下面分别演示归档模式和非归档模式下丢失数据文件的恢复。 归档模式且日志全部存在: 创建测试表空间、测试用户和测试数据。 [HTML] 纯文本查看 复制代码 SQL> select name from v$datafile
NAME
——————————————————————————–
/u01/app/oracle/oradata/dbdream/dbdream/system01.dbf
/u01/app/oracle/oradata/dbdream/dbdream/undotbs01.dbf
/u01/app/oracle/oradata/dbdream/dbdream/sysaux01.dbf
/u01/app/oracle/oradata/dbdream/dbdream/users01.dbf
SQL> create tablespace dbdream datafile ‘/u01/app/oracle/oradata/dbdream/dbdream/dbdream.dbf’ size 100M;
Tablespace created.
SQL> create user dbdream identified by dbdream default tablespace dbdream;
User created.
SQL> grant connect,resource to dbdream;
Grant succeeded.
SQL> create table dbdream.reco_test as select rownum as id,object_name,object_type from dba_objects where rownum<11;
Table created.
SQL> select * from dbdream.reco_test;
ID OBJECT_NAME OBJECT_TYPE
———- —————————— ——————-
1 ICOL$ TABLE
2 I_USER1 INDEX
3 CON$ TABLE
4 UNDO$ TABLE
5 C_COBJ# CLUSTER
6 I_OBJ# INDEX
7 PROXY_ROLE_DATA$ TABLE
8 I_IND1 INDEX
9 I_CDEF2 INDEX
10 I_PROXY_ROLE_DATA$_1 INDEX
10 rows selected. 关闭数据库,在操作系统上删除新建的数据文件。 [HTML] 纯文本查看 复制代码 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@localhost dbdream]$ cd /u01/app/oracle/oradata/dbdream/dbdream
[oracle@localhost dbdream]$ ls
control01.ctl control02.ctl control03.ctl dbdream.dbf redo01.log redo02.log
redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@localhost dbdream]$ rm dbdream.dbf
[oracle@localhost dbdream]$ ls
control01.ctl control02.ctl control03.ctl redo01.log redo02.log redo03.log
sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf 启动数据库,OPEN阶段会报错。 [HTML] 纯文本查看 复制代码 SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2085288 bytes
Variable Size 150998616 bytes
Database Buffers 377487360 bytes
Redo Buffers 6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/dbdream/dbdream/dbdream.dbf’ 可见错误提示,第5号数据文件已经找不到了,此时正常情况下,数据库无法OPEN。但是不要太过紧张,只要归档还在,这种情况是可以恢复的。ORACLE支持通过控制文件创建数据文件然后通过日志来恢复丢失数据文件。并且这个操作在MOUNT状态就可以直接操作,如下: [HTML] 纯文本查看 复制代码 SQL> alter database create datafile 5;
Database altered. 这样一条简单的命令,ORACLE就会根据控制文件记录的信息重新创建一个新的数据文件,当然,此时还是空文件,需要通过日志来恢复的。 [HTML] 纯文本查看 复制代码 SQL> ! ls /u01/app/oracle/oradata/dbdream/dbdream/
control01.ctl control02.ctl control03.ctl dbdream.dbf redo01.log redo02.log
redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
SQL> recover datafile 5;
Media recovery complete. 如果日志都存在,通过日志恢复后,就可以直接打开数据库了,而且数据不会丢失。 [HTML] 纯文本查看 复制代码 SQL> alter database open;
Database altered.
SQL> select * from dbdream.reco_test;
ID OBJECT_NAME OBJECT_TYPE
———- —————————— ——————-
1 ICOL$ TABLE
2 I_USER1 INDEX
3 CON$ TABLE
4 UNDO$ TABLE
5 C_COBJ# CLUSTER
6 I_OBJ# INDEX
7 PROXY_ROLE_DATA$ TABLE
8 I_IND1 INDEX
9 I_CDEF2 INDEX
10 I_PROXY_ROLE_DATA$_1 INDEX
10 rows selected. 归档模式且日志全部丢失: 如果日志不存在,那么恢复也就无从谈起,但是可以讲这个数据文件offline,数据库还是可以打开的,只不过这个数据文件里的数据丢失了。下面演示过程省略创建测试数据部分。 [HTML] 纯文本查看 复制代码 SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2085288 bytes
Variable Size 150998616 bytes
Database Buffers 377487360 bytes
Redo Buffers 6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/dbdream/dbdream/dbdream.dbf’
启动报错,第5号数据文件丢失,归档全部丢失,recover无法进行,可以讲数据文件offline,然后打开数据库。
[HTML] 纯文本查看 复制代码 SQL> alter database datafile 5 offline;
Database altered.
SQL> alter database open;
Database altered. 此时查询这个数据文件里的数据肯定不行。 [HTML] 纯文本查看 复制代码 SQL> select * from dbdream.reco_test;
select * from dbdream.reco_test
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/dbdream/dbdream/dbdream.dbf’ 但是对其他数据文件操作还是可以的。如果日志不全,丢失了部分日志,那么可以恢复到最近的日志,然后使用BBED工具修改数据文件头,使这个数据文件online,当然,丢失数据是避免不了的。 非归档模式: 非归档模式,在没备份的情况下,数据文件丢失就悲剧了(这里不考虑扫盘),下面演示下非归档模式丢了数据文件怎么把库拉起来,演示过程同样省略创建测试数据部分。 [HTML] 纯文本查看 复制代码 SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2085288 bytes
Variable Size 150998616 bytes
Database Buffers 377487360 bytes
Redo Buffers 6299648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 – see DBWR trace file
ORA-01110: data file 5: ‘/u01/app/oracle/oradata/dbdream/dbdream/dbdream.dbf’
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/10.0/db_1/dbs/arch
Oldest online log sequence 8
Current log sequence 10
SQL> alter database datafile 5 offline;
alter database datafile 5 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled 在非归档模式,是不可以直接offline的,但是可以加上drop字句。 [HTML] 纯文本查看 复制代码 SQL> alter database datafile 5 offline drop;
Database altered.
SQL> alter database open;
Database altered. 在不支持offline drop的版本,可以通过重建控制文件的方式打开数据库。 [HTML] 纯文本查看 复制代码 SQL> alter database backup controlfile to trace;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2085288 bytes
Variable Size 150998616 bytes
Database Buffers 377487360 bytes
Redo Buffers 6299648 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE “DBDREAM” NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 ‘/u01/app/oracle/oradata/dbdream/dbdream/redo01.log’ SIZE 50M,
9 GROUP 2 ‘/u01/app/oracle/oradata/dbdream/dbdream/redo02.log’ SIZE 50M,
10 GROUP 3 ‘/u01/app/oracle/oradata/dbdream/dbdream/redo03.log’ SIZE 50M
11 — STANDBY LOGFILE
12 DATAFILE
13 ‘/u01/app/oracle/oradata/dbdream/dbdream/system01.dbf’,
14 ‘/u01/app/oracle/oradata/dbdream/dbdream/undotbs01.dbf’,
15 ‘/u01/app/oracle/oradata/dbdream/dbdream/sysaux01.dbf’,
16 ‘/u01/app/oracle/oradata/dbdream/dbdream/users01.dbf’
17 CHARACTER SET ZHS16GBK;
Control file created.
SQL> alter database open;
Database altered. 库虽然拉起来了,但是也意味着,这个数据文件的数据丢失了。 来自群组: Oracle DBA交流 |