db_file之system损坏恢复实验-CSDN博客

磁盘故障导致以备份的system数据文件损坏

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/db254/system01.dbf
/db254/undotbs01.dbf
/db254/sysaux01.dbf
/db254/users01.dbf
/db254/mytbs01.dbf

SQL> alter tablespace system begin backup;

Tablespace altered.

SQL> ! cp /db254/system01.dbf /u01/oracle/

SQL> alter tablespace system end backup;

Tablespace altered.

SQL> ! ls /u01/oracle/
admin ctl_rw.sql oradata product tcl.sql
ctl_ro.sql flash_recovery_area oraInventory system01.dbf

update scott.emp set sal=sal+1;
commit;
alter system switch logfile;

重复上面更新提交切换产生5次交易每次交易都在不同的日志中模拟实际成产机效果

SQL> select name from v$datafile;

NAME
--------------------------------------------------
/db254/system01.dbf
/db254/undotbs01.dbf
/db254/sysaux01.dbf
/db254/users01.dbf
/db254/mytbs01.dbf

SQL> ! ls
1 1.txt afiedt.buf arc1 a.sh Desktop mystat2.sql mystat.sql oratab

SQL> ! cp 1.txt /db254/system01.dbf

SQL> alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE

alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL>

系统崩溃开始恢复

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size      1218992 bytes
Variable Size     79693392 bytes
Database Buffers   201326592 bytes
Redo Buffers      2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/db254/system01.dbf'

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------------------------------------- ---------- ---------
1 ONLINE ONLINE FILE NOT FOUND 0

SQL> ! ls -l /db254/system01.dbf
-rw-r----- 1 oracle oinstall 26 02-28 17:11 /db254/system01.dbf

SQL> select * from v$recovery_log;

no rows selected

取回备份
SQL> ! cp /u01/oracle/system01.dbf /db254/

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ---------------------------------------- ---------- ---------
1 ONLINE ONLINE 846670 28-FEB-11

SQL> select * from v$recovery_log;
恢复所需要使用的归档

   THREAD# SEQUENCE# TIME ARCHIVE_NAME
---------- ---------- --------- ---------------------------------------------
1    61 28-FEB-11 /home/oracle/arc1/61_1_744004808.arc
1    62 28-FEB-11 /home/oracle/arc1/62_1_744004808.arc
1    63 28-FEB-11 /home/oracle/arc1/63_1_744004808.arc

SQL> recover datafile 1;
ORA-00279: change 846670 generated at 02/28/2011 17:02:37 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/61_1_744004808.arc
ORA-00280: change 846670 for thread 1 is in sequence #61

Specify log: {=suggested | filename | AUTO | CANCEL}
  =suggested 回车系统自己去查找
  filename   手动指明写日志或归档的文件位置
  AUTO       系统自动去查找
  cancel     取消恢复

ORA-00279: change 846737 generated at 02/28/2011 17:04:40 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/62_1_744004808.arc
ORA-00280: change 846737 for thread 1 is in sequence #62
ORA-00278: log file '/home/oracle/arc1/61_1_744004808.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 846742 generated at 02/28/2011 17:04:45 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/63_1_744004808.arc
ORA-00280: change 846742 for thread 1 is in sequence #63
ORA-00278: log file '/home/oracle/arc1/62_1_744004808.arc' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> sele

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756465/viewspace-722841/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24756465/viewspace-722841/