前言,此种恢复是有条件的,只有在某些特定的环境下,才有可能在没有备份情况下恢复数据。
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 520095776 bytes
Database Buffers 310378496 bytes
Redo Buffers 2412544 bytes
Database mounted.
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: '/u01/app/oracle/oradata/orcl/test_data.dbf'
Media recovery complete.
例如:system表空间的数据文件丢失或损坏,除非借助备份,否则无法直接恢复。
1:创建演示环境
确认数据库是否处于归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 75
Next log sequence to archive 77
Current log sequence 77
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 75
Next log sequence to archive 77
Current log sequence 77
创建一个新的表空间和用户并授予相应权限
SQL> create tablespace test_data logging datafile '/u01/app/oracle/oradata/orcl/test_data.dbf' size 50m autoextend on next 50m maxsize 2048m extent management local;
Tablespace created.
SQL> create user test identified by test default tablespace test_data;
User created.
SQL> grant connect,resource,dba to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table t1(id number,name varchar(20));
Table created.
SQL> insert into t1 values(1,'sun');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from t1;
ID NAME
---------- --------------------
1 sun
1 sun
1 sun
1 sun
SQL> commit;
Commit complete.
SQL>
Tablespace created.
SQL> create user test identified by test default tablespace test_data;
User created.
SQL> grant connect,resource,dba to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table t1(id number,name varchar(20));
Table created.
SQL> insert into t1 values(1,'sun');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from t1;
ID NAME
---------- --------------------
1 sun
1 sun
1 sun
1 sun
SQL> commit;
Commit complete.
SQL>
2:模拟数据文件丢失,手动删除test_orcl数据文件
重新启动数据库
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 520095776 bytes
Database Buffers 310378496 bytes
Redo Buffers 2412544 bytes
Database mounted.
ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 6: '/u01/app/oracle/oradata/orcl/test_data.dbf'
3:执行恢复 ---归档日志和redo 都在的情况下
首先通过 alter database create datafile命令重建一个该文件。
SQL> alter database create datafile '/u01/app/oracle/oradata/orcl/test_data.dbf';
执行recover命令修复丢失的数据文件
SQL> recover datafile 6;
Media recovery complete.
打开数据库并测试
SQL> alter database open;
Database altered.
SQL> conn test/test
Connected.
SQL> select * from t1;
ID NAME
---------- --------------------
1 sun
1 sun
1 sun
1 sun
SQL>
Database altered.
SQL> conn test/test
Connected.
SQL> select * from t1;
ID NAME
---------- --------------------
1 sun
1 sun
1 sun
1 sun
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28698327/viewspace-1065458/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28698327/viewspace-1065458/