保是归档模式
检查数据状态
SQL> conn / as sysdBA
Connected.
SQL> select current_Scn from v$database;
CURRENT_SCN
-----------
2756129
SQL> @/home/oracle/q_log
GROUP# SEQUENCE# FILENAME FILESTA GROUPSTAT ARC
------ --------- ---------------------------------------- ------- ---------- ---
1 4 /u01/app/oracle/oradata/orcl/redo01.log INACTIVE YES
2 5 /u01/app/oracle/oradata/orcl/redo02.log CURRENT NO
3 3 /u01/app/oracle/oradata/orcl/redo03.log INACTIVE YES
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf 2754635
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2754635
/u01/app/oracle/oradata/orcl/undotbs01.dbf 2754635
/u01/app/oracle/oradata/orcl/users01.dbf 2755118
/u01/app/oracle/oradata/orcl/example01.dbf 2754635
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf 2754635
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2754635
/u01/app/oracle/oradata/orcl/undotbs01.dbf 2754635
/u01/app/oracle/oradata/orcl/users01.dbf 2755118
/u01/app/oracle/oradata/orcl/example01.dbf 2754635
2.模拟故障,发现报错
SQL> !rm -rf /u01/app/oracle/oradata/orcl/undotbs01.dbf
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
3.执行恢复
停止数据并重启到MOUNT状态
SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 548216832 bytes
Fixed Size 1337804 bytes
Variable Size 377488948 bytes
Database Buffers 163577856 bytes
Redo Buffers 5812224 bytes
Database mounted.
使用备份的文件,把丢失的UNDO文件复制回来
[oracle@oel orcl]$ cp /u03/hot_bak/undotbs01.dbf /u01/app/oracle/oradata/orcl/
检查一下控制文件及数据文件头中,检查点的信息,发现undotbs01.dbf这个文件的文件头中记录的检查信息比其它文件旧
需要恢复
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf 2754635
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2754635
/u01/app/oracle/oradata/orcl/undotbs01.dbf 2754635
/u01/app/oracle/oradata/orcl/users01.dbf 2755118
/u01/app/oracle/oradata/orcl/example01.dbf 2754635
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf 2754635
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2754635
/u01/app/oracle/oradata/orcl/undotbs01.dbf 2754456
/u01/app/oracle/oradata/orcl/users01.dbf 2755118
/u01/app/oracle/oradata/orcl/example01.dbf 2754635
单独对该文件进行恢复
SQL> recover datafile 3;
ORA-00279: change 2754456 generated at 05/20/2016 09:40:33 needed for thread 1
ORA-00289: suggestion : /u02/arch/1_1_912326240.dbf
ORA-00280: change 2754456 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2754614 generated at 05/20/2016 09:43:58 needed for thread 1
ORA-00289: suggestion : /u02/arch/1_2_912326240.dbf
ORA-00280: change 2754614 for thread 1 is in sequence #2
Log applied.
Media recovery complete.
打开数据库
SQL> alter database open;
Database altered.
再次检查数据库状态
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf 2776233
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2776233
/u01/app/oracle/oradata/orcl/undotbs01.dbf 2776233
/u01/app/oracle/oradata/orcl/users01.dbf 2776233
/u01/app/oracle/oradata/orcl/example01.dbf 2776233
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
-------------------------------------------------- ------------------
/u01/app/oracle/oradata/orcl/system01.dbf 2776233
/u01/app/oracle/oradata/orcl/sysaux01.dbf 2776233
/u01/app/oracle/oradata/orcl/undotbs01.dbf 2776233
/u01/app/oracle/oradata/orcl/users01.dbf 2776233
/u01/app/oracle/oradata/orcl/example01.dbf 2776233
归档模式下-丢失关键数据文件
最新推荐文章于 2023-04-18 09:29:31 发布